7

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'
4

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.

1

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}
]
');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.