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