PostgreSQL creates extra foreign-key catalog rows because a reference to a partitioned table must also work across its physical partitions. To determine whether those rows represent one relationship or separate declarations, inspect their parent links and referenced tables.

This walkthrough is for a developer using a Mac to investigate an unexpected constraint list in a database browser or Node.js application. The task is to reproduce the behavior with synthetic article rows, trace the constraint family, and decide what belongs in a logical schema report.

Why does one foreign key produce several constraint rows?

A partitioned table provides a logical table whose rows are stored in its partitions. The parent has no storage of its own. That distinction matters when another table references it: PostgreSQL must maintain the relationship when rows in the physical partitions change. See the official Table Partitioning documentation.

The PostgreSQL 18 implementation makes the mechanism explicit. Its addFkRecurseReferenced routine creates foreign-key action triggers and recursively creates an additional pg_constraint row for each referenced partition. Its validation code explains that these referenced-side rows support action triggers rather than separate validation scans. See PostgreSQL’s foreign-key creation and validation implementation.

These rows therefore do not mean that every referencing value must exist in every partition. The declared relationship still targets the partitioned parent. PostgreSQL also needs to handle changes on the referenced side, including the configured behavior when a referenced row is deleted or its key changes. Those behaviors are described in Foreign Keys.

Which PostgreSQL versions support this?

Foreign keys referencing a partitioned table were introduced in PostgreSQL 12. Earlier versions cannot use the same parent-targeting declaration. PostgreSQL 12 is now listed as unsupported; it is the historical feature boundary, not a recommended installation target. This article uses the current PostgreSQL 18 documentation and its maintained source branch. See the PostgreSQL 12 release notes.

Reproduce the relationship with synthetic article rows

Use an existing local PostgreSQL development database and a working psql installation. On your Mac, open Terminal, connect with psql -d postgres, and check the server with SHOW server_version;. Substitute your development database name if needed. The official psql documentation describes database selection and interactive SQL execution.

Run this complete setup once. It creates a dedicated schema without removing existing objects. If that schema already exists, stop and use a fresh development database or consistently rename the schema throughout the examples.

BEGIN;

CREATE SCHEMA fk_partition_demo;

CREATE TABLE fk_partition_demo.articles (
    article_id integer NOT NULL,
    publication_year integer NOT NULL,
    title text NOT NULL,
    PRIMARY KEY (article_id, publication_year)
) PARTITION BY RANGE (publication_year);

CREATE TABLE fk_partition_demo.articles_2025
    PARTITION OF fk_partition_demo.articles
    FOR VALUES FROM (2025) TO (2026);

CREATE TABLE fk_partition_demo.articles_2026
    PARTITION OF fk_partition_demo.articles
    FOR VALUES FROM (2026) TO (2027);

CREATE TABLE fk_partition_demo.article_notes (
    note_id integer PRIMARY KEY,
    article_id integer NOT NULL,
    publication_year integer NOT NULL,
    note_text text NOT NULL,
    CONSTRAINT article_notes_article_fk
        FOREIGN KEY (article_id, publication_year)
        REFERENCES fk_partition_demo.articles
            (article_id, publication_year)
);

INSERT INTO fk_partition_demo.articles
    (article_id, publication_year, title)
VALUES
    (101, 2025, 'A guide to paper models'),
    (102, 2026, 'Choosing a notebook layout');

INSERT INTO fk_partition_demo.article_notes
    (note_id, article_id, publication_year, note_text)
VALUES
    (1, 101, 2025, 'Add a materials list'),
    (2, 102, 2026, 'Include a grid example');

COMMIT;

The composite primary key is deliberate. PostgreSQL requires a partitioned table’s primary or unique constraint to include every partition-key column, with additional restrictions on expressions in partition keys. Because this table partitions by publication_year, the primary key includes that column, and the foreign key references the complete pair. See Declarative Partitioning: Limitations.

The sample consequently identifies an article by (article_id, publication_year). It does not establish that article_id alone is globally unique. If your application requires references using only article_id, revisit the partitioning and key design before adopting this example.

Trace the constraint family instead of comparing names

The useful catalog fields are conrelid, the referencing table; confrelid, the referenced table; and conparentid, the parent constraint’s OID. Constraint names are not necessarily unique, so names alone cannot establish whether two rows are independent. These fields are documented in pg_constraint.

After running the setup, execute this query. It starts with root foreign keys declared on article_notes and follows their child constraints recursively.

WITH RECURSIVE fk_family AS (
    SELECT c.oid, c.conparentid, c.oid AS root_oid, 0 AS depth
    FROM pg_catalog.pg_constraint AS c
    WHERE c.contype = 'f'
      AND c.conrelid =
          'fk_partition_demo.article_notes'::regclass
      AND c.conparentid = 0

    UNION ALL

    SELECT child.oid, child.conparentid,
           parent.root_oid, parent.depth + 1
    FROM pg_catalog.pg_constraint AS child
    JOIN fk_family AS parent
      ON child.conparentid = parent.oid
    WHERE child.contype = 'f'
)
SELECT
    family.root_oid,
    family.depth,
    c.oid AS constraint_oid,
    c.conname,
    c.conparentid,
    c.conrelid::regclass AS referencing_table,
    c.confrelid::regclass AS referenced_table,
    pg_get_constraintdef(c.oid) AS definition
