主页 / dba / 问题 / 317108
Accepted
s.k
Asked: 2022-09-20 10:49:02 +0800 CST

将虚拟数据插入到具有主键整数字段 GENERATED ALWAYS AS IDENTITY 的空表中

  • 772

我正在尝试使用虚拟数据生成虚拟表，以测试某些 SQL 任务的性能。

基于这个旧线程：有没有办法将多行插入到所有列的默认值的表中？

我注意到，对于一个IDENTITY类型，使用建议的generate_series(1, N)解决方案不再可能用虚拟数据填充表。

那么，我如何将 1'000 个虚拟数据插入到例如下表 (PG 14) 中，利用该字段的默认值name而不覆盖该字段的系统值id：

CREATE TABLE foo (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT DEFAULT md5(random()::text)
);

尝试以下失败：

INSERT INTO foo (id) 
  SELECT generate_series(1, 1000);

-- which results in:
ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
SQL state: 428C9

作为一种解决方法，我现在正在创建一个带有额外字段的表，以便i简单INTEGER地提供这个字段，而不是id在插入数据后将其删除，但它对我来说并不是 100% 干净的。

我希望有一些简单明了的东西，例如：

INSERT INTO foo DEFAULT VALUES (1000);

但这显然是一种无效的语法。

postgresql default-value

2 个回答

  1. 即使所有列都具有默认表达式也可以使用的另一种方法是提供一个列并使用OVERRIDING USER VALUE选项中的选项INSERT以忽略那些提供的值。

    insert into foo (id)
  OVERRIDING USER VALUE
select null
from generate_series(1, 10) as gs(i) ;

    在dbfiddle.uk (1)中测试

    使用的另一种方法MERGE- 仅在 Postgres 版本 15（下个月发布）中可用。感谢 Paul White 为 SQL Server 和 Martin Smith 和 AndriyM 找到了类似问题的答案：

    在这里重写了 Postgres 的必要更改并在dbfiddle.uk (2)中进行了测试：

    MERGE INTO
  foo AS tgt
USING
  generate_series(1, 10) AS src  -- << use your source row set here
ON
  FALSE
WHEN NOT MATCHED THEN
  INSERT DEFAULT VALUES
;

    （希望说明已更新）
    如果我们可以提供一个 0 列表作为输入，但这还没有实现，这将是很好的——select它可以自己工作，但不能insert into foo ()：

    insert into foo ()
select
from generate_series(1, 10) as gs(i) ;

    但这从 9.4 版开始有效。诀窍是使用insert into foo- 没有()- 它接受具有任意列数的输入表（从零到表的列数）：

    insert into foo
select
from generate_series(1, 10) as gs(i) ;

    在dbfiddle.uk (3)中测试所有三种方法：

    select version();
    版本
    x86_64-pc-linux-gnu 上的 PostgreSQL 15beta3，由 gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10) 编译，64 位 
    SELECT 1

    CREATE TABLE foo (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT DEFAULT md5(random()::text)
);

    CREATE TABLE

    insert into foo (id)
  OVERRIDING USER VALUE
select null
from generate_series(1, 3) as gs(i) ;   -- add 3 rows

select * from foo ;

    INSERT 0 3
    ID 姓名
    1 08a153d31bafbef82d67c63b959ede87
    2 5295cbb135445f954616fbf18031097a
    3 5997bfd8e71c09bf244104797369c573 
    SELECT 3

    MERGE INTO
  foo AS tgt
USING
  generate_series(1, 5) AS src   -- add 5 more rows
ON
  FALSE
WHEN NOT MATCHED THEN
  INSERT DEFAULT VALUES ;

select * from foo ;

    MERGE 5
    ID 姓名
    1 08a153d31bafbef82d67c63b959ede87
    2 5295cbb135445f954616fbf18031097a
    3 5997bfd8e71c09bf244104797369c573
    4 73c644ac045b8dbdb4b17b2fb624538a
    5 fb0ddc243709b3e3629838268ba5407f
    6 af517dc84cb68ac0a5bd8c0cd2ef2ba5
    7 fe33bf8a25bce0dbae0e8b80ff23af97
    8 e35566ed57b7e0fe70878a03319fbb52 
    SELECT 8

    或者

    MERGE INTO
  foo AS tgt
USING
  generate_series(1, 5) AS src   -- add 5 more rows
ON
  FALSE
WHEN NOT MATCHED THEN   
  INSERT (id) VALUES (DEFAULT);

    insert into foo
select
from generate_series(1, 2) as gs(i) ;

select * from foo ;

    INSERT 0 2
    ID 姓名
    1 08a153d31bafbef82d67c63b959ede87
    2 5295cbb135445f954616fbf18031097a
    3 5997bfd8e71c09bf244104797369c573
    4 73c644ac045b8dbdb4b17b2fb624538a
    5 fb0ddc243709b3e3629838268ba5407f
    6 af517dc84cb68ac0a5bd8c0cd2ef2ba5
    7 fe33bf8a25bce0dbae0e8b80ff23af97
    8 e35566ed57b7e0fe70878a03319fbb52
    9 6c71ffb4347d6593358cda63e802ae5e
    10 a07ab009a6838533df51b02ca24d26fe 
    SELECT 10

    小提琴

    • 5
  2. Best Answer
    INSERT INTO foo (name) 
  SELECT md5(random()::text) FROM generate_series(1, 1000);
    • 3

相关问题