CODEWITHSUNDEEP
postgresql
Manu K R
Manu K R

Reputation: 43

Using SELECT in USING clause in ALTER TABLE ALTER COLUMN?

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

Upvotes: 2

Views: 802

Answers (1)

klin
klin

Reputation: 121534

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);

Upvotes: 4

Related Questions