module Sequel::Postgres::DatasetMethods

Constants

LOCK_MODES
NULL

Public Instance Methods

analyze() click to toggle source

Return the results of an EXPLAIN ANALYZE query as a string

     # File lib/sequel/adapters/shared/postgres.rb
1726 def analyze
1727   explain(:analyze=>true)
1728 end
complex_expression_sql_append(sql, op, args) click to toggle source

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

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1733 def complex_expression_sql_append(sql, op, args)
1734   case op
1735   when :^
1736     j = ' # '
1737     c = false
1738     args.each do |a|
1739       sql << j if c
1740       literal_append(sql, a)
1741       c ||= true
1742     end
1743   when :ILIKE, :'NOT ILIKE'
1744     sql << '('
1745     literal_append(sql, args[0])
1746     sql << ' ' << op.to_s << ' '
1747     literal_append(sql, args[1])
1748     sql << ')'
1749   else
1750     super
1751   end
1752 end
disable_insert_returning() click to toggle source

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).

     # File lib/sequel/adapters/shared/postgres.rb
1768 def disable_insert_returning
1769   clone(:disable_insert_returning=>true)
1770 end
empty?() click to toggle source

Always return false when using VALUES

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1773 def empty?
1774   return false if @opts[:values]
1775   super
1776 end
explain(opts=OPTS) click to toggle source

Return the results of an EXPLAIN query as a string

     # File lib/sequel/adapters/shared/postgres.rb
1779 def explain(opts=OPTS)
1780   with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n")
1781 end
for_share() click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
1784 def for_share
1785   lock_style(:share)
1786 end
insert(*values) click to toggle source

Insert given values into the database.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1847 def insert(*values)
1848   if @opts[:returning]
1849     # Already know which columns to return, let the standard code handle it
1850     super
1851   elsif @opts[:sql] || @opts[:disable_insert_returning]
1852     # Raw SQL used or RETURNING disabled, just use the default behavior
1853     # and return nil since sequence is not known.
1854     super
1855     nil
1856   else
1857     # Force the use of RETURNING with the primary key value,
1858     # unless it has been disabled.
1859     returning(insert_pk).insert(*values){|r| return r.values.first}
1860   end
1861 end
insert_conflict(opts=OPTS) click to toggle source

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)
     # File lib/sequel/adapters/shared/postgres.rb
1898 def insert_conflict(opts=OPTS)
1899   clone(:insert_conflict => opts)
1900 end
insert_ignore() click to toggle source

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
     # File lib/sequel/adapters/shared/postgres.rb
1908 def insert_ignore
1909   insert_conflict
1910 end
insert_select(*values) click to toggle source

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.

     # File lib/sequel/adapters/shared/postgres.rb
1915 def insert_select(*values)
1916   return unless supports_insert_select?
1917   # Handle case where query does not return a row
1918   server?(:default).with_sql_first(insert_select_sql(*values)) || false
1919 end
insert_select_sql(*values) click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
1923 def insert_select_sql(*values)
1924   ds = opts[:returning] ? self : returning
1925   ds.insert_sql(*values)
1926 end
join_table(type, table, expr=nil, options=OPTS, &block) click to toggle source

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

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1930 def join_table(type, table, expr=nil, options=OPTS, &block)
1931   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
1932     options = options.merge(:join_using=>true)
1933   end
1934   super
1935 end
lock(mode, opts=OPTS) { || ... } click to toggle source

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.

     # File lib/sequel/adapters/shared/postgres.rb
1942 def lock(mode, opts=OPTS)
1943   if defined?(yield) # perform locking inside a transaction and yield to block
1944     @db.transaction(opts){lock(mode, opts); yield}
1945   else
1946     sql = 'LOCK TABLE '.dup
1947     source_list_append(sql, @opts[:from])
1948     mode = mode.to_s.upcase.strip
1949     unless LOCK_MODES.include?(mode)
1950       raise Error, "Unsupported lock mode: #{mode}"
1951     end
1952     sql << " IN #{mode} MODE"
1953     @db.execute(sql, opts)
1954   end
1955   nil
1956 end
merge_do_nothing_when_matched(&block) click to toggle source

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
     # File lib/sequel/adapters/shared/postgres.rb
1967 def merge_do_nothing_when_matched(&block)
1968   _merge_when(:type=>:matched, &block)
1969 end
merge_do_nothing_when_not_matched(&block) click to toggle source

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
     # File lib/sequel/adapters/shared/postgres.rb
