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

Always return false when using VALUES

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1874 def empty?
1875   return false if @opts[:values]
1876   super
1877 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
1880 def explain(opts=OPTS)
1881   with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n")
1882 end
for_share() click to toggle source

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

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

Insert given values into the database.

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

Support MERGE RETURNING on PostgreSQL 17+.

     # File lib/sequel/adapters/shared/postgres.rb
2062 def merge(&block)
2063   sql = merge_sql
2064   if uses_returning?(:merge)
2065     returning_fetch_rows(sql, &block)
2066   else
2067     execute_ddl(sql)
2068   end
2069 end
merge_delete_when_not_matched_by_source(&block) click to toggle source

Return a dataset with a WHEN NOT MATCHED BY SOURCE THEN DELETE clause added to the MERGE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_delete_not_matched_by_source
# WHEN NOT MATCHED BY SOURCE THEN DELETE

merge_delete_not_matched_by_source{a > 30}
# WHEN NOT MATCHED BY SOURCE AND (a > 30) THEN DELETE
     # File lib/sequel/adapters/shared/postgres.rb
2080 def merge_delete_when_not_matched_by_source(&block)
2081   _merge_when(:type=>:delete_not_matched_by_source, &block)
2082 end
merge_do_nothing_when_matched(&block) 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
2093 def merge_do_nothing_when_matched(&block)
2094   _merge_when(:type=>:matched, &block)
2095 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
2106 def merge_do_nothing_when_not_matched(&block)
2107   _merge_when(:type=>:not_matched, &block)
2108 end
merge_do_nothing_when_not_matched_by_source(&block) click to toggle source

Return a dataset with a WHEN NOT MATCHED BY SOURCE THEN DO NOTHING clause added to the MERGE BY SOURCE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_do_nothing_when_not_matched_by_source
# WHEN NOT MATCHED BY SOURCE THEN DO NOTHING

merge_do_nothing_when_not_matched_by_source{a > 30}
# WHEN NOT MATCHED BY SOURCE AND (a > 30) THEN DO NOTHING
     # File lib/sequel/adapters/shared/postgres.rb
2119 def merge_do_nothing_when_not_matched_by_source(&block)
2120   _merge_when(:type=>:not_matched_by_source, &block)
2121 end
merge_insert(*values, &block) click to toggle source

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2124 def merge_insert(*values, &block)
2125   h = {:type=>:insert, :values=>values}
2126   if @opts[:override]
2127     h[:override] = insert_override_sql(String.new)
2128   end
2129   _merge_when(h, &block)
2130 end
merge_update_when_not_matched_by_source(values, &block) click to toggle source

Return a dataset with a WHEN NOT MATCHED BY SOURCE THEN UPDATE clause added to the MERGE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_update_not_matched_by_source(i1: Sequel[:i1]+:i2+10, a: Sequel[:a]+:b+20)
# WHEN NOT MATCHED BY SOURCE THEN UPDATE SET i1 = (i1 + i2 + 10), a = (a + b + 20)

merge_update_not_matched_by_source(i1: :i2){a > 30}
# WHEN NOT MATCHED BY SOURCE AND (a > 30) THEN UPDATE SET i1 = i2
     # File lib/sequel/adapters/shared/postgres.rb
2141 def merge_update_when_not_matched_by_source(values, &block)
2142   _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block)
2143 end
overriding_system_value() 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
2148 def overriding_system_value
2149   clone(:override=>:system)
2150 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
2154 def overriding_user_value
2155   clone(:override=>:user)
2156 end
supports_cte?(type=:select) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2158 def supports_cte?(type=:select)
2159   if type == :select
2160     server_version >= 80400
2161   else
2162     server_version >= 90100
2163   end
2164 end
supports_cte_in_subqueries?() 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
2168 def supports_cte_in_subqueries?
2169   supports_cte?
2170 end
supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

     # File lib/sequel/adapters/shared/postgres.rb
