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
1849 def insert(*values)
1850   if @opts[:returning]
1851     # Already know which columns to return, let the standard code handle it
1852     super
1853   elsif @opts[:sql] || @opts[:disable_insert_returning]
1854     # Raw SQL used or RETURNING disabled, just use the default behavior
1855     # and return nil since sequence is not known.
1856     super
1857     nil
1858   else
1859     # Force the use of RETURNING with the primary key value,
1860     # unless it has been disabled.
1861     returning(insert_pk).insert(*values){|r| return r.values.first}
1862   end
1863 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
1900 def insert_conflict(opts=OPTS)
1901   clone(:insert_conflict => opts)
1902 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
1910 def insert_ignore
1911   insert_conflict
1912 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
1917 def insert_select(*values)
1918   return unless supports_insert_select?
1919   # Handle case where query does not return a row
1920   server?(:default).with_sql_first(insert_select_sql(*values)) || false
1921 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
1925 def insert_select_sql(*values)
1926   ds = opts[:returning] ? self : returning
1927   ds.insert_sql(*values)
1928 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
1932 def join_table(type, table, expr=nil, options=OPTS, &block)
1933   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
1934     options = options.merge(:join_using=>true)
1935   end
1936   super
1937 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
1944 def lock(mode, opts=OPTS)
1945   if defined?(yield) # perform locking inside a transaction and yield to block
1946     @db.transaction(opts){lock(mode, opts); yield}
1947   else
1948     sql = 'LOCK TABLE '.dup
1949     source_list_append(sql, @opts[:from])
1950     mode = mode.to_s.upcase.strip
1951     unless LOCK_MODES.include?(mode)
1952       raise Error, "Unsupported lock mode: #{mode}"
1953     end
1954     sql << " IN #{mode} MODE"
1955     @db.execute(sql, opts)
1956   end
1957   nil
1958 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
1969 def merge_do_nothing_when_matched(&block)
1970   _merge_when(:type=>:matched, &block)
1971 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
1982 def merge_do_nothing_when_not_matched(&block)
1983   _merge_when(:type=>:not_matched, &block)
1984 end
merge_insert(*values, &block) click to toggle source

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
1987 def merge_insert(*values, &block)
1988   h = {:type=>:insert, :values=>values}
1989   if override = @opts[:override]
1990     h[:override] = insert_override_sql(String.new)
1991   end
1992   _merge_when(h, &block)
1993 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
1998 def overriding_system_value
1999   clone(:override=>:system)
2000 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
2004 def overriding_user_value
2005   clone(:override=>:user)
2006 end
supports_cte?(type=:select) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2008 def supports_cte?(type=:select)
2009   if type == :select
2010     server_version >= 80400
2011   else
2012     server_version >= 90100
2013   end
2014 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
2018 def supports_cte_in_subqueries?
2019   supports_cte?
2020 end
supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

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

PostgreSQL 9.5+ supports GROUP CUBE

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

PostgreSQL 9.5+ supports GROUP ROLLUP

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

PostgreSQL 9.5+ supports GROUPING SETS

     # File lib/sequel/adapters/shared/postgres.rb
2038 def supports_grouping_sets?
2039   server_version >= 90500
2040 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
2048 def supports_insert_conflict?
2049   server_version >= 90500
2050 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
2043 def supports_insert_select?
2044   !@opts[:disable_insert_returning]
2045 end
supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

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

PostgreSQL 15+ supports MERGE.

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

PostgreSQL supports modifying joined datasets

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

PostgreSQL supports NOWAIT.

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

PostgreSQL supports pattern matching via regular expressions

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

Returning is always supported.

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

PostgreSQL 9.5+ supports SKIP LOCKED.

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

PostgreSQL supports timezones in literal timestamps

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

PostgreSQL 8.4+ supports WINDOW clause.

     # File lib/sequel/adapters/shared/postgres.rb
2093 def supports_window_clause?
2094   server_version >= 80400
2095 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
2104 def supports_window_function_frame_option?(option)
2105   case option
2106   when :rows, :range
2107     true
2108   when :offset
2109     server_version >= 90000
2110   when :groups, :exclude
2111     server_version >= 110000
2112   else
2113     false
2114   end
2115 end
supports_window_functions?() click to toggle source

PostgreSQL 8.4+ supports window functions

     # File lib/sequel/adapters/shared/postgres.rb
