I'm seeing an odd behaviour - if I don't wrap my schema name in quotes, the server returns

[42601] ERROR: syntax error at or near "."

The query is simply:

select count(*) from "authorization".logins

Here's what psql \dn shows:

     List of schemas
     Name      |  Owner
 authorization | user_login
(1 row)

Schema name doesn't contain upper case letters, only English ones. Tried it using DataGrip and Npgsql (.NET driver for PostgreSQL).
DataGrip added quotes around schema name, when with others it doesn't do so.

Table and schema was created and applied via Flyway

  • Which query produces the error? select count(*) from "authorization".logins; or select count(*) from authorization.logins;? – ypercubeᵀᴹ Dec 4 '18 at 8:30
  • @ypercubeᵀᴹ the one without quotes – chester89 Dec 4 '18 at 8:37

authorization is a reserved keyword in Postgres, so it needs to be quoted to be used as a (database/schema/table) name. See the list: Appendix C. SQL Key Words

And a very popular opinion among Postgres developers - to avoid the complications - is:
Do not use reserved words as identifiers.

  • 2
    I would even go so far to say: "Never use double quotes for identifiers" – a_horse_with_no_name Dec 4 '18 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.