返回在公用表表达式中使用INSERT从子查询数据(Return data from subselec

我试图移动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:

你需要让你的表名和别名权利。另外，这两个表之间的连接是列image （ data在新的表file_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;

就像我，你引用的旧的答案解释说， image必须是唯一task_log这个工作：

插入数据，并设置与Postgres的外键

我添加了一个技术如何消除歧义的参考答案不唯一的值。不知道如果你想在重复的图像file_data ，虽然。

在RETURNING一个子句INSERT你只能从插入行中的列。手动：

可选的RETURNING子句使INSERT来计算，并根据各行实际插入（...）的返回值（一个或多个）但是， 使用表中的列的表达式是允许的 。

大胆重点煤矿。

折叠重复的来源值

如果你想在目标表不同的条目INSERT （ task_log ），你在这种情况下，需要的是DISTINCT在初始SELECT ：

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 。小心更新和删除...

Answer 2:

我需要复制的重复 ，所以我最终加入一个临时列使用的数据行的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;

由于这产生一些死数据我跑了vacuum full事后清理。

但是请让我从@ErwinBrandstetter重复的警告：