How to insert values into a table with default values from a select query in PostgreSQL?

Question

Is it possible to INSERT values into a PostgreSQL table from a SELECT statement and to use DEFAULT values for the columns that are null?

In my case, the SELECT statement is selecting from JSON.

In the attempts below, I was successful by explicitly retrieving the sequence but I am hoping there is a way to either INSERT using the DEFAULT values. Or select DEFAULT values without having to call the default functions explicitly.

-- Example table
create table animals
(
 id serial,
 nm character varying (30) NOT NULL, --name
 typ character varying(10),
 tvi integer,
 tvf numeric(8,3)
);
insert into animals VALUES (DEFAULT,'mouse','m',4,12.45);
select row_to_json(a) from animals a;
select * from json_populate_record(null::animals,'{"id":null,"nm":"mouse","typ":"m","tvi":4,"tvf":12.450}');
--All good.
-- Attempt #1
INSERT INTO animals
select id ,nm,typ,tvi,tvf from json_populate_record(null::animals,'{"id":null,"nm":"mouse","typ":"m","tvi":4,"tvf":12.450}');
/*
ERROR: null value in column "id" violates not-null constraint
DETAIL: Failing row contains (null, mouse, m, 4, 12.450).
********** Error **********
ERROR: null value in column "id" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null, mouse, m, 4, 12.450).
*/
-- Attempt #2
INSERT INTO animals
select DEFAULT,nm,typ,tvi,tvf from json_populate_record(null::animals,'{"id":null,"nm":"mouse","typ":"m","tvi":4,"tvf":12.450}');
/* I didn't expect this to work, but it does illustrate what I am trying to accomplish
ERROR: syntax error at or near "DEFAULT"
LINE 2: select DEFAULT,nm,typ,tvi,tvf from json_populate_record(null...
 ^
********** Error **********
ERROR: syntax error at or near "DEFAULT"
SQL state: 42601
Character: 28
*/
-- Attempt #3
INSERT INTO animals
select nextval('animals_id_seq'::regclass),nm,typ,tvi,tvf from json_populate_record(null::animals,'{"id":null,"nm":"mouse","typ":"m","tvi":4,"tvf":12.450}');
/* This works, but I'm hoping for a way to accomplish this without knowing the underlying functions generating the default values.
Query returned successfully: one row affected, 11 msec execution time.
*/
select version(); --'PostgreSQL 9.5.1, compiled by Visual C++ build 1800, 64-bit'

Answer 1

If you add all ColumnsNames (exclude the 'id' Column) after the Tablename, there will be Insert the serial automaticly like:

INSERT INTO animals(nm,typ,tvi,tvf) select nm,typ,tvi,tvf from json_po..... 

You can also add a DEFAULT Value in your Column, to set a Default Value if the column is not in the Insert Column-list.

Answer 2

If you want to add more than one record :

insert into animals(nm,typ,tvi,tvf) 
select nm,typ,tvi,tvf 
from json_populate_recordset(null::animals, 
'
[
 {"nm":"mouse","typ":"m","tvi":4,"tvf":12.450},
 {"nm":"dog","typ":"x","tvi":10,"tvf":13.450}
]
');

Answer 3

This is kind of related. I found it useful when all the columns have defaults.

drop schema if exists Working cascade;
create schema Working;
create table if not exists Working.postgresDateTesting
(
 ts1 timestamptz not null default now(),
 ts2 timestamptz not null default now(),
 ts3 timestamptz not null default now()
);
insert into Working.postgresDateTesting select;
select * from Working.postgresDateTesting;