module Sequel::Postgres::DatasetMethods

Constants

EXPLAIN_BOOLEAN_OPTIONS
EXPLAIN_NONBOOLEAN_OPTIONS
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
1991 def analyze
1992   explain(:analyze=>true)
1993 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
1998 def complex_expression_sql_append(sql, op, args)
1999   case op
2000   when :^
2001     j = ' # '
2002     c = false
2003     args.each do |a|
2004       sql << j if c
2005       literal_append(sql, a)
2006       c ||= true
2007     end
2008   when :ILIKE, :'NOT ILIKE'
2009     sql << '('
2010     literal_append(sql, args[0])
2011     sql << ' ' << op.to_s << ' '
2012     literal_append(sql, args[1])
2013     sql << ')'
2014   else
2015     super
2016   end
2017 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
2033 def disable_insert_returning
2034   clone(:disable_insert_returning=>true)
2035 end

empty?() click to toggle source

Always return false when using VALUES

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2038 def empty?
2039   return false if @opts[:values]
2040   super
2041 end

explain(opts=OPTS) click to toggle source

Return the results of an EXPLAIN query. Boolean options:

:analyze: Use the ANALYZE option.
:buffers: Use the BUFFERS option.
:costs: Use the COSTS option.
:generic_plan: Use the GENERIC_PLAN option.
:memory: Use the MEMORY option.
:settings: Use the SETTINGS option.
:summary: Use the SUMMARY option.
:timing: Use the TIMING option.
:verbose: Use the VERBOSE option.
:wal: Use the WAL option.

Non boolean options:

:format: Use the FORMAT option to change the format of the returned value. Values can be :text, :xml, :json, or :yaml.
:serialize: Use the SERIALIZE option to get timing on serialization. Values can be :none, :text, or :binary.

See the PostgreSQL EXPLAIN documentation for an explanation of what each option does.

In most cases, the return value is a single string. However, using the format: :json option can result in the return value being an array containing a hash.

     # File lib/sequel/adapters/shared/postgres.rb
2071 def explain(opts=OPTS)
2072   rows = clone(:append_sql=>explain_sql_string_origin(opts)).map(:'QUERY PLAN')
2073 
2074   if rows.length == 1
2075     rows[0]
2076   elsif rows.all?{|row| String === row}
2077     rows.join("\r\n") 
2078   # :nocov:
2079   else
2080     # This branch is unreachable in tests, but it seems better to just return
2081     # all rows than throw in error if this case actually happens.
2082     rows
2083   # :nocov:
2084   end
2085 end

for_key_share() click to toggle source

Return a cloned dataset which will use FOR KEY SHARE to lock returned rows. Supported on PostgreSQL 9.3+.

     # File lib/sequel/adapters/shared/postgres.rb
2089 def for_key_share
2090   cached_lock_style_dataset(:_for_key_share_ds, :key_share)
2091 end

for_no_key_update() click to toggle source

Return a cloned dataset which will use FOR NO KEY UPDATE to lock returned rows. This is generally a better choice than using for_update on PostgreSQL, unless you will be deleting the row or modifying a key column. Supported on PostgreSQL 9.3+.

     # File lib/sequel/adapters/shared/postgres.rb
2096 def for_no_key_update
2097   cached_lock_style_dataset(:_for_no_key_update_ds, :no_key_update)
2098 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
2101 def for_share
2102   cached_lock_style_dataset(:_for_share_ds, :share)
2103 end

full_text_search(cols, terms, opts = OPTS) click to toggle source

Run a full text search on PostgreSQL. By default, searching for the inclusion of any of the terms in any of the cols.

Options:

:headline: Append a expression to the selected columns aliased to headline that contains an extract of the matched text.
:language: The language to use for the search (default: ‘simple’)
:plain: Whether a plain search should be used (default: false). In this case, terms should be a single string, and it will do a search where cols contains all of the words in terms. This ignores search operators in terms.
:phrase: Similar to :plain, but also adding an ILIKE filter to ensure that returned rows also include the exact phrase used.
:rank: Set to true to order by the rank, so that closer matches are returned first.
:to_tsquery: Can be set to :plain, :phrase, or :websearch to specify the function to use to convert the terms to a ts_query.
:tsquery: Specifies the terms argument is already a valid SQL expression returning a tsquery, and can be used directly in the query.
:tsvector: Specifies the cols argument is already a valid SQL expression returning a tsvector, and can be used directly in the query.

     # File lib/sequel/adapters/shared/postgres.rb
