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

Aug 21 2020

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

답변

1 AkhileshMishra Aug 21 2020 at 17:45

실제 범인은 코드에서이 줄입니다.

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

다음과 같이 시도하십시오.

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

데모:

I have removed the schema name for clarity
1 LaurenzAlbe Aug 21 2020 at 18:39

문제는 있다는 것입니다 user_id귀하의 하위 쿼리입니다 하지 에 새로 삽입 된 행을 참조 main.comments만에 main.users조건이 될 수 있도록, TRUE그리고 모든 행이 users반환됩니다.

다음과 같이 CTE를 사용합니다.

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;
FranckT Aug 25 2020 at 17:32

먼저 빈 값을 입력 할 수 있습니다.

       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;

그런 다음 테이블을 업데이트하십시오.

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

테스트하지 않았지만 작동해야합니다.