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