2124 def full_text_search(cols, terms, opts = OPTS)
2125   lang = Sequel.cast(opts[:language] || 'simple', :regconfig)
2126 
2127   unless opts[:tsvector]
2128     phrase_cols = full_text_string_join(cols)
2129     cols = Sequel.function(:to_tsvector, lang, phrase_cols)
2130   end
2131 
2132   unless opts[:tsquery]
2133     phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms
2134 
2135     query_func = case to_tsquery = opts[:to_tsquery]
2136     when :phrase, :plain
2137       :"#{to_tsquery}to_tsquery"
2138     when :websearch
2139       :"websearch_to_tsquery"
2140     else
2141       (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery
2142     end
2143 
2144     terms = Sequel.function(query_func, lang, phrase_terms)
2145   end
2146 
2147   ds = where(Sequel.lit(["", " @@ ", ""], cols, terms))
2148 
2149   if opts[:phrase]
2150     raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery]
2151     ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true)
2152   end
2153 
2154   if opts[:rank]
2155     ds = ds.reverse{ts_rank_cd(cols, terms)}
2156   end
2157 
2158   if opts[:headline]
2159     ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)}
2160   end
2161 
2162   ds
2163 end

insert(*values) click to toggle source

Insert given values into the database.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2166 def insert(*values)
2167   if @opts[:returning]
2168     # Already know which columns to return, let the standard code handle it
2169     super
2170   elsif @opts[:sql] || @opts[:disable_insert_returning]
2171     # Raw SQL used or RETURNING disabled, just use the default behavior
2172     # and return nil since sequence is not known.
2173     super
2174     nil
2175   else
2176     # Force the use of RETURNING with the primary key value,
2177     # unless it has been disabled.
2178     returning(insert_pk).insert(*values){|r| return r.values.first}
2179   end
2180 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
2217 def insert_conflict(opts=OPTS)
2218   clone(:insert_conflict => opts)
2219 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
2227 def insert_ignore
2228   insert_conflict
2229 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
2234 def insert_select(*values)
2235   return unless supports_insert_select?
2236   # Handle case where query does not return a row
2237   server?(:default).with_sql_first(insert_select_sql(*values)) || false
2238 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
2242 def insert_select_sql(*values)
2243   ds = opts[:returning] ? self : returning
2244   ds.insert_sql(*values)
2245 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
2249 def join_table(type, table, expr=nil, options=OPTS, &block)
2250   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
2251     options = options.merge(:join_using=>true)
2252   end
2253   super
2254 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
2261 def lock(mode, opts=OPTS)
2262   if defined?(yield) # perform locking inside a transaction and yield to block
2263     @db.transaction(opts){lock(mode, opts); yield}
2264   else
2265     sql = 'LOCK TABLE '.dup
2266     source_list_append(sql, @opts[:from])
2267     mode = mode.to_s.upcase.strip
2268     unless LOCK_MODES.include?(mode)
2269       raise Error, "Unsupported lock mode: #{mode}"
2270     end
2271     sql << " IN #{mode} MODE"
2272     @db.execute(sql, opts)
2273   end
2274   nil
2275 end

merge(&block) click to toggle source

Support MERGE RETURNING on PostgreSQL 17+.

     # File lib/sequel/adapters/shared/postgres.rb
2278 def merge(&block)
2279   sql = merge_sql
2280   if uses_returning?(:merge)
2281     returning_fetch_rows(sql, &block)
2282   else
2283     execute_ddl(sql)
2284   end
2285 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
2296 def merge_delete_when_not_matched_by_source(&block)
2297   _merge_when(:type=>:delete_not_matched_by_source, &block)
2298 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
2309 def merge_do_nothing_when_matched(&block)
2310   _merge_when(:type=>:matched, &block)
2311 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
2322 def merge_do_nothing_when_not_matched(&block)
2323   _merge_when(:type=>:not_matched, &block)
2324 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
2335 def merge_do_nothing_when_not_matched_by_source(&block)
2336   _merge_when(:type=>:not_matched_by_source, &block)
2337 end

