主页 / dba / 问题 / 335441
Accepted
Ron Piggott
Asked: 2024-02-02 10:36:55 +0800 CST

没有唯一序列约束的 PostgreSQL 表

  • 772

我使用 PostgreSQL。是否有一个查询来获取对序列列没有唯一约束的所有表的列表？如果它有助于形成查询，我将“引用”用于我的序列列。

这是包含 this.das 的表的示例

\d+ a_practical_approach_when_there_is_a_lot_to_do
                                                                   Table "public.a_practical_approach_when_there_is_a_lot_to_do"
        Column        |           Type           | Collation | Nullable |                                      Default                                      | Storage  | Stats target | Description
----------------------+--------------------------+-----------+----------+-----------------------------------------------------------------------------------+----------+--------------+-------------
 reference            | bigint                   |           | not null | nextval('a_practical_approach_when_there_is_a_lot_to_do_reference_seq'::regclass) | plain    |              |
 display_order        | bigint                   |           |          |                                                                                   | plain    |              |
 principal            | text                     |           |          |                                                                                   | extended |              |
 explanation          | text                     |           |          |                                                                                   | extended |              |
 add_date             | timestamp with time zone |           |          |                                                                                   | plain    |              | UTC
 membership_reference | bigint                   |           |          |                                                                                   | plain    |              |
Indexes:
    "a_practical_approach_when_there_is_a_lot_to_do_reference_unique" UNIQUE CONSTRAINT, btree (reference)
Access method: heap
postgresql

1 个回答

  1. Best Answer

    搜索具有名为非主键的information_schema列的表很容易：reference

    SELECT table_schema,
       table_name
FROM information_schema.columns
WHERE column_name = 'reference'
EXCEPT ALL
SELECT table_schema,
       table_name
FROM information_schema.table_constraints AS tc
   JOIN information_schema.key_column_usage AS kcu
      USING (table_schema, table_name)
WHERE kcu.column_name = 'reference'
  AND tc.constraint_type = 'PRIMARY KEY';
    • 1

相关问题