Início / dba / Perguntas / 134399
Accepted
Jay Cummins
Asked: 2016-04-06 04:58:32 +0800 CST

Como inserir valores em uma tabela com valores padrão de uma consulta select no PostgreSQL?

  • 772

É possível INSERTinserir valores em uma tabela PostgreSQL a partir de uma SELECTinstrução e usar DEFAULTvalores para as colunas que são nulos?

No meu caso, a SELECTinstrução está selecionando de JSON.

Nas tentativas abaixo, obtive sucesso recuperando explicitamente a sequência, mas espero que haja uma maneira de INSERT usando os valores DEFAULT. Ou selecione valores DEFAULT sem precisar chamar explicitamente as funções padrão.

-- 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'
postgresql insert

3 respostas

  1. Best Answer

    Se você adicionar todos os ColumnsNames (excluir a coluna 'id') após o Tablename, haverá Inserir o serial automaticamente como:

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

    Você também pode adicionar um Valor PADRÃO em sua Coluna, para definir um Valor Padrão se a coluna não estiver na lista Inserir Coluna.

    • 6

  2. Se você deseja adicionar mais de um registro:

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

  3. Isso é meio relacionado. Achei útil quando todas as colunas têm padrões.

    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;
    • 0

relate perguntas