pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedBe more clear what names can be used for tables with "CREATE TABLE"?
8+ messages / 4 participants
[nested] [flat]
* Be more clear what names can be used for tables with "CREATE TABLE"?
@ 2021-10-30 11:08 Daniel Westermann (DWE) <daniel.westermann@dbi-services.com>
0 siblings, 2 replies; 8+ messages in thread
From: Daniel Westermann (DWE) @ 2021-10-30 11:08 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Be more clear what names can be used for tables with "CREATE TABLE"?
@ 2021-10-30 15:27 Jürgen Purtz <juergen@purtz.de>
parent: Daniel Westermann (DWE) <daniel.westermann@dbi-services.com>
1 sibling, 1 reply; 8+ messages in thread
From: Jürgen Purtz @ 2021-10-30 15:27 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Be more clear what names can be used for tables with "CREATE TABLE"?
@ 2021-10-31 19:48 Daniel Westermann (DWE) <daniel.westermann@dbi-services.com>
parent: Jürgen Purtz <juergen@purtz.de>
0 siblings, 0 replies; 8+ messages in thread
From: Daniel Westermann (DWE) @ 2021-10-31 19:48 UTC (permalink / raw)
To: Jürgen Purtz <juergen@purtz.de>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
>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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Be more clear what names can be used for tables with "CREATE TABLE"?
@ 2021-11-01 04:39 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Daniel Westermann (DWE) <daniel.westermann@dbi-services.com>
1 sibling, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2021-11-01 04:39 UTC (permalink / raw)
To: Daniel Westermann (DWE) <daniel.westermann@dbi-services.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Be more clear what names can be used for tables with "CREATE TABLE"?
@ 2021-11-01 16:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2021-11-01 16:04 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Daniel Westermann (DWE) <daniel.westermann@dbi-services.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Be more clear what names can be used for tables with "CREATE TABLE"?
@ 2021-11-02 12:38 Daniel Westermann (DWE) <daniel.westermann@dbi-services.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Daniel Westermann (DWE) @ 2021-11-02 12:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
>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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Be more clear what names can be used for tables with "CREATE TABLE"?
@ 2021-11-02 16:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Daniel Westermann (DWE) <daniel.westermann@dbi-services.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2021-11-02 16:12 UTC (permalink / raw)
To: Daniel Westermann (DWE) <daniel.westermann@dbi-services.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Be more clear what names can be used for tables with "CREATE TABLE"?
@ 2021-11-02 18:51 Daniel Westermann (DWE) <daniel.westermann@dbi-services.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Daniel Westermann (DWE) @ 2021-11-02 18:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
>>> "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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2021-11-02 18:51 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-30 11:08 Be more clear what names can be used for tables with "CREATE TABLE"? Daniel Westermann (DWE) <daniel.westermann@dbi-services.com>
2021-10-30 15:27 ` Jürgen Purtz <juergen@purtz.de>
2021-10-31 19:48 ` Daniel Westermann (DWE) <daniel.westermann@dbi-services.com>
2021-11-01 04:39 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-11-01 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-11-02 12:38 ` Daniel Westermann (DWE) <daniel.westermann@dbi-services.com>
2021-11-02 16:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-11-02 18:51 ` Daniel Westermann (DWE) <daniel.westermann@dbi-services.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox