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
2021 def analyze
2022   explain(:analyze=>true)
2023 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
2028 def complex_expression_sql_append(sql, op, args)
2029   case op
2030   when :^
2031     j = ' # '
2032     c = false
2033     args.each do |a|
2034       sql << j if c
2035       literal_append(sql, a)
2036       c ||= true
2037     end
2038   when :ILIKE, :'NOT ILIKE'
2039     sql << '('
2040     literal_append(sql, args[0])
2041     sql << ' ' << op.to_s << ' '
2042     literal_append(sql, args[1])
2043     sql << ')'
2044   else
2045     super
2046   end
2047 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
2063 def disable_insert_returning
2064   clone(:disable_insert_returning=>true)
2065 end

empty?() click to toggle source

Always return false when using VALUES

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2068 def empty?
2069   return false if @opts[:values]
2070   super
2071 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
2101 def explain(opts=OPTS)
2102   rows = clone(:append_sql=>explain_sql_string_origin(opts)).map(:'QUERY PLAN')
2103 
2104   if rows.length == 1
2105     rows[0]
2106   elsif rows.all?{|row| String === row}
2107     rows.join("\r\n") 
2108   # :nocov:
2109   else
2110     # This branch is unreachable in tests, but it seems better to just return
2111     # all rows than throw in error if this case actually happens.
2112     rows
2113   # :nocov:
2114   end
2115 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
2119 def for_key_share
2120   cached_lock_style_dataset(:_for_key_share_ds, :key_share)
2121 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
2126 def for_no_key_update
2127   cached_lock_style_dataset(:_for_no_key_update_ds, :no_key_update)
2128 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
2131 def for_share
2132   cached_lock_style_dataset(:_for_share_ds, :share)
2133 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
2154 def full_text_search(cols, terms, opts = OPTS)
2155   lang = Sequel.cast(opts[:language] || 'simple', :regconfig)
2156 
2157   unless opts[:tsvector]
2158     phrase_cols = full_text_string_join(cols)
2159     cols = Sequel.function(:to_tsvector, lang, phrase_cols)
2160   end
2161 
2162   unless opts[:tsquery]
2163     phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms
2164 
2165     query_func = case to_tsquery = opts[:to_tsquery]
2166     when :phrase, :plain
2167       :"#{to_tsquery}to_tsquery"
2168     when :websearch
2169       :"websearch_to_tsquery"
2170     else
2171       (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery
2172     end
2173 
2174     terms = Sequel.function(query_func, lang, phrase_terms)
2175   end
2176 
2177   ds = where(Sequel.lit(["", " @@ ", ""], cols, terms))
2178 
2179   if opts[:phrase]
2180     raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery]
2181     ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true)
2182   end
2183 
2184   if opts[:rank]
2185     ds = ds.reverse{ts_rank_cd(cols, terms)}
2186   end
2187 
2188   if opts[:headline]
2189     ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)}
2190   end
2191 
2192   ds
2193 end

insert(*values) click to toggle source

Insert given values into the database.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2196 def insert(*values)
2197   if @opts[:returning]
2198     # Already know which columns to return, let the standard code handle it
2199     super
2200   elsif @opts[:sql] || @opts[:disable_insert_returning]
2201     # Raw SQL used or RETURNING disabled, just use the default behavior
2202     # and return nil since sequence is not known.
2203     super
2204     nil
2205   else
2206     # Force the use of RETURNING with the primary key value,
2207     # unless it has been disabled.
2208     returning(insert_pk).insert(*values){|r| return r.values.first}
2209   end
2210 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
2247 def insert_conflict(opts=OPTS)
2248   clone(:insert_conflict => opts)
2249 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
2257 def insert_ignore
2258   insert_conflict
2259 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
2264 def insert_select(*values)
2265   return unless supports_insert_select?
2266   # Handle case where query does not return a row
2267   server?(:default).with_sql_first(insert_select_sql(*values)) || false
2268 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
2272 def insert_select_sql(*values)
2273   ds = opts[:returning] ? self : returning
2274   ds.insert_sql(*values)
2275 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
2279 def join_table(type, table, expr=nil, options=OPTS, &block)
2280   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
2281     options = options.merge(:join_using=>true)
2282   end
2283   super
2284 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
2291 def lock(mode, opts=OPTS)
2292   if defined?(yield) # perform locking inside a transaction and yield to block
2293     @db.transaction(opts){lock(mode, opts); yield}
2294   else
2295     sql = 'LOCK TABLE '.dup
2296     source_list_append(sql, @opts[:from])
2297     mode = mode.to_s.upcase.strip
2298     unless LOCK_MODES.include?(mode)
2299       raise Error, "Unsupported lock mode: #{mode}"
2300     end
2301     sql << " IN #{mode} MODE"
2302     @db.execute(sql, opts)
2303   end
2304   nil
2305 end

merge(&block) click to toggle source

Support MERGE RETURNING on PostgreSQL 17+.

     # File lib/sequel/adapters/shared/postgres.rb
2308 def merge(&block)
2309   sql = merge_sql
2310   if uses_returning?(:merge)
2311     returning_fetch_rows(sql, &block)
2312   else
2313     execute_ddl(sql)
2314   end
2315 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
2326 def merge_delete_when_not_matched_by_source(&block)
2327   _merge_when(:type=>:delete_not_matched_by_source, &block)
2328 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
2339 def merge_do_nothing_when_matched(&block)
2340   _merge_when(:type=>:matched, &block)
2341 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
2352 def merge_do_nothing_when_not_matched(&block)
2353   _merge_when(:type=>:not_matched, &block)
2354 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
2365 def merge_do_nothing_when_not_matched_by_source(&block)
2366   _merge_when(:type=>:not_matched_by_source, &block)
2367 end

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

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2370 def merge_insert(*values, &block)
2371   h = {:type=>:insert, :values=>values}
2372   if @opts[:override]
2373     h[:override] = insert_override_sql(String.new)
2374   end
2375   _merge_when(h, &block)
2376 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
2387 def merge_update_when_not_matched_by_source(values, &block)
2388   _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block)
2389 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
2394 def overriding_system_value
2395   clone(:override=>:system)
2396 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
2400 def overriding_user_value
2401   clone(:override=>:user)
2402 end

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

     # File lib/sequel/adapters/shared/postgres.rb
2404 def supports_cte?(type=:select)
2405   if type == :select
2406     server_version >= 80400
2407   else
2408     server_version >= 90100
2409   end
2410 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
2414 def supports_cte_in_subqueries?
2415   supports_cte?
2416 end

supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

     # File lib/sequel/adapters/shared/postgres.rb
2419 def supports_distinct_on?
2420   true
2421 end

supports_group_cube?() click to toggle source

PostgreSQL 9.5+ supports GROUP CUBE

     # File lib/sequel/adapters/shared/postgres.rb
2424 def supports_group_cube?
2425   server_version >= 90500
2426 end

supports_group_rollup?() click to toggle source

PostgreSQL 9.5+ supports GROUP ROLLUP

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

supports_grouping_sets?() click to toggle source

PostgreSQL 9.5+ supports GROUPING SETS

     # File lib/sequel/adapters/shared/postgres.rb
2434 def supports_grouping_sets?
2435   server_version >= 90500
2436 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
2444 def supports_insert_conflict?
2445   server_version >= 90500
2446 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
2439 def supports_insert_select?
2440   !@opts[:disable_insert_returning]
2441 end

supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

     # File lib/sequel/adapters/shared/postgres.rb
2449 def supports_lateral_subqueries?
2450   server_version >= 90300
2451 end

supports_merge?() click to toggle source

PostgreSQL 15+ supports MERGE.

     # File lib/sequel/adapters/shared/postgres.rb
2459 def supports_merge?
2460   server_version >= 150000
2461 end

supports_modifying_joins?() click to toggle source

PostgreSQL supports modifying joined datasets

     # File lib/sequel/adapters/shared/postgres.rb
2454 def supports_modifying_joins?
2455   true
2456 end

supports_nowait?() click to toggle source

PostgreSQL supports NOWAIT.

     # File lib/sequel/adapters/shared/postgres.rb
2464 def supports_nowait?
2465   true
2466 end

supports_regexp?() click to toggle source

PostgreSQL supports pattern matching via regular expressions

     # File lib/sequel/adapters/shared/postgres.rb
2479 def supports_regexp?
2480   true
2481 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
2470 def supports_returning?(type)
2471   if type == :merge
2472     server_version >= 170000
2473   else
2474     true
2475   end
2476 end

supports_skip_locked?() click to toggle source

PostgreSQL 9.5+ supports SKIP LOCKED.

     # File lib/sequel/adapters/shared/postgres.rb
2484 def supports_skip_locked?
2485   server_version >= 90500
2486 end

supports_timestamp_timezones?() click to toggle source

PostgreSQL supports timezones in literal timestamps

     # File lib/sequel/adapters/shared/postgres.rb
2491 def supports_timestamp_timezones?
2492   # SEQUEL6: Remove
2493   true
2494 end

supports_window_clause?() click to toggle source

PostgreSQL 8.4+ supports WINDOW clause.

     # File lib/sequel/adapters/shared/postgres.rb
2498 def supports_window_clause?
2499   server_version >= 80400
2500 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
2509 def supports_window_function_frame_option?(option)
2510   case option
2511   when :rows, :range
2512     true
2513   when :offset
2514     server_version >= 90000
2515   when :groups, :exclude
2516     server_version >= 110000
2517   else
2518     false
2519   end
2520 end

supports_window_functions?() click to toggle source

PostgreSQL 8.4+ supports window functions

     # File lib/sequel/adapters/shared/postgres.rb
2503 def supports_window_functions?
2504   server_version >= 80400
2505 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
2538 def truncate(opts = OPTS)
2539   if opts.empty?
2540     super()
2541   else
2542     clone(:truncate_opts=>opts).truncate
2543   end
2544 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
2549 def with_ties
2550   clone(:limit_with_ties=>true)
2551 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
2559 def _import(columns, values, opts=OPTS)
2560   if @opts[:returning]
2561     # no transaction: our multi_insert_sql_strategy should guarantee
2562     # that there's only ever a single statement.
2563     sql = multi_insert_sql(columns, values)[0]
2564     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
2565   elsif opts[:return] == :primary_key
2566     returning(insert_pk)._import(columns, values, opts)
2567   else
2568     super
2569   end
2570 end

to_prepared_statement(type, *a) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2572 def to_prepared_statement(type, *a)
2573   if type == :insert && !@opts.has_key?(:returning)
2574     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
2575   else
2576     super
2577   end
2578 end

Private Instance Methods

_merge_do_nothing_sql(sql, data) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2593 def _merge_do_nothing_sql(sql, data)
2594   sql << " THEN DO NOTHING"
2595 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
2583 def _merge_insert_sql(sql, data)
2584   sql << " THEN INSERT"
2585   columns, values = _parse_insert_sql_args(data[:values])
2586   _insert_columns_sql(sql, columns)
2587   if override = data[:override]
2588     sql << override
2589   end
2590   _insert_values_sql(sql, values)
2591 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
2598 def _merge_when_sql(sql)
2599   super
2600   insert_returning_sql(sql) if uses_returning?(:merge)
2601 end

_truncate_sql(table) click to toggle source

Format TRUNCATE statement with PostgreSQL specific options.

     # File lib/sequel/adapters/shared/postgres.rb
2604 def _truncate_sql(table)
2605   to = @opts[:truncate_opts] || OPTS
2606   "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}"
2607 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
2610 def aggreate_dataset_use_from_self?
2611   super || @opts[:values]
2612 end

check_truncation_allowed!() click to toggle source

Allow truncation of multiple source tables.

     # File lib/sequel/adapters/shared/postgres.rb
2615 def check_truncation_allowed!
2616   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
2617   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
2618 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
2861 def compound_dataset_sql_append(sql, ds)
2862   sql << '('
2863   super
2864   sql << ')'
2865 end

default_timestamp_format() click to toggle source

The strftime format to use when literalizing the time.

     # File lib/sequel/adapters/shared/postgres.rb
