从通用表表达式中INSERT中使用的子选择返回数据

Question

我尝试将< code>bytea数据从一个表移动到另一个表，在一个查询中更新引用。

因此，我想从用于插入的查询中返回不用于插入的数据。

INSERT INTO file_data (data)
  select image from task_log where image is not null
RETURNING id as file_data_id, task_log.id as task_log_id

但我得到了一个查询错误：

[42P01] ERROR: missing FROM-clause entry for table "task_log"

我想做这样的事情：

WITH inserted AS (
  INSERT INTO file_data (data)
    SELECT image FROM task_log WHERE image IS NOT NULL
  RETURNING id AS file_data_id, task_log.id AS task_log_id
)
UPDATE task_log
SET    task_log.attachment_id = inserted.file_data_id,
       task_log.attachment_type = 'INLINE_IMAGE'
FROM   inserted
WHERE  inserted.task_log_id = task_log.id;

但我无法获取用于插入的所有数据，我无法从子选择返回id。

我受到这个关于如何使用通用表表达式做到这一点的答案的启发，但我找不到一种方法来使它工作。

Answer 1

我需要复制副本，所以我最后添加了一个temp列作为所用数据行的id。

alter table file_data add column task_log_id bigint;
-- insert & update data
alter table file_data drop column task_log_id;

完整的移动脚本是

-- A new table for any file data
CREATE TABLE file_data (
  id         BIGSERIAL PRIMARY KEY,
  data  bytea
);

-- Move data from task_log to bytes

-- Create new columns to reference file_data
alter table task_log add column attachment_type VARCHAR(50);
alter table task_log add column attachment_id bigint REFERENCES file_data;

-- add a temp column for the task_id used for the insert
alter table file_data add column task_log_id bigint;

-- insert data into file_data and set references
with inserted as (
  INSERT INTO file_data (data, task_log_id)
    select image, id from task_log where image is not null
  RETURNING id, task_log_id
)
UPDATE task_log
SET   attachment_id = inserted.id,
      attachment_type = 'INLINE_IMAGE'
FROM  inserted
where inserted.task_log_id = task_log.id;
-- delete the temp column
alter table file_data drop column task_log_id;
-- delete task_log images
alter table task_log drop column image;

由于这会产生一些死数据，我事后运行了一个真空机进行清理。

但请让我重复@ErwinBrandstetter的警告:

性能比我在链接答案中提出的使用序列号的方法差得多。添加

Answer 2

您需要正确设置表名和别名。另外，两个表之间的连接是列image（新表中的datafile_data）：

WITH inserted AS (
  INSERT INTO file_data (data)
  SELECT image
  FROM   task_log
  WHERE  image IS NOT NULL
  RETURNING id, data  -- can only reference target row
)
UPDATE task_log t
SET    attachment_id = i.id
     , attachment_type = 'INLINE_IMAGE'
FROM   inserted i
WHERE  t.image = i.data;

就像我在你提到的旧答案中解释的那样，< code>image在< code>task_log中必须是唯一的，这样才能起作用:

< li >使用Postgres插入数据并设置外键

我添加了一种技术，如何消除引用答案中非唯一值的歧义。但是，不确定您是否希望file_data中出现重复的图像。

在INSERT的RETURNING子句中，只能引用插入行中的列。手册：

可选的< code > return 子句使< code>INSERT根据实际插入的每一行计算并返回值(...)但是，任何使用表列的表达式都是允许的。

大胆强调我的。

如果要在 INSERT（task_log）的目标表中使用不同的条目，则在这种情况下，您只需要在初始 SELECT 中显示 DISTINCT：

WITH inserted AS (
  INSERT INTO file_data (data)
  SELECT DISTINCT image  -- fold duplicates
  FROM   task_log
  WHERE  image IS NOT NULL
  RETURNING id, data  -- can only reference target row
)
UPDATE task_log t
SET    attachment_id = i.id
     , attachment_type = 'INLINE_IMAGE'
FROM   inserted i
WHERE  t.image = i.data;

生成的 file_data.id 在task_log中多次使用。请注意，task_log中的多行现在指向file_data中的同一图像。小心更新和删除...