cannot cast type to

What it means

A cast (value::type or CAST(value AS type)) only works when PostgreSQL has a rule for turning the source type into the target type. There’s one from integer to bigint, numeric, text or boolean, but none from integer to uuid, timestamp or date, and none from boolean to date. PostgreSQL checks this while parsing, before it reads any rows, so the error doesn’t depend on your data:

ERROR:  cannot cast type integer to uuid
LINE 1: SELECT ref::uuid FROM seo_err_pg.products;
                  ^

Two rules help predict it:

• Any type can be cast to text, and text can be cast to any type whose input accepts the string. So x::text::uuid gets past this error, but only works if the text looks like a UUID.
• Casts are about representation, not meaning. An integer could be seconds since 1970, milliseconds, or days; PostgreSQL won’t guess, so there’s a function for each meaning instead.

The JSON relative, cannot cast jsonb object to type integer, is a different error (code 22023): there the cast exists, but the value is an object rather than a number. Take the field out first: (data->>'qty')::integer.

Common causes

1. Epoch numbers to timestamps (1790845200::timestamp) or timestamps to numbers.
2. Changing an id column to uuid in a migration (ALTER COLUMN id TYPE uuid USING id::uuid).
3. A cast that makes no sense for the data, often from a mixed-up column (true::date), or an array cast to a single value (integer[] to integer).
4. Code written for another database whose casts are looser, such as MySQL’s.

How to fix it

Find out which casts exist

SELECT castsource::regtype, casttarget::regtype, castcontext
FROM pg_cast
WHERE castsource = 'integer'::regtype
ORDER BY 2;

castcontext is e (only explicit), a (also on assignment) or i (anywhere, implicitly). Casts to and from text don’t appear, because every type gets them automatically. In psql, \dC integer lists the casts to and from integer.

Use a conversion function

Changing a column to uuid

There’s no meaningful conversion from a sequence number to a UUID. Add a new column, fill it, and move references over:

ALTER TABLE products ADD COLUMN uid uuid NOT NULL DEFAULT gen_random_uuid();

Then update the tables that reference the old id, and swap the primary key. If you only need a UUID-shaped value derived from the number (to keep ids stable across systems), you can build one from text: lpad(to_hex(id), 32, '0')::uuid turns 7 into 00000000-0000-0000-0000-000000000007.

Go through text only when the text form is valid

ref::text::uuid passes the cast check, then fails with invalid input syntax for type uuid for a value like 7. Going through text is right when the text already has the target’s format: a text column of dates in ISO form, or of UUIDs.

Reproduce it

On PostgreSQL 18.6:

CREATE TABLE products (id integer PRIMARY KEY, sku text, created_at timestamp, ref integer);

SELECT true::date;
SELECT created_at::integer FROM products;
SELECT ref::uuid FROM products;
SELECT ARRAY[1,2]::integer;
SELECT 1790845200::timestamp;

ERROR:  cannot cast type boolean to date
ERROR:  cannot cast type timestamp without time zone to integer
ERROR:  cannot cast type integer to uuid
ERROR:  cannot cast type integer[] to integer
ERROR:  cannot cast type integer to timestamp without time zone

Each with a LINE pointer at the ::. ALTER TABLE products ALTER COLUMN ref TYPE uuid USING ref::uuid failed the same way. The conversions worked: to_timestamp(1790845200) AT TIME ZONE 'UTC' gave 2026-10-01 09:00:00, and extract(epoch FROM created_at)::bigint gave 1790845200. ref::boolean worked (that cast exists). '{"a":1}'::jsonb::integer gave cannot cast jsonb object to type integer, while '5'::jsonb::integer returned 5.

With \set VERBOSITY verbose, psql shows the code: ERROR: 42846: cannot cast type boolean to date. PostgreSQL 14.24 gives the same messages.

In Inlet

The structure editor changes a column’s type, shows the DDL, and warns when the change rewrites the table. When a statement fails, Inlet shows the error at the position the server reports; with your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix it, sending the schema, the SQL and the error, never rows.