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
1839 def analyze
1840   explain(:analyze=>true)
1841 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
1846 def complex_expression_sql_append(sql, op, args)
1847   case op
1848   when :^
1849     j = ' # '
1850     c = false
1851     args.each do |a|
1852       sql << j if c
1853       literal_append(sql, a)
1854       c ||= true
1855     end
1856   when :ILIKE, :'NOT ILIKE'
1857     sql << '('
1858     literal_append(sql, args[0])
1859     sql << ' ' << op.to_s << ' '
1860     literal_append(sql, args[1])
1861     sql << ')'
1862   else
1863     super
1864   end
1865 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
1881 def disable_insert_returning
1882   clone(:disable_insert_returning=>true)
1883 end
empty?() click to toggle source

Always return false when using VALUES

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1886 def empty?
1887   return false if @opts[:values]
1888   super
1889 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
1892 def explain(opts=OPTS)
1893   with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n")
1894 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
1897 def for_share
1898   lock_style(:share)
1899 end
insert(*values) click to toggle source

Insert given values into the database.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
1962 def insert(*values)
1963   if @opts[:returning]
1964     # Already know which columns to return, let the standard code handle it
1965     super
1966   elsif @opts[:sql] || @opts[:disable_insert_returning]
1967     # Raw SQL used or RETURNING disabled, just use the default behavior
1968     # and return nil since sequence is not known.
1969     super
1970     nil
1971   else
1972     # Force the use of RETURNING with the primary key value,
1973     # unless it has been disabled.
1974     returning(insert_pk).insert(*values){|r| return r.values.first}
1975   end
1976 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
2013 def insert_conflict(opts=OPTS)
2014   clone(:insert_conflict => opts)
2015 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
2023 def insert_ignore
2024   insert_conflict
2025 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
2030 def insert_select(*values)
2031   return unless supports_insert_select?
2032   # Handle case where query does not return a row
2033   server?(:default).with_sql_first(insert_select_sql(*values)) || false
2034 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
2038 def insert_select_sql(*values)
2039   ds = opts[:returning] ? self : returning
2040   ds.insert_sql(*values)
2041 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
2045 def join_table(type, table, expr=nil, options=OPTS, &block)
2046   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
2047     options = options.merge(:join_using=>true)
2048   end
2049   super
2050 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
2057 def lock(mode, opts=OPTS)
2058   if defined?(yield) # perform locking inside a transaction and yield to block
2059     @db.transaction(opts){lock(mode, opts); yield}
2060   else
2061     sql = 'LOCK TABLE '.dup
2062     source_list_append(sql, @opts[:from])
2063     mode = mode.to_s.upcase.strip
2064     unless LOCK_MODES.include?(mode)
2065       raise Error, "Unsupported lock mode: #{mode}"
2066     end
2067     sql << " IN #{mode} MODE"
2068     @db.execute(sql, opts)
2069   end
2070   nil
2071 end
merge(&block) click to toggle source

Support MERGE RETURNING on PostgreSQL 17+.

     # File lib/sequel/adapters/shared/postgres.rb
2074 def merge(&block)
2075   sql = merge_sql
2076   if uses_returning?(:merge)
2077     returning_fetch_rows(sql, &block)
2078   else
2079     execute_ddl(sql)
2080   end
2081 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
2092 def merge_delete_when_not_matched_by_source(&block)
2093   _merge_when(:type=>:delete_not_matched_by_source, &block)
2094 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
2105 def merge_do_nothing_when_matched(&block)
2106   _merge_when(:type=>:matched, &block)
2107 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
2118 def merge_do_nothing_when_not_matched(&block)
2119   _merge_when(:type=>:not_matched, &block)
2120 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
2131 def merge_do_nothing_when_not_matched_by_source(&block)
2132   _merge_when(:type=>:not_matched_by_source, &block)
2133 end
merge_insert(*values, &block) click to toggle source

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2136 def merge_insert(*values, &block)
2137   h = {:type=>:insert, :values=>values}
2138   if @opts[:override]
2139     h[:override] = insert_override_sql(String.new)
2140   end
2141   _merge_when(h, &block)
2142 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
2153 def merge_update_when_not_matched_by_source(values, &block)
2154   _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block)
2155 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
2160 def overriding_system_value
2161   clone(:override=>:system)
2162 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
2166 def overriding_user_value
2167   clone(:override=>:user)
2168 end
supports_cte?(type=:select) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2170 def supports_cte?(type=:select)
2171   if type == :select
2172     server_version >= 80400
2173   else
2174     server_version >= 90100
2175   end
2176 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
2180 def supports_cte_in_subqueries?
2181   supports_cte?
2182 end
supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

     # File lib/sequel/adapters/shared/postgres.rb
