CODEWITHSUNDEEP
sqlmongodbpostgresql
blu
blu

Reputation: 13175

How can I port this mongo query to postgres?

I have an atomic update that finds a record by the lowest value in a property, increments that value, and returns the result in one query.

If I were in SQL Server I would use DECLARE and SET inside of a transaction and do a multiple queries, but this postgres DO syntax is pancake to me.

I see DO $$DECLARE r record; which is a good start, but can I SET or by other means assign a single record to that variable?

In Mongo:

this.findOneAndUpdate(
    { active: true },
    { $inc: { userCount: 1 } },
    { sort: { userCount: 1 } }, // grab the lowest count for update
    cb
);

In SQL Server I would do something like this: (man it's been a while...)

declare @id int
begin
    set @id = select top 1 id 
        from foos 
        where active = 1 
        order by userCount desc

    update foos 
        set userCount = userCount + 1 
        where id = @id

    select foos 
        where id = @id
end

Upvotes: 1

Views: 499

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78513

No need for a do statement... Use a normal update statement with a returning clause, e.g.:

update foos 
set userCount = userCount + 1 
where id = (
      select id 
      from foos 
      where active = 1 
      order by userCount desc
      limit 1
    )
returning *

Upvotes: 3

Related Questions