mysql - postgresql 中的 "Subquery must return only one column"错误

Question

当我运行此查询时出现错误“错误:子查询必须只返回一列”:

INSERT INTO details (id, object_id, detail)
  (
    SELECT
      CASE
      WHEN (SELECT * FROM details WHERE NOT EXISTS(SELECT 1 FROM main_base WHERE main_base.id = details.id))
        THEN
          concat(SUBSTRING(main_base.id, '(\d+.\d+.)'), n.counted :: TEXT, 'A')
        ELSE
           concat( SUBSTRING (main_base.id, '(\d+.\d+.)'), n.counted :: TEXT)
        END AS id,
          main_base.object_id,
          main_base.details
    FROM main_base
    CROSS JOIN LATERAL
    generate_series(1, COALESCE ((string_to_array(main_base.id, '-')) [2] :: INT, 1)) AS n (counted)
    WHERE main_base.id LIKE '%-%' AND NOT main_base.details ~ '^\.\d+|\(\.\d+\)'
  );

我不知道哪里出了问题。我读过一些主题，有人遇到了同样的问题，但仍然不知道如何解决。

Answer 1

我认为问题在于:

SELECT * FROM details WHERE NOT EXISTS(SELECT 1 FROM main_base WHERE main_base.id = details.id)

可以返回多行，因此会导致 WHEN 语句出现问题。它可以返回多行，因为每次满足条件时子查询都会返回 1。

如果你想在这个集合中存在一些记录时触发 case 语句，你可以使用:

(SELECT COUNT(*) FROM details WHERE NOT EXISTS(SELECT 1 FROM main_base WHERE main_base.id = details.id)) > 1