How to run UPDATE query on PostgreSQL which involves UUID?

Question

I have a PostgreSQL table, and there's a user_id column which data type is uuid.

Given that uuid, I want to update a expired_at column which datatype is timestampz.

I tried a few queries, like:

UPDATE public.session SET expired_at = '2025-06-18 15:41:30.489 +00:00' WHERE user_id::text = "32e9b5cb-d033-4c57-a4b9-dcca911e013";
UPDATE public.session SET expired_at = '2025-06-18 15:41:30.489 +00:00' WHERE user_id = cast('32e9b5cb-d033-4c57-a4b9-dcca911e013') as uuid;
UPDATE public.session SET expired_at = '2025-06-18 15:41:30.489 +00:00' WHERE user_id = '32e9b5cb-d033-4c57-a4b9-dcca911e013'::uuid;

Nothing works. Postgres will complain something like:

SQL Error [22P02]: ERROR: invalid input syntax for type uuid: "32e9b5cb-d033-4c57-a4b9-dcca911e013"
  Position: 89

Error position: line: 1 pos: 88

What's the correct syntax?

In PostgreSQL version 16 and above, you can catch things like that with pg_input_is_valid(). In earlier versions, you can define your own. — Zegarek
– Zegarek, Commented Jun 18 at 19:48

Adrian Klaver · Accepted Answer · 2025-06-18 15:19:28Z

Per UUID Type:

A UUID is written as a sequence of lower-case hexadecimal digits, in several groups separated by hyphens, specifically a group of 8 digits followed by three groups of 4 digits followed by a group of 12 digits, for a total of 32 digits representing the 128 bits.

Your value 32e9b5cb-d033-4c57-a4b9-dcca911e013 has only 11 digits in the final block so:

select '32e9b5cb-d033-4c57-a4b9-dcca911e013'::uuid;
ERROR:  invalid input syntax for type uuid: "32e9b5cb-d033-4c57-a4b9-dcca911e013"

If you do something like:

select '32e9b5cb-d033-4c57-a4b9-dcca911e0134'::uuid;
                 uuid                 
--------------------------------------
 32e9b5cb-d033-4c57-a4b9-dcca911e0134

It works.

I would review what is actually in the user_id column and if the column is actually of uuid type. If it is the value you are filtering on in your question cannot exist in the field.

ValNik · Accepted Answer · 2025-06-18 15:27:08Z

Your UUID constant is incorrect

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11  -- correct uuid
32e9b5cb-d033-4c57-a4b9-dcca911e013   -- incorrect, 1 symbol is absent

All forms for update query works.

create table session(user_id uuid,expired_at timestamp);
insert into session values
 ('32e9b5cb-d033-4c57-a4b9-dcca911e0130'::uuid,'2025-06-19 15:41:30.489 +00:00')
;

Literal "32e9b5cb-d033-4c57-a4b9-dcca911e0130" - is column name.
Use '32e9b5cb-d033-4c57-a4b9-dcca911e0130'.

UPDATE session SET expired_at = '2025-06-19 15:41:30.489 +00:00' 
WHERE user_id::text = '32e9b5cb-d033-4c57-a4b9-dcca911e0130';

select * from session;

user_id	expired_at
32e9b5cb-d033-4c57-a4b9-dcca911e0130	2025-06-19 15:41:30.489

UPDATE session SET expired_at = '2025-06-20 15:41:30.489 +00:00' 
WHERE user_id = cast('32e9b5cb-d033-4c57-a4b9-dcca911e0130' as uuid);

user_id	expired_at
32e9b5cb-d033-4c57-a4b9-dcca911e0130	2025-06-20 15:41:30.489

UPDATE session SET expired_at = '2025-06-21 15:41:30.489 +00:00' 
WHERE user_id = '32e9b5cb-d033-4c57-a4b9-dcca911e0130'::uuid;

user_id	expired_at
32e9b5cb-d033-4c57-a4b9-dcca911e0130	2025-06-21 15:41:30.489

fiddle

AVTUNEY · Accepted Answer · 2025-06-18 09:33:18Z

1

" are for identifiers

' are for string literals

This will work

UPDATE public.session
SET expired_at = '2025-06-18 15:41:30.489+00'
WHERE user_id = '32e9b5cb-d033-4c57-a4b9-dcca911e013'::uuid;

answered Jun 18 at 9:33

AVTUNEY

1,35310 silver badges14 bronze badges

3 Comments

anta40 Jun 18 at 10:28

Hmm didn't work: SQL Error [22P02]: ERROR: invalid input syntax for type uuid: "32e9b5cb-d033-4c57-a4b9-dcca911e013" Position: 85. I tested this on the latest DBeaver though. Will try using psql tonight.

AVTUNEY Jun 18 at 11:16

@anta40 DBeaver is auto-wrapping your UUID string in double quotes. turning it into an invalid UUID literal. psql will work, 100%

Adrian Klaver Jun 18 at 15:11

@AVTUNEY, you are confusing the value as shown in the error message with what is entered. Postgres does the double quote of the value in an error message. Given a field id that is an integer trying to insert a value of 'one' will result in the error message: ERROR: invalid input syntax for type integer: "one"

Collectives™ on Stack Overflow

How to run UPDATE query on PostgreSQL which involves UUID?

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related