Обсуждение: Be more clear what names can be used for tables with "CREATE TABLE"?
Be more clear what names can be used for tables with "CREATE TABLE"?
От
"Daniel Westermann (DWE)"
Дата:
HI all, in the documentation for CREATE TABLE we have this sentence: "The name of the table must be distinct from the name of any other table, sequence, index, view, or foreign table in thesame schema." At least materialized views are missing: postgres=# create materialized view t as select 1; SELECT 1 postgres=# create table t ( a int ); ERROR: relation "t" already exists postgres=# drop materialized view t; DROP MATERIALIZED VIEW Domains are also missing: postgres=# create domain t as int; CREATE DOMAIN postgres=# create table t ( a int ); ERROR: type "t" already exists HINT: A relation has an associated type of the same name, so you must use a name that doesn't conflict with any existingtype. postgres=# drop domain t; DROP DOMAIN ... but that maybe is covered by the next paragraph? "CREATE TABLE also automatically creates a data type that represents the composite type corresponding to one row of the table.Therefore, tables cannot have the same name as any existing data type in the same schema." Regards Daniel
a) Don't forget: constraint, function, procedure, trigger.
b) The hint applies to each of such objects: CREATE [TABLE|VIEW|FUNCTION|...]. To avoid such redundancy we could extend and clarify the definition of 'SQL object / local object / global object' within the glossary and refer to it.
c) In general we have 3 levels (namespaces) where object names are unique across the same or different types.
- cluster-level: database name, tablespace, replication origin, subscription for logical replication, role, ...?
- database-level: schema, extension, collation, data type cast, ...?
- schema-level: table, index, view, materialized view, foreign table, sequence, constraint, function, procedure, trigger, operator, ...?
--
Jürgen Purtz
Re: Be more clear what names can be used for tables with "CREATE TABLE"?
От
"Daniel Westermann (DWE)"
Дата:
>a) Don't forget: constraint, function, procedure, trigger. Functions don't count: postgres=# create function f() returns int as $$ select 1; $$ language sql; CREATE FUNCTION postgres=# create table f ( a int ); CREATE TABLE Procedures and constraints don't count: postgres=# create procedure p() as $$ declare a int; begin a = 1; end; $$ language plpgsql; CREATE PROCEDURE postgres=# create table p ( a int ); CREATE TABLE postgres=# alter table p add constraint c check ( a > 1 ); ALTER TABLE postgres=# create table c ( a int ); CREATE TABLE >b) The hint applies to each of such objects: CREATE [TABLE|VIEW|FUNCTION|...]. To avoid such redundancy we could extendand clarify the definition of 'SQL object / local object / global object' within the glossary and refer to it. +1 Regards Daniel
On Sat, 2021-10-30 at 11:08 +0000, Daniel Westermann (DWE) wrote: > HI all, > > in the documentation for CREATE TABLE we have this sentence: > > "The name of the table must be distinct from the name of any other table, sequence, index, view, or foreign table in thesame schema." > > At least materialized views are missing: > > Domains are also missing: > > ... but that maybe is covered by the next paragraph? > > "CREATE TABLE also automatically creates a data type that represents the composite type corresponding to one row of thetable. > Therefore, tables cannot have the same name as any existing data type in the same schema." Technically speaking, it is "objects stored in pg_class". Perhaps we can refer to the documentation of "pg_class", which will in turn refer you to "relkind". Yours, Laurenz Albe
Laurenz Albe <laurenz.albe@cybertec.at> writes: > On Sat, 2021-10-30 at 11:08 +0000, Daniel Westermann (DWE) wrote: >> in the documentation for CREATE TABLE we have this sentence: >> "The name of the table must be distinct from the name of any other table, sequence, index, view, or foreign table in thesame schema." >> At least materialized views are missing: > Technically speaking, it is "objects stored in pg_class". We use "relation" for that concept in the code, and I believe that that terminology is also used in the manual. I'm inclined to propose "The name of the table must be distinct from the name of any other relation (table, sequence, index, view, materialized view, or foreign table) in the same schema." I think the existing wording might be that way because somebody figured that "view" could subsume "materialized view". Which isn't an unreasonable position, but we haven't done it like that consistently. I don't think we need to be similarly exhaustive about enumerating the kinds of types that there are. regards, tom lane
Re: Be more clear what names can be used for tables with "CREATE TABLE"?
От
"Daniel Westermann (DWE)"
Дата:
>Laurenz Albe <laurenz.albe@cybertec.at> writes: >> On Sat, 2021-10-30 at 11:08 +0000, Daniel Westermann (DWE) wrote: >>> in the documentation for CREATE TABLE we have this sentence: >>> "The name of the table must be distinct from the name of any other table, sequence, index, view, or foreign table inthe same schema." >>> At least materialized views are missing: >> Technically speaking, it is "objects stored in pg_class". >We use "relation" for that concept in the code, and I believe that >that terminology is also used in the manual. I'm inclined to propose >"The name of the table must be distinct from the name of any other >relation (table, sequence, index, view, materialized view, or foreign >table) in the same schema." Works for me, +1 Regards Daniel
"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes: >> "The name of the table must be distinct from the name of any other >> relation (table, sequence, index, view, materialized view, or foreign >> table) in the same schema." > Works for me, +1 Done that way, then. regards, tom lane
Re: Be more clear what names can be used for tables with "CREATE TABLE"?
От
"Daniel Westermann (DWE)"
Дата:
>>> "The name of the table must be distinct from the name of any other >>> relation (table, sequence, index, view, materialized view, or foreign >>> table) in the same schema." >> Works for me, +1 >Done that way, then. Thank you