merge_insert(*values, &block) click to toggle source

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2340 def merge_insert(*values, &block)
2341   h = {:type=>:insert, :values=>values}
2342   if @opts[:override]
2343     h[:override] = insert_override_sql(String.new)
2344   end
2345   _merge_when(h, &block)
2346 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
2357 def merge_update_when_not_matched_by_source(values, &block)
2358   _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block)
2359 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
2364 def overriding_system_value
2365   clone(:override=>:system)
2366 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
2370 def overriding_user_value
2371   clone(:override=>:user)
2372 end

supports_cte?(type=:select) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2374 def supports_cte?(type=:select)
2375   if type == :select
2376     server_version >= 80400
2377   else
2378     server_version >= 90100
2379   end
2380 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
2384 def supports_cte_in_subqueries?
2385   supports_cte?
2386 end

supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

     # File lib/sequel/adapters/shared/postgres.rb
2389 def supports_distinct_on?
2390   true
2391 end

supports_group_cube?() click to toggle source

PostgreSQL 9.5+ supports GROUP CUBE

     # File lib/sequel/adapters/shared/postgres.rb
2394 def supports_group_cube?
2395   server_version >= 90500
2396 end

supports_group_rollup?() click to toggle source

PostgreSQL 9.5+ supports GROUP ROLLUP

     # File lib/sequel/adapters/shared/postgres.rb
2399 def supports_group_rollup?
2400   server_version >= 90500
2401 end

supports_grouping_sets?() click to toggle source

PostgreSQL 9.5+ supports GROUPING SETS

     # File lib/sequel/adapters/shared/postgres.rb
2404 def supports_grouping_sets?
2405   server_version >= 90500
2406 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
2414 def supports_insert_conflict?
2415   server_version >= 90500
2416 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
2409 def supports_insert_select?
2410   !@opts[:disable_insert_returning]
2411 end

supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

     # File lib/sequel/adapters/shared/postgres.rb
2419 def supports_lateral_subqueries?
2420   server_version >= 90300
2421 end

supports_merge?() click to toggle source

PostgreSQL 15+ supports MERGE.

     # File lib/sequel/adapters/shared/postgres.rb
2429 def supports_merge?
2430   server_version >= 150000
2431 end

supports_modifying_joins?() click to toggle source

PostgreSQL supports modifying joined datasets

     # File lib/sequel/adapters/shared/postgres.rb
2424 def supports_modifying_joins?
2425   true
2426 end

supports_nowait?() click to toggle source

PostgreSQL supports NOWAIT.

     # File lib/sequel/adapters/shared/postgres.rb
2434 def supports_nowait?
2435   true
2436 end

supports_regexp?() click to toggle source

PostgreSQL supports pattern matching via regular expressions

     # File lib/sequel/adapters/shared/postgres.rb
2449 def supports_regexp?
2450   true
2451 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
2440 def supports_returning?(type)
2441   if type == :merge
2442     server_version >= 170000
2443   else
2444     true
2445   end
2446 end

supports_skip_locked?() click to toggle source

PostgreSQL 9.5+ supports SKIP LOCKED.

     # File lib/sequel/adapters/shared/postgres.rb
2454 def supports_skip_locked?
2455   server_version >= 90500
2456 end

supports_timestamp_timezones?() click to toggle source

PostgreSQL supports timezones in literal timestamps

     # File lib/sequel/adapters/shared/postgres.rb
2461 def supports_timestamp_timezones?
2462   # SEQUEL6: Remove
2463   true
2464 end

supports_window_clause?() click to toggle source

PostgreSQL 8.4+ supports WINDOW clause.

     # File lib/sequel/adapters/shared/postgres.rb
2468 def supports_window_clause?
2469   server_version >= 80400
2470 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
2479 def supports_window_function_frame_option?(option)
2480   case option
2481   when :rows, :range
2482     true
2483   when :offset
2484     server_version >= 90000
2485   when :groups, :exclude
2486     server_version >= 110000
2487   else
2488     false
2489   end
2490 end

supports_window_functions?() click to toggle source

PostgreSQL 8.4+ supports window functions

     # File lib/sequel/adapters/shared/postgres.rb
