Be more clear what names can be used for tables with "CREATE TABLE"?
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 the same 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 existing type.
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
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 extend and 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 the same 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 the table.
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 the 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."
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
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 the 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