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