Delete statement not executed in postgresql

Question

I am creating a transaction where I want to update a user in one table and delete some data in another that belongs to that user. But only the first query is executed, not the second one. In the delete statement in the second query code is a comma-separated std::string.

pqxx::connection c(connectionString);

try {
    pqxx::work w(c);
    pqxx::result r;

    c.prepare("user", "update user set started = null, finished = null, task = $1 where id = $2");
    r = w.prepared("user")(task)(email).exec();

    c.prepare("belongings", "delete from belongings where id in " \
        "(select id from info where code in ($1) and id = $2)");
    r = w.prepared("belongings")(code)(id).exec();

    w.commit();
}

I read this SO-thread that explain how to run multiple queries before commit(). So I must be making a mistake in the second delete statement but can't find the reason.

What's the type of belongings.code? Also show assignment to code variable. — klin
– klin, Commented Nov 17, 2017 at 11:40
This doesn't answer your question, but you could wrap all of that in a function and execute the function. This would have the added benefit that if the first statement failed it wouldn't execute the second (although a transaction could accomplish the same thing). — Hambone
– Hambone, Commented Nov 17, 2017 at 11:42
@klin belongings.code is varchar in the table. And code is a std::string like 'abc','def','ghi'. I first thought there was a limit to the IN clause but that appears not to be the case. — kometen
– kometen, Commented Nov 17, 2017 at 11:50

klin · Accepted Answer · 2017-11-17 11:53:36Z

1

The code parameter is interpreted as a single literal. You can try to use the alternative syntax of any(array expression), e.g.:

code = "{abc,def}";     // instead of code = "'abc','def'"
...

c.prepare("belongings", "delete from belongings where id in " \
    "(select id from info where code = any ($1) and id = $2)");
r = w.prepared("belongings")(code)(id).exec();

answered Nov 17, 2017 at 11:53

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kometen Over a year ago

Works. That was very helpful!

Collectives™ on Stack Overflow

Delete statement not executed in postgresql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related