module Sequel::Postgres::DatasetMethods

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

Constants

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
1828 def analyze
1829   explain(:analyze=>true)
1830 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
1835 def complex_expression_sql_append(sql, op, args)
1836   case op
1837   when :^
1838     j = ' # '
1839     c = false
1840     args.each do |a|
1841       sql << j if c
1842       literal_append(sql, a)
1843       c ||= true
1844     end
1845   when :ILIKE, :'NOT ILIKE'
1846     sql << '('
1847     literal_append(sql, args[0])
1848     sql << ' ' << op.to_s << ' '
1849     literal_append(sql, args[1])
1850     sql << ')'
1851   else
1852     super
1853   end
1854 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
1870 def disable_insert_returning
1871   clone(:disable_insert_returning=>true)
1872 end
empty?()

Always return false when using VALUES

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1875 def empty?
1876   return false if @opts[:values]
1877   super
1878 end
explain(opts=OPTS)

Return the results of an EXPLAIN query as a string

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1881 def explain(opts=OPTS)
1882   with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n")
1883 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
1886 def for_share
1887   lock_style(:share)
1888 end
full_text_search(cols, terms, opts = OPTS)

Run a full text search on PostgreSQL. By default, searching for the inclusion of any of the terms in any of the cols.

Options:

:headline

Append a expression to the selected columns aliased to headline that contains an extract of the matched text.

:language

The language to use for the search (default: ‘simple’)

:plain

Whether a plain search should be used (default: false). In this case, terms should be a single string, and it will do a search where cols contains all of the words in terms. This ignores search operators in terms.

:phrase

Similar to :plain, but also adding an ILIKE filter to ensure that returned rows also include the exact phrase used.

:rank

Set to true to order by the rank, so that closer matches are returned first.

:to_tsquery

Can be set to :plain, :phrase, or :websearch to specify the function to use to convert the terms to a ts_query.

:tsquery

Specifies the terms argument is already a valid SQL expression returning a tsquery, and can be used directly in the query.

:tsvector

Specifies the cols argument is already a valid SQL expression returning a tsvector, and can be used directly in the query.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1909 def full_text_search(cols, terms, opts = OPTS)
1910   lang = Sequel.cast(opts[:language] || 'simple', :regconfig)
1911 
1912   unless opts[:tsvector]
1913     phrase_cols = full_text_string_join(cols)
1914     cols = Sequel.function(:to_tsvector, lang, phrase_cols)
1915   end
1916 
1917   unless opts[:tsquery]
1918     phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms
1919 
1920     query_func = case to_tsquery = opts[:to_tsquery]
1921     when :phrase, :plain
1922       :"#{to_tsquery}to_tsquery"
1923     when :websearch
1924       :"websearch_to_tsquery"
1925     else
1926       (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery
1927     end
1928 
1929     terms = Sequel.function(query_func, lang, phrase_terms)
1930   end
1931 
1932   ds = where(Sequel.lit(["", " @@ ", ""], cols, terms))
1933 
1934   if opts[:phrase]
1935     raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery]
1936     ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true)
1937   end
1938 
1939   if opts[:rank]
1940     ds = ds.reverse{ts_rank_cd(cols, terms)}
1941   end
1942 
1943   if opts[:headline]
1944     ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)}
1945   end
1946 
1947   ds
1948 end
insert(*values)

Insert given values into the database.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1951 def insert(*values)
1952   if @opts[:returning]
1953     # Already know which columns to return, let the standard code handle it
1954     super
1955   elsif @opts[:sql] || @opts[:disable_insert_returning]
1956     # Raw SQL used or RETURNING disabled, just use the default behavior
1957     # and return nil since sequence is not known.
1958     super
1959     nil
1960   else
1961     # Force the use of RETURNING with the primary key value,
1962     # unless it has been disabled.
1963     returning(insert_pk).insert(*values){|r| return r.values.first}
1964   end
1965 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
2002 def insert_conflict(opts=OPTS)
2003   clone(:insert_conflict => opts)
2004 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
2012 def insert_ignore
2013   insert_conflict
2014 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
2019 def insert_select(*values)
2020   return unless supports_insert_select?
2021   # Handle case where query does not return a row
2022   server?(:default).with_sql_first(insert_select_sql(*values)) || false
2023 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
2027 def insert_select_sql(*values)
2028   ds = opts[:returning] ? self : returning
2029   ds.insert_sql(*values)
2030 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
2034 def join_table(type, table, expr=nil, options=OPTS, &block)
2035   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
2036     options = options.merge(:join_using=>true)
2037   end
2038   super
2039 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
2046 def lock(mode, opts=OPTS)
2047   if defined?(yield) # perform locking inside a transaction and yield to block
2048     @db.transaction(opts){lock(mode, opts); yield}
2049   else
2050     sql = 'LOCK TABLE '.dup
2051     source_list_append(sql, @opts[:from])
2052     mode = mode.to_s.upcase.strip
2053     unless LOCK_MODES.include?(mode)
2054       raise Error, "Unsupported lock mode: #{mode}"
2055     end
2056     sql << " IN #{mode} MODE"
2057     @db.execute(sql, opts)
2058   end
2059   nil
2060 end
merge(&block)