2473 def supports_window_functions?
2474   server_version >= 80400
2475 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
2508 def truncate(opts = OPTS)
2509   if opts.empty?
2510     super()
2511   else
2512     clone(:truncate_opts=>opts).truncate
2513   end
2514 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
2519 def with_ties
2520   clone(:limit_with_ties=>true)
2521 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
2529 def _import(columns, values, opts=OPTS)
2530   if @opts[:returning]
2531     # no transaction: our multi_insert_sql_strategy should guarantee
2532     # that there's only ever a single statement.
2533     sql = multi_insert_sql(columns, values)[0]
2534     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
2535   elsif opts[:return] == :primary_key
2536     returning(insert_pk)._import(columns, values, opts)
2537   else
2538     super
2539   end
2540 end

to_prepared_statement(type, *a) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2542 def to_prepared_statement(type, *a)
2543   if type == :insert && !@opts.has_key?(:returning)
2544     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
2545   else
2546     super
2547   end
2548 end

Private Instance Methods

_merge_do_nothing_sql(sql, data) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2563 def _merge_do_nothing_sql(sql, data)
2564   sql << " THEN DO NOTHING"
2565 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
2553 def _merge_insert_sql(sql, data)
2554   sql << " THEN INSERT"
2555   columns, values = _parse_insert_sql_args(data[:values])
2556   _insert_columns_sql(sql, columns)
2557   if override = data[:override]
2558     sql << override
2559   end
2560   _insert_values_sql(sql, values)
2561 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
2568 def _merge_when_sql(sql)
2569   super
2570   insert_returning_sql(sql) if uses_returning?(:merge)
2571 end

_truncate_sql(table) click to toggle source

Format TRUNCATE statement with PostgreSQL specific options.

     # File lib/sequel/adapters/shared/postgres.rb
2574 def _truncate_sql(table)
2575   to = @opts[:truncate_opts] || OPTS
2576   "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}"
2577 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
2580 def aggreate_dataset_use_from_self?
2581   super || @opts[:values]
2582 end

check_truncation_allowed!() click to toggle source

Allow truncation of multiple source tables.

     # File lib/sequel/adapters/shared/postgres.rb
2585 def check_truncation_allowed!
2586   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
2587   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
2588 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
2831 def compound_dataset_sql_append(sql, ds)
2832   sql << '('
2833   super
2834   sql << ')'
2835 end

default_timestamp_format() click to toggle source

The strftime format to use when literalizing the time.

     # File lib/sequel/adapters/shared/postgres.rb
2591 def default_timestamp_format
2592   "'%Y-%m-%d %H:%M:%S.%6N%z'"
2593 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
2596 def delete_from_sql(sql)
2597   sql << ' FROM '
2598   source_list_append(sql, @opts[:from][0..0])
2599 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
2602 def delete_using_sql(sql)
2603   join_from_sql(:USING, sql)
2604 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
2608 def derived_column_list_sql_append(sql, column_aliases)
2609   c = false
2610   comma = ', '
2611   column_aliases.each do |a|
2612     sql << comma if c
2613     if a.is_a?(Array)
2614       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
2615       a, type = a
2616       identifier_append(sql, a)
2617       sql << " " << db.cast_type_literal(type).to_s
2618     else
2619       identifier_append(sql, a)
2620     end
2621     c ||= true
2622   end
2623 end

explain_sql_string_origin(opts) click to toggle source

A mutable string used as the prefix when explaining a query.

     # File lib/sequel/adapters/shared/postgres.rb
