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
1661 def analyze
1662   explain(:analyze=>true)
1663 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
1668 def complex_expression_sql_append(sql, op, args)
1669   case op
1670   when :^
1671     j = ' # '
1672     c = false
1673     args.each do |a|
1674       sql << j if c
1675       literal_append(sql, a)
1676       c ||= true
1677     end
1678   when :ILIKE, :'NOT ILIKE'
1679     sql << '('
1680     literal_append(sql, args[0])
1681     sql << ' ' << op.to_s << ' '
1682     literal_append(sql, args[1])
1683     sql << " ESCAPE "
1684     literal_append(sql, "\\")
1685     sql << ')'
1686   else
1687     super
1688   end
1689 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
1705 def disable_insert_returning
1706   clone(:disable_insert_returning=>true)
1707 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
1710 def explain(opts=OPTS)
1711   with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n")
1712 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
1715 def for_share
1716   lock_style(:share)
1717 end
insert(*values) click to toggle source

Insert given values into the database.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1778 def insert(*values)
1779   if @opts[:returning]
1780     # Already know which columns to return, let the standard code handle it
1781     super
1782   elsif @opts[:sql] || @opts[:disable_insert_returning]
1783     # Raw SQL used or RETURNING disabled, just use the default behavior
1784     # and return nil since sequence is not known.
1785     super
1786     nil
1787   else
1788     # Force the use of RETURNING with the primary key value,
1789     # unless it has been disabled.
1790     returning(insert_pk).insert(*values){|r| return r.values.first}
1791   end
1792 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
1829 def insert_conflict(opts=OPTS)
1830   clone(:insert_conflict => opts)
1831 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
1839 def insert_ignore
1840   insert_conflict
1841 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
1846 def insert_select(*values)
1847   return unless supports_insert_select?
1848   # Handle case where query does not return a row
1849   server?(:default).with_sql_first(insert_select_sql(*values)) || false
1850 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
1854 def insert_select_sql(*values)
1855   ds = opts[:returning] ? self : returning
1856   ds.insert_sql(*values)
1857 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
1861 def join_table(type, table, expr=nil, options=OPTS, &block)
1862   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
1863     options = options.merge(:join_using=>true)
1864   end
1865   super
1866 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
1873 def lock(mode, opts=OPTS)
1874   if defined?(yield) # perform locking inside a transaction and yield to block
1875     @db.transaction(opts){lock(mode, opts); yield}
1876   else
1877     sql = 'LOCK TABLE '.dup
1878     source_list_append(sql, @opts[:from])
1879     mode = mode.to_s.upcase.strip
1880     unless LOCK_MODES.include?(mode)
1881       raise Error, "Unsupported lock mode: #{mode}"
1882     end
1883     sql << " IN #{mode} MODE"
1884     @db.execute(sql, opts)
1885   end
1886   nil
1887 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
1898 def merge_do_nothing_when_matched(&block)
1899   _merge_when(:type=>:matched, &block)
1900 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
1911 def merge_do_nothing_when_not_matched(&block)
1912   _merge_when(:type=>:not_matched, &block)
1913 end
merge_insert(*values, &block) click to toggle source

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
1916 def merge_insert(*values, &block)
1917   h = {:type=>:insert, :values=>values}
1918   if override = @opts[:override]
1919     h[:override] = insert_override_sql(String.new)
1920   end
1921   _merge_when(h, &block)
1922 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
1927 def overriding_system_value
1928   clone(:override=>:system)
1929 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
1933 def overriding_user_value
1934   clone(:override=>:user)
1935 end
supports_cte?(type=:select) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
1937 def supports_cte?(type=:select)
1938   if type == :select
1939     server_version >= 80400
1940   else
1941     server_version >= 90100
1942   end
1943 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
1947 def supports_cte_in_subqueries?
1948   supports_cte?
1949 end
supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

     # File lib/sequel/adapters/shared/postgres.rb
1952 def supports_distinct_on?
1953   true
1954 end
supports_group_cube?() click to toggle source

