Hi Jeremy,
Thanks for the quick reply! So I failed to mention initially that this is
not just for unique constraints, but rather an exclusion constraint
(dealing with a time range where we don't want overlapping periods) --
which kind of complicates things.
So after more research it actually appears Postgres doesn't support
updating on conflicts for exclusion constraints. Instead, I'm going to run
with:
CREATE TABLE example (
id serial PRIMARY KEY,
a text NOT NULL,
b text NOT NULL,
c text NOT NULL,
d tsrange NOT NULL,
EXCLUDE USING gist (a WITH =, b WITH =, c WITH =, d WITH &&)
);
ts_range = Sequel::Postgres::PGRange.new(Time.new(2015, 1, 1), nil)
DB[:example].returning.insert_ignore.import(
[:a, :b, :c, :d],
[['a', 'a', 'a', ts_range], ['a', 'a', 'a', ts_range]]
)
This of course would return only the one insert. Doing so I think I can
just run a diff against what was created vs. what wasn't, and then handle
accordingly.
Again, thanks for the tip and your enormous amount of work on Sequel.
Cheers,
John
On Thursday, March 10, 2016 at 7:36:21 PM UTC-5, Jeremy Evans wrote:
>
> On Thursday, March 10, 2016 at 4:26:07 PM UTC-8, John Barker wrote:
>>
>> Apologies if this has already been asked and/or answered (I did
>> extensively search the documentation, github issues and this group).
>>
>> Is it possible to use `insert_conflict` with `import` using Postgres? I'm
>> wanting to efficiently bulk add items, but I do have unique constraints in
>> place that I need to account for and handle accordingly. Currently, I'm
>> running with the following idea (but have yet to test):
>>
>> DB[:table]
>>> .insert_conflict(
>>> constraint: :table_a_uniq_idx,
>>> update: {
>>> a: :excluded__a
>>> }
>>> )
>>> .import([:a, :b], [['a', 'b'], ['a', 'c']])
>>>
>>
> I don't think it will work due to a PostgreSQL restriction:
>
> Sequel::DatabaseError: PG::CardinalityViolation: ERROR: ON CONFLICT DO
> UPDATE command cannot affect row a second time
>
> However, assuming that values for the 'a' column are unique in the
> imported data, which you should be able to check before using them, it will
> update existing matching rows already in the table.
>
> 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.
