module Sequel::Postgres::DatasetMethods

Constants

EXPLAIN_BOOLEAN_OPTIONS
EXPLAIN_NONBOOLEAN_OPTIONS
LOCK_MODES
NULL

Public Instance Methods

Source

     # File lib/sequel/adapters/shared/postgres.rb
2046 def analyze
2047   explain(:analyze=>true)
2048 end

Return the results of an EXPLAIN ANALYZE query as a string

complex_expression_sql_append (sql, op, args)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2053 def complex_expression_sql_append(sql, op, args)
2054   case op
2055   when :^
2056     j = ' # '
2057     c = false
2058     args.each do |a|
2059       sql << j if c
2060       literal_append(sql, a)
2061       c ||= true
2062     end
2063   when :ILIKE, :'NOT ILIKE'
2064     sql << '('
2065     literal_append(sql, args[0])
2066     sql << ' ' << op.to_s << ' '
2067     literal_append(sql, args[1])
2068     sql << ')'
2069   else
2070     super
2071   end
2072 end

Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#), and use the ILIKE and NOT ILIKE operators.

Calls superclass method

disable_insert_returning ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2088 def disable_insert_returning
2089   clone(:disable_insert_returning=>true)
2090 end

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).

empty? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2093 def empty?
2094   return false if @opts[:values]
2095   super
2096 end

Always return false when using VALUES

Calls superclass method

explain (opts=OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2126 def explain(opts=OPTS)
2127   rows = clone(:append_sql=>explain_sql_string_origin(opts)).map(:'QUERY PLAN')
2128 
2129   if rows.length == 1
2130     rows[0]
2131   elsif rows.all?{|row| String === row}
2132     rows.join("\r\n") 
2133   # simplecov:disable
2134   else
2135     # This branch is unreachable in tests, but it seems better to just return
2136     # all rows than throw in error if this case actually happens.
2137     rows
2138   # simplecov:enable
2139   end
2140 end

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.

for_key_share ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2144 def for_key_share
2145   cached_lock_style_dataset(:_for_key_share_ds, :key_share)
2146 end

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

for_no_key_update ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2151 def for_no_key_update
2152   cached_lock_style_dataset(:_for_no_key_update_ds, :no_key_update)
2153 end

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+.

for_share ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2156 def for_share
2157   cached_lock_style_dataset(:_for_share_ds, :share)
2158 end

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

full_text_search (cols, terms, opts = OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2179 def full_text_search(cols, terms, opts = OPTS)
2180   lang = Sequel.cast(opts[:language] || 'simple', :regconfig)
2181 
2182   unless opts[:tsvector]
2183     phrase_cols = full_text_string_join(cols)
2184     cols = Sequel.function(:to_tsvector, lang, phrase_cols)
2185   end
2186 
2187   unless opts[:tsquery]
2188     phrase_terms = terms.is_a?(Array) || terms.is_a?(Set) ? Sequel.array_or_set_join(terms, ' | ') : terms
2189 
2190     query_func = case to_tsquery = opts[:to_tsquery]
2191     when :phrase, :plain
2192       :"#{to_tsquery}to_tsquery"
2193     when :websearch
2194       :"websearch_to_tsquery"
2195     else
2196       (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery
2197     end
2198 
2199     terms = Sequel.function(query_func, lang, phrase_terms)
2200   end
2201 
2202   ds = where(Sequel.lit(["", " @@ ", ""], cols, terms))
2203 
2204   if opts[:phrase]
2205     raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery]
2206     ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true)
2207   end
2208 
2209   if opts[:rank]
2210     ds = ds.reverse{ts_rank_cd(cols, terms)}
2211   end
2212 
2213   if opts[:headline]
2214     ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)}
2215   end
2216 
2217   ds
2218 end

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.