1980 def merge_do_nothing_when_not_matched(&block)
1981   _merge_when(:type=>:not_matched, &block)
1982 end
merge_insert(*values, &block) click to toggle source

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
1985 def merge_insert(*values, &block)
1986   h = {:type=>:insert, :values=>values}
1987   if override = @opts[:override]
1988     h[:override] = insert_override_sql(String.new)
1989   end
1990   _merge_when(h, &block)
1991 end
overriding_system_value() click to toggle source

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.

     # File lib/sequel/adapters/shared/postgres.rb
1996 def overriding_system_value
1997   clone(:override=>:system)
1998 end
overriding_user_value() click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
2002 def overriding_user_value
2003   clone(:override=>:user)
2004 end
supports_cte?(type=:select) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2006 def supports_cte?(type=:select)
2007   if type == :select
2008     server_version >= 80400
2009   else
2010     server_version >= 90100
2011   end
2012 end
supports_cte_in_subqueries?() click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
2016 def supports_cte_in_subqueries?
2017   supports_cte?
2018 end
supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

     # File lib/sequel/adapters/shared/postgres.rb
2021 def supports_distinct_on?
2022   true
2023 end
supports_group_cube?() click to toggle source

PostgreSQL 9.5+ supports GROUP CUBE

     # File lib/sequel/adapters/shared/postgres.rb
2026 def supports_group_cube?
2027   server_version >= 90500
2028 end
supports_group_rollup?() click to toggle source

PostgreSQL 9.5+ supports GROUP ROLLUP

     # File lib/sequel/adapters/shared/postgres.rb
2031 def supports_group_rollup?
2032   server_version >= 90500
2033 end
supports_grouping_sets?() click to toggle source

PostgreSQL 9.5+ supports GROUPING SETS

     # File lib/sequel/adapters/shared/postgres.rb
2036 def supports_grouping_sets?
2037   server_version >= 90500
2038 end
supports_insert_conflict?() click to toggle source

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2046 def supports_insert_conflict?
2047   server_version >= 90500
2048 end
supports_insert_select?() click to toggle source

True unless insert returning has been disabled for this dataset.

     # File lib/sequel/adapters/shared/postgres.rb
2041 def supports_insert_select?
2042   !@opts[:disable_insert_returning]
2043 end
supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

     # File lib/sequel/adapters/shared/postgres.rb
2051 def supports_lateral_subqueries?
2052   server_version >= 90300
2053 end
supports_merge?() click to toggle source

PostgreSQL 15+ supports MERGE.

     # File lib/sequel/adapters/shared/postgres.rb
2061 def supports_merge?
2062   server_version >= 150000
2063 end
supports_modifying_joins?() click to toggle source

PostgreSQL supports modifying joined datasets

     # File lib/sequel/adapters/shared/postgres.rb
2056 def supports_modifying_joins?
2057   true
2058 end
supports_nowait?() click to toggle source

PostgreSQL supports NOWAIT.

     # File lib/sequel/adapters/shared/postgres.rb
2066 def supports_nowait?
2067   true
2068 end
supports_regexp?() click to toggle source

PostgreSQL supports pattern matching via regular expressions

     # File lib/sequel/adapters/shared/postgres.rb
2076 def supports_regexp?
2077   true
2078 end
supports_returning?(type) click to toggle source

Returning is always supported.

     # File lib/sequel/adapters/shared/postgres.rb
2071 def supports_returning?(type)
2072   true
2073 end
supports_skip_locked?() click to toggle source

PostgreSQL 9.5+ supports SKIP LOCKED.

     # File lib/sequel/adapters/shared/postgres.rb
2081 def supports_skip_locked?
2082   server_version >= 90500
2083 end
supports_timestamp_timezones?() click to toggle source

PostgreSQL supports timezones in literal timestamps

     # File lib/sequel/adapters/shared/postgres.rb
2086 def supports_timestamp_timezones?
2087   true
2088 end
supports_window_clause?() click to toggle source

PostgreSQL 8.4+ supports WINDOW clause.

     # File lib/sequel/adapters/shared/postgres.rb
2091 def supports_window_clause?
2092   server_version >= 80400
2093 end
supports_window_function_frame_option?(option) click to toggle source

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

     # File lib/sequel/adapters/shared/postgres.rb
2102 def supports_window_function_frame_option?(option)
2103   case option
2104   when :rows, :range
2105     true
2106   when :offset
2107     server_version >= 90000
2108   when :groups, :exclude
2109     server_version >= 110000
2110   else
2111     false
2112   end
2113 end
supports_window_functions?() click to toggle source

PostgreSQL 8.4+ supports window functions

     # File lib/sequel/adapters/shared/postgres.rb
