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

Question

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.

Petronia

i dont see the error part on your query you posted... the correct syntax should be AND extract(year from b1.created_at) – Conformist

Are you sure this is the same query? Error message is saying LINE 10? – Heptavalent

Sorry guys, added the correct error message – Petronia

The problem is that the column first_rental_year is not in your source table. Other SQL engines support syntax such as AND CALCULATED first_rental_year = 2013, but postgre doesn't. You could reuse your formula (AND Min(Date_part('year', created_at)) = 2013) or better, use @Juan Carlos' solution which avoids this redundancy. See this post for more info. – Tarratarradiddle

I can't reuse my formula because it says that aggregate functions aren't allowed in the WHERE clause – Petronia

i'd belong in the having clause but i'd go with @Juan Carlos – Lafrance

Answer 1

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 ; 

Interposition

This looks correct all it needs is T.first_rental_year, T.renter?? Right Juan or am i full of shi? – Heptavalent

The problem is you cant use the alias first_rental_year on the where, because at that time the alias doesnt exist. So you create the alias inside a subquery and use it outside. You also can change the alias and use the function Min(Date_part('year', created_at)) = 2013 – Interposition

ERROR: column "t.first_rental_year" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT * ^ ********** Error ********** ERROR: column "t.first_rental_year" must appear in the GROUP BY clause or be used in an aggregate function SQL state: 42803 Character: 8 – Petronia

Answer 2

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"

Susi

not really relevant for this question – Sentimental

@Sentimental completely relevant, it produces the same error message and this is the SO thread for answers. Disappointing you've been here longer than me and don't appreciate people sharing knowledge :( – Susi

1) even if it would produce the same error, in this thread the problem was different, and 2) The answer is ill-advised. If you want to go through the route of using quotes, you really should explain it all - when to quote and when to not quote. 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". – Sentimental

Answer 3

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").

Primate