insert (*values)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2221 def insert(*values)
2222   if @opts[:returning]
2223     # Already know which columns to return, let the standard code handle it
2224     super
2225   elsif @opts[:sql] || @opts[:disable_insert_returning]
2226     # Raw SQL used or RETURNING disabled, just use the default behavior
2227     # and return nil since sequence is not known.
2228     super
2229     nil
2230   else
2231     # Force the use of RETURNING with the primary key value,
2232     # unless it has been disabled.
2233     returning(insert_pk).insert(*values){|r| return r.values.first}
2234   end
2235 end

Insert given values into the database.

Calls superclass method

insert_conflict (opts=OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2285 def insert_conflict(opts=OPTS)
2286   if opts[:select] && opts[:update]
2287     raise Error, "Cannot provide both :select and :update options to insert_conflict"
2288   end
2289 
2290   clone(:insert_conflict => opts)
2291 end

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 precedence over :target.
:target: The column name or expression to handle uniqueness violations on.
:select: Use ON CONFLICT DO SELECT. This will use the dataset’s lock style. You must use returning on this dataset when using this option.
:select_where: A WHERE condition to use for the select.
: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)

DB[:table].returning(:a).insert_conflict(target: :a, select: true).insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) DO SELECT RETURNING a

DB[:table].returning(:a).for_update.
  insert_conflict(target: :a, select: true, select_where: {Sequel[:excluded][:b] => 3}).
  insert(a: 1, b: 2)
# INSERT INTO TABLE (a, b) VALUES (1, 2)
# ON CONFLICT (a) DO SELECT FOR UPDATE WHERE (excluded.b = 3) RETURNING a

insert_ignore ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2299 def insert_ignore
2300   insert_conflict
2301 end

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

insert_select (*values)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2306 def insert_select(*values)
2307   return unless supports_insert_select?
2308   # Handle case where query does not return a row
2309   server?(:default).with_sql_first(insert_select_sql(*values)) || false
2310 end

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.

insert_select_sql (*values)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2314 def insert_select_sql(*values)
2315   ds = opts[:returning] ? self : returning
2316   ds.insert_sql(*values)
2317 end

The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.

join_table (type, table, expr=nil, options=OPTS, &block)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2321 def join_table(type, table, expr=nil, options=OPTS, &block)
2322   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
2323     options = options.merge(:join_using=>true)
2324   end
2325   super
2326 end

Support SQL::AliasedExpression as expr to setup a USING join with a table alias for the USING columns.

Calls superclass method

lock (mode, opts=OPTS) { || ... }

Source

     # File lib/sequel/adapters/shared/postgres.rb
2333 def lock(mode, opts=OPTS)
2334   if defined?(yield) # perform locking inside a transaction and yield to block
2335     @db.transaction(opts){lock(mode, opts); yield}
2336   else
2337     sql = 'LOCK TABLE '.dup
2338     source_list_append(sql, @opts[:from])
2339     mode = mode.to_s.upcase.strip
2340     unless LOCK_MODES.include?(mode)
2341       raise Error, "Unsupported lock mode: #{mode}"
2342     end
2343     sql << " IN #{mode} MODE"
2344     @db.execute(sql, opts)
2345   end
2346   nil
2347 end

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.

merge (&block)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2350 def merge(&block)
2351   sql = merge_sql
2352   if uses_returning?(:merge)
2353     returning_fetch_rows(sql, &block)
2354   else
2355     execute_ddl(sql)
2356   end
2357 end

Support MERGE RETURNING on PostgreSQL 17+.

merge_delete_when_not_matched_by_source (&block)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2368 def merge_delete_when_not_matched_by_source(&block)
2369   _merge_when(:type=>:delete_not_matched_by_source, &block)
2370 end

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

merge_do_nothing_when_matched (&block)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2381 def merge_do_nothing_when_matched(&block)
2382   _merge_when(:type=>:matched, &block)
2383 end

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

merge_do_nothing_when_not_matched (&block)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2394 def merge_do_nothing_when_not_matched(&block)
2395   _merge_when(:type=>:not_matched, &block)
2396 end

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