2096 def supports_window_functions?
2097   server_version >= 80400
2098 end
truncate(opts = OPTS) click to toggle source

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
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2131 def truncate(opts = OPTS)
2132   if opts.empty?
2133     super()
2134   else
2135     clone(:truncate_opts=>opts).truncate
2136   end
2137 end
with_ties() click to toggle source

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.

     # File lib/sequel/adapters/shared/postgres.rb
2142 def with_ties
2143   clone(:limit_with_ties=>true)
2144 end

Protected Instance Methods

_import(columns, values, opts=OPTS) click to toggle source

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.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2152 def _import(columns, values, opts=OPTS)
2153   if @opts[:returning]
2154     # no transaction: our multi_insert_sql_strategy should guarantee
2155     # that there's only ever a single statement.
2156     sql = multi_insert_sql(columns, values)[0]
2157     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
2158   elsif opts[:return] == :primary_key
2159     returning(insert_pk)._import(columns, values, opts)
2160   else
2161     super
2162   end
2163 end
to_prepared_statement(type, *a) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2165 def to_prepared_statement(type, *a)
2166   if type == :insert && !@opts.has_key?(:returning)
2167     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
2168   else
2169     super
2170   end
2171 end

Private Instance Methods

_merge_insert_sql(sql, data) click to toggle source

Append the INSERT sql used in a MERGE

     # File lib/sequel/adapters/shared/postgres.rb
2176 def _merge_insert_sql(sql, data)
2177   sql << " THEN INSERT "
2178   columns, values = _parse_insert_sql_args(data[:values])
2179   _insert_columns_sql(sql, columns)
2180   if override = data[:override]
2181     sql << override
2182   end
2183   _insert_values_sql(sql, values)
2184 end
_merge_matched_sql(sql, data) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2186 def _merge_matched_sql(sql, data)
2187   sql << " THEN DO NOTHING"
2188 end
Also aliased as: _merge_not_matched_sql
_merge_not_matched_sql(sql, data)
Alias for: _merge_matched_sql
_truncate_sql(table) click to toggle source

Format TRUNCATE statement with PostgreSQL specific options.

     # File lib/sequel/adapters/shared/postgres.rb
2192 def _truncate_sql(table)
2193   to = @opts[:truncate_opts] || OPTS
2194   "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}"
2195 end
aggreate_dataset_use_from_self?() click to toggle source

Use from_self for aggregate dataset using VALUES.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2198 def aggreate_dataset_use_from_self?
2199   super || @opts[:values]
2200 end
check_truncation_allowed!() click to toggle source

Allow truncation of multiple source tables.

     # File lib/sequel/adapters/shared/postgres.rb
2203 def check_truncation_allowed!
2204   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
2205   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
2206 end
compound_dataset_sql_append(sql, ds) click to toggle source

PostgreSQL requires parentheses around compound datasets if they use CTEs, and using them in other places doesn't hurt.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2364 def compound_dataset_sql_append(sql, ds)
2365   sql << '('
2366   super
2367   sql << ')'
2368 end
delete_from_sql(sql) click to toggle source

Only include the primary table in the main delete clause

     # File lib/sequel/adapters/shared/postgres.rb
2209 def delete_from_sql(sql)
2210   sql << ' FROM '
2211   source_list_append(sql, @opts[:from][0..0])
2212 end
delete_using_sql(sql) click to toggle source

Use USING to specify additional tables in a delete query

     # File lib/sequel/adapters/shared/postgres.rb
2215 def delete_using_sql(sql)
2216   join_from_sql(:USING, sql)
2217 end
full_text_string_join(cols) click to toggle source

Concatenate the expressions with a space in between

     # File lib/sequel/adapters/shared/postgres.rb
2488 def full_text_string_join(cols)
2489   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
2490   cols = cols.zip([' '] * cols.length).flatten
2491   cols.pop
2492   SQL::StringExpression.new(:'||', *cols)
2493 end
insert_conflict_sql(sql) click to toggle source

Add ON CONFLICT clause if it should be used

     # File lib/sequel/adapters/shared/postgres.rb
2220 def insert_conflict_sql(sql)
2221   if opts = @opts[:insert_conflict]
2222     sql << " ON CONFLICT"
2223 
2224     if target = opts[:constraint] 
2225       sql << " ON CONSTRAINT "
2226       identifier_append(sql, target)
2227     elsif target = opts[:target]
2228       sql << ' '
2229       identifier_append(sql, Array(target))
2230       if conflict_where = opts[:conflict_where]
2231         sql << " WHERE "
2232         literal_append(sql, conflict_where)
2233       end
2234     end
2235 
2236     if values = opts[:update]
2237       sql << " DO UPDATE SET "
2238       update_sql_values_hash(sql, values)
2239       if update_where = opts[:update_where]
2240         sql << " WHERE "
2241         literal_append(sql, update_where)
2242       end
2243     else
2244       sql << " DO NOTHING"
2245     end
2246   end
2247 end
insert_into_sql(sql) click to toggle source

