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
1792 def analyze
1793   explain(:analyze=>true)
1794 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
1799 def complex_expression_sql_append(sql, op, args)
1800   case op
1801   when :^
1802     j = ' # '
1803     c = false
1804     args.each do |a|
1805       sql << j if c
1806       literal_append(sql, a)
1807       c ||= true
1808     end
1809   when :ILIKE, :'NOT ILIKE'
1810     sql << '('
1811     literal_append(sql, args[0])
1812     sql << ' ' << op.to_s << ' '
1813     literal_append(sql, args[1])
1814     sql << ')'
1815   else
1816     super
1817   end
1818 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
1834 def disable_insert_returning
1835   clone(:disable_insert_returning=>true)
1836 end
empty?() click to toggle source

Always return false when using VALUES

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1839 def empty?
1840   return false if @opts[:values]
1841   super
1842 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
1845 def explain(opts=OPTS)
1846   with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n")
1847 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
1850 def for_share
1851   lock_style(:share)
1852 end
insert(*values) click to toggle source

Insert given values into the database.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1915 def insert(*values)
1916   if @opts[:returning]
1917     # Already know which columns to return, let the standard code handle it
1918     super
1919   elsif @opts[:sql] || @opts[:disable_insert_returning]
1920     # Raw SQL used or RETURNING disabled, just use the default behavior
1921     # and return nil since sequence is not known.
1922     super
1923     nil
1924   else
1925     # Force the use of RETURNING with the primary key value,
1926     # unless it has been disabled.
1927     returning(insert_pk).insert(*values){|r| return r.values.first}
1928   end
1929 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
1966 def insert_conflict(opts=OPTS)
1967   clone(:insert_conflict => opts)
1968 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
1976 def insert_ignore
1977   insert_conflict
1978 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
1983 def insert_select(*values)
1984   return unless supports_insert_select?
1985   # Handle case where query does not return a row
1986   server?(:default).with_sql_first(insert_select_sql(*values)) || false
1987 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
1991 def insert_select_sql(*values)
1992   ds = opts[:returning] ? self : returning
1993   ds.insert_sql(*values)
1994 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
1998 def join_table(type, table, expr=nil, options=OPTS, &block)
1999   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
2000     options = options.merge(:join_using=>true)
2001   end
2002   super
2003 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
2010 def lock(mode, opts=OPTS)
2011   if defined?(yield) # perform locking inside a transaction and yield to block
2012     @db.transaction(opts){lock(mode, opts); yield}
2013   else
2014     sql = 'LOCK TABLE '.dup
2015     source_list_append(sql, @opts[:from])
2016     mode = mode.to_s.upcase.strip
2017     unless LOCK_MODES.include?(mode)
2018       raise Error, "Unsupported lock mode: #{mode}"
2019     end
2020     sql << " IN #{mode} MODE"
2021     @db.execute(sql, opts)
2022   end
2023   nil
2024 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
2035 def merge_do_nothing_when_matched(&block)
2036   _merge_when(:type=>:matched, &block)
2037 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
2048 def merge_do_nothing_when_not_matched(&block)
2049   _merge_when(:type=>:not_matched, &block)
2050 end
merge_insert(*values, &block) click to toggle source

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2053 def merge_insert(*values, &block)
2054   h = {:type=>:insert, :values=>values}
2055   if override = @opts[:override]
2056     h[:override] = insert_override_sql(String.new)
2057   end
2058   _merge_when(h, &block)
2059 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
2064 def overriding_system_value
2065   clone(:override=>:system)
2066 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
2070 def overriding_user_value
2071   clone(:override=>:user)
2072 end
supports_cte?(type=:select) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2074 def supports_cte?(type=:select)
2075   if type == :select
2076     server_version >= 80400
2077   else
2078     server_version >= 90100
2079   end
2080 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
2084 def supports_cte_in_subqueries?
2085   supports_cte?
2086 end
supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

     # File lib/sequel/adapters/shared/postgres.rb
2089 def supports_distinct_on?
2090   true
2091 end
supports_group_cube?() click to toggle source

PostgreSQL 9.5+ supports GROUP CUBE

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

