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?
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.