FROM fk_family AS family
JOIN pg_catalog.pg_constraint AS c ON c.oid = family.oid
ORDER BY family.root_oid, family.depth, c.oid;

For this single-level example, the implementation implies three foreign-key rows: one targeting articles and one targeting each of its two partitions. This is an expected result derived from the source, not captured execution output. Generated names and OID values should not be hard-coded. The same source also creates referenced-side constraints when a partition is created or attached later. See tablecmds.c.

Row in this example Referencing table Referenced table Parent link
Root relationship article_notes articles conparentid = 0
Referenced-partition child article_notes articles_2025 Root constraint OID
Referenced-partition child article_notes articles_2026 Root constraint OID

The easily missed detail is that the referencing table remains article_notes in all three rows. Inspect both table columns and the parent link; looking only at the table associated with the constraint can conceal the referenced-partition structure.

The query also displays reconstructed definitions through pg_get_constraintdef(). PostgreSQL documents this function as a reconstruction, rather than the original submitted SQL. Use it to understand the relationship, not to recover the exact migration text. See System Information Functions and Operators.

Inspect the same rows with node-postgres on your Mac

node-postgres returns the server’s query results; the partition-related rows originate in PostgreSQL’s constraint machinery. Its documented query API accepts SQL text and a separate array of parameter values. Here, the relation name is passed as a text value converted by PostgreSQL to regclass. See Queries.

With a current supported Node.js version installed, create a working directory and install pg. The installation command and runtime compatibility guidance appear in the node-postgres introduction.

mkdir pg-fk-inspection
cd pg-fk-inspection
npm init -y
npm install pg

Save the following complete program as inspect-fks.mjs. It requires the committed SQL setup above. Unlike the recursive SQL report, this program lists rows attached directly to the supplied referencing table; that is sufficient for the unpartitioned article_notes table in this example.

import pg from 'pg';

const { Client } = pg;
const client = new Client({ connectionTimeoutMillis: 5000 });
const relation = process.argv[2] ??
  'fk_partition_demo.article_notes';

try {
  await client.connect();

  const version = await client.query('SHOW server_version');
  console.log('PostgreSQL:', version.rows[0].server_version);

  const result = await client.query(`
    SELECT oid, conname,
           conrelid::regclass::text AS referencing_table,
           confrelid::regclass::text AS referenced_table,
           conparentid,
           conparentid = 0 AS is_root,
           pg_get_constraintdef(oid) AS definition
    FROM pg_catalog.pg_constraint
    WHERE contype = 'f' AND conrelid = $1::regclass
    ORDER BY conparentid, oid
  `, [relation]);

  console.table(result.rows);
} catch (error) {
  console.error(error.message);
  process.exitCode = 1;
} finally {
  await client.end();
}

Run it against the same database used for the SQL setup. node-postgres uses PostgreSQL connection environment variables, including PGDATABASE, and documents their defaults in Connecting.

PGDATABASE=postgres node inspect-fks.mjs

If the relation cannot be found, confirm that both sessions use the same database and that the setup committed successfully. An empty result means that this query found no foreign keys attached directly to that relation; it does not report incoming references from other tables. The JavaScript passed a syntax check during preparation of this article. Database execution was unavailable, so the SQL examples and expected catalog structure were inspected against the retrieved documentation and source.

Decide what to display or investigate

Use the following sequence when a constraint list appears unexpectedly long. It separates a logical relationship report from an implementation inventory without discarding useful evidence.

  1. Confirm the relation and server version. Use the schema-qualified relation name and record SHOW server_version; before comparing behavior with documentation.
  2. Follow parent links. For this example, child rows sharing the root OID belong to the declared relationship’s constraint family.
  3. Compare both ends. A changing confrelid with the same referencing table explains the referenced-partition rows shown above.
  4. Choose the report’s purpose. For root relationships declared on the selected table, filter with contype = 'f' AND conparentid = 0. For a complete family, retain the recursive report.
  5. Investigate separate roots individually. Compare column pairs, referenced tables, actions, validation state and deferrability before deciding that two declarations are redundant.

This reporting approach follows the identifiers and relationship fields documented in pg_constraint. The root-only filter is intentionally scoped: a partition may participate through inherited child constraints, so filtering a leaf table to roots is not a complete report of every relationship affecting it.

If you need evidence of the enforcement objects, pg_trigger.tgconstraint links a trigger to its constraint, while tgrelid identifies the table carrying the trigger. The catalog also identifies internally generated triggers. These fields provide a further inspection path in pg_trigger.

Keep the generated rows when the declared relationship is still required. For the reproduced case, the practical resolution is to display the root relationship in a logical schema view and retain its children in the diagnostic view. Once the parent links and both table endpoints are visible, the extra rows explain how PostgreSQL implements the reference across partitions.

Sources