DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 01P01 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 01P01: Deprecated Feature — What It Means and How to Fix It

PostgreSQL error code 01P01 is a warning-level notice (SQLSTATE class 01) indicating that your query or configuration is using a deprecated feature — something that still works today but is scheduled for removal or replacement in a future PostgreSQL version. Unlike fatal errors, this won't stop your application immediately, but ignoring it is a ticking time bomb, especially before a major version upgrade. You'll most commonly encounter this during legacy code maintenance, database migrations, or when using outdated client drivers.

Top 3 Causes

1. Using Removed or Deprecated Data Types

Older PostgreSQL versions supported non-standard types like abstime, reltime, and tinterval. These were fully removed in PostgreSQL 12. If your codebase still references them, you'll see 01P01 on older versions and hard errors on newer ones.

-- Deprecated (removed in PostgreSQL 12)
SELECT CAST('2024-01-01' AS abstime);

-- Correct modern approach
SELECT CAST('2024-01-01' AS timestamptz);

-- Deprecated: Creating tables WITH OIDS
CREATE TABLE legacy_table (id INT) WITH OIDS;

-- Correct modern approach (OIDs are gone by default in PG 12+)
CREATE TABLE modern_table (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

2. Calling Deprecated Built-in Functions or Operators

Some built-in functions are soft-deprecated before being removed. PostgreSQL signals their use with 01P01 to give developers time to migrate.

-- Deprecated style: using epoch-based timestamp conversion loosely
SELECT to_timestamp(1609459200.0);

-- Preferred: explicit format string for clarity
SELECT to_timestamp('2021-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- Deprecated: broad stat reset without targeting
SELECT pg_stat_reset();

-- Preferred: targeted reset for specific objects
SELECT pg_stat_reset_single_table_counters('public.orders'::regclass);

-- Check which functions in your schema might reference deprecated internals
SELECT proname, pg_get_functiondef(oid) AS definition
FROM pg_proc
WHERE pg_get_functiondef(oid) ILIKE '%abstime%'
   OR pg_get_functiondef(oid) ILIKE '%reltime%';
Enter fullscreen mode Exit fullscreen mode

3. Using Deprecated Configuration Parameters (GUCs)

Setting deprecated postgresql.conf parameters or using SET with them triggers 01P01. For example, default_with_oids was deprecated in PostgreSQL 12 and fully removed in PostgreSQL 13.

-- This triggers 01P01 on PG 12, ERROR on PG 13+
-- SET default_with_oids = on;

-- Check currently active deprecated-style settings
SELECT name, setting, source, short_desc
FROM pg_settings
WHERE name IN (
    'default_with_oids',
    'sql_inheritance',
    'escape_string_warning'
);

-- Verify your server version to understand what's deprecated
SELECT version();
SELECT current_setting('server_version_num')::int AS version_num;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Audit your schema for deprecated types and functions using pg_proc and pg_type catalog queries.
  2. Replace deprecated types (abstime → timestamptz, oid columns → explicit BIGINT PKs).
  3. Remove deprecated GUC parameters from postgresql.conf before upgrading.
  4. Run pg_dump on your current DB and restore it on a new major version in a test environment — all 01P01 warnings (and any hard errors) will surface immediately. 
-- Scan for deprecated OID-based patterns in table definitions
SELECT c.relname, c.relhasoids
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relhasoids = true
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Read the official Release Notes before every major upgrade. The "Incompatibilities" and "Deprecated Features" sections are essential reading for DBAs.
  • Integrate plpgsql_check and check_postgres into your CI/CD pipeline to automatically catch deprecated usage in stored procedures and schema definitions before they reach production.

📖 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)