Início / dba / Perguntas / 242757
Accepted
gertvdijk
Asked: 2019-07-13 05:53:32 +0800 CST

Como posso obter a última linha inserida com INSERT ... SELECT no PostgreSQL?

  • 772

Estou tentando copiar dados em lote em uma ordem específica de uma tabela para outra no PostgreSQL 12-beta2. A tabela não está usando sequências, mas contém uma chave primária exclusiva composta ( user_id, object_id).

Para determinar por onde começar o próximo lote, gostaria de começar da última linha inserida ( WHERE user_id >= last_user_id AND object_id > last_object_id).

Começando com isso:

INSERT INTO dest_table
SELECT (user_id, object_id, object_type, colN, ...) 
FROM source_table 
ORDER BY user_id, colN, object_id  -- this is indexed
LIMIT 1000  -- batch size
RETURNING user_id, object_id;

... retorna uma tabela de 1.000 tuplas. Eu gostaria de obter a última tupla inserida dele.

Eu tentei fazer um SELECT em torno dele, assim:

SELECT user_id, object_id FROM (
    INSERT INTO dest_table
    SELECT (user_id, object_id, object_type, colN, ...) 
    FROM source_table 
    ORDER BY user_id, colN, object_id  -- this is indexed
    LIMIT 1000  -- batch size
    RETURNING user_id, object_id
)
ORDER BY user_id DESC, colN DESC, object_id DESC
LIMIT 1
RETURNING user_id, object_id;

Mas isso retorna um erro de sintaxe:

ERROR:  syntax error at or near "INTO"
LINE 2: INSERT INTO dest_table
               ^

Eu também tentei RETURNING ... INTO variable conforme descrito aqui , mas isso também falha:

ERROR:  syntax error at or near "INTO"
LINE 23: RETURNING user_id, object_id INTO my_variable;
                                      ^

Preciso criar uma função para isso (por exemplo, plpgsql) ou estou perdendo algo óbvio no SQL simples que me permite fazer isso? Isso seria altamente favorável.

postgresql insert

2 respostas

  1. Best Answer

    Parece que você precisa apenas do último inserido (user_id,object_id) como um par. O primeiro INSERT pode ser:

    WITH inserted_rows as (
INSERT INTO dest_table
SELECT user_id, object_id, object_type, colN, ...
FROM source_table 
ORDER BY user_id, colN, object_id  -- this is indexed
LIMIT 1000  -- batch size
RETURNING user_id, object_id
)
SELECT row(user_id,object_id) FROM inserted_rows
 ORDER BY 1 DESC LIMIT 1;

    Este formulário usa uma linha para se referir ao (user_id,object_id)casal por conveniência. Os elementos de linha são comparados da esquerda para a direita conforme necessário no seu caso para usar esses dois valores como um limite. Consulte Comparação do Construtor de Linha na documentação.

    Com esta solução, os INSERTs subsequentes injetariam o valor retornado pela última inserção anterior. Isso evita procurar a última linha inserida em dest_table.

    WITH inserted_rows as (
INSERT INTO dest_table
SELECT user_id, object_id, object_type, colN, ...
FROM source_table
  WHERE row(user_id,object_id) > row(...inject the result of the previous query here...)
ORDER BY user_id, colN, object_id
LIMIT 1000
RETURNING user_id, object_id
)
SELECT row(user_id,object_id) FROM inserted_rows
ORDER BY 1 DESC LIMIT 1;

    Preciso criar uma função para isso (por exemplo, plpgsql) ou estou perdendo algo óbvio no SQL simples que me permite fazer isso? Isso seria altamente favorável.

    No SQL simples, você faria um único INSERT em uma etapa sem nenhum lote. Como você deseja dividi-lo em lotes, deve haver algum loop direcionando esses INSERTs com uma condição de parada, e essa lógica procedural vai além do SQL puro.

    Se você não quiser reinjetar o limite por meio de uma variável, ela pode ser mantida em uma tabela dedicada de uma única linha.

    • 1

  2. Se ninguém mais estiver escrevendo dest_table, exceto você, obtenha o último ID inserido em uma subconsulta em todos os lotes subsequentes, por exemplo:

    INSERT INTO dest_table
SELECT (user_id, object_id, object_type, colN, ...) 
FROM source_table
WHERE
    user_id >= (
        SELECT user_id
        FROM dest_table
        ORDER BY user_id DESC, colN DESC, object_id DESC
        LIMIT 1)
    )
    AND object_id > (
        SELECT event_id
        FROM dest_table
        ORDER BY user_id DESC, colN DESC, object_id DESC
        LIMIT 1)
    )
ORDER BY user_id, colN, object_id
LIMIT 1000;
    • 0

relate perguntas