2637 def explain_sql_string_origin(opts)
2638   origin = String.new
2639   origin << 'EXPLAIN '
2640 
2641   # :nocov:
2642   if server_version < 90000
2643     if opts[:analyze]
2644       origin << 'ANALYZE '
2645     end
2646 
2647     return origin
2648   end
2649   # :nocov:
2650 
2651   comma = nil
2652   paren = "("
2653 
2654   add_opt = lambda do |str, value|
2655     origin << paren if paren
2656     origin << comma if comma
2657     origin << str
2658     origin << " FALSE" unless value
2659     comma ||= ', '
2660     paren &&= nil
2661   end
2662 
2663   EXPLAIN_BOOLEAN_OPTIONS.each do |key, str|
2664     unless (value = opts[key]).nil?
2665       add_opt.call(str, value)
2666     end
2667   end
2668 
2669   EXPLAIN_NONBOOLEAN_OPTIONS.each do |key, e_opts|
2670     if value = opts[key]
2671       if str = e_opts[value]
2672         add_opt.call(str, true)
2673       else
2674         raise Sequel::Error, "unrecognized value for Dataset#explain #{key.inspect} option: #{value.inspect}"
2675       end
2676     end
2677   end
2678 
2679   origin << ') ' unless paren
2680   origin
2681 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
2960 def full_text_string_join(cols)
2961   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
2962   cols = cols.zip([' '] * cols.length).flatten
2963   cols.pop
2964   SQL::StringExpression.new(:'||', *cols)
2965 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
2684 def insert_conflict_sql(sql)
2685   if opts = @opts[:insert_conflict]
2686     sql << " ON CONFLICT"
2687 
2688     if target = opts[:constraint] 
2689       sql << " ON CONSTRAINT "
2690       identifier_append(sql, target)
2691     elsif target = opts[:target]
2692       sql << ' '
2693       identifier_append(sql, Array(target))
2694       if conflict_where = opts[:conflict_where]
2695         sql << " WHERE "
2696         literal_append(sql, conflict_where)
2697       end
2698     end
2699 
2700     if values = opts[:update]
2701       sql << " DO UPDATE SET "
2702       update_sql_values_hash(sql, values)
2703       if update_where = opts[:update_where]
2704         sql << " WHERE "
2705         literal_append(sql, update_where)
2706       end
2707     else
2708       sql << " DO NOTHING"
2709     end
2710   end
2711 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
2714 def insert_into_sql(sql)
2715   sql << " INTO "
2716   if (f = @opts[:from]) && f.length == 1
2717     identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first))
2718   else
2719     source_list_append(sql, f)
2720   end
2721 end

insert_override_sql(sql) click to toggle source

Support OVERRIDING SYSTEM|USER VALUE in insert statements

     # File lib/sequel/adapters/shared/postgres.rb
2738 def insert_override_sql(sql)
2739   case opts[:override]
2740   when :system
2741     sql << " OVERRIDING SYSTEM VALUE"
2742   when :user
2743     sql << " OVERRIDING USER VALUE"
2744   end
2745 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
2724 def insert_pk
2725   (f = opts[:from]) && !f.empty? && (t = f.first)
2726 
2727   t = t.call(self) if t.is_a? Sequel::SQL::DelayedEvaluation
2728 
2729   case t
2730   when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
2731     if pk = db.primary_key(t)
2732       Sequel::SQL::Identifier.new(pk)
2733     end
2734   end
2735 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
2749 def join_from_sql(type, sql)
2750   if(from = @opts[:from][1..-1]).empty?
2751     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
2752   else
2753     sql << ' ' << type.to_s << ' '
2754     source_list_append(sql, from)
2755     select_join_sql(sql)
2756   end
2757 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
2760 def join_using_clause_using_sql_append(sql, using_columns)
2761   if using_columns.is_a?(SQL::AliasedExpression)
2762     super(sql, using_columns.expression)
2763     sql << ' AS '
2764     identifier_append(sql, using_columns.alias)
2765   else
2766     super
2767   end
2768 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
2771 def literal_blob_append(sql, v)
2772   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
2773 end

literal_false() click to toggle source

PostgreSQL uses FALSE for false values

     # File lib/sequel/adapters/shared/postgres.rb
2776 def literal_false
2777   'false'
2778 end

literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2781 def literal_float(value)
2782   if value.finite?
2783     super
2784   elsif value.nan?
2785     "'NaN'"
2786   elsif value.infinite? == 1
2787     "'Infinity'"
2788   else
2789     "'-Infinity'"
2790   end
2791 end

literal_integer(v) click to toggle source

Handle Ruby integers outside PostgreSQL bigint range specially.

     # File lib/sequel/adapters/shared/postgres.rb
2794 def literal_integer(v)
2795   if v > 9223372036854775807 || v < -9223372036854775808
2796     literal_integer_outside_bigint_range(v)
2797   else
2798     v.to_s
2799   end
2800 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
2805 def literal_integer_outside_bigint_range(v)
2806   raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}"
2807 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
2810 def literal_string_append(sql, v)
2811   sql << "'" << v.gsub("'", "''") << "'"
2812 end

