PostgreSQL database code 42703 is a common error encountered and triggers an error message "column does not exist". Basically, this error indicates either that the requested column does not exist, or that the query is not correct.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform related PostgreSQL queries.
In this context, we shall look into how to fix this PostgreSQL error.
How to resolve PostgreSQL Error code 42703 ?
Sometimes this error is a result of lack of quotes. We can add double quotes to the column name to fix this error.
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 in fixing PostgreSQL Errors? We can help you. ]