Início / dba / Perguntas / 317108
Accepted
s.k
Asked: 2022-09-20 10:49:02 +0800 CST

Inserindo dados fictícios em uma tabela vazia com um campo inteiro de chave primária GERADO SEMPRE COMO IDENTIDADE

  • 772

Estou tentando gerar tabelas fictícias com dados fictícios para testar o desempenho com algumas tarefas SQL.

Com base neste tópico antigo: Existe uma maneira de inserir várias linhas em uma tabela com valores padrão para todas as colunas?

Percebi que com um IDENTITYtipo, não é mais possível preencher uma tabela com dados fictícios usando a generate_series(1, N)solução sugerida.

Então, como eu poderia inserir 1.000 dados fictícios, por exemplo, na tabela a seguir (PG 14), aproveitando o valor padrão para o campo namesem substituir os valores do sistema para o campo id:

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

Tentar o seguinte falha:

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

Como solução alternativa, agora estou criando a tabela com um campo extra itão simples INTEGERpara alimentar este em vez do ide soltá-lo após a inserção dos dados, mas não é 100% limpo para mim.

Eu estava esperando por algo simples e claro como isso, por exemplo:

INSERT INTO foo DEFAULT VALUES (1000);

mas esta é obviamente uma sintaxe inválida.

postgresql default-value

2 respostas

  1. Outro método que pode ser usado mesmo quando todas as colunas possuem expressões padrão é fornecer uma coluna e usar a OVERRIDING USER VALUEopção no INSERTpara ignorar esses valores fornecidos.

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

    Testado em dbfiddle.uk (1)

    Mais um método que usa MERGE- disponível apenas na versão 15 do Postgres (a ser lançada no próximo mês). Obrigado a Paul White que localizou as perguntas semelhantes para SQL Server e Martin Smith e AndriyM pelas respostas:

    Reescrito aqui com as mudanças necessárias para o Postgres e testado em 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
;

    (Nota de desejo atualizada)
    Seria bom se pudéssemos fornecer uma tabela de 0 colunas como entrada, mas isso ainda não foi implementado - o selectfunciona por conta própria, mas não o insert into foo ():

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

    Mas isso funciona desde a versão 9.4. O truque é usar insert into foo- sem o ()- que aceita uma tabela de entrada com número arbitrário de colunas (de zero até o número de colunas da tabela):

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

    Testando todos os três métodos em dbfiddle.uk (3) :

    select version();
    versão
    PostgreSQL 15beta3 em x86_64-pc-linux-gnu, compilado por gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64 bits 
    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
    Eu iria nome
    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
    Eu iria nome
    1 08a153d31bafbef82d67c63b959ede87
    2 5295cbb135445f954616fbf18031097a
    3 5997bfd8e71c09bf244104797369c573
    4 73c644ac045b8dbdb4b17b2fb624538a
    5 fb0ddc243709b3e3629838268ba5407f
    6 af517dc84cb68ac0a5bd8c0cd2ef2ba5
    7 fe33bf8a25bce0dbae0e8b80ff23af97
    8 e35566ed57b7e0fe70878a03319fbb52 
    SELECT 8

    alternativamente

    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
    Eu iria nome
    1 08a153d31bafbef82d67c63b959ede87
    2 5295cbb135445f954616fbf18031097a
    3 5997bfd8e71c09bf244104797369c573
    4 73c644ac045b8dbdb4b17b2fb624538a
    5 fb0ddc243709b3e3629838268ba5407f
    6 af517dc84cb68ac0a5bd8c0cd2ef2ba5
    7 fe33bf8a25bce0dbae0e8b80ff23af97
    8 e35566ed57b7e0fe70878a03319fbb52
    9 6c71ffb4347d6593358cda63e802ae5e
    10 a07ab009a6838533df51b02ca24d26fe 
    SELECT 10

    violino

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

relate perguntas