module Sequel::Postgres::DatasetMethods

  1. lib/sequel/adapters/shared/postgres.rb

Constants

EXPLAIN_BOOLEAN_OPTIONS = {}  
EXPLAIN_NONBOOLEAN_OPTIONS = { :serialize => {:none=>"SERIALIZE NONE", :text=>"SERIALIZE TEXT", :binary=>"SERIALIZE BINARY"}.freeze, :format => {:text=>"FORMAT TEXT", :xml=>"FORMAT XML", :json=>"FORMAT JSON", :yaml=>"FORMAT YAML"}.freeze }.freeze  
LOCK_MODES = ['ACCESS SHARE', 'ROW SHARE', 'ROW EXCLUSIVE', 'SHARE UPDATE EXCLUSIVE', 'SHARE', 'SHARE ROW EXCLUSIVE', 'EXCLUSIVE', 'ACCESS EXCLUSIVE'].each(&:freeze).freeze  
NULL = LiteralString.new('NULL').freeze  

Public Instance methods

analyze()

Return the results of an EXPLAIN ANALYZE query as a string

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1839 def analyze
1840   explain(:analyze=>true)
1841 end
complex_expression_sql_append(sql, op, args)

Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#), and use the ILIKE and NOT ILIKE operators.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1846 def complex_expression_sql_append(sql, op, args)
1847   case op
1848   when :^
1849     j = ' # '
1850     c = false
1851     args.each do |a|
1852       sql << j if c
1853       literal_append(sql, a)
1854       c ||= true
1855     end
1856   when :ILIKE, :'NOT ILIKE'
1857     sql << '('
1858     literal_append(sql, args[0])
1859     sql << ' ' << op.to_s << ' '
1860     literal_append(sql, args[1])
1861     sql << ')'
1862   else
1863     super
1864   end
1865 end
disable_insert_returning()

Disables automatic use of INSERT … RETURNING. You can still use returning manually to force the use of RETURNING when inserting.

This is designed for cases where INSERT RETURNING cannot be used, such as when you are using partitioning with trigger functions or conditional rules, or when you are using a PostgreSQL version less than 8.2, or a PostgreSQL derivative that does not support returning.

Note that when this method is used, insert will not return the primary key of the inserted row, you will have to get the primary key of the inserted row before inserting via nextval, or after inserting via currval or lastval (making sure to use the same database connection for currval or lastval).

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1881 def disable_insert_returning
1882   clone(:disable_insert_returning=>true)
1883 end
empty?()

Always return false when using VALUES

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1886 def empty?
1887   return false if @opts[:values]
1888   super
1889 end
explain(opts=OPTS)

Return the results of an EXPLAIN query. Boolean options:

:analyze

Use the ANALYZE option.

:buffers

Use the BUFFERS option.

:costs

Use the COSTS option.

:generic_plan

Use the GENERIC_PLAN option.

:memory

Use the MEMORY option.

:settings

Use the SETTINGS option.

:summary

Use the SUMMARY option.

:timing

Use the TIMING option.

:verbose

Use the VERBOSE option.

:wal

Use the WAL option.

Non boolean options:

:format

Use the FORMAT option to change the format of the returned value. Values can be :text, :xml, :json, or :yaml.

:serialize

Use the SERIALIZE option to get timing on serialization. Values can be :none, :text, or :binary.

See the PostgreSQL EXPLAIN documentation for an explanation of what each option does.

In most cases, the return value is a single string. However, using the format: :json option can result in the return value being an array containing a hash.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1919 def explain(opts=OPTS)
1920   rows = clone(:append_sql=>explain_sql_string_origin(opts)).map(:'QUERY PLAN')
1921 
1922   if rows.length == 1
1923     rows[0]
1924   elsif rows.all?{|row| String === row}
1925     rows.join("\r\n") 
1926   # :nocov:
1927   else
1928     # This branch is unreachable in tests, but it seems better to just return
1929     # all rows than throw in error if this case actually happens.
1930     rows
1931   # :nocov:
1932   end
1933 end
for_share()

Return a cloned dataset which will use FOR SHARE to lock returned rows.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1936 def for_share
1937   lock_style(:share)
1938 end
insert(*values)