2098 def supports_window_functions?
2099   server_version >= 80400
2100 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
2133 def truncate(opts = OPTS)
2134   if opts.empty?
2135     super()
2136   else
2137     clone(:truncate_opts=>opts).truncate
2138   end
2139 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
2144 def with_ties
2145   clone(:limit_with_ties=>true)
2146 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
2154 def _import(columns, values, opts=OPTS)
2155   if @opts[:returning]
2156     # no transaction: our multi_insert_sql_strategy should guarantee
2157     # that there's only ever a single statement.
2158     sql = multi_insert_sql(columns, values)[0]
2159     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
2160   elsif opts[:return] == :primary_key
2161     returning(insert_pk)._import(columns, values, opts)
2162   else
2163     super
2164   end
2165 end
to_prepared_statement(type, *a) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2167 def to_prepared_statement(type, *a)
2168   if type == :insert && !@opts.has_key?(:returning)
2169     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
2170   else
2171     super
2172   end
2173 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
2178 def _merge_insert_sql(sql, data)
2179   sql << " THEN INSERT "
2180   columns, values = _parse_insert_sql_args(data[:values])
2181   _insert_columns_sql(sql, columns)
2182   if override = data[:override]
2183     sql << override
2184   end
2185   _insert_values_sql(sql, values)
2186 end
_merge_matched_sql(sql, data) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2188 def _merge_matched_sql(sql, data)
2189   sql << " THEN DO NOTHING"
2190 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
2194 def _truncate_sql(table)
2195   to = @opts[:truncate_opts] || OPTS
2196   "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}"
2197 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
2200 def aggreate_dataset_use_from_self?
2201   super || @opts[:values]
2202 end
check_truncation_allowed!() click to toggle source

Allow truncation of multiple source tables.

     # File lib/sequel/adapters/shared/postgres.rb
2205 def check_truncation_allowed!
2206   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
2207   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
2208 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
2366 def compound_dataset_sql_append(sql, ds)
2367   sql << '('
2368   super
2369   sql << ')'
2370 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
2211 def delete_from_sql(sql)
2212   sql << ' FROM '
2213   source_list_append(sql, @opts[:from][0..0])
2214 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
2217 def delete_using_sql(sql)
2218   join_from_sql(:USING, sql)
2219 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
2490 def full_text_string_join(cols)
2491   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
2492   cols = cols.zip([' '] * cols.length).flatten
2493   cols.pop
2494   SQL::StringExpression.new(:'||', *cols)
2495 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
2222 def insert_conflict_sql(sql)
2223   if opts = @opts[:insert_conflict]
2224     sql << " ON CONFLICT"
2225 
2226     if target = opts[:constraint] 
2227       sql << " ON CONSTRAINT "
2228       identifier_append(sql, target)
2229     elsif target = opts[:target]
2230       sql << ' '
2231       identifier_append(sql, Array(target))
2232       if conflict_where = opts[:conflict_where]
2233         sql << " WHERE "
2234         literal_append(sql, conflict_where)
2235       end
2236     end
2237 
2238     if values = opts[:update]
2239       sql << " DO UPDATE SET "
2240       update_sql_values_hash(sql, values)
2241       if update_where = opts[:update_where]
2242         sql << " WHERE "
2243         literal_append(sql, update_where)
2244       end
2245     else
2246       sql << " DO NOTHING"
2247     end
2248   end
2249 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
2252 def insert_into_sql(sql)
2253   sql << " INTO "
2254   if (f = @opts[:from]) && f.length == 1
2255     identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first))
2256   else
2257     source_list_append(sql, f)
2258   end
2259 end
insert_override_sql(sql) click to toggle source

Support OVERRIDING SYSTEM|USER VALUE in insert statements

     # File lib/sequel/adapters/shared/postgres.rb
2273 def insert_override_sql(sql)
2274   case opts[:override]
2275   when :system
2276     sql << " OVERRIDING SYSTEM VALUE"
2277   when :user
2278     sql << " OVERRIDING USER VALUE"
2279   end
2280 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
2262 def insert_pk
2263   (f = opts[:from]) && !f.empty? && (t = f.first)
2264   case t
2265   when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
2266     if pk = db.primary_key(t)
2267       Sequel::SQL::Identifier.new(pk)
2268     end
2269   end
2270 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
2284 def join_from_sql(type, sql)
2285   if(from = @opts[:from][1..-1]).empty?
2286     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
2287   else
2288     sql << ' ' << type.to_s << ' '
2289     source_list_append(sql, from)
2290     select_join_sql(sql)
2291   end
2292 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
2295 def join_using_clause_using_sql_append(sql, using_columns)
2296   if using_columns.is_a?(SQL::AliasedExpression)
2297     super(sql, using_columns.expression)
2298     sql << ' AS '
2299     identifier_append(sql, using_columns.alias)
2300   else
2301     super
2302   end
2303 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
2306 def literal_blob_append(sql, v)
2307   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
2308 end
literal_false() click to toggle source

