Answer a 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

Answers

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