2185 def supports_distinct_on?
2186   true
2187 end
supports_group_cube?() click to toggle source

PostgreSQL 9.5+ supports GROUP CUBE

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

PostgreSQL 9.5+ supports GROUP ROLLUP

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

PostgreSQL 9.5+ supports GROUPING SETS

     # File lib/sequel/adapters/shared/postgres.rb
2200 def supports_grouping_sets?
2201   server_version >= 90500
2202 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
2210 def supports_insert_conflict?
2211   server_version >= 90500
2212 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
2205 def supports_insert_select?
2206   !@opts[:disable_insert_returning]
2207 end
supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

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

PostgreSQL 15+ supports MERGE.

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

PostgreSQL supports modifying joined datasets

     # File lib/sequel/adapters/shared/postgres.rb
2220 def supports_modifying_joins?
2221   true
2222 end
supports_nowait?() click to toggle source

PostgreSQL supports NOWAIT.

     # File lib/sequel/adapters/shared/postgres.rb
2230 def supports_nowait?
2231   true
2232 end
supports_regexp?() click to toggle source

PostgreSQL supports pattern matching via regular expressions

     # File lib/sequel/adapters/shared/postgres.rb
2245 def supports_regexp?
2246   true
2247 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
2236 def supports_returning?(type)
2237   if type == :merge
2238     server_version >= 170000
2239   else
2240     true
2241   end
2242 end
supports_skip_locked?() click to toggle source

PostgreSQL 9.5+ supports SKIP LOCKED.

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

PostgreSQL supports timezones in literal timestamps

     # File lib/sequel/adapters/shared/postgres.rb
2257 def supports_timestamp_timezones?
2258   # SEQUEL6: Remove
2259   true
2260 end
supports_window_clause?() click to toggle source

PostgreSQL 8.4+ supports WINDOW clause.

     # File lib/sequel/adapters/shared/postgres.rb
2264 def supports_window_clause?
2265   server_version >= 80400
2266 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
2275 def supports_window_function_frame_option?(option)
2276   case option
2277   when :rows, :range
2278     true
2279   when :offset
2280     server_version >= 90000
2281   when :groups, :exclude
2282     server_version >= 110000
2283   else
2284     false
2285   end
2286 end
supports_window_functions?() click to toggle source

PostgreSQL 8.4+ supports window functions

     # File lib/sequel/adapters/shared/postgres.rb
2269 def supports_window_functions?
2270   server_version >= 80400
2271 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
2304 def truncate(opts = OPTS)
2305   if opts.empty?
2306     super()
2307   else
2308     clone(:truncate_opts=>opts).truncate
2309   end
2310 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
2315 def with_ties
2316   clone(:limit_with_ties=>true)
2317 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
2325 def _import(columns, values, opts=OPTS)
2326   if @opts[:returning]
2327     # no transaction: our multi_insert_sql_strategy should guarantee
2328     # that there's only ever a single statement.
2329     sql = multi_insert_sql(columns, values)[0]
2330     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
2331   elsif opts[:return] == :primary_key
2332     returning(insert_pk)._import(columns, values, opts)
2333   else
2334     super
2335   end
2336 end
to_prepared_statement(type, *a) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2338 def to_prepared_statement(type, *a)
2339   if type == :insert && !@opts.has_key?(:returning)
2340     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
2341   else
2342     super
2343   end
2344 end

Private Instance Methods

_merge_do_nothing_sql(sql, data) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2359 def _merge_do_nothing_sql(sql, data)
2360   sql << " THEN DO NOTHING"
2361 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
2349 def _merge_insert_sql(sql, data)
2350   sql << " THEN INSERT"
2351   columns, values = _parse_insert_sql_args(data[:values])
2352   _insert_columns_sql(sql, columns)
2353   if override = data[:override]
2354     sql << override
2355   end
2356   _insert_values_sql(sql, values)
2357 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
2364 def _merge_when_sql(sql)
2365   super
2366   insert_returning_sql(sql) if uses_returning?(:merge)
2367 end
_truncate_sql(table) click to toggle source

Format TRUNCATE statement with PostgreSQL specific options.

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

Allow truncation of multiple source tables.

     # File lib/sequel/adapters/shared/postgres.rb
