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

Always return false when using VALUES

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

Return the results of an EXPLAIN query as a string

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

Insert given values into the database.

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

Support MERGE RETURNING on PostgreSQL 17+.

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

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

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

DISTINCT ON is a PostgreSQL extension

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

PostgreSQL 9.5+ supports GROUP CUBE

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

PostgreSQL 9.5+ supports GROUP ROLLUP

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

PostgreSQL 9.5+ supports GROUPING SETS

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

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

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

True unless insert returning has been disabled for this dataset.

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

PostgreSQL 9.3+ supports lateral subqueries

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

PostgreSQL 15+ supports MERGE.

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

PostgreSQL supports modifying joined datasets

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

PostgreSQL supports NOWAIT.

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

PostgreSQL supports pattern matching via regular expressions

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

PostgreSQL 9.5+ supports SKIP LOCKED.

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

PostgreSQL supports timezones in literal timestamps

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

PostgreSQL 8.4+ supports WINDOW clause.

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

PostgreSQL 8.4+ supports window functions

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