1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
|
--
-- CREATE_TABLE
--
-- Datatypes
CREATE TABLE datatype_table (
id SERIAL,
id_big BIGSERIAL,
is_small SMALLSERIAL,
v_bytea BYTEA,
v_smallint SMALLINT,
v_int INT,
v_bigint BIGINT,
v_char CHAR(1),
v_varchar VARCHAR(10),
v_text TEXT,
v_bool BOOLEAN,
v_inet INET,
v_cidr CIDR,
v_macaddr MACADDR,
v_numeric NUMERIC(1,0),
v_real REAL,
v_float FLOAT(1),
v_float8 FLOAT8,
v_money MONEY,
v_tsquery TSQUERY,
v_tsvector TSVECTOR,
v_date DATE,
v_time TIME,
v_time_tz TIME WITH TIME ZONE,
v_timestamp TIMESTAMP,
v_timestamp_tz TIMESTAMP WITH TIME ZONE,
v_interval INTERVAL,
v_bit BIT,
v_bit4 BIT(4),
v_varbit VARBIT,
v_varbit4 VARBIT(4),
v_box BOX,
v_circle CIRCLE,
v_lseg LSEG,
v_path PATH,
v_point POINT,
v_polygon POLYGON,
v_json JSON,
v_xml XML,
v_uuid UUID,
v_pg_snapshot pg_snapshot,
v_enum ENUM_TEST,
v_postal_code japanese_postal_code,
v_int2range int2range,
PRIMARY KEY (id),
UNIQUE (id_big)
);
NOTICE: DDL test: type simple, tag CREATE SEQUENCE
NOTICE: DDL test: type simple, tag CREATE SEQUENCE
NOTICE: DDL test: type simple, tag CREATE SEQUENCE
NOTICE: DDL test: type simple, tag CREATE TABLE
NOTICE: DDL test: type simple, tag CREATE INDEX
NOTICE: DDL test: type simple, tag CREATE INDEX
NOTICE: DDL test: type simple, tag ALTER SEQUENCE
NOTICE: DDL test: type simple, tag ALTER SEQUENCE
NOTICE: DDL test: type simple, tag ALTER SEQUENCE
-- Constraint definitions
CREATE TABLE IF NOT EXISTS fkey_table (
id INT NOT NULL DEFAULT nextval('fkey_table_seq'::REGCLASS),
datatype_id INT NOT NULL REFERENCES datatype_table(id),
big_id BIGINT NOT NULL,
sometext TEXT COLLATE "POSIX",
check_col_1 INT NOT NULL CHECK(check_col_1 < 10),
check_col_2 INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fkey_big_id
FOREIGN KEY (big_id)
REFERENCES datatype_table(id_big),
EXCLUDE USING btree (check_col_2 WITH =)
);
NOTICE: DDL test: type simple, tag CREATE TABLE
NOTICE: DDL test: type simple, tag CREATE INDEX
NOTICE: DDL test: type simple, tag CREATE INDEX
NOTICE: DDL test: type alter table, tag ALTER TABLE
NOTICE: subcommand: ADD CONSTRAINT (and recurse)
NOTICE: subcommand: ADD CONSTRAINT (and recurse)
-- Typed table
CREATE TABLE employees OF employee_type (
PRIMARY KEY (name),
salary WITH OPTIONS DEFAULT 1000
);
NOTICE: DDL test: type simple, tag CREATE TABLE
NOTICE: DDL test: type alter table, tag ALTER TABLE
NOTICE: subcommand: SET NOT NULL
NOTICE: DDL test: type simple, tag CREATE INDEX
-- Inheritance
CREATE TABLE person (
id INT NOT NULL PRIMARY KEY,
name text,
age int4,
location point
);
NOTICE: DDL test: type simple, tag CREATE TABLE
NOTICE: DDL test: type simple, tag CREATE INDEX
CREATE TABLE emp (
salary int4,
manager name
) INHERITS (person);
NOTICE: DDL test: type simple, tag CREATE TABLE
CREATE TABLE student (
gpa float8
) INHERITS (person);
NOTICE: DDL test: type simple, tag CREATE TABLE
CREATE TABLE stud_emp (
percent int4
) INHERITS (emp, student);
NOTICE: merging multiple inherited definitions of column "id"
NOTICE: merging multiple inherited definitions of column "name"
NOTICE: merging multiple inherited definitions of column "age"
NOTICE: merging multiple inherited definitions of column "location"
NOTICE: DDL test: type simple, tag CREATE TABLE
-- Storage parameters
CREATE TABLE storage (
id INT
) WITH (
fillfactor = 10,
autovacuum_enabled = FALSE
);
NOTICE: DDL test: type simple, tag CREATE TABLE
-- LIKE
CREATE TABLE like_datatype_table (
LIKE datatype_table
EXCLUDING ALL
);
NOTICE: DDL test: type simple, tag CREATE TABLE
CREATE TABLE like_fkey_table (
LIKE fkey_table
INCLUDING DEFAULTS
INCLUDING INDEXES
INCLUDING STORAGE
);
NOTICE: DDL test: type simple, tag CREATE TABLE
NOTICE: DDL test: type alter table, tag ALTER TABLE
NOTICE: subcommand: ALTER COLUMN SET DEFAULT (precooked)
NOTICE: DDL test: type simple, tag CREATE INDEX
NOTICE: DDL test: type simple, tag CREATE INDEX
-- Volatile table types
CREATE UNLOGGED TABLE unlogged_table (
id INT PRIMARY KEY
);
NOTICE: DDL test: type simple, tag CREATE TABLE
NOTICE: DDL test: type simple, tag CREATE INDEX
CREATE TEMP TABLE temp_table (
id INT PRIMARY KEY
);
NOTICE: DDL test: type simple, tag CREATE TABLE
NOTICE: DDL test: type simple, tag CREATE INDEX
CREATE TEMP TABLE temp_table_commit_delete (
id INT PRIMARY KEY
)
ON COMMIT DELETE ROWS;
NOTICE: DDL test: type simple, tag CREATE TABLE
NOTICE: DDL test: type simple, tag CREATE INDEX
CREATE TEMP TABLE temp_table_commit_drop (
id INT PRIMARY KEY
)
ON COMMIT DROP;
NOTICE: DDL test: type simple, tag CREATE TABLE
NOTICE: DDL test: type simple, tag CREATE INDEX
|