2173 def supports_distinct_on?
2174   true
2175 end
supports_group_cube?() click to toggle source

PostgreSQL 9.5+ supports GROUP CUBE

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

PostgreSQL 9.5+ supports GROUP ROLLUP

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

PostgreSQL 9.5+ supports GROUPING SETS

     # File lib/sequel/adapters/shared/postgres.rb
2188 def supports_grouping_sets?
2189   server_version >= 90500
2190 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
2198 def supports_insert_conflict?
2199   server_version >= 90500
2200 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
2193 def supports_insert_select?
2194   !@opts[:disable_insert_returning]
2195 end
supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

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

PostgreSQL 15+ supports MERGE.

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

PostgreSQL supports modifying joined datasets

     # File lib/sequel/adapters/shared/postgres.rb
2208 def supports_modifying_joins?
2209   true
2210 end
supports_nowait?() click to toggle source

PostgreSQL supports NOWAIT.

     # File lib/sequel/adapters/shared/postgres.rb
2218 def supports_nowait?
2219   true
2220 end
supports_regexp?() click to toggle source

PostgreSQL supports pattern matching via regular expressions

     # File lib/sequel/adapters/shared/postgres.rb
2233 def supports_regexp?
2234   true
2235 end
supports_returning?(type) click to toggle source

MERGE RETURNING is supported on PostgreSQL 17+. Other RETURNING is supported on all supported PostgreSQL versions.

     # File lib/sequel/adapters/shared/postgres.rb
2224 def supports_returning?(type)
2225   if type == :merge
2226     server_version >= 170000
2227   else
2228     true
2229   end
2230 end
supports_skip_locked?() click to toggle source

PostgreSQL 9.5+ supports SKIP LOCKED.

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

PostgreSQL supports timezones in literal timestamps

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

PostgreSQL 8.4+ supports WINDOW clause.

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

PostgreSQL 8.4+ supports window functions

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

Private Instance Methods

_merge_do_nothing_sql(sql, data) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2347 def _merge_do_nothing_sql(sql, data)
2348   sql << " THEN DO NOTHING"
2349 end
_merge_insert_sql(sql, data) click to toggle source

Append the INSERT sql used in a MERGE

     # File lib/sequel/adapters/shared/postgres.rb
2337 def _merge_insert_sql(sql, data)
2338   sql << " THEN INSERT"
2339   columns, values = _parse_insert_sql_args(data[:values])
2340   _insert_columns_sql(sql, columns)
2341   if override = data[:override]
2342     sql << override
2343   end
2344   _insert_values_sql(sql, values)
2345 end
_merge_when_sql(sql) click to toggle source

Support MERGE RETURNING on PostgreSQL 17+.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2352 def _merge_when_sql(sql)
2353   super
2354   insert_returning_sql(sql) if uses_returning?(:merge)
2355 end
_truncate_sql(table) click to toggle source

Format TRUNCATE statement with PostgreSQL specific options.

     # File lib/sequel/adapters/shared/postgres.rb
2358 def _truncate_sql(table)
2359   to = @opts[:truncate_opts] || OPTS
2360   "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}"
2361 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
2364 def aggreate_dataset_use_from_self?
2365   super || @opts[:values]
2366 end
check_truncation_allowed!() click to toggle source

Allow truncation of multiple source tables.

     # File lib/sequel/adapters/shared/postgres.rb
2369 def check_truncation_allowed!
2370   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
2371   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
2372 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
2535 def compound_dataset_sql_append(sql, ds)
2536   sql << '('
2537   super
2538   sql << ')'
2539 end
default_timestamp_format() click to toggle source

The strftime format to use when literalizing the time.

     # File lib/sequel/adapters/shared/postgres.rb
