Postgres change or check datatype of all column during select into statement [closed]-CodePudding

INSERT INTO table_name( a, b, c, d) 
SELECT t1.a a, t1.b b, t1.c c, t1.d d 
FROM table_name t1
AS t(a bigint, b character varying, c numeric, d numeric)

But I am getting PG::SyntaxError: ERROR: syntax error at or near "AS" Please anyone help me out.

CodePudding user response：

As a_horse_with_no_name said - cast the select list expressions. As an alternative to numerous alter table statements you may do this: (1) create table with a temp name by using the select query then (2) drop the original table and finally (3) rename the one with the temp name. Still you will have to take care about constraints and indexes that may exist in the original table.

-- 1.
create table tmp_table_name as
  SELECT a::bigint as a, b::text as b, c::numeric as c, d::numeric as d
  FROM table_name;
-- 2.
drop table table_name;
-- 3.
alter table tmp_table_name rename to table_name;