PostgreSQL 9.5+ supports GROUP ROLLUP

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

PostgreSQL 9.5+ supports GROUPING SETS

     # File lib/sequel/adapters/shared/postgres.rb
2104 def supports_grouping_sets?
2105   server_version >= 90500
2106 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
2114 def supports_insert_conflict?
2115   server_version >= 90500
2116 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
2109 def supports_insert_select?
2110   !@opts[:disable_insert_returning]
2111 end
supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

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

PostgreSQL 15+ supports MERGE.

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

PostgreSQL supports modifying joined datasets

     # File lib/sequel/adapters/shared/postgres.rb
2124 def supports_modifying_joins?
2125   true
2126 end
supports_nowait?() click to toggle source

PostgreSQL supports NOWAIT.

     # File lib/sequel/adapters/shared/postgres.rb
2134 def supports_nowait?
2135   true
2136 end
supports_regexp?() click to toggle source

PostgreSQL supports pattern matching via regular expressions

     # File lib/sequel/adapters/shared/postgres.rb
2144 def supports_regexp?
2145   true
2146 end
supports_returning?(type) click to toggle source

Returning is always supported.

     # File lib/sequel/adapters/shared/postgres.rb
2139 def supports_returning?(type)
2140   true
2141 end
supports_skip_locked?() click to toggle source

PostgreSQL 9.5+ supports SKIP LOCKED.

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

PostgreSQL supports timezones in literal timestamps

     # File lib/sequel/adapters/shared/postgres.rb
2156 def supports_timestamp_timezones?
2157   # SEQUEL6: Remove
2158   true
2159 end
supports_window_clause?() click to toggle source

PostgreSQL 8.4+ supports WINDOW clause.

     # File lib/sequel/adapters/shared/postgres.rb
2163 def supports_window_clause?
2164   server_version >= 80400
2165 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
2174 def supports_window_function_frame_option?(option)
2175   case option
2176   when :rows, :range
2177     true
2178   when :offset
2179     server_version >= 90000
2180   when :groups, :exclude
2181     server_version >= 110000
2182   else
2183     false
2184   end
2185 end
supports_window_functions?() click to toggle source

PostgreSQL 8.4+ supports window functions

     # File lib/sequel/adapters/shared/postgres.rb
2168 def supports_window_functions?
2169   server_version >= 80400
2170 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
2203 def truncate(opts = OPTS)
2204   if opts.empty?
2205     super()
2206   else
2207     clone(:truncate_opts=>opts).truncate
2208   end
2209 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
2214 def with_ties
2215   clone(:limit_with_ties=>true)
2216 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
2224 def _import(columns, values, opts=OPTS)
2225   if @opts[:returning]
2226     # no transaction: our multi_insert_sql_strategy should guarantee
2227     # that there's only ever a single statement.
2228     sql = multi_insert_sql(columns, values)[0]
2229     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
2230   elsif opts[:return] == :primary_key
2231     returning(insert_pk)._import(columns, values, opts)
2232   else
2233     super
2234   end
2235 end
to_prepared_statement(type, *a) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2237 def to_prepared_statement(type, *a)
2238   if type == :insert && !@opts.has_key?(:returning)
2239     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
2240   else
2241     super
2242   end
2243 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
2248 def _merge_insert_sql(sql, data)
2249   sql << " THEN INSERT "
2250   columns, values = _parse_insert_sql_args(data[:values])
2251   _insert_columns_sql(sql, columns)
2252   if override = data[:override]
2253     sql << override
2254   end
2255   _insert_values_sql(sql, values)
2256 end
_merge_matched_sql(sql, data) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2258 def _merge_matched_sql(sql, data)
2259   sql << " THEN DO NOTHING"
2260 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
2264 def _truncate_sql(table)
2265   to = @opts[:truncate_opts] || OPTS
2266   "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}"
2267 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
2270 def aggreate_dataset_use_from_self?
2271   super || @opts[:values]
2272 end
check_truncation_allowed!() click to toggle source

Allow truncation of multiple source tables.

     # File lib/sequel/adapters/shared/postgres.rb