literal_true() click to toggle source

PostgreSQL uses true for true values

     # File lib/sequel/adapters/shared/postgres.rb
2815 def literal_true
2816   'true'
2817 end

multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2820 def multi_insert_sql_strategy
2821   :values
2822 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
2825 def non_sql_option?(key)
2826   super || key == :cursor || key == :insert_conflict
2827 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
2839 def requires_like_escape?
2840   false
2841 end

select_limit_sql(sql) click to toggle source

Support FETCH FIRST WITH TIES on PostgreSQL 13+.

     # File lib/sequel/adapters/shared/postgres.rb
2844 def select_limit_sql(sql)
2845   l = @opts[:limit]
2846   o = @opts[:offset]
2847 
2848   return unless l || o
2849 
2850   if @opts[:limit_with_ties]
2851     if o
2852       sql << " OFFSET "
2853       literal_append(sql, o)
2854     end
2855 
2856     if l
2857       sql << " FETCH FIRST "
2858       literal_append(sql, l)
2859       sql << " ROWS WITH TIES"
2860     end
2861   else
2862     if l
2863       sql << " LIMIT "
2864       literal_append(sql, l)
2865     end
2866 
2867     if o
2868       sql << " OFFSET "
2869       literal_append(sql, o)
2870     end
2871   end
2872 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
2876 def select_lock_sql(sql)
2877   lock = @opts[:lock]
2878   case lock
2879   when :share
2880     sql << ' FOR SHARE'
2881   when :no_key_update
2882     sql << ' FOR NO KEY UPDATE'
2883   when :key_share
2884     sql << ' FOR KEY SHARE'
2885   else
2886     super
2887   end
2888 
2889   if lock
2890     if @opts[:skip_locked]
2891       sql << " SKIP LOCKED"
2892     elsif @opts[:nowait]
2893       sql << " NOWAIT"
2894     end
2895   end
2896 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
2899 def select_values_sql(sql)
2900   sql << "VALUES "
2901   expression_list_append(sql, opts[:values])
2902 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
2905 def select_with_sql_base
2906   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
2907 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
2910 def select_with_sql_cte(sql, cte)
2911   super
2912   select_with_sql_cte_search_cycle(sql, cte)
2913 end

select_with_sql_cte_search_cycle(sql, cte) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2915 def select_with_sql_cte_search_cycle(sql, cte)
2916   if search_opts = cte[:search]
2917     sql << if search_opts[:type] == :breadth
2918       " SEARCH BREADTH FIRST BY "
2919     else
2920       " SEARCH DEPTH FIRST BY "
2921     end
2922 
2923     identifier_list_append(sql, Array(search_opts[:by]))
2924     sql << " SET "
2925     identifier_append(sql, search_opts[:set] || :ordercol)
2926   end
2927 
2928   if cycle_opts = cte[:cycle]
2929     sql << " CYCLE "
2930     identifier_list_append(sql, Array(cycle_opts[:columns]))
2931     sql << " SET "
2932     identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle)
2933     if cycle_opts.has_key?(:cycle_value)
2934       sql << " TO "
2935       literal_append(sql, cycle_opts[:cycle_value])
2936       sql << " DEFAULT "
2937       literal_append(sql, cycle_opts.fetch(:noncycle_value, false))
2938     end
2939     sql << " USING "
2940     identifier_append(sql, cycle_opts[:path_column] || :path)
2941   end
2942 end

server_version() click to toggle source

The version of the database server

     # File lib/sequel/adapters/shared/postgres.rb
2945 def server_version
2946   db.server_version(@opts[:server])
2947 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
2950 def supports_filtered_aggregates?
2951   server_version >= 90400
2952 end

supports_quoted_function_names?() click to toggle source

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
2955 def supports_quoted_function_names?
2956   true
2957 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
2968 def update_from_sql(sql)
2969   join_from_sql(:FROM, sql)
2970 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
2973 def update_table_sql(sql)
2974   sql << ' '
2975   source_list_append(sql, @opts[:from][0..0])
2976 end