2621 def default_timestamp_format
2622   "'%Y-%m-%d %H:%M:%S.%6N%z'"
2623 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
2626 def delete_from_sql(sql)
2627   sql << ' FROM '
2628   source_list_append(sql, @opts[:from][0..0])
2629 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
2632 def delete_using_sql(sql)
2633   join_from_sql(:USING, sql)
2634 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
2638 def derived_column_list_sql_append(sql, column_aliases)
2639   c = false
2640   comma = ', '
2641   column_aliases.each do |a|
2642     sql << comma if c
2643     if a.is_a?(Array)
2644       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
2645       a, type = a
2646       identifier_append(sql, a)
2647       sql << " " << db.cast_type_literal(type).to_s
2648     else
2649       identifier_append(sql, a)
2650     end
2651     c ||= true
2652   end
2653 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
2667 def explain_sql_string_origin(opts)
2668   origin = String.new
2669   origin << 'EXPLAIN '
2670 
2671   # :nocov:
2672   if server_version < 90000
2673     if opts[:analyze]
2674       origin << 'ANALYZE '
2675     end
2676 
2677     return origin
2678   end
2679   # :nocov:
2680 
2681   comma = nil
2682   paren = "("
2683 
2684   add_opt = lambda do |str, value|
2685     origin << paren if paren
2686     origin << comma if comma
2687     origin << str
2688     origin << " FALSE" unless value
2689     comma ||= ', '
2690     paren &&= nil
2691   end
2692 
2693   EXPLAIN_BOOLEAN_OPTIONS.each do |key, str|
2694     unless (value = opts[key]).nil?
2695       add_opt.call(str, value)
2696     end
2697   end
2698 
2699   EXPLAIN_NONBOOLEAN_OPTIONS.each do |key, e_opts|
2700     if value = opts[key]
2701       if str = e_opts[value]
2702         add_opt.call(str, true)
2703       else
2704         raise Sequel::Error, "unrecognized value for Dataset#explain #{key.inspect} option: #{value.inspect}"
2705       end
2706     end
2707   end
2708 
2709   origin << ') ' unless paren
2710   origin
2711 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
2990 def full_text_string_join(cols)
2991   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
2992   cols = cols.zip([' '] * cols.length).flatten
2993   cols.pop
2994   SQL::StringExpression.new(:'||', *cols)
2995 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
2714 def insert_conflict_sql(sql)
2715   if opts = @opts[:insert_conflict]
2716     sql << " ON CONFLICT"
2717 
2718     if target = opts[:constraint] 
2719       sql << " ON CONSTRAINT "
2720       identifier_append(sql, target)
2721     elsif target = opts[:target]
2722       sql << ' '
2723       identifier_append(sql, Array(target))
2724       if conflict_where = opts[:conflict_where]
2725         sql << " WHERE "
2726         literal_append(sql, conflict_where)
2727       end
2728     end
2729 
2730     if values = opts[:update]
2731       sql << " DO UPDATE SET "
2732       update_sql_values_hash(sql, values)
2733       if update_where = opts[:update_where]
2734         sql << " WHERE "
2735         literal_append(sql, update_where)
2736       end
2737     else
2738       sql << " DO NOTHING"
2739     end
2740   end
2741 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
2744 def insert_into_sql(sql)
2745   sql << " INTO "
2746   if (f = @opts[:from]) && f.length == 1
2747     identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first))
2748   else
2749     source_list_append(sql, f)
2750   end
2751 end

insert_override_sql(sql) click to toggle source

Support OVERRIDING SYSTEM|USER VALUE in insert statements

     # File lib/sequel/adapters/shared/postgres.rb
2768 def insert_override_sql(sql)
2769   case opts[:override]
2770   when :system
2771     sql << " OVERRIDING SYSTEM VALUE"
2772   when :user
2773     sql << " OVERRIDING USER VALUE"
2774   end
2775 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
2754 def insert_pk
2755   (f = opts[:from]) && !f.empty? && (t = f.first)
2756 
2757   t = t.call(self) if t.is_a? Sequel::SQL::DelayedEvaluation
2758 
2759   case t
2760   when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
2761     if pk = db.primary_key(t)
2762       Sequel::SQL::Identifier.new(pk)
2763     end
2764   end
2765 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
2779 def join_from_sql(type, sql)
2780   if(from = @opts[:from][1..-1]).empty?
2781     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
2782   else
2783     sql << ' ' << type.to_s << ' '
2784     source_list_append(sql, from)
2785     select_join_sql(sql)
2786   end
2787 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
2790 def join_using_clause_using_sql_append(sql, using_columns)
2791   if using_columns.is_a?(SQL::AliasedExpression)
2792     super(sql, using_columns.expression)
2793     sql << ' AS '
2794     identifier_append(sql, using_columns.alias)
2795   else
2796     super
2797   end
2798 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
2801 def literal_blob_append(sql, v)
2802   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
2803 end

literal_false() click to toggle source

PostgreSQL uses FALSE for false values

     # File lib/sequel/adapters/shared/postgres.rb
2806 def literal_false
2807   'false'
2808 end

literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2811 def literal_float(value)
2812   if value.finite?
2813     super
2814   elsif value.nan?
2815     "'NaN'"
2816   elsif value.infinite? == 1
2817     "'Infinity'"
2818   else
2819     "'-Infinity'"
2820   end
2821 end

literal_integer(v) click to toggle source

Handle Ruby integers outside PostgreSQL bigint range specially.

     # File lib/sequel/adapters/shared/postgres.rb
