CREATE TABLE creates a composite type corresponding to the table row, which is and is not there

From: Hannu Krosing <hannuk(at)google(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: CREATE TABLE creates a composite type corresponding to the table row, which is and is not there
Date: 2024-03-08 00:12:40
Message-ID: CAMT0RQRysCb_Amy5CTENSc5GfsvXL1a4qX3mv_hx31_v74P==g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I could not find any explanation of the following behaviour in docs -

Our documentation for CREATE TABLE says:

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.

But these composite tables are only sometimes there

hannuk=# CREATE TABLE pair(a int, b int);
CREATE TABLE

hannuk=# INSERT INTO pair VALUES(1,2);
INSERT 0 1

hannuk=# select pg_typeof(p) from pair as p;
pg_typeof
-----------
pair

hannuk=# select pg_typeof(pg_typeof(p)) from pair as p;
pg_typeof
-----------
regtype

# first case where I can not use the table-defined type

hannuk=# create table anoter_pair of pair;
ERROR: type pair is not a composite type

# the type definitely is there as promised

hannuk=# create type pair as (a int, b int);
ERROR: type "pair" already exists

# and I can create similar type wit other name and use it to create table

hannuk=# create type pair2 as (a int, b int);
CREATE TYPE

hannuk=# create table anoter_pair of pair2;
CREATE TABLE

# and i can even use it in LIKE

hannuk=# CREATE TABLE pair3(like pair2);
CREATE TABLE

# the type is present in pg_type with type 'c' for Composite

hannuk=# select typname, typtype from pg_type where typname = 'pair';
typname | typtype
---------+---------
pair | c
(1 row)

# and I can add comment to the type

hannuk=# COMMENT ON TYPE pair is 'A Shroedingers type';
COMMENT

# but \dT does not show it (second case)

hannuk=# \dT pair
List of data types
Schema | Name | Description
--------+------+-------------
(0 rows)

---
Hannu

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Sutou Kouhei 2024-03-08 00:22:54 Re: Make COPY format extendable: Extract COPY TO format implementations
Previous Message Nathan Bossart 2024-03-07 22:56:17 Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value