2381 def check_truncation_allowed!
2382   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
2383   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
2384 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
2569 def compound_dataset_sql_append(sql, ds)
2570   sql << '('
2571   super
2572   sql << ')'
2573 end
default_timestamp_format() click to toggle source

The strftime format to use when literalizing the time.

     # File lib/sequel/adapters/shared/postgres.rb
2387 def default_timestamp_format
2388   "'%Y-%m-%d %H:%M:%S.%6N%z'"
2389 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
2392 def delete_from_sql(sql)
2393   sql << ' FROM '
2394   source_list_append(sql, @opts[:from][0..0])
2395 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
2398 def delete_using_sql(sql)
2399   join_from_sql(:USING, sql)
2400 end
derived_column_list_sql_append(sql, column_aliases) click to toggle source

Handle column aliases containing data types, useful for selecting from functions that return the record data type.

     # File lib/sequel/adapters/shared/postgres.rb
2404 def derived_column_list_sql_append(sql, column_aliases)
2405   c = false
2406   comma = ', '
2407   column_aliases.each do |a|
2408     sql << comma if c
2409     if a.is_a?(Array)
2410       raise Error, "column aliases specified as arrays must have only 2 elements, the first is alias name and the second is data type" unless a.length == 2
2411       a, type = a
2412       identifier_append(sql, a)
2413       sql << " " << db.cast_type_literal(type).to_s
2414     else
2415       identifier_append(sql, a)
2416     end
2417     c ||= true
2418   end
2419 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
2693 def full_text_string_join(cols)
2694   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
2695   cols = cols.zip([' '] * cols.length).flatten
2696   cols.pop
2697   SQL::StringExpression.new(:'||', *cols)
2698 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
2422 def insert_conflict_sql(sql)
2423   if opts = @opts[:insert_conflict]
2424     sql << " ON CONFLICT"
2425 
2426     if target = opts[:constraint] 
2427       sql << " ON CONSTRAINT "
2428       identifier_append(sql, target)
2429     elsif target = opts[:target]
2430       sql << ' '
2431       identifier_append(sql, Array(target))
2432       if conflict_where = opts[:conflict_where]
2433         sql << " WHERE "
2434         literal_append(sql, conflict_where)
2435       end
2436     end
2437 
2438     if values = opts[:update]
2439       sql << " DO UPDATE SET "
2440       update_sql_values_hash(sql, values)
2441       if update_where = opts[:update_where]
2442         sql << " WHERE "
2443         literal_append(sql, update_where)
2444       end
2445     else
2446       sql << " DO NOTHING"
2447     end
2448   end
2449 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
2452 def insert_into_sql(sql)
2453   sql << " INTO "
2454   if (f = @opts[:from]) && f.length == 1
2455     identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first))
2456   else
2457     source_list_append(sql, f)
2458   end
2459 end
insert_override_sql(sql) click to toggle source

Support OVERRIDING SYSTEM|USER VALUE in insert statements

     # File lib/sequel/adapters/shared/postgres.rb
2476 def insert_override_sql(sql)
2477   case opts[:override]
2478   when :system
2479     sql << " OVERRIDING SYSTEM VALUE"
2480   when :user
2481     sql << " OVERRIDING USER VALUE"
2482   end
2483 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
2462 def insert_pk
2463   (f = opts[:from]) && !f.empty? && (t = f.first)
2464 
2465   t = t.call(self) if t.is_a? Sequel::SQL::DelayedEvaluation
2466 
2467   case t
2468   when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
2469     if pk = db.primary_key(t)
2470       Sequel::SQL::Identifier.new(pk)
2471     end
2472   end
2473 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
2487 def join_from_sql(type, sql)
2488   if(from = @opts[:from][1..-1]).empty?
2489     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
2490   else
2491     sql << ' ' << type.to_s << ' '
2492     source_list_append(sql, from)
2493     select_join_sql(sql)
2494   end
2495 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
2498 def join_using_clause_using_sql_append(sql, using_columns)
2499   if using_columns.is_a?(SQL::AliasedExpression)
2500     super(sql, using_columns.expression)
2501     sql << ' AS '
2502     identifier_append(sql, using_columns.alias)
2503   else
2504     super
2505   end
2506 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
2509 def literal_blob_append(sql, v)
2510   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
2511 end
literal_false() click to toggle source

PostgreSQL uses FALSE for false values

     # File lib/sequel/adapters/shared/postgres.rb
2514 def literal_false
2515   'false'
2516 end
literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2519 def literal_float(value)
2520   if value.finite?
2521     super
2522   elsif value.nan?
2523     "'NaN'"
2524   elsif value.infinite? == 1
2525     "'Infinity'"
2526   else
2527     "'-Infinity'"
2528   end
2529 end
literal_integer(v) click to toggle source