Include aliases when inserting into a single table on PostgreSQL 9.5+.

     # File lib/sequel/adapters/shared/postgres.rb
2250 def insert_into_sql(sql)
2251   sql << " INTO "
2252   if (f = @opts[:from]) && f.length == 1
2253     identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first))
2254   else
2255     source_list_append(sql, f)
2256   end
2257 end
insert_override_sql(sql) click to toggle source

Support OVERRIDING SYSTEM|USER VALUE in insert statements

     # File lib/sequel/adapters/shared/postgres.rb
2271 def insert_override_sql(sql)
2272   case opts[:override]
2273   when :system
2274     sql << " OVERRIDING SYSTEM VALUE"
2275   when :user
2276     sql << " OVERRIDING USER VALUE"
2277   end
2278 end
insert_pk() click to toggle source

Return the primary key to use for RETURNING in an INSERT statement

     # File lib/sequel/adapters/shared/postgres.rb
2260 def insert_pk
2261   (f = opts[:from]) && !f.empty? && (t = f.first)
2262   case t
2263   when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
2264     if pk = db.primary_key(t)
2265       Sequel::SQL::Identifier.new(pk)
2266     end
2267   end
2268 end
join_from_sql(type, sql) click to toggle source

For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.

     # File lib/sequel/adapters/shared/postgres.rb
2282 def join_from_sql(type, sql)
2283   if(from = @opts[:from][1..-1]).empty?
2284     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
2285   else
2286     sql << ' ' << type.to_s << ' '
2287     source_list_append(sql, from)
2288     select_join_sql(sql)
2289   end
2290 end
join_using_clause_using_sql_append(sql, using_columns) click to toggle source

Support table aliases for USING columns

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2293 def join_using_clause_using_sql_append(sql, using_columns)
2294   if using_columns.is_a?(SQL::AliasedExpression)
2295     super(sql, using_columns.expression)
2296     sql << ' AS '
2297     identifier_append(sql, using_columns.alias)
2298   else
2299     super
2300   end
2301 end
literal_blob_append(sql, v) click to toggle source

Use a generic blob quoting method, hopefully overridden in one of the subadapter methods

     # File lib/sequel/adapters/shared/postgres.rb
2304 def literal_blob_append(sql, v)
2305   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
2306 end
literal_false() click to toggle source

PostgreSQL uses FALSE for false values

     # File lib/sequel/adapters/shared/postgres.rb
2309 def literal_false
2310   'false'
2311 end
literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2314 def literal_float(value)
2315   if value.finite?
2316     super
2317   elsif value.nan?
2318     "'NaN'"
2319   elsif value.infinite? == 1
2320     "'Infinity'"
2321   else
2322     "'-Infinity'"
2323   end
2324 end
literal_integer(v) click to toggle source

Handle Ruby integers outside PostgreSQL bigint range specially.

     # File lib/sequel/adapters/shared/postgres.rb
2327 def literal_integer(v)
2328   if v > 9223372036854775807 || v < -9223372036854775808
2329     literal_integer_outside_bigint_range(v)
2330   else
2331     v.to_s
2332   end
2333 end
literal_integer_outside_bigint_range(v) click to toggle source

Raise IntegerOutsideBigintRange when attempting to literalize Ruby integer outside PostgreSQL bigint range, so PostgreSQL doesn't treat the value as numeric.

     # File lib/sequel/adapters/shared/postgres.rb
2338 def literal_integer_outside_bigint_range(v)
2339   raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}"
2340 end
literal_string_append(sql, v) click to toggle source

Assume that SQL standard quoting is on, per Sequel's defaults

     # File lib/sequel/adapters/shared/postgres.rb
2343 def literal_string_append(sql, v)
2344   sql << "'" << v.gsub("'", "''") << "'"
2345 end
literal_true() click to toggle source

PostgreSQL uses true for true values

     # File lib/sequel/adapters/shared/postgres.rb
2348 def literal_true
2349   'true'
2350 end
multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2353 def multi_insert_sql_strategy
2354   :values
2355 end
non_sql_option?(key) click to toggle source

Dataset options that do not affect the generated SQL.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2358 def non_sql_option?(key)
2359   super || key == :cursor || key == :insert_conflict
2360 end
requires_like_escape?() click to toggle source

