Using SELECT in USING clause in ALTER TABLE ALTER COLUMN?

Question

I have a table1 with column 'abc' with type as character varying I want it to be modified to bigint. The column values should be replaced with a value from external table like a foreign key reference, i.e id corresponding to entry of this value in table2 How can i do it? My attempt was :

 ALTER TABLE table1 ALTER COLUMN abc TYPE bigint USING SELECT some_id from table2 where col1=abc

klin klin · Accepted Answer · 2019-11-26T05:55:16

You cannot use a query in this context. Create a function:

create or replace function get_some_id(varchar)
returns bigint language sql as $$
    select some_id from table2 where col1 = $1
$$;

alter table table1 alter column abc type bigint using get_some_id(abc);

Using SELECT in USING clause in ALTER TABLE ALTER COLUMN?

1 Answers