6

I need to copy records within the same table changing only one field. My table has default-generated sequence entry_id_seq, however I am not sure id column is SERIAL (how to check that?).

\d tab returns just this

      Column      |              Type              |            Modifiers            
 -----------------+--------------------------------+------------------------
       id         |             integer            |            not null
...
Indexes:
"tab_entry_pkey" PRIMARY KEY, btree (id)

So the problem is: when I try copy record in a simplified manner:

insert into tab_entry select * from tab_entry where id = 3052;

it throws error

ERROR:  duplicate key value violates unique constraint "tab_entry_pkey"
DETAIL:  Key (id)=(3052) already exists.

Default sequence doesn't generate next value by default. Is there any concise syntax allowing inserting and changing single field without full table specification aka FROM tab(col1, col2, col3, ..., col N)?

The table has many fields, so I don't want to write them all, as this will affect code readability. I want something like this, but this syntax doesn't work

insert into tab_entry(id, *) select nextval('seq'), * from tab_entry where id = 3052;

And will this SELECT nextval('seq') approach work if there be multiple records at once?

Psql and Postgres version is 9.6.2.

Suncatcher
  • 367
  • 2
  • 4
  • 12
  • have you designed this table? – McNets May 09 '17 at 15:25
  • No, it is standard schema of one open-source package Wallabag. Actually it is not intended for manual editing, but currently they haven't developed this functionality at all. – Suncatcher May 09 '17 at 15:27
  • 2
    Multiple rows are no problem, but you cannot avoid listing all the columns (unless you want to use dynamic SQL, I think). And here is what serial is: https://dba.stackexchange.com/a/47107/6219 – András Váczi May 09 '17 at 15:29
  • 1
    insert into tab_entry(id,f1,f2,f3,..) select nextval('seq'),f1,f2,f3,... from tab_entry where id = 3052; – McNets May 09 '17 at 15:31
  • @dezso, and if I want to use dynamic SQL? How to achieve that? – Suncatcher May 09 '17 at 16:04
  • What's wrong with spelling out the columns? It's good coding style to begin with. Plus: typically SQL clients offer code-completion for table and column names. Using such a feature, writing the insert statement specifying the column names would have taken less time then writing this question. –  May 09 '17 at 16:21
  • psql and code-completion? in a terminal SSH session? seriously? – Suncatcher May 09 '17 at 18:37

1 Answers1

6

As noted in the comments there is no special syntax for such task.
You could to use combination of functions to_json(b), json(b)_set and json(b)_populate_record:

--drop table if exists t;
create table t(i serial primary key, x int, y text, z timestamp);
insert into t values(default, 1, 'a', now()),(default, 2, 'b', now());

insert into t
select n.*
from t, jsonb_populate_record(
  null::t, 
  jsonb_set(
    to_jsonb(t.*),
    array['i'],
    to_jsonb(nextval('t_i_seq')))) as n;

select * from t;

But I think it is not much simpler then just enumerate all columns. You always can use this relatively simple query to get the columns list of table:

select string_agg(attname,',' order by attnum)
from pg_attribute
where attrelid = 'public.t'::regclass and attnum > 0;
Abelisto
  • 1,549
  • 1
  • 9
  • 13
  • Thanks. How can I use this column query with my initial query? If I use insert into tab_entry(select string_agg(attname,',' order by attnum) from pg_attribute where attrelid = 'public.tab_entry'::regclass and attnum > 0) it throws syntax error. – Suncatcher May 10 '17 at 09:35
  • @Suncatcher You can not. It is for "Copy-paste" technology :o) – Abelisto May 10 '17 at 09:51