PostgreSQL Invalid Column Definition (SQLSTATE 42611)

PostgreSQL raises ERROR: invalid column definition with SQLSTATE 42611 (condition name invalid_column_definition) when a column definition in a CREATE TABLE, ALTER TABLE, or related DDL statement is structurally invalid — meaning it violates PostgreSQL's rules for how a column may be defined. This is a parse- or analysis-phase error, so no table modification occurs.

What This Error Means

SQLSTATE 42611 belongs to error class 42, which covers "Syntax Error or Access Rule Violation" errors. Errors in this class are detected during statement parsing or analysis, before any execution takes place. Because the statement never executes, no transaction state is corrupted — but the current transaction is still aborted in the usual PostgreSQL fashion, requiring a ROLLBACK before issuing new statements in the same session.

The error is specifically triggered by problems in individual column definitions: the type, constraints, default expressions, collation clauses, or storage parameters attached to a column are in a configuration that PostgreSQL does not permit. The error message typically identifies the problematic column and the nature of the violation.

Unlike 42601 (syntax error) which catches raw parse failures, 42611 is raised after the parser succeeds but the semantic validation of the column specification fails. This distinction matters: the SQL may be syntactically well-formed yet still describe a column definition that PostgreSQL considers invalid.

Common Causes

1. Specifying a collation for a non-character type. Collation clauses (COLLATE) are only valid on character types (text, varchar, char, name, bpchar). Applying one to an integer, timestamp, or other non-text type raises this error.

2. Combining incompatible column options. Certain constraint or option combinations are not allowed on the same column. For example, specifying GENERATED ALWAYS AS IDENTITY together with an explicit DEFAULT clause, or using GENERATED ALWAYS AS (<expr>) STORED (a generated column) while also specifying DEFAULT.

3. Invalid GENERATED column expression. A generated column expression that references another generated column, uses a volatile function, or references a column from another table will be rejected.

4. Using column options not valid for the context. Some column options are only valid in specific DDL contexts. For instance, certain storage parameters or compression options may only be accepted for particular types or in certain PostgreSQL versions.

5. Applying COLLATE to an array element type incorrectly. Specifying a collation at the column level for an array of a non-collatable type can trigger this error.

How to Fix invalid_column_definition

1. Remove or relocate the collation clause. If you are applying COLLATE to a non-character-type column, remove it or change the column type to one that supports collation.

   -- Wrong: integer does not support collation
   CREATE TABLE t (
       id integer COLLATE "en-US"
   );
   
   -- Correct: collation on a text column
   CREATE TABLE t (
       name text COLLATE "en-US"
   );
   

2. Fix incompatible identity or generated column definitions. Do not combine DEFAULT with GENERATED ALWAYS AS IDENTITY or with a generated column expression.

   -- Wrong: DEFAULT and GENERATED ALWAYS AS IDENTITY conflict
   CREATE TABLE t (
       id integer DEFAULT 1 GENERATED ALWAYS AS IDENTITY
   );
   
   -- Correct: use one or the other
   CREATE TABLE t (
       id integer GENERATED ALWAYS AS IDENTITY
   );
   

3. Correct a generated column expression. Make sure generated column expressions only reference base (non-generated) columns in the same table and use only immutable functions.

   -- Wrong: references another generated column
   CREATE TABLE t (
       a integer,
       b integer GENERATED ALWAYS AS (a * 2) STORED,
       c integer GENERATED ALWAYS AS (b + 1) STORED  -- b is generated
   );
   
   -- Correct: reference the base column
   CREATE TABLE t (
       a integer,
       b integer GENERATED ALWAYS AS (a * 2) STORED,
       c integer GENERATED ALWAYS AS (a * 2 + 1) STORED
   );
   

4. Check the PostgreSQL version. Some column options (compression methods, certain storage parameters) were introduced in specific versions. If a column definition works in a newer version but fails on an older one, verify that all options you are using are supported.

   -- COMPRESSION option was added in PostgreSQL 14
   CREATE TABLE t (
       payload text COMPRESSION lz4
   );
   -- On PostgreSQL 13 or earlier, remove the COMPRESSION clause
   

5. Isolate the offending column. If the table definition is large, comment out columns one at a time to identify which column's definition triggers the error, then inspect that column's type, constraints, and options.

Additional Information

• SQLSTATE 42611 is defined in the SQL standard and PostgreSQL maps it to invalid_column_definition. The full list of class 42 errors is documented in PostgreSQL Appendix A.
• Generated columns (GENERATED ALWAYS AS ... STORED) were introduced in PostgreSQL 12. Errors involving generated column definitions only appear on PostgreSQL 12 and later.
• The COMPRESSION column option was added in PostgreSQL 14. Using it on earlier versions produces a syntax error (42601) rather than 42611.
• Related SQLSTATE codes in class 42: 42601 (syntax error), 42703 (undefined column), 42P16 (invalid table definition). If you are getting a different class 42 error, check those condition names.
• Most PostgreSQL client drivers and ORMs (e.g., psycopg2, asyncpg, SQLAlchemy) surface this as a ProgrammingError or equivalent, with the SQLSTATE code included in the exception details.

Frequently Asked Questions

Why does my transaction get aborted even though the error is in a DDL statement? In PostgreSQL, any error inside a transaction block aborts the entire transaction. DDL errors are no exception — once 42611 is raised, the transaction enters an error state and all subsequent commands will fail with ERROR: current transaction is aborted, commands ignored until end of transaction block until you issue ROLLBACK.

Can this error appear in an ALTER TABLE statement? Yes. ALTER TABLE ... ADD COLUMN and ALTER TABLE ... ALTER COLUMN TYPE ... USING ... both parse full column definitions and will raise 42611 if the definition is invalid, for the same reasons as CREATE TABLE.

Is 42611 the same as a syntax error? No. A syntax error (42601) means PostgreSQL could not even parse the SQL text. 42611 means the SQL parsed successfully but the column definition describes something semantically invalid — a logically inconsistent or unsupported combination of type, constraints, or options.

How can I quickly find which column is causing the error? The error message usually names the column and the specific problem, for example: ERROR: column "foo" has pseudo-type void or ERROR: only table rows can have a collation. Read the full error message carefully. If the message is not specific enough, try creating the table with one column at a time to isolate the problem.