github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/information_schema (about) 1 # LogicTest: local 2 3 # Verify information_schema database handles mutation statements correctly. 4 5 query error database "information_schema" does not exist 6 ALTER DATABASE information_schema RENAME TO not_information_schema 7 8 statement error schema cannot be modified: "information_schema" 9 CREATE TABLE information_schema.t (x INT) 10 11 query error database "information_schema" does not exist 12 DROP DATABASE information_schema 13 14 query TTT 15 SHOW TABLES FROM information_schema 16 ---- 17 information_schema administrable_role_authorizations table 18 information_schema applicable_roles table 19 information_schema check_constraints table 20 information_schema column_privileges table 21 information_schema columns table 22 information_schema constraint_column_usage table 23 information_schema enabled_roles table 24 information_schema key_column_usage table 25 information_schema parameters table 26 information_schema referential_constraints table 27 information_schema role_table_grants table 28 information_schema routines table 29 information_schema schema_privileges table 30 information_schema schemata table 31 information_schema sequences table 32 information_schema statistics table 33 information_schema table_constraints table 34 information_schema table_privileges table 35 information_schema tables table 36 information_schema user_privileges table 37 information_schema views table 38 39 # Verify that the name is not special for databases. 40 41 statement ok 42 CREATE DATABASE other_db 43 44 statement ok 45 ALTER DATABASE other_db RENAME TO information_schema 46 47 statement error database "information_schema" already exists 48 CREATE DATABASE information_schema 49 50 statement ok 51 DROP DATABASE information_schema CASCADE 52 53 54 # Verify information_schema tables handle mutation statements correctly. 55 56 statement error user root does not have DROP privilege on relation tables 57 ALTER TABLE information_schema.tables RENAME TO information_schema.bad 58 59 statement error user root does not have CREATE privilege on relation tables 60 ALTER TABLE information_schema.tables RENAME COLUMN x TO y 61 62 statement error user root does not have CREATE privilege on relation tables 63 ALTER TABLE information_schema.tables ADD COLUMN x DECIMAL 64 65 statement error user root does not have CREATE privilege on relation tables 66 ALTER TABLE information_schema.tables DROP COLUMN x 67 68 statement error user root does not have CREATE privilege on relation tables 69 ALTER TABLE information_schema.tables ADD CONSTRAINT foo UNIQUE (b) 70 71 statement error user root does not have CREATE privilege on relation tables 72 ALTER TABLE information_schema.tables DROP CONSTRAINT bar 73 74 statement error user root does not have CREATE privilege on relation tables 75 ALTER TABLE information_schema.tables ALTER COLUMN x SET DEFAULT 'foo' 76 77 statement error user root does not have CREATE privilege on relation tables 78 ALTER TABLE information_schema.tables ALTER x DROP NOT NULL 79 80 statement error user root does not have CREATE privilege on relation tables 81 CREATE INDEX i on information_schema.tables (x) 82 83 statement error user root does not have DROP privilege on relation tables 84 DROP TABLE information_schema.tables 85 86 statement error user root does not have CREATE privilege on relation tables 87 DROP INDEX information_schema.tables@i 88 89 statement error user root does not have GRANT privilege on relation tables 90 GRANT CREATE ON information_schema.tables TO root 91 92 statement error user root does not have GRANT privilege on relation tables 93 REVOKE CREATE ON information_schema.tables FROM root 94 95 96 # Verify information_schema tables handles read-only property correctly. 97 98 query error user root does not have DELETE privilege on relation tables 99 DELETE FROM information_schema.tables 100 101 query error user root does not have INSERT privilege on relation tables 102 INSERT INTO information_schema.tables VALUES ('abc') 103 104 statement error user root does not have UPDATE privilege on relation tables 105 UPDATE information_schema.tables SET a = 'abc' 106 107 statement error user root does not have DROP privilege on relation tables 108 TRUNCATE TABLE information_schema.tables 109 110 111 # Verify information_schema handles reflection correctly. 112 113 query T 114 SHOW DATABASES 115 ---- 116 defaultdb 117 postgres 118 system 119 test 120 121 query TTT 122 SHOW TABLES FROM test.information_schema 123 ---- 124 information_schema administrable_role_authorizations table 125 information_schema applicable_roles table 126 information_schema check_constraints table 127 information_schema column_privileges table 128 information_schema columns table 129 information_schema constraint_column_usage table 130 information_schema enabled_roles table 131 information_schema key_column_usage table 132 information_schema parameters table 133 information_schema referential_constraints table 134 information_schema role_table_grants table 135 information_schema routines table 136 information_schema schema_privileges table 137 information_schema schemata table 138 information_schema sequences table 139 information_schema statistics table 140 information_schema table_constraints table 141 information_schema table_privileges table 142 information_schema tables table 143 information_schema user_privileges table 144 information_schema views table 145 146 query TT colnames 147 SHOW CREATE TABLE information_schema.tables 148 ---- 149 table_name create_statement 150 information_schema.tables CREATE TABLE tables ( 151 table_catalog STRING NOT NULL, 152 table_schema STRING NOT NULL, 153 table_name STRING NOT NULL, 154 table_type STRING NOT NULL, 155 is_insertable_into STRING NOT NULL, 156 version INT8 NULL, 157 INDEX tables_table_name_idx (table_name ASC) STORING (table_catalog, table_schema, table_type, is_insertable_into, version) 158 ) 159 160 query TTBTTTB colnames 161 SHOW COLUMNS FROM information_schema.tables 162 ---- 163 column_name data_type is_nullable column_default generation_expression indices is_hidden 164 table_catalog STRING false NULL · {} false 165 table_schema STRING false NULL · {} false 166 table_name STRING false NULL · {} false 167 table_type STRING false NULL · {} false 168 is_insertable_into STRING false NULL · {} false 169 version INT8 true NULL · {} false 170 171 query TTBITTBB colnames 172 SHOW INDEXES FROM information_schema.tables 173 ---- 174 table_name index_name non_unique seq_in_index column_name direction storing implicit 175 176 query TTTTB colnames 177 SHOW CONSTRAINTS FROM information_schema.tables 178 ---- 179 table_name constraint_name constraint_type details validated 180 181 query TTTTT colnames 182 SHOW GRANTS ON information_schema.tables 183 ---- 184 database_name schema_name table_name grantee privilege_type 185 test information_schema tables public SELECT 186 187 188 # Verify selecting from information_schema. 189 190 ## information_schema.schemata 191 192 query TTTT colnames 193 SELECT * FROM information_schema.schemata 194 ---- 195 catalog_name schema_name default_character_set_name sql_path 196 test crdb_internal NULL NULL 197 test information_schema NULL NULL 198 test pg_catalog NULL NULL 199 test pg_extension NULL NULL 200 test public NULL NULL 201 202 query TTTT colnames 203 SELECT * FROM INFormaTION_SCHEMa.schemata 204 ---- 205 catalog_name schema_name default_character_set_name sql_path 206 test crdb_internal NULL NULL 207 test information_schema NULL NULL 208 test pg_catalog NULL NULL 209 test pg_extension NULL NULL 210 test public NULL NULL 211 212 ## information_schema.tables 213 214 # Check the default contents of information_schema.tables (incl. the 215 # special system tables) 216 query TT rowsort 217 select table_schema, table_name FROM information_schema.tables 218 ---- 219 crdb_internal backward_dependencies 220 crdb_internal builtin_functions 221 crdb_internal cluster_queries 222 crdb_internal cluster_sessions 223 crdb_internal cluster_settings 224 crdb_internal cluster_transactions 225 crdb_internal create_statements 226 crdb_internal create_type_statements 227 crdb_internal feature_usage 228 crdb_internal forward_dependencies 229 crdb_internal gossip_alerts 230 crdb_internal gossip_liveness 231 crdb_internal gossip_network 232 crdb_internal gossip_nodes 233 crdb_internal index_columns 234 crdb_internal jobs 235 crdb_internal kv_node_status 236 crdb_internal kv_store_status 237 crdb_internal leases 238 crdb_internal node_build_info 239 crdb_internal node_metrics 240 crdb_internal node_queries 241 crdb_internal node_runtime_info 242 crdb_internal node_sessions 243 crdb_internal node_statement_statistics 244 crdb_internal node_transactions 245 crdb_internal node_txn_stats 246 crdb_internal partitions 247 crdb_internal predefined_comments 248 crdb_internal ranges 249 crdb_internal ranges_no_leases 250 crdb_internal schema_changes 251 crdb_internal session_trace 252 crdb_internal session_variables 253 crdb_internal table_columns 254 crdb_internal table_indexes 255 crdb_internal tables 256 crdb_internal zones 257 information_schema administrable_role_authorizations 258 information_schema applicable_roles 259 information_schema check_constraints 260 information_schema column_privileges 261 information_schema columns 262 information_schema constraint_column_usage 263 information_schema enabled_roles 264 information_schema key_column_usage 265 information_schema parameters 266 information_schema referential_constraints 267 information_schema role_table_grants 268 information_schema routines 269 information_schema schema_privileges 270 information_schema schemata 271 information_schema sequences 272 information_schema statistics 273 information_schema table_constraints 274 information_schema table_privileges 275 information_schema tables 276 information_schema user_privileges 277 information_schema views 278 pg_catalog pg_aggregate 279 pg_catalog pg_am 280 pg_catalog pg_attrdef 281 pg_catalog pg_attribute 282 pg_catalog pg_auth_members 283 pg_catalog pg_authid 284 pg_catalog pg_available_extensions 285 pg_catalog pg_cast 286 pg_catalog pg_class 287 pg_catalog pg_collation 288 pg_catalog pg_constraint 289 pg_catalog pg_conversion 290 pg_catalog pg_database 291 pg_catalog pg_default_acl 292 pg_catalog pg_depend 293 pg_catalog pg_description 294 pg_catalog pg_enum 295 pg_catalog pg_event_trigger 296 pg_catalog pg_extension 297 pg_catalog pg_foreign_data_wrapper 298 pg_catalog pg_foreign_server 299 pg_catalog pg_foreign_table 300 pg_catalog pg_index 301 pg_catalog pg_indexes 302 pg_catalog pg_inherits 303 pg_catalog pg_language 304 pg_catalog pg_locks 305 pg_catalog pg_matviews 306 pg_catalog pg_namespace 307 pg_catalog pg_operator 308 pg_catalog pg_prepared_statements 309 pg_catalog pg_prepared_xacts 310 pg_catalog pg_proc 311 pg_catalog pg_range 312 pg_catalog pg_rewrite 313 pg_catalog pg_roles 314 pg_catalog pg_seclabel 315 pg_catalog pg_seclabels 316 pg_catalog pg_sequence 317 pg_catalog pg_settings 318 pg_catalog pg_shdepend 319 pg_catalog pg_shdescription 320 pg_catalog pg_shseclabel 321 pg_catalog pg_stat_activity 322 pg_catalog pg_tables 323 pg_catalog pg_tablespace 324 pg_catalog pg_trigger 325 pg_catalog pg_type 326 pg_catalog pg_user 327 pg_catalog pg_user_mapping 328 pg_catalog pg_views 329 pg_extension geography_columns 330 pg_extension geometry_columns 331 pg_extension spatial_ref_sys 332 333 statement ok 334 CREATE DATABASE other_db 335 336 statement ok 337 CREATE TABLE other_db.xyz (i INT) 338 339 statement ok 340 CREATE SEQUENCE other_db.seq 341 342 statement ok 343 CREATE VIEW other_db.abc AS SELECT i from other_db.xyz 344 345 statement ok 346 GRANT UPDATE ON other_db.xyz TO testuser 347 348 user testuser 349 350 # Check the output with the current database set to 'test' (the 351 # defaults in tests). This will make the tables in other_db invisible to 352 # a non-root user. 353 query T 354 SELECT table_name FROM information_schema.tables WHERE table_catalog = 'other_db' 355 ---- 356 357 # Check that the other_db tables become visible when a prefix is specified 358 query T 359 SELECT table_name FROM other_db.information_schema.tables WHERE table_catalog = 'other_db' AND table_schema = 'public' 360 ---- 361 xyz 362 363 # Check that one can see all tables with the empty prefix. 364 query T rowsort 365 SELECT table_name FROM "".information_schema.tables WHERE table_catalog = 'other_db' 366 ---- 367 backward_dependencies 368 builtin_functions 369 cluster_queries 370 cluster_sessions 371 cluster_settings 372 cluster_transactions 373 create_statements 374 create_type_statements 375 feature_usage 376 forward_dependencies 377 gossip_alerts 378 gossip_liveness 379 gossip_network 380 gossip_nodes 381 index_columns 382 jobs 383 kv_node_status 384 kv_store_status 385 leases 386 node_build_info 387 node_metrics 388 node_queries 389 node_runtime_info 390 node_sessions 391 node_statement_statistics 392 node_transactions 393 node_txn_stats 394 partitions 395 predefined_comments 396 ranges 397 ranges_no_leases 398 schema_changes 399 session_trace 400 session_variables 401 table_columns 402 table_indexes 403 tables 404 zones 405 administrable_role_authorizations 406 applicable_roles 407 check_constraints 408 column_privileges 409 columns 410 constraint_column_usage 411 enabled_roles 412 key_column_usage 413 parameters 414 referential_constraints 415 role_table_grants 416 routines 417 schema_privileges 418 schemata 419 sequences 420 statistics 421 table_constraints 422 table_privileges 423 tables 424 user_privileges 425 views 426 pg_aggregate 427 pg_am 428 pg_attrdef 429 pg_attribute 430 pg_auth_members 431 pg_authid 432 pg_available_extensions 433 pg_cast 434 pg_class 435 pg_collation 436 pg_constraint 437 pg_conversion 438 pg_database 439 pg_default_acl 440 pg_depend 441 pg_description 442 pg_enum 443 pg_event_trigger 444 pg_extension 445 pg_foreign_data_wrapper 446 pg_foreign_server 447 pg_foreign_table 448 pg_index 449 pg_indexes 450 pg_inherits 451 pg_language 452 pg_locks 453 pg_matviews 454 pg_namespace 455 pg_operator 456 pg_prepared_statements 457 pg_prepared_xacts 458 pg_proc 459 pg_range 460 pg_rewrite 461 pg_roles 462 pg_seclabel 463 pg_seclabels 464 pg_sequence 465 pg_settings 466 pg_shdepend 467 pg_shdescription 468 pg_shseclabel 469 pg_stat_activity 470 pg_tables 471 pg_tablespace 472 pg_trigger 473 pg_type 474 pg_user 475 pg_user_mapping 476 pg_views 477 geography_columns 478 geometry_columns 479 spatial_ref_sys 480 xyz 481 482 # Check that the other_db tables become visible to non-root when the current database is changed. 483 query T 484 SET DATABASE = other_db; SELECT table_name FROM information_schema.tables WHERE table_catalog = 'other_db' AND table_schema = 'public' 485 ---- 486 xyz 487 488 user root 489 490 # Check that root sees everything when there is no current database 491 query T 492 SET DATABASE = ''; SELECT table_name FROM information_schema.tables WHERE table_catalog = 'other_db' AND table_schema = 'public' 493 ---- 494 xyz 495 abc 496 497 # Check that root doesn't see other things when there is a current database 498 query T 499 SET DATABASE = test; SELECT table_name FROM information_schema.tables WHERE table_schema = 'other_db' 500 ---- 501 502 # Check that filtering works. 503 query T 504 SELECT table_name FROM other_db.information_schema.tables WHERE table_name > 't' ORDER BY 1 DESC 505 ---- 506 zones 507 xyz 508 views 509 user_privileges 510 tables 511 tables 512 table_privileges 513 table_indexes 514 table_constraints 515 table_columns 516 517 # Check that the metadata is reported properly. 518 query TTTTTI colnames 519 SELECT * FROM system.information_schema.tables 520 ---- 521 table_catalog table_schema table_name table_type is_insertable_into version 522 system crdb_internal backward_dependencies SYSTEM VIEW NO 1 523 system crdb_internal builtin_functions SYSTEM VIEW NO 1 524 system crdb_internal cluster_queries SYSTEM VIEW NO 1 525 system crdb_internal cluster_sessions SYSTEM VIEW NO 1 526 system crdb_internal cluster_settings SYSTEM VIEW NO 1 527 system crdb_internal cluster_transactions SYSTEM VIEW NO 1 528 system crdb_internal create_statements SYSTEM VIEW NO 1 529 system crdb_internal create_type_statements SYSTEM VIEW NO 1 530 system crdb_internal feature_usage SYSTEM VIEW NO 1 531 system crdb_internal forward_dependencies SYSTEM VIEW NO 1 532 system crdb_internal gossip_alerts SYSTEM VIEW NO 1 533 system crdb_internal gossip_liveness SYSTEM VIEW NO 1 534 system crdb_internal gossip_network SYSTEM VIEW NO 1 535 system crdb_internal gossip_nodes SYSTEM VIEW NO 1 536 system crdb_internal index_columns SYSTEM VIEW NO 1 537 system crdb_internal jobs SYSTEM VIEW NO 1 538 system crdb_internal kv_node_status SYSTEM VIEW NO 1 539 system crdb_internal kv_store_status SYSTEM VIEW NO 1 540 system crdb_internal leases SYSTEM VIEW NO 1 541 system crdb_internal node_build_info SYSTEM VIEW NO 1 542 system crdb_internal node_metrics SYSTEM VIEW NO 1 543 system crdb_internal node_queries SYSTEM VIEW NO 1 544 system crdb_internal node_runtime_info SYSTEM VIEW NO 1 545 system crdb_internal node_sessions SYSTEM VIEW NO 1 546 system crdb_internal node_statement_statistics SYSTEM VIEW NO 1 547 system crdb_internal node_transactions SYSTEM VIEW NO 1 548 system crdb_internal node_txn_stats SYSTEM VIEW NO 1 549 system crdb_internal partitions SYSTEM VIEW NO 1 550 system crdb_internal predefined_comments SYSTEM VIEW NO 1 551 system crdb_internal ranges SYSTEM VIEW NO 1 552 system crdb_internal ranges_no_leases SYSTEM VIEW NO 1 553 system crdb_internal schema_changes SYSTEM VIEW NO 1 554 system crdb_internal session_trace SYSTEM VIEW NO 1 555 system crdb_internal session_variables SYSTEM VIEW NO 1 556 system crdb_internal table_columns SYSTEM VIEW NO 1 557 system crdb_internal table_indexes SYSTEM VIEW NO 1 558 system crdb_internal tables SYSTEM VIEW NO 1 559 system crdb_internal zones SYSTEM VIEW NO 1 560 system information_schema administrable_role_authorizations SYSTEM VIEW NO 1 561 system information_schema applicable_roles SYSTEM VIEW NO 1 562 system information_schema check_constraints SYSTEM VIEW NO 1 563 system information_schema column_privileges SYSTEM VIEW NO 1 564 system information_schema columns SYSTEM VIEW NO 1 565 system information_schema constraint_column_usage SYSTEM VIEW NO 1 566 system information_schema enabled_roles SYSTEM VIEW NO 1 567 system information_schema key_column_usage SYSTEM VIEW NO 1 568 system information_schema parameters SYSTEM VIEW NO 1 569 system information_schema referential_constraints SYSTEM VIEW NO 1 570 system information_schema role_table_grants SYSTEM VIEW NO 1 571 system information_schema routines SYSTEM VIEW NO 1 572 system information_schema schema_privileges SYSTEM VIEW NO 1 573 system information_schema schemata SYSTEM VIEW NO 1 574 system information_schema sequences SYSTEM VIEW NO 1 575 system information_schema statistics SYSTEM VIEW NO 1 576 system information_schema table_constraints SYSTEM VIEW NO 1 577 system information_schema table_privileges SYSTEM VIEW NO 1 578 system information_schema tables SYSTEM VIEW NO 1 579 system information_schema user_privileges SYSTEM VIEW NO 1 580 system information_schema views SYSTEM VIEW NO 1 581 system pg_catalog pg_aggregate SYSTEM VIEW NO 1 582 system pg_catalog pg_am SYSTEM VIEW NO 1 583 system pg_catalog pg_attrdef SYSTEM VIEW NO 1 584 system pg_catalog pg_attribute SYSTEM VIEW NO 1 585 system pg_catalog pg_auth_members SYSTEM VIEW NO 1 586 system pg_catalog pg_authid SYSTEM VIEW NO 1 587 system pg_catalog pg_available_extensions SYSTEM VIEW NO 1 588 system pg_catalog pg_cast SYSTEM VIEW NO 1 589 system pg_catalog pg_class SYSTEM VIEW NO 1 590 system pg_catalog pg_collation SYSTEM VIEW NO 1 591 system pg_catalog pg_constraint SYSTEM VIEW NO 1 592 system pg_catalog pg_conversion SYSTEM VIEW NO 1 593 system pg_catalog pg_database SYSTEM VIEW NO 1 594 system pg_catalog pg_default_acl SYSTEM VIEW NO 1 595 system pg_catalog pg_depend SYSTEM VIEW NO 1 596 system pg_catalog pg_description SYSTEM VIEW NO 1 597 system pg_catalog pg_enum SYSTEM VIEW NO 1 598 system pg_catalog pg_event_trigger SYSTEM VIEW NO 1 599 system pg_catalog pg_extension SYSTEM VIEW NO 1 600 system pg_catalog pg_foreign_data_wrapper SYSTEM VIEW NO 1 601 system pg_catalog pg_foreign_server SYSTEM VIEW NO 1 602 system pg_catalog pg_foreign_table SYSTEM VIEW NO 1 603 system pg_catalog pg_index SYSTEM VIEW NO 1 604 system pg_catalog pg_indexes SYSTEM VIEW NO 1 605 system pg_catalog pg_inherits SYSTEM VIEW NO 1 606 system pg_catalog pg_language SYSTEM VIEW NO 1 607 system pg_catalog pg_locks SYSTEM VIEW NO 1 608 system pg_catalog pg_matviews SYSTEM VIEW NO 1 609 system pg_catalog pg_namespace SYSTEM VIEW NO 1 610 system pg_catalog pg_operator SYSTEM VIEW NO 1 611 system pg_catalog pg_prepared_statements SYSTEM VIEW NO 1 612 system pg_catalog pg_prepared_xacts SYSTEM VIEW NO 1 613 system pg_catalog pg_proc SYSTEM VIEW NO 1 614 system pg_catalog pg_range SYSTEM VIEW NO 1 615 system pg_catalog pg_rewrite SYSTEM VIEW NO 1 616 system pg_catalog pg_roles SYSTEM VIEW NO 1 617 system pg_catalog pg_seclabel SYSTEM VIEW NO 1 618 system pg_catalog pg_seclabels SYSTEM VIEW NO 1 619 system pg_catalog pg_sequence SYSTEM VIEW NO 1 620 system pg_catalog pg_settings SYSTEM VIEW NO 1 621 system pg_catalog pg_shdepend SYSTEM VIEW NO 1 622 system pg_catalog pg_shdescription SYSTEM VIEW NO 1 623 system pg_catalog pg_shseclabel SYSTEM VIEW NO 1 624 system pg_catalog pg_stat_activity SYSTEM VIEW NO 1 625 system pg_catalog pg_tables SYSTEM VIEW NO 1 626 system pg_catalog pg_tablespace SYSTEM VIEW NO 1 627 system pg_catalog pg_trigger SYSTEM VIEW NO 1 628 system pg_catalog pg_type SYSTEM VIEW NO 1 629 system pg_catalog pg_user SYSTEM VIEW NO 1 630 system pg_catalog pg_user_mapping SYSTEM VIEW NO 1 631 system pg_catalog pg_views SYSTEM VIEW NO 1 632 system pg_extension geography_columns SYSTEM VIEW NO 1 633 system pg_extension geometry_columns SYSTEM VIEW NO 1 634 system pg_extension spatial_ref_sys SYSTEM VIEW NO 1 635 system public namespace BASE TABLE YES 1 636 system public descriptor BASE TABLE YES 1 637 system public users BASE TABLE YES 1 638 system public zones BASE TABLE YES 1 639 system public settings BASE TABLE YES 1 640 system public tenants BASE TABLE YES 1 641 system public lease BASE TABLE YES 1 642 system public eventlog BASE TABLE YES 1 643 system public rangelog BASE TABLE YES 1 644 system public ui BASE TABLE YES 1 645 system public jobs BASE TABLE YES 1 646 system public web_sessions BASE TABLE YES 1 647 system public table_statistics BASE TABLE YES 1 648 system public locations BASE TABLE YES 1 649 system public role_members BASE TABLE YES 1 650 system public comments BASE TABLE YES 1 651 system public replication_constraint_stats BASE TABLE YES 1 652 system public replication_critical_localities BASE TABLE YES 1 653 system public replication_stats BASE TABLE YES 1 654 system public reports_meta BASE TABLE YES 1 655 system public namespace2 BASE TABLE YES 1 656 system public protected_ts_meta BASE TABLE YES 1 657 system public protected_ts_records BASE TABLE YES 1 658 system public role_options BASE TABLE YES 1 659 system public statement_bundle_chunks BASE TABLE YES 1 660 system public statement_diagnostics_requests BASE TABLE YES 1 661 system public statement_diagnostics BASE TABLE YES 1 662 663 statement ok 664 ALTER TABLE other_db.xyz ADD COLUMN j INT 665 666 query TTI colnames 667 SELECT table_catalog, table_name, version 668 FROM "".information_schema.tables 669 WHERE table_catalog != 'system' AND version > 1 AND table_schema = 'public' ORDER BY 1,2 670 ---- 671 table_catalog table_name version 672 other_db xyz 6 673 674 user testuser 675 676 # Check that another user cannot see other_db.adbc any more because they 677 # don't have privileges on it. 678 query TTTTTI colnames 679 SELECT * FROM other_db.information_schema.tables WHERE table_catalog = 'other_db' AND table_schema = 'public' 680 ---- 681 table_catalog table_schema table_name table_type is_insertable_into version 682 other_db public xyz BASE TABLE YES 6 683 684 685 user root 686 687 statement ok 688 GRANT SELECT ON other_db.abc TO testuser 689 690 user testuser 691 692 # Check the user can see the tables now that they have privilege. 693 query TTTTTI colnames 694 SELECT * FROM other_db.information_schema.tables WHERE table_catalog = 'other_db' AND table_schema = 'public' ORDER BY 1, 3 695 ---- 696 table_catalog table_schema table_name table_type is_insertable_into version 697 other_db public abc VIEW NO 2 698 other_db public xyz BASE TABLE YES 6 699 700 user root 701 702 statement ok 703 DROP DATABASE other_db CASCADE 704 705 statement ok 706 SET DATABASE = test 707 708 ## information_schema.table_constraints 709 ## information_schema.check_constraints 710 ## information_schema.constraint_column_usage 711 712 query TTTTTTTTT colnames 713 SELECT * 714 FROM system.information_schema.table_constraints 715 ORDER BY TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME 716 ---- 717 constraint_catalog constraint_schema constraint_name table_catalog table_schema table_name constraint_type is_deferrable initially_deferred 718 system public 630200280_24_1_not_null system public comments CHECK NO NO 719 system public 630200280_24_2_not_null system public comments CHECK NO NO 720 system public 630200280_24_3_not_null system public comments CHECK NO NO 721 system public 630200280_24_4_not_null system public comments CHECK NO NO 722 system public primary system public comments PRIMARY KEY NO NO 723 system public 630200280_3_1_not_null system public descriptor CHECK NO NO 724 system public primary system public descriptor PRIMARY KEY NO NO 725 system public 630200280_12_1_not_null system public eventlog CHECK NO NO 726 system public 630200280_12_2_not_null system public eventlog CHECK NO NO 727 system public 630200280_12_3_not_null system public eventlog CHECK NO NO 728 system public 630200280_12_4_not_null system public eventlog CHECK NO NO 729 system public 630200280_12_6_not_null system public eventlog CHECK NO NO 730 system public primary system public eventlog PRIMARY KEY NO NO 731 system public 630200280_15_1_not_null system public jobs CHECK NO NO 732 system public 630200280_15_2_not_null system public jobs CHECK NO NO 733 system public 630200280_15_3_not_null system public jobs CHECK NO NO 734 system public 630200280_15_4_not_null system public jobs CHECK NO NO 735 system public primary system public jobs PRIMARY KEY NO NO 736 system public 630200280_11_1_not_null system public lease CHECK NO NO 737 system public 630200280_11_2_not_null system public lease CHECK NO NO 738 system public 630200280_11_3_not_null system public lease CHECK NO NO 739 system public 630200280_11_4_not_null system public lease CHECK NO NO 740 system public primary system public lease PRIMARY KEY NO NO 741 system public 630200280_21_1_not_null system public locations CHECK NO NO 742 system public 630200280_21_2_not_null system public locations CHECK NO NO 743 system public 630200280_21_3_not_null system public locations CHECK NO NO 744 system public 630200280_21_4_not_null system public locations CHECK NO NO 745 system public primary system public locations PRIMARY KEY NO NO 746 system public 630200280_2_1_not_null system public namespace CHECK NO NO 747 system public 630200280_2_2_not_null system public namespace CHECK NO NO 748 system public primary system public namespace PRIMARY KEY NO NO 749 system public 630200280_30_1_not_null system public namespace2 CHECK NO NO 750 system public 630200280_30_2_not_null system public namespace2 CHECK NO NO 751 system public 630200280_30_3_not_null system public namespace2 CHECK NO NO 752 system public primary system public namespace2 PRIMARY KEY NO NO 753 system public 630200280_31_1_not_null system public protected_ts_meta CHECK NO NO 754 system public 630200280_31_2_not_null system public protected_ts_meta CHECK NO NO 755 system public 630200280_31_3_not_null system public protected_ts_meta CHECK NO NO 756 system public 630200280_31_4_not_null system public protected_ts_meta CHECK NO NO 757 system public 630200280_31_5_not_null system public protected_ts_meta CHECK NO NO 758 system public check_singleton system public protected_ts_meta CHECK NO NO 759 system public primary system public protected_ts_meta PRIMARY KEY NO NO 760 system public 630200280_32_1_not_null system public protected_ts_records CHECK NO NO 761 system public 630200280_32_2_not_null system public protected_ts_records CHECK NO NO 762 system public 630200280_32_3_not_null system public protected_ts_records CHECK NO NO 763 system public 630200280_32_5_not_null system public protected_ts_records CHECK NO NO 764 system public 630200280_32_6_not_null system public protected_ts_records CHECK NO NO 765 system public 630200280_32_7_not_null system public protected_ts_records CHECK NO NO 766 system public primary system public protected_ts_records PRIMARY KEY NO NO 767 system public 630200280_13_1_not_null system public rangelog CHECK NO NO 768 system public 630200280_13_2_not_null system public rangelog CHECK NO NO 769 system public 630200280_13_3_not_null system public rangelog CHECK NO NO 770 system public 630200280_13_4_not_null system public rangelog CHECK NO NO 771 system public 630200280_13_7_not_null system public rangelog CHECK NO NO 772 system public primary system public rangelog PRIMARY KEY NO NO 773 system public 630200280_25_1_not_null system public replication_constraint_stats CHECK NO NO 774 system public 630200280_25_2_not_null system public replication_constraint_stats CHECK NO NO 775 system public 630200280_25_3_not_null system public replication_constraint_stats CHECK NO NO 776 system public 630200280_25_4_not_null system public replication_constraint_stats CHECK NO NO 777 system public 630200280_25_5_not_null system public replication_constraint_stats CHECK NO NO 778 system public 630200280_25_7_not_null system public replication_constraint_stats CHECK NO NO 779 system public primary system public replication_constraint_stats PRIMARY KEY NO NO 780 system public 630200280_26_1_not_null system public replication_critical_localities CHECK NO NO 781 system public 630200280_26_2_not_null system public replication_critical_localities CHECK NO NO 782 system public 630200280_26_3_not_null system public replication_critical_localities CHECK NO NO 783 system public 630200280_26_4_not_null system public replication_critical_localities CHECK NO NO 784 system public 630200280_26_5_not_null system public replication_critical_localities CHECK NO NO 785 system public primary system public replication_critical_localities PRIMARY KEY NO NO 786 system public 630200280_27_1_not_null system public replication_stats CHECK NO NO 787 system public 630200280_27_2_not_null system public replication_stats CHECK NO NO 788 system public 630200280_27_3_not_null system public replication_stats CHECK NO NO 789 system public 630200280_27_4_not_null system public replication_stats CHECK NO NO 790 system public 630200280_27_5_not_null system public replication_stats CHECK NO NO 791 system public 630200280_27_6_not_null system public replication_stats CHECK NO NO 792 system public 630200280_27_7_not_null system public replication_stats CHECK NO NO 793 system public primary system public replication_stats PRIMARY KEY NO NO 794 system public 630200280_28_1_not_null system public reports_meta CHECK NO NO 795 system public 630200280_28_2_not_null system public reports_meta CHECK NO NO 796 system public primary system public reports_meta PRIMARY KEY NO NO 797 system public 630200280_23_1_not_null system public role_members CHECK NO NO 798 system public 630200280_23_2_not_null system public role_members CHECK NO NO 799 system public 630200280_23_3_not_null system public role_members CHECK NO NO 800 system public primary system public role_members PRIMARY KEY NO NO 801 system public 630200280_33_1_not_null system public role_options CHECK NO NO 802 system public 630200280_33_2_not_null system public role_options CHECK NO NO 803 system public primary system public role_options PRIMARY KEY NO NO 804 system public 630200280_6_1_not_null system public settings CHECK NO NO 805 system public 630200280_6_2_not_null system public settings CHECK NO NO 806 system public 630200280_6_3_not_null system public settings CHECK NO NO 807 system public primary system public settings PRIMARY KEY NO NO 808 system public 630200280_34_1_not_null system public statement_bundle_chunks CHECK NO NO 809 system public 630200280_34_3_not_null system public statement_bundle_chunks CHECK NO NO 810 system public primary system public statement_bundle_chunks PRIMARY KEY NO NO 811 system public 630200280_36_1_not_null system public statement_diagnostics CHECK NO NO 812 system public 630200280_36_2_not_null system public statement_diagnostics CHECK NO NO 813 system public 630200280_36_3_not_null system public statement_diagnostics CHECK NO NO 814 system public 630200280_36_4_not_null system public statement_diagnostics CHECK NO NO 815 system public primary system public statement_diagnostics PRIMARY KEY NO NO 816 system public 630200280_35_1_not_null system public statement_diagnostics_requests CHECK NO NO 817 system public 630200280_35_2_not_null system public statement_diagnostics_requests CHECK NO NO 818 system public 630200280_35_3_not_null system public statement_diagnostics_requests CHECK NO NO 819 system public 630200280_35_5_not_null system public statement_diagnostics_requests CHECK NO NO 820 system public primary system public statement_diagnostics_requests PRIMARY KEY NO NO 821 system public 630200280_20_1_not_null system public table_statistics CHECK NO NO 822 system public 630200280_20_2_not_null system public table_statistics CHECK NO NO 823 system public 630200280_20_4_not_null system public table_statistics CHECK NO NO 824 system public 630200280_20_5_not_null system public table_statistics CHECK NO NO 825 system public 630200280_20_6_not_null system public table_statistics CHECK NO NO 826 system public 630200280_20_7_not_null system public table_statistics CHECK NO NO 827 system public 630200280_20_8_not_null system public table_statistics CHECK NO NO 828 system public primary system public table_statistics PRIMARY KEY NO NO 829 system public 630200280_8_1_not_null system public tenants CHECK NO NO 830 system public 630200280_8_2_not_null system public tenants CHECK NO NO 831 system public primary system public tenants PRIMARY KEY NO NO 832 system public 630200280_14_1_not_null system public ui CHECK NO NO 833 system public 630200280_14_3_not_null system public ui CHECK NO NO 834 system public primary system public ui PRIMARY KEY NO NO 835 system public 630200280_4_1_not_null system public users CHECK NO NO 836 system public 630200280_4_3_not_null system public users CHECK NO NO 837 system public primary system public users PRIMARY KEY NO NO 838 system public 630200280_19_1_not_null system public web_sessions CHECK NO NO 839 system public 630200280_19_2_not_null system public web_sessions CHECK NO NO 840 system public 630200280_19_3_not_null system public web_sessions CHECK NO NO 841 system public 630200280_19_4_not_null system public web_sessions CHECK NO NO 842 system public 630200280_19_5_not_null system public web_sessions CHECK NO NO 843 system public 630200280_19_7_not_null system public web_sessions CHECK NO NO 844 system public primary system public web_sessions PRIMARY KEY NO NO 845 system public 630200280_5_1_not_null system public zones CHECK NO NO 846 system public primary system public zones PRIMARY KEY NO NO 847 848 query TTTT colnames 849 SELECT * 850 FROM system.information_schema.check_constraints 851 ORDER BY CONSTRAINT_CATALOG, CONSTRAINT_NAME 852 ---- 853 constraint_catalog constraint_schema constraint_name check_clause 854 system public 630200280_11_1_not_null descID IS NOT NULL 855 system public 630200280_11_2_not_null version IS NOT NULL 856 system public 630200280_11_3_not_null nodeID IS NOT NULL 857 system public 630200280_11_4_not_null expiration IS NOT NULL 858 system public 630200280_12_1_not_null timestamp IS NOT NULL 859 system public 630200280_12_2_not_null eventType IS NOT NULL 860 system public 630200280_12_3_not_null targetID IS NOT NULL 861 system public 630200280_12_4_not_null reportingID IS NOT NULL 862 system public 630200280_12_6_not_null uniqueID IS NOT NULL 863 system public 630200280_13_1_not_null timestamp IS NOT NULL 864 system public 630200280_13_2_not_null rangeID IS NOT NULL 865 system public 630200280_13_3_not_null storeID IS NOT NULL 866 system public 630200280_13_4_not_null eventType IS NOT NULL 867 system public 630200280_13_7_not_null uniqueID IS NOT NULL 868 system public 630200280_14_1_not_null key IS NOT NULL 869 system public 630200280_14_3_not_null lastUpdated IS NOT NULL 870 system public 630200280_15_1_not_null id IS NOT NULL 871 system public 630200280_15_2_not_null status IS NOT NULL 872 system public 630200280_15_3_not_null created IS NOT NULL 873 system public 630200280_15_4_not_null payload IS NOT NULL 874 system public 630200280_19_1_not_null id IS NOT NULL 875 system public 630200280_19_2_not_null hashedSecret IS NOT NULL 876 system public 630200280_19_3_not_null username IS NOT NULL 877 system public 630200280_19_4_not_null createdAt IS NOT NULL 878 system public 630200280_19_5_not_null expiresAt IS NOT NULL 879 system public 630200280_19_7_not_null lastUsedAt IS NOT NULL 880 system public 630200280_20_1_not_null tableID IS NOT NULL 881 system public 630200280_20_2_not_null statisticID IS NOT NULL 882 system public 630200280_20_4_not_null columnIDs IS NOT NULL 883 system public 630200280_20_5_not_null createdAt IS NOT NULL 884 system public 630200280_20_6_not_null rowCount IS NOT NULL 885 system public 630200280_20_7_not_null distinctCount IS NOT NULL 886 system public 630200280_20_8_not_null nullCount IS NOT NULL 887 system public 630200280_21_1_not_null localityKey IS NOT NULL 888 system public 630200280_21_2_not_null localityValue IS NOT NULL 889 system public 630200280_21_3_not_null latitude IS NOT NULL 890 system public 630200280_21_4_not_null longitude IS NOT NULL 891 system public 630200280_23_1_not_null role IS NOT NULL 892 system public 630200280_23_2_not_null member IS NOT NULL 893 system public 630200280_23_3_not_null isAdmin IS NOT NULL 894 system public 630200280_24_1_not_null type IS NOT NULL 895 system public 630200280_24_2_not_null object_id IS NOT NULL 896 system public 630200280_24_3_not_null sub_id IS NOT NULL 897 system public 630200280_24_4_not_null comment IS NOT NULL 898 system public 630200280_25_1_not_null zone_id IS NOT NULL 899 system public 630200280_25_2_not_null subzone_id IS NOT NULL 900 system public 630200280_25_3_not_null type IS NOT NULL 901 system public 630200280_25_4_not_null config IS NOT NULL 902 system public 630200280_25_5_not_null report_id IS NOT NULL 903 system public 630200280_25_7_not_null violating_ranges IS NOT NULL 904 system public 630200280_26_1_not_null zone_id IS NOT NULL 905 system public 630200280_26_2_not_null subzone_id IS NOT NULL 906 system public 630200280_26_3_not_null locality IS NOT NULL 907 system public 630200280_26_4_not_null report_id IS NOT NULL 908 system public 630200280_26_5_not_null at_risk_ranges IS NOT NULL 909 system public 630200280_27_1_not_null zone_id IS NOT NULL 910 system public 630200280_27_2_not_null subzone_id IS NOT NULL 911 system public 630200280_27_3_not_null report_id IS NOT NULL 912 system public 630200280_27_4_not_null total_ranges IS NOT NULL 913 system public 630200280_27_5_not_null unavailable_ranges IS NOT NULL 914 system public 630200280_27_6_not_null under_replicated_ranges IS NOT NULL 915 system public 630200280_27_7_not_null over_replicated_ranges IS NOT NULL 916 system public 630200280_28_1_not_null id IS NOT NULL 917 system public 630200280_28_2_not_null generated IS NOT NULL 918 system public 630200280_2_1_not_null parentID IS NOT NULL 919 system public 630200280_2_2_not_null name IS NOT NULL 920 system public 630200280_30_1_not_null parentID IS NOT NULL 921 system public 630200280_30_2_not_null parentSchemaID IS NOT NULL 922 system public 630200280_30_3_not_null name IS NOT NULL 923 system public 630200280_31_1_not_null singleton IS NOT NULL 924 system public 630200280_31_2_not_null version IS NOT NULL 925 system public 630200280_31_3_not_null num_records IS NOT NULL 926 system public 630200280_31_4_not_null num_spans IS NOT NULL 927 system public 630200280_31_5_not_null total_bytes IS NOT NULL 928 system public 630200280_32_1_not_null id IS NOT NULL 929 system public 630200280_32_2_not_null ts IS NOT NULL 930 system public 630200280_32_3_not_null meta_type IS NOT NULL 931 system public 630200280_32_5_not_null num_spans IS NOT NULL 932 system public 630200280_32_6_not_null spans IS NOT NULL 933 system public 630200280_32_7_not_null verified IS NOT NULL 934 system public 630200280_33_1_not_null username IS NOT NULL 935 system public 630200280_33_2_not_null option IS NOT NULL 936 system public 630200280_34_1_not_null id IS NOT NULL 937 system public 630200280_34_3_not_null data IS NOT NULL 938 system public 630200280_35_1_not_null id IS NOT NULL 939 system public 630200280_35_2_not_null completed IS NOT NULL 940 system public 630200280_35_3_not_null statement_fingerprint IS NOT NULL 941 system public 630200280_35_5_not_null requested_at IS NOT NULL 942 system public 630200280_36_1_not_null id IS NOT NULL 943 system public 630200280_36_2_not_null statement_fingerprint IS NOT NULL 944 system public 630200280_36_3_not_null statement IS NOT NULL 945 system public 630200280_36_4_not_null collected_at IS NOT NULL 946 system public 630200280_3_1_not_null id IS NOT NULL 947 system public 630200280_4_1_not_null username IS NOT NULL 948 system public 630200280_4_3_not_null isRole IS NOT NULL 949 system public 630200280_5_1_not_null id IS NOT NULL 950 system public 630200280_6_1_not_null name IS NOT NULL 951 system public 630200280_6_2_not_null value IS NOT NULL 952 system public 630200280_6_3_not_null lastUpdated IS NOT NULL 953 system public 630200280_8_1_not_null id IS NOT NULL 954 system public 630200280_8_2_not_null active IS NOT NULL 955 system public check_singleton ((singleton)) 956 957 query TTTTTTT colnames 958 SELECT * 959 FROM system.information_schema.constraint_column_usage 960 ORDER BY TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME 961 ---- 962 table_catalog table_schema table_name column_name constraint_catalog constraint_schema constraint_name 963 system public comments object_id system public primary 964 system public comments sub_id system public primary 965 system public comments type system public primary 966 system public descriptor id system public primary 967 system public eventlog timestamp system public primary 968 system public eventlog uniqueID system public primary 969 system public jobs id system public primary 970 system public lease descID system public primary 971 system public lease expiration system public primary 972 system public lease nodeID system public primary 973 system public lease version system public primary 974 system public locations localityKey system public primary 975 system public locations localityValue system public primary 976 system public namespace name system public primary 977 system public namespace parentID system public primary 978 system public namespace2 name system public primary 979 system public namespace2 parentID system public primary 980 system public namespace2 parentSchemaID system public primary 981 system public protected_ts_meta singleton system public check_singleton 982 system public protected_ts_meta singleton system public primary 983 system public protected_ts_records id system public primary 984 system public rangelog timestamp system public primary 985 system public rangelog uniqueID system public primary 986 system public replication_constraint_stats config system public primary 987 system public replication_constraint_stats subzone_id system public primary 988 system public replication_constraint_stats type system public primary 989 system public replication_constraint_stats zone_id system public primary 990 system public replication_critical_localities locality system public primary 991 system public replication_critical_localities subzone_id system public primary 992 system public replication_critical_localities zone_id system public primary 993 system public replication_stats subzone_id system public primary 994 system public replication_stats zone_id system public primary 995 system public reports_meta id system public primary 996 system public role_members member system public primary 997 system public role_members role system public primary 998 system public role_options option system public primary 999 system public role_options username system public primary 1000 system public settings name system public primary 1001 system public statement_bundle_chunks id system public primary 1002 system public statement_diagnostics id system public primary 1003 system public statement_diagnostics_requests id system public primary 1004 system public table_statistics statisticID system public primary 1005 system public table_statistics tableID system public primary 1006 system public tenants id system public primary 1007 system public ui key system public primary 1008 system public users username system public primary 1009 system public web_sessions id system public primary 1010 system public zones id system public primary 1011 1012 statement ok 1013 CREATE DATABASE constraint_db 1014 1015 statement ok 1016 CREATE TABLE constraint_db.t1 ( 1017 p FLOAT PRIMARY KEY, 1018 a INT UNIQUE CHECK (a > 4), 1019 CONSTRAINT c2 CHECK (a < 99) 1020 ) 1021 1022 statement ok 1023 CREATE TABLE constraint_db.t2 ( 1024 t1_ID INT, 1025 CONSTRAINT fk FOREIGN KEY (t1_ID) REFERENCES constraint_db.t1(a), 1026 INDEX (t1_ID) 1027 ) 1028 1029 statement ok 1030 SET DATABASE = constraint_db 1031 1032 query TTTTTTTTT colnames 1033 SELECT * 1034 FROM information_schema.table_constraints 1035 ORDER BY TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME 1036 ---- 1037 constraint_catalog constraint_schema constraint_name table_catalog table_schema table_name constraint_type is_deferrable initially_deferred 1038 constraint_db public 541687103_59_1_not_null constraint_db public t1 CHECK NO NO 1039 constraint_db public c2 constraint_db public t1 CHECK NO NO 1040 constraint_db public check_a constraint_db public t1 CHECK NO NO 1041 constraint_db public primary constraint_db public t1 PRIMARY KEY NO NO 1042 constraint_db public t1_a_key constraint_db public t1 UNIQUE NO NO 1043 constraint_db public 541687103_60_2_not_null constraint_db public t2 CHECK NO NO 1044 constraint_db public fk constraint_db public t2 FOREIGN KEY NO NO 1045 1046 query TTTT colnames 1047 SELECT * 1048 FROM information_schema.check_constraints 1049 ORDER BY CONSTRAINT_CATALOG, CONSTRAINT_NAME 1050 ---- 1051 constraint_catalog constraint_schema constraint_name check_clause 1052 constraint_db public 541687103_59_1_not_null p IS NOT NULL 1053 constraint_db public c2 ((a < 99:::INT8)) 1054 constraint_db public check_a ((a > 4:::INT8)) 1055 1056 query TTTTTTT colnames 1057 SELECT * 1058 FROM information_schema.constraint_column_usage 1059 WHERE constraint_catalog = 'constraint_db' 1060 ORDER BY TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME 1061 ---- 1062 table_catalog table_schema table_name column_name constraint_catalog constraint_schema constraint_name 1063 constraint_db public t1 a constraint_db public c2 1064 constraint_db public t1 a constraint_db public check_a 1065 constraint_db public t1 a constraint_db public fk 1066 constraint_db public t1 a constraint_db public t1_a_key 1067 constraint_db public t1 p constraint_db public primary 1068 1069 # Query issued by jOOQ in PostgresDatabase.loadCheckConstraints. 1070 query TTTT colnames 1071 SELECT tc.table_schema, tc.table_name, cc.constraint_name, cc.check_clause 1072 FROM information_schema.table_constraints AS tc 1073 JOIN information_schema.check_constraints AS cc 1074 USING (constraint_catalog, constraint_schema, constraint_name) 1075 WHERE tc.table_schema in ('public') 1076 ORDER BY tc.table_schema, tc.table_name, cc.constraint_name 1077 ---- 1078 table_schema table_name constraint_name check_clause 1079 public t1 541687103_59_1_not_null p IS NOT NULL 1080 public t1 c2 ((a < 99:::INT8)) 1081 public t1 check_a ((a > 4:::INT8)) 1082 1083 statement ok 1084 DROP DATABASE constraint_db CASCADE 1085 1086 ## information_schema.columns 1087 1088 query TTTTI colnames 1089 SELECT table_catalog, table_schema, table_name, column_name, ordinal_position 1090 FROM system.information_schema.columns 1091 WHERE table_schema != 'information_schema' AND table_schema != 'pg_catalog' AND table_schema != 'crdb_internal' 1092 ORDER BY 3,4 1093 ---- 1094 table_catalog table_schema table_name column_name ordinal_position 1095 system public comments comment 4 1096 system public comments object_id 2 1097 system public comments sub_id 3 1098 system public comments type 1 1099 system public descriptor descriptor 2 1100 system public descriptor id 1 1101 system public eventlog eventType 2 1102 system public eventlog info 5 1103 system public eventlog reportingID 4 1104 system public eventlog targetID 3 1105 system public eventlog timestamp 1 1106 system public eventlog uniqueID 6 1107 system pg_extension geography_columns coord_dimension 5 1108 system pg_extension geography_columns f_geography_column 4 1109 system pg_extension geography_columns f_table_catalog 1 1110 system pg_extension geography_columns f_table_name 3 1111 system pg_extension geography_columns f_table_schema 2 1112 system pg_extension geography_columns srid 6 1113 system pg_extension geography_columns type 7 1114 system pg_extension geometry_columns coord_dimension 5 1115 system pg_extension geometry_columns f_geometry_column 4 1116 system pg_extension geometry_columns f_table_catalog 1 1117 system pg_extension geometry_columns f_table_name 3 1118 system pg_extension geometry_columns f_table_schema 2 1119 system pg_extension geometry_columns srid 6 1120 system pg_extension geometry_columns type 7 1121 system public jobs created 3 1122 system public jobs created_by_id 7 1123 system public jobs created_by_type 6 1124 system public jobs id 1 1125 system public jobs payload 4 1126 system public jobs progress 5 1127 system public jobs status 2 1128 system public lease descID 1 1129 system public lease expiration 4 1130 system public lease nodeID 3 1131 system public lease version 2 1132 system public locations latitude 3 1133 system public locations localityKey 1 1134 system public locations localityValue 2 1135 system public locations longitude 4 1136 system public namespace id 3 1137 system public namespace name 2 1138 system public namespace parentID 1 1139 system public namespace2 id 4 1140 system public namespace2 name 3 1141 system public namespace2 parentID 1 1142 system public namespace2 parentSchemaID 2 1143 system public protected_ts_meta num_records 3 1144 system public protected_ts_meta num_spans 4 1145 system public protected_ts_meta singleton 1 1146 system public protected_ts_meta total_bytes 5 1147 system public protected_ts_meta version 2 1148 system public protected_ts_records id 1 1149 system public protected_ts_records meta 4 1150 system public protected_ts_records meta_type 3 1151 system public protected_ts_records num_spans 5 1152 system public protected_ts_records spans 6 1153 system public protected_ts_records ts 2 1154 system public protected_ts_records verified 7 1155 system public rangelog eventType 4 1156 system public rangelog info 6 1157 system public rangelog otherRangeID 5 1158 system public rangelog rangeID 2 1159 system public rangelog storeID 3 1160 system public rangelog timestamp 1 1161 system public rangelog uniqueID 7 1162 system public replication_constraint_stats config 4 1163 system public replication_constraint_stats report_id 5 1164 system public replication_constraint_stats subzone_id 2 1165 system public replication_constraint_stats type 3 1166 system public replication_constraint_stats violating_ranges 7 1167 system public replication_constraint_stats violation_start 6 1168 system public replication_constraint_stats zone_id 1 1169 system public replication_critical_localities at_risk_ranges 5 1170 system public replication_critical_localities locality 3 1171 system public replication_critical_localities report_id 4 1172 system public replication_critical_localities subzone_id 2 1173 system public replication_critical_localities zone_id 1 1174 system public replication_stats over_replicated_ranges 7 1175 system public replication_stats report_id 3 1176 system public replication_stats subzone_id 2 1177 system public replication_stats total_ranges 4 1178 system public replication_stats unavailable_ranges 5 1179 system public replication_stats under_replicated_ranges 6 1180 system public replication_stats zone_id 1 1181 system public reports_meta generated 2 1182 system public reports_meta id 1 1183 system public role_members isAdmin 3 1184 system public role_members member 2 1185 system public role_members role 1 1186 system public role_options option 2 1187 system public role_options username 1 1188 system public role_options value 3 1189 system public settings lastUpdated 3 1190 system public settings name 1 1191 system public settings value 2 1192 system public settings valueType 4 1193 system pg_extension spatial_ref_sys auth_name 2 1194 system pg_extension spatial_ref_sys auth_srid 3 1195 system pg_extension spatial_ref_sys proj4text 5 1196 system pg_extension spatial_ref_sys srid 1 1197 system pg_extension spatial_ref_sys srtext 4 1198 system public statement_bundle_chunks data 3 1199 system public statement_bundle_chunks description 2 1200 system public statement_bundle_chunks id 1 1201 system public statement_diagnostics bundle_chunks 6 1202 system public statement_diagnostics collected_at 4 1203 system public statement_diagnostics error 7 1204 system public statement_diagnostics id 1 1205 system public statement_diagnostics statement 3 1206 system public statement_diagnostics statement_fingerprint 2 1207 system public statement_diagnostics trace 5 1208 system public statement_diagnostics_requests completed 2 1209 system public statement_diagnostics_requests id 1 1210 system public statement_diagnostics_requests requested_at 5 1211 system public statement_diagnostics_requests statement_diagnostics_id 4 1212 system public statement_diagnostics_requests statement_fingerprint 3 1213 system public table_statistics columnIDs 4 1214 system public table_statistics createdAt 5 1215 system public table_statistics distinctCount 7 1216 system public table_statistics histogram 9 1217 system public table_statistics name 3 1218 system public table_statistics nullCount 8 1219 system public table_statistics rowCount 6 1220 system public table_statistics statisticID 2 1221 system public table_statistics tableID 1 1222 system public tenants active 2 1223 system public tenants id 1 1224 system public tenants info 3 1225 system public ui key 1 1226 system public ui lastUpdated 3 1227 system public ui value 2 1228 system public users hashedPassword 2 1229 system public users isRole 3 1230 system public users username 1 1231 system public web_sessions auditInfo 8 1232 system public web_sessions createdAt 4 1233 system public web_sessions expiresAt 5 1234 system public web_sessions hashedSecret 2 1235 system public web_sessions id 1 1236 system public web_sessions lastUsedAt 7 1237 system public web_sessions revokedAt 6 1238 system public web_sessions username 3 1239 system public zones config 2 1240 system public zones id 1 1241 1242 statement ok 1243 SET DATABASE = test 1244 1245 statement ok 1246 CREATE TABLE with_defaults (a INT DEFAULT 9, b STRING DEFAULT 'default', c INT, d STRING) 1247 1248 query TTT colnames 1249 SELECT table_name, column_name, column_default 1250 FROM information_schema.columns 1251 WHERE table_schema = 'public' AND table_name = 'with_defaults' 1252 ---- 1253 table_name column_name column_default 1254 with_defaults a 9:::INT8 1255 with_defaults b 'default':::STRING 1256 with_defaults c NULL 1257 with_defaults d NULL 1258 with_defaults rowid unique_rowid() 1259 1260 statement ok 1261 DROP TABLE with_defaults 1262 1263 statement ok 1264 CREATE TABLE nullability (a INT NOT NULL, b STRING NOT NULL, c INT, d STRING) 1265 1266 query TTT colnames 1267 SELECT table_name, column_name, is_nullable 1268 FROM information_schema.columns 1269 WHERE table_schema = 'public' AND table_name = 'nullability' 1270 ---- 1271 table_name column_name is_nullable 1272 nullability a NO 1273 nullability b NO 1274 nullability c YES 1275 nullability d YES 1276 nullability rowid NO 1277 1278 statement ok 1279 DROP TABLE nullability 1280 1281 statement ok 1282 CREATE TABLE data_types ( 1283 a INT, 1284 a2 INT2, 1285 a4 INT4, 1286 a8 INT8, 1287 b FLOAT, 1288 b4 FLOAT4, 1289 br REAL, 1290 c DECIMAL, 1291 cp DECIMAL(3), 1292 cps DECIMAL(3,2), 1293 d STRING, 1294 dl STRING COLLATE en, 1295 dc CHAR, 1296 dc2 CHAR(2), 1297 dv VARCHAR, 1298 dv2 VARCHAR(2), 1299 dq "char", 1300 e BYTES, 1301 f TIMESTAMP, 1302 f6 TIMESTAMP(6), 1303 g TIMESTAMPTZ, 1304 g6 TIMESTAMPTZ(6), 1305 h BIT, 1306 h2 BIT(2), 1307 hv VARBIT, 1308 hv2 VARBIT(2), 1309 i INTERVAL, 1310 j BOOL, 1311 k OID, 1312 k2 REGCLASS, 1313 k3 REGNAMESPACE, 1314 k4 REGPROC, 1315 k5 REGPROCEDURE, 1316 k6 REGTYPE, 1317 l UUID, 1318 m INT2[], 1319 m2 STRING[], 1320 m3 DECIMAL(3, 2)[], 1321 m4 VARCHAR(2)[] COLLATE en, 1322 n INET, 1323 o TIME, 1324 o6 TIME(6), 1325 p JSONB, 1326 q NAME 1327 ) 1328 1329 query TTTTTTTTTT colnames 1330 SELECT table_name, column_name, data_type, crdb_sql_type, udt_catalog, udt_schema, udt_name, collation_catalog, collation_schema, collation_name 1331 FROM information_schema.columns 1332 WHERE table_schema = 'public' AND table_name = 'data_types' 1333 ---- 1334 table_name column_name data_type crdb_sql_type udt_catalog udt_schema udt_name collation_catalog collation_schema collation_name 1335 data_types a bigint INT8 test pg_catalog int8 NULL NULL NULL 1336 data_types a2 smallint INT2 test pg_catalog int2 NULL NULL NULL 1337 data_types a4 integer INT4 test pg_catalog int4 NULL NULL NULL 1338 data_types a8 bigint INT8 test pg_catalog int8 NULL NULL NULL 1339 data_types b double precision FLOAT8 test pg_catalog float8 NULL NULL NULL 1340 data_types b4 real FLOAT4 test pg_catalog float4 NULL NULL NULL 1341 data_types br real FLOAT4 test pg_catalog float4 NULL NULL NULL 1342 data_types c numeric DECIMAL test pg_catalog numeric NULL NULL NULL 1343 data_types cp numeric DECIMAL(3) test pg_catalog numeric NULL NULL NULL 1344 data_types cps numeric DECIMAL(3,2) test pg_catalog numeric NULL NULL NULL 1345 data_types d text STRING test pg_catalog text NULL NULL NULL 1346 data_types dl text STRING COLLATE en test pg_catalog text test pg_catalog en 1347 data_types dc character CHAR test pg_catalog bpchar NULL NULL NULL 1348 data_types dc2 character CHAR(2) test pg_catalog bpchar NULL NULL NULL 1349 data_types dv character varying VARCHAR test pg_catalog varchar NULL NULL NULL 1350 data_types dv2 character varying VARCHAR(2) test pg_catalog varchar NULL NULL NULL 1351 data_types dq "char" "char" test pg_catalog char NULL NULL NULL 1352 data_types e bytea BYTES test pg_catalog bytea NULL NULL NULL 1353 data_types f timestamp without time zone TIMESTAMP test pg_catalog timestamp NULL NULL NULL 1354 data_types f6 timestamp without time zone TIMESTAMP(6) test pg_catalog timestamp NULL NULL NULL 1355 data_types g timestamp with time zone TIMESTAMPTZ test pg_catalog timestamptz NULL NULL NULL 1356 data_types g6 timestamp with time zone TIMESTAMPTZ(6) test pg_catalog timestamptz NULL NULL NULL 1357 data_types h bit BIT test pg_catalog bit NULL NULL NULL 1358 data_types h2 bit BIT(2) test pg_catalog bit NULL NULL NULL 1359 data_types hv bit varying VARBIT test pg_catalog varbit NULL NULL NULL 1360 data_types hv2 bit varying VARBIT(2) test pg_catalog varbit NULL NULL NULL 1361 data_types i interval INTERVAL test pg_catalog interval NULL NULL NULL 1362 data_types j boolean BOOL test pg_catalog bool NULL NULL NULL 1363 data_types k oid OID test pg_catalog oid NULL NULL NULL 1364 data_types k2 regclass REGCLASS test pg_catalog regclass NULL NULL NULL 1365 data_types k3 regnamespace REGNAMESPACE test pg_catalog regnamespace NULL NULL NULL 1366 data_types k4 regproc REGPROC test pg_catalog regproc NULL NULL NULL 1367 data_types k5 regprocedure REGPROCEDURE test pg_catalog regprocedure NULL NULL NULL 1368 data_types k6 regtype REGTYPE test pg_catalog regtype NULL NULL NULL 1369 data_types l uuid UUID test pg_catalog uuid NULL NULL NULL 1370 data_types m ARRAY INT2[] test pg_catalog _int2 NULL NULL NULL 1371 data_types m2 ARRAY STRING[] test pg_catalog _text NULL NULL NULL 1372 data_types m3 ARRAY DECIMAL(3,2)[] test pg_catalog _numeric NULL NULL NULL 1373 data_types m4 ARRAY VARCHAR(2)[] COLLATE en test pg_catalog _varchar NULL NULL NULL 1374 data_types n inet INET test pg_catalog inet NULL NULL NULL 1375 data_types o time without time zone TIME test pg_catalog time NULL NULL NULL 1376 data_types o6 time without time zone TIME(6) test pg_catalog time NULL NULL NULL 1377 data_types p jsonb JSONB test pg_catalog jsonb NULL NULL NULL 1378 data_types q name NAME test pg_catalog name NULL NULL NULL 1379 data_types rowid bigint INT8 test pg_catalog int8 NULL NULL NULL 1380 1381 statement ok 1382 DROP TABLE data_types 1383 1384 statement ok 1385 CREATE TABLE computed (a INT, b INT AS (a + 1) STORED) 1386 1387 query TTTT colnames 1388 SELECT column_name, is_generated, generation_expression, is_updatable 1389 FROM information_schema.columns 1390 WHERE table_schema = 'public' AND table_name = 'computed' 1391 ---- 1392 column_name is_generated generation_expression is_updatable 1393 a NO · YES 1394 b YES a + 1:::INT8 NO 1395 rowid NO · YES 1396 1397 statement ok 1398 CREATE TABLE char_len ( 1399 a INT, b INT2, c INT4, 1400 d STRING, e STRING(12), 1401 dc CHAR, ec CHAR(12), 1402 dv VARCHAR, ev VARCHAR(12), 1403 dq "char", 1404 f FLOAT, 1405 g BIT, h BIT(12), 1406 i VARBIT, j VARBIT(12)) 1407 1408 query TTII colnames 1409 SELECT table_name, column_name, character_maximum_length, character_octet_length 1410 FROM information_schema.columns 1411 WHERE table_schema = 'public' AND table_name = 'char_len' 1412 ---- 1413 table_name column_name character_maximum_length character_octet_length 1414 char_len a NULL NULL 1415 char_len b NULL NULL 1416 char_len c NULL NULL 1417 char_len d NULL NULL 1418 char_len e 12 48 1419 char_len dc 1 4 1420 char_len ec 12 48 1421 char_len dv NULL NULL 1422 char_len ev 12 48 1423 char_len dq NULL NULL 1424 char_len f NULL NULL 1425 char_len g 1 NULL 1426 char_len h 12 NULL 1427 char_len i NULL NULL 1428 char_len j 12 NULL 1429 char_len rowid NULL NULL 1430 1431 statement ok 1432 DROP TABLE char_len 1433 1434 statement ok 1435 CREATE TABLE num_prec (a INT, b FLOAT, c FLOAT(23), d DECIMAL, e DECIMAL(12), f DECIMAL(12, 6), g BOOLEAN) 1436 1437 query TTIIII colnames 1438 SELECT table_name, column_name, numeric_precision, numeric_precision_radix, numeric_scale, datetime_precision 1439 FROM information_schema.columns 1440 WHERE table_schema = 'public' AND table_name = 'num_prec' 1441 ---- 1442 table_name column_name numeric_precision numeric_precision_radix numeric_scale datetime_precision 1443 num_prec a 64 2 0 NULL 1444 num_prec b 53 2 NULL NULL 1445 num_prec c 24 2 NULL NULL 1446 num_prec d NULL 10 NULL NULL 1447 num_prec e 12 10 0 NULL 1448 num_prec f 12 10 6 NULL 1449 num_prec g NULL NULL NULL NULL 1450 num_prec rowid 64 2 0 NULL 1451 1452 statement ok 1453 DROP TABLE num_prec 1454 1455 ## information_schema.key_column_usage 1456 ## information_schema.referential_constraints 1457 1458 statement ok 1459 CREATE DATABASE constraint_column 1460 1461 statement ok 1462 CREATE TABLE constraint_column.t1 ( 1463 p FLOAT PRIMARY KEY, 1464 a INT UNIQUE, 1465 b INT, 1466 c INT CHECK(c > 0), 1467 UNIQUE INDEX index_key(b, c) 1468 ) 1469 1470 statement ok 1471 CREATE TABLE constraint_column.t2 ( 1472 t1_ID INT PRIMARY KEY, 1473 CONSTRAINT fk FOREIGN KEY (t1_ID) REFERENCES constraint_column.t1(a) ON DELETE RESTRICT 1474 ) 1475 1476 statement ok 1477 CREATE TABLE constraint_column.t3 ( 1478 a INT, 1479 b INT, 1480 CONSTRAINT fk2 FOREIGN KEY (a, b) REFERENCES constraint_column.t1(b, c) ON UPDATE CASCADE, 1481 INDEX (a, b) 1482 ) 1483 1484 statement ok 1485 SET DATABASE = constraint_column 1486 1487 query TTTTTTTII colnames 1488 SELECT * FROM information_schema.key_column_usage WHERE constraint_schema = 'public' ORDER BY TABLE_NAME, CONSTRAINT_NAME, ORDINAL_POSITION 1489 ---- 1490 constraint_catalog constraint_schema constraint_name table_catalog table_schema table_name column_name ordinal_position position_in_unique_constraint 1491 constraint_column public index_key constraint_column public t1 b 1 NULL 1492 constraint_column public index_key constraint_column public t1 c 2 NULL 1493 constraint_column public primary constraint_column public t1 p 1 NULL 1494 constraint_column public t1_a_key constraint_column public t1 a 1 NULL 1495 constraint_column public fk constraint_column public t2 t1_id 1 1 1496 constraint_column public primary constraint_column public t2 t1_id 1 NULL 1497 constraint_column public fk2 constraint_column public t3 a 1 1 1498 constraint_column public fk2 constraint_column public t3 b 2 2 1499 1500 query TTTTTTTTTTT colnames 1501 SELECT * FROM information_schema.referential_constraints WHERE constraint_schema = 'public' ORDER BY TABLE_NAME, CONSTRAINT_NAME 1502 ---- 1503 constraint_catalog constraint_schema constraint_name unique_constraint_catalog unique_constraint_schema unique_constraint_name match_option update_rule delete_rule table_name referenced_table_name 1504 constraint_column public fk constraint_column public t1_a_key NONE NO ACTION RESTRICT t2 t1 1505 constraint_column public fk2 constraint_column public index_key NONE CASCADE NO ACTION t3 t1 1506 1507 statement ok 1508 DROP DATABASE constraint_column CASCADE 1509 1510 ## information_schema.schema_privileges 1511 1512 statement ok 1513 CREATE DATABASE other_db; SET DATABASE = other_db 1514 1515 query TTTTT colnames 1516 SELECT * FROM information_schema.schema_privileges 1517 ---- 1518 grantee table_catalog table_schema privilege_type is_grantable 1519 admin other_db crdb_internal ALL NULL 1520 root other_db crdb_internal ALL NULL 1521 admin other_db information_schema ALL NULL 1522 root other_db information_schema ALL NULL 1523 admin other_db pg_catalog ALL NULL 1524 root other_db pg_catalog ALL NULL 1525 admin other_db pg_extension ALL NULL 1526 root other_db pg_extension ALL NULL 1527 admin other_db public ALL NULL 1528 root other_db public ALL NULL 1529 1530 statement ok 1531 GRANT SELECT ON DATABASE other_db TO testuser 1532 1533 query TTTTT colnames 1534 SELECT * FROM information_schema.schema_privileges 1535 ---- 1536 grantee table_catalog table_schema privilege_type is_grantable 1537 admin other_db crdb_internal ALL NULL 1538 root other_db crdb_internal ALL NULL 1539 testuser other_db crdb_internal SELECT NULL 1540 admin other_db information_schema ALL NULL 1541 root other_db information_schema ALL NULL 1542 testuser other_db information_schema SELECT NULL 1543 admin other_db pg_catalog ALL NULL 1544 root other_db pg_catalog ALL NULL 1545 testuser other_db pg_catalog SELECT NULL 1546 admin other_db pg_extension ALL NULL 1547 root other_db pg_extension ALL NULL 1548 testuser other_db pg_extension SELECT NULL 1549 admin other_db public ALL NULL 1550 root other_db public ALL NULL 1551 testuser other_db public SELECT NULL 1552 1553 ## information_schema.table_privileges and information_schema.role_table_grants 1554 1555 # root can see everything 1556 query TTTTTTTT colnames rowsort 1557 SELECT * FROM system.information_schema.table_privileges ORDER BY table_schema, table_name, table_schema, grantee, privilege_type 1558 ---- 1559 grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy 1560 NULL public system crdb_internal backward_dependencies SELECT NULL YES 1561 NULL public system crdb_internal builtin_functions SELECT NULL YES 1562 NULL public system crdb_internal cluster_queries SELECT NULL YES 1563 NULL public system crdb_internal cluster_sessions SELECT NULL YES 1564 NULL public system crdb_internal cluster_settings SELECT NULL YES 1565 NULL public system crdb_internal cluster_transactions SELECT NULL YES 1566 NULL public system crdb_internal create_statements SELECT NULL YES 1567 NULL public system crdb_internal create_type_statements SELECT NULL YES 1568 NULL public system crdb_internal feature_usage SELECT NULL YES 1569 NULL public system crdb_internal forward_dependencies SELECT NULL YES 1570 NULL public system crdb_internal gossip_alerts SELECT NULL YES 1571 NULL public system crdb_internal gossip_liveness SELECT NULL YES 1572 NULL public system crdb_internal gossip_network SELECT NULL YES 1573 NULL public system crdb_internal gossip_nodes SELECT NULL YES 1574 NULL public system crdb_internal index_columns SELECT NULL YES 1575 NULL public system crdb_internal jobs SELECT NULL YES 1576 NULL public system crdb_internal kv_node_status SELECT NULL YES 1577 NULL public system crdb_internal kv_store_status SELECT NULL YES 1578 NULL public system crdb_internal leases SELECT NULL YES 1579 NULL public system crdb_internal node_build_info SELECT NULL YES 1580 NULL public system crdb_internal node_metrics SELECT NULL YES 1581 NULL public system crdb_internal node_queries SELECT NULL YES 1582 NULL public system crdb_internal node_runtime_info SELECT NULL YES 1583 NULL public system crdb_internal node_sessions SELECT NULL YES 1584 NULL public system crdb_internal node_statement_statistics SELECT NULL YES 1585 NULL public system crdb_internal node_transactions SELECT NULL YES 1586 NULL public system crdb_internal node_txn_stats SELECT NULL YES 1587 NULL public system crdb_internal partitions SELECT NULL YES 1588 NULL public system crdb_internal predefined_comments SELECT NULL YES 1589 NULL public system crdb_internal ranges SELECT NULL YES 1590 NULL public system crdb_internal ranges_no_leases SELECT NULL YES 1591 NULL public system crdb_internal schema_changes SELECT NULL YES 1592 NULL public system crdb_internal session_trace SELECT NULL YES 1593 NULL public system crdb_internal session_variables SELECT NULL YES 1594 NULL public system crdb_internal table_columns SELECT NULL YES 1595 NULL public system crdb_internal table_indexes SELECT NULL YES 1596 NULL public system crdb_internal tables SELECT NULL YES 1597 NULL public system crdb_internal zones SELECT NULL YES 1598 NULL public system information_schema administrable_role_authorizations SELECT NULL YES 1599 NULL public system information_schema applicable_roles SELECT NULL YES 1600 NULL public system information_schema check_constraints SELECT NULL YES 1601 NULL public system information_schema column_privileges SELECT NULL YES 1602 NULL public system information_schema columns SELECT NULL YES 1603 NULL public system information_schema constraint_column_usage SELECT NULL YES 1604 NULL public system information_schema enabled_roles SELECT NULL YES 1605 NULL public system information_schema key_column_usage SELECT NULL YES 1606 NULL public system information_schema parameters SELECT NULL YES 1607 NULL public system information_schema referential_constraints SELECT NULL YES 1608 NULL public system information_schema role_table_grants SELECT NULL YES 1609 NULL public system information_schema routines SELECT NULL YES 1610 NULL public system information_schema schema_privileges SELECT NULL YES 1611 NULL public system information_schema schemata SELECT NULL YES 1612 NULL public system information_schema sequences SELECT NULL YES 1613 NULL public system information_schema statistics SELECT NULL YES 1614 NULL public system information_schema table_constraints SELECT NULL YES 1615 NULL public system information_schema table_privileges SELECT NULL YES 1616 NULL public system information_schema tables SELECT NULL YES 1617 NULL public system information_schema user_privileges SELECT NULL YES 1618 NULL public system information_schema views SELECT NULL YES 1619 NULL public system pg_catalog pg_aggregate SELECT NULL YES 1620 NULL public system pg_catalog pg_am SELECT NULL YES 1621 NULL public system pg_catalog pg_attrdef SELECT NULL YES 1622 NULL public system pg_catalog pg_attribute SELECT NULL YES 1623 NULL public system pg_catalog pg_auth_members SELECT NULL YES 1624 NULL public system pg_catalog pg_authid SELECT NULL YES 1625 NULL public system pg_catalog pg_available_extensions SELECT NULL YES 1626 NULL public system pg_catalog pg_cast SELECT NULL YES 1627 NULL public system pg_catalog pg_class SELECT NULL YES 1628 NULL public system pg_catalog pg_collation SELECT NULL YES 1629 NULL public system pg_catalog pg_constraint SELECT NULL YES 1630 NULL public system pg_catalog pg_conversion SELECT NULL YES 1631 NULL public system pg_catalog pg_database SELECT NULL YES 1632 NULL public system pg_catalog pg_default_acl SELECT NULL YES 1633 NULL public system pg_catalog pg_depend SELECT NULL YES 1634 NULL public system pg_catalog pg_description SELECT NULL YES 1635 NULL public system pg_catalog pg_enum SELECT NULL YES 1636 NULL public system pg_catalog pg_event_trigger SELECT NULL YES 1637 NULL public system pg_catalog pg_extension SELECT NULL YES 1638 NULL public system pg_catalog pg_foreign_data_wrapper SELECT NULL YES 1639 NULL public system pg_catalog pg_foreign_server SELECT NULL YES 1640 NULL public system pg_catalog pg_foreign_table SELECT NULL YES 1641 NULL public system pg_catalog pg_index SELECT NULL YES 1642 NULL public system pg_catalog pg_indexes SELECT NULL YES 1643 NULL public system pg_catalog pg_inherits SELECT NULL YES 1644 NULL public system pg_catalog pg_language SELECT NULL YES 1645 NULL public system pg_catalog pg_locks SELECT NULL YES 1646 NULL public system pg_catalog pg_matviews SELECT NULL YES 1647 NULL public system pg_catalog pg_namespace SELECT NULL YES 1648 NULL public system pg_catalog pg_operator SELECT NULL YES 1649 NULL public system pg_catalog pg_prepared_statements SELECT NULL YES 1650 NULL public system pg_catalog pg_prepared_xacts SELECT NULL YES 1651 NULL public system pg_catalog pg_proc SELECT NULL YES 1652 NULL public system pg_catalog pg_range SELECT NULL YES 1653 NULL public system pg_catalog pg_rewrite SELECT NULL YES 1654 NULL public system pg_catalog pg_roles SELECT NULL YES 1655 NULL public system pg_catalog pg_seclabel SELECT NULL YES 1656 NULL public system pg_catalog pg_seclabels SELECT NULL YES 1657 NULL public system pg_catalog pg_sequence SELECT NULL YES 1658 NULL public system pg_catalog pg_settings SELECT NULL YES 1659 NULL public system pg_catalog pg_shdepend SELECT NULL YES 1660 NULL public system pg_catalog pg_shdescription SELECT NULL YES 1661 NULL public system pg_catalog pg_shseclabel SELECT NULL YES 1662 NULL public system pg_catalog pg_stat_activity SELECT NULL YES 1663 NULL public system pg_catalog pg_tables SELECT NULL YES 1664 NULL public system pg_catalog pg_tablespace SELECT NULL YES 1665 NULL public system pg_catalog pg_trigger SELECT NULL YES 1666 NULL public system pg_catalog pg_type SELECT NULL YES 1667 NULL public system pg_catalog pg_user SELECT NULL YES 1668 NULL public system pg_catalog pg_user_mapping SELECT NULL YES 1669 NULL public system pg_catalog pg_views SELECT NULL YES 1670 NULL public system pg_extension geography_columns SELECT NULL YES 1671 NULL public system pg_extension geometry_columns SELECT NULL YES 1672 NULL public system pg_extension spatial_ref_sys SELECT NULL YES 1673 NULL admin system public comments DELETE NULL NO 1674 NULL admin system public comments GRANT NULL NO 1675 NULL admin system public comments INSERT NULL NO 1676 NULL admin system public comments SELECT NULL YES 1677 NULL admin system public comments UPDATE NULL NO 1678 NULL public system public comments SELECT NULL YES 1679 NULL root system public comments DELETE NULL NO 1680 NULL root system public comments GRANT NULL NO 1681 NULL root system public comments INSERT NULL NO 1682 NULL root system public comments SELECT NULL YES 1683 NULL root system public comments UPDATE NULL NO 1684 NULL admin system public descriptor GRANT NULL NO 1685 NULL admin system public descriptor SELECT NULL YES 1686 NULL root system public descriptor GRANT NULL NO 1687 NULL root system public descriptor SELECT NULL YES 1688 NULL admin system public eventlog DELETE NULL NO 1689 NULL admin system public eventlog GRANT NULL NO 1690 NULL admin system public eventlog INSERT NULL NO 1691 NULL admin system public eventlog SELECT NULL YES 1692 NULL admin system public eventlog UPDATE NULL NO 1693 NULL root system public eventlog DELETE NULL NO 1694 NULL root system public eventlog GRANT NULL NO 1695 NULL root system public eventlog INSERT NULL NO 1696 NULL root system public eventlog SELECT NULL YES 1697 NULL root system public eventlog UPDATE NULL NO 1698 NULL admin system public jobs DELETE NULL NO 1699 NULL admin system public jobs GRANT NULL NO 1700 NULL admin system public jobs INSERT NULL NO 1701 NULL admin system public jobs SELECT NULL YES 1702 NULL admin system public jobs UPDATE NULL NO 1703 NULL root system public jobs DELETE NULL NO 1704 NULL root system public jobs GRANT NULL NO 1705 NULL root system public jobs INSERT NULL NO 1706 NULL root system public jobs SELECT NULL YES 1707 NULL root system public jobs UPDATE NULL NO 1708 NULL admin system public lease DELETE NULL NO 1709 NULL admin system public lease GRANT NULL NO 1710 NULL admin system public lease INSERT NULL NO 1711 NULL admin system public lease SELECT NULL YES 1712 NULL admin system public lease UPDATE NULL NO 1713 NULL root system public lease DELETE NULL NO 1714 NULL root system public lease GRANT NULL NO 1715 NULL root system public lease INSERT NULL NO 1716 NULL root system public lease SELECT NULL YES 1717 NULL root system public lease UPDATE NULL NO 1718 NULL admin system public locations DELETE NULL NO 1719 NULL admin system public locations GRANT NULL NO 1720 NULL admin system public locations INSERT NULL NO 1721 NULL admin system public locations SELECT NULL YES 1722 NULL admin system public locations UPDATE NULL NO 1723 NULL root system public locations DELETE NULL NO 1724 NULL root system public locations GRANT NULL NO 1725 NULL root system public locations INSERT NULL NO 1726 NULL root system public locations SELECT NULL YES 1727 NULL root system public locations UPDATE NULL NO 1728 NULL admin system public namespace GRANT NULL NO 1729 NULL admin system public namespace SELECT NULL YES 1730 NULL root system public namespace GRANT NULL NO 1731 NULL root system public namespace SELECT NULL YES 1732 NULL admin system public namespace2 GRANT NULL NO 1733 NULL admin system public namespace2 SELECT NULL YES 1734 NULL root system public namespace2 GRANT NULL NO 1735 NULL root system public namespace2 SELECT NULL YES 1736 NULL admin system public protected_ts_meta GRANT NULL NO 1737 NULL admin system public protected_ts_meta SELECT NULL YES 1738 NULL root system public protected_ts_meta GRANT NULL NO 1739 NULL root system public protected_ts_meta SELECT NULL YES 1740 NULL admin system public protected_ts_records GRANT NULL NO 1741 NULL admin system public protected_ts_records SELECT NULL YES 1742 NULL root system public protected_ts_records GRANT NULL NO 1743 NULL root system public protected_ts_records SELECT NULL YES 1744 NULL admin system public rangelog DELETE NULL NO 1745 NULL admin system public rangelog GRANT NULL NO 1746 NULL admin system public rangelog INSERT NULL NO 1747 NULL admin system public rangelog SELECT NULL YES 1748 NULL admin system public rangelog UPDATE NULL NO 1749 NULL root system public rangelog DELETE NULL NO 1750 NULL root system public rangelog GRANT NULL NO 1751 NULL root system public rangelog INSERT NULL NO 1752 NULL root system public rangelog SELECT NULL YES 1753 NULL root system public rangelog UPDATE NULL NO 1754 NULL admin system public replication_constraint_stats DELETE NULL NO 1755 NULL admin system public replication_constraint_stats GRANT NULL NO 1756 NULL admin system public replication_constraint_stats INSERT NULL NO 1757 NULL admin system public replication_constraint_stats SELECT NULL YES 1758 NULL admin system public replication_constraint_stats UPDATE NULL NO 1759 NULL root system public replication_constraint_stats DELETE NULL NO 1760 NULL root system public replication_constraint_stats GRANT NULL NO 1761 NULL root system public replication_constraint_stats INSERT NULL NO 1762 NULL root system public replication_constraint_stats SELECT NULL YES 1763 NULL root system public replication_constraint_stats UPDATE NULL NO 1764 NULL admin system public replication_critical_localities DELETE NULL NO 1765 NULL admin system public replication_critical_localities GRANT NULL NO 1766 NULL admin system public replication_critical_localities INSERT NULL NO 1767 NULL admin system public replication_critical_localities SELECT NULL YES 1768 NULL admin system public replication_critical_localities UPDATE NULL NO 1769 NULL root system public replication_critical_localities DELETE NULL NO 1770 NULL root system public replication_critical_localities GRANT NULL NO 1771 NULL root system public replication_critical_localities INSERT NULL NO 1772 NULL root system public replication_critical_localities SELECT NULL YES 1773 NULL root system public replication_critical_localities UPDATE NULL NO 1774 NULL admin system public replication_stats DELETE NULL NO 1775 NULL admin system public replication_stats GRANT NULL NO 1776 NULL admin system public replication_stats INSERT NULL NO 1777 NULL admin system public replication_stats SELECT NULL YES 1778 NULL admin system public replication_stats UPDATE NULL NO 1779 NULL root system public replication_stats DELETE NULL NO 1780 NULL root system public replication_stats GRANT NULL NO 1781 NULL root system public replication_stats INSERT NULL NO 1782 NULL root system public replication_stats SELECT NULL YES 1783 NULL root system public replication_stats UPDATE NULL NO 1784 NULL admin system public reports_meta DELETE NULL NO 1785 NULL admin system public reports_meta GRANT NULL NO 1786 NULL admin system public reports_meta INSERT NULL NO 1787 NULL admin system public reports_meta SELECT NULL YES 1788 NULL admin system public reports_meta UPDATE NULL NO 1789 NULL root system public reports_meta DELETE NULL NO 1790 NULL root system public reports_meta GRANT NULL NO 1791 NULL root system public reports_meta INSERT NULL NO 1792 NULL root system public reports_meta SELECT NULL YES 1793 NULL root system public reports_meta UPDATE NULL NO 1794 NULL admin system public role_members DELETE NULL NO 1795 NULL admin system public role_members GRANT NULL NO 1796 NULL admin system public role_members INSERT NULL NO 1797 NULL admin system public role_members SELECT NULL YES 1798 NULL admin system public role_members UPDATE NULL NO 1799 NULL root system public role_members DELETE NULL NO 1800 NULL root system public role_members GRANT NULL NO 1801 NULL root system public role_members INSERT NULL NO 1802 NULL root system public role_members SELECT NULL YES 1803 NULL root system public role_members UPDATE NULL NO 1804 NULL admin system public role_options DELETE NULL NO 1805 NULL admin system public role_options GRANT NULL NO 1806 NULL admin system public role_options INSERT NULL NO 1807 NULL admin system public role_options SELECT NULL YES 1808 NULL admin system public role_options UPDATE NULL NO 1809 NULL root system public role_options DELETE NULL NO 1810 NULL root system public role_options GRANT NULL NO 1811 NULL root system public role_options INSERT NULL NO 1812 NULL root system public role_options SELECT NULL YES 1813 NULL root system public role_options UPDATE NULL NO 1814 NULL admin system public settings DELETE NULL NO 1815 NULL admin system public settings GRANT NULL NO 1816 NULL admin system public settings INSERT NULL NO 1817 NULL admin system public settings SELECT NULL YES 1818 NULL admin system public settings UPDATE NULL NO 1819 NULL root system public settings DELETE NULL NO 1820 NULL root system public settings GRANT NULL NO 1821 NULL root system public settings INSERT NULL NO 1822 NULL root system public settings SELECT NULL YES 1823 NULL root system public settings UPDATE NULL NO 1824 NULL admin system public statement_bundle_chunks DELETE NULL NO 1825 NULL admin system public statement_bundle_chunks GRANT NULL NO 1826 NULL admin system public statement_bundle_chunks INSERT NULL NO 1827 NULL admin system public statement_bundle_chunks SELECT NULL YES 1828 NULL admin system public statement_bundle_chunks UPDATE NULL NO 1829 NULL root system public statement_bundle_chunks DELETE NULL NO 1830 NULL root system public statement_bundle_chunks GRANT NULL NO 1831 NULL root system public statement_bundle_chunks INSERT NULL NO 1832 NULL root system public statement_bundle_chunks SELECT NULL YES 1833 NULL root system public statement_bundle_chunks UPDATE NULL NO 1834 NULL admin system public statement_diagnostics DELETE NULL NO 1835 NULL admin system public statement_diagnostics GRANT NULL NO 1836 NULL admin system public statement_diagnostics INSERT NULL NO 1837 NULL admin system public statement_diagnostics SELECT NULL YES 1838 NULL admin system public statement_diagnostics UPDATE NULL NO 1839 NULL root system public statement_diagnostics DELETE NULL NO 1840 NULL root system public statement_diagnostics GRANT NULL NO 1841 NULL root system public statement_diagnostics INSERT NULL NO 1842 NULL root system public statement_diagnostics SELECT NULL YES 1843 NULL root system public statement_diagnostics UPDATE NULL NO 1844 NULL admin system public statement_diagnostics_requests DELETE NULL NO 1845 NULL admin system public statement_diagnostics_requests GRANT NULL NO 1846 NULL admin system public statement_diagnostics_requests INSERT NULL NO 1847 NULL admin system public statement_diagnostics_requests SELECT NULL YES 1848 NULL admin system public statement_diagnostics_requests UPDATE NULL NO 1849 NULL root system public statement_diagnostics_requests DELETE NULL NO 1850 NULL root system public statement_diagnostics_requests GRANT NULL NO 1851 NULL root system public statement_diagnostics_requests INSERT NULL NO 1852 NULL root system public statement_diagnostics_requests SELECT NULL YES 1853 NULL root system public statement_diagnostics_requests UPDATE NULL NO 1854 NULL admin system public table_statistics DELETE NULL NO 1855 NULL admin system public table_statistics GRANT NULL NO 1856 NULL admin system public table_statistics INSERT NULL NO 1857 NULL admin system public table_statistics SELECT NULL YES 1858 NULL admin system public table_statistics UPDATE NULL NO 1859 NULL root system public table_statistics DELETE NULL NO 1860 NULL root system public table_statistics GRANT NULL NO 1861 NULL root system public table_statistics INSERT NULL NO 1862 NULL root system public table_statistics SELECT NULL YES 1863 NULL root system public table_statistics UPDATE NULL NO 1864 NULL admin system public tenants GRANT NULL NO 1865 NULL admin system public tenants SELECT NULL YES 1866 NULL root system public tenants GRANT NULL NO 1867 NULL root system public tenants SELECT NULL YES 1868 NULL admin system public ui DELETE NULL NO 1869 NULL admin system public ui GRANT NULL NO 1870 NULL admin system public ui INSERT NULL NO 1871 NULL admin system public ui SELECT NULL YES 1872 NULL admin system public ui UPDATE NULL NO 1873 NULL root system public ui DELETE NULL NO 1874 NULL root system public ui GRANT NULL NO 1875 NULL root system public ui INSERT NULL NO 1876 NULL root system public ui SELECT NULL YES 1877 NULL root system public ui UPDATE NULL NO 1878 NULL admin system public users DELETE NULL NO 1879 NULL admin system public users GRANT NULL NO 1880 NULL admin system public users INSERT NULL NO 1881 NULL admin system public users SELECT NULL YES 1882 NULL admin system public users UPDATE NULL NO 1883 NULL root system public users DELETE NULL NO 1884 NULL root system public users GRANT NULL NO 1885 NULL root system public users INSERT NULL NO 1886 NULL root system public users SELECT NULL YES 1887 NULL root system public users UPDATE NULL NO 1888 NULL admin system public web_sessions DELETE NULL NO 1889 NULL admin system public web_sessions GRANT NULL NO 1890 NULL admin system public web_sessions INSERT NULL NO 1891 NULL admin system public web_sessions SELECT NULL YES 1892 NULL admin system public web_sessions UPDATE NULL NO 1893 NULL root system public web_sessions DELETE NULL NO 1894 NULL root system public web_sessions GRANT NULL NO 1895 NULL root system public web_sessions INSERT NULL NO 1896 NULL root system public web_sessions SELECT NULL YES 1897 NULL root system public web_sessions UPDATE NULL NO 1898 NULL admin system public zones DELETE NULL NO 1899 NULL admin system public zones GRANT NULL NO 1900 NULL admin system public zones INSERT NULL NO 1901 NULL admin system public zones SELECT NULL YES 1902 NULL admin system public zones UPDATE NULL NO 1903 NULL root system public zones DELETE NULL NO 1904 NULL root system public zones GRANT NULL NO 1905 NULL root system public zones INSERT NULL NO 1906 NULL root system public zones SELECT NULL YES 1907 NULL root system public zones UPDATE NULL NO 1908 1909 query TTTTTTTT colnames 1910 SELECT * FROM system.information_schema.role_table_grants 1911 ---- 1912 grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy 1913 NULL public system crdb_internal backward_dependencies SELECT NULL YES 1914 NULL public system crdb_internal builtin_functions SELECT NULL YES 1915 NULL public system crdb_internal cluster_queries SELECT NULL YES 1916 NULL public system crdb_internal cluster_sessions SELECT NULL YES 1917 NULL public system crdb_internal cluster_settings SELECT NULL YES 1918 NULL public system crdb_internal cluster_transactions SELECT NULL YES 1919 NULL public system crdb_internal create_statements SELECT NULL YES 1920 NULL public system crdb_internal create_type_statements SELECT NULL YES 1921 NULL public system crdb_internal feature_usage SELECT NULL YES 1922 NULL public system crdb_internal forward_dependencies SELECT NULL YES 1923 NULL public system crdb_internal gossip_alerts SELECT NULL YES 1924 NULL public system crdb_internal gossip_liveness SELECT NULL YES 1925 NULL public system crdb_internal gossip_network SELECT NULL YES 1926 NULL public system crdb_internal gossip_nodes SELECT NULL YES 1927 NULL public system crdb_internal index_columns SELECT NULL YES 1928 NULL public system crdb_internal jobs SELECT NULL YES 1929 NULL public system crdb_internal kv_node_status SELECT NULL YES 1930 NULL public system crdb_internal kv_store_status SELECT NULL YES 1931 NULL public system crdb_internal leases SELECT NULL YES 1932 NULL public system crdb_internal node_build_info SELECT NULL YES 1933 NULL public system crdb_internal node_metrics SELECT NULL YES 1934 NULL public system crdb_internal node_queries SELECT NULL YES 1935 NULL public system crdb_internal node_runtime_info SELECT NULL YES 1936 NULL public system crdb_internal node_sessions SELECT NULL YES 1937 NULL public system crdb_internal node_statement_statistics SELECT NULL YES 1938 NULL public system crdb_internal node_transactions SELECT NULL YES 1939 NULL public system crdb_internal node_txn_stats SELECT NULL YES 1940 NULL public system crdb_internal partitions SELECT NULL YES 1941 NULL public system crdb_internal predefined_comments SELECT NULL YES 1942 NULL public system crdb_internal ranges SELECT NULL YES 1943 NULL public system crdb_internal ranges_no_leases SELECT NULL YES 1944 NULL public system crdb_internal schema_changes SELECT NULL YES 1945 NULL public system crdb_internal session_trace SELECT NULL YES 1946 NULL public system crdb_internal session_variables SELECT NULL YES 1947 NULL public system crdb_internal table_columns SELECT NULL YES 1948 NULL public system crdb_internal table_indexes SELECT NULL YES 1949 NULL public system crdb_internal tables SELECT NULL YES 1950 NULL public system crdb_internal zones SELECT NULL YES 1951 NULL public system information_schema administrable_role_authorizations SELECT NULL YES 1952 NULL public system information_schema applicable_roles SELECT NULL YES 1953 NULL public system information_schema check_constraints SELECT NULL YES 1954 NULL public system information_schema column_privileges SELECT NULL YES 1955 NULL public system information_schema columns SELECT NULL YES 1956 NULL public system information_schema constraint_column_usage SELECT NULL YES 1957 NULL public system information_schema enabled_roles SELECT NULL YES 1958 NULL public system information_schema key_column_usage SELECT NULL YES 1959 NULL public system information_schema parameters SELECT NULL YES 1960 NULL public system information_schema referential_constraints SELECT NULL YES 1961 NULL public system information_schema role_table_grants SELECT NULL YES 1962 NULL public system information_schema routines SELECT NULL YES 1963 NULL public system information_schema schema_privileges SELECT NULL YES 1964 NULL public system information_schema schemata SELECT NULL YES 1965 NULL public system information_schema sequences SELECT NULL YES 1966 NULL public system information_schema statistics SELECT NULL YES 1967 NULL public system information_schema table_constraints SELECT NULL YES 1968 NULL public system information_schema table_privileges SELECT NULL YES 1969 NULL public system information_schema tables SELECT NULL YES 1970 NULL public system information_schema user_privileges SELECT NULL YES 1971 NULL public system information_schema views SELECT NULL YES 1972 NULL public system pg_catalog pg_aggregate SELECT NULL YES 1973 NULL public system pg_catalog pg_am SELECT NULL YES 1974 NULL public system pg_catalog pg_attrdef SELECT NULL YES 1975 NULL public system pg_catalog pg_attribute SELECT NULL YES 1976 NULL public system pg_catalog pg_auth_members SELECT NULL YES 1977 NULL public system pg_catalog pg_authid SELECT NULL YES 1978 NULL public system pg_catalog pg_available_extensions SELECT NULL YES 1979 NULL public system pg_catalog pg_cast SELECT NULL YES 1980 NULL public system pg_catalog pg_class SELECT NULL YES 1981 NULL public system pg_catalog pg_collation SELECT NULL YES 1982 NULL public system pg_catalog pg_constraint SELECT NULL YES 1983 NULL public system pg_catalog pg_conversion SELECT NULL YES 1984 NULL public system pg_catalog pg_database SELECT NULL YES 1985 NULL public system pg_catalog pg_default_acl SELECT NULL YES 1986 NULL public system pg_catalog pg_depend SELECT NULL YES 1987 NULL public system pg_catalog pg_description SELECT NULL YES 1988 NULL public system pg_catalog pg_enum SELECT NULL YES 1989 NULL public system pg_catalog pg_event_trigger SELECT NULL YES 1990 NULL public system pg_catalog pg_extension SELECT NULL YES 1991 NULL public system pg_catalog pg_foreign_data_wrapper SELECT NULL YES 1992 NULL public system pg_catalog pg_foreign_server SELECT NULL YES 1993 NULL public system pg_catalog pg_foreign_table SELECT NULL YES 1994 NULL public system pg_catalog pg_index SELECT NULL YES 1995 NULL public system pg_catalog pg_indexes SELECT NULL YES 1996 NULL public system pg_catalog pg_inherits SELECT NULL YES 1997 NULL public system pg_catalog pg_language SELECT NULL YES 1998 NULL public system pg_catalog pg_locks SELECT NULL YES 1999 NULL public system pg_catalog pg_matviews SELECT NULL YES 2000 NULL public system pg_catalog pg_namespace SELECT NULL YES 2001 NULL public system pg_catalog pg_operator SELECT NULL YES 2002 NULL public system pg_catalog pg_prepared_statements SELECT NULL YES 2003 NULL public system pg_catalog pg_prepared_xacts SELECT NULL YES 2004 NULL public system pg_catalog pg_proc SELECT NULL YES 2005 NULL public system pg_catalog pg_range SELECT NULL YES 2006 NULL public system pg_catalog pg_rewrite SELECT NULL YES 2007 NULL public system pg_catalog pg_roles SELECT NULL YES 2008 NULL public system pg_catalog pg_seclabel SELECT NULL YES 2009 NULL public system pg_catalog pg_seclabels SELECT NULL YES 2010 NULL public system pg_catalog pg_sequence SELECT NULL YES 2011 NULL public system pg_catalog pg_settings SELECT NULL YES 2012 NULL public system pg_catalog pg_shdepend SELECT NULL YES 2013 NULL public system pg_catalog pg_shdescription SELECT NULL YES 2014 NULL public system pg_catalog pg_shseclabel SELECT NULL YES 2015 NULL public system pg_catalog pg_stat_activity SELECT NULL YES 2016 NULL public system pg_catalog pg_tables SELECT NULL YES 2017 NULL public system pg_catalog pg_tablespace SELECT NULL YES 2018 NULL public system pg_catalog pg_trigger SELECT NULL YES 2019 NULL public system pg_catalog pg_type SELECT NULL YES 2020 NULL public system pg_catalog pg_user SELECT NULL YES 2021 NULL public system pg_catalog pg_user_mapping SELECT NULL YES 2022 NULL public system pg_catalog pg_views SELECT NULL YES 2023 NULL public system pg_extension geography_columns SELECT NULL YES 2024 NULL public system pg_extension geometry_columns SELECT NULL YES 2025 NULL public system pg_extension spatial_ref_sys SELECT NULL YES 2026 NULL admin system public namespace GRANT NULL NO 2027 NULL admin system public namespace SELECT NULL YES 2028 NULL root system public namespace GRANT NULL NO 2029 NULL root system public namespace SELECT NULL YES 2030 NULL admin system public descriptor GRANT NULL NO 2031 NULL admin system public descriptor SELECT NULL YES 2032 NULL root system public descriptor GRANT NULL NO 2033 NULL root system public descriptor SELECT NULL YES 2034 NULL admin system public users DELETE NULL NO 2035 NULL admin system public users GRANT NULL NO 2036 NULL admin system public users INSERT NULL NO 2037 NULL admin system public users SELECT NULL YES 2038 NULL admin system public users UPDATE NULL NO 2039 NULL root system public users DELETE NULL NO 2040 NULL root system public users GRANT NULL NO 2041 NULL root system public users INSERT NULL NO 2042 NULL root system public users SELECT NULL YES 2043 NULL root system public users UPDATE NULL NO 2044 NULL admin system public zones DELETE NULL NO 2045 NULL admin system public zones GRANT NULL NO 2046 NULL admin system public zones INSERT NULL NO 2047 NULL admin system public zones SELECT NULL YES 2048 NULL admin system public zones UPDATE NULL NO 2049 NULL root system public zones DELETE NULL NO 2050 NULL root system public zones GRANT NULL NO 2051 NULL root system public zones INSERT NULL NO 2052 NULL root system public zones SELECT NULL YES 2053 NULL root system public zones UPDATE NULL NO 2054 NULL admin system public settings DELETE NULL NO 2055 NULL admin system public settings GRANT NULL NO 2056 NULL admin system public settings INSERT NULL NO 2057 NULL admin system public settings SELECT NULL YES 2058 NULL admin system public settings UPDATE NULL NO 2059 NULL root system public settings DELETE NULL NO 2060 NULL root system public settings GRANT NULL NO 2061 NULL root system public settings INSERT NULL NO 2062 NULL root system public settings SELECT NULL YES 2063 NULL root system public settings UPDATE NULL NO 2064 NULL admin system public tenants GRANT NULL NO 2065 NULL admin system public tenants SELECT NULL YES 2066 NULL root system public tenants GRANT NULL NO 2067 NULL root system public tenants SELECT NULL YES 2068 NULL admin system public lease DELETE NULL NO 2069 NULL admin system public lease GRANT NULL NO 2070 NULL admin system public lease INSERT NULL NO 2071 NULL admin system public lease SELECT NULL YES 2072 NULL admin system public lease UPDATE NULL NO 2073 NULL root system public lease DELETE NULL NO 2074 NULL root system public lease GRANT NULL NO 2075 NULL root system public lease INSERT NULL NO 2076 NULL root system public lease SELECT NULL YES 2077 NULL root system public lease UPDATE NULL NO 2078 NULL admin system public eventlog DELETE NULL NO 2079 NULL admin system public eventlog GRANT NULL NO 2080 NULL admin system public eventlog INSERT NULL NO 2081 NULL admin system public eventlog SELECT NULL YES 2082 NULL admin system public eventlog UPDATE NULL NO 2083 NULL root system public eventlog DELETE NULL NO 2084 NULL root system public eventlog GRANT NULL NO 2085 NULL root system public eventlog INSERT NULL NO 2086 NULL root system public eventlog SELECT NULL YES 2087 NULL root system public eventlog UPDATE NULL NO 2088 NULL admin system public rangelog DELETE NULL NO 2089 NULL admin system public rangelog GRANT NULL NO 2090 NULL admin system public rangelog INSERT NULL NO 2091 NULL admin system public rangelog SELECT NULL YES 2092 NULL admin system public rangelog UPDATE NULL NO 2093 NULL root system public rangelog DELETE NULL NO 2094 NULL root system public rangelog GRANT NULL NO 2095 NULL root system public rangelog INSERT NULL NO 2096 NULL root system public rangelog SELECT NULL YES 2097 NULL root system public rangelog UPDATE NULL NO 2098 NULL admin system public ui DELETE NULL NO 2099 NULL admin system public ui GRANT NULL NO 2100 NULL admin system public ui INSERT NULL NO 2101 NULL admin system public ui SELECT NULL YES 2102 NULL admin system public ui UPDATE NULL NO 2103 NULL root system public ui DELETE NULL NO 2104 NULL root system public ui GRANT NULL NO 2105 NULL root system public ui INSERT NULL NO 2106 NULL root system public ui SELECT NULL YES 2107 NULL root system public ui UPDATE NULL NO 2108 NULL admin system public jobs DELETE NULL NO 2109 NULL admin system public jobs GRANT NULL NO 2110 NULL admin system public jobs INSERT NULL NO 2111 NULL admin system public jobs SELECT NULL YES 2112 NULL admin system public jobs UPDATE NULL NO 2113 NULL root system public jobs DELETE NULL NO 2114 NULL root system public jobs GRANT NULL NO 2115 NULL root system public jobs INSERT NULL NO 2116 NULL root system public jobs SELECT NULL YES 2117 NULL root system public jobs UPDATE NULL NO 2118 NULL admin system public web_sessions DELETE NULL NO 2119 NULL admin system public web_sessions GRANT NULL NO 2120 NULL admin system public web_sessions INSERT NULL NO 2121 NULL admin system public web_sessions SELECT NULL YES 2122 NULL admin system public web_sessions UPDATE NULL NO 2123 NULL root system public web_sessions DELETE NULL NO 2124 NULL root system public web_sessions GRANT NULL NO 2125 NULL root system public web_sessions INSERT NULL NO 2126 NULL root system public web_sessions SELECT NULL YES 2127 NULL root system public web_sessions UPDATE NULL NO 2128 NULL admin system public table_statistics DELETE NULL NO 2129 NULL admin system public table_statistics GRANT NULL NO 2130 NULL admin system public table_statistics INSERT NULL NO 2131 NULL admin system public table_statistics SELECT NULL YES 2132 NULL admin system public table_statistics UPDATE NULL NO 2133 NULL root system public table_statistics DELETE NULL NO 2134 NULL root system public table_statistics GRANT NULL NO 2135 NULL root system public table_statistics INSERT NULL NO 2136 NULL root system public table_statistics SELECT NULL YES 2137 NULL root system public table_statistics UPDATE NULL NO 2138 NULL admin system public locations DELETE NULL NO 2139 NULL admin system public locations GRANT NULL NO 2140 NULL admin system public locations INSERT NULL NO 2141 NULL admin system public locations SELECT NULL YES 2142 NULL admin system public locations UPDATE NULL NO 2143 NULL root system public locations DELETE NULL NO 2144 NULL root system public locations GRANT NULL NO 2145 NULL root system public locations INSERT NULL NO 2146 NULL root system public locations SELECT NULL YES 2147 NULL root system public locations UPDATE NULL NO 2148 NULL admin system public role_members DELETE NULL NO 2149 NULL admin system public role_members GRANT NULL NO 2150 NULL admin system public role_members INSERT NULL NO 2151 NULL admin system public role_members SELECT NULL YES 2152 NULL admin system public role_members UPDATE NULL NO 2153 NULL root system public role_members DELETE NULL NO 2154 NULL root system public role_members GRANT NULL NO 2155 NULL root system public role_members INSERT NULL NO 2156 NULL root system public role_members SELECT NULL YES 2157 NULL root system public role_members UPDATE NULL NO 2158 NULL admin system public comments DELETE NULL NO 2159 NULL admin system public comments GRANT NULL NO 2160 NULL admin system public comments INSERT NULL NO 2161 NULL admin system public comments SELECT NULL YES 2162 NULL admin system public comments UPDATE NULL NO 2163 NULL public system public comments SELECT NULL YES 2164 NULL root system public comments DELETE NULL NO 2165 NULL root system public comments GRANT NULL NO 2166 NULL root system public comments INSERT NULL NO 2167 NULL root system public comments SELECT NULL YES 2168 NULL root system public comments UPDATE NULL NO 2169 NULL admin system public replication_constraint_stats DELETE NULL NO 2170 NULL admin system public replication_constraint_stats GRANT NULL NO 2171 NULL admin system public replication_constraint_stats INSERT NULL NO 2172 NULL admin system public replication_constraint_stats SELECT NULL YES 2173 NULL admin system public replication_constraint_stats UPDATE NULL NO 2174 NULL root system public replication_constraint_stats DELETE NULL NO 2175 NULL root system public replication_constraint_stats GRANT NULL NO 2176 NULL root system public replication_constraint_stats INSERT NULL NO 2177 NULL root system public replication_constraint_stats SELECT NULL YES 2178 NULL root system public replication_constraint_stats UPDATE NULL NO 2179 NULL admin system public replication_critical_localities DELETE NULL NO 2180 NULL admin system public replication_critical_localities GRANT NULL NO 2181 NULL admin system public replication_critical_localities INSERT NULL NO 2182 NULL admin system public replication_critical_localities SELECT NULL YES 2183 NULL admin system public replication_critical_localities UPDATE NULL NO 2184 NULL root system public replication_critical_localities DELETE NULL NO 2185 NULL root system public replication_critical_localities GRANT NULL NO 2186 NULL root system public replication_critical_localities INSERT NULL NO 2187 NULL root system public replication_critical_localities SELECT NULL YES 2188 NULL root system public replication_critical_localities UPDATE NULL NO 2189 NULL admin system public replication_stats DELETE NULL NO 2190 NULL admin system public replication_stats GRANT NULL NO 2191 NULL admin system public replication_stats INSERT NULL NO 2192 NULL admin system public replication_stats SELECT NULL YES 2193 NULL admin system public replication_stats UPDATE NULL NO 2194 NULL root system public replication_stats DELETE NULL NO 2195 NULL root system public replication_stats GRANT NULL NO 2196 NULL root system public replication_stats INSERT NULL NO 2197 NULL root system public replication_stats SELECT NULL YES 2198 NULL root system public replication_stats UPDATE NULL NO 2199 NULL admin system public reports_meta DELETE NULL NO 2200 NULL admin system public reports_meta GRANT NULL NO 2201 NULL admin system public reports_meta INSERT NULL NO 2202 NULL admin system public reports_meta SELECT NULL YES 2203 NULL admin system public reports_meta UPDATE NULL NO 2204 NULL root system public reports_meta DELETE NULL NO 2205 NULL root system public reports_meta GRANT NULL NO 2206 NULL root system public reports_meta INSERT NULL NO 2207 NULL root system public reports_meta SELECT NULL YES 2208 NULL root system public reports_meta UPDATE NULL NO 2209 NULL admin system public namespace2 GRANT NULL NO 2210 NULL admin system public namespace2 SELECT NULL YES 2211 NULL root system public namespace2 GRANT NULL NO 2212 NULL root system public namespace2 SELECT NULL YES 2213 NULL admin system public protected_ts_meta GRANT NULL NO 2214 NULL admin system public protected_ts_meta SELECT NULL YES 2215 NULL root system public protected_ts_meta GRANT NULL NO 2216 NULL root system public protected_ts_meta SELECT NULL YES 2217 NULL admin system public protected_ts_records GRANT NULL NO 2218 NULL admin system public protected_ts_records SELECT NULL YES 2219 NULL root system public protected_ts_records GRANT NULL NO 2220 NULL root system public protected_ts_records SELECT NULL YES 2221 NULL admin system public role_options DELETE NULL NO 2222 NULL admin system public role_options GRANT NULL NO 2223 NULL admin system public role_options INSERT NULL NO 2224 NULL admin system public role_options SELECT NULL YES 2225 NULL admin system public role_options UPDATE NULL NO 2226 NULL root system public role_options DELETE NULL NO 2227 NULL root system public role_options GRANT NULL NO 2228 NULL root system public role_options INSERT NULL NO 2229 NULL root system public role_options SELECT NULL YES 2230 NULL root system public role_options UPDATE NULL NO 2231 NULL admin system public statement_bundle_chunks DELETE NULL NO 2232 NULL admin system public statement_bundle_chunks GRANT NULL NO 2233 NULL admin system public statement_bundle_chunks INSERT NULL NO 2234 NULL admin system public statement_bundle_chunks SELECT NULL YES 2235 NULL admin system public statement_bundle_chunks UPDATE NULL NO 2236 NULL root system public statement_bundle_chunks DELETE NULL NO 2237 NULL root system public statement_bundle_chunks GRANT NULL NO 2238 NULL root system public statement_bundle_chunks INSERT NULL NO 2239 NULL root system public statement_bundle_chunks SELECT NULL YES 2240 NULL root system public statement_bundle_chunks UPDATE NULL NO 2241 NULL admin system public statement_diagnostics_requests DELETE NULL NO 2242 NULL admin system public statement_diagnostics_requests GRANT NULL NO 2243 NULL admin system public statement_diagnostics_requests INSERT NULL NO 2244 NULL admin system public statement_diagnostics_requests SELECT NULL YES 2245 NULL admin system public statement_diagnostics_requests UPDATE NULL NO 2246 NULL root system public statement_diagnostics_requests DELETE NULL NO 2247 NULL root system public statement_diagnostics_requests GRANT NULL NO 2248 NULL root system public statement_diagnostics_requests INSERT NULL NO 2249 NULL root system public statement_diagnostics_requests SELECT NULL YES 2250 NULL root system public statement_diagnostics_requests UPDATE NULL NO 2251 NULL admin system public statement_diagnostics DELETE NULL NO 2252 NULL admin system public statement_diagnostics GRANT NULL NO 2253 NULL admin system public statement_diagnostics INSERT NULL NO 2254 NULL admin system public statement_diagnostics SELECT NULL YES 2255 NULL admin system public statement_diagnostics UPDATE NULL NO 2256 NULL root system public statement_diagnostics DELETE NULL NO 2257 NULL root system public statement_diagnostics GRANT NULL NO 2258 NULL root system public statement_diagnostics INSERT NULL NO 2259 NULL root system public statement_diagnostics SELECT NULL YES 2260 NULL root system public statement_diagnostics UPDATE NULL NO 2261 2262 statement ok 2263 CREATE TABLE other_db.xyz (i INT) 2264 2265 statement ok 2266 CREATE VIEW other_db.abc AS SELECT i from other_db.xyz 2267 2268 query TTTTTTTT colnames 2269 SELECT * FROM other_db.information_schema.table_privileges WHERE TABLE_SCHEMA = 'public' 2270 ---- 2271 grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy 2272 NULL admin other_db public xyz ALL NULL NO 2273 NULL root other_db public xyz ALL NULL NO 2274 NULL testuser other_db public xyz SELECT NULL YES 2275 NULL admin other_db public abc ALL NULL NO 2276 NULL root other_db public abc ALL NULL NO 2277 NULL testuser other_db public abc SELECT NULL YES 2278 2279 query TTTTTTTT colnames 2280 SELECT * FROM other_db.information_schema.role_table_grants WHERE TABLE_SCHEMA = 'public' 2281 ---- 2282 grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy 2283 NULL admin other_db public xyz ALL NULL NO 2284 NULL root other_db public xyz ALL NULL NO 2285 NULL testuser other_db public xyz SELECT NULL YES 2286 NULL admin other_db public abc ALL NULL NO 2287 NULL root other_db public abc ALL NULL NO 2288 NULL testuser other_db public abc SELECT NULL YES 2289 2290 statement ok 2291 GRANT UPDATE ON other_db.xyz TO testuser 2292 2293 query TTTTTTTT colnames 2294 SELECT * FROM other_db.information_schema.table_privileges WHERE TABLE_SCHEMA = 'public' 2295 ---- 2296 grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy 2297 NULL admin other_db public xyz ALL NULL NO 2298 NULL root other_db public xyz ALL NULL NO 2299 NULL testuser other_db public xyz SELECT NULL YES 2300 NULL testuser other_db public xyz UPDATE NULL NO 2301 NULL admin other_db public abc ALL NULL NO 2302 NULL root other_db public abc ALL NULL NO 2303 NULL testuser other_db public abc SELECT NULL YES 2304 2305 query TTTTTTTT colnames 2306 SELECT * FROM other_db.information_schema.role_table_grants WHERE TABLE_SCHEMA = 'public' 2307 ---- 2308 grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy 2309 NULL admin other_db public xyz ALL NULL NO 2310 NULL root other_db public xyz ALL NULL NO 2311 NULL testuser other_db public xyz SELECT NULL YES 2312 NULL testuser other_db public xyz UPDATE NULL NO 2313 NULL admin other_db public abc ALL NULL NO 2314 NULL root other_db public abc ALL NULL NO 2315 NULL testuser other_db public abc SELECT NULL YES 2316 2317 # testuser can read permissions as well 2318 user testuser 2319 2320 statement ok 2321 SET DATABASE = other_db 2322 2323 query TTTTTTTT colnames 2324 SELECT * FROM information_schema.table_privileges WHERE TABLE_SCHEMA = 'public' 2325 ---- 2326 grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy 2327 NULL admin other_db public xyz ALL NULL NO 2328 NULL root other_db public xyz ALL NULL NO 2329 NULL testuser other_db public xyz SELECT NULL YES 2330 NULL testuser other_db public xyz UPDATE NULL NO 2331 NULL admin other_db public abc ALL NULL NO 2332 NULL root other_db public abc ALL NULL NO 2333 NULL testuser other_db public abc SELECT NULL YES 2334 2335 query TTTTTTTT colnames 2336 SELECT * FROM information_schema.role_table_grants WHERE TABLE_SCHEMA = 'public' 2337 ---- 2338 grantor grantee table_catalog table_schema table_name privilege_type is_grantable with_hierarchy 2339 NULL admin other_db public xyz ALL NULL NO 2340 NULL root other_db public xyz ALL NULL NO 2341 NULL testuser other_db public xyz SELECT NULL YES 2342 NULL testuser other_db public xyz UPDATE NULL NO 2343 NULL admin other_db public abc ALL NULL NO 2344 NULL root other_db public abc ALL NULL NO 2345 NULL testuser other_db public abc SELECT NULL YES 2346 2347 statement ok 2348 SET DATABASE = test 2349 2350 user root 2351 2352 ## information_schema.statistics 2353 2354 statement ok 2355 CREATE TABLE other_db.teststatics(id INT PRIMARY KEY, c INT, d INT, e STRING, INDEX idx_c(c), UNIQUE INDEX idx_cd(c,d)) 2356 2357 query TTTTTTITIITTT colnames 2358 SELECT * FROM other_db.information_schema.statistics WHERE table_schema='public' AND table_name='teststatics' ORDER BY INDEX_SCHEMA,INDEX_NAME,SEQ_IN_INDEX 2359 ---- 2360 table_catalog table_schema table_name non_unique index_schema index_name seq_in_index column_name COLLATION cardinality direction storing implicit 2361 other_db public teststatics YES public idx_c 1 c NULL NULL ASC NO NO 2362 other_db public teststatics YES public idx_c 2 id NULL NULL ASC NO YES 2363 other_db public teststatics NO public idx_cd 1 c NULL NULL ASC NO NO 2364 other_db public teststatics NO public idx_cd 2 d NULL NULL ASC NO NO 2365 other_db public teststatics NO public idx_cd 3 id NULL NULL ASC NO YES 2366 other_db public teststatics NO public primary 1 id NULL NULL ASC NO NO 2367 2368 # Verify information_schema.views 2369 statement ok 2370 CREATE VIEW other_db.v_xyz AS SELECT i FROM other_db.xyz 2371 2372 query TTTTT colnames 2373 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION 2374 FROM other_db.information_schema.views 2375 WHERE TABLE_NAME='v_xyz' 2376 ---- 2377 table_catalog table_schema table_name view_definition check_option 2378 other_db public v_xyz SELECT i FROM other_db.public.xyz NULL 2379 2380 query TTTTT colnames 2381 SELECT IS_UPDATABLE, IS_INSERTABLE_INTO, IS_TRIGGER_UPDATABLE, IS_TRIGGER_DELETABLE, IS_TRIGGER_INSERTABLE_INTO 2382 FROM other_db.information_schema.views 2383 WHERE TABLE_NAME='v_xyz' 2384 ---- 2385 is_updatable is_insertable_into is_trigger_updatable is_trigger_deletable is_trigger_insertable_into 2386 NO NO NO NO NO 2387 2388 statement ok 2389 SET DATABASE = 'test' 2390 2391 statement ok 2392 DROP DATABASE other_db CASCADE 2393 2394 #Verify information_schema.user_privileges 2395 2396 query TTTT colnames,rowsort 2397 SELECT * FROM information_schema.user_privileges ORDER BY grantee,privilege_type 2398 ---- 2399 grantee table_catalog privilege_type is_grantable 2400 admin test ALL NULL 2401 admin test CREATE NULL 2402 admin test DELETE NULL 2403 admin test DROP NULL 2404 admin test GRANT NULL 2405 admin test INSERT NULL 2406 admin test SELECT NULL 2407 admin test UPDATE NULL 2408 admin test ZONECONFIG NULL 2409 root test ALL NULL 2410 root test CREATE NULL 2411 root test DELETE NULL 2412 root test DROP NULL 2413 root test GRANT NULL 2414 root test INSERT NULL 2415 root test SELECT NULL 2416 root test UPDATE NULL 2417 root test ZONECONFIG NULL 2418 2419 # information_schema.sequences 2420 2421 statement ok 2422 SET DATABASE = test 2423 2424 query TTTTIIITTTTT 2425 SELECT * FROM information_schema.sequences 2426 ---- 2427 2428 statement ok 2429 CREATE SEQUENCE test_seq 2430 2431 statement ok 2432 CREATE SEQUENCE test_seq_2 INCREMENT -1 MINVALUE 5 MAXVALUE 1000 START WITH 15 2433 2434 2435 query TTTTIIITTTTT colnames 2436 SELECT * FROM information_schema.sequences 2437 ---- 2438 sequence_catalog sequence_schema sequence_name data_type numeric_precision numeric_precision_radix numeric_scale start_value minimum_value maximum_value increment cycle_option 2439 test public test_seq bigint 64 2 0 1 1 9223372036854775807 1 NO 2440 test public test_seq_2 bigint 64 2 0 15 5 1000 -1 NO 2441 2442 statement ok 2443 CREATE DATABASE other_db 2444 2445 statement ok 2446 SET DATABASE = other_db 2447 2448 # Sequences in one database can't be seen from another database. 2449 2450 query TTTTIIITTTTT 2451 SELECT * FROM information_schema.sequences 2452 ---- 2453 2454 statement ok 2455 SET DATABASE = test 2456 2457 statement ok 2458 DROP DATABASE other_db CASCADE 2459 2460 # test information_schema.column_privileges 2461 query TTBTTTB colnames 2462 SHOW COLUMNS FROM information_schema.column_privileges 2463 ---- 2464 column_name data_type is_nullable column_default generation_expression indices is_hidden 2465 grantor STRING true NULL · {} false 2466 grantee STRING false NULL · {} false 2467 table_catalog STRING false NULL · {} false 2468 table_schema STRING false NULL · {} false 2469 table_name STRING false NULL · {} false 2470 column_name STRING false NULL · {} false 2471 privilege_type STRING false NULL · {} false 2472 is_grantable STRING true NULL · {} false 2473 2474 2475 # test information_schema.routines 2476 query TTBTTTB colnames 2477 SHOW COLUMNS FROM information_schema.routines 2478 ---- 2479 column_name data_type is_nullable column_default generation_expression indices is_hidden 2480 specific_catalog STRING true NULL · {} false 2481 specific_schema STRING true NULL · {} false 2482 specific_name STRING true NULL · {} false 2483 routine_catalog STRING true NULL · {} false 2484 routine_schema STRING true NULL · {} false 2485 routine_name STRING true NULL · {} false 2486 routine_type STRING true NULL · {} false 2487 module_catalog STRING true NULL · {} false 2488 module_schema STRING true NULL · {} false 2489 module_name STRING true NULL · {} false 2490 udt_catalog STRING true NULL · {} false 2491 udt_schema STRING true NULL · {} false 2492 udt_name STRING true NULL · {} false 2493 data_type STRING true NULL · {} false 2494 character_maximum_length INT8 true NULL · {} false 2495 character_octet_length INT8 true NULL · {} false 2496 character_set_catalog STRING true NULL · {} false 2497 character_set_schema STRING true NULL · {} false 2498 character_set_name STRING true NULL · {} false 2499 collation_catalog STRING true NULL · {} false 2500 collation_schema STRING true NULL · {} false 2501 collation_name STRING true NULL · {} false 2502 numeric_precision INT8 true NULL · {} false 2503 numeric_precision_radix INT8 true NULL · {} false 2504 numeric_scale INT8 true NULL · {} false 2505 datetime_precision INT8 true NULL · {} false 2506 interval_type STRING true NULL · {} false 2507 interval_precision STRING true NULL · {} false 2508 type_udt_catalog STRING true NULL · {} false 2509 type_udt_schema STRING true NULL · {} false 2510 type_udt_name STRING true NULL · {} false 2511 scope_catalog STRING true NULL · {} false 2512 scope_name STRING true NULL · {} false 2513 maximum_cardinality INT8 true NULL · {} false 2514 dtd_identifier STRING true NULL · {} false 2515 routine_body STRING true NULL · {} false 2516 routine_definition STRING true NULL · {} false 2517 external_name STRING true NULL · {} false 2518 external_language STRING true NULL · {} false 2519 parameter_style STRING true NULL · {} false 2520 is_deterministic STRING true NULL · {} false 2521 sql_data_access STRING true NULL · {} false 2522 is_null_call STRING true NULL · {} false 2523 sql_path STRING true NULL · {} false 2524 schema_level_routine STRING true NULL · {} false 2525 max_dynamic_result_sets INT8 true NULL · {} false 2526 is_user_defined_cast STRING true NULL · {} false 2527 is_implicitly_invocable STRING true NULL · {} false 2528 security_type STRING true NULL · {} false 2529 to_sql_specific_catalog STRING true NULL · {} false 2530 to_sql_specific_schema STRING true NULL · {} false 2531 to_sql_specific_name STRING true NULL · {} false 2532 as_locator STRING true NULL · {} false 2533 created TIMESTAMPTZ true NULL · {} false 2534 last_altered TIMESTAMPTZ true NULL · {} false 2535 new_savepoint_level STRING true NULL · {} false 2536 is_udt_dependent STRING true NULL · {} false 2537 result_cast_from_data_type STRING true NULL · {} false 2538 result_cast_as_locator STRING true NULL · {} false 2539 result_cast_char_max_length INT8 true NULL · {} false 2540 result_cast_char_octet_length STRING true NULL · {} false 2541 result_cast_char_set_catalog STRING true NULL · {} false 2542 result_cast_char_set_schema STRING true NULL · {} false 2543 result_cast_char_set_name STRING true NULL · {} false 2544 result_cast_collation_catalog STRING true NULL · {} false 2545 result_cast_collation_schema STRING true NULL · {} false 2546 result_cast_collation_name STRING true NULL · {} false 2547 result_cast_numeric_precision INT8 true NULL · {} false 2548 result_cast_numeric_precision_radix INT8 true NULL · {} false 2549 result_cast_numeric_scale INT8 true NULL · {} false 2550 result_cast_datetime_precision STRING true NULL · {} false 2551 result_cast_interval_type STRING true NULL · {} false 2552 result_cast_interval_precision INT8 true NULL · {} false 2553 result_cast_type_udt_catalog STRING true NULL · {} false 2554 result_cast_type_udt_schema STRING true NULL · {} false 2555 result_cast_type_udt_name STRING true NULL · {} false 2556 result_cast_scope_catalog STRING true NULL · {} false 2557 result_cast_scope_schema STRING true NULL · {} false 2558 result_cast_scope_name STRING true NULL · {} false 2559 result_cast_maximum_cardinality INT8 true NULL · {} false 2560 result_cast_dtd_identifier STRING true NULL · {} false 2561 2562 query TTTTTTTTTTTTTTIITTTTTTIIIITTTTTTTITTTTTTTTTTTITTTTTTTTTTTTTITTTTTTTIIITTITTTTTTIT colnames 2563 SELECT * FROM information_schema.routines 2564 ---- 2565 specific_catalog specific_schema specific_name routine_catalog routine_schema routine_name routine_type module_catalog module_schema module_name udt_catalog udt_schema udt_name data_type character_maximum_length character_octet_length character_set_catalog character_set_schema character_set_name collation_catalog collation_schema collation_name numeric_precision numeric_precision_radix numeric_scale datetime_precision interval_type interval_precision type_udt_catalog type_udt_schema type_udt_name scope_catalog scope_name maximum_cardinality dtd_identifier routine_body routine_definition external_name external_language parameter_style is_deterministic sql_data_access is_null_call sql_path schema_level_routine max_dynamic_result_sets is_user_defined_cast is_implicitly_invocable security_type to_sql_specific_catalog to_sql_specific_schema to_sql_specific_name as_locator created last_altered new_savepoint_level is_udt_dependent result_cast_from_data_type result_cast_as_locator result_cast_char_max_length result_cast_char_octet_length result_cast_char_set_catalog result_cast_char_set_schema result_cast_char_set_name result_cast_collation_catalog result_cast_collation_schema result_cast_collation_name result_cast_numeric_precision result_cast_numeric_precision_radix result_cast_numeric_scale result_cast_datetime_precision result_cast_interval_type result_cast_interval_precision result_cast_type_udt_catalog result_cast_type_udt_schema result_cast_type_udt_name result_cast_scope_catalog result_cast_scope_schema result_cast_scope_name result_cast_maximum_cardinality result_cast_dtd_identifier 2566 2567 # test information_schema.parameters 2568 query TTBTTTB colnames 2569 SHOW COLUMNS FROM information_schema.parameters 2570 ---- 2571 column_name data_type is_nullable column_default generation_expression indices is_hidden 2572 specific_catalog STRING true NULL · {} false 2573 specific_schema STRING true NULL · {} false 2574 specific_name STRING true NULL · {} false 2575 ordinal_position INT8 true NULL · {} false 2576 parameter_mode STRING true NULL · {} false 2577 is_result STRING true NULL · {} false 2578 as_locator STRING true NULL · {} false 2579 parameter_name STRING true NULL · {} false 2580 data_type STRING true NULL · {} false 2581 character_maximum_length INT8 true NULL · {} false 2582 character_octet_length INT8 true NULL · {} false 2583 character_set_catalog STRING true NULL · {} false 2584 character_set_schema STRING true NULL · {} false 2585 character_set_name STRING true NULL · {} false 2586 collation_catalog STRING true NULL · {} false 2587 collation_schema STRING true NULL · {} false 2588 collation_name STRING true NULL · {} false 2589 numeric_precision INT8 true NULL · {} false 2590 numeric_precision_radix INT8 true NULL · {} false 2591 numeric_scale INT8 true NULL · {} false 2592 datetime_precision INT8 true NULL · {} false 2593 interval_type STRING true NULL · {} false 2594 interval_precision INT8 true NULL · {} false 2595 udt_catalog STRING true NULL · {} false 2596 udt_schema STRING true NULL · {} false 2597 udt_name STRING true NULL · {} false 2598 scope_catalog STRING true NULL · {} false 2599 scope_schema STRING true NULL · {} false 2600 scope_name STRING true NULL · {} false 2601 maximum_cardinality INT8 true NULL · {} false 2602 dtd_identifier STRING true NULL · {} false 2603 parameter_default STRING true NULL · {} false 2604 2605 query TTTITTTTTIITTTTTTIIIITITTTTTTITT colnames 2606 SELECT * FROM information_schema.parameters 2607 ---- 2608 specific_catalog specific_schema specific_name ordinal_position parameter_mode is_result as_locator parameter_name data_type character_maximum_length character_octet_length character_set_catalog character_set_schema character_set_name collation_catalog collation_schema collation_name numeric_precision numeric_precision_radix numeric_scale datetime_precision interval_type interval_precision udt_catalog udt_schema udt_name scope_catalog scope_schema scope_name maximum_cardinality dtd_identifier parameter_default 2609 2610 query TTTTTTTT colnames 2611 SELECT * FROM system.information_schema.column_privileges WHERE table_name = 'eventlog' 2612 ---- 2613 grantor grantee table_catalog table_schema table_name column_name privilege_type is_grantable 2614 NULL admin system public eventlog timestamp SELECT NULL 2615 NULL admin system public eventlog eventType SELECT NULL 2616 NULL admin system public eventlog targetID SELECT NULL 2617 NULL admin system public eventlog reportingID SELECT NULL 2618 NULL admin system public eventlog info SELECT NULL 2619 NULL admin system public eventlog uniqueID SELECT NULL 2620 NULL admin system public eventlog timestamp INSERT NULL 2621 NULL admin system public eventlog eventType INSERT NULL 2622 NULL admin system public eventlog targetID INSERT NULL 2623 NULL admin system public eventlog reportingID INSERT NULL 2624 NULL admin system public eventlog info INSERT NULL 2625 NULL admin system public eventlog uniqueID INSERT NULL 2626 NULL admin system public eventlog timestamp UPDATE NULL 2627 NULL admin system public eventlog eventType UPDATE NULL 2628 NULL admin system public eventlog targetID UPDATE NULL 2629 NULL admin system public eventlog reportingID UPDATE NULL 2630 NULL admin system public eventlog info UPDATE NULL 2631 NULL admin system public eventlog uniqueID UPDATE NULL 2632 NULL root system public eventlog timestamp SELECT NULL 2633 NULL root system public eventlog eventType SELECT NULL 2634 NULL root system public eventlog targetID SELECT NULL 2635 NULL root system public eventlog reportingID SELECT NULL 2636 NULL root system public eventlog info SELECT NULL 2637 NULL root system public eventlog uniqueID SELECT NULL 2638 NULL root system public eventlog timestamp INSERT NULL 2639 NULL root system public eventlog eventType INSERT NULL 2640 NULL root system public eventlog targetID INSERT NULL 2641 NULL root system public eventlog reportingID INSERT NULL 2642 NULL root system public eventlog info INSERT NULL 2643 NULL root system public eventlog uniqueID INSERT NULL 2644 NULL root system public eventlog timestamp UPDATE NULL 2645 NULL root system public eventlog eventType UPDATE NULL 2646 NULL root system public eventlog targetID UPDATE NULL 2647 NULL root system public eventlog reportingID UPDATE NULL 2648 NULL root system public eventlog info UPDATE NULL 2649 NULL root system public eventlog uniqueID UPDATE NULL 2650 2651 # information_schema.administrable_role_authorizations 2652 2653 query TTT colnames,rowsort 2654 SELECT * FROM information_schema.administrable_role_authorizations 2655 ---- 2656 grantee role_name is_grantable 2657 root admin YES 2658 2659 user testuser 2660 2661 query TTT colnames,rowsort 2662 SELECT * FROM information_schema.administrable_role_authorizations 2663 ---- 2664 grantee role_name is_grantable 2665 2666 user root 2667 2668 # information_schema.applicable_roles 2669 2670 query TTT colnames,rowsort 2671 SELECT * FROM information_schema.applicable_roles 2672 ---- 2673 grantee role_name is_grantable 2674 root admin YES 2675 2676 user testuser 2677 2678 query TTT colnames,rowsort 2679 SELECT * FROM information_schema.applicable_roles 2680 ---- 2681 grantee role_name is_grantable 2682 2683 user root 2684 2685 # information_schema.enabled_roles 2686 2687 query T colnames,rowsort 2688 SELECT * FROM information_schema.enabled_roles 2689 ---- 2690 role_name 2691 admin 2692 root 2693 2694 user testuser 2695 2696 query T colnames,rowsort 2697 SELECT * FROM information_schema.enabled_roles 2698 ---- 2699 role_name 2700 testuser 2701 2702 user root 2703 2704 subtest fk_match_type 2705 2706 statement ok 2707 CREATE DATABASE dfk; SET database=dfk 2708 2709 statement ok 2710 CREATE TABLE v(x INT, y INT, UNIQUE (x,y)) 2711 2712 statement ok 2713 CREATE TABLE w( 2714 a INT, b INT, c INT, d INT, 2715 FOREIGN KEY (a,b) REFERENCES v(x,y) MATCH FULL, 2716 FOREIGN KEY (c,d) REFERENCES v(x,y) MATCH SIMPLE 2717 ); 2718 2719 query TTTT 2720 SELECT constraint_name, table_name, referenced_table_name, match_option 2721 FROM information_schema.referential_constraints 2722 ---- 2723 fk_a_ref_v w v FULL 2724 fk_c_ref_v w v NONE 2725 2726 statement ok 2727 SET database = test 2728 2729 statement ok 2730 DROP DATABASE dfk CASCADE 2731 2732 # Regression test for #39787. Verify information_schema.ordinal_position 2733 # matches pg_attribute.attnum when a leading column is dropped. 2734 2735 statement ok 2736 CREATE TABLE ab(a INT, b INT) 2737 2738 statement ok 2739 ALTER TABLE ab DROP COLUMN a 2740 2741 let $attnum 2742 SELECT attnum FROM pg_attribute WHERE attrelid = 'ab'::regclass AND attname = 'b' 2743 2744 query I 2745 SELECT 2746 ordinal_position 2747 FROM 2748 information_schema.columns 2749 WHERE 2750 table_name = 'ab' 2751 AND column_name = 'b' 2752 AND ordinal_position = $attnum; 2753 ---- 2754 2 2755 2756 statement ok 2757 DROP TABLE ab