CODEWITHSUNDEEP
postgresql
sandesh Jadhav
sandesh Jadhav

Reputation: 329

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

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 Collation 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

Upvotes: 0

Views: 4575

Answers (1)

jian
jian

Reputation: 4824

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.

Upvotes: 0

Related Questions