PostgreSQL Tables Without Unique Constrtaint On Sequence

Question

I use PostgreSQL. Is there a query to get a list of all tables that do not have the unique constraint on the sequence column? If it helps forming a query I use "reference" for my sequence column.

This is an example of a table that has 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

Answer 1

It is easy to search the information_schema for tables with a column named reference that is no primary key:

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';