Insert given values into the database.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2001 def insert(*values)
2002   if @opts[:returning]
2003     # Already know which columns to return, let the standard code handle it
2004     super
2005   elsif @opts[:sql] || @opts[:disable_insert_returning]
2006     # Raw SQL used or RETURNING disabled, just use the default behavior
2007     # and return nil since sequence is not known.
2008     super
2009     nil
2010   else
2011     # Force the use of RETURNING with the primary key value,
2012     # unless it has been disabled.
2013     returning(insert_pk).insert(*values){|r| return r.values.first}
2014   end
2015 end
insert_conflict(opts=OPTS)

Handle uniqueness violations when inserting, by updating the conflicting row, using ON CONFLICT. With no options, uses ON CONFLICT DO NOTHING. Options:

:conflict_where

The index filter, when using a partial index to determine uniqueness.

:constraint

An explicit constraint name, has precendence over :target.

:target

The column name or expression to handle uniqueness violations on.

:update

A hash of columns and values to set. Uses ON CONFLICT DO UPDATE.

:update_where

A WHERE condition to use for the update.

Examples:

DB[:table].insert_conflict.insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT DO NOTHING

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

DB[:table].insert_conflict(target: :a).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) DO NOTHING

DB[:table].insert_conflict(target: :a, conflict_where: {c: true}).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) WHERE (c IS TRUE) DO NOTHING

DB[:table].insert_conflict(target: :a, update: {b: Sequel[:excluded][:b]}).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) DO UPDATE SET b = excluded.b

