Postgres SQL Scripts

To translate a T-SQL query for counting foreign keys (FKs) and listing them by table into PostgreSQL, we need to take into account how foreign keys are managed and queried in PostgreSQL.

In PostgreSQL, foreign keys are stored in the information_schema database, specifically in the table_constraints, key_column_usage, and referential_constraints tables.

1. Get the count of all foreign keys in PostgreSQL:

SELECT COUNT(*) AS fk_count
FROM information_schema.table_constraints tc
JOIN information_schema.referential_constraints rc
ON tc.constraint_name = rc.constraint_name
WHERE tc.constraint_type = ‘FOREIGN KEY’;

2. Get the list of foreign keys by table in PostgreSQL:

SELECT
tc.table_schema,
tc.table_name,
tc.constraint_name AS fk_name,
kcu.column_name AS fk_column,
ccu.table_schema AS referenced_schema,
ccu.table_name AS referenced_table,
ccu.column_name AS referenced_column
FROM
information_schema.table_constraints tc
JOIN
information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
JOIN
information_schema.referential_constraints rc
ON tc.constraint_name = rc.constraint_name
JOIN
information_schema.constraint_column_usage ccu
ON rc.unique_constraint_name = ccu.constraint_name
WHERE
tc.constraint_type = ‘FOREIGN KEY’
ORDER BY
tc.table_name, tc.constraint_name;

3. Get the count of all foreign keys by database:

SELECT COUNT(*) AS fk_count
FROM information_schema.table_constraints tc
JOIN information_schema.referential_constraints rc
ON tc.constraint_name = rc.constraint_name
WHERE tc.constraint_type = ‘FOREIGN KEY’
AND tc.table_schema NOT IN (‘pg_catalog’, ‘information_schema’);

4. List all foreign keys by table and database in SQL SERVER:

The query below lists foreign keys for all tables in the current database:

SELECT
tc.table_schema,
tc.table_name,
tc.constraint_name AS fk_name,
kcu.column_name AS fk_column,
ccu.table_schema AS referenced_schema,
ccu.table_name AS referenced_table,
ccu.column_name AS referenced_column,

current_database() AS database_name — in postgres, in sql remove this line

FROM
information_schema.table_constraints tc
JOIN
information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
JOIN
information_schema.referential_constraints rc
ON tc.constraint_name = rc.constraint_name
JOIN
information_schema.constraint_column_usage ccu
ON rc.unique_constraint_name = ccu.constraint_name
WHERE
tc.constraint_type = ‘FOREIGN KEY’
AND tc.table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY
tc.table_name, tc.constraint_name;

5. Check If Foreign Keys Exist

If you’re still not seeing results in postgres, ensure that foreign keys are indeed defined in your database. You can check for any foreign key constraints with this query:

SELECT
conname AS fk_name,
conrelid::regclass AS table_name,
a.attname AS column_name,
confrelid::regclass AS referenced_table,
af.attname AS referenced_column
FROM
pg_constraint AS c
JOIN
pg_attribute AS a ON a.attnum = ANY(c.conkey)
JOIN
pg_attribute AS af ON af.attnum = ANY(c.confkey)
WHERE
c.contype = ‘f’;

If .4 didn’t return results for postgres try this one

SELECT
tc.table_schema,
tc.table_name,
tc.constraint_name AS fk_name,
kcu.column_name AS fk_column,
ccu.table_schema AS referenced_schema,
ccu.table_name AS referenced_table,
ccu.column_name AS referenced_column
FROM
information_schema.table_constraints tc
JOIN
information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
JOIN
information_schema.referential_constraints rc
ON tc.constraint_name = rc.constraint_name
JOIN
information_schema.constraint_column_usage ccu
ON rc.unique_constraint_name = ccu.constraint_name
WHERE
tc.constraint_type = ‘FOREIGN KEY’
ORDER BY
tc.table_schema, tc.table_name, tc.constraint_name;

T-SQL Script to Find Tables with 1 or Fewer Foreign Keys:

