while altering the columns data type got error 'ERROR: invalid input syntax for type uuid: "" '?

Question

In my vault_log table there is a column resp_id, the data type is varchar(40) and I want to alter the data type from varchar(40) to uuid but I get this error when trying to do so:

ERROR: invalid input syntax for type UUID:

This is the command I have used:

alter table vault_log 
    alter column resp_id type uuid using resp_id::uuid;

Table schema:

Column	Type	Nullable	Default
ac	character varying(20)	not null
scheme_code	character varying(20)	not null
req_ts	timestamp without time zone	not null
res_ts	timestamp without time zone	not null
resp	character varying(20)	not null
err	character varying(20)
resp_id	character varying(40)	not null
txn	uuid	not null
client_ip	character varying(20)
opr	character varying(20)	not null
vault_id	integer	not null	nextval('vault_log_vault_id_seq'::regclass)
mac_id	character varying

An empty string is not a valid UUID. Use NULL when there is no valid UUID, but you have to drop the current NOT NULL constraint. — Frank Heikens
– Frank Heikens, Commented May 26, 2022 at 9:29

jian · Accepted Answer · 2022-05-26 16:06:29Z

0

It should work.
demo
If not working means resp_id column stored some value(s) in it.
Obvious, everything can be cast to text. further info, see pg_cast

( SELECT DISTINCT
        typname
    FROM
        pg_cast c
        JOIN pg_type t ON c.casttarget = t.oid
    WHERE
        typname ILIKE '%uuid%')
UNION ALL ( SELECT DISTINCT
        typname
    FROM
        pg_cast c
        JOIN pg_type t ON c.castsource = t.oid
    WHERE
        typname ILIKE '%uuid%');

return zero rows.
zero rows means: There is no explicit cast to and from data type uuid.
So you should just add an new uuid column.

answered May 26, 2022 at 16:06

jian

5,0231 gold badge29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

while altering the columns data type got error 'ERROR: invalid input syntax for type uuid: "" '?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related