merge_do_nothing_when_not_matched_by_source (&block)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2407 def merge_do_nothing_when_not_matched_by_source(&block)
2408   _merge_when(:type=>:not_matched_by_source, &block)
2409 end

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

merge_insert (*values, &block)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2412 def merge_insert(*values, &block)
2413   h = {:type=>:insert, :values=>values}
2414   if @opts[:override]
2415     h[:override] = insert_override_sql(String.new)
2416   end
2417   _merge_when(h, &block)
2418 end

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

merge_update_when_not_matched_by_source (values, &block)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2429 def merge_update_when_not_matched_by_source(values, &block)
2430   _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block)
2431 end

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

overriding_system_value ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2436 def overriding_system_value
2437   clone(:override=>:system)
2438 end

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.

overriding_user_value ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2442 def overriding_user_value
2443   clone(:override=>:user)
2444 end

Use OVERRIDING USER VALUE for INSERT statements, so that identity columns always use the sequence value instead of the user supplied value.

supports_cte? (type=:select)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2446 def supports_cte?(type=:select)
2447   if type == :select
2448     server_version >= 80400
2449   else
2450     server_version >= 90100
2451   end
2452 end

supports_cte_in_subqueries? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2456 def supports_cte_in_subqueries?
2457   supports_cte?
2458 end

PostgreSQL supports using the WITH clause in subqueries if it supports using WITH at all (i.e. on PostgreSQL 8.4+).

supports_distinct_on? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2461 def supports_distinct_on?
2462   true
2463 end

DISTINCT ON is a PostgreSQL extension

supports_group_cube? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2466 def supports_group_cube?
2467   server_version >= 90500
2468 end

PostgreSQL 9.5+ supports GROUP CUBE

supports_group_rollup? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2471 def supports_group_rollup?
2472   server_version >= 90500
2473 end

PostgreSQL 9.5+ supports GROUP ROLLUP

supports_grouping_sets? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2476 def supports_grouping_sets?
2477   server_version >= 90500
2478 end

PostgreSQL 9.5+ supports GROUPING SETS

supports_insert_conflict? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2486 def supports_insert_conflict?
2487   server_version >= 90500
2488 end

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

supports_insert_select? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2481 def supports_insert_select?
2482   !@opts[:disable_insert_returning]
2483 end

True unless insert returning has been disabled for this dataset.

supports_lateral_subqueries? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2491 def supports_lateral_subqueries?
2492   server_version >= 90300
2493 end

PostgreSQL 9.3+ supports lateral subqueries

supports_merge? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2501 def supports_merge?
2502   server_version >= 150000
2503 end

PostgreSQL 15+ supports MERGE.

supports_modifying_joins? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2496 def supports_modifying_joins?
2497   true
2498 end

PostgreSQL supports modifying joined datasets

supports_nowait? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2506 def supports_nowait?
2507   true
2508 end

PostgreSQL supports NOWAIT.

supports_regexp? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2521 def supports_regexp?
2522   true
2523 end

PostgreSQL supports pattern matching via regular expressions

supports_returning? (type)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2512 def supports_returning?(type)
2513   if type == :merge
2514     server_version >= 170000
2515   else
2516     true
2517   end
2518 end

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

supports_skip_locked? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2526 def supports_skip_locked?
2527   server_version >= 90500
2528 end

PostgreSQL 9.5+ supports SKIP LOCKED.

supports_timestamp_timezones? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2533 def supports_timestamp_timezones?
2534   # SEQUEL6: Remove
2535   true
2536 end

PostgreSQL supports timezones in literal timestamps

supports_window_clause? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2540 def supports_window_clause?
2541   server_version >= 80400
2542 end

PostgreSQL 8.4+ supports WINDOW clause.

