Обсуждение: 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
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

>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

>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

>>> "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