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

Always return false when using VALUES

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

Insert given values into the database.

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

Support MERGE RETURNING on PostgreSQL 17+.

     # File lib/sequel/adapters/shared/postgres.rb
2063 def merge(&block)
2064   sql = merge_sql
2065   if uses_returning?(:merge)
2066     returning_fetch_rows(sql, &block)
2067   else
2068     execute_ddl(sql)
2069   end
2070 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
2081 def merge_delete_when_not_matched_by_source(&block)
2082   _merge_when(:type=>:delete_not_matched_by_source, &block)
2083 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
2094 def merge_do_nothing_when_matched(&block)
2095   _merge_when(:type=>:matched, &block)
2096 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
2107 def merge_do_nothing_when_not_matched(&block)
2108   _merge_when(:type=>:not_matched, &block)
2109 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
2120 def merge_do_nothing_when_not_matched_by_source(&block)
2121   _merge_when(:type=>:not_matched_by_source, &block)
2122 end
merge_insert(*values, &block) click to toggle source

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2125 def merge_insert(*values, &block)
2126   h = {:type=>:insert, :values=>values}
2127   if @opts[:override]
2128     h[:override] = insert_override_sql(String.new)
2129   end
2130   _merge_when(h, &block)
2131 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
2142 def merge_update_when_not_matched_by_source(values, &block)
2143   _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block)
2144 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
2149 def overriding_system_value
2150   clone(:override=>:system)
2151 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
2155 def overriding_user_value
2156   clone(:override=>:user)
2157 end
supports_cte?(type=:select) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2159 def supports_cte?(type=:select)
2160   if type == :select
2161     server_version >= 80400
2162   else
2163     server_version >= 90100
2164   end
2165 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
2169 def supports_cte_in_subqueries?
2170   supports_cte?
2171 end
supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

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

PostgreSQL 9.5+ supports GROUP CUBE

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

PostgreSQL 9.5+ supports GROUP ROLLUP

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

PostgreSQL 9.5+ supports GROUPING SETS

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

PostgreSQL 9.3+ supports lateral subqueries

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

PostgreSQL 15+ supports MERGE.

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

PostgreSQL supports modifying joined datasets

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

PostgreSQL supports NOWAIT.

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

PostgreSQL supports pattern matching via regular expressions

     # File lib/sequel/adapters/shared/postgres.rb
2234 def supports_regexp?
2235   true
2236 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
2225 def supports_returning?(type)
2226   if type == :merge
2227     server_version >= 170000
2228   else
2229     true
2230   end
2231 end
supports_skip_locked?() click to toggle source

PostgreSQL 9.5+ supports SKIP LOCKED.

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

PostgreSQL supports timezones in literal timestamps

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

PostgreSQL 8.4+ supports WINDOW clause.

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

PostgreSQL 8.4+ supports window functions

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

Private Instance Methods

_merge_do_nothing_sql(sql, data) click to toggle source
     # File lib/sequel/adapters/shared/postgres.rb
2348 def _merge_do_nothing_sql(sql, data)
2349   sql << " THEN DO NOTHING"
2350 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
2338 def _merge_insert_sql(sql, data)
2339   sql << " THEN INSERT"
2340   columns, values = _parse_insert_sql_args(data[:values])
2341   _insert_columns_sql(sql, columns)
2342   if override = data[:override]
2343     sql << override
2344   end
2345   _insert_values_sql(sql, values)
2346 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
2353 def _merge_when_sql(sql)
2354   super
2355   insert_returning_sql(sql) if uses_returning?(:merge)
2356 end
_truncate_sql(table) click to toggle source

Format TRUNCATE statement with PostgreSQL specific options.

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

Allow truncation of multiple source tables.

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

The strftime format to use when literalizing the time.

     # File lib/sequel/adapters/shared/postgres.rb