supports_window_function_frame_option? (option)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2551 def supports_window_function_frame_option?(option)
2552   case option
2553   when :rows, :range
2554     true
2555   when :offset
2556     server_version >= 90000
2557   when :groups, :exclude
2558     server_version >= 110000
2559   else
2560     false
2561   end
2562 end

Base support added in 8.4, offset supported added in 9.0, GROUPS and EXCLUDE support added in 11.0.

supports_window_functions? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2545 def supports_window_functions?
2546   server_version >= 80400
2547 end

PostgreSQL 8.4+ supports window functions

truncate (opts = OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2580 def truncate(opts = OPTS)
2581   if opts.empty?
2582     super()
2583   else
2584     clone(:truncate_opts=>opts).truncate
2585   end
2586 end

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

with_ties ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2591 def with_ties
2592   clone(:limit_with_ties=>true)
2593 end

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.

Protected Instance Methods

_import (columns, values, opts=OPTS)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2601 def _import(columns, values, opts=OPTS)
2602   if @opts[:returning]
2603     # no transaction: our multi_insert_sql_strategy should guarantee
2604     # that there's only ever a single statement.
2605     sql = multi_insert_sql(columns, values)[0]
2606     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
2607   elsif opts[:return] == :primary_key
2608     returning(insert_pk)._import(columns, values, opts)
2609   else
2610     super
2611   end
2612 end

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

to_prepared_statement (type, *a)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2614 def to_prepared_statement(type, *a)
2615   if type == :insert && !@opts.has_key?(:returning)
2616     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
2617   else
2618     super
2619   end
2620 end

Calls superclass method

Private Instance Methods

_merge_do_nothing_sql (sql, data)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2635 def _merge_do_nothing_sql(sql, data)
2636   sql << " THEN DO NOTHING"
2637 end

_merge_insert_sql (sql, data)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2625 def _merge_insert_sql(sql, data)
2626   sql << " THEN INSERT"
2627   columns, values = _parse_insert_sql_args(data[:values])
2628   _insert_columns_sql(sql, columns)
2629   if override = data[:override]
2630     sql << override
2631   end
2632   _insert_values_sql(sql, values)
2633 end

Append the INSERT sql used in a MERGE

_merge_when_sql (sql)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2640 def _merge_when_sql(sql)
2641   super
2642   insert_returning_sql(sql) if uses_returning?(:merge)
2643 end

Support MERGE RETURNING on PostgreSQL 17+.

Calls superclass method

_truncate_sql (table)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2646 def _truncate_sql(table)
2647   to = @opts[:truncate_opts] || OPTS
2648   "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}"
2649 end

Format TRUNCATE statement with PostgreSQL specific options.

aggregate_dataset_use_from_self? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2652 def aggregate_dataset_use_from_self?
2653   super || @opts[:values]
2654 end

Use from_self for aggregate dataset using VALUES.

Calls superclass method

check_truncation_allowed! ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2657 def check_truncation_allowed!
2658   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
2659   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
2660 end

Allow truncation of multiple source tables.

compound_dataset_sql_append (sql, ds)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2909 def compound_dataset_sql_append(sql, ds)
2910   sql << '('
2911   super
2912   sql << ')'
2913 end

PostgreSQL requires parentheses around compound datasets if they use CTEs, and using them in other places doesn’t hurt.

Calls superclass method

default_timestamp_format ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2663 def default_timestamp_format
2664   "'%Y-%m-%d %H:%M:%S.%6N%z'"
2665 end

The strftime format to use when literalizing the time.

delete_from_sql (sql)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2668 def delete_from_sql(sql)
2669   sql << ' FROM '
2670   source_list_append(sql, @opts[:from][0..0])
2671 end

Only include the primary table in the main delete clause

delete_using_sql (sql)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2674 def delete_using_sql(sql)
2675   join_from_sql(:USING, sql)
2676 end

Use USING to specify additional tables in a delete query

derived_column_list_sql_append (sql, column_aliases)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2680 def derived_column_list_sql_append(sql, column_aliases)
2681   c = false
2682   comma = ', '
2683   column_aliases.each do |a|
2684     sql << comma if c
2685     if a.is_a?(Array)
2686       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
2687       a, type = a
2688       identifier_append(sql, a)
2689       sql << " " << db.cast_type_literal(type).to_s
2690     else
2691       identifier_append(sql, a)
2692     end
2693     c ||= true
2694   end
2695 end

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

explain_sql_string_origin (opts)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2709 def explain_sql_string_origin(opts)
2710   origin = String.new
2711   origin << 'EXPLAIN '
2712 
2713   # simplecov:disable
2714   if server_version < 90000
2715     if opts[:analyze]
2716       origin << 'ANALYZE '
2717     end
2718 
2719     return origin
2720   end
2721   # simplecov:enable
2722 
2723   comma = nil
2724   paren = "("
2725 
2726   add_opt = lambda do |str, value|
2727     origin << paren if paren
2728     origin << comma if comma
2729     origin << str
2730     origin << " FALSE" unless value
2731     comma ||= ', '
2732     paren &&= nil
2733   end
2734 
2735   EXPLAIN_BOOLEAN_OPTIONS.each do |key, str|
2736     unless (value = opts[key]).nil?
2737       add_opt.call(str, value)
2738     end
2739   end
2740 
2741   EXPLAIN_NONBOOLEAN_OPTIONS.each do |key, e_opts|
2742     if value = opts[key]
2743       if str = e_opts[value]
2744         add_opt.call(str, true)
2745       else
2746         raise Sequel::Error, "unrecognized value for Dataset#explain #{key.inspect} option: #{value.inspect}"
2747       end
2748     end
2749   end
2750 
2751   origin << ') ' unless paren
2752   origin
2753 end

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

full_text_string_join (cols)

Source

     # File lib/sequel/adapters/shared/postgres.rb
3038 def full_text_string_join(cols)
3039   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
3040   cols = cols.zip([' '] * cols.length).flatten
3041   cols.pop
3042   SQL::StringExpression.new(:'||', *cols)
3043 end

Concatenate the expressions with a space in between

insert_conflict_sql (sql)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2756 def insert_conflict_sql(sql)
2757   if opts = @opts[:insert_conflict]
2758     sql << " ON CONFLICT"
2759 
2760     if target = opts[:constraint] 
2761       sql << " ON CONSTRAINT "
2762       identifier_append(sql, target)
2763     elsif target = opts[:target]
2764       sql << ' '
2765       identifier_append(sql, Array(target))
2766       if conflict_where = opts[:conflict_where]
2767         sql << " WHERE "
2768         literal_append(sql, conflict_where)
2769       end
2770     end
2771 
2772     if values = opts[:update]
2773       sql << " DO UPDATE SET "
2774       update_sql_values_hash(sql, values)
2775       where = opts[:update_where]
2776     elsif opts[:select]
2777       sql << " DO SELECT"
2778       select_lock_sql(sql)
2779       where = opts[:select_where]
2780     else
2781       sql << " DO NOTHING"
2782     end
2783 
2784     if where
2785       sql << " WHERE "
2786       literal_append(sql, where)
2787     end
2788   end
2789 end

Add ON CONFLICT clause if it should be used

insert_into_sql (sql)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2792 def insert_into_sql(sql)
2793   sql << " INTO "
2794   if (f = @opts[:from]) && f.length == 1
2795     identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first))
2796   else
2797     source_list_append(sql, f)
2798   end
2799 end

