主页 / dba / 问题 / 134399
Accepted
Jay Cummins
Asked: 2016-04-06 04:58:32 +0800 CST

如何从 PostgreSQL 中的选择查询中将值插入到具有默认值的表中？

  • 772

是否可以从语句中将INSERT值放入 PostgreSQL 表并为空列使用值？SELECTDEFAULT

就我而言，该SELECT语句是从 JSON 中选择的。

在下面的尝试中，我通过显式检索序列而成功，但我希望有一种方法可以使用 DEFAULT 值插入。或者选择默认值而无需显式调用默认函数。

-- 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 个回答

  1. Best Answer

    如果在 Tablename 之后添加所有 ColumnsNames（不包括 'id' 列），则会自动插入序列号，如：

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

    如果列不在插入列列表中，您还可以在列中添加默认值以设置默认值。

    • 6

  2. 如果要添加多条记录：

    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. 这有点关系。当所有列都有默认值时，我发现它很有用。

    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

相关问题