Backslash is supported by default as the escape character on PostgreSQL, and using ESCAPE can break LIKE ANY() usage.

     # File lib/sequel/adapters/shared/postgres.rb
2372 def requires_like_escape?
2373   false
2374 end
select_limit_sql(sql) click to toggle source

Support FETCH FIRST WITH TIES on PostgreSQL 13+.

     # File lib/sequel/adapters/shared/postgres.rb
2377 def select_limit_sql(sql)
2378   l = @opts[:limit]
2379   o = @opts[:offset]
2380 
2381   return unless l || o
2382 
2383   if @opts[:limit_with_ties]
2384     if o
2385       sql << " OFFSET "
2386       literal_append(sql, o)
2387     end
2388 
2389     if l
2390       sql << " FETCH FIRST "
2391       literal_append(sql, l)
2392       sql << " ROWS WITH TIES"
2393     end
2394   else
2395     if l
2396       sql << " LIMIT "
2397       literal_append(sql, l)
2398     end
2399 
2400     if o
2401       sql << " OFFSET "
2402       literal_append(sql, o)
2403     end
2404   end
2405 end
select_lock_sql(sql) click to toggle source

Support FOR SHARE locking when using the :share lock style. Use SKIP LOCKED if skipping locked rows.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2409 def select_lock_sql(sql)
2410   lock = @opts[:lock]
2411   if lock == :share
2412     sql << ' FOR SHARE'
2413   else
2414     super
2415   end
2416 
2417   if lock
2418     if @opts[:skip_locked]
2419       sql << " SKIP LOCKED"
2420     elsif @opts[:nowait]
2421       sql << " NOWAIT"
2422     end
2423   end
2424 end
select_values_sql(sql) click to toggle source

Support VALUES clause instead of the SELECT clause to return rows.

     # File lib/sequel/adapters/shared/postgres.rb
2427 def select_values_sql(sql)
2428   sql << "VALUES "
2429   expression_list_append(sql, opts[:values])
2430 end
select_with_sql_base() click to toggle source

Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2433 def select_with_sql_base
2434   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
2435 end
select_with_sql_cte(sql, cte) click to toggle source

Support PostgreSQL 14+ CTE SEARCH/CYCLE clauses

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2438 def select_with_sql_cte(sql, cte)
2439   super
2440   select_with_sql_cte_search_cycle(sql, cte)
2441 end
select_with_sql_cte_search_cycle(sql, cte) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2443 def select_with_sql_cte_search_cycle(sql, cte)
2444   if search_opts = cte[:search]
2445     sql << if search_opts[:type] == :breadth
2446       " SEARCH BREADTH FIRST BY "
2447     else
2448       " SEARCH DEPTH FIRST BY "
2449     end
2450 
2451     identifier_list_append(sql, Array(search_opts[:by]))
2452     sql << " SET "
2453     identifier_append(sql, search_opts[:set] || :ordercol)
2454   end
2455 
2456   if cycle_opts = cte[:cycle]
2457     sql << " CYCLE "
2458     identifier_list_append(sql, Array(cycle_opts[:columns]))
2459     sql << " SET "
2460     identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle)
2461     if cycle_opts.has_key?(:cycle_value)
2462       sql << " TO "
2463       literal_append(sql, cycle_opts[:cycle_value])
2464       sql << " DEFAULT "
2465       literal_append(sql, cycle_opts.fetch(:noncycle_value, false))
2466     end
2467     sql << " USING "
2468     identifier_append(sql, cycle_opts[:path_column] || :path)
2469   end
2470 end
server_version() click to toggle source

The version of the database server

     # File lib/sequel/adapters/shared/postgres.rb
2473 def server_version
2474   db.server_version(@opts[:server])
2475 end
supports_filtered_aggregates?() click to toggle source

PostgreSQL 9.4+ supports the FILTER clause for aggregate functions.

     # File lib/sequel/adapters/shared/postgres.rb
2478 def supports_filtered_aggregates?
2479   server_version >= 90400
2480 end
supports_quoted_function_names?() click to toggle source

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
2483 def supports_quoted_function_names?
2484   true
2485 end
update_from_sql(sql) click to toggle source

Use FROM to specify additional tables in an update query

     # File lib/sequel/adapters/shared/postgres.rb
2496 def update_from_sql(sql)
2497   join_from_sql(:FROM, sql)
2498 end
update_table_sql(sql) click to toggle source

Only include the primary table in the main update clause

     # File lib/sequel/adapters/shared/postgres.rb
2501 def update_table_sql(sql)
2502   sql << ' '
2503   source_list_append(sql, @opts[:from][0..0])
2504 end