PostgreSQL 9.5+ supports GROUP CUBE

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

PostgreSQL 9.5+ supports GROUP ROLLUP

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

PostgreSQL 9.5+ supports GROUPING SETS

     # File lib/sequel/adapters/shared/postgres.rb
1967 def supports_grouping_sets?
1968   server_version >= 90500
1969 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
1977 def supports_insert_conflict?
1978   server_version >= 90500
1979 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
1972 def supports_insert_select?
1973   !@opts[:disable_insert_returning]
1974 end
supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

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

PostgreSQL 15+ supports MERGE.

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

PostgreSQL supports modifying joined datasets

     # File lib/sequel/adapters/shared/postgres.rb
1987 def supports_modifying_joins?
1988   true
1989 end
supports_nowait?() click to toggle source

PostgreSQL supports NOWAIT.

     # File lib/sequel/adapters/shared/postgres.rb
1997 def supports_nowait?
1998   true
1999 end
supports_regexp?() click to toggle source

PostgreSQL supports pattern matching via regular expressions

     # File lib/sequel/adapters/shared/postgres.rb
2007 def supports_regexp?
2008   true
2009 end
supports_returning?(type) click to toggle source

Returning is always supported.

     # File lib/sequel/adapters/shared/postgres.rb
2002 def supports_returning?(type)
2003   true
2004 end
supports_skip_locked?() click to toggle source

PostgreSQL 9.5+ supports SKIP LOCKED.

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

PostgreSQL supports timezones in literal timestamps

     # File lib/sequel/adapters/shared/postgres.rb
2017 def supports_timestamp_timezones?
2018   true
2019 end
supports_window_clause?() click to toggle source

PostgreSQL 8.4+ supports WINDOW clause.

     # File lib/sequel/adapters/shared/postgres.rb
2022 def supports_window_clause?
2023   server_version >= 80400
2024 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
2033 def supports_window_function_frame_option?(option)
2034   case option
2035   when :rows, :range
2036     true
2037   when :offset
2038     server_version >= 90000
2039   when :groups, :exclude
2040     server_version >= 110000
2041   else
2042     false
2043   end
2044 end
supports_window_functions?() click to toggle source

PostgreSQL 8.4+ supports window functions

     # File lib/sequel/adapters/shared/postgres.rb
2027 def supports_window_functions?
2028   server_version >= 80400
2029 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
2062 def truncate(opts = OPTS)
2063   if opts.empty?
2064     super()
2065   else
2066     clone(:truncate_opts=>opts).truncate
2067   end
2068 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
2073 def with_ties
2074   clone(:limit_with_ties=>true)
2075 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
2083 def _import(columns, values, opts=OPTS)
2084   if @opts[:returning]
2085     # no transaction: our multi_insert_sql_strategy should guarantee
2086     # that there's only ever a single statement.
2087     sql = multi_insert_sql(columns, values)[0]
2088     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
2089   elsif opts[:return] == :primary_key
2090     returning(insert_pk)._import(columns, values, opts)
2091   else
2092     super
2093   end
2094 end
to_prepared_statement(type, *a) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2096 def to_prepared_statement(type, *a)
2097   if type == :insert && !@opts.has_key?(:returning)
2098     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
2099   else
2100     super
2101   end
2102 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
2107 def _merge_insert_sql(sql, data)
2108   sql << " THEN INSERT "
2109   columns, values = _parse_insert_sql_args(data[:values])
2110   _insert_columns_sql(sql, columns)
2111   if override = data[:override]
2112     sql << override
2113   end
2114   _insert_values_sql(sql, values)
2115 end
_merge_matched_sql(sql, data) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2117 def _merge_matched_sql(sql, data)
2118   sql << " THEN DO NOTHING"
2119 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
2123 def _truncate_sql(table)
2124   to = @opts[:truncate_opts] || OPTS
2125   "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}"
2126 end
check_truncation_allowed!() click to toggle source

Allow truncation of multiple source tables.

     # File lib/sequel/adapters/shared/postgres.rb