Include aliases when inserting into a single table on PostgreSQL 9.5+.

insert_override_sql (sql)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2816 def insert_override_sql(sql)
2817   case opts[:override]
2818   when :system
2819     sql << " OVERRIDING SYSTEM VALUE"
2820   when :user
2821     sql << " OVERRIDING USER VALUE"
2822   end
2823 end

Support OVERRIDING SYSTEM|USER VALUE in insert statements

insert_pk ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2802 def insert_pk
2803   (f = opts[:from]) && !f.empty? && (t = f.first)
2804 
2805   t = t.call(self) if t.is_a? Sequel::SQL::DelayedEvaluation
2806 
2807   case t
2808   when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
2809     if pk = db.primary_key(t)
2810       Sequel::SQL::Identifier.new(pk)
2811     end
2812   end
2813 end

Return the primary key to use for RETURNING in an INSERT statement

join_from_sql (type, sql)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2827 def join_from_sql(type, sql)
2828   if(from = @opts[:from][1..-1]).empty?
2829     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
2830   else
2831     sql << ' ' << type.to_s << ' '
2832     source_list_append(sql, from)
2833     select_join_sql(sql)
2834   end
2835 end

For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.

join_using_clause_using_sql_append (sql, using_columns)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2838 def join_using_clause_using_sql_append(sql, using_columns)
2839   if using_columns.is_a?(SQL::AliasedExpression)
2840     super(sql, using_columns.expression)
2841     sql << ' AS '
2842     identifier_append(sql, using_columns.alias)
2843   else
2844     super
2845   end
2846 end

