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 simple, tag CREATE INDEX 89-- Inheritance 90CREATE TABLE person ( 91 id INT NOT NULL PRIMARY KEY, 92 name text, 93 age int4, 94 location point 95); 96NOTICE: DDL test: type simple, tag CREATE TABLE 97NOTICE: DDL test: type simple, tag CREATE INDEX 98CREATE TABLE emp ( 99 salary int4, 100 manager name 101) INHERITS (person) WITH OIDS; 102NOTICE: DDL test: type simple, tag CREATE TABLE 103CREATE TABLE student ( 104 gpa float8 105) INHERITS (person); 106NOTICE: DDL test: type simple, tag CREATE TABLE 107CREATE TABLE stud_emp ( 108 percent int4 109) INHERITS (emp, student); 110NOTICE: merging multiple inherited definitions of column "id" 111NOTICE: merging multiple inherited definitions of column "name" 112NOTICE: merging multiple inherited definitions of column "age" 113NOTICE: merging multiple inherited definitions of column "location" 114NOTICE: DDL test: type simple, tag CREATE TABLE 115-- Storage parameters 116CREATE TABLE storage ( 117 id INT 118) WITH ( 119 fillfactor = 10, 120 autovacuum_enabled = FALSE 121); 122NOTICE: DDL test: type simple, tag CREATE TABLE 123-- LIKE 124CREATE TABLE like_datatype_table ( 125 LIKE datatype_table 126 EXCLUDING ALL 127); 128NOTICE: DDL test: type simple, tag CREATE TABLE 129CREATE TABLE like_fkey_table ( 130 LIKE fkey_table 131 INCLUDING DEFAULTS 132 INCLUDING INDEXES 133 INCLUDING STORAGE 134); 135NOTICE: DDL test: type simple, tag CREATE TABLE 136NOTICE: DDL test: type simple, tag CREATE INDEX 137NOTICE: DDL test: type simple, tag CREATE INDEX 138-- Volatile table types 139CREATE UNLOGGED TABLE unlogged_table ( 140 id INT PRIMARY KEY 141); 142NOTICE: DDL test: type simple, tag CREATE TABLE 143NOTICE: DDL test: type simple, tag CREATE INDEX 144CREATE TEMP TABLE temp_table ( 145 id INT PRIMARY KEY 146); 147NOTICE: DDL test: type simple, tag CREATE TABLE 148NOTICE: DDL test: type simple, tag CREATE INDEX 149CREATE TEMP TABLE temp_table_commit_delete ( 150 id INT PRIMARY KEY 151) 152ON COMMIT DELETE ROWS; 153NOTICE: DDL test: type simple, tag CREATE TABLE 154NOTICE: DDL test: type simple, tag CREATE INDEX 155CREATE TEMP TABLE temp_table_commit_drop ( 156 id INT PRIMARY KEY 157) 158ON COMMIT DROP; 159NOTICE: DDL test: type simple, tag CREATE TABLE 160NOTICE: DDL test: type simple, tag CREATE INDEX 161