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.
Wondering how to fix PostgreSQL Error code 42703? We can help you.
One of the most common error codes with the PostgreSQL database is 42703. It will be seen along with the error message “column does not exist”. This error indicates either that the requested column does not exist, or that the query is not correct.
Here at Bobcares, we often handle requests from our customers to fix similar PostgreSQL errors as a part of our Server Management Services. Today we will see how our support engineers fix this for our customers.
How to fix PostgreSQL Error code 42703
Often, the error is caused by a lack of quotes. We can add double quotes to the column name to fix this error.
For example:
We will try to run a simple select query:
SELECT return_part_i.CntrctTrmntnInd FROM return_part_i LIMIT 10;
And get the following error:
ERROR: column return_part_i.cntrcttrmntnind does not exist LINE 1: SELECT return_part_i.CntrctTrmntnInd FROM return_part_i LIMI... ^ HINT: Perhaps you meant to reference the column "return_part_i.CntrctTrmntnInd". SQL state: 42703 Character: 8
if we have a camel case in our column name we must ensure to wrap the column name with a double quote.
This can be done in the following way:
SELECT "CntrctTrmntnInd" FROM return_part_i LIMIT 10;PostgreSQL columns (object) names are case sensitive when specified with double quotes. Unquoted identifiers are automatically used as lowercase so the correct case sequence must be written with double quotes.
If we want a LIMIT in result we must use an order by
SELECT "CntrctTrmntnInd" FROM return_part_i ORDER BY "CntrctTrmntnInd" LIMIT 10;When used with quotes, Postgresql is case sensitive regarding identifier names like table names and column names.
So a common issue that triggers this error is when we use the column name in our commands in any other cases other than that of the original one.
For instance, if the column name is “Price”, using “price” in the command can trigger the error.
Thus we need to make sure that the cases are correct.
[Need assistance? We can help you]
Conclusion
In short, we saw how our Support Techs fix PostgreSQL Error code 42703 for our customers.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED
var google_conversion_label = «owonCMyG5nEQ0aD71QM»;
I tried to create exactly the same but new table from old table in another database using dblink. This procedure used to worked last two times, but this time I got message:
«SQL state: 42703
Context: Error occurred on dblink connection named «unnamed»: could not execute query.»
Anyone knows where is the problem or how to solve it?
Please!
asked Jun 8, 2010 at 12:32
I encountered this problem and fixed it by using single quotes, rather than double quotes, for the string I was searching for:
select * from table where column = 'value';
answered Oct 7, 2013 at 23:00
Ashwin BalamohanAshwin Balamohan
3,2942 gold badges25 silver badges47 bronze badges
Asked
6 years, 10 months ago
Viewed
35k times
I am getting an error while running an update query with table name specified along with column name:
UPDATE Temp SET Temp.Id='234',Temp.Name='Test'WHERE Id='245'
This is the error:
ERROR: column "temp" of relation "temp" does not exist
LINE 1: UPDATE Temp SET Temp.Id='23...
^
********** Error **********
ERROR: column "temp" of relation "temp" does not exist
SQL state: 42703
Character: 24
dezso
30.4k13 gold badges98 silver badges143 bronze badges
asked Aug 3, 2016 at 9:02
You cannot (and need not) use table aliases (or tablename qualified column names) in the SET clause of an UPDATE. This even makes sense, as you can only update a single table in a single UPDATE, so there is no ambiguity in column names there.
Fortunately, the ever helpful documentation explicitly mentions your case:
column_name
The name of a column in the table named by
table_name. The column name can be qualified with a subfield name or array subscript,
if needed. Do not include the table’s name in the specification of a
target column — for example,UPDATE tab SET tab.col = 1is invalid.
So, the solution is to simply remove temp. from the SET clause:
UPDATE temp SET id = '234', name = 'Test' WHERE id = '245'
Notes:
- Are you really storing numbers as text? If yes, why? It is usually a recipe for disaster. For example, how do you prevent something like
'mkjcvnd7y78r3tgbhvcjh'entering youridcolumn? - The way you are using object names starting with capital letters is confusing. Without double-quoting its name, your table in reality is called
tempas opposed toTemp. Using it the latter way may decrease readability (depending on your preferences and habits, of course).
answered Aug 3, 2016 at 9:13
dezsodezso
30.4k13 gold badges98 silver badges143 bronze badges
What would happen in the case of doing an update for 2 tables that have the same field name?
update car c, airplane a
set c.nr_rute = 1
set a.nr_rute = 1
answered Dec 8, 2021 at 17:52
2
Simple LINQ query produces the following sql:
SELECT «Extent1″.»price» FROM «schema1».»table1″ AS «Extent1»
which fails with «ERROR: 42703: column Extent1.price does not exist»
Another example from modified LINQ query
SELECT «Alias1″.»Id», «Alias1″.»price» FROM «schema1».»table1″ AS «Alias1» LIMIT 1
ERROR: 42703: column Alias1.Id does not exist
Manually running those statements without the quotes works fine.
My setup is Npgsql.EntityFramework 2.2.5.0 / EF 6.1.3.0 / Postgres 9.4.1
When used with quotes, Postgresql is case sensitive regarding identifier names like table names and columns names.
In your case, your column name may have been created with double quotes using a different case of price. Maybe it was created as "Price"? If so, just recreate it without quotes, or using the same case sensitivity of your queries.
I need to check it, but I think those quotes are added by EF when generating the queries.
I hope it helps.
Thanks Francisco, you are right, this issue is caused by case mismatch.
Is there way to switch Npgsql to case-insensitive mode apart from applying data annotations to the model columns ?
Hi, @fatim !
I’m glad you got it working.
Is there way to switch Npgsql to case-insensitive mode apart from applying data annotations to the model columns ?
I don’t know it yet. I need to check if we can make something about those quotes.
@Emill , do you have any idea if it is possible to remove those quotes from EF queries?
Thanks in advance.
Postgresql’s names are case-sensitive but for some strange reason the identifiers put in an sql query are automatically converted to lower case by the lexer unless they are surrounded by quotes.
If the properties of the models don’t match the column names, it is possible by using attributes to use other column names.
If requested, we could have some option to automatically convert CamelCase identifiers to lower_case_with_underlines to better fit the naming conventions used by .net and Postgresql.
You can use EF6’s own API to specify the database names on all classes and properties (some Linq on the model + EF6’s fluent API should do the trick).