Support MERGE RETURNING on PostgreSQL 17+.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2063 def merge(&block)
2064   sql = merge_sql
2065   if uses_returning?(:merge)
2066     returning_fetch_rows(sql, &block)
2067   else
2068     execute_ddl(sql)
2069   end
2070 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
2081 def merge_delete_when_not_matched_by_source(&block)
2082   _merge_when(:type=>:delete_not_matched_by_source, &block)
2083 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
2094 def merge_do_nothing_when_matched(&block)
2095   _merge_when(:type=>:matched, &block)
2096 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
2107 def merge_do_nothing_when_not_matched(&block)
2108   _merge_when(:type=>:not_matched, &block)
2109 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
2120 def merge_do_nothing_when_not_matched_by_source(&block)
2121   _merge_when(:type=>:not_matched_by_source, &block)
2122 end
merge_insert(*values, &block)

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2125 def merge_insert(*values, &block)
2126   h = {:type=>:insert, :values=>values}
2127   if @opts[:override]
2128     h[:override] = insert_override_sql(String.new)
2129   end
2130   _merge_when(h, &block)
2131 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
2142 def merge_update_when_not_matched_by_source(values, &block)
2143   _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block)
2144 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
2149 def overriding_system_value
2150   clone(:override=>:system)
2151 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
2155 def overriding_user_value
2156   clone(:override=>:user)
2157 end
supports_cte?(type=:select)
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2159 def supports_cte?(type=:select)
2160   if type == :select
2161     server_version >= 80400
2162   else
2163     server_version >= 90100
2164   end
2165 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
2169 def supports_cte_in_subqueries?
2170   supports_cte?
2171 end
supports_distinct_on?()

DISTINCT ON is a PostgreSQL extension

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2174 def supports_distinct_on?
2175   true
2176 end
supports_group_cube?()

PostgreSQL 9.5+ supports GROUP CUBE

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

PostgreSQL 9.5+ supports GROUP ROLLUP

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

PostgreSQL 9.5+ supports GROUPING SETS

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

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

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

True unless insert returning has been disabled for this dataset.

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

PostgreSQL 9.3+ supports lateral subqueries

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

PostgreSQL 15+ supports MERGE.

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

PostgreSQL supports modifying joined datasets

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2209 def supports_modifying_joins?
2210   true
2211 end
supports_nowait?()

PostgreSQL supports NOWAIT.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2219 def supports_nowait?
2220   true
2221 end
supports_regexp?()

PostgreSQL supports pattern matching via regular expressions

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2234 def supports_regexp?
2235   true
2236 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
2225 def supports_returning?(type)
2226   if type == :merge
2227     server_version >= 170000
2228   else
2229     true
2230   end
2231 end
supports_skip_locked?()

PostgreSQL 9.5+ supports SKIP LOCKED.

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

PostgreSQL supports timezones in literal timestamps

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2246 def supports_timestamp_timezones?
2247   # SEQUEL6: Remove
2248   true
2249 end
supports_window_clause?()

PostgreSQL 8.4+ supports WINDOW clause.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2253 def supports_window_clause?
2254   server_version >= 80400
2255 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
2264 def supports_window_function_frame_option?(option)
2265   case option
2266   when :rows, :range
2267     true
2268   when :offset
2269     server_version >= 90000
2270   when :groups, :exclude
2271     server_version >= 110000
2272   else
2273     false
2274   end
2275 end
supports_window_functions?()

PostgreSQL 8.4+ supports window functions

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2258 def supports_window_functions?
2259   server_version >= 80400
2260 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
2293 def truncate(opts = OPTS)
2294   if opts.empty?
2295     super()
2296   else
2297     clone(:truncate_opts=>opts).truncate
2298   end
2299 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
2304 def with_ties
2305   clone(:limit_with_ties=>true)
2306 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
2314 def _import(columns, values, opts=OPTS)
2315   if @opts[:returning]
2316     # no transaction: our multi_insert_sql_strategy should guarantee
2317     # that there's only ever a single statement.
2318     sql = multi_insert_sql(columns, values)[0]
2319     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
2320   elsif opts[:return] == :primary_key
2321     returning(insert_pk)._import(columns, values, opts)
2322   else
2323     super
2324   end
2325 end
to_prepared_statement(type, *a)
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2327 def to_prepared_statement(type, *a)
2328   if type == :insert && !@opts.has_key?(:returning)
2329     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
2330   else
2331     super
2332   end
2333 end