1 CREATE TABLE ttable1 OF nothing;
2 ERROR: type "nothing" does not exist
3 CREATE TYPE person_type AS (id int, name text);
4 CREATE TABLE persons OF person_type;
5 CREATE TABLE IF NOT EXISTS persons OF person_type;
6 NOTICE: relation "persons" already exists, skipping
13 Table "public.persons"
14 Column | Type | Collation | Nullable | Default
15 --------+---------+-----------+----------+---------
18 Typed table of type: person_type
20 CREATE FUNCTION get_all_persons() RETURNS SETOF person_type
23 SELECT * FROM persons;
25 SELECT * FROM get_all_persons();
30 -- certain ALTER TABLE operations on typed tables are not allowed
31 ALTER TABLE persons ADD COLUMN comment text;
32 ERROR: cannot add column to typed table
33 ALTER TABLE persons DROP COLUMN name;
34 ERROR: cannot drop column from typed table
35 ALTER TABLE persons RENAME COLUMN id TO num;
36 ERROR: cannot rename column of typed table
37 ALTER TABLE persons ALTER COLUMN name TYPE varchar;
38 ERROR: cannot alter column type of typed table
39 CREATE TABLE stuff (id int);
40 ALTER TABLE persons INHERIT stuff;
41 ERROR: cannot change inheritance of typed table
42 CREATE TABLE personsx OF person_type (myname WITH OPTIONS NOT NULL); -- error
43 ERROR: column "myname" does not exist
44 CREATE TABLE persons2 OF person_type (
45 id WITH OPTIONS PRIMARY KEY,
49 Table "public.persons2"
50 Column | Type | Collation | Nullable | Default
51 --------+---------+-----------+----------+---------
52 id | integer | | not null |
55 "persons2_pkey" PRIMARY KEY, btree (id)
56 "persons2_name_key" UNIQUE CONSTRAINT, btree (name)
57 Typed table of type: person_type
59 CREATE TABLE persons3 OF person_type (
61 name WITH OPTIONS DEFAULT ''
64 Table "public.persons3"
65 Column | Type | Collation | Nullable | Default
66 --------+---------+-----------+----------+----------
67 id | integer | | not null |
68 name | text | | | ''::text
70 "persons3_pkey" PRIMARY KEY, btree (id)
71 Typed table of type: person_type
73 CREATE TABLE persons4 OF person_type (
74 name WITH OPTIONS NOT NULL,
75 name WITH OPTIONS DEFAULT '' -- error, specified more than once
77 ERROR: column "name" specified more than once
78 DROP TYPE person_type RESTRICT;
79 ERROR: cannot drop type person_type because other objects depend on it
80 DETAIL: table persons depends on type person_type
81 function get_all_persons() depends on type person_type
82 table persons2 depends on type person_type
83 table persons3 depends on type person_type
84 HINT: Use DROP ... CASCADE to drop the dependent objects too.
85 DROP TYPE person_type CASCADE;
86 NOTICE: drop cascades to 4 other objects
87 DETAIL: drop cascades to table persons
88 drop cascades to function get_all_persons()
89 drop cascades to table persons2
90 drop cascades to table persons3
91 CREATE TABLE persons5 OF stuff; -- only CREATE TYPE AS types may be used
92 ERROR: type stuff is not a composite type
95 CREATE TYPE person_type AS (id int, name text);
96 CREATE TABLE persons OF person_type;
97 INSERT INTO persons VALUES (1, 'test');
98 CREATE FUNCTION namelen(person_type) RETURNS int LANGUAGE SQL AS $$ SELECT length($1.name) $$;
99 SELECT id, namelen(persons) FROM persons;
105 CREATE TABLE persons2 OF person_type (
106 id WITH OPTIONS PRIMARY KEY,
110 Table "public.persons2"
111 Column | Type | Collation | Nullable | Default
112 --------+---------+-----------+----------+---------
113 id | integer | | not null |
116 "persons2_pkey" PRIMARY KEY, btree (id)
117 "persons2_name_key" UNIQUE CONSTRAINT, btree (name)
118 Typed table of type: person_type
120 CREATE TABLE persons3 OF person_type (
122 name NOT NULL DEFAULT ''
125 Table "public.persons3"
126 Column | Type | Collation | Nullable | Default
127 --------+---------+-----------+----------+----------
128 id | integer | | not null |
129 name | text | | not null | ''::text
131 "persons3_pkey" PRIMARY KEY, btree (id)
132 Typed table of type: person_type