Path: | doc/release_notes/4.25.0.txt |
Last Update: | Sun Oct 02 21:06:53 +0000 2016 |
DB[:table].where{(column1 + column2) =~ column3}
ds = DB[:table].group(:column1) # SELECT * FROM table GROUP BY column1 ds = ds.group_append(:column2) # SELECT * FROM table GROUP BY column1, column2
Album.plugin :inverted_subsets Album.subset :published, :published=>true Album.published # SELECT * FROM albums WHERE published IS TRUE Album.not_published # SELECT * FROM albums WHERE published IS NOT TRUE
By default, the subset method name is prefixed with "not_". You can pass a block to override the default behavior:
Album.plugin(:inverted_subsets){|name| "exclude_#{name}"} Album.subset :published, :published=>true Album.exclude_published # SELECT * FROM albums WHERE published IS NOT TRUE
Sequel::Model.plugin :singular_table_names class FooBar < Sequel::Model; end FooBar.table_name # => foo_bar
DB[:table].insert_conflict.insert(:a=>1, :b=>2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT DO NOTHING
You can pass a specific constraint name using :constraint, to only ignore a specific constraint violation:
DB[:table].insert_conflict(:constraint=>:table_a_uidx). insert(:a=>1, :b=>2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT ON CONSTRAINT table_a_uidx DO NOTHING
If the unique or exclusion constraint covers the whole table (e.g. it isn‘t a partial unique index), then you can just specify the column using the :target option:
DB[:table].insert_conflict(:target=>:a).insert(:a=>1, :b=>2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT (a) DO NOTHING
If you want to update the existing row instead of ignoring the constraint violation, you can pass an :update option with a hash of values to update. You must pass either the :target or :constraint options when passing the :update option:
DB[:table].insert_conflict(:target=>:a, :update=>{:b=>:excluded__b}). insert(:a=>1, :b=>2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT (a) DO UPDATE SET b = excluded.b
Additionally, if you only want to do the update in certain cases, you can specify an :update_where option, which will be used as a filter. If the row doesn‘t match the conditions, the constraint violation will be ignored, but the row will not be updated:
DB[:table].insert_conflict(:constraint=>:table_a_uidx, :update=>{:b=>:excluded__b}, :update_where=>{:table__status_id=>1}). insert(:a=>1, :b=>2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT ON CONSTRAINT table_a_uidx # DO UPDATE SET b = excluded.b WHERE (table.status_id = 1)
DB[:table].returning.prepare(:insert, :i, :col=>:$col). call(:col=>42) # => [{:col=>42}]
DB[:table].where(:column=>[])
would be literalized as:
SELECT * FROM table WHERE (column != column)
This yields a NULL value when column is NULL, similarly to how most other SQL operators work. Unfortunately, most databases do not optimize this, and such a query can require a sequential scan of the table.
Sequel previously shipped with a empty_array_ignore_nulls extension that literalized the query to:
SELECT * FROM table WHERE (1 = 0)
which databases will generally optimize to a constant false value, resulting in much faster queries. This behavior is now the default.
Users that desire the previous behavior can use the new empty_array_consider_nulls extension.
String :column_name, collate=>:'"C"'
would need to change to:
String :column_name, collate=>:C # or String :column_name, collate=>'"C"'