Horton

Thread

Commits

  1. Doc: be more precise about conflicts between relation names.

  1. Be more clear what names can be used for tables with "CREATE TABLE"?

    Daniel Westermann (DWE) <daniel.westermann@dbi-services.com> — 2021-10-30T11:08:15Z

    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

  2. Re: Be more clear what names can be used for tables with "CREATE TABLE"?

    Jürgen Purtz <juergen@purtz.de> — 2021-10-30T15:27:02Z

    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

  3. Re: Be more clear what names can be used for tables with "CREATE TABLE"?

    Daniel Westermann (DWE) <daniel.westermann@dbi-services.com> — 2021-10-31T19:48:15Z

    >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

  4. Re: Be more clear what names can be used for tables with "CREATE TABLE"?

    Laurenz Albe <laurenz.albe@cybertec.at> — 2021-11-01T04:39:43Z

    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

  5. Re: Be more clear what names can be used for tables with "CREATE TABLE"?

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-01T16:04:16Z

    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

  6. Re: Be more clear what names can be used for tables with "CREATE TABLE"?

    Daniel Westermann (DWE) <daniel.westermann@dbi-services.com> — 2021-11-02T12:38:28Z

    >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

  7. Re: Be more clear what names can be used for tables with "CREATE TABLE"?

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-02T16:12:41Z

    "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

  8. Re: Be more clear what names can be used for tables with "CREATE TABLE"?

    Daniel Westermann (DWE) <daniel.westermann@dbi-services.com> — 2021-11-02T18:51:34Z

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