2376 def default_timestamp_format
2377   "'%Y-%m-%d %H:%M:%S.%6N%z'"
2378 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
2381 def delete_from_sql(sql)
2382   sql << ' FROM '
2383   source_list_append(sql, @opts[:from][0..0])
2384 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
2387 def delete_using_sql(sql)
2388   join_from_sql(:USING, sql)
2389 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
2393 def derived_column_list_sql_append(sql, column_aliases)
2394   c = false
2395   comma = ', '
2396   column_aliases.each do |a|
2397     sql << comma if c
2398     if a.is_a?(Array)
2399       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
2400       a, type = a
2401       identifier_append(sql, a)
2402       sql << " " << db.cast_type_literal(type).to_s
2403     else
2404       identifier_append(sql, a)
2405     end
2406     c ||= true
2407   end
2408 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
2682 def full_text_string_join(cols)
2683   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
2684   cols = cols.zip([' '] * cols.length).flatten
2685   cols.pop
2686   SQL::StringExpression.new(:'||', *cols)
2687 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
2411 def insert_conflict_sql(sql)
2412   if opts = @opts[:insert_conflict]
2413     sql << " ON CONFLICT"
2414 
2415     if target = opts[:constraint] 
2416       sql << " ON CONSTRAINT "
2417       identifier_append(sql, target)
2418     elsif target = opts[:target]
2419       sql << ' '
2420       identifier_append(sql, Array(target))
2421       if conflict_where = opts[:conflict_where]
2422         sql << " WHERE "
2423         literal_append(sql, conflict_where)
2424       end
2425     end
2426 
2427     if values = opts[:update]
2428       sql << " DO UPDATE SET "
2429       update_sql_values_hash(sql, values)
2430       if update_where = opts[:update_where]
2431         sql << " WHERE "
2432         literal_append(sql, update_where)
2433       end
2434     else
2435       sql << " DO NOTHING"
2436     end
2437   end
2438 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
2441 def insert_into_sql(sql)
2442   sql << " INTO "
2443   if (f = @opts[:from]) && f.length == 1
2444     identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first))
2445   else
2446     source_list_append(sql, f)
2447   end
2448 end
insert_override_sql(sql) click to toggle source

Support OVERRIDING SYSTEM|USER VALUE in insert statements

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

PostgreSQL uses FALSE for false values

     # File lib/sequel/adapters/shared/postgres.rb
2503 def literal_false
2504   'false'
2505 end
literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method
     # File lib/sequel/adapters/shared/postgres.rb
2508 def literal_float(value)
2509   if value.finite?
2510     super
2511   elsif value.nan?
2512     "'NaN'"
2513   elsif value.infinite? == 1
2514     "'Infinity'"
2515   else
2516     "'-Infinity'"
2517   end
2518 end
literal_integer(v) click to toggle source

Handle Ruby integers outside PostgreSQL bigint range specially.

     # File lib/sequel/adapters/shared/postgres.rb
2521 def literal_integer(v)
2522   if v > 9223372036854775807 || v < -9223372036854775808
2523     literal_integer_outside_bigint_range(v)
2524   else
2525     v.to_s
2526   end
2527 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
2532 def literal_integer_outside_bigint_range(v)
2533   raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}"
2534 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
2537 def literal_string_append(sql, v)
2538   sql << "'" << v.gsub("'", "''") << "'"
2539 end
literal_true() click to toggle source

PostgreSQL uses true for true values

     # File lib/sequel/adapters/shared/postgres.rb
2542 def literal_true
2543   'true'
2544 end
multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2547 def multi_insert_sql_strategy
2548   :values
2549 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
2552 def non_sql_option?(key)
2553   super || key == :cursor || key == :insert_conflict
2554 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
2566 def requires_like_escape?
2567   false
2568 end
select_limit_sql(sql) click to toggle source

Support FETCH FIRST WITH TIES on PostgreSQL 13+.

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

The version of the database server

     # File lib/sequel/adapters/shared/postgres.rb
2667 def server_version
2668   db.server_version(@opts[:server])
2669 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
2672 def supports_filtered_aggregates?
2673   server_version >= 90400
2674 end
supports_quoted_function_names?() click to toggle source

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
2677 def supports_quoted_function_names?
2678   true
2679 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
2690 def update_from_sql(sql)
2691   join_from_sql(:FROM, sql)
2692 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
2695 def update_table_sql(sql)
2696   sql << ' '
2697   source_list_append(sql, @opts[:from][0..0])
2698 end