patternsqlMinor
INSERT INTO table FROM SELECT * with nextval() specification
Viewed 0 times
specificationinsertwithintonextvalselectfromtable
Problem
I need to copy records within the same table changing only one field.
My table has default-generated sequence
So the problem is: when I try copy record in a simplified manner:
it throws error
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
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
And will this
Psql and Postgres version is 9.6.2.
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 thisColumn | 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.
Solution
As noted in the comments there is no special syntax for such task.
You could to use combination of functions
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:
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;Code Snippets
--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;select string_agg(attname,',' order by attnum)
from pg_attribute
where attrelid = 'public.t'::regclass and attnum > 0;Context
StackExchange Database Administrators Q#173103, answer score: 6
Revisions (0)
No revisions yet.