PostgreSQL: more than one row returned by a subquery used as an expression

Question

I have a main.comments table where I store users comments. I'm trying to add a comment to the database and get some data as a return. Here's my query

INSERT INTO main.comments (text, post_id, user_id)
VALUES('sample', 11, 1)
RETURNING 
comment_id,
text,
post_id,
(SELECT username FROM main.users WHERE main.users.user_id = user_id) AS username,
created_at,
updated_at

So I was expecting to add a comment and get the data I wanted but that wasn't the case, instead I got this error

ERROR: more than one row returned by a subquery used as an expression SQL state: 21000

I thought I had a problem with the subquery so I used it individually and got only one row in return. So I used LIMIT 1 within the subquery and I got the result I was expecting but that doesn't make sense in my query. Can someone please explain this behavior? And also my main.users table doesn't contain any user_id duplicate since I'm using the SERIAL type.

PostgreSQL 12.4

The `WHERE` condition in the subquery will be `TRUE` for all rows where `user_id IS NOT NULL`. — Laurenz Albe, Aug 21 '20 at 10:34
@LaurenzAlbe Thanks for you response, is there any solutions to that or should i just stick with workarounds like LIMIT? — aingthed, Aug 21 '20 at 10:36
@aingthed Limit Workaround will not work. it will give wrong name. — Akhilesh Mishra, Aug 21 '20 at 10:57

score 1 · Accepted Answer · answered Aug 21 '20 at 10:45

Real Culprit is this line in your code

(SELECT username FROM main.users WHERE main.users.user_id = user_id)

Try it like This:

INSERT INTO comments (text, post_id, user_id)
VALUES('sample', 11, 1)
RETURNING 
comment_id,
text,
post_id,
(SELECT username FROM users t1 WHERE t1.user_id = comments.user_id) AS username,
created_at,
updated_at

DEMO:

I have removed the schema name for clarity

score 1 · Answer 2 · answered Aug 21 '20 at 11:39

The problem is that the user_id in your subquery is not referring to the newly inserted row in main.comments, but to main.users, so the condition becomes TRUE and all rows from users are returned.

I'd use a CTE it like this:

WITH ins AS (
   INSERT INTO main.comments (text, post_id, user_id)
   VALUES('sample', 11, 1)
   RETURNING 
      comment_id,
      text,
      post_id,
      user_id,
      created_at,
      updated_at
)
SELECT ins.comment_id,
       ins.text,
       ins.post_id,
       u.username,
       ins.created_at,
       ins.updated_at
FROM ins
   JOIN main.users AS u ON ins.user_id = u.user_id;

score 0 · Answer 3 · answered Aug 25 '20 at 10:32

you could first enter blank values

       INSERT INTO main.comments (text, post_id, user_id)
    VALUES('sample', 11, 1)
    RETURNING 
    comment_id,
    text,
    post_id,
    NULL AS username,
    created_at,
updated_at;

then update your table

UPDATE main.comment 
SET username = (SELECT username )
FROM main.users 
WHERE main.users.user_id = main.comment.user_id;

didn't test but should work

PostgreSQL: more than one row returned by a subquery used as an expression

3 Answers3