PostgreSQL : plusieurs lignes renvoyées par une sous-requête utilisée comme expression

Question

J'ai une main.commentstable où je stocke les commentaires des utilisateurs. J'essaie d'ajouter un commentaire à la base de données et d'obtenir des données en retour. Voici ma requête

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

Je m'attendais donc à ajouter un commentaire et à obtenir les données que je voulais mais ce n'était pas le cas, à la place j'ai eu cette erreur

ERREUR : plusieurs lignes renvoyées par une sous-requête utilisée comme expression État SQL : 21000

Je pensais avoir un problème avec la sous-requête, je l'ai donc utilisée individuellement et n'ai obtenu qu'une seule ligne en retour. J'ai donc utilisé LIMIT 1dans la sous-requête et j'ai obtenu le résultat que j'attendais mais cela n'a pas de sens dans ma requête. Quelqu'un peut-il s'il vous plaît expliquer ce comportement? Et aussi ma main.userstable ne contient aucun user_iddoublon puisque j'utilise le SERIALtype.

• PostgreSQL 12.4

Answer 1

Le vrai coupable est cette ligne dans votre code

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

Essayez-le comme ceci :

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

DÉMO :

I have removed the schema name for clarity

Answer 2

Le problème est que le user_iddans votre sous-requête ne fait pas référence à la ligne nouvellement insérée dans main.comments, mais à main.users, donc la condition devient TRUEet toutes les lignes de userssont renvoyées.

J'utiliserais un CTE comme ceci:

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;

Answer 3

vous pouvez d'abord entrer des valeurs vides

       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;

puis mettez à jour votre table

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

pas testé mais devrait fonctionner