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

insertjsonpostgresqlpostgresql-9.5

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'

Best Answer

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.

Related Solutions

Postgresql – How to insert values into a table from a select query in PostgreSQL

Column order does matter so if (and only if) the column orders match you can for example:

insert into items_ver
select * from items where item_id=2;

Or if they don't match you could for example:

insert into items_ver(item_id, item_group, name)
select * from items where item_id=2;

but relying on column order is a bug waiting to happen (it can change, as can the number of columns) - it also makes your SQL harder to read

There is no good 'shortcut' - you should explicitly list columns for both the table you are inserting into and the query you are using for the source data, eg:

insert into items_ver (item_id, name, item_group)
select item_id, name, item_group from items where item_id=2;

dbfiddle here

Postgresql – Altering a parent table in Postgresql 8.4 breaks child table defaults

Your problem is that when you add a new column to the_person, its child, the_person_two will have this field appended at the end of columns list (4th position), so after has_default column. See:

db=> \d temp_person
  Column   |       Type        |                            Modifiers                            
-----------+-------------------+-----------------------------------------------------------------
 person_id | integer           | not null default nextval('temp_person_person_id_seq'::regclass)
 name      | character varying | 
 foo       | text              | 

db=> \d temp_person_two 
   Column    |         Type         |                            Modifiers                            
-------------+----------------------+-----------------------------------------------------------------
 person_id   | integer              | not null default nextval('temp_person_person_id_seq'::regclass)
 name        | character varying    | 
 has_default | character varying(4) | not null default 'en'::character varying
 foo         | text                 |

So, when you execute this:

INSERT INTO temp_person_two VALUES ( NEW.* );

PostgreSQL will actually understand that you want to insert on the first three columns of temp_person_two (as NEW.* will expand to three values), generating something similar to this:

INSERT INTO temp_person_two(person_id,name,has_default)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

So, temp_person_two.has_default will get the value of NEW.foo, which is NULL in your case.

The solution is to simply expand the column names:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

or, you could also use this:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.* );

But this is weak, as any changes on column positions may break your statements, so I'd recommend the first one.

EDIT:

So the conclusion and the lesson learned here is:

Always explicitly type the names of the columns and the values when issuing an INSERT command, in fact, when issuing any SQL command at all... =D

This will save you a lot of time solving problems like that in future.

Best Answer

Related Solutions

Postgresql – How to insert values into a table from a select query in PostgreSQL

Postgresql – Altering a parent table in Postgresql 8.4 breaks child table defaults

Related Question