DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 54011 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 54011: Too Many Columns

PostgreSQL error 54011: too many columns occurs when you attempt to create a table, view, or query result that exceeds PostgreSQL's hard limit of 1,600 columns per table. This is a program limit error (class 54) and will immediately abort the offending DDL or DML statement. While 1,600 columns may sound like a lot, poorly designed schemas—especially those migrated from legacy systems—can hit this ceiling surprisingly fast.

Top 3 Causes

1. Wide Table Anti-Pattern

Designing a table with hundreds or thousands of columns (e.g., one column per survey question, one column per day) is the most common cause.

-- BAD: adding a column per date
CREATE TABLE daily_revenue (
    store_id          INT,
    rev_2024_01_01    NUMERIC,
    rev_2024_01_02    NUMERIC,
    -- ... keep going past 1,600 and you'll hit ERROR 54011
    rev_2028_06_15    NUMERIC
);

-- GOOD: normalized row-per-day design
CREATE TABLE daily_revenue_normalized (
    store_id      INT  NOT NULL,
    revenue_date  DATE NOT NULL,
    revenue       NUMERIC(15,2) NOT NULL DEFAULT 0,
    PRIMARY KEY (store_id, revenue_date)
);
Enter fullscreen mode Exit fullscreen mode

2. Schema Migration from Another DBMS

Migrating a wide legacy schema from Oracle, MySQL, or SQL Server without redesigning it will immediately trigger this error in PostgreSQL.

-- Check column counts before migration
SELECT
    table_name,
    COUNT(column_name) AS col_count
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name
HAVING COUNT(column_name) > 1500
ORDER BY col_count DESC;
Enter fullscreen mode Exit fullscreen mode

Identify any table returning rows here and redesign it before loading data into PostgreSQL.

3. Dynamic PIVOT / Crosstab with Many Categories

Dynamically generating a PIVOT query where the number of distinct category values exceeds 1,600 will fail at runtime.

-- This blows up when there are > 1,600 distinct categories
SELECT
    user_id,
    MAX(CASE WHEN category = 'A001' THEN value END) AS A001,
    MAX(CASE WHEN category = 'A002' THEN value END) AS A002
    -- ... more than 1,600 categories → ERROR 54011
FROM events
GROUP BY user_id;

-- Fix: limit to top N categories
WITH top_cats AS (
    SELECT category FROM events
    GROUP BY category ORDER BY COUNT(*) DESC LIMIT 50
)
SELECT
    user_id,
    MAX(CASE WHEN category = 'A001' THEN value END) AS A001
    -- only pivot top 50 categories
FROM events
WHERE category IN (SELECT category FROM top_cats)
GROUP BY user_id;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Use JSONB for dynamic attributes — instead of adding a new column for every attribute, store them in a single JSONB column:

CREATE TABLE product_attributes (
    product_id  INT PRIMARY KEY,
    attrs       JSONB NOT NULL DEFAULT '{}'
);

-- Store any number of attributes without schema changes
INSERT INTO product_attributes (product_id, attrs)
VALUES (1, '{"color": "red", "size": "L", "weight_kg": 1.2}');

-- Index for fast lookups
CREATE INDEX idx_product_attrs ON product_attributes USING GIN (attrs);

-- Query a specific attribute
SELECT product_id, attrs->>'color' AS color
FROM product_attributes
WHERE attrs @> '{"size": "L"}';
Enter fullscreen mode Exit fullscreen mode

Normalize wide tables into key-value pairs:

CREATE TABLE entity_attributes (
    entity_id   INT  NOT NULL,
    attr_name   TEXT NOT NULL,
    attr_value  TEXT,
    PRIMARY KEY (entity_id, attr_name)
);
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Add a column-count gate to your CI/CD pipeline. Run the query below against every migration script before it reaches production: 
-- Alert when any table exceeds 500 columns
SELECT table_name, COUNT(*) AS col_count
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name
HAVING COUNT(*) > 500;
Enter fullscreen mode Exit fullscreen mode
  1. Enforce normalization in design reviews. Any table with more than 100 columns should require explicit DBA sign-off. Adopt a schema linting tool (e.g., squawk) in pull requests to catch overly wide tables before they reach the database. Prefer JSONB or a separate attribute table for truly dynamic data models.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)