2129 def check_truncation_allowed!
2130   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
2131   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
2132 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
2290 def compound_dataset_sql_append(sql, ds)
2291   sql << '('
2292   super
2293   sql << ')'
2294 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
2135 def delete_from_sql(sql)
2136   sql << ' FROM '
2137   source_list_append(sql, @opts[:from][0..0])
2138 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
2141 def delete_using_sql(sql)
2142   join_from_sql(:USING, sql)
2143 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
2414 def full_text_string_join(cols)
2415   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
2416   cols = cols.zip([' '] * cols.length).flatten
2417   cols.pop
2418   SQL::StringExpression.new(:'||', *cols)
2419 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
2146 def insert_conflict_sql(sql)
2147   if opts = @opts[:insert_conflict]
2148     sql << " ON CONFLICT"
2149 
2150     if target = opts[:constraint] 
2151       sql << " ON CONSTRAINT "
2152       identifier_append(sql, target)
2153     elsif target = opts[:target]
2154       sql << ' '
2155       identifier_append(sql, Array(target))
2156       if conflict_where = opts[:conflict_where]
2157         sql << " WHERE "
2158         literal_append(sql, conflict_where)
2159       end
2160     end
2161 
2162     if values = opts[:update]
2163       sql << " DO UPDATE SET "
2164       update_sql_values_hash(sql, values)
2165       if update_where = opts[:update_where]
2166         sql << " WHERE "
2167         literal_append(sql, update_where)
2168       end
2169     else
2170       sql << " DO NOTHING"
2171     end
2172   end
2173 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
2176 def insert_into_sql(sql)
2177   sql << " INTO "
2178   if (f = @opts[:from]) && f.length == 1
2179     identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first))
2180   else
2181     source_list_append(sql, f)
2182   end
2183 end
insert_override_sql(sql) click to toggle source

Support OVERRIDING SYSTEM|USER VALUE in insert statements

     # File lib/sequel/adapters/shared/postgres.rb
2197 def insert_override_sql(sql)
2198   case opts[:override]
2199   when :system
2200     sql << " OVERRIDING SYSTEM VALUE"
2201   when :user
2202     sql << " OVERRIDING USER VALUE"
2203   end
2204 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
2186 def insert_pk
2187   (f = opts[:from]) && !f.empty? && (t = f.first)
2188   case t
2189   when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
2190     if pk = db.primary_key(t)
2191       Sequel::SQL::Identifier.new(pk)
2192     end
2193   end
2194 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
2208 def join_from_sql(type, sql)
2209   if(from = @opts[:from][1..-1]).empty?
2210     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
2211   else
2212     sql << ' ' << type.to_s << ' '
2213     source_list_append(sql, from)
2214     select_join_sql(sql)
2215   end
2216 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
2219 def join_using_clause_using_sql_append(sql, using_columns)
2220   if using_columns.is_a?(SQL::AliasedExpression)
2221     super(sql, using_columns.expression)
2222     sql << ' AS '
2223     identifier_append(sql, using_columns.alias)
2224   else
2225     super
2226   end
2227 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
2230 def literal_blob_append(sql, v)
2231   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
2232 end
literal_false() click to toggle source

PostgreSQL uses FALSE for false values

     # File lib/sequel/adapters/shared/postgres.rb
2235 def literal_false
2236   'false'
2237 end
literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2240 def literal_float(value)
2241   if value.finite?
2242     super
2243   elsif value.nan?
2244     "'NaN'"
2245   elsif value.infinite? == 1
2246     "'Infinity'"
2247   else
2248     "'-Infinity'"
2249   end
2250 end
literal_integer(v) click to toggle source

Handle Ruby integers outside PostgreSQL bigint range specially.

     # File lib/sequel/adapters/shared/postgres.rb
2253 def literal_integer(v)
2254   if v > 9223372036854775807 || v < -9223372036854775808
2255     literal_integer_outside_bigint_range(v)
2256   else
2257     v.to_s
2258   end
2259 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
2264 def literal_integer_outside_bigint_range(v)
2265   raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}"
2266 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
2269 def literal_string_append(sql, v)
2270   sql << "'" << v.gsub("'", "''") << "'"
2271 end
literal_true() click to toggle source

