0

I am making an application that needs to construct Postgresql queries that will execute successfully in scenarios when table names are reserved keywords etc.

In Sql Server syntax this is achieved by wrapping everything in square brackets [] i.e. SELECT * FROM [database].[schema].[table_name].

I thought the equivalent in Postgresql was the use of double quotes "" i.e. SELECT * FROM "database"."schema"."table_name".

However, when I try this in Postgresql I get the error

Relation X doesn't exist

This works:

SELECT * FROM "postgres"."schema_a".Academic_Attainment

But this doesn't:

SELECT * FROM "postgres"."schema_a"."Academic_Attainment"

Related to: Escaping keyword-like column names in Postgres

Any suggestions?

HelloWorld
  • 418
  • 3
  • 15
  • What's the actual name of the table, how was it created? Maybe it's `"academic_attainment"` - notice that in quotes, case matters. – Bergi Feb 27 '21 at 22:40
  • I'm guessing the table name is not actually Academic_Attainment but academic_attainment. If the table was created without double quoting the table name then it would get folded to all lower case. See here [Identifiers](https://www.postgresql.org/docs/12/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS). – Adrian Klaver Feb 27 '21 at 22:41
  • Possibly a dupe of https://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive/20880247 – Erwin Brandstetter Feb 28 '21 at 00:23

1 Answers1

1

As documented in the manual unquoted identifiers are folded to lowercase.

A quoted identifier is also case sensitive, so "Foo" is a different name than "foo".

So the name Academic_Attainment is the same as academic_attainment. If you really insist on using those dreaded double quotes, then you need to use a lower case identifier:

SELECT * 
FROM "schema_a"."academic_attainment"

In general it's strongly recommended to not use quoted identifiers at all. As a rule of thumb: never use double quotes and you are fine.

If you are constructing dynamic SQL, use the format() function to do that together with the %I placeholder. It will take care of quoting if necessary (and only then), e.g.

format('select * from %I.%I', 'public', 'some_table') yields select * from public.some_table but format('select * from %I.%I', 'public', 'group') yields select * from public."group"

Unrelated to your question: Postgres doesn't support cross-database queries, so you should not get into the habit including the database name into your fully qualified names. The syntax you are using only works because you are connected to the database postgres. So I would recommend to stop using the database name in any table reference.