2275 def check_truncation_allowed!
2276   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
2277   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
2278 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
2441 def compound_dataset_sql_append(sql, ds)
2442   sql << '('
2443   super
2444   sql << ')'
2445 end
default_timestamp_format() click to toggle source

The strftime format to use when literalizing the time.

     # File lib/sequel/adapters/shared/postgres.rb
2281 def default_timestamp_format
2282   "'%Y-%m-%d %H:%M:%S.%6N%z'"
2283 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
2286 def delete_from_sql(sql)
2287   sql << ' FROM '
2288   source_list_append(sql, @opts[:from][0..0])
2289 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
2292 def delete_using_sql(sql)
2293   join_from_sql(:USING, sql)
2294 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
2565 def full_text_string_join(cols)
2566   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
2567   cols = cols.zip([' '] * cols.length).flatten
2568   cols.pop
2569   SQL::StringExpression.new(:'||', *cols)
2570 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
2297 def insert_conflict_sql(sql)
2298   if opts = @opts[:insert_conflict]
2299     sql << " ON CONFLICT"
2300 
2301     if target = opts[:constraint] 
2302       sql << " ON CONSTRAINT "
2303       identifier_append(sql, target)
2304     elsif target = opts[:target]
2305       sql << ' '
2306       identifier_append(sql, Array(target))
2307       if conflict_where = opts[:conflict_where]
2308         sql << " WHERE "
2309         literal_append(sql, conflict_where)
2310       end
2311     end
2312 
2313     if values = opts[:update]
2314       sql << " DO UPDATE SET "
2315       update_sql_values_hash(sql, values)
2316       if update_where = opts[:update_where]
2317         sql << " WHERE "
2318         literal_append(sql, update_where)
2319       end
2320     else
2321       sql << " DO NOTHING"
2322     end
2323   end
2324 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
2327 def insert_into_sql(sql)
2328   sql << " INTO "
2329   if (f = @opts[:from]) && f.length == 1
2330     identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first))
2331   else
2332     source_list_append(sql, f)
2333   end
2334 end
insert_override_sql(sql) click to toggle source

Support OVERRIDING SYSTEM|USER VALUE in insert statements

     # File lib/sequel/adapters/shared/postgres.rb
2348 def insert_override_sql(sql)
2349   case opts[:override]
2350   when :system
2351     sql << " OVERRIDING SYSTEM VALUE"
2352   when :user
2353     sql << " OVERRIDING USER VALUE"
2354   end
2355 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
2337 def insert_pk
2338   (f = opts[:from]) && !f.empty? && (t = f.first)
2339   case t
2340   when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
2341     if pk = db.primary_key(t)
2342       Sequel::SQL::Identifier.new(pk)
2343     end
2344   end
2345 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
2359 def join_from_sql(type, sql)
2360   if(from = @opts[:from][1..-1]).empty?
2361     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
2362   else
2363     sql << ' ' << type.to_s << ' '
2364     source_list_append(sql, from)
2365     select_join_sql(sql)
2366   end
2367 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
2370 def join_using_clause_using_sql_append(sql, using_columns)
2371   if using_columns.is_a?(SQL::AliasedExpression)
2372     super(sql, using_columns.expression)
2373     sql << ' AS '
2374     identifier_append(sql, using_columns.alias)
2375   else
2376     super
2377   end
2378 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
2381 def literal_blob_append(sql, v)
2382   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
2383 end
literal_false() click to toggle source

PostgreSQL uses FALSE for false values

     # File lib/sequel/adapters/shared/postgres.rb
2386 def literal_false
2387   'false'
2388 end
literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2391 def literal_float(value)
2392   if value.finite?
2393     super
2394   elsif value.nan?
2395     "'NaN'"
2396   elsif value.infinite? == 1
2397     "'Infinity'"
2398   else
2399     "'-Infinity'"
2400   end
2401 end
literal_integer(v) click to toggle source

Handle Ruby integers outside PostgreSQL bigint range specially.

     # File lib/sequel/adapters/shared/postgres.rb