2824 def literal_integer(v)
2825   if v > 9223372036854775807 || v < -9223372036854775808
2826     literal_integer_outside_bigint_range(v)
2827   else
2828     v.to_s
2829   end
2830 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
2835 def literal_integer_outside_bigint_range(v)
2836   raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}"
2837 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
2840 def literal_string_append(sql, v)
2841   sql << "'" << v.gsub("'", "''") << "'"
2842 end

literal_true() click to toggle source

PostgreSQL uses true for true values

     # File lib/sequel/adapters/shared/postgres.rb
2845 def literal_true
2846   'true'
2847 end

multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2850 def multi_insert_sql_strategy
2851   :values
2852 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
2855 def non_sql_option?(key)
2856   super || key == :cursor || key == :insert_conflict
2857 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
2869 def requires_like_escape?
2870   false
2871 end

select_limit_sql(sql) click to toggle source

Support FETCH FIRST WITH TIES on PostgreSQL 13+.

     # File lib/sequel/adapters/shared/postgres.rb
2874 def select_limit_sql(sql)
2875   l = @opts[:limit]
2876   o = @opts[:offset]
2877 
2878   return unless l || o
2879 
2880   if @opts[:limit_with_ties]
2881     if o
2882       sql << " OFFSET "
2883       literal_append(sql, o)
2884     end
2885 
2886     if l
2887       sql << " FETCH FIRST "
2888       literal_append(sql, l)
2889       sql << " ROWS WITH TIES"
2890     end
2891   else
2892     if l
2893       sql << " LIMIT "
2894       literal_append(sql, l)
2895     end
2896 
2897     if o
2898       sql << " OFFSET "
2899       literal_append(sql, o)
2900     end
2901   end
2902 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
2906 def select_lock_sql(sql)
2907   lock = @opts[:lock]
2908   case lock
2909   when :share
2910     sql << ' FOR SHARE'
2911   when :no_key_update
2912     sql << ' FOR NO KEY UPDATE'
2913   when :key_share
2914     sql << ' FOR KEY SHARE'
2915   else
2916     super
2917   end
2918 
2919   if lock
2920     if @opts[:skip_locked]
2921       sql << " SKIP LOCKED"
2922     elsif @opts[:nowait]
2923       sql << " NOWAIT"
2924     end
2925   end
2926 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
2929 def select_values_sql(sql)
2930   sql << "VALUES "
2931   expression_list_append(sql, opts[:values])
2932 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
2935 def select_with_sql_base
2936   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
2937 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
2940 def select_with_sql_cte(sql, cte)
2941   super
2942   select_with_sql_cte_search_cycle(sql, cte)
2943 end

select_with_sql_cte_search_cycle(sql, cte) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2945 def select_with_sql_cte_search_cycle(sql, cte)
2946   if search_opts = cte[:search]
2947     sql << if search_opts[:type] == :breadth
2948       " SEARCH BREADTH FIRST BY "
2949     else
2950       " SEARCH DEPTH FIRST BY "
2951     end
2952 
2953     identifier_list_append(sql, Array(search_opts[:by]))
2954     sql << " SET "
2955     identifier_append(sql, search_opts[:set] || :ordercol)
2956   end
2957 
2958   if cycle_opts = cte[:cycle]
2959     sql << " CYCLE "
2960     identifier_list_append(sql, Array(cycle_opts[:columns]))
2961     sql << " SET "
2962     identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle)
2963     if cycle_opts.has_key?(:cycle_value)
2964       sql << " TO "
2965       literal_append(sql, cycle_opts[:cycle_value])
2966       sql << " DEFAULT "
2967       literal_append(sql, cycle_opts.fetch(:noncycle_value, false))
2968     end
2969     sql << " USING "
2970     identifier_append(sql, cycle_opts[:path_column] || :path)
2971   end
2972 end

server_version() click to toggle source

The version of the database server

     # File lib/sequel/adapters/shared/postgres.rb
2975 def server_version
2976   db.server_version(@opts[:server])
2977 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
2980 def supports_filtered_aggregates?
2981   server_version >= 90400
2982 end

supports_quoted_function_names?() click to toggle source

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
2985 def supports_quoted_function_names?
2986   true
2987 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
2998 def update_from_sql(sql)
2999   join_from_sql(:FROM, sql)
3000 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
3003 def update_table_sql(sql)
3004   sql << ' '
3005   source_list_append(sql, @opts[:from][0..0])
3006 end