EC
42P16
PostgreSQLERRORCommonSyntax Error or Access Rule ViolationHIGH confidence

invalid table definition

What this means

A CREATE TABLE or ALTER TABLE statement produced a table definition that violates Postgres structural rules. This includes circular inheritance, duplicate column names, invalid partitioning specifications, or a PRIMARY KEY on a column that permits NULLs.

Why it happens
  1. 1Duplicate column name in the CREATE TABLE column list
  2. 2PRIMARY KEY specified on a column already declared NOT NULL with a separate UNIQUE constraint (not an error, but see below)
  3. 3Inheritance cycle: table A inherits from B which inherits from A
  4. 4Partitioned table missing the PARTITION BY clause
  5. 5Conflicting column type in a partitioned table that does not match the partition key type
How to reproduce

A CREATE TABLE has a duplicate column name.

trigger — this will error
trigger — this will error
CREATE TABLE events (
  id   SERIAL PRIMARY KEY,
  name TEXT,
  name TEXT -- duplicate column name
);

expected output

ERROR:  column "name" specified more than once

Fix 1

Remove the duplicate column

WHEN When the same column name appears more than once in the column list.

Remove the duplicate column
CREATE TABLE events (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

Why this works

Postgres validates the column list in DefineRelation() before any catalog entries are written. Finding a duplicate column name raises the error immediately. Removing the duplicate allows the table to be created.

Fix 2

Fix the partitioning specification

WHEN When the error relates to an invalid PARTITION BY clause.

Fix the partitioning specification
-- Correct partitioned table definition:
CREATE TABLE measurements (
  id         BIGSERIAL,
  recorded   DATE NOT NULL,
  value      NUMERIC
) PARTITION BY RANGE (recorded);

CREATE TABLE measurements_2024
  PARTITION OF measurements
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Why this works

Partitioned table validation checks that the partition key columns exist, have compatible types, and that partition bounds do not overlap. Each partition must be a separate CREATE TABLE PARTITION OF statement after the parent is created.

What not to do

✕ Use ALTER TABLE to add duplicate columns after creation

ALTER TABLE ADD COLUMN also raises an error for duplicate names; the fix is always to correct the schema definition.

Version notes
Postgres 10+

Declarative table partitioning introduced. The 42P16 error covers partitioning definition errors in addition to classic structural errors.

Sources
Official documentation ↗

src/backend/commands/tablecmds.c — DefineRelation()

CREATE TABLE ↗Table Partitioning ↗

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors