Thread: Prefixing schema name
Hi,
Would it be possible to create a table or index in a schema without prefixing the schema name? I find it a hassle to keep specifying the schema name when I create an object or performing DMLs in a schema. The search_path configuration works only for queries.
For example:
Is there a way to run the create/insert statements below without prefixing the schema name, user1?
As the user, user1:
Create table user1.table1 (id int);
Insert into user1.table1 values (1);
Thanks.
The search_path configuration works only for queries.
Um....
"A CREATE command specifying an unqualified object name creates the object in the current schema (the one at the front of the search path, which can be determined with the function current_schema)"
For example:Is there a way to run the create/insert statements below without prefixing the schema name, user1?
As the user, user1:Create table user1.table1 (id int);Insert into user1.table1 values (1);
Just omitting "user1" and seeing what happens would be informative. You should find it does exactly what you think - namely because the default search_path will cause "user1" to appear first.
Insert is more similar to Select than it is to Create - the object being inserted into must already exist
David J.
On 08/03/2018 01:13, David G. Johnston wrote:
The search_path configuration works only for queries.Um...."A CREATE command specifying an unqualified object name creates the object in the current schema (the one at the front of the search path, which can be determined with the function current_schema)"
In pg_dump relies heavily on search_path for all CREATE statements.
For example:Is there a way to run the create/insert statements below without prefixing the schema name, user1?As the user, user1:Create table user1.table1 (id int);Insert into user1.table1 values (1);Just omitting "user1" and seeing what happens would be informative. You should find it does exactly what you think - namely because the default search_path will cause "user1" to appear first.Insert is more similar to Select than it is to Create - the object being inserted into must already existDavid J.
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Thanks David and Achilleas. This is no longer an issue. The table previously went to the public schema. I'm not sure what I did. Anyway, without a prefix, it defaults to the current schema.
On Thu, Mar 8, 2018 at 2:02 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
On 08/03/2018 01:13, David G. Johnston wrote:The search_path configuration works only for queries.Um...."A CREATE command specifying an unqualified object name creates the object in the current schema (the one at the front of the search path, which can be determined with the function current_schema)"
In pg_dump relies heavily on search_path for all CREATE statements.For example:Is there a way to run the create/insert statements below without prefixing the schema name, user1?As the user, user1:Create table user1.table1 (id int);Insert into user1.table1 values (1);Just omitting "user1" and seeing what happens would be informative. You should find it does exactly what you think - namely because the default search_path will cause "user1" to appear first.Insert is more similar to Select than it is to Create - the object being inserted into must already existDavid J.
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt