1-- 2-- CREATE_TABLE 3-- 4-- 5-- CLASS DEFINITIONS 6-- 7CREATE TABLE hobbies_r ( 8 name text, 9 person text 10); 11CREATE TABLE equipment_r ( 12 name text, 13 hobby text 14); 15CREATE TABLE onek ( 16 unique1 int4, 17 unique2 int4, 18 two int4, 19 four int4, 20 ten int4, 21 twenty int4, 22 hundred int4, 23 thousand int4, 24 twothousand int4, 25 fivethous int4, 26 tenthous int4, 27 odd int4, 28 even int4, 29 stringu1 name, 30 stringu2 name, 31 string4 name 32); 33CREATE TABLE tenk1 ( 34 unique1 int4, 35 unique2 int4, 36 two int4, 37 four int4, 38 ten int4, 39 twenty int4, 40 hundred int4, 41 thousand int4, 42 twothousand int4, 43 fivethous int4, 44 tenthous int4, 45 odd int4, 46 even int4, 47 stringu1 name, 48 stringu2 name, 49 string4 name 50) WITH OIDS; 51CREATE TABLE tenk2 ( 52 unique1 int4, 53 unique2 int4, 54 two int4, 55 four int4, 56 ten int4, 57 twenty int4, 58 hundred int4, 59 thousand int4, 60 twothousand int4, 61 fivethous int4, 62 tenthous int4, 63 odd int4, 64 even int4, 65 stringu1 name, 66 stringu2 name, 67 string4 name 68); 69CREATE TABLE person ( 70 name text, 71 age int4, 72 location point 73); 74CREATE TABLE emp ( 75 salary int4, 76 manager name 77) INHERITS (person) WITH OIDS; 78CREATE TABLE student ( 79 gpa float8 80) INHERITS (person); 81CREATE TABLE stud_emp ( 82 percent int4 83) INHERITS (emp, student); 84NOTICE: merging multiple inherited definitions of column "name" 85NOTICE: merging multiple inherited definitions of column "age" 86NOTICE: merging multiple inherited definitions of column "location" 87CREATE TABLE city ( 88 name name, 89 location box, 90 budget city_budget 91); 92CREATE TABLE dept ( 93 dname name, 94 mgrname text 95); 96CREATE TABLE slow_emp4000 ( 97 home_base box 98); 99CREATE TABLE fast_emp4000 ( 100 home_base box 101); 102CREATE TABLE road ( 103 name text, 104 thepath path 105); 106CREATE TABLE ihighway () INHERITS (road); 107CREATE TABLE shighway ( 108 surface text 109) INHERITS (road); 110CREATE TABLE real_city ( 111 pop int4, 112 cname text, 113 outline path 114); 115-- 116-- test the "star" operators a bit more thoroughly -- this time, 117-- throw in lots of NULL fields... 118-- 119-- a is the type root 120-- b and c inherit from a (one-level single inheritance) 121-- d inherits from b and c (two-level multiple inheritance) 122-- e inherits from c (two-level single inheritance) 123-- f inherits from e (three-level single inheritance) 124-- 125CREATE TABLE a_star ( 126 class char, 127 a int4 128); 129CREATE TABLE b_star ( 130 b text 131) INHERITS (a_star); 132CREATE TABLE c_star ( 133 c name 134) INHERITS (a_star); 135CREATE TABLE d_star ( 136 d float8 137) INHERITS (b_star, c_star); 138NOTICE: merging multiple inherited definitions of column "class" 139NOTICE: merging multiple inherited definitions of column "a" 140CREATE TABLE e_star ( 141 e int2 142) INHERITS (c_star); 143CREATE TABLE f_star ( 144 f polygon 145) INHERITS (e_star); 146CREATE TABLE aggtest ( 147 a int2, 148 b float4 149); 150CREATE TABLE hash_i4_heap ( 151 seqno int4, 152 random int4 153); 154CREATE TABLE hash_name_heap ( 155 seqno int4, 156 random name 157); 158CREATE TABLE hash_txt_heap ( 159 seqno int4, 160 random text 161); 162CREATE TABLE hash_f8_heap ( 163 seqno int4, 164 random float8 165); 166-- don't include the hash_ovfl_heap stuff in the distribution 167-- the data set is too large for what it's worth 168-- 169-- CREATE TABLE hash_ovfl_heap ( 170-- x int4, 171-- y int4 172-- ); 173CREATE TABLE bt_i4_heap ( 174 seqno int4, 175 random int4 176); 177CREATE TABLE bt_name_heap ( 178 seqno name, 179 random int4 180); 181CREATE TABLE bt_txt_heap ( 182 seqno text, 183 random int4 184); 185CREATE TABLE bt_f8_heap ( 186 seqno float8, 187 random int4 188); 189CREATE TABLE array_op_test ( 190 seqno int4, 191 i int4[], 192 t text[] 193); 194CREATE TABLE array_index_op_test ( 195 seqno int4, 196 i int4[], 197 t text[] 198); 199CREATE TABLE testjsonb ( 200 j jsonb 201); 202CREATE TABLE IF NOT EXISTS test_tsvector( 203 t text, 204 a tsvector 205); 206CREATE TABLE IF NOT EXISTS test_tsvector( 207 t text 208); 209NOTICE: relation "test_tsvector" already exists, skipping 210CREATE UNLOGGED TABLE unlogged1 (a int primary key); -- OK 211CREATE TEMPORARY TABLE unlogged2 (a int primary key); -- OK 212SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname; 213 relname | relkind | relpersistence 214----------------+---------+---------------- 215 unlogged1 | r | u 216 unlogged1_pkey | i | u 217 unlogged2 | r | t 218 unlogged2_pkey | i | t 219(4 rows) 220 221REINDEX INDEX unlogged1_pkey; 222REINDEX INDEX unlogged2_pkey; 223SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged\d' ORDER BY relname; 224 relname | relkind | relpersistence 225----------------+---------+---------------- 226 unlogged1 | r | u 227 unlogged1_pkey | i | u 228 unlogged2 | r | t 229 unlogged2_pkey | i | t 230(4 rows) 231 232DROP TABLE unlogged2; 233INSERT INTO unlogged1 VALUES (42); 234CREATE UNLOGGED TABLE public.unlogged2 (a int primary key); -- also OK 235CREATE UNLOGGED TABLE pg_temp.unlogged3 (a int primary key); -- not OK 236ERROR: only temporary relations may be created in temporary schemas 237LINE 1: CREATE UNLOGGED TABLE pg_temp.unlogged3 (a int primary key); 238 ^ 239CREATE TABLE pg_temp.implicitly_temp (a int primary key); -- OK 240CREATE TEMP TABLE explicitly_temp (a int primary key); -- also OK 241CREATE TEMP TABLE pg_temp.doubly_temp (a int primary key); -- also OK 242CREATE TEMP TABLE public.temp_to_perm (a int primary key); -- not OK 243ERROR: cannot create temporary relation in non-temporary schema 244LINE 1: CREATE TEMP TABLE public.temp_to_perm (a int primary key); 245 ^ 246DROP TABLE unlogged1, public.unlogged2; 247CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r'; 248CREATE TABLE as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r'; 249ERROR: relation "as_select1" already exists 250CREATE TABLE IF NOT EXISTS as_select1 AS SELECT * FROM pg_class WHERE relkind = 'r'; 251NOTICE: relation "as_select1" already exists, skipping 252DROP TABLE as_select1; 253PREPARE select1 AS SELECT 1 as a; 254CREATE TABLE as_select1 AS EXECUTE select1; 255CREATE TABLE as_select1 AS EXECUTE select1; 256ERROR: relation "as_select1" already exists 257SELECT * FROM as_select1; 258 a 259--- 260 1 261(1 row) 262 263CREATE TABLE IF NOT EXISTS as_select1 AS EXECUTE select1; 264NOTICE: relation "as_select1" already exists, skipping 265DROP TABLE as_select1; 266DEALLOCATE select1; 267-- check that the oid column is added before the primary key is checked 268CREATE TABLE oid_pk (f1 INT, PRIMARY KEY(oid)) WITH OIDS; 269DROP TABLE oid_pk; 270