PostgreSQL Error Codes

Error Code Categories

23505: unique_violation
23503: foreign_key_violation

42601: syntax_error
42P01: undefined_table

08001: connection_failure
08006: connection_lost

00000: successful_completion

🔹 Common Constraint Violation Errors (Class 23)

Constraint violations occur when data doesn't meet table rules. These are the most common errors when inserting or updating data in PostgreSQL databases.

-- 23505: Unique Violation
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

INSERT INTO users (email) VALUES ('[email protected]');
-- This will fail with error 23505
INSERT INTO users (email) VALUES ('[email protected]');

-- Error: duplicate key value violates unique constraint "users_email_key"
-- SQL state: 23505

-- 23503: Foreign Key Violation
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id)
);

-- This will fail with error 23503
INSERT INTO orders (user_id) VALUES (999);

-- Error: insert or update violates foreign key constraint
-- SQL state: 23503

-- 23502: Not Null Violation
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- This will fail with error 23502
INSERT INTO products (id) VALUES (1);

-- Error: null value in column "name" violates not-null constraint
-- SQL state: 23502

🔹 Syntax and Object Errors (Class 42)

Class 42 errors indicate problems with SQL syntax or references to non-existent database objects. These typically occur during development when writing queries.

-- 42601: Syntax Error
-- Missing comma
SELECT name email FROM users;
-- Error: syntax error at or near "email"
-- SQL state: 42601

-- Correct syntax
SELECT name, email FROM users;

-- 42P01: Undefined Table
SELECT * FROM non_existent_table;
-- Error: relation "non_existent_table" does not exist
-- SQL state: 42P01

-- 42703: Undefined Column
SELECT invalid_column FROM users;
-- Error: column "invalid_column" does not exist
-- SQL state: 42703

-- 42P07: Duplicate Table
CREATE TABLE users (id INTEGER);
-- If table exists, error:
-- Error: relation "users" already exists
-- SQL state: 42P07

-- Use IF NOT EXISTS
CREATE TABLE IF NOT EXISTS users (id INTEGER);

-- 42883: Undefined Function
SELECT unknown_function(name) FROM users;
-- Error: function unknown_function does not exist
-- SQL state: 42883

🔹 Data Type Errors (Class 22)

Data type errors occur when values don't match expected column types or when data conversion fails. Common during data import or user input validation.

-- 22P02: Invalid Text Representation
CREATE TABLE numbers (
    id SERIAL PRIMARY KEY,
    value INTEGER
);

-- This will fail with error 22P02
INSERT INTO numbers (value) VALUES ('abc');
-- Error: invalid input syntax for type integer: "abc"
-- SQL state: 22P02

-- Correct usage
INSERT INTO numbers (value) VALUES (123);

-- 22001: String Data Right Truncation
CREATE TABLE short_text (
    code VARCHAR(5)
);

-- This will fail with error 22001
INSERT INTO short_text (code) VALUES ('TOOLONG123');
-- Error: value too long for type character varying(5)
-- SQL state: 22001

-- 22003: Numeric Value Out of Range
CREATE TABLE small_numbers (
    tiny SMALLINT  -- Range: -32768 to 32767
);

-- This will fail with error 22003
INSERT INTO small_numbers (tiny) VALUES (99999);
-- Error: smallint out of range
-- SQL state: 22003

-- 22012: Division by Zero
SELECT 10 / 0;
-- Error: division by zero
-- SQL state: 22012

-- Safe division
SELECT CASE WHEN 0 != 0 THEN 10 / 0 ELSE NULL END;

🔹 Connection Errors (Class 08)

Connection errors indicate problems establishing or maintaining database connections. These often occur in production environments with network or configuration issues.

-- 08001: Connection Failure
-- Cannot connect to database
psql -h wrong_host -U postgres
-- Error: could not connect to server
-- SQL state: 08001

-- 08006: Connection Lost
-- Connection dropped during query
-- Error: server closed the connection unexpectedly
-- SQL state: 08006

-- 08003: Connection Does Not Exist
-- Trying to use closed connection
-- Error: connection does not exist
-- SQL state: 08003

-- 08P01: Protocol Violation
-- Client/server protocol mismatch
-- Error: protocol error
-- SQL state: 08P01

🔹 Transaction Errors (Class 25)

Transaction errors occur during transaction management. Understanding these helps maintain data consistency in multi-step operations.

-- 25P02: Transaction Aborted
BEGIN;
INSERT INTO users (email) VALUES ('[email protected]');
-- Some error occurs
SELECT * FROM non_existent_table;
-- Transaction is now aborted
INSERT INTO users (email) VALUES ('[email protected]');
-- Error: current transaction is aborted
-- SQL state: 25P02
ROLLBACK;

-- Correct approach
BEGIN;
INSERT INTO users (email) VALUES ('[email protected]');
COMMIT;

-- 25001: Active SQL Transaction
-- Cannot perform operation in transaction
BEGIN;
CREATE DATABASE newdb;
-- Error: CREATE DATABASE cannot run inside a transaction block
-- SQL state: 25001
ROLLBACK;

-- 25P01: No Active Transaction
COMMIT;
-- Error: there is no transaction in progress
-- SQL state: 25P01

🔹 Permission Errors (Class 42501)

Permission errors occur when users lack necessary privileges for database operations:

-- 42501: Insufficient Privilege
-- User without permission tries to access table
SELECT * FROM sensitive_data;
-- Error: permission denied for table sensitive_data
-- SQL state: 42501

-- Grant permission (as superuser)
GRANT SELECT ON sensitive_data TO username;

-- Try to create table without permission
CREATE TABLE new_table (id INTEGER);
-- Error: permission denied for schema public
-- SQL state: 42501

-- Grant schema permission
GRANT CREATE ON SCHEMA public TO username;

🔹 Handling Errors in Application Code

Best practices for handling PostgreSQL errors in your applications:

// Node.js example with pg library
const { Client } = require('pg');
const client = new Client();

try {
    await client.connect();
    await client.query('INSERT INTO users (email) VALUES ($1)', 
        ['[email protected]']);
} catch (error) {
    // Check error code
    if (error.code === '23505') {
        console.log('Email already exists');
    } else if (error.code === '23503') {
        console.log('Foreign key violation');
    } else if (error.code === '42P01') {
        console.log('Table does not exist');
    } else {
        console.log('Database error:', error.message);
    }
} finally {
    await client.end();
}

# Python example with psycopg2
import psycopg2
from psycopg2 import errors

try:
    conn = psycopg2.connect("dbname=mydb user=postgres")
    cur = conn.cursor()
    cur.execute("INSERT INTO users (email) VALUES (%s)", 
        ('[email protected]',))
    conn.commit()
except errors.UniqueViolation:
    print("Email already exists")
except errors.ForeignKeyViolation:
    print("Foreign key violation")
except errors.UndefinedTable:
    print("Table does not exist")
finally:
    conn.close()

🔹 Error Code Quick Reference