CODEWITHSUNDEEP
sqlpostgresqlcolumn-alias
DBE7
DBE7

Reputation: 816

PostgreSQL - ERROR: column does not exist SQL state: 42703

I am trying to do a cohort analysis and compare average number of rentals based on the renter's first rental year(= the year where a renter rented first time). Basically, I am asking the question: are we retaining renters whose first year renting was 2013 than renters whose first year was 2015?

Here is my code:

SELECT renter_id, 
       Min(Date_part('year', created_at)) AS first_rental_year, 
       ( Count(trip_finish) )             AS number_of_trips 
FROM   bookings 
WHERE  state IN ( 'approved', 'aboard', 'ashore', 'concluded', 'disputed' ) 
  AND  first_rental_year = 2013 
GROUP  BY 1 
ORDER  BY 1;

The error message I get is:

ERROR:  column "first_rental_year" does not exist
LINE 6: ... 'aboard', 'ashore', 'concluded', 'disputed') AND first_rent...
                                                             ^

********** Error **********

ERROR: column "first_rental_year" does not exist
SQL state: 42703
Character: 208

Any help is much appreciated.

Upvotes: 6

Views: 73841

Answers (3)

Shreyash282
Shreyash282

Reputation: 86

little late to reply but I faced this issue and had to put a lot of time solving simple issue to save ur times heres the solution

in PostgreSQL, if a column name was created with uppercase or mixed case letters or contains special characters, you must use double quotes around it when referencing it in SQL queries. By default, PostgreSQL converts all unquoted identifiers to lowercase, which means that a column named Patient_id (with mixed case) needs to be referenced as "Patient_id".

Key Points: If the column is created in all lowercase, you do not need double quotes (e.g., patient_id). If the column is created with uppercase or mixed case, you must use double quotes (e.g., "Patient_id").

Upvotes: 0

Jeremy Thompson
Jeremy Thompson

Reputation: 65554

ERROR:

SQL Error [42703]: ERROR: column XYZ does not exist

Check you have double quotes around Column Fields:

BAD:

update public."AppTime" t Set "CustomTask"= 'XYZ' where  t.SharedAppId = 12890;

GOOD:

With double quotes around "SharedAppId"

update public."AppTime" t Set "CustomTask"= 'XYZ' where  t."SharedAppId" = 12890;

If you created the table without quotes, you should not use quotes when querying it, and vice versa. This is explained in the manual: "If you want to write portable applications you are advised to always quote a particular name or never quote it"

Upvotes: 4

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

SELECT renter_id,
       Count(trip_finish) AS number_of_trips 
FROM (
        SELECT renter_id, 
               trip_finish,
               Min(Date_part('year', created_at)) AS first_rental_year
        FROM   bookings 
        WHERE  state IN ( 'approved', 'aboard', 'ashore', 'concluded', 'disputed' ) 
     ) T
WHERE first_rental_year = 2013  
GROUP  BY renter_id
ORDER  BY renter_id ;

Upvotes: 6

Related Questions