PostgreSQL uses true for true values

     # File lib/sequel/adapters/shared/postgres.rb
2274 def literal_true
2275   'true'
2276 end
multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2279 def multi_insert_sql_strategy
2280   :values
2281 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
2284 def non_sql_option?(key)
2285   super || key == :cursor || key == :insert_conflict
2286 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
2298 def requires_like_escape?
2299   false
2300 end
select_limit_sql(sql) click to toggle source

Support FETCH FIRST WITH TIES on PostgreSQL 13+.

     # File lib/sequel/adapters/shared/postgres.rb
2303 def select_limit_sql(sql)
2304   l = @opts[:limit]
2305   o = @opts[:offset]
2306 
2307   return unless l || o
2308 
2309   if @opts[:limit_with_ties]
2310     if o
2311       sql << " OFFSET "
2312       literal_append(sql, o)
2313     end
2314 
2315     if l
2316       sql << " FETCH FIRST "
2317       literal_append(sql, l)
2318       sql << " ROWS WITH TIES"
2319     end
2320   else
2321     if l
2322       sql << " LIMIT "
2323       literal_append(sql, l)
2324     end
2325 
2326     if o
2327       sql << " OFFSET "
2328       literal_append(sql, o)
2329     end
2330   end
2331 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
2335 def select_lock_sql(sql)
2336   lock = @opts[:lock]
2337   if lock == :share
2338     sql << ' FOR SHARE'
2339   else
2340     super
2341   end
2342 
2343   if lock
2344     if @opts[:skip_locked]
2345       sql << " SKIP LOCKED"
2346     elsif @opts[:nowait]
2347       sql << " NOWAIT"
2348     end
2349   end
2350 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
2353 def select_values_sql(sql)
2354   sql << "VALUES "
2355   expression_list_append(sql, opts[:values])
2356 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
2359 def select_with_sql_base
2360   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
2361 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
2364 def select_with_sql_cte(sql, cte)
2365   super
2366   select_with_sql_cte_search_cycle(sql, cte)
2367 end
select_with_sql_cte_search_cycle(sql, cte) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2369 def select_with_sql_cte_search_cycle(sql, cte)
2370   if search_opts = cte[:search]
2371     sql << if search_opts[:type] == :breadth
2372       " SEARCH BREADTH FIRST BY "
2373     else
2374       " SEARCH DEPTH FIRST BY "
2375     end
2376 
2377     identifier_list_append(sql, Array(search_opts[:by]))
2378     sql << " SET "
2379     identifier_append(sql, search_opts[:set] || :ordercol)
2380   end
2381 
2382   if cycle_opts = cte[:cycle]
2383     sql << " CYCLE "
2384     identifier_list_append(sql, Array(cycle_opts[:columns]))
2385     sql << " SET "
2386     identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle)
2387     if cycle_opts.has_key?(:cycle_value)
2388       sql << " TO "
2389       literal_append(sql, cycle_opts[:cycle_value])
2390       sql << " DEFAULT "
2391       literal_append(sql, cycle_opts.fetch(:noncycle_value, false))
2392     end
2393     sql << " USING "
2394     identifier_append(sql, cycle_opts[:path_column] || :path)
2395   end
2396 end
server_version() click to toggle source

The version of the database server

     # File lib/sequel/adapters/shared/postgres.rb
2399 def server_version
2400   db.server_version(@opts[:server])
2401 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
2404 def supports_filtered_aggregates?
2405   server_version >= 90400
2406 end
supports_quoted_function_names?() click to toggle source

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
2409 def supports_quoted_function_names?
2410   true
2411 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
2422 def update_from_sql(sql)
2423   join_from_sql(:FROM, sql)
2424 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
2427 def update_table_sql(sql)
2428   sql << ' '
2429   source_list_append(sql, @opts[:from][0..0])
2430 end