Linha duplicada com chave primária no PostgreSQL

Simples comhstore

Se você tiver o módulo adicional hstoreinstalado ( instruções no link abaixo ), existe uma maneira surpreendentemente simples de substituir o(s) valor(es) do(s) campo(s) individual(is) sem saber nada sobre outras colunas:

Exemplo básico: duplique a linha com, id = 2mas substitua 2por 3:

INSERT INTO people
SELECT (p #= hstore('id', '3')).* FROM people p WHERE id = 2;

Detalhes:

• Como definir o valor do campo variável composto usando SQL dinâmico
• Atribuir a NEW por chave em um gatilho Postgres

Supondo (já que não está definido na pergunta) quepeople.idé umaserialcoluna com uma sequência anexada, você desejará o próximo valor da sequência. Podemos determinar o nome da sequência compg_get_serial_sequence(). Detalhes:

• PostgreSQL SELECT chave primária como "serial" ou "bigserial"

Ou você pode apenas codificar o nome da sequência se ele não for alterado.
Teríamos esta consulta:

INSERT INTO people
SELECT (p #= hstore('id', nextval(pg_get_serial_sequence('people', 'id'))::text)).*
FROM people p WHERE id = 2;

O que funciona , mas sofre de uma fraqueza no planejador de consulta do Postgres: a expressão é avaliada separadamente para cada coluna na linha, desperdiçando números de sequência e desempenho. Para evitar isso, mova a expressão para uma subqery e decomponha a linha apenas uma vez :

INSERT INTO people
SELECT (p1).*
FROM  (
   SELECT p #= hstore('id', nextval(pg_get_serial_sequence('people', 'id'))::text) AS p1
   FROM   people p WHERE id = 2
   ) sub;

Provavelmente o mais rápido para uma única (ou poucas) linha(s) de uma só vez.

json/jsonb

Se você não hstoreinstalou e não pode instalar módulos adicionais, pode fazer um truque semelhante com json_populate_record()ou jsonb_populate_record(), mas esse recurso não está documentado e pode não ser confiável . Atualização: O recurso também está documentado desde o Postgres 13. Veja:

• Como definir o valor do campo variável composto usando SQL dinâmico

Tabela temporária transitória

Outra solução simples seria usar um transiente temporário como este:

BEGIN;
CREATE TEMP TABLE people_tmp ON COMMIT DROP AS
SELECT * FROM people WHERE id = 2;
UPDATE people_tmp SET id = nextval(pg_get_serial_sequence('people', 'id'));
INSERT INTO people TABLE people_tmp;
COMMIT;

Adicionei ON COMMIT DROPpara dropar a tabela automaticamente ao final da transação. Consequentemente, também envolvi a operação em uma transação própria. Nenhum dos dois é estritamente necessário.

Isso oferece uma ampla variedade de opções adicionais - você pode fazer qualquer coisa com a linha antes de inserir, mas será um pouco mais lento devido à sobrecarga de criar e descartar uma tabela temporária.

Esta solução funciona para uma única linha ou para qualquer número de linhas de uma só vez . Cada linha obtém um novo valor padrão da sequência automaticamente.

Usando a notação curta (padrão SQL)TABLE people .

SQL dinâmico

Para muitas linhas de uma só vez, o SQL dinâmico será mais rápido. Concatene as colunas da tabela do sistema pg_attributeou do esquema de informações e execute-as dinamicamente em uma DOinstrução ou escreva uma função para uso repetido:

CREATE OR REPLACE FUNCTION f_row_copy(_tbl regclass, _id int, OUT row_ct int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE (
      SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE id = $1',
                    _tbl, string_agg(quote_ident(attname), ', '))
      FROM   pg_attribute
      WHERE  attrelid = _tbl
      AND    NOT attisdropped  -- no dropped (dead) columns
      AND    attnum > 0        -- no system columns
      AND    attname <> 'id'   -- exclude id column
      )
   USING _id;

   GET DIAGNOSTICS row_ct = ROW_COUNT;  -- directly assign OUT parameter
END
$func$;

Ligar:

SELECT f_row_copy('people', 9);

Funciona para qualquer tabela com uma coluna inteira chamada id. Você também pode facilmente tornar o nome da coluna dinâmico ...

Talvez não seja sua primeira escolha desde que você queria stay away from stored procedures, mas, novamente, não é um "procedimento armazenado" de qualquer maneira ...

Relacionado:

• psql: SELECT * ... exceto uma coluna
• Listar todas as colunas de uma tabela especificada

Solução avançada

Uma serialcoluna é um caso especial. Se você quiser preencher mais ou todas as colunas com seus respectivos valores padrão, fica mais sofisticado. Considere esta resposta relacionada:

• Gere valores DEFAULT em um CTE UPSERT usando o PostgreSQL 9.3

Tente criar um triggeron insert:

CREATE TRIGGER name BEFORE INSERT

Nesta trigger você torna o ID NULL. Quando o gatilho terminar, a inserção é feita e o Postgres fornecerá um ID. Presumo que você tenha definido o ID como DEFAULT NEXTVAL('A_SEQUENCE'::REGCLASS).

SQL dinâmico Funciona muito bem, estou procurando por isso há alguns anos,

se você tiver mais de uma coluna excluída, tente simplesmente,

AND    attname <> 'id'   -- exclude id column
AND    attname <> 'second_col_name'   -- exclude second_col_name