DB[:table].insert_conflict(constraint: :table_a_uidx,
  update: {b: Sequel[:excluded][:b]}, update_where: {Sequel[: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)
[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2052 def insert_conflict(opts=OPTS)
2053   clone(:insert_conflict => opts)
2054 end
insert_ignore()

Ignore uniqueness/exclusion violations when inserting, using ON CONFLICT DO NOTHING. Exists mostly for compatibility to MySQL’s insert_ignore. Example:

DB[:table].insert_ignore.insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT DO NOTHING
[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2062 def insert_ignore
2063   insert_conflict
2064 end
insert_select(*values)

Insert a record, returning the record inserted, using RETURNING. Always returns nil without running an INSERT statement if disable_insert_returning is used. If the query runs but returns no values, returns false.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2069 def insert_select(*values)
2070   return unless supports_insert_select?
2071   # Handle case where query does not return a row
2072   server?(:default).with_sql_first(insert_select_sql(*values)) || false
2073 end
insert_select_sql(*values)

The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2077 def insert_select_sql(*values)
2078   ds = opts[:returning] ? self : returning
2079   ds.insert_sql(*values)
2080 end
join_table(type, table, expr=nil, options=OPTS, &block)

Support SQL::AliasedExpression as expr to setup a USING join with a table alias for the USING columns.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2084 def join_table(type, table, expr=nil, options=OPTS, &block)
2085   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
2086     options = options.merge(:join_using=>true)
2087   end
2088   super
2089 end
lock(mode, opts=OPTS)

Locks all tables in the dataset’s FROM clause (but not in JOINs) with the specified mode (e.g. ‘EXCLUSIVE’). If a block is given, starts a new transaction, locks the table, and yields. If a block is not given, just locks the tables. Note that PostgreSQL will probably raise an error if you lock the table outside of an existing transaction. Returns nil.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2096 def lock(mode, opts=OPTS)
2097   if defined?(yield) # perform locking inside a transaction and yield to block
2098     @db.transaction(opts){lock(mode, opts); yield}
2099   else
2100     sql = 'LOCK TABLE '.dup
2101     source_list_append(sql, @opts[:from])
2102     mode = mode.to_s.upcase.strip
2103     unless LOCK_MODES.include?(mode)
2104       raise Error, "Unsupported lock mode: #{mode}"
2105     end
2106     sql << " IN #{mode} MODE"
2107     @db.execute(sql, opts)
2108   end
2109   nil
2110 end
merge(&block)

Support MERGE RETURNING on PostgreSQL 17+.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2113 def merge(&block)
2114   sql = merge_sql
2115   if uses_returning?(:merge)
2116     returning_fetch_rows(sql, &block)
2117   else
2118     execute_ddl(sql)
2119   end
2120 end
merge_delete_when_not_matched_by_source(&block)

Return a dataset with a WHEN NOT MATCHED BY SOURCE THEN DELETE clause added to the MERGE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_delete_not_matched_by_source
# WHEN NOT MATCHED BY SOURCE THEN DELETE

merge_delete_not_matched_by_source{a > 30}
# WHEN NOT MATCHED BY SOURCE AND (a > 30) THEN DELETE
[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2131 def merge_delete_when_not_matched_by_source(&block)
2132   _merge_when(:type=>:delete_not_matched_by_source, &block)
2133 end
merge_do_nothing_when_matched(&block)

Return a dataset with a WHEN MATCHED THEN DO NOTHING clause added to the MERGE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_do_nothing_when_matched
# WHEN MATCHED THEN DO NOTHING

merge_do_nothing_when_matched{a > 30}
# WHEN MATCHED AND (a > 30) THEN DO NOTHING
[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2144 def merge_do_nothing_when_matched(&block)
2145   _merge_when(:type=>:matched, &block)
2146 end
merge_do_nothing_when_not_matched(&block)

Return a dataset with a WHEN NOT MATCHED THEN DO NOTHING clause added to the MERGE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_do_nothing_when_not_matched
# WHEN NOT MATCHED THEN DO NOTHING

merge_do_nothing_when_not_matched{a > 30}
# WHEN NOT MATCHED AND (a > 30) THEN DO NOTHING
[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2157 def merge_do_nothing_when_not_matched(&block)
2158   _merge_when(:type=>:not_matched, &block)
2159 end
merge_do_nothing_when_not_matched_by_source(&block)

Return a dataset with a WHEN NOT MATCHED BY SOURCE THEN DO NOTHING clause added to the MERGE BY SOURCE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_do_nothing_when_not_matched_by_source
# WHEN NOT MATCHED BY SOURCE THEN DO NOTHING

merge_do_nothing_when_not_matched_by_source{a > 30}
# WHEN NOT MATCHED BY SOURCE AND (a > 30) THEN DO NOTHING
[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2170 def merge_do_nothing_when_not_matched_by_source(&block)
2171   _merge_when(:type=>:not_matched_by_source, &block)
2172 end
merge_insert(*values, &block)

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2175 def merge_insert(*values, &block)
2176   h = {:type=>:insert, :values=>values}
2177   if @opts[:override]
2178     h[:override] = insert_override_sql(String.new)
2179   end
2180   _merge_when(h, &block)
2181 end
merge_update_when_not_matched_by_source(values, &block)

Return a dataset with a WHEN NOT MATCHED BY SOURCE THEN UPDATE clause added to the MERGE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_update_not_matched_by_source(i1: Sequel[:i1]+:i2+10, a: Sequel[:a]+:b+20)
# WHEN NOT MATCHED BY SOURCE THEN UPDATE SET i1 = (i1 + i2 + 10), a = (a + b + 20)

merge_update_not_matched_by_source(i1: :i2){a > 30}
# WHEN NOT MATCHED BY SOURCE AND (a > 30) THEN UPDATE SET i1 = i2
[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2192 def merge_update_when_not_matched_by_source(values, &block)
2193   _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block)
2194 end
overriding_system_value()

Use OVERRIDING USER VALUE for INSERT statements, so that identity columns always use the user supplied value, and an error is not raised for identity columns that are GENERATED ALWAYS.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2199 def overriding_system_value
2200   clone(:override=>:system)
2201 end
overriding_user_value()

Use OVERRIDING USER VALUE for INSERT statements, so that identity columns always use the sequence value instead of the user supplied value.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2205 def overriding_user_value
2206   clone(:override=>:user)
2207 end
supports_cte?(type=:select)
[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2209 def supports_cte?(type=:select)
2210   if type == :select
2211     server_version >= 80400
2212   else
2213     server_version >= 90100
2214   end
2215 end
supports_cte_in_subqueries?()

PostgreSQL supports using the WITH clause in subqueries if it supports using WITH at all (i.e. on PostgreSQL 8.4+).

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2219 def supports_cte_in_subqueries?
2220   supports_cte?
2221 end
supports_distinct_on?()

DISTINCT ON is a PostgreSQL extension

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2224 def supports_distinct_on?
2225   true
2226 end
supports_group_cube?()

PostgreSQL 9.5+ supports GROUP CUBE

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2229 def supports_group_cube?
2230   server_version >= 90500
2231 end
supports_group_rollup?()

PostgreSQL 9.5+ supports GROUP ROLLUP

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2234 def supports_group_rollup?
2235   server_version >= 90500
2236 end
supports_grouping_sets?()

PostgreSQL 9.5+ supports GROUPING SETS

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2239 def supports_grouping_sets?
2240   server_version >= 90500
2241 end
supports_insert_conflict?()

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2249 def supports_insert_conflict?
2250   server_version >= 90500
2251 end
supports_insert_select?()

True unless insert returning has been disabled for this dataset.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2244 def supports_insert_select?
2245   !@opts[:disable_insert_returning]
2246 end
supports_lateral_subqueries?()

PostgreSQL 9.3+ supports lateral subqueries

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2254 def supports_lateral_subqueries?
2255   server_version >= 90300
2256 end
supports_merge?()

PostgreSQL 15+ supports MERGE.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2264 def supports_merge?
2265   server_version >= 150000
2266 end
supports_modifying_joins?()

PostgreSQL supports modifying joined datasets

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2259 def supports_modifying_joins?
2260   true
2261 end
supports_nowait?()

PostgreSQL supports NOWAIT.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2269 def supports_nowait?
2270   true
2271 end
supports_regexp?()

PostgreSQL supports pattern matching via regular expressions

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2284 def supports_regexp?
2285   true
2286 end
supports_returning?(type)

MERGE RETURNING is supported on PostgreSQL 17+. Other RETURNING is supported on all supported PostgreSQL versions.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2275 def supports_returning?(type)
2276   if type == :merge
2277     server_version >= 170000
2278   else
2279     true
2280   end
2281 end
supports_skip_locked?()

PostgreSQL 9.5+ supports SKIP LOCKED.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2289 def supports_skip_locked?
2290   server_version >= 90500
2291 end
supports_timestamp_timezones?()

PostgreSQL supports timezones in literal timestamps

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2296 def supports_timestamp_timezones?
2297   # SEQUEL6: Remove
2298   true
2299 end
supports_window_clause?()

PostgreSQL 8.4+ supports WINDOW clause.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2303 def supports_window_clause?
2304   server_version >= 80400
2305 end
supports_window_function_frame_option?(option)

Base support added in 8.4, offset supported added in 9.0, GROUPS and EXCLUDE support added in 11.0.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2314 def supports_window_function_frame_option?(option)
2315   case option
2316   when :rows, :range
2317     true
2318   when :offset
2319     server_version >= 90000
2320   when :groups, :exclude
2321     server_version >= 110000
2322   else
2323     false
2324   end
2325 end
supports_window_functions?()

PostgreSQL 8.4+ supports window functions

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2308 def supports_window_functions?
2309   server_version >= 80400
2310 end
truncate(opts = OPTS)

Truncates the dataset. Returns nil.

Options:

:cascade

whether to use the CASCADE option, useful when truncating tables with foreign keys.

:only

truncate using ONLY, so child tables are unaffected

:restart

use RESTART IDENTITY to restart any related sequences

:only and :restart only work correctly on PostgreSQL 8.4+.

Usage:

DB[:table].truncate
# TRUNCATE TABLE "table"

DB[:table].truncate(cascade: true, only: true, restart: true)
# TRUNCATE TABLE ONLY "table" RESTART IDENTITY CASCADE
[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2343 def truncate(opts = OPTS)
2344   if opts.empty?
2345     super()
2346   else
2347     clone(:truncate_opts=>opts).truncate
2348   end
2349 end
with_ties()

Use WITH TIES when limiting the result set to also include additional rules that have the same results for the order column as the final row. Requires PostgreSQL 13.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2354 def with_ties
2355   clone(:limit_with_ties=>true)
2356 end

Protected Instance methods

_import(columns, values, opts=OPTS)

If returned primary keys are requested, use RETURNING unless already set on the dataset. If RETURNING is already set, use existing returning values. If RETURNING is only set to return a single columns, return an array of just that column. Otherwise, return an array of hashes.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2364 def _import(columns, values, opts=OPTS)
2365   if @opts[:returning]
2366     # no transaction: our multi_insert_sql_strategy should guarantee
2367     # that there's only ever a single statement.
2368     sql = multi_insert_sql(columns, values)[0]
2369     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
2370   elsif opts[:return] == :primary_key
2371     returning(insert_pk)._import(columns, values, opts)
2372   else
2373     super
2374   end
2375 end
to_prepared_statement(type, *a)
[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2377 def to_prepared_statement(type, *a)
2378   if type == :insert && !@opts.has_key?(:returning)
2379     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
2380   else
2381     super
2382   end
2383 end