invalid input syntax for type

This error occurs when you try to insert or convert a value that doesn't match the expected data type format.

The invalid input syntax error means the value can't be converted to the required type.

Understanding the Error

ERROR: invalid input syntax for type integer: "abc"

PostgreSQL can't parse the value as the expected data type.

Common Causes

1. String Where Number Expected

SQL

INSERT INTO products (price) VALUES ('abc');  -- Error!
INSERT INTO products (price) VALUES (19.99);  -- Works

2. Invalid Date Format

SQL

INSERT INTO events (event_date) VALUES ('not-a-date');  -- Error!
INSERT INTO events (event_date) VALUES ('2024-03-15');  -- Works

3. Invalid Boolean Value

SQL

UPDATE users SET active = 'yes';  -- Error!
UPDATE users SET active = true;   -- Works
UPDATE users SET active = 'true'; -- Also works

4. Invalid UUID

SQL

INSERT INTO items (id) VALUES ('not-a-uuid');  -- Error!
INSERT INTO items (id) VALUES ('550e8400-e29b-41d4-a716-446655440000');  -- Works

How to Fix It

Solution 1: Use Correct Format

SQL

-- Integer
INSERT INTO orders (quantity) VALUES (5);

-- Decimal
INSERT INTO products (price) VALUES (19.99);

-- Date (ISO format recommended)
INSERT INTO events (event_date) VALUES ('2024-03-15');

-- Timestamp
INSERT INTO logs (created_at) VALUES ('2024-03-15 10:30:00');

-- Boolean
UPDATE users SET active = true;  -- or false, 't', 'f', '1', '0'

-- UUID
INSERT INTO items (id) VALUES ('550e8400-e29b-41d4-a716-446655440000');

Solution 2: Cast or Convert Values

SQL

-- Safe casting with NULLIF for empty strings
INSERT INTO orders (quantity)
VALUES (NULLIF('', '')::INTEGER);

-- Using CASE for conditional conversion
INSERT INTO products (price)
VALUES (
  CASE WHEN '19.99' ~ '^[0-9.]+$'
  THEN '19.99'::DECIMAL
  ELSE 0
  END
);

Solution 3: Validate in Application

JAVASCRIPT

function createOrder(quantity) {
  const qty = parseInt(quantity, 10);
  if (isNaN(qty)) {
    throw new Error('Quantity must be a number');
  }
  return db.query('INSERT INTO orders (quantity) VALUES ($1)', [qty]);
}

Solution 4: Use TRY/CATCH in PL/pgSQL

SQL

CREATE FUNCTION safe_to_int(text) RETURNS INTEGER AS $$
BEGIN
  RETURN $1::INTEGER;
EXCEPTION
  WHEN invalid_text_representation THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

SELECT safe_to_int('abc');  -- Returns NULL instead of error

Solution 5: Generate Valid UUIDs

SQL

-- Use gen_random_uuid() for new UUIDs
INSERT INTO items (id) VALUES (gen_random_uuid());

-- Or uuid-ossp extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
INSERT INTO items (id) VALUES (uuid_generate_v4());

Common Type Formats

SQL

-- Integer: whole numbers
42, -17, 0

-- Decimal/Numeric: numbers with decimals
19.99, -3.14, 0.0

-- Boolean: true/false values
true, false, 't', 'f', '1', '0', 'yes', 'no'

-- Date: YYYY-MM-DD
'2024-03-15'

-- Timestamp: YYYY-MM-DD HH:MI:SS
'2024-03-15 10:30:00'

-- UUID: 8-4-4-4-12 hex format
'550e8400-e29b-41d4-a716-446655440000'

-- JSON/JSONB: valid JSON
'{"key": "value"}'
'[1, 2, 3]'

Best Practices

Validate input before inserting
Use parameterized queries to avoid type issues
Use appropriate data types in schema design
Handle conversion errors gracefully
Use ISO date formats for consistency