Should I always be prefixing the schema name before the table names in queries(PostgreSQL 14)?-CodePudding

I am running Postgres 14 on my Mac. I installed the app version. I am trying from both the psql window and through jdbc calls and it expects the schema name to be prefixed always.

Example: select * from public."Melting";

Thanks.

CodePudding user response：

That's a good habit. Always use the explicit schema names, then you don't have to rely on the current setting of search_path.

CodePudding user response：

Normally public schema is in the search path mentioned in comments, default path and it's no necessary write it, but could be insecure and bad practice as cited in official docs:

Remove the public schema from the default search path, by modifying postgresql.conf or by issuing ALTER ROLE ALL SET search_path = "$user". https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATTERNS

so issue command

show search_path;

to see what's your case.