2375 def default_timestamp_format
2376   "'%Y-%m-%d %H:%M:%S.%6N%z'"
2377 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
2380 def delete_from_sql(sql)
2381   sql << ' FROM '
2382   source_list_append(sql, @opts[:from][0..0])
2383 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
2386 def delete_using_sql(sql)
2387   join_from_sql(:USING, sql)
2388 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
2659 def full_text_string_join(cols)
2660   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
2661   cols = cols.zip([' '] * cols.length).flatten
2662   cols.pop
2663   SQL::StringExpression.new(:'||', *cols)
2664 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
2391 def insert_conflict_sql(sql)
2392   if opts = @opts[:insert_conflict]
2393     sql << " ON CONFLICT"
2394 
2395     if target = opts[:constraint] 
2396       sql << " ON CONSTRAINT "
2397       identifier_append(sql, target)
2398     elsif target = opts[:target]
2399       sql << ' '
2400       identifier_append(sql, Array(target))
2401       if conflict_where = opts[:conflict_where]
2402         sql << " WHERE "
2403         literal_append(sql, conflict_where)
2404       end
2405     end
2406 
2407     if values = opts[:update]
2408       sql << " DO UPDATE SET "
2409       update_sql_values_hash(sql, values)
2410       if update_where = opts[:update_where]
2411         sql << " WHERE "
2412         literal_append(sql, update_where)
2413       end
2414     else
2415       sql << " DO NOTHING"
2416     end
2417   end
2418 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
2421 def insert_into_sql(sql)
2422   sql << " INTO "
2423   if (f = @opts[:from]) && f.length == 1
2424     identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first))
2425   else
2426     source_list_append(sql, f)
2427   end
2428 end
insert_override_sql(sql) click to toggle source

Support OVERRIDING SYSTEM|USER VALUE in insert statements

     # File lib/sequel/adapters/shared/postgres.rb
2442 def insert_override_sql(sql)
2443   case opts[:override]
2444   when :system
2445     sql << " OVERRIDING SYSTEM VALUE"
2446   when :user
2447     sql << " OVERRIDING USER VALUE"
2448   end
2449 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
2431 def insert_pk
2432   (f = opts[:from]) && !f.empty? && (t = f.first)
2433   case t
2434   when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
2435     if pk = db.primary_key(t)
2436       Sequel::SQL::Identifier.new(pk)
2437     end
2438   end
2439 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
2453 def join_from_sql(type, sql)
2454   if(from = @opts[:from][1..-1]).empty?
2455     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
2456   else
2457     sql << ' ' << type.to_s << ' '
2458     source_list_append(sql, from)
2459     select_join_sql(sql)
2460   end
2461 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
2464 def join_using_clause_using_sql_append(sql, using_columns)
2465   if using_columns.is_a?(SQL::AliasedExpression)
2466     super(sql, using_columns.expression)
2467     sql << ' AS '
2468     identifier_append(sql, using_columns.alias)
2469   else
2470     super
2471   end
2472 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
2475 def literal_blob_append(sql, v)
2476   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
2477 end
literal_false() click to toggle source

PostgreSQL uses FALSE for false values

     # File lib/sequel/adapters/shared/postgres.rb
2480 def literal_false
2481   'false'
2482 end
literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2485 def literal_float(value)
2486   if value.finite?
2487     super
2488   elsif value.nan?
2489     "'NaN'"
2490   elsif value.infinite? == 1
2491     "'Infinity'"
2492   else
2493     "'-Infinity'"
2494   end
2495 end
literal_integer(v) click to toggle source

Handle Ruby integers outside PostgreSQL bigint range specially.

     # File lib/sequel/adapters/shared/postgres.rb
2498 def literal_integer(v)
2499   if v > 9223372036854775807 || v < -9223372036854775808
2500     literal_integer_outside_bigint_range(v)
2501   else
2502     v.to_s
2503   end
2504 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
2509 def literal_integer_outside_bigint_range(v)
2510   raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}"
2511 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
2514 def literal_string_append(sql, v)
2515   sql << "'" << v.gsub("'", "''") << "'"
2516 end
literal_true() click to toggle source