2404 def literal_integer(v)
2405   if v > 9223372036854775807 || v < -9223372036854775808
2406     literal_integer_outside_bigint_range(v)
2407   else
2408     v.to_s
2409   end
2410 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
2415 def literal_integer_outside_bigint_range(v)
2416   raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}"
2417 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
2420 def literal_string_append(sql, v)
2421   sql << "'" << v.gsub("'", "''") << "'"
2422 end
literal_true() click to toggle source

PostgreSQL uses true for true values

     # File lib/sequel/adapters/shared/postgres.rb
2425 def literal_true
2426   'true'
2427 end
multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2430 def multi_insert_sql_strategy
2431   :values
2432 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
2435 def non_sql_option?(key)
2436   super || key == :cursor || key == :insert_conflict
2437 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
2449 def requires_like_escape?
2450   false
2451 end
select_limit_sql(sql) click to toggle source

Support FETCH FIRST WITH TIES on PostgreSQL 13+.

     # File lib/sequel/adapters/shared/postgres.rb
2454 def select_limit_sql(sql)
2455   l = @opts[:limit]
2456   o = @opts[:offset]
2457 
2458   return unless l || o
2459 
2460   if @opts[:limit_with_ties]
2461     if o
2462       sql << " OFFSET "
2463       literal_append(sql, o)
2464     end
2465 
2466     if l
2467       sql << " FETCH FIRST "
2468       literal_append(sql, l)
2469       sql << " ROWS WITH TIES"
2470     end
2471   else
2472     if l
2473       sql << " LIMIT "
2474       literal_append(sql, l)
2475     end
2476 
2477     if o
2478       sql << " OFFSET "
2479       literal_append(sql, o)
2480     end
2481   end
2482 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
2486 def select_lock_sql(sql)
2487   lock = @opts[:lock]
2488   if lock == :share
2489     sql << ' FOR SHARE'
2490   else
2491     super
2492   end
2493 
2494   if lock
2495     if @opts[:skip_locked]
2496       sql << " SKIP LOCKED"
2497     elsif @opts[:nowait]
2498       sql << " NOWAIT"
2499     end
2500   end
2501 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
2504 def select_values_sql(sql)
2505   sql << "VALUES "
2506   expression_list_append(sql, opts[:values])
2507 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
2510 def select_with_sql_base
2511   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
2512 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
2515 def select_with_sql_cte(sql, cte)
2516   super
2517   select_with_sql_cte_search_cycle(sql, cte)
2518 end
select_with_sql_cte_search_cycle(sql, cte) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2520 def select_with_sql_cte_search_cycle(sql, cte)
2521   if search_opts = cte[:search]
2522     sql << if search_opts[:type] == :breadth
2523       " SEARCH BREADTH FIRST BY "
2524     else
2525       " SEARCH DEPTH FIRST BY "
2526     end
2527 
2528     identifier_list_append(sql, Array(search_opts[:by]))
2529     sql << " SET "
2530     identifier_append(sql, search_opts[:set] || :ordercol)
2531   end
2532 
2533   if cycle_opts = cte[:cycle]
2534     sql << " CYCLE "
2535     identifier_list_append(sql, Array(cycle_opts[:columns]))
2536     sql << " SET "
2537     identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle)
2538     if cycle_opts.has_key?(:cycle_value)
2539       sql << " TO "
2540       literal_append(sql, cycle_opts[:cycle_value])
2541       sql << " DEFAULT "
2542       literal_append(sql, cycle_opts.fetch(:noncycle_value, false))
2543     end
2544     sql << " USING "
2545     identifier_append(sql, cycle_opts[:path_column] || :path)
2546   end
2547 end
server_version() click to toggle source

The version of the database server

     # File lib/sequel/adapters/shared/postgres.rb
2550 def server_version
2551   db.server_version(@opts[:server])
2552 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
2555 def supports_filtered_aggregates?
2556   server_version >= 90400
2557 end
supports_quoted_function_names?() click to toggle source

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
2560 def supports_quoted_function_names?
2561   true
2562 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
2573 def update_from_sql(sql)
2574   join_from_sql(:FROM, sql)
2575 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
2578 def update_table_sql(sql)
2579   sql << ' '
2580   source_list_append(sql, @opts[:from][0..0])
2581 end