Search code examples
postgresqlinsert

INSERT INTO a table with serial using SELECT *

In Postgres, I have a table with many columns (e.g. t1(a, b, c, ..., z)). I need to obtain its subset through a select-from-where statement into a new table (e.g. t2), but this new table must have a serial attribute. So, t2 would like t2(id, a, b, c, ..., z), where id the serial attribute. In Postgres, this works:

INSERT INTO t2(a, b, c, d, ..., z)
SELECT * 
FROM t1 
WHERE <condition>

However, is it possible to achieve the same without writing all the attributes of t1?

Solution

  • You can define a view that is a simple SELECT of all but the serial column.

    Such views are updateable in PostgreSQL, so you can use it as the target for your INSERT.