PostgreSQL uses true for true values

     # File lib/sequel/adapters/shared/postgres.rb
2519 def literal_true
2520   'true'
2521 end
multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2524 def multi_insert_sql_strategy
2525   :values
2526 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
2529 def non_sql_option?(key)
2530   super || key == :cursor || key == :insert_conflict
2531 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
2543 def requires_like_escape?
2544   false
2545 end
select_limit_sql(sql) click to toggle source

Support FETCH FIRST WITH TIES on PostgreSQL 13+.

     # File lib/sequel/adapters/shared/postgres.rb
2548 def select_limit_sql(sql)
2549   l = @opts[:limit]
2550   o = @opts[:offset]
2551 
2552   return unless l || o
2553 
2554   if @opts[:limit_with_ties]
2555     if o
2556       sql << " OFFSET "
2557       literal_append(sql, o)
2558     end
2559 
2560     if l
2561       sql << " FETCH FIRST "
2562       literal_append(sql, l)
2563       sql << " ROWS WITH TIES"
2564     end
2565   else
2566     if l
2567       sql << " LIMIT "
2568       literal_append(sql, l)
2569     end
2570 
2571     if o
2572       sql << " OFFSET "
2573       literal_append(sql, o)
2574     end
2575   end
2576 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
2580 def select_lock_sql(sql)
2581   lock = @opts[:lock]
2582   if lock == :share
2583     sql << ' FOR SHARE'
2584   else
2585     super
2586   end
2587 
2588   if lock
2589     if @opts[:skip_locked]
2590       sql << " SKIP LOCKED"
2591     elsif @opts[:nowait]
2592       sql << " NOWAIT"
2593     end
2594   end
2595 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
2598 def select_values_sql(sql)
2599   sql << "VALUES "
2600   expression_list_append(sql, opts[:values])
2601 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
2604 def select_with_sql_base
2605   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
2606 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
2609 def select_with_sql_cte(sql, cte)
2610   super
2611   select_with_sql_cte_search_cycle(sql, cte)
2612 end
select_with_sql_cte_search_cycle(sql, cte) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2614 def select_with_sql_cte_search_cycle(sql, cte)
2615   if search_opts = cte[:search]
2616     sql << if search_opts[:type] == :breadth
2617       " SEARCH BREADTH FIRST BY "
2618     else
2619       " SEARCH DEPTH FIRST BY "
2620     end
2621 
2622     identifier_list_append(sql, Array(search_opts[:by]))
2623     sql << " SET "
2624     identifier_append(sql, search_opts[:set] || :ordercol)
2625   end
2626 
2627   if cycle_opts = cte[:cycle]
2628     sql << " CYCLE "
2629     identifier_list_append(sql, Array(cycle_opts[:columns]))
2630     sql << " SET "
2631     identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle)
2632     if cycle_opts.has_key?(:cycle_value)
2633       sql << " TO "
2634       literal_append(sql, cycle_opts[:cycle_value])
2635       sql << " DEFAULT "
2636       literal_append(sql, cycle_opts.fetch(:noncycle_value, false))
2637     end
2638     sql << " USING "
2639     identifier_append(sql, cycle_opts[:path_column] || :path)
2640   end
2641 end
server_version() click to toggle source

The version of the database server

     # File lib/sequel/adapters/shared/postgres.rb
2644 def server_version
2645   db.server_version(@opts[:server])
2646 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
2649 def supports_filtered_aggregates?
2650   server_version >= 90400
2651 end
supports_quoted_function_names?() click to toggle source

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
2654 def supports_quoted_function_names?
2655   true
2656 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
2667 def update_from_sql(sql)
2668   join_from_sql(:FROM, sql)
2669 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
2672 def update_table_sql(sql)
2673   sql << ' '
2674   source_list_append(sql, @opts[:from][0..0])
2675 end