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

Answer 1

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

Answer 2

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;