On Tuesday, November 28, 2017 at 1:21:28 PM UTC-8, Jason Landry wrote:
>
> I'd like to get the counts of the number of records inserted and updated
> when I do an insert_conflict with Sequel.
>
> Consider the following sql code (from
> https://stackoverflow.com/questions/38851217/postgres-9-5-upsert-to-return-the-count-of-updated-and-inserted-rows)
>
> which accomplishes what I want:
>
> WITH t AS (
> INSERT INTO test3
> VALUES('www9','rrr'), ('www7','rrr2')
> ON CONFLICT (r1) DO UPDATE SET r2 = 'QQQQ' RETURNING xmax
> )
> SELECT COUNT(*) AS all_rows,
> SUM(CASE WHEN xmax = 0 THEN 1 ELSE 0 END) AS ins,
> SUM(CASE WHEN xmax::text::int > 0 THEN 1 ELSE 0 END) AS upd
> FROM t;
>
> all_rows | ins | upd
> ----------+-----+-----
> 2 | 1 | 1
>
> I've tried to emulate it lilke this:
>
> my_dataset = <some code here>
>
> DB[:results]
> .with(:results, my_dataset.insert_conflict.insert(...))
> .select_map(["count(*) as total".lit,
> "sum(case when xmax = 0 then 1 else 0 end) as inserts".lit,
> "sum(case when xmax::text::int >0 then 1 else 0 end) as
> updates".lit]
> )
>
> Looking at the output, it appears that the insert() happens before the
> rest of the query is built.
>
Expected, as Dataset#insert is an action method that sends the INSERT
statement to the database.
> Is there another way to accomplish this?
>
ds = DB[:t].
with(:t,
DB[:test3].
insert_conflict(:target=>:r1, :update=>{:r2=>'QQQQ'}).
returning(:xmax).
with_sql(:insert_sql, DB.values([%w'www9 rrrr', %w'www7 rrr2']))).
select do [
count.function.*.as(:all_rows),
sum(Sequel.case({0=>1}, 0, :xmax)).as(:ins),
sum(Sequel.case({(xmax.cast(:text).cast(:int) > 0)=>1}, 0)).as(:upd)
] end
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
