"Subquery must return only one column" error in postgresql

Question

I have got an error "ERROR: subquery must return only one column " when I am runing this query:

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+\)'
  );

I have not clue what is wrong. I've read some topic that people had the same problem but still dont know how to fix it.

And that will probably raise "sub-query returned more than 1 row" error. — jarlh
– jarlh, Commented Feb 23, 2016 at 15:42
Maybe start by describing what you were trying to achieve with this query? Because you certainly overcomplicated it. — Jakub Kania
– Jakub Kania, Commented Feb 23, 2016 at 15:51

JCollerton · Accepted Answer · 2016-02-23 15:47:48Z

2

I think the problem is that:

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

Can return more than one row, so causes problems in the WHEN statement. It can return more than one row, as the subquery will return 1 every time the condition is met.

If you want to trigger the case statement based on when there exists some records in this set, could you use:

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

answered Feb 23, 2016 at 15:47

JCollerton

3,3272 gold badges22 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

"Subquery must return only one column" error in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related