Handle Ruby integers outside PostgreSQL bigint range specially.

     # File lib/sequel/adapters/shared/postgres.rb
2532 def literal_integer(v)
2533   if v > 9223372036854775807 || v < -9223372036854775808
2534     literal_integer_outside_bigint_range(v)
2535   else
2536     v.to_s
2537   end
2538 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
2543 def literal_integer_outside_bigint_range(v)
2544   raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}"
2545 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
2548 def literal_string_append(sql, v)
2549   sql << "'" << v.gsub("'", "''") << "'"
2550 end
literal_true() click to toggle source

PostgreSQL uses true for true values

     # File lib/sequel/adapters/shared/postgres.rb
2553 def literal_true
2554   'true'
2555 end
multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2558 def multi_insert_sql_strategy
2559   :values
2560 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
2563 def non_sql_option?(key)
2564   super || key == :cursor || key == :insert_conflict
2565 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
2577 def requires_like_escape?
2578   false
2579 end
select_limit_sql(sql) click to toggle source

Support FETCH FIRST WITH TIES on PostgreSQL 13+.

     # File lib/sequel/adapters/shared/postgres.rb
2582 def select_limit_sql(sql)
2583   l = @opts[:limit]
2584   o = @opts[:offset]
2585 
2586   return unless l || o
2587 
2588   if @opts[:limit_with_ties]
2589     if o
2590       sql << " OFFSET "
2591       literal_append(sql, o)
2592     end
2593 
2594     if l
2595       sql << " FETCH FIRST "
2596       literal_append(sql, l)
2597       sql << " ROWS WITH TIES"
2598     end
2599   else
2600     if l
2601       sql << " LIMIT "
2602       literal_append(sql, l)
2603     end
2604 
2605     if o
2606       sql << " OFFSET "
2607       literal_append(sql, o)
2608     end
2609   end
2610 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
2614 def select_lock_sql(sql)
2615   lock = @opts[:lock]
2616   if lock == :share
2617     sql << ' FOR SHARE'
2618   else
2619     super
2620   end
2621 
2622   if lock
2623     if @opts[:skip_locked]
2624       sql << " SKIP LOCKED"
2625     elsif @opts[:nowait]
2626       sql << " NOWAIT"
2627     end
2628   end
2629 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
2632 def select_values_sql(sql)
2633   sql << "VALUES "
2634   expression_list_append(sql, opts[:values])
2635 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
2638 def select_with_sql_base
2639   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
2640 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
2643 def select_with_sql_cte(sql, cte)
2644   super
2645   select_with_sql_cte_search_cycle(sql, cte)
2646 end
select_with_sql_cte_search_cycle(sql, cte) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2648 def select_with_sql_cte_search_cycle(sql, cte)
2649   if search_opts = cte[:search]
2650     sql << if search_opts[:type] == :breadth
2651       " SEARCH BREADTH FIRST BY "
2652     else
2653       " SEARCH DEPTH FIRST BY "
2654     end
2655 
2656     identifier_list_append(sql, Array(search_opts[:by]))
2657     sql << " SET "
2658     identifier_append(sql, search_opts[:set] || :ordercol)
2659   end
2660 
2661   if cycle_opts = cte[:cycle]
2662     sql << " CYCLE "
2663     identifier_list_append(sql, Array(cycle_opts[:columns]))
2664     sql << " SET "
2665     identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle)
2666     if cycle_opts.has_key?(:cycle_value)
2667       sql << " TO "
2668       literal_append(sql, cycle_opts[:cycle_value])
2669       sql << " DEFAULT "
2670       literal_append(sql, cycle_opts.fetch(:noncycle_value, false))
2671     end
2672     sql << " USING "
2673     identifier_append(sql, cycle_opts[:path_column] || :path)
2674   end
2675 end
server_version() click to toggle source

The version of the database server

     # File lib/sequel/adapters/shared/postgres.rb
2678 def server_version
2679   db.server_version(@opts[:server])
2680 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
2683 def supports_filtered_aggregates?
2684   server_version >= 90400
2685 end
supports_quoted_function_names?() click to toggle source

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
2688 def supports_quoted_function_names?
2689   true
2690 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
2701 def update_from_sql(sql)
2702   join_from_sql(:FROM, sql)
2703 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
2706 def update_table_sql(sql)
2707   sql << ' '
2708   source_list_append(sql, @opts[:from][0..0])
2709 end