2

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.

superblowncolon
  • 183
  • 1
  • 1
  • 15
  • Sounds to me like the DB account that you're running as does not have access (GRANT Privileges) to the table you're trying to query. – SoConfused Aug 24 '18 at 15:15
  • It appears that the column name has quotes in it, so the error message says that you literally need to use: `SELECT "return_part_i.CntrctTrmntnInd" FROM return_part_i` – Lamak Aug 24 '18 at 15:16
  • SQL is a unsorted dataset. Using `LIMIT` without `ORDER BY` can give other records each time when executed. It's even documentated in the PostgreSQL documentation "When using LIMIT, it is important to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows. You might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? The ordering is unknown, unless you specified ORDER BY." https://www.postgresql.org/docs/current/static/queries-limit.html – Raymond Nijland Aug 24 '18 at 16:28

1 Answers1

11

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;
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • SQL is a unsorted dataset. Using `LIMIT` without `ORDER BY` can give other records each time when executed.. So this query requires a `ORDER BY` clause to deliver the same results always. – Raymond Nijland Aug 24 '18 at 16:32
  • 1
    @RaymondNijland . correct suggestion .. answer updated with order by – ScaisEdge Aug 24 '18 at 16:42