Postgresql Column Doesn't Exist

sql postgresql quoted-identifier
superblowncolon picture superblowncolon · Aug 24, 2018 · Viewed 7.6k times · Source

I'm running a simple select query:

SELECT return_part_i.CntrctTrmntnInd FROM return_part_i LIMIT 10;

And getting 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

I have tried the query with and without the table identifier. I am copying the field name directly from the hint. This is happening with numerous fields in the database.

Answer

ScaisEdge picture ScaisEdge · Aug 24, 2018

if you really have a camel case in you column name then you must wrap the column name with double quote 

SELECT "CntrctTrmntnInd"  FROM return_part_i LIMIT 10;

PostgreSQL columns (object) name are case sensitive when specified with double quotes. Unquoted identifiers are automatically used as lowercase so the correct case sequence must be write with double quotes

and as correctly suggested by Raymond Nijland if you want a LIMIT in result you should use an order by 

SELECT "CntrctTrmntnInd"  FROM return_part_i ORDER BY "CntrctTrmntnInd" LIMIT 10;

Related questions

Cannot simply use PostgreSQL table name ("relation does not exist")

I'm trying to run the following PHP script to do a simple database query: $db_host = "localhost"; $db_name = "showfinder"; $username = "user"; $password = "password"; $dbconn = pg_connect("host=$db_host dbname=$db_name user=$username password=$password") or die('Could …

Read More
PostgreSQL column 'foo' does not exist

I have a table that has 20 integer columns and 1 text column named 'foo' If I run query: SELECT * from table_name where foo is NULL I get error: ERROR: column "foo" does not exist I have checked myself that his …

Read More
PostgreSQL "Column does not exist" but it actually does

I'm writing a Java application to automatically build and run SQL queries. For many tables my code works fine but on a certain table it gets stuck by throwing the following exception: Exception in thread "main" org.postgresql.util.PSQLException: …

Read More