WITH FK_Count AS (
SELECT
t.name AS table_name,
COUNT(fk.name) AS fk_count
FROM
sys.tables t
LEFT JOIN
sys.foreign_keys fk ON fk.parent_object_id = t.object_id
GROUP BY
t.name
)
SELECT
table_name,
fk_count
FROM
FK_Count
WHERE
fk_count <= 1
ORDER BY
fk_count, table_name;

Explanation:

• Counting FK: The first query simply counts how many foreign key constraints are present in the table_constraints table.
• Listing FKs by Table: The second query lists the foreign keys, including details like the foreign key’s name, the column(s) it refers to, the referenced table, and the referenced columns. It joins several information schema views to fetch all related data about foreign keys.

In both queries, the information_schema views are used, which are a part of the PostgreSQL system catalog and provide metadata about the database structure.

• current_database(): This function is used to retrieve the name of the current database. It is included in the query to explicitly show which database the foreign keys belong to, though it’s mostly redundant when running within the current database context.
• tc.table_schema NOT IN ('pg_catalog', 'information_schema'): This filters out system schemas like pg_catalog and information_schema from the results, focusing on user-created schemas.

For multiple databases:

PostgreSQL doesn’t allow querying across multiple databases directly using a single SQL query, so if you want to get foreign key details for multiple databases, you’d need to connect to each database and run the queries separately.

CREATE OR ALTER VIEW mars.vw_certificate_transaction_totals

AS

SELECT ct.certificate_transaction_id,

      ct.certificate_id,

      ct.product_type_cd,

      ct.certificate_transaction_type_cd,

      ct.certificate_transaction_reason_cd,

      ISNULL(ct.transaction_begin_date, ‘0001-01-01’) AS transaction_begin_date,

      ISNULL(ct.transaction_end_date, ‘0001-01-01’) AS transaction_end_date,

      ISNULL(ct.transaction_date, ‘0001-01-01’) AS transaction_date,

      ct.transaction_sequence_number,

      ct.unique_identification_number,

      ISNULL(ct.premium_amount, 0) AS premium_amount,

      cttafd.tax_amount,

      cttafd.fee_amount,

      ISNULL(ct.premium_amount, 0) + cttafd.tax_amount + cttafd.fee_amount AS total_amount,

      ISNULL(ct.interest_amount, 0) AS interest_amount,

      ct.refund_check,

      ISNULL(ct.refund_check_date, ‘0001-01-01’) AS refund_check_date

FROM mars.certificate_transactions AS ct

LEFT JOIN

      (SELECT certificate_transaction_id,

            SUM(CASE tax_fee_type_cd WHEN ‘TAX’ THEN ISNULL(amount, 0) ELSE 0 END) AS tax_amount,

            SUM(CASE tax_fee_type_cd WHEN ‘FEE’ THEN ISNULL(amount, 0) ELSE 0 END) AS fee_amount

      FROM mars.certificate_transaction_tax_and_fee_details

      WHERE is_active = 1

      GROUP BY certificate_transaction_id) AS cttafd

ON ct.certificate_transaction_id = cttafd.certificate_transaction_id

WHERE ct.is_active = 1

–ORDER BY ct.certificate_transaction_id DESC 

*Script out all FK in postgres SQL

DO $$
DECLARE
fk RECORD;
BEGIN
FOR fk IN
SELECT conname AS constraint_name,
conrelid::regclass AS table_name,
a.attname AS column_name,
confrelid::regclass AS referenced_table_name,
af.attname AS referenced_column_name,
confupdtype AS on_update,
confdeltype AS on_delete
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
JOIN pg_attribute af ON af.attnum = ANY(c.confkey) AND af.attrelid = c.confrelid
WHERE c.contype = ‘f’
LOOP
RAISE NOTICE ‘ALTER TABLE % ADD CONSTRAINT % FOREIGN KEY (%) REFERENCES % (%);’,
fk.table_name,
fk.constraint_name,
fk.column_name,
fk.referenced_table_name,
fk.referenced_column_name;
END LOOP;
END $$;

In new window