CODEWITHSUNDEEP
postgresqlinsert
banbar
banbar

Reputation: 299

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?

Upvotes: 1

Views: 3492

Answers (2)

Torc
Torc

Reputation: 1322

In addition to Laurenz's answer, it's worth noting that you can call the next value for each record in your serial sequence within your insert.

One way you could do it requires that you know the name of your sequence beforehand. By default the naming convention for a serial sequence will be tablename_id_seq where tablename in this case would be t2.

INSERT INTO t2
SELECT
    nextval('t2_id_seq')
  , t1.*
FROM t1

For more details on dealing with sequences:

Auto-generated sequences will adhere to the pattern ${table}_${column}_seq.

You can find all sequences by running the following queries:

/* Version 10+ */
SELECT
  *
FROM pg_sequences -- Not to be confused with `pg_sequence`
WHERE sequencename LIKE '%t2%'
;

/* Version 9.5+ */
-- Returns the sequences associated with a table
SELECT 
  pg_get_serial_sequence('schema.tablename', 'columnname')
;

-- Returns sequences accessible to the user, not those owned by the user
SELECT
  *
FROM information_schema.sequences
WHERE sequence_name LIKE '%t2%'
;

-- Return sequences owned by the current user
SELECT 
  n.nspname AS sequence_schema, 
  c.relname AS sequence_name,
  u.usename AS owner
FROM pg_class c 
  JOIN pg_namespace n ON n.oid = c.relnamespace
  JOIN pg_user u ON u.usesysid = c.relowner
WHERE c.relkind = 'S'
  AND u.usename = current_user;

/* Version 8.1+ */
-- Returns sequences accessible to the user, not those owned by the user
SELECT 
  relname
FROM pg_class
WHERE relkind = 'S' -- 'S' for sequence
;

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246798

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.

Upvotes: 1

Related Questions