PostgreSQL uses FALSE for false values

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

PostgreSQL quotes NaN and Infinity.

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

Handle Ruby integers outside PostgreSQL bigint range specially.

     # File lib/sequel/adapters/shared/postgres.rb
2329 def literal_integer(v)
2330   if v > 9223372036854775807 || v < -9223372036854775808
2331     literal_integer_outside_bigint_range(v)
2332   else
2333     v.to_s
2334   end
2335 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
2340 def literal_integer_outside_bigint_range(v)
2341   raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}"
2342 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
2345 def literal_string_append(sql, v)
2346   sql << "'" << v.gsub("'", "''") << "'"
2347 end
literal_true() click to toggle source

PostgreSQL uses true for true values

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

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2355 def multi_insert_sql_strategy
2356   :values
2357 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
2360 def non_sql_option?(key)
2361   super || key == :cursor || key == :insert_conflict
2362 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
2374 def requires_like_escape?
2375   false
2376 end
select_limit_sql(sql) click to toggle source

Support FETCH FIRST WITH TIES on PostgreSQL 13+.

     # File lib/sequel/adapters/shared/postgres.rb
2379 def select_limit_sql(sql)
2380   l = @opts[:limit]
2381   o = @opts[:offset]
2382 
2383   return unless l || o
2384 
2385   if @opts[:limit_with_ties]
2386     if o
2387       sql << " OFFSET "
2388       literal_append(sql, o)
2389     end
2390 
2391     if l
2392       sql << " FETCH FIRST "
2393       literal_append(sql, l)
2394       sql << " ROWS WITH TIES"
2395     end
2396   else
2397     if l
2398       sql << " LIMIT "
2399       literal_append(sql, l)
2400     end
2401 
2402     if o
2403       sql << " OFFSET "
2404       literal_append(sql, o)
2405     end
2406   end
2407 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
2411 def select_lock_sql(sql)
2412   lock = @opts[:lock]
2413   if lock == :share
2414     sql << ' FOR SHARE'
2415   else
2416     super
2417   end
2418 
2419   if lock
2420     if @opts[:skip_locked]
2421       sql << " SKIP LOCKED"
2422     elsif @opts[:nowait]
2423       sql << " NOWAIT"
2424     end
2425   end
2426 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
2429 def select_values_sql(sql)
2430   sql << "VALUES "
2431   expression_list_append(sql, opts[:values])
2432 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
2435 def select_with_sql_base
2436   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
2437 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
2440 def select_with_sql_cte(sql, cte)
2441   super
2442   select_with_sql_cte_search_cycle(sql, cte)
2443 end
select_with_sql_cte_search_cycle(sql, cte) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2445 def select_with_sql_cte_search_cycle(sql, cte)
2446   if search_opts = cte[:search]
2447     sql << if search_opts[:type] == :breadth
2448       " SEARCH BREADTH FIRST BY "
2449     else
2450       " SEARCH DEPTH FIRST BY "
2451     end
2452 
2453     identifier_list_append(sql, Array(search_opts[:by]))
2454     sql << " SET "
2455     identifier_append(sql, search_opts[:set] || :ordercol)
2456   end
2457 
2458   if cycle_opts = cte[:cycle]
2459     sql << " CYCLE "
2460     identifier_list_append(sql, Array(cycle_opts[:columns]))
2461     sql << " SET "
2462     identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle)
2463     if cycle_opts.has_key?(:cycle_value)
2464       sql << " TO "
2465       literal_append(sql, cycle_opts[:cycle_value])
2466       sql << " DEFAULT "
2467       literal_append(sql, cycle_opts.fetch(:noncycle_value, false))
2468     end
2469     sql << " USING "
2470     identifier_append(sql, cycle_opts[:path_column] || :path)
2471   end
2472 end
server_version() click to toggle source

The version of the database server

     # File lib/sequel/adapters/shared/postgres.rb
2475 def server_version
2476   db.server_version(@opts[:server])
2477 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
2480 def supports_filtered_aggregates?
2481   server_version >= 90400
2482 end
supports_quoted_function_names?() click to toggle source

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
2485 def supports_quoted_function_names?
2486   true
2487 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
2498 def update_from_sql(sql)
2499   join_from_sql(:FROM, sql)
2500 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
2503 def update_table_sql(sql)
2504   sql << ' '
2505   source_list_append(sql, @opts[:from][0..0])
2506 end