PostgreSQL: more than one row returned by a subquery used as an expression
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
답변
실제 범인은 코드에서이 줄입니다.
(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
문제는 있다는 것입니다 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;
먼저 빈 값을 입력 할 수 있습니다.
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;
테스트하지 않았지만 작동해야합니다.