📋

Description

The `22P02` error, 'invalid text representation', occurs when PostgreSQL attempts to convert a text string into a different data type (e.g., numeric, date, boolean) but the string's format is incompatible with the target type. This typically happens during `INSERT` or `UPDATE` operations where input data does not conform to the column's expected format.

💬

Error Message

invalid text representation

🔍

Known Causes

3 known causes

⚠️

Mismatched Data Type Input

Attempting to insert or update a column with text data that cannot be implicitly or explicitly converted to the column's defined data type, such as placing 'hello' into an `INT` column.

⚠️

Incorrect Date/Time Format

Providing a date or time string that does not match PostgreSQL's expected format for `DATE`, `TIME`, or `TIMESTAMP` types, or a format that cannot be parsed without explicit conversion.

⚠️

Invalid Boolean Literal

Using a text string that PostgreSQL does not recognize as a valid boolean literal (e.g., 'maybe') when populating a `BOOLEAN` column.

🛠️

Solutions

5 solutions available

1. Fix Type Mismatch easy

Ensure value matches column type

1

Check column type

SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'users';

2

Fix integer column

-- Wrong:
INSERT INTO users (age) VALUES ('twenty');

-- Right:
INSERT INTO users (age) VALUES (20);

3

Fix UUID column

-- Wrong:
INSERT INTO users (id) VALUES ('not-a-uuid');

-- Right:
INSERT INTO users (id) VALUES ('550e8400-e29b-41d4-a716-446655440000');

2. Use Explicit Cast easy

Convert values to correct type

1

Cast string to integer

SELECT '123'::INTEGER;  -- PostgreSQL syntax
SELECT CAST('123' AS INTEGER);  -- Standard SQL

2

Cast string to UUID

SELECT '550e8400-e29b-41d4-a716-446655440000'::UUID;

3

Handle invalid cast gracefully

-- Using CASE to handle conversion errors
SELECT 
  CASE WHEN value ~ '^[0-9]+$' THEN value::INTEGER
       ELSE NULL
  END AS converted_value
FROM data;

3. Fix Boolean Values easy

Use valid boolean literals

1

Valid boolean values

-- True: TRUE, 't', 'true', 'y', 'yes', 'on', '1'
-- False: FALSE, 'f', 'false', 'n', 'no', 'off', '0'

INSERT INTO users (active) VALUES (TRUE);
INSERT INTO users (active) VALUES ('yes');

4. Fix Array Syntax easy

Use correct array literal format

1

Array syntax

-- Wrong:
INSERT INTO posts (tags) VALUES ('[tag1, tag2]');

-- Right:
INSERT INTO posts (tags) VALUES (ARRAY['tag1', 'tag2']);
INSERT INTO posts (tags) VALUES ('{tag1,tag2}');

5. Handle JSON Input easy

Use valid JSON format

1

Valid JSON/JSONB

-- Wrong:
INSERT INTO data (config) VALUES ('{name: value}');

-- Right (JSON requires double quotes):
INSERT INTO data (config) VALUES ('{"name": "value"}');

🔗

Related Errors

5 related errors

PostgreSQL Error 22P02: Invalid Text Representation