Support table aliases for USING columns

Calls superclass method

literal_blob_append (sql, v)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2849 def literal_blob_append(sql, v)
2850   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
2851 end

Use a generic blob quoting method, hopefully overridden in one of the subadapter methods

literal_false ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2854 def literal_false
2855   'false'
2856 end

PostgreSQL uses FALSE for false values

literal_float (value)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2859 def literal_float(value)
2860   if value.finite?
2861     super
2862   elsif value.nan?
2863     "'NaN'"
2864   elsif value.infinite? == 1
2865     "'Infinity'"
2866   else
2867     "'-Infinity'"
2868   end
2869 end

PostgreSQL quotes NaN and Infinity.

Calls superclass method

literal_integer (v)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2872 def literal_integer(v)
2873   if v > 9223372036854775807 || v < -9223372036854775808
2874     literal_integer_outside_bigint_range(v)
2875   else
2876     v.to_s
2877   end
2878 end

Handle Ruby integers outside PostgreSQL bigint range specially.

literal_integer_outside_bigint_range (v)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2883 def literal_integer_outside_bigint_range(v)
2884   raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}"
2885 end

Raise IntegerOutsideBigintRange when attempting to literalize Ruby integer outside PostgreSQL bigint range, so PostgreSQL doesn’t treat the value as numeric.

literal_string_append (sql, v)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2888 def literal_string_append(sql, v)
2889   sql << "'" << v.gsub("'", "''") << "'"
2890 end

Assume that SQL standard quoting is on, per Sequel’s defaults

literal_true ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2893 def literal_true
2894   'true'
2895 end

PostgreSQL uses true for true values

multi_insert_sql_strategy ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2898 def multi_insert_sql_strategy
2899   :values
2900 end

PostgreSQL supports multiple rows in INSERT.

non_sql_option? (key)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2903 def non_sql_option?(key)
2904   super || key == :cursor || key == :insert_conflict
2905 end

Dataset options that do not affect the generated SQL.

Calls superclass method

requires_like_escape? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2917 def requires_like_escape?
2918   false
2919 end

Backslash is supported by default as the escape character on PostgreSQL, and using ESCAPE can break LIKE ANY() usage.

select_limit_sql (sql)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2922 def select_limit_sql(sql)
2923   l = @opts[:limit]
2924   o = @opts[:offset]
2925 
2926   return unless l || o
2927 
2928   if @opts[:limit_with_ties]
2929     if o
2930       sql << " OFFSET "
2931       literal_append(sql, o)
2932     end
2933 
2934     if l
2935       sql << " FETCH FIRST "
2936       literal_append(sql, l)
2937       sql << " ROWS WITH TIES"
2938     end
2939   else
2940     if l
2941       sql << " LIMIT "
2942       literal_append(sql, l)
2943     end
2944 
2945     if o
2946       sql << " OFFSET "
2947       literal_append(sql, o)
2948     end
2949   end
2950 end

