github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/table (about) 1 # LogicTest: !3node-tenant 2 statement ok 3 SET DATABASE = "" 4 5 statement error no database specified 6 CREATE TABLE a (id INT PRIMARY KEY) 7 8 statement error invalid table name: test."" 9 CREATE TABLE test."" (id INT PRIMARY KEY) 10 11 statement ok 12 CREATE TABLE test.a (id INT PRIMARY KEY) 13 14 statement error pgcode 42P07 relation "a" already exists 15 CREATE TABLE test.a (id INT PRIMARY KEY) 16 17 statement ok 18 SET DATABASE = test 19 20 statement error invalid table name: "" 21 CREATE TABLE "" (id INT PRIMARY KEY) 22 23 statement error pgcode 42P07 relation "a" already exists 24 CREATE TABLE a (id INT PRIMARY KEY) 25 26 statement error duplicate column name: "id" 27 CREATE TABLE b (id INT PRIMARY KEY, id INT) 28 29 statement error multiple primary keys for table "b" are not allowed 30 CREATE TABLE b (id INT PRIMARY KEY, id2 INT PRIMARY KEY) 31 32 statement error index \"primary\" contains duplicate column \"a\" 33 CREATE TABLE dup_primary (a int, primary key (a,a)) 34 35 statement error index \"dup_unique_a_a_key\" contains duplicate column \"a\" 36 CREATE TABLE dup_unique (a int, unique (a,a)) 37 38 statement ok 39 CREATE TABLE IF NOT EXISTS a (id INT PRIMARY KEY) 40 41 statement ok 42 COMMENT ON TABLE a IS 'a_comment' 43 44 query TTT colnames 45 SHOW TABLES FROM test 46 ---- 47 schema_name table_name type 48 public a table 49 50 statement ok 51 CREATE TABLE b (id INT PRIMARY KEY) 52 53 statement ok 54 CREATE TABLE c ( 55 id INT PRIMARY KEY, 56 foo INT, 57 bar INT, 58 INDEX c_foo_idx (foo), 59 INDEX (foo), 60 INDEX c_foo_bar_idx (foo ASC, bar DESC), 61 UNIQUE (bar) 62 ) 63 64 statement ok 65 COMMENT ON INDEX c_foo_idx IS 'index_comment' 66 67 query TTBITTBB colnames 68 SHOW INDEXES FROM c 69 ---- 70 table_name index_name non_unique seq_in_index column_name direction storing implicit 71 c primary false 1 id ASC false false 72 c c_foo_idx true 1 foo ASC false false 73 c c_foo_idx true 2 id ASC false true 74 c c_foo_idx1 true 1 foo ASC false false 75 c c_foo_idx1 true 2 id ASC false true 76 c c_foo_bar_idx true 1 foo ASC false false 77 c c_foo_bar_idx true 2 bar DESC false false 78 c c_foo_bar_idx true 3 id ASC false true 79 c c_bar_key false 1 bar ASC false false 80 c c_bar_key false 2 id ASC false true 81 82 query TTBITTBBT colnames 83 SHOW INDEXES FROM c WITH COMMENT 84 ---- 85 table_name index_name non_unique seq_in_index column_name direction storing implicit comment 86 c primary false 1 id ASC false false NULL 87 c c_foo_idx true 1 foo ASC false false index_comment 88 c c_foo_idx true 2 id ASC false true index_comment 89 c c_foo_idx1 true 1 foo ASC false false NULL 90 c c_foo_idx1 true 2 id ASC false true NULL 91 c c_foo_bar_idx true 1 foo ASC false false NULL 92 c c_foo_bar_idx true 2 bar DESC false false NULL 93 c c_foo_bar_idx true 3 id ASC false true NULL 94 c c_bar_key false 1 bar ASC false false NULL 95 c c_bar_key false 2 id ASC false true NULL 96 97 # primary keys can never be null 98 99 statement ok 100 CREATE TABLE d ( 101 id INT PRIMARY KEY NULL 102 ) 103 104 query TTBTTTB colnames 105 SHOW COLUMNS FROM d 106 ---- 107 column_name data_type is_nullable column_default generation_expression indices is_hidden 108 id INT8 false NULL · {primary} false 109 110 statement ok 111 CREATE TABLE e ( 112 id INT NULL PRIMARY KEY 113 ) 114 115 query TTBTTTB colnames 116 SHOW COLUMNS FROM e 117 ---- 118 column_name data_type is_nullable column_default generation_expression indices is_hidden 119 id INT8 false NULL · {primary} false 120 121 statement ok 122 CREATE TABLE f ( 123 a INT, 124 b INT, 125 c INT, 126 PRIMARY KEY (a, b, c) 127 ) 128 129 query TTBTTTB colnames 130 SHOW COLUMNS FROM f 131 ---- 132 column_name data_type is_nullable column_default generation_expression indices is_hidden 133 a INT8 false NULL · {primary} false 134 b INT8 false NULL · {primary} false 135 c INT8 false NULL · {primary} false 136 137 query TTTT 138 SHOW TABLES FROM test WITH COMMENT 139 ---- 140 public a table a_comment 141 public b table · 142 public c table · 143 public d table · 144 public e table · 145 public f table · 146 147 statement ok 148 SET DATABASE = "" 149 150 query error pgcode 42P01 relation "users" does not exist 151 SHOW COLUMNS FROM users 152 153 query error pgcode 42P01 relation "test.users" does not exist 154 SHOW COLUMNS FROM test.users 155 156 query error pgcode 42P01 relation "users" does not exist 157 SHOW INDEXES FROM users 158 159 query error pgcode 42P01 relation "test.users" does not exist 160 SHOW INDEXES FROM test.users 161 162 statement ok 163 CREATE TABLE test.users ( 164 id INT PRIMARY KEY, 165 name VARCHAR NOT NULL, 166 title VARCHAR, 167 nickname STRING CHECK (length(nickname) < 10), 168 username STRING(10), 169 email VARCHAR(100) NULL, 170 INDEX foo (name), 171 CHECK (length(nickname) < length(name)), 172 UNIQUE INDEX bar (id, name), 173 FAMILY "primary" (id, name), 174 FAMILY fam_1_title (title), 175 FAMILY fam_2_nickname (nickname), 176 FAMILY fam_3_username_email (username, email) 177 ) 178 179 query TTBTTTB colnames 180 SHOW COLUMNS FROM test.users 181 ---- 182 column_name data_type is_nullable column_default generation_expression indices is_hidden 183 id INT8 false NULL · {primary,foo,bar} false 184 name VARCHAR false NULL · {foo,bar} false 185 title VARCHAR true NULL · {} false 186 nickname STRING true NULL · {} false 187 username STRING(10) true NULL · {} false 188 email VARCHAR(100) true NULL · {} false 189 190 query TTBITTBB colnames 191 SHOW INDEXES FROM test.users 192 ---- 193 table_name index_name non_unique seq_in_index column_name direction storing implicit 194 users primary false 1 id ASC false false 195 users foo true 1 name ASC false false 196 users foo true 2 id ASC false true 197 users bar false 1 id ASC false false 198 users bar false 2 name ASC false false 199 200 statement error precision for type float must be at least 1 bit 201 CREATE TABLE test.precision (x FLOAT(0)) 202 203 statement error at or near "\)": syntax error: scale \(2\) must be between 0 and precision \(0\) 204 CREATE TABLE test.precision (x DECIMAL(0, 2)) 205 206 statement error at or near "\)": syntax error: scale \(4\) must be between 0 and precision \(2\) 207 CREATE TABLE test.precision (x DECIMAL(2, 4)) 208 209 query TT 210 SHOW CREATE TABLE test.users 211 ---- 212 test.public.users CREATE TABLE users ( 213 id INT8 NOT NULL, 214 name VARCHAR NOT NULL, 215 title VARCHAR NULL, 216 nickname STRING NULL, 217 username STRING(10) NULL, 218 email VARCHAR(100) NULL, 219 CONSTRAINT "primary" PRIMARY KEY (id ASC), 220 INDEX foo (name ASC), 221 UNIQUE INDEX bar (id ASC, name ASC), 222 FAMILY "primary" (id, name), 223 FAMILY fam_1_title (title), 224 FAMILY fam_2_nickname (nickname), 225 FAMILY fam_3_username_email (username, email), 226 CONSTRAINT check_nickname_name CHECK (length(nickname) < length(name)), 227 CONSTRAINT check_nickname CHECK (length(nickname) < 10:::INT8) 228 ) 229 230 statement ok 231 CREATE TABLE test.dupe_generated ( 232 foo INT CHECK (foo > 1), 233 bar INT CHECK (bar > 2), 234 CHECK (foo > 2), 235 CHECK (foo < 10) 236 ) 237 238 query TTTTB colnames 239 SHOW CONSTRAINTS FROM test.dupe_generated 240 ---- 241 table_name constraint_name constraint_type details validated 242 dupe_generated check_bar CHECK CHECK ((bar > 2:::INT8)) true 243 dupe_generated check_foo CHECK CHECK ((foo > 2:::INT8)) true 244 dupe_generated check_foo1 CHECK CHECK ((foo < 10:::INT8)) true 245 dupe_generated check_foo2 CHECK CHECK ((foo > 1:::INT8)) true 246 247 statement ok 248 CREATE TABLE test.named_constraints ( 249 id INT CONSTRAINT pk PRIMARY KEY, 250 name VARCHAR CONSTRAINT nn NOT NULL, 251 title VARCHAR CONSTRAINT def DEFAULT 'VP of Something', 252 nickname STRING CONSTRAINT ck1 CHECK (length(nickname) < 10), 253 username STRING(10) CONSTRAINT nl NULL, 254 email VARCHAR(100) CONSTRAINT uq UNIQUE, 255 INDEX foo (name), 256 CONSTRAINT uq2 UNIQUE (username), 257 CONSTRAINT ck2 CHECK (length(nickname) < length(name)), 258 UNIQUE INDEX bar (id, name), 259 FAMILY "primary" (id, name), 260 FAMILY fam_1_title (title), 261 FAMILY fam_2_nickname (nickname), 262 FAMILY fam_3_username_email (username, email) 263 ) 264 265 query TT 266 SHOW CREATE TABLE test.named_constraints 267 ---- 268 test.public.named_constraints CREATE TABLE named_constraints ( 269 id INT8 NOT NULL, 270 name VARCHAR NOT NULL, 271 title VARCHAR NULL DEFAULT 'VP of Something':::STRING, 272 nickname STRING NULL, 273 username STRING(10) NULL, 274 email VARCHAR(100) NULL, 275 CONSTRAINT pk PRIMARY KEY (id ASC), 276 UNIQUE INDEX uq (email ASC), 277 INDEX foo (name ASC), 278 UNIQUE INDEX uq2 (username ASC), 279 UNIQUE INDEX bar (id ASC, name ASC), 280 FAMILY "primary" (id, name), 281 FAMILY fam_1_title (title), 282 FAMILY fam_2_nickname (nickname), 283 FAMILY fam_3_username_email (username, email), 284 CONSTRAINT ck2 CHECK (length(nickname) < length(name)), 285 CONSTRAINT ck1 CHECK (length(nickname) < 10:::INT8) 286 ) 287 288 query TTTTB colnames 289 SHOW CONSTRAINTS FROM test.named_constraints 290 ---- 291 table_name constraint_name constraint_type details validated 292 named_constraints bar UNIQUE UNIQUE (id ASC, name ASC) true 293 named_constraints ck1 CHECK CHECK ((length(nickname) < 10:::INT8)) true 294 named_constraints ck2 CHECK CHECK ((length(nickname) < length(name))) true 295 named_constraints pk PRIMARY KEY PRIMARY KEY (id ASC) true 296 named_constraints uq UNIQUE UNIQUE (email ASC) true 297 named_constraints uq2 UNIQUE UNIQUE (username ASC) true 298 299 statement error duplicate constraint name: "pk" 300 CREATE TABLE test.dupe_named_constraints ( 301 id INT CONSTRAINT pk PRIMARY KEY, 302 title VARCHAR CONSTRAINT one CHECK (1>1), 303 name VARCHAR CONSTRAINT pk UNIQUE 304 ) 305 306 statement error duplicate constraint name: "one" 307 CREATE TABLE test.dupe_named_constraints ( 308 id INT CONSTRAINT pk PRIMARY KEY, 309 title VARCHAR CONSTRAINT one CHECK (1>1), 310 name VARCHAR CONSTRAINT one UNIQUE 311 ) 312 313 statement error duplicate constraint name: "one" 314 CREATE TABLE test.dupe_named_constraints ( 315 id INT CONSTRAINT pk PRIMARY KEY, 316 title VARCHAR CONSTRAINT one CHECK (1>1), 317 name VARCHAR CONSTRAINT one REFERENCES test.named_constraints (username), 318 INDEX (name) 319 ) 320 321 statement error duplicate constraint name: "one" 322 CREATE TABLE test.dupe_named_constraints ( 323 id INT CONSTRAINT pk PRIMARY KEY, 324 title VARCHAR CONSTRAINT one CHECK (1>1) CONSTRAINT one CHECK (1<1) 325 ) 326 327 statement ok 328 SET database = test 329 330 statement ok 331 CREATE TABLE alltypes ( 332 cbigint BIGINT, 333 cbigserial BIGSERIAL, 334 cblob BLOB, 335 cbool BOOL, 336 cbit BIT, 337 cbit12 BIT(12), 338 cvarbit VARBIT, 339 cvarbit12 VARBIT(12), 340 cbytea BYTEA, 341 cbytes BYTES, 342 cchar CHAR, 343 cchar12 CHAR(12), 344 cdate DATE, 345 cdec DEC, 346 cdec1 DEC(1), 347 cdec21 DEC(2,1), 348 cdecimal DECIMAL, 349 cdecimal1 DECIMAL(1), 350 cdecimal21 DECIMAL(2,1), 351 cdoubleprecision DOUBLE PRECISION, 352 cfloat FLOAT, 353 cfloat4 FLOAT4, 354 cfloat8 FLOAT8, 355 cint INT, 356 cint2 INT2, 357 cint4 INT4, 358 cint64 INT64, 359 cint8 INT8, 360 cinteger INTEGER, 361 cinterval INTERVAL, 362 cjson JSONB, 363 cnumeric NUMERIC, 364 cnumeric1 NUMERIC(1), 365 cnumeric21 NUMERIC(2,1), 366 cqchar "char", 367 creal REAL, 368 cserial SERIAL, 369 csmallint SMALLINT, 370 csmallserial SMALLSERIAL, 371 cstring STRING, 372 cstring12 STRING(12), 373 ctext TEXT, 374 ctimestamp TIMESTAMP, 375 ctimestampwtz TIMESTAMPTZ, 376 cvarchar VARCHAR, 377 cvarchar12 VARCHAR(12) 378 ) 379 380 query TTBTTTB colnames 381 SHOW COLUMNS FROM alltypes 382 ---- 383 column_name data_type is_nullable column_default generation_expression indices is_hidden 384 cbigint INT8 true NULL · {} false 385 cbigserial INT8 false unique_rowid() · {} false 386 cblob BYTES true NULL · {} false 387 cbool BOOL true NULL · {} false 388 cbit BIT true NULL · {} false 389 cbit12 BIT(12) true NULL · {} false 390 cvarbit VARBIT true NULL · {} false 391 cvarbit12 VARBIT(12) true NULL · {} false 392 cbytea BYTES true NULL · {} false 393 cbytes BYTES true NULL · {} false 394 cchar CHAR true NULL · {} false 395 cchar12 CHAR(12) true NULL · {} false 396 cdate DATE true NULL · {} false 397 cdec DECIMAL true NULL · {} false 398 cdec1 DECIMAL(1) true NULL · {} false 399 cdec21 DECIMAL(2,1) true NULL · {} false 400 cdecimal DECIMAL true NULL · {} false 401 cdecimal1 DECIMAL(1) true NULL · {} false 402 cdecimal21 DECIMAL(2,1) true NULL · {} false 403 cdoubleprecision FLOAT8 true NULL · {} false 404 cfloat FLOAT8 true NULL · {} false 405 cfloat4 FLOAT4 true NULL · {} false 406 cfloat8 FLOAT8 true NULL · {} false 407 cint INT8 true NULL · {} false 408 cint2 INT2 true NULL · {} false 409 cint4 INT4 true NULL · {} false 410 cint64 INT8 true NULL · {} false 411 cint8 INT8 true NULL · {} false 412 cinteger INT8 true NULL · {} false 413 cinterval INTERVAL true NULL · {} false 414 cjson JSONB true NULL · {} false 415 cnumeric DECIMAL true NULL · {} false 416 cnumeric1 DECIMAL(1) true NULL · {} false 417 cnumeric21 DECIMAL(2,1) true NULL · {} false 418 cqchar "char" true NULL · {} false 419 creal FLOAT4 true NULL · {} false 420 cserial INT8 false unique_rowid() · {} false 421 csmallint INT2 true NULL · {} false 422 csmallserial INT8 false unique_rowid() · {} false 423 cstring STRING true NULL · {} false 424 cstring12 STRING(12) true NULL · {} false 425 ctext STRING true NULL · {} false 426 ctimestamp TIMESTAMP true NULL · {} false 427 ctimestampwtz TIMESTAMPTZ true NULL · {} false 428 cvarchar VARCHAR true NULL · {} false 429 cvarchar12 VARCHAR(12) true NULL · {} false 430 rowid INT8 false unique_rowid() · {primary} true 431 432 statement ok 433 CREATE DATABASE IF NOT EXISTS smtng 434 435 statement ok 436 CREATE TABLE IF NOT EXISTS smtng.something ( 437 ID SERIAL PRIMARY KEY 438 ) 439 440 statement ok 441 ALTER TABLE smtng.something ADD COLUMN IF NOT EXISTS OWNER_ID INT 442 443 statement ok 444 ALTER TABLE smtng.something ADD COLUMN IF NOT EXISTS MODEL_ID INT 445 446 statement ok 447 ALTER TABLE smtng.something ADD COLUMN IF NOT EXISTS NAME STRING 448 449 statement ok 450 CREATE DATABASE IF NOT EXISTS smtng 451 452 statement ok 453 CREATE TABLE IF NOT EXISTS smtng.something ( 454 ID SERIAL PRIMARY KEY 455 ) 456 457 statement ok 458 ALTER TABLE smtng.something ADD COLUMN IF NOT EXISTS OWNER_ID INT 459 460 statement ok 461 ALTER TABLE smtng.something ADD COLUMN IF NOT EXISTS MODEL_ID INT 462 463 statement ok 464 ALTER TABLE smtng.something ADD COLUMN IF NOT EXISTS NAME STRING 465 466 # Regression test for #13725 467 statement ok 468 CREATE TABLE test.empty () 469 470 statement ok 471 SELECT * FROM test.empty 472 473 # Issue #14308: support tables with DEFAULT NULL columns. 474 statement ok 475 CREATE TABLE test.null_default ( 476 ts timestamp NULL DEFAULT NULL 477 ) 478 479 query TT 480 SHOW CREATE TABLE test.null_default 481 ---- 482 test.public.null_default CREATE TABLE null_default ( 483 ts TIMESTAMP NULL, 484 FAMILY "primary" (ts, rowid) 485 ) 486 487 # Issue #13873: don't permit invalid default columns 488 statement error could not parse "blah" as type decimal 489 CREATE TABLE test.t1 (a DECIMAL DEFAULT (DECIMAL 'blah')); 490 491 statement error could not parse "blah" as type decimal 492 create table test.t1 (c decimal default if(false, 1, 'blah'::decimal)); 493 494 statement ok 495 CREATE DATABASE a; CREATE TABLE a.c(d INT); INSERT INTO a.public.c(d) VALUES (1) 496 497 query I 498 SELECT a.public.c.d FROM a.public.c 499 ---- 500 1