Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qdD5m-002ZO6-EJ for pgsql-hackers@arkaria.postgresql.org; Mon, 04 Sep 2023 17:10:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qdD5k-00DEfT-31 for pgsql-hackers@arkaria.postgresql.org; Mon, 04 Sep 2023 17:10:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qdD5i-00DEf4-UJ for pgsql-hackers@lists.postgresql.org; Mon, 04 Sep 2023 17:10:23 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qdD5Y-0031o7-4G for pgsql-hackers@lists.postgresql.org; Mon, 04 Sep 2023 17:10:22 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id 4D03C5C008C; Mon, 4 Sep 2023 13:10:10 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute4.internal (MEProxy); Mon, 04 Sep 2023 13:10:10 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:message-id:mime-version:reply-to:sender :subject:subject:to:to:x-me-proxy:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm1; t=1693847410; x=1693933810; bh=M qKMswyEWg6eYfLAlGrIjr7P2aPRYPc2nn6jyQpXX10=; b=LzEtHZ+8V5Vjf2Fyx sQThOtGuJpvmH96HTXDSmmEflP1gcHN+hXjXo8S4xf6dK/yMdle+5ElkvAFdPYKD +Atgr5KqNcUKDBD2/PA9wNyOmhEbRTpLFwBDzA2CX94a7bA+o0cDIkrqbnYXX0hd qvJxFvgGCqprMSfs0if/zxJjCHJBNk54xMZkicpVT8A9ChW3q558l7P9nsU7opGX LdMJlK/ERIxB1jw+GVICsOejjdwKTiUb03ynzGGQ09HR0OnV1ovlWaToy6sDDAM7 bBj2x3yZKIjvnDXp5/uxI7XEXwjjC+G5dK19/PUkreNhfnAeIZaeZwQyz+r8ZIfX QY+fw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedviedrudegkedgudduudcutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enucfjughrpeffhffvvefukfggtggugfesmhekreertddtjeenucfhrhhomheptehlvhgr rhhoucfjvghrrhgvrhgruceorghlvhhhvghrrhgvsegrlhhvhhdrnhhoqdhiphdrohhrgh eqnecuggftrfgrthhtvghrnhepveduffejfeehkeethffhjedujeejieelffehtdejhffh ueekveffgfdthfffleeknecuffhomhgrihhnpehpohhsthhgrhdrvghspdgvnhhtvghrph hrihhsvggusgdrtghomhdpthifihhtthgvrhdrtghomhenucevlhhushhtvghrufhiiigv pedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrlhhvhhgvrhhrvgesrghlvhhhrdhnoh dqihhprdhorhhg X-ME-Proxy: Feedback-ID: ia2694551:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 4 Sep 2023 13:10:09 -0400 (EDT) Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 50367426; Mon, 4 Sep 2023 19:10:06 +0200 (CEST) Date: Mon, 4 Sep 2023 19:10:06 +0200 From: Alvaro Herrera To: Pg Hackers Cc: Peter Eisentraut Subject: information_schema and not-null constraints Message-ID: <202309041710.psytrxlsiqex@alvherre.pgsql> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="v4ugctxfhn3h4fso" Content-Disposition: inline Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --v4ugctxfhn3h4fso Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In reference to [1], 0001 attached to this email contains the updated view definitions that I propose. In 0002, I took the tests added by Peter's proposed patch and put them in a separate test file that runs at the end. There are some issues, however. One is that the ORDER BY clause in the check_constraints view is not fully deterministic, because the table name is not part of the view definition, so we cannot sort by table name. In the current regression database there is only one case[2] where two constraints have the same name and different definition: inh_check_constraint │ 2 │ ((f1 > 0)) NOT VALID ↵ │ │ ((f1 > 0)) (on tables invalid_check_con and invalid_check_con_child). I assume this is going to bite us at some point. We could just add a WHERE clause to omit that one constraint. Another issue I notice eyeballing at the results is that foreign keys on partitioned tables are listing the rows used to implement the constraints on partitions, which are sort-of "internal" constraints (and are not displayed by psql's \d). I hope this is a relatively simple fix that we could extract from the code used by psql. Anyway, I think I'm going to get 0001 committed sometime tomorrow, and then play a bit more with 0002 to try and get it pushed soon also. Thanks [1] https://postgr.es/m/81b461c4-edab-5d8c-2f88-203108425340@enterprisedb.com [2] select constraint_name, count(*), string_agg(distinct check_clause, E'\n') from information_schema.check_constraints group by constraint_name having count(*) > 1; -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "You don't solve a bad join with SELECT DISTINCT" #CupsOfFail https://twitter.com/connor_mc_d/status/1431240081726115845 --v4ugctxfhn3h4fso Content-Type: text/x-diff; charset=utf-8 Content-Disposition: attachment; filename="v2-0001-update-information_schema-definition.patch" From d8a5f8103934fe65a83a2ca44f6af72449cb6aa9 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Mon, 4 Sep 2023 18:05:50 +0200 Subject: [PATCH v2 1/2] update information_schema definition --- src/backend/catalog/information_schema.sql | 74 ++++++++-------------- 1 file changed, 27 insertions(+), 47 deletions(-) diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index a06ec7a0a8..c402cca7f4 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -444,22 +444,19 @@ CREATE VIEW check_constraints AS WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE') AND con.contype = 'c' - UNION + UNION ALL -- not-null constraints - - SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, - CAST(n.nspname AS sql_identifier) AS constraint_schema, - CAST(CAST(n.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX - CAST(a.attname || ' IS NOT NULL' AS character_data) - AS check_clause - FROM pg_namespace n, pg_class r, pg_attribute a - WHERE n.oid = r.relnamespace - AND r.oid = a.attrelid - AND a.attnum > 0 - AND NOT a.attisdropped - AND a.attnotnull - AND r.relkind IN ('r', 'p') - AND pg_has_role(r.relowner, 'USAGE'); + SELECT current_database()::information_schema.sql_identifier AS constraint_catalog, + rs.nspname::information_schema.sql_identifier AS constraint_schema, + con.conname::information_schema.sql_identifier AS constraint_name, + format('CHECK (%s IS NOT NULL)', at.attname)::information_schema.character_data AS check_clause + FROM pg_constraint con + LEFT JOIN pg_namespace rs ON rs.oid = con.connamespace + LEFT JOIN pg_class c ON c.oid = con.conrelid + LEFT JOIN pg_type t ON t.oid = con.contypid + LEFT JOIN pg_attribute at ON (con.conrelid = at.attrelid AND con.conkey[1] = at.attnum) + WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE'::text) + AND con.contype = 'n'; GRANT SELECT ON check_constraints TO PUBLIC; @@ -826,6 +823,20 @@ CREATE VIEW constraint_column_usage AS AND r.relkind IN ('r', 'p') AND NOT a.attisdropped + UNION ALL + + /* not-null constraints */ + SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname + FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c + WHERE nr.oid = r.relnamespace + AND r.oid = a.attrelid + AND r.oid = c.conrelid + AND a.attnum = c.conkey[1] + AND c.connamespace = nc.oid + AND c.contype = 'n' + AND r.relkind in ('r', 'p') + AND not a.attisdropped + UNION ALL /* unique/primary key/foreign key constraints */ @@ -1828,6 +1839,7 @@ CREATE VIEW table_constraints AS CAST(r.relname AS sql_identifier) AS table_name, CAST( CASE c.contype WHEN 'c' THEN 'CHECK' + WHEN 'n' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END @@ -1852,38 +1864,6 @@ CREATE VIEW table_constraints AS AND c.contype NOT IN ('t', 'x') -- ignore nonstandard constraints AND r.relkind IN ('r', 'p') AND (NOT pg_is_other_temp_schema(nr.oid)) - AND (pg_has_role(r.relowner, 'USAGE') - -- SELECT privilege omitted, per SQL standard - OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') - OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') ) - - UNION ALL - - -- not-null constraints - - SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, - CAST(nr.nspname AS sql_identifier) AS constraint_schema, - CAST(CAST(nr.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX - CAST(current_database() AS sql_identifier) AS table_catalog, - CAST(nr.nspname AS sql_identifier) AS table_schema, - CAST(r.relname AS sql_identifier) AS table_name, - CAST('CHECK' AS character_data) AS constraint_type, - CAST('NO' AS yes_or_no) AS is_deferrable, - CAST('NO' AS yes_or_no) AS initially_deferred, - CAST('YES' AS yes_or_no) AS enforced, - CAST(NULL AS yes_or_no) AS nulls_distinct - - FROM pg_namespace nr, - pg_class r, - pg_attribute a - - WHERE nr.oid = r.relnamespace - AND r.oid = a.attrelid - AND a.attnotnull - AND a.attnum > 0 - AND NOT a.attisdropped - AND r.relkind IN ('r', 'p') - AND (NOT pg_is_other_temp_schema(nr.oid)) AND (pg_has_role(r.relowner, 'USAGE') -- SELECT privilege omitted, per SQL standard OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') -- 2.39.2 --v4ugctxfhn3h4fso Content-Type: text/x-diff; charset=utf-8 Content-Disposition: attachment; filename="v2-0002-add-information_schema-test.patch" From b0c39ecaf6c4a1179d45fa9de7876b173c92d6ca Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Mon, 4 Sep 2023 18:05:33 +0200 Subject: [PATCH v2 2/2] add information_schema test --- .../regress/expected/information_schema.out | 691 ++++++++++++++++++ src/test/regress/parallel_schedule | 5 +- src/test/regress/sql/information_schema.sql | 41 ++ 3 files changed, 735 insertions(+), 2 deletions(-) create mode 100644 src/test/regress/expected/information_schema.out create mode 100644 src/test/regress/sql/information_schema.sql diff --git a/src/test/regress/expected/information_schema.out b/src/test/regress/expected/information_schema.out new file mode 100644 index 0000000000..0b7b93eb69 --- /dev/null +++ b/src/test/regress/expected/information_schema.out @@ -0,0 +1,691 @@ +-- We test the information schema last, so that we examine +-- database state at the end of the regression test run. +-- +-- Table constraints +-- +SELECT * FROM information_schema.check_constraints + WHERE constraint_schema = 'public' + ORDER BY constraint_name; + constraint_catalog | constraint_schema | constraint_name | check_clause +--------------------+-------------------+--------------------------------------------+----------------------------------------------------------------------- + regression | public | atnotnull1_a_not_null | CHECK (a IS NOT NULL) + regression | public | atnotnull1_b_not_null | CHECK (b IS NOT NULL) + regression | public | bar1_b_not_null | CHECK (b IS NOT NULL) + regression | public | bar2_b_not_null | CHECK (b IS NOT NULL) + regression | public | blocal | (((b)::double precision < (1000)::double precision)) + regression | public | bmerged | (((b)::double precision > (1)::double precision)) + regression | public | bmerged | (((b)::double precision > (1)::double precision)) + regression | public | bnoinherit | (((b)::double precision > (100)::double precision)) NO INHERIT + regression | public | boolvalues_value_not_null | CHECK (value IS NOT NULL) + regression | public | brinopers_bloom_check | ((cardinality(op) = cardinality(value))) + regression | public | brinopers_bloom_check1 | ((cardinality(op) = cardinality(matches))) + regression | public | brinopers_check | ((cardinality(op) = cardinality(value))) + regression | public | brinopers_check1 | ((cardinality(op) = cardinality(matches))) + regression | public | brinopers_multi_check | ((cardinality(op) = cardinality(value))) + regression | public | brinopers_multi_check1 | ((cardinality(op) = cardinality(matches))) + regression | public | cc1_f1_not_null | CHECK (f1 IS NOT NULL) + regression | public | cc1_f1_not_null | CHECK (f1 IS NOT NULL) + regression | public | check_b | (((b IS NULL) OR (b = 'a'::bpchar))) + regression | public | check_con | ((x > 3)) + regression | public | check_con_tbl_check | (check_con_function(check_con_tbl.*)) + regression | public | chk | ((b < 50)) NOT VALID + regression | public | clstr_tst_a_not_null | CHECK (a IS NOT NULL) + regression | public | clstr_tst_a_not_null | CHECK (a IS NOT NULL) + regression | public | clstr_tst_s_rf_a_not_null | CHECK (rf_a IS NOT NULL) + regression | public | cnn_child2_a_not_null | CHECK (a IS NOT NULL) + regression | public | cnn_child2_b_not_null | CHECK (b IS NOT NULL) + regression | public | cnn_child_b_not_null | CHECK (b IS NOT NULL) + regression | public | cnn_grandchild_b_not_null | CHECK (b IS NOT NULL) + regression | public | cnn_grandchild_b_not_null | CHECK (b IS NOT NULL) + regression | public | comment_test_positive_col_check | ((positive_col > 0)) + regression | public | con_check | ((VALUE > 0)) + regression | public | copy_con | (((x > 3) AND (y <> 'check failed'::text) AND (x < 7))) + regression | public | customer_name_not_null | CHECK (name IS NOT NULL) + regression | public | foo | ((xx = 'text'::text)) + regression | public | gtest1_1_a_not_null | CHECK (a IS NOT NULL) + regression | public | gtest1_y_a_not_null | CHECK (a IS NOT NULL) + regression | public | gtest20_b_check | ((b < 50)) + regression | public | gtest21a_b_not_null | CHECK (b IS NOT NULL) + regression | public | gtest_child3_f1_not_null | CHECK (f1 IS NOT NULL) + regression | public | gtest_parent_f1_not_null | CHECK (f1 IS NOT NULL) + regression | public | gtest_parent_f1_not_null | CHECK (f1 IS NOT NULL) + regression | public | gtest_parent_f1_not_null | CHECK (f1 IS NOT NULL) + regression | public | gtestdomain1_check | ((VALUE < 10)) + regression | public | gtestx_a_not_null | CHECK (a IS NOT NULL) + regression | public | gtestxx_1_a_not_null | CHECK (a IS NOT NULL) + regression | public | gtestxx_3_a_not_null | CHECK (a IS NOT NULL) + regression | public | gtestxx_4_a_not_null | CHECK (a IS NOT NULL) + regression | public | inh_check_constraint | ((f1 > 0)) NOT VALID + regression | public | inh_check_constraint | ((f1 > 0)) + regression | public | insert_tbl_check | (((x + z) = 0)) + regression | public | insert_tbl_con | (((x >= 3) AND (y <> 'check failed'::text) AND (x < 8))) + regression | public | itest10_a_not_null | CHECK (a IS NOT NULL) + regression | public | itest11_a_not_null | CHECK (a IS NOT NULL) + regression | public | itest13_b_not_null | CHECK (b IS NOT NULL) + regression | public | itest13_c_not_null | CHECK (c IS NOT NULL) + regression | public | itest14_id_not_null | CHECK (id IS NOT NULL) + regression | public | itest1_a_not_null | CHECK (a IS NOT NULL) + regression | public | itest2_a_not_null | CHECK (a IS NOT NULL) + regression | public | itest3_a_not_null | CHECK (a IS NOT NULL) + regression | public | itest3_c_not_null | CHECK (c IS NOT NULL) + regression | public | itest5_a_not_null | CHECK (a IS NOT NULL) + regression | public | itest6_a_not_null | CHECK (a IS NOT NULL) + regression | public | itest7_a_not_null | CHECK (a IS NOT NULL) + regression | public | itest7_a_not_null | CHECK (a IS NOT NULL) + regression | public | itest7c_a_not_null | CHECK (a IS NOT NULL) + regression | public | itest7d_a_not_null | CHECK (a IS NOT NULL) + regression | public | itest7d_a_not_null | CHECK (a IS NOT NULL) + regression | public | itest9_a_not_null | CHECK (a IS NOT NULL) + regression | public | list_parted2_b_not_null | CHECK (b IS NOT NULL) + regression | public | meow | ((VALUE < 11)) + regression | public | mlparted11_a_not_null | CHECK (a IS NOT NULL) + regression | public | mlparted1_a_not_null | CHECK (a IS NOT NULL) + regression | public | mlparted1_a_not_null | CHECK (a IS NOT NULL) + regression | public | mlparted1_b_not_null | CHECK (b IS NOT NULL) + regression | public | mlparted1_b_not_null | CHECK (b IS NOT NULL) + regression | public | mlparted1_b_not_null | CHECK (b IS NOT NULL) + regression | public | mlparted2_a_not_null | CHECK (a IS NOT NULL) + regression | public | mlparted2_b_not_null | CHECK (b IS NOT NULL) + regression | public | mlparted4_a_not_null | CHECK (a IS NOT NULL) + regression | public | mvtest_t_amt_not_null | CHECK (amt IS NOT NULL) + regression | public | mvtest_t_id_not_null | CHECK (id IS NOT NULL) + regression | public | mvtest_t_type_not_null | CHECK (type IS NOT NULL) + regression | public | notnull_tbl3_a_check | ((a IS NOT NULL)) + regression | public | nv_child_2009_d_check | (((d >= '01-01-2009'::date) AND (d <= '12-31-2009'::date))) + regression | public | nv_child_2010_d_check | (((d >= '01-01-2010'::date) AND (d <= '12-31-2010'::date))) NOT VALID + regression | public | nv_child_2011_d_check | (((d >= '01-01-2011'::date) AND (d <= '12-31-2011'::date))) + regression | public | nv_parent_check | (false) NO INHERIT + regression | public | nv_parent_d_check | (((d >= '01-01-2001'::date) AND (d <= '12-31-2099'::date))) NOT VALID + regression | public | nv_parent_d_check | (((d >= '01-01-2001'::date) AND (d <= '12-31-2099'::date))) NOT VALID + regression | public | nv_parent_d_check | (((d >= '01-01-2001'::date) AND (d <= '12-31-2099'::date))) NOT VALID + regression | public | nv_parent_d_check | (((d >= '01-01-2001'::date) AND (d <= '12-31-2099'::date))) NOT VALID + regression | public | orderedpair_check | (((VALUE)[1] < (VALUE)[2])) + regression | public | part1_a_check | ((a = 1)) + regression | public | part1_a_not_null | CHECK (a IS NOT NULL) + regression | public | part1_b_check | (((b >= 1) AND (b <= 10))) + regression | public | part1_b_not_null | CHECK (b IS NOT NULL) + regression | public | part1_self_fk_id_not_null | CHECK (id IS NOT NULL) + regression | public | part33_self_fk_id_not_null | CHECK (id IS NOT NULL) + regression | public | part3_self_fk_id_not_null | CHECK (id IS NOT NULL) + regression | public | part3_self_fk_id_not_null | CHECK (id IS NOT NULL) + regression | public | part_rp100_a_check | (((a >= 123) AND (a < 133) AND (a IS NOT NULL))) + regression | public | part_rp_a_check | (((a IS NOT NULL) AND (a >= 0) AND (a < 100))) + regression | public | parted_self_fk_id_not_null | CHECK (id IS NOT NULL) + regression | public | parted_self_fk_id_not_null | CHECK (id IS NOT NULL) + regression | public | persons3_name_not_null | CHECK (name IS NOT NULL) + regression | public | plpgsql_arr_domain_check | (plpgsql_arr_domain_check(VALUE)) + regression | public | plpgsql_domain_check | (plpgsql_domain_check(VALUE)) + regression | public | pos_int_check | ((VALUE > 0)) + regression | public | reservations_room_id_not_null | CHECK (room_id IS NOT NULL) + regression | public | rules_log_id_not_null | CHECK (id IS NOT NULL) + regression | public | sequence_con | (((x > 3) AND (y <> 'check failed'::text) AND (z < 8))) + regression | public | sorted | (((VALUE)[1] < (VALUE)[2])) + regression | public | spgist_box_tbl_id_not_null | CHECK (id IS NOT NULL) + regression | public | spgist_unlogged_tbl_id_not_null | CHECK (id IS NOT NULL) + regression | public | str_domain2_check | ((VALUE <> 'foo'::text)) + regression | public | test_inh_check_a_check | (((a)::double precision > (10.2)::double precision)) + regression | public | test_inh_check_a_check | (((a)::double precision > (10.2)::double precision)) + regression | public | transition_table_level1_level1_no_not_null | CHECK (level1_no IS NOT NULL) + regression | public | transition_table_level2_level2_no_not_null | CHECK (level2_no IS NOT NULL) + regression | public | transition_table_level2_parent_no_not_null | CHECK (parent_no IS NOT NULL) + regression | public | transition_table_status_level_not_null | CHECK (level IS NOT NULL) + regression | public | transition_table_status_node_no_not_null | CHECK (node_no IS NOT NULL) + regression | public | truncate_b_id_not_null | CHECK (id IS NOT NULL) + regression | public | tt0_x_not_null | CHECK (x IS NOT NULL) + regression | public | tt0_x_not_null | CHECK (x IS NOT NULL) +(125 rows) + +SELECT * FROM information_schema.constraint_column_usage + WHERE constraint_schema = 'public' + ORDER BY table_name, column_name, constraint_name; + table_catalog | table_schema | table_name | column_name | constraint_catalog | constraint_schema | constraint_name +---------------+--------------+-------------------------------------+--------------+--------------------+-------------------+-------------------------------------------- + regression | public | alter_table_under_transition_tables | id | regression | public | alter_table_under_transition_tables_pkey + regression | public | atnotnull1 | a | regression | public | atnotnull1_a_not_null + regression | public | atnotnull1 | b | regression | public | atnotnull1_b_not_null + regression | public | atnotnull1 | c | regression | public | atnotnull1_pkey + regression | public | bar1 | b | regression | public | bar1_b_not_null + regression | public | bar2 | b | regression | public | bar2_b_not_null + regression | public | boolvalues | value | regression | public | boolvalues_value_not_null + regression | public | brinopers | matches | regression | public | brinopers_check1 + regression | public | brinopers | op | regression | public | brinopers_check + regression | public | brinopers | op | regression | public | brinopers_check1 + regression | public | brinopers | value | regression | public | brinopers_check + regression | public | brinopers_bloom | matches | regression | public | brinopers_bloom_check1 + regression | public | brinopers_bloom | op | regression | public | brinopers_bloom_check + regression | public | brinopers_bloom | op | regression | public | brinopers_bloom_check1 + regression | public | brinopers_bloom | value | regression | public | brinopers_bloom_check + regression | public | brinopers_multi | matches | regression | public | brinopers_multi_check1 + regression | public | brinopers_multi | op | regression | public | brinopers_multi_check + regression | public | brinopers_multi | op | regression | public | brinopers_multi_check1 + regression | public | brinopers_multi | value | regression | public | brinopers_multi_check + regression | public | cc1 | f1 | regression | public | cc1_f1_not_null + regression | public | cc2 | f1 | regression | public | cc1_f1_not_null + regression | public | check2_tbl | x | regression | public | sequence_con + regression | public | check2_tbl | y | regression | public | sequence_con + regression | public | check2_tbl | z | regression | public | sequence_con + regression | public | check_tbl | x | regression | public | check_con + regression | public | clstr_tst | a | regression | public | clstr_tst_a_not_null + regression | public | clstr_tst | a | regression | public | clstr_tst_pkey + regression | public | clstr_tst_inh | a | regression | public | clstr_tst_a_not_null + regression | public | clstr_tst_s | rf_a | regression | public | clstr_tst_con + regression | public | clstr_tst_s | rf_a | regression | public | clstr_tst_s_pkey + regression | public | clstr_tst_s | rf_a | regression | public | clstr_tst_s_rf_a_not_null + regression | public | cnn_child | b | regression | public | cnn_child_b_not_null + regression | public | cnn_child2 | a | regression | public | cnn_child2_a_not_null + regression | public | cnn_child2 | b | regression | public | cnn_child2_b_not_null + regression | public | cnn_grandchild | b | regression | public | cnn_grandchild_b_not_null + regression | public | cnn_grandchild2 | b | regression | public | cnn_grandchild_b_not_null + regression | public | cnn_parent | b | regression | public | b_uq + regression | public | comment_test | id | regression | public | comment_test_child_fk + regression | public | comment_test | id | regression | public | comment_test_pk + regression | public | comment_test | positive_col | regression | public | comment_test_positive_col_check + regression | public | copy_tbl | x | regression | public | copy_con + regression | public | copy_tbl | y | regression | public | copy_con + regression | public | customer | cid | regression | public | credit_card_cid_fkey + regression | public | customer | cid | regression | public | credit_usage_cid_fkey + regression | public | customer | cid | regression | public | customer_pkey + regression | public | customer | name | regression | public | customer_name_not_null + regression | public | delete_test_table | a | regression | public | delete_test_table_pkey + regression | public | delete_test_table | b | regression | public | delete_test_table_pkey + regression | public | delete_test_table | c | regression | public | delete_test_table_pkey + regression | public | delete_test_table | d | regression | public | delete_test_table_pkey + regression | public | ec0 | ff | regression | public | ec0_pkey + regression | public | ec1 | ff | regression | public | ec1_pkey + regression | public | ec2 | xf | regression | public | ec2_pkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_notpartitioned_pk_pkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_4_2_a_b_fkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_4_a_b_fkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_4_a_b_fkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_4_a_b_fkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_5_1_a_b_fkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_5_a_b_fkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_5_a_b_fkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_5_a_b_fkey1 + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_5_a_b_fkey1 + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | fk_notpartitioned_pk | a | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_notpartitioned_pk_pkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_4_2_a_b_fkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_4_a_b_fkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_4_a_b_fkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_4_a_b_fkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_5_1_a_b_fkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_5_a_b_fkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_5_a_b_fkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_5_a_b_fkey1 + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_5_a_b_fkey1 + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | fk_notpartitioned_pk | b | regression | public | fk_partitioned_fk_a_b_fkey + regression | public | gtest0 | a | regression | public | gtest0_pkey + regression | public | gtest1 | a | regression | public | gtest1_pkey + regression | public | gtest10 | a | regression | public | gtest10_pkey + regression | public | gtest10a | a | regression | public | gtest10a_pkey + regression | public | gtest1_1 | a | regression | public | gtest1_1_a_not_null + regression | public | gtest1_y | a | regression | public | gtest1_y_a_not_null + regression | public | gtest2 | a | regression | public | gtest2_pkey + regression | public | gtest20 | a | regression | public | gtest20_pkey + regression | public | gtest20 | b | regression | public | gtest20_b_check + regression | public | gtest20a | a | regression | public | gtest20a_pkey + regression | public | gtest20b | a | regression | public | gtest20b_pkey + regression | public | gtest20b | b | regression | public | chk + regression | public | gtest21a | a | regression | public | gtest21a_pkey + regression | public | gtest21a | b | regression | public | gtest21a_b_not_null + regression | public | gtest21b | a | regression | public | gtest21b_pkey + regression | public | gtest22a | a | regression | public | gtest22a_pkey + regression | public | gtest22a | b | regression | public | gtest22a_b_key + regression | public | gtest22b | a | regression | public | gtest22b_pkey + regression | public | gtest22b | b | regression | public | gtest22b_pkey + regression | public | gtest23p | y | regression | public | gtest23p_pkey + regression | public | gtest23p | y | regression | public | gtest23q_b_fkey + regression | public | gtest23q | a | regression | public | gtest23q_pkey + regression | public | gtest24 | a | regression | public | gtest24_pkey + regression | public | gtest25 | a | regression | public | gtest25_pkey + regression | public | gtest26 | a | regression | public | gtest26_pkey + regression | public | gtest_child | f1 | regression | public | gtest_parent_f1_not_null + regression | public | gtest_child2 | f1 | regression | public | gtest_parent_f1_not_null + regression | public | gtest_child3 | f1 | regression | public | gtest_child3_f1_not_null + regression | public | gtest_parent | f1 | regression | public | gtest_parent_f1_not_null + regression | public | gtest_tableoid | a | regression | public | gtest_tableoid_pkey + regression | public | gtestx | a | regression | public | gtestx_a_not_null + regression | public | gtestxx_1 | a | regression | public | gtestxx_1_a_not_null + regression | public | gtestxx_3 | a | regression | public | gtestxx_3_a_not_null + regression | public | gtestxx_4 | a | regression | public | gtestxx_4_a_not_null + regression | public | idxpart_another | a | regression | public | idxpart_another_pkey + regression | public | idxpart_another | b | regression | public | idxpart_another_pkey + regression | public | idxpart_another_1 | a | regression | public | idxpart_another_1_pkey + regression | public | idxpart_another_1 | b | regression | public | idxpart_another_1_pkey + regression | public | inh_test | b | regression | public | list_parted2_b_not_null + regression | public | inhx | xx | regression | public | foo + regression | public | inhx | xx | regression | public | inhx_pkey + regression | public | insert_tbl | x | regression | public | insert_tbl_check + regression | public | insert_tbl | x | regression | public | insert_tbl_con + regression | public | insert_tbl | y | regression | public | insert_tbl_con + regression | public | insert_tbl | z | regression | public | insert_tbl_check + regression | public | invalid_check_con | f1 | regression | public | inh_check_constraint + regression | public | invalid_check_con_child | f1 | regression | public | inh_check_constraint + regression | public | itest1 | a | regression | public | itest1_a_not_null + regression | public | itest10 | a | regression | public | itest10_a_not_null + regression | public | itest11 | a | regression | public | itest11_a_not_null + regression | public | itest13 | b | regression | public | itest13_b_not_null + regression | public | itest13 | c | regression | public | itest13_c_not_null + regression | public | itest14 | id | regression | public | itest14_id_not_null + regression | public | itest2 | a | regression | public | itest2_a_not_null + regression | public | itest3 | a | regression | public | itest3_a_not_null + regression | public | itest3 | c | regression | public | itest3_c_not_null + regression | public | itest5 | a | regression | public | itest5_a_not_null + regression | public | itest6 | a | regression | public | itest6_a_not_null + regression | public | itest7 | a | regression | public | itest7_a_not_null + regression | public | itest7a | a | regression | public | itest7_a_not_null + regression | public | itest7c | a | regression | public | itest7c_a_not_null + regression | public | itest7d | a | regression | public | itest7d_a_not_null + regression | public | itest7e | a | regression | public | itest7d_a_not_null + regression | public | itest9 | a | regression | public | itest9_a_not_null + regression | public | mlparted1 | a | regression | public | mlparted1_a_not_null + regression | public | mlparted1 | b | regression | public | mlparted1_b_not_null + regression | public | mlparted11 | a | regression | public | mlparted11_a_not_null + regression | public | mlparted11 | b | regression | public | mlparted1_b_not_null + regression | public | mlparted12 | a | regression | public | mlparted1_a_not_null + regression | public | mlparted12 | b | regression | public | mlparted1_b_not_null + regression | public | mlparted2 | a | regression | public | mlparted2_a_not_null + regression | public | mlparted2 | b | regression | public | mlparted2_b_not_null + regression | public | mlparted4 | a | regression | public | mlparted4_a_not_null + regression | public | mvtest_t | amt | regression | public | mvtest_t_amt_not_null + regression | public | mvtest_t | id | regression | public | mvtest_t_id_not_null + regression | public | mvtest_t | id | regression | public | mvtest_t_pkey + regression | public | mvtest_t | type | regression | public | mvtest_t_type_not_null + regression | public | notnull_tbl2 | a | regression | public | notnull_tbl2_pkey + regression | public | notnull_tbl3 | a | regression | public | notnull_tbl3_a_check + regression | public | nv_child_2009 | d | regression | public | nv_child_2009_d_check + regression | public | nv_child_2009 | d | regression | public | nv_parent_d_check + regression | public | nv_child_2010 | d | regression | public | nv_child_2010_d_check + regression | public | nv_child_2010 | d | regression | public | nv_parent_d_check + regression | public | nv_child_2011 | d | regression | public | nv_child_2011_d_check + regression | public | nv_child_2011 | d | regression | public | nv_parent_d_check + regression | public | nv_parent | d | regression | public | nv_parent_d_check + regression | public | part1_self_fk | id | regression | public | part1_self_fk_id_not_null + regression | public | part1_self_fk | id | regression | public | part1_self_fk_pkey + regression | public | part2_self_fk | id | regression | public | part2_self_fk_pkey + regression | public | part2_self_fk | id | regression | public | parted_self_fk_id_not_null + regression | public | part32_self_fk | id | regression | public | part32_self_fk_pkey + regression | public | part32_self_fk | id | regression | public | part3_self_fk_id_not_null + regression | public | part33_self_fk | id | regression | public | part33_self_fk_id_not_null + regression | public | part33_self_fk | id | regression | public | part33_self_fk_pkey + regression | public | part3_self_fk | id | regression | public | part3_self_fk_id_not_null + regression | public | part3_self_fk | id | regression | public | part3_self_fk_pkey + regression | public | part_7_a_null | b | regression | public | check_b + regression | public | part_rp | a | regression | public | part_rp_a_check + regression | public | part_rp100 | a | regression | public | part_rp100_a_check + regression | public | parted_self_fk | id | regression | public | parted_self_fk_id_abc_fkey + regression | public | parted_self_fk | id | regression | public | parted_self_fk_id_abc_fkey + regression | public | parted_self_fk | id | regression | public | parted_self_fk_id_abc_fkey + regression | public | parted_self_fk | id | regression | public | parted_self_fk_id_abc_fkey + regression | public | parted_self_fk | id | regression | public | parted_self_fk_id_abc_fkey + regression | public | parted_self_fk | id | regression | public | parted_self_fk_id_abc_fkey + regression | public | parted_self_fk | id | regression | public | parted_self_fk_id_not_null + regression | public | parted_self_fk | id | regression | public | parted_self_fk_pkey + regression | public | partr_def2 | a | regression | public | part1_a_check + regression | public | partr_def2 | a | regression | public | part1_a_not_null + regression | public | partr_def2 | b | regression | public | part1_b_check + regression | public | partr_def2 | b | regression | public | part1_b_not_null + regression | public | persons2 | id | regression | public | persons2_pkey + regression | public | persons2 | name | regression | public | persons2_name_key + regression | public | persons3 | id | regression | public | persons3_pkey + regression | public | persons3 | name | regression | public | persons3_name_not_null + regression | public | pp1 | f1 | regression | public | pp1_pkey + regression | public | reservations | room_id | regression | public | reservations_room_id_not_null + regression | public | rule_and_refint_t1 | id1a | regression | public | rule_and_refint_t1_pkey + regression | public | rule_and_refint_t1 | id1a | regression | public | rule_and_refint_t3_id3a_id3b_fkey + regression | public | rule_and_refint_t1 | id1b | regression | public | rule_and_refint_t1_pkey + regression | public | rule_and_refint_t1 | id1b | regression | public | rule_and_refint_t3_id3a_id3b_fkey + regression | public | rule_and_refint_t2 | id2a | regression | public | rule_and_refint_t2_pkey + regression | public | rule_and_refint_t2 | id2a | regression | public | rule_and_refint_t3_id3a_id3c_fkey + regression | public | rule_and_refint_t2 | id2c | regression | public | rule_and_refint_t2_pkey + regression | public | rule_and_refint_t2 | id2c | regression | public | rule_and_refint_t3_id3a_id3c_fkey + regression | public | rule_and_refint_t3 | id3a | regression | public | rule_and_refint_t3_pkey + regression | public | rule_and_refint_t3 | id3b | regression | public | rule_and_refint_t3_pkey + regression | public | rule_and_refint_t3 | id3c | regression | public | rule_and_refint_t3_pkey + regression | public | rules_log | id | regression | public | rules_log_id_not_null + regression | public | skip_wal_skip_rewrite_index | c | regression | public | skip_wal_skip_rewrite_index_pkey + regression | public | spgist_box_tbl | id | regression | public | spgist_box_tbl_id_not_null + regression | public | spgist_unlogged_tbl | id | regression | public | spgist_unlogged_tbl_id_not_null + regression | public | tbl_include_box | c1 | regression | public | tbl_include_box_idx_unique + regression | public | tbl_include_box | c2 | regression | public | tbl_include_box_idx_unique + regression | public | tbl_include_pk | c1 | regression | public | tbl_include_pk_pkey + regression | public | tbl_include_pk | c2 | regression | public | tbl_include_pk_pkey + regression | public | tbl_include_unique1 | c1 | regression | public | tbl_include_unique1_c1_c2_c3_c4_key + regression | public | tbl_include_unique1 | c1 | regression | public | tbl_include_unique1_idx_unique + regression | public | tbl_include_unique1 | c2 | regression | public | tbl_include_unique1_c1_c2_c3_c4_key + regression | public | tbl_include_unique1 | c2 | regression | public | tbl_include_unique1_idx_unique + regression | public | test_inh_check | a | regression | public | test_inh_check_a_check + regression | public | test_inh_check | b | regression | public | bmerged + regression | public | test_inh_check | b | regression | public | bnoinherit + regression | public | test_inh_check_child | a | regression | public | test_inh_check_a_check + regression | public | test_inh_check_child | b | regression | public | blocal + regression | public | test_inh_check_child | b | regression | public | bmerged + regression | public | transition_table_base | id | regression | public | transition_table_base_pkey + regression | public | transition_table_level1 | level1_no | regression | public | transition_table_level1_level1_no_not_null + regression | public | transition_table_level1 | level1_no | regression | public | transition_table_level1_pkey + regression | public | transition_table_level2 | level2_no | regression | public | transition_table_level2_level2_no_not_null + regression | public | transition_table_level2 | level2_no | regression | public | transition_table_level2_pkey + regression | public | transition_table_level2 | parent_no | regression | public | transition_table_level2_parent_no_not_null + regression | public | transition_table_status | level | regression | public | transition_table_status_level_not_null + regression | public | transition_table_status | level | regression | public | transition_table_status_pkey + regression | public | transition_table_status | node_no | regression | public | transition_table_status_node_no_not_null + regression | public | transition_table_status | node_no | regression | public | transition_table_status_pkey + regression | public | trigger_parted | a | regression | public | trigger_parted_pkey + regression | public | trigger_parted_p1 | a | regression | public | trigger_parted_p1_pkey + regression | public | trigger_parted_p1_1 | a | regression | public | trigger_parted_p1_1_pkey + regression | public | trigger_parted_p2 | a | regression | public | trigger_parted_p2_pkey + regression | public | trigger_parted_p2_2 | a | regression | public | trigger_parted_p2_2_pkey + regression | public | truncate_b | id | regression | public | truncate_b_id_not_null + regression | public | tt0 | x | regression | public | tt0_x_not_null + regression | public | tt6 | x | regression | public | tt0_x_not_null + regression | public | utf8_verification_inputs | description | regression | public | utf8_verification_inputs_pkey + regression | public | view_base_table | key | regression | public | view_base_table_pkey +(255 rows) + +SELECT * FROM information_schema.table_constraints + WHERE constraint_schema = 'public' + ORDER BY table_name, constraint_name; + constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | constraint_type | is_deferrable | initially_deferred | enforced | nulls_distinct +--------------------+-------------------+--------------------------------------------+---------------+--------------+-------------------------------------+-----------------+---------------+--------------------+----------+---------------- + regression | public | alter_table_under_transition_tables_pkey | regression | public | alter_table_under_transition_tables | PRIMARY KEY | NO | NO | YES | + regression | public | atnotnull1_a_not_null | regression | public | atnotnull1 | CHECK | NO | NO | YES | + regression | public | atnotnull1_b_not_null | regression | public | atnotnull1 | CHECK | NO | NO | YES | + regression | public | atnotnull1_pkey | regression | public | atnotnull1 | PRIMARY KEY | NO | NO | YES | + regression | public | bar1_b_not_null | regression | public | bar1 | CHECK | NO | NO | YES | + regression | public | bar2_b_not_null | regression | public | bar2 | CHECK | NO | NO | YES | + regression | public | boolvalues_value_not_null | regression | public | boolvalues | CHECK | NO | NO | YES | + regression | public | brinopers_check | regression | public | brinopers | CHECK | NO | NO | YES | + regression | public | brinopers_check1 | regression | public | brinopers | CHECK | NO | NO | YES | + regression | public | brinopers_bloom_check | regression | public | brinopers_bloom | CHECK | NO | NO | YES | + regression | public | brinopers_bloom_check1 | regression | public | brinopers_bloom | CHECK | NO | NO | YES | + regression | public | brinopers_multi_check | regression | public | brinopers_multi | CHECK | NO | NO | YES | + regression | public | brinopers_multi_check1 | regression | public | brinopers_multi | CHECK | NO | NO | YES | + regression | public | cc1_f1_not_null | regression | public | cc1 | CHECK | NO | NO | YES | + regression | public | cc1_f1_not_null | regression | public | cc2 | CHECK | NO | NO | YES | + regression | public | sequence_con | regression | public | check2_tbl | CHECK | NO | NO | YES | + regression | public | check_con_tbl_check | regression | public | check_con_tbl | CHECK | NO | NO | YES | + regression | public | check_con | regression | public | check_tbl | CHECK | NO | NO | YES | + regression | public | clstr_tst_a_not_null | regression | public | clstr_tst | CHECK | NO | NO | YES | + regression | public | clstr_tst_con | regression | public | clstr_tst | FOREIGN KEY | NO | NO | YES | + regression | public | clstr_tst_pkey | regression | public | clstr_tst | PRIMARY KEY | NO | NO | YES | + regression | public | clstr_tst_a_not_null | regression | public | clstr_tst_inh | CHECK | NO | NO | YES | + regression | public | clstr_tst_s_pkey | regression | public | clstr_tst_s | PRIMARY KEY | NO | NO | YES | + regression | public | clstr_tst_s_rf_a_not_null | regression | public | clstr_tst_s | CHECK | NO | NO | YES | + regression | public | cnn_child_b_not_null | regression | public | cnn_child | CHECK | NO | NO | YES | + regression | public | cnn_child2_a_not_null | regression | public | cnn_child2 | CHECK | NO | NO | YES | + regression | public | cnn_child2_b_not_null | regression | public | cnn_child2 | CHECK | NO | NO | YES | + regression | public | cnn_grandchild_b_not_null | regression | public | cnn_grandchild | CHECK | NO | NO | YES | + regression | public | cnn_grandchild_b_not_null | regression | public | cnn_grandchild2 | CHECK | NO | NO | YES | + regression | public | b_uq | regression | public | cnn_parent | PRIMARY KEY | NO | NO | YES | + regression | public | comment_test_pk | regression | public | comment_test | PRIMARY KEY | NO | NO | YES | + regression | public | comment_test_positive_col_check | regression | public | comment_test | CHECK | NO | NO | YES | + regression | public | comment_test_child_fk | regression | public | comment_test_child | FOREIGN KEY | NO | NO | YES | + regression | public | copy_con | regression | public | copy_tbl | CHECK | NO | NO | YES | + regression | public | credit_card_cid_fkey | regression | public | credit_card | FOREIGN KEY | NO | NO | YES | + regression | public | credit_usage_cid_fkey | regression | public | credit_usage | FOREIGN KEY | NO | NO | YES | + regression | public | customer_name_not_null | regression | public | customer | CHECK | NO | NO | YES | + regression | public | customer_pkey | regression | public | customer | PRIMARY KEY | NO | NO | YES | + regression | public | delete_test_table_pkey | regression | public | delete_test_table | PRIMARY KEY | NO | NO | YES | + regression | public | ec0_pkey | regression | public | ec0 | PRIMARY KEY | NO | NO | YES | + regression | public | ec1_pkey | regression | public | ec1 | PRIMARY KEY | NO | NO | YES | + regression | public | ec2_pkey | regression | public | ec2 | PRIMARY KEY | NO | NO | YES | + regression | public | fk_notpartitioned_pk_pkey | regression | public | fk_notpartitioned_pk | PRIMARY KEY | NO | NO | YES | + regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk | FOREIGN KEY | NO | NO | YES | + regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk_1 | FOREIGN KEY | NO | NO | YES | + regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk_2 | FOREIGN KEY | NO | NO | YES | + regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk_2_1 | FOREIGN KEY | NO | NO | YES | + regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk_2_2 | FOREIGN KEY | NO | NO | YES | + regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk_3 | FOREIGN KEY | NO | NO | YES | + regression | public | fk_partitioned_fk_4_a_b_fkey | regression | public | fk_partitioned_fk_4 | FOREIGN KEY | NO | NO | YES | + regression | public | fk_partitioned_fk_4_a_b_fkey | regression | public | fk_partitioned_fk_4_1 | FOREIGN KEY | NO | NO | YES | + regression | public | fk_partitioned_fk_4_2_a_b_fkey | regression | public | fk_partitioned_fk_4_2 | FOREIGN KEY | NO | NO | YES | + regression | public | fk_partitioned_fk_4_a_b_fkey | regression | public | fk_partitioned_fk_4_2 | FOREIGN KEY | NO | NO | YES | + regression | public | fk_partitioned_fk_5_a_b_fkey | regression | public | fk_partitioned_fk_5 | FOREIGN KEY | YES | NO | YES | + regression | public | fk_partitioned_fk_5_a_b_fkey1 | regression | public | fk_partitioned_fk_5 | FOREIGN KEY | NO | NO | YES | + regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk_5 | FOREIGN KEY | NO | NO | YES | + regression | public | fk_partitioned_fk_5_1_a_b_fkey | regression | public | fk_partitioned_fk_5_1 | FOREIGN KEY | NO | NO | YES | + regression | public | fk_partitioned_fk_5_a_b_fkey | regression | public | fk_partitioned_fk_5_1 | FOREIGN KEY | YES | NO | YES | + regression | public | fk_partitioned_fk_5_a_b_fkey1 | regression | public | fk_partitioned_fk_5_1 | FOREIGN KEY | NO | NO | YES | + regression | public | fk_partitioned_fk_a_b_fkey | regression | public | fk_partitioned_fk_5_1 | FOREIGN KEY | NO | NO | YES | + regression | public | gtest0_pkey | regression | public | gtest0 | PRIMARY KEY | NO | NO | YES | + regression | public | gtest1_pkey | regression | public | gtest1 | PRIMARY KEY | NO | NO | YES | + regression | public | gtest10_pkey | regression | public | gtest10 | PRIMARY KEY | NO | NO | YES | + regression | public | gtest10a_pkey | regression | public | gtest10a | PRIMARY KEY | NO | NO | YES | + regression | public | gtest1_1_a_not_null | regression | public | gtest1_1 | CHECK | NO | NO | YES | + regression | public | gtest1_y_a_not_null | regression | public | gtest1_y | CHECK | NO | NO | YES | + regression | public | gtest2_pkey | regression | public | gtest2 | PRIMARY KEY | NO | NO | YES | + regression | public | gtest20_b_check | regression | public | gtest20 | CHECK | NO | NO | YES | + regression | public | gtest20_pkey | regression | public | gtest20 | PRIMARY KEY | NO | NO | YES | + regression | public | gtest20a_pkey | regression | public | gtest20a | PRIMARY KEY | NO | NO | YES | + regression | public | chk | regression | public | gtest20b | CHECK | NO | NO | YES | + regression | public | gtest20b_pkey | regression | public | gtest20b | PRIMARY KEY | NO | NO | YES | + regression | public | gtest21a_b_not_null | regression | public | gtest21a | CHECK | NO | NO | YES | + regression | public | gtest21a_pkey | regression | public | gtest21a | PRIMARY KEY | NO | NO | YES | + regression | public | gtest21b_pkey | regression | public | gtest21b | PRIMARY KEY | NO | NO | YES | + regression | public | gtest22a_b_key | regression | public | gtest22a | UNIQUE | NO | NO | YES | YES + regression | public | gtest22a_pkey | regression | public | gtest22a | PRIMARY KEY | NO | NO | YES | + regression | public | gtest22b_pkey | regression | public | gtest22b | PRIMARY KEY | NO | NO | YES | + regression | public | gtest23p_pkey | regression | public | gtest23p | PRIMARY KEY | NO | NO | YES | + regression | public | gtest23q_b_fkey | regression | public | gtest23q | FOREIGN KEY | NO | NO | YES | + regression | public | gtest23q_pkey | regression | public | gtest23q | PRIMARY KEY | NO | NO | YES | + regression | public | gtest24_pkey | regression | public | gtest24 | PRIMARY KEY | NO | NO | YES | + regression | public | gtest25_pkey | regression | public | gtest25 | PRIMARY KEY | NO | NO | YES | + regression | public | gtest26_pkey | regression | public | gtest26 | PRIMARY KEY | NO | NO | YES | + regression | public | gtest_parent_f1_not_null | regression | public | gtest_child | CHECK | NO | NO | YES | + regression | public | gtest_parent_f1_not_null | regression | public | gtest_child2 | CHECK | NO | NO | YES | + regression | public | gtest_child3_f1_not_null | regression | public | gtest_child3 | CHECK | NO | NO | YES | + regression | public | gtest_parent_f1_not_null | regression | public | gtest_parent | CHECK | NO | NO | YES | + regression | public | gtest_tableoid_pkey | regression | public | gtest_tableoid | PRIMARY KEY | NO | NO | YES | + regression | public | gtestx_a_not_null | regression | public | gtestx | CHECK | NO | NO | YES | + regression | public | gtestxx_1_a_not_null | regression | public | gtestxx_1 | CHECK | NO | NO | YES | + regression | public | gtestxx_3_a_not_null | regression | public | gtestxx_3 | CHECK | NO | NO | YES | + regression | public | gtestxx_4_a_not_null | regression | public | gtestxx_4 | CHECK | NO | NO | YES | + regression | public | idxpart_another_pkey | regression | public | idxpart_another | PRIMARY KEY | NO | NO | YES | + regression | public | idxpart_another_1_pkey | regression | public | idxpart_another_1 | PRIMARY KEY | NO | NO | YES | + regression | public | list_parted2_b_not_null | regression | public | inh_test | CHECK | NO | NO | YES | + regression | public | foo | regression | public | inhx | CHECK | NO | NO | YES | + regression | public | inhx_pkey | regression | public | inhx | PRIMARY KEY | NO | NO | YES | + regression | public | insert_tbl_check | regression | public | insert_tbl | CHECK | NO | NO | YES | + regression | public | insert_tbl_con | regression | public | insert_tbl | CHECK | NO | NO | YES | + regression | public | inh_check_constraint | regression | public | invalid_check_con | CHECK | NO | NO | YES | + regression | public | inh_check_constraint | regression | public | invalid_check_con_child | CHECK | NO | NO | YES | + regression | public | itest1_a_not_null | regression | public | itest1 | CHECK | NO | NO | YES | + regression | public | itest10_a_not_null | regression | public | itest10 | CHECK | NO | NO | YES | + regression | public | itest11_a_not_null | regression | public | itest11 | CHECK | NO | NO | YES | + regression | public | itest13_b_not_null | regression | public | itest13 | CHECK | NO | NO | YES | + regression | public | itest13_c_not_null | regression | public | itest13 | CHECK | NO | NO | YES | + regression | public | itest14_id_not_null | regression | public | itest14 | CHECK | NO | NO | YES | + regression | public | itest2_a_not_null | regression | public | itest2 | CHECK | NO | NO | YES | + regression | public | itest3_a_not_null | regression | public | itest3 | CHECK | NO | NO | YES | + regression | public | itest3_c_not_null | regression | public | itest3 | CHECK | NO | NO | YES | + regression | public | itest5_a_not_null | regression | public | itest5 | CHECK | NO | NO | YES | + regression | public | itest6_a_not_null | regression | public | itest6 | CHECK | NO | NO | YES | + regression | public | itest7_a_not_null | regression | public | itest7 | CHECK | NO | NO | YES | + regression | public | itest7_a_not_null | regression | public | itest7a | CHECK | NO | NO | YES | + regression | public | itest7c_a_not_null | regression | public | itest7c | CHECK | NO | NO | YES | + regression | public | itest7d_a_not_null | regression | public | itest7d | CHECK | NO | NO | YES | + regression | public | itest7d_a_not_null | regression | public | itest7e | CHECK | NO | NO | YES | + regression | public | itest9_a_not_null | regression | public | itest9 | CHECK | NO | NO | YES | + regression | public | mlparted1_a_not_null | regression | public | mlparted1 | CHECK | NO | NO | YES | + regression | public | mlparted1_b_not_null | regression | public | mlparted1 | CHECK | NO | NO | YES | + regression | public | mlparted11_a_not_null | regression | public | mlparted11 | CHECK | NO | NO | YES | + regression | public | mlparted1_b_not_null | regression | public | mlparted11 | CHECK | NO | NO | YES | + regression | public | mlparted1_a_not_null | regression | public | mlparted12 | CHECK | NO | NO | YES | + regression | public | mlparted1_b_not_null | regression | public | mlparted12 | CHECK | NO | NO | YES | + regression | public | mlparted2_a_not_null | regression | public | mlparted2 | CHECK | NO | NO | YES | + regression | public | mlparted2_b_not_null | regression | public | mlparted2 | CHECK | NO | NO | YES | + regression | public | mlparted4_a_not_null | regression | public | mlparted4 | CHECK | NO | NO | YES | + regression | public | mvtest_t_amt_not_null | regression | public | mvtest_t | CHECK | NO | NO | YES | + regression | public | mvtest_t_id_not_null | regression | public | mvtest_t | CHECK | NO | NO | YES | + regression | public | mvtest_t_pkey | regression | public | mvtest_t | PRIMARY KEY | NO | NO | YES | + regression | public | mvtest_t_type_not_null | regression | public | mvtest_t | CHECK | NO | NO | YES | + regression | public | notnull_tbl2_pkey | regression | public | notnull_tbl2 | PRIMARY KEY | NO | NO | YES | + regression | public | notnull_tbl3_a_check | regression | public | notnull_tbl3 | CHECK | NO | NO | YES | + regression | public | nv_child_2009_d_check | regression | public | nv_child_2009 | CHECK | NO | NO | YES | + regression | public | nv_parent_d_check | regression | public | nv_child_2009 | CHECK | NO | NO | YES | + regression | public | nv_child_2010_d_check | regression | public | nv_child_2010 | CHECK | NO | NO | YES | + regression | public | nv_parent_d_check | regression | public | nv_child_2010 | CHECK | NO | NO | YES | + regression | public | nv_child_2011_d_check | regression | public | nv_child_2011 | CHECK | NO | NO | YES | + regression | public | nv_parent_d_check | regression | public | nv_child_2011 | CHECK | NO | NO | YES | + regression | public | nv_parent_check | regression | public | nv_parent | CHECK | NO | NO | YES | + regression | public | nv_parent_d_check | regression | public | nv_parent | CHECK | NO | NO | YES | + regression | public | part1_self_fk_id_not_null | regression | public | part1_self_fk | CHECK | NO | NO | YES | + regression | public | part1_self_fk_pkey | regression | public | part1_self_fk | PRIMARY KEY | NO | NO | YES | + regression | public | parted_self_fk_id_abc_fkey | regression | public | part1_self_fk | FOREIGN KEY | NO | NO | YES | + regression | public | part2_self_fk_pkey | regression | public | part2_self_fk | PRIMARY KEY | NO | NO | YES | + regression | public | parted_self_fk_id_abc_fkey | regression | public | part2_self_fk | FOREIGN KEY | NO | NO | YES | + regression | public | parted_self_fk_id_not_null | regression | public | part2_self_fk | CHECK | NO | NO | YES | + regression | public | part32_self_fk_pkey | regression | public | part32_self_fk | PRIMARY KEY | NO | NO | YES | + regression | public | part3_self_fk_id_not_null | regression | public | part32_self_fk | CHECK | NO | NO | YES | + regression | public | parted_self_fk_id_abc_fkey | regression | public | part32_self_fk | FOREIGN KEY | NO | NO | YES | + regression | public | part33_self_fk_id_not_null | regression | public | part33_self_fk | CHECK | NO | NO | YES | + regression | public | part33_self_fk_pkey | regression | public | part33_self_fk | PRIMARY KEY | NO | NO | YES | + regression | public | parted_self_fk_id_abc_fkey | regression | public | part33_self_fk | FOREIGN KEY | NO | NO | YES | + regression | public | part3_self_fk_id_not_null | regression | public | part3_self_fk | CHECK | NO | NO | YES | + regression | public | part3_self_fk_pkey | regression | public | part3_self_fk | PRIMARY KEY | NO | NO | YES | + regression | public | parted_self_fk_id_abc_fkey | regression | public | part3_self_fk | FOREIGN KEY | NO | NO | YES | + regression | public | check_b | regression | public | part_7_a_null | CHECK | NO | NO | YES | + regression | public | part_rp_a_check | regression | public | part_rp | CHECK | NO | NO | YES | + regression | public | part_rp100_a_check | regression | public | part_rp100 | CHECK | NO | NO | YES | + regression | public | parted_self_fk_id_abc_fkey | regression | public | parted_self_fk | FOREIGN KEY | NO | NO | YES | + regression | public | parted_self_fk_id_not_null | regression | public | parted_self_fk | CHECK | NO | NO | YES | + regression | public | parted_self_fk_pkey | regression | public | parted_self_fk | PRIMARY KEY | NO | NO | YES | + regression | public | part1_a_check | regression | public | partr_def2 | CHECK | NO | NO | YES | + regression | public | part1_a_not_null | regression | public | partr_def2 | CHECK | NO | NO | YES | + regression | public | part1_b_check | regression | public | partr_def2 | CHECK | NO | NO | YES | + regression | public | part1_b_not_null | regression | public | partr_def2 | CHECK | NO | NO | YES | + regression | public | persons2_name_key | regression | public | persons2 | UNIQUE | NO | NO | YES | YES + regression | public | persons2_pkey | regression | public | persons2 | PRIMARY KEY | NO | NO | YES | + regression | public | persons3_name_not_null | regression | public | persons3 | CHECK | NO | NO | YES | + regression | public | persons3_pkey | regression | public | persons3 | PRIMARY KEY | NO | NO | YES | + regression | public | pp1_pkey | regression | public | pp1 | PRIMARY KEY | NO | NO | YES | + regression | public | reservations_room_id_not_null | regression | public | reservations | CHECK | NO | NO | YES | + regression | public | rule_and_refint_t1_pkey | regression | public | rule_and_refint_t1 | PRIMARY KEY | NO | NO | YES | + regression | public | rule_and_refint_t2_pkey | regression | public | rule_and_refint_t2 | PRIMARY KEY | NO | NO | YES | + regression | public | rule_and_refint_t3_id3a_id3b_fkey | regression | public | rule_and_refint_t3 | FOREIGN KEY | NO | NO | YES | + regression | public | rule_and_refint_t3_id3a_id3c_fkey | regression | public | rule_and_refint_t3 | FOREIGN KEY | NO | NO | YES | + regression | public | rule_and_refint_t3_pkey | regression | public | rule_and_refint_t3 | PRIMARY KEY | NO | NO | YES | + regression | public | rules_log_id_not_null | regression | public | rules_log | CHECK | NO | NO | YES | + regression | public | skip_wal_skip_rewrite_index_pkey | regression | public | skip_wal_skip_rewrite_index | PRIMARY KEY | NO | NO | YES | + regression | public | spgist_box_tbl_id_not_null | regression | public | spgist_box_tbl | CHECK | NO | NO | YES | + regression | public | spgist_unlogged_tbl_id_not_null | regression | public | spgist_unlogged_tbl | CHECK | NO | NO | YES | + regression | public | tbl_include_box_idx_unique | regression | public | tbl_include_box | PRIMARY KEY | NO | NO | YES | + regression | public | tbl_include_pk_pkey | regression | public | tbl_include_pk | PRIMARY KEY | NO | NO | YES | + regression | public | tbl_include_unique1_c1_c2_c3_c4_key | regression | public | tbl_include_unique1 | UNIQUE | NO | NO | YES | YES + regression | public | tbl_include_unique1_idx_unique | regression | public | tbl_include_unique1 | UNIQUE | NO | NO | YES | YES + regression | public | bmerged | regression | public | test_inh_check | CHECK | NO | NO | YES | + regression | public | bnoinherit | regression | public | test_inh_check | CHECK | NO | NO | YES | + regression | public | test_inh_check_a_check | regression | public | test_inh_check | CHECK | NO | NO | YES | + regression | public | blocal | regression | public | test_inh_check_child | CHECK | NO | NO | YES | + regression | public | bmerged | regression | public | test_inh_check_child | CHECK | NO | NO | YES | + regression | public | test_inh_check_a_check | regression | public | test_inh_check_child | CHECK | NO | NO | YES | + regression | public | transition_table_base_pkey | regression | public | transition_table_base | PRIMARY KEY | NO | NO | YES | + regression | public | transition_table_level1_level1_no_not_null | regression | public | transition_table_level1 | CHECK | NO | NO | YES | + regression | public | transition_table_level1_pkey | regression | public | transition_table_level1 | PRIMARY KEY | NO | NO | YES | + regression | public | transition_table_level2_level2_no_not_null | regression | public | transition_table_level2 | CHECK | NO | NO | YES | + regression | public | transition_table_level2_parent_no_not_null | regression | public | transition_table_level2 | CHECK | NO | NO | YES | + regression | public | transition_table_level2_pkey | regression | public | transition_table_level2 | PRIMARY KEY | NO | NO | YES | + regression | public | transition_table_status_level_not_null | regression | public | transition_table_status | CHECK | NO | NO | YES | + regression | public | transition_table_status_node_no_not_null | regression | public | transition_table_status | CHECK | NO | NO | YES | + regression | public | transition_table_status_pkey | regression | public | transition_table_status | PRIMARY KEY | NO | NO | YES | + regression | public | trigger_parted_pkey | regression | public | trigger_parted | PRIMARY KEY | NO | NO | YES | + regression | public | trigger_parted_p1_pkey | regression | public | trigger_parted_p1 | PRIMARY KEY | NO | NO | YES | + regression | public | trigger_parted_p1_1_pkey | regression | public | trigger_parted_p1_1 | PRIMARY KEY | NO | NO | YES | + regression | public | trigger_parted_p2_pkey | regression | public | trigger_parted_p2 | PRIMARY KEY | NO | NO | YES | + regression | public | trigger_parted_p2_2_pkey | regression | public | trigger_parted_p2_2 | PRIMARY KEY | NO | NO | YES | + regression | public | truncate_b_id_not_null | regression | public | truncate_b | CHECK | NO | NO | YES | + regression | public | tt0_x_not_null | regression | public | tt0 | CHECK | NO | NO | YES | + regression | public | tt0_x_not_null | regression | public | tt6 | CHECK | NO | NO | YES | + regression | public | utf8_verification_inputs_pkey | regression | public | utf8_verification_inputs | PRIMARY KEY | NO | NO | YES | + regression | public | view_base_table_pkey | regression | public | view_base_table | PRIMARY KEY | NO | NO | YES | +(211 rows) + +-- +-- Domain constraints +-- +SELECT * FROM information_schema.column_domain_usage + WHERE domain_schema = 'public' AND table_schema = 'public' + ORDER BY domain_name; + domain_catalog | domain_schema | domain_name | table_catalog | table_schema | table_name | column_name +----------------+---------------+--------------+---------------+--------------+-------------------+------------- + regression | public | con | regression | public | domcontest | col1 + regression | public | dom | regression | public | domview | col1 + regression | public | gtestdomain1 | regression | public | gtest24 | b + regression | public | spgist_text | regression | public | spgist_domain_tbl | f1 + regression | public | things | regression | public | thethings | stuff +(5 rows) + +SELECT * FROM information_schema.domain_constraints + WHERE domain_schema = 'public' + ORDER BY constraint_name; + constraint_catalog | constraint_schema | constraint_name | domain_catalog | domain_schema | domain_name | is_deferrable | initially_deferred +--------------------+-------------------+--------------------------+----------------+---------------+--------------------+---------------+-------------------- + regression | public | con_check | regression | public | con | NO | NO + regression | public | gtestdomain1_check | regression | public | gtestdomain1 | NO | NO + regression | public | meow | regression | public | things | NO | NO + regression | public | orderedpair_check | regression | public | orderedpair | NO | NO + regression | public | plpgsql_arr_domain_check | regression | public | plpgsql_arr_domain | NO | NO + regression | public | plpgsql_domain_check | regression | public | plpgsql_domain | NO | NO + regression | public | pos_int_check | regression | public | pos_int | NO | NO + regression | public | sorted | regression | public | orderedarray | NO | NO + regression | public | str_domain2_check | regression | public | str_domain2 | NO | NO +(9 rows) + +SELECT * FROM information_schema.domains + WHERE domain_schema = 'public' + ORDER BY domain_name; + domain_catalog | domain_schema | domain_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 | domain_default | udt_catalog | udt_schema | udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier +----------------+---------------+--------------------+-------------------+--------------------------+------------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+----------------+-------------+------------+----------+---------------+--------------+------------+---------------------+---------------- + regression | public | con | integer | | | | | | | | | 32 | 2 | 0 | | | | | regression | pg_catalog | int4 | | | | | 1 + regression | public | dom | integer | | | | | | | | | 32 | 2 | 0 | | | | | regression | pg_catalog | int4 | | | | | 1 + regression | public | gtestdomain1 | integer | | | | | | | | | 32 | 2 | 0 | | | | | regression | pg_catalog | int4 | | | | | 1 + regression | public | mynums | numeric[] | | | | | | | | | | | | | | | | regression | pg_catalog | _numeric | | | | | 1 + regression | public | mynums2 | USER-DEFINED | | | | | | | | | | | | | | | | regression | public | mynums | | | | | 1 + regression | public | orderedarray | integer[] | | | | | | | | | | | | | | | | regression | pg_catalog | _int4 | | | | | 1 + regression | public | orderedpair | integer[] | | | | | | | | | | | | | | | | regression | pg_catalog | _int4 | | | | | 1 + regression | public | plpgsql_arr_domain | integer[] | | | | | | | | | | | | | | | | regression | pg_catalog | _int4 | | | | | 1 + regression | public | plpgsql_domain | integer | | | | | | | | | 32 | 2 | 0 | | | | | regression | pg_catalog | int4 | | | | | 1 + regression | public | pos_int | integer | | | | | | | | | 32 | 2 | 0 | | | | | regression | pg_catalog | int4 | | | | | 1 + regression | public | spgist_text | character varying | | 1073741824 | | | | | | | | | | | | | | regression | pg_catalog | varchar | | | | | 1 + regression | public | str_domain | text | | 1073741824 | | | | | | | | | | | | | | regression | pg_catalog | text | | | | | 1 + regression | public | str_domain2 | text | | 1073741824 | | | | | | | | | | | | | 'foo'::text | regression | pg_catalog | text | | | | | 1 + regression | public | testboolxmldomain | boolean | | | | | | | | | | | | | | | | regression | pg_catalog | bool | | | | | 1 + regression | public | testdatexmldomain | date | | | | | | | | | | | | 0 | | | | regression | pg_catalog | date | | | | | 1 + regression | public | testxmldomain | character varying | | 1073741824 | | | | | | | | | | | | | | regression | pg_catalog | varchar | | | | | 1 + regression | public | things | integer | | | | | | | | | 32 | 2 | 0 | | | | | regression | pg_catalog | int4 | | | | | 1 +(17 rows) + +SELECT * FROM information_schema.check_constraints + WHERE (constraint_schema, constraint_name) + IN (SELECT constraint_schema, constraint_name + FROM information_schema.domain_constraints + WHERE domain_schema = 'public') + ORDER BY constraint_name; + constraint_catalog | constraint_schema | constraint_name | check_clause +--------------------+-------------------+--------------------------+----------------------------------- + regression | public | con_check | ((VALUE > 0)) + regression | public | gtestdomain1_check | ((VALUE < 10)) + regression | public | meow | ((VALUE < 11)) + regression | public | orderedpair_check | (((VALUE)[1] < (VALUE)[2])) + regression | public | plpgsql_arr_domain_check | (plpgsql_arr_domain_check(VALUE)) + regression | public | plpgsql_domain_check | (plpgsql_domain_check(VALUE)) + regression | public | pos_int_check | ((VALUE > 0)) + regression | public | sorted | (((VALUE)[1] < (VALUE)[2])) + regression | public | str_domain2_check | ((VALUE <> 'foo'::text)) +(9 rows) + diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 4df9d8503b..22e9896f18 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -130,5 +130,6 @@ test: event_trigger oidjoins test: fast_default # run tablespace test at the end because it drops the tablespace created during -# setup that other tests may use. -test: tablespace +# setup that other tests may use. This is also a good place to verify +# our information_schema definitions. +test: tablespace information_schema diff --git a/src/test/regress/sql/information_schema.sql b/src/test/regress/sql/information_schema.sql new file mode 100644 index 0000000000..a0a8b1ed58 --- /dev/null +++ b/src/test/regress/sql/information_schema.sql @@ -0,0 +1,41 @@ +-- We test the information schema last, so that we examine +-- database state at the end of the regression test run. + +-- +-- Table constraints +-- + +SELECT * FROM information_schema.check_constraints + WHERE constraint_schema = 'public' + ORDER BY constraint_name; + +SELECT * FROM information_schema.constraint_column_usage + WHERE constraint_schema = 'public' + ORDER BY table_name, column_name, constraint_name; + +SELECT * FROM information_schema.table_constraints + WHERE constraint_schema = 'public' + ORDER BY table_name, constraint_name; + +-- +-- Domain constraints +-- + +SELECT * FROM information_schema.column_domain_usage + WHERE domain_schema = 'public' AND table_schema = 'public' + ORDER BY domain_name; + +SELECT * FROM information_schema.domain_constraints + WHERE domain_schema = 'public' + ORDER BY constraint_name; + +SELECT * FROM information_schema.domains + WHERE domain_schema = 'public' + ORDER BY domain_name; + +SELECT * FROM information_schema.check_constraints + WHERE (constraint_schema, constraint_name) + IN (SELECT constraint_schema, constraint_name + FROM information_schema.domain_constraints + WHERE domain_schema = 'public') + ORDER BY constraint_name; -- 2.39.2 --v4ugctxfhn3h4fso--