Support FETCH FIRST WITH TIES on PostgreSQL 13+.

select_lock_sql (sql)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2954 def select_lock_sql(sql)
2955   lock = @opts[:lock]
2956   case lock
2957   when :share
2958     sql << ' FOR SHARE'
2959   when :no_key_update
2960     sql << ' FOR NO KEY UPDATE'
2961   when :key_share
2962     sql << ' FOR KEY SHARE'
2963   else
2964     super
2965   end
2966 
2967   if lock
2968     if @opts[:skip_locked]
2969       sql << " SKIP LOCKED"
2970     elsif @opts[:nowait]
2971       sql << " NOWAIT"
2972     end
2973   end
2974 end

Support FOR SHARE locking when using the :share lock style. Use SKIP LOCKED if skipping locked rows.

Calls superclass method

select_values_sql (sql)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2977 def select_values_sql(sql)
2978   sql << "VALUES "
2979   expression_list_append(sql, opts[:values])
2980 end

Support VALUES clause instead of the SELECT clause to return rows.

select_with_sql_base ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
2983 def select_with_sql_base
2984   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
2985 end

Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive

Calls superclass method

select_with_sql_cte (sql, cte)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2988 def select_with_sql_cte(sql, cte)
2989   super
2990   select_with_sql_cte_search_cycle(sql, cte)
2991 end

Support PostgreSQL 14+ CTE SEARCH/CYCLE clauses

Calls superclass method

select_with_sql_cte_search_cycle (sql, cte)

Source

     # File lib/sequel/adapters/shared/postgres.rb
2993 def select_with_sql_cte_search_cycle(sql, cte)
2994   if search_opts = cte[:search]
2995     sql << if search_opts[:type] == :breadth
2996       " SEARCH BREADTH FIRST BY "
2997     else
2998       " SEARCH DEPTH FIRST BY "
2999     end
3000 
3001     identifier_list_append(sql, Array(search_opts[:by]))
3002     sql << " SET "
3003     identifier_append(sql, search_opts[:set] || :ordercol)
3004   end
3005 
3006   if cycle_opts = cte[:cycle]
3007     sql << " CYCLE "
3008     identifier_list_append(sql, Array(cycle_opts[:columns]))
3009     sql << " SET "
3010     identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle)
3011     if cycle_opts.has_key?(:cycle_value)
3012       sql << " TO "
3013       literal_append(sql, cycle_opts[:cycle_value])
3014       sql << " DEFAULT "
3015       literal_append(sql, cycle_opts.fetch(:noncycle_value, false))
3016     end
3017     sql << " USING "
3018     identifier_append(sql, cycle_opts[:path_column] || :path)
3019   end
3020 end

server_version ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
3023 def server_version
3024   db.server_version(@opts[:server])
3025 end

The version of the database server

supports_filtered_aggregates? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
3028 def supports_filtered_aggregates?
3029   server_version >= 90400
3030 end

PostgreSQL 9.4+ supports the FILTER clause for aggregate functions.

supports_quoted_function_names? ()

Source

     # File lib/sequel/adapters/shared/postgres.rb
3033 def supports_quoted_function_names?
3034   true
3035 end

PostgreSQL supports quoted function names.

update_from_sql (sql)

Source

     # File lib/sequel/adapters/shared/postgres.rb
3046 def update_from_sql(sql)
3047   join_from_sql(:FROM, sql)
3048 end

Use FROM to specify additional tables in an update query

update_table_sql (sql)

Source

     # File lib/sequel/adapters/shared/postgres.rb
3051 def update_table_sql(sql)
3052   sql << ' '
3053   source_list_append(sql, @opts[:from][0..0])
3054 end

Only include the primary table in the main update clause