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
2034 def analyze
2035   explain(:analyze=>true)
2036 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
2041 def complex_expression_sql_append(sql, op, args)
2042   case op
2043   when :^
2044     j = ' # '
2045     c = false
2046     args.each do |a|
2047       sql << j if c
2048       literal_append(sql, a)
2049       c ||= true
2050     end
2051   when :ILIKE, :'NOT ILIKE'
2052     sql << '('
2053     literal_append(sql, args[0])
2054     sql << ' ' << op.to_s << ' '
2055     literal_append(sql, args[1])
2056     sql << ')'
2057   else
2058     super
2059   end
2060 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
2076 def disable_insert_returning
2077   clone(:disable_insert_returning=>true)
2078 end

empty?() click to toggle source

Always return false when using VALUES

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2081 def empty?
2082   return false if @opts[:values]
2083   super
2084 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
2114 def explain(opts=OPTS)
2115   rows = clone(:append_sql=>explain_sql_string_origin(opts)).map(:'QUERY PLAN')
2116 
2117   if rows.length == 1
2118     rows[0]
2119   elsif rows.all?{|row| String === row}
2120     rows.join("\r\n") 
2121   # :nocov:
2122   else
2123     # This branch is unreachable in tests, but it seems better to just return
2124     # all rows than throw in error if this case actually happens.
2125     rows
2126   # :nocov:
2127   end
2128 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
2132 def for_key_share
2133   cached_lock_style_dataset(:_for_key_share_ds, :key_share)
2134 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
2139 def for_no_key_update
2140   cached_lock_style_dataset(:_for_no_key_update_ds, :no_key_update)
2141 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
2144 def for_share
2145   cached_lock_style_dataset(:_for_share_ds, :share)
2146 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
2167 def full_text_search(cols, terms, opts = OPTS)
2168   lang = Sequel.cast(opts[:language] || 'simple', :regconfig)
2169 
2170   unless opts[:tsvector]
2171     phrase_cols = full_text_string_join(cols)
2172     cols = Sequel.function(:to_tsvector, lang, phrase_cols)
2173   end
2174 
2175   unless opts[:tsquery]
2176     phrase_terms = terms.is_a?(Array) || terms.is_a?(Set) ? Sequel.array_or_set_join(terms, ' | ') : terms
2177 
2178     query_func = case to_tsquery = opts[:to_tsquery]
2179     when :phrase, :plain
2180       :"#{to_tsquery}to_tsquery"
2181     when :websearch
2182       :"websearch_to_tsquery"
2183     else
2184       (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery
2185     end
2186 
2187     terms = Sequel.function(query_func, lang, phrase_terms)
2188   end
2189 
2190   ds = where(Sequel.lit(["", " @@ ", ""], cols, terms))
2191 
2192   if opts[:phrase]
2193     raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery]
2194     ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true)
2195   end
2196 
2197   if opts[:rank]
2198     ds = ds.reverse{ts_rank_cd(cols, terms)}
2199   end
2200 
2201   if opts[:headline]
2202     ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)}
2203   end
2204 
2205   ds
2206 end

insert(*values) click to toggle source

Insert given values into the database.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2209 def insert(*values)
2210   if @opts[:returning]
2211     # Already know which columns to return, let the standard code handle it
2212     super
2213   elsif @opts[:sql] || @opts[:disable_insert_returning]
2214     # Raw SQL used or RETURNING disabled, just use the default behavior
2215     # and return nil since sequence is not known.
2216     super
2217     nil
2218   else
2219     # Force the use of RETURNING with the primary key value,
2220     # unless it has been disabled.
2221     returning(insert_pk).insert(*values){|r| return r.values.first}
2222   end
2223 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
2260 def insert_conflict(opts=OPTS)
2261   clone(:insert_conflict => opts)
2262 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
2270 def insert_ignore
2271   insert_conflict
2272 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
2277 def insert_select(*values)
2278   return unless supports_insert_select?
2279   # Handle case where query does not return a row
2280   server?(:default).with_sql_first(insert_select_sql(*values)) || false
2281 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
2285 def insert_select_sql(*values)
2286   ds = opts[:returning] ? self : returning
2287   ds.insert_sql(*values)
2288 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
2292 def join_table(type, table, expr=nil, options=OPTS, &block)
2293   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
2294     options = options.merge(:join_using=>true)
2295   end
2296   super
2297 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
2304 def lock(mode, opts=OPTS)
2305   if defined?(yield) # perform locking inside a transaction and yield to block
2306     @db.transaction(opts){lock(mode, opts); yield}
2307   else
2308     sql = 'LOCK TABLE '.dup
2309     source_list_append(sql, @opts[:from])
2310     mode = mode.to_s.upcase.strip
2311     unless LOCK_MODES.include?(mode)
2312       raise Error, "Unsupported lock mode: #{mode}"
2313     end
2314     sql << " IN #{mode} MODE"
2315     @db.execute(sql, opts)
2316   end
2317   nil
2318 end

merge(&block) click to toggle source

Support MERGE RETURNING on PostgreSQL 17+.

     # File lib/sequel/adapters/shared/postgres.rb
2321 def merge(&block)
2322   sql = merge_sql
2323   if uses_returning?(:merge)
2324     returning_fetch_rows(sql, &block)
2325   else
2326     execute_ddl(sql)
2327   end
2328 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
2339 def merge_delete_when_not_matched_by_source(&block)
2340   _merge_when(:type=>:delete_not_matched_by_source, &block)
2341 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
2352 def merge_do_nothing_when_matched(&block)
2353   _merge_when(:type=>:matched, &block)
2354 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
2365 def merge_do_nothing_when_not_matched(&block)
2366   _merge_when(:type=>:not_matched, &block)
2367 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
2378 def merge_do_nothing_when_not_matched_by_source(&block)
2379   _merge_when(:type=>:not_matched_by_source, &block)
2380 end

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

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2383 def merge_insert(*values, &block)
2384   h = {:type=>:insert, :values=>values}
2385   if @opts[:override]
2386     h[:override] = insert_override_sql(String.new)
2387   end
2388   _merge_when(h, &block)
2389 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
2400 def merge_update_when_not_matched_by_source(values, &block)
2401   _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block)
2402 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
2407 def overriding_system_value
2408   clone(:override=>:system)
2409 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
2413 def overriding_user_value
2414   clone(:override=>:user)
2415 end

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

     # File lib/sequel/adapters/shared/postgres.rb
2417 def supports_cte?(type=:select)
2418   if type == :select
2419     server_version >= 80400
2420   else
2421     server_version >= 90100
2422   end
2423 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
2427 def supports_cte_in_subqueries?
2428   supports_cte?
2429 end

supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

     # File lib/sequel/adapters/shared/postgres.rb
2432 def supports_distinct_on?
2433   true
2434 end

supports_group_cube?() click to toggle source

PostgreSQL 9.5+ supports GROUP CUBE

     # File lib/sequel/adapters/shared/postgres.rb
2437 def supports_group_cube?
2438   server_version >= 90500
2439 end

supports_group_rollup?() click to toggle source

PostgreSQL 9.5+ supports GROUP ROLLUP

     # File lib/sequel/adapters/shared/postgres.rb
2442 def supports_group_rollup?
2443   server_version >= 90500
2444 end

supports_grouping_sets?() click to toggle source

PostgreSQL 9.5+ supports GROUPING SETS

     # File lib/sequel/adapters/shared/postgres.rb
2447 def supports_grouping_sets?
2448   server_version >= 90500
2449 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
2457 def supports_insert_conflict?
2458   server_version >= 90500
2459 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
2452 def supports_insert_select?
2453   !@opts[:disable_insert_returning]
2454 end

supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

     # File lib/sequel/adapters/shared/postgres.rb
2462 def supports_lateral_subqueries?
2463   server_version >= 90300
2464 end

supports_merge?() click to toggle source

PostgreSQL 15+ supports MERGE.

     # File lib/sequel/adapters/shared/postgres.rb
2472 def supports_merge?
2473   server_version >= 150000
2474 end

supports_modifying_joins?() click to toggle source

PostgreSQL supports modifying joined datasets

     # File lib/sequel/adapters/shared/postgres.rb
2467 def supports_modifying_joins?
2468   true
2469 end

supports_nowait?() click to toggle source

PostgreSQL supports NOWAIT.

     # File lib/sequel/adapters/shared/postgres.rb
2477 def supports_nowait?
2478   true
2479 end

supports_regexp?() click to toggle source

PostgreSQL supports pattern matching via regular expressions

     # File lib/sequel/adapters/shared/postgres.rb
2492 def supports_regexp?
2493   true
2494 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
2483 def supports_returning?(type)
2484   if type == :merge
2485     server_version >= 170000
2486   else
2487     true
2488   end
2489 end

supports_skip_locked?() click to toggle source

PostgreSQL 9.5+ supports SKIP LOCKED.

     # File lib/sequel/adapters/shared/postgres.rb
2497 def supports_skip_locked?
2498   server_version >= 90500
2499 end

supports_timestamp_timezones?() click to toggle source

PostgreSQL supports timezones in literal timestamps

     # File lib/sequel/adapters/shared/postgres.rb
2504 def supports_timestamp_timezones?
2505   # SEQUEL6: Remove
2506   true
2507 end

supports_window_clause?() click to toggle source

PostgreSQL 8.4+ supports WINDOW clause.

     # File lib/sequel/adapters/shared/postgres.rb
2511 def supports_window_clause?
2512   server_version >= 80400
2513 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
2522 def supports_window_function_frame_option?(option)
2523   case option
2524   when :rows, :range
2525     true
2526   when :offset
2527     server_version >= 90000
2528   when :groups, :exclude
2529     server_version >= 110000
2530   else
2531     false
2532   end
2533 end

supports_window_functions?() click to toggle source

PostgreSQL 8.4+ supports window functions

     # File lib/sequel/adapters/shared/postgres.rb
2516 def supports_window_functions?
2517   server_version >= 80400
2518 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
2551 def truncate(opts = OPTS)
2552   if opts.empty?
2553     super()
2554   else
2555     clone(:truncate_opts=>opts).truncate
2556   end
2557 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
2562 def with_ties
2563   clone(:limit_with_ties=>true)
2564 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
2572 def _import(columns, values, opts=OPTS)
2573   if @opts[:returning]
2574     # no transaction: our multi_insert_sql_strategy should guarantee
2575     # that there's only ever a single statement.
2576     sql = multi_insert_sql(columns, values)[0]
2577     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
2578   elsif opts[:return] == :primary_key
2579     returning(insert_pk)._import(columns, values, opts)
2580   else
2581     super
2582   end
2583 end

to_prepared_statement(type, *a) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2585 def to_prepared_statement(type, *a)
2586   if type == :insert && !@opts.has_key?(:returning)
2587     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
2588   else
2589     super
2590   end
2591 end

Private Instance Methods

_merge_do_nothing_sql(sql, data) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2606 def _merge_do_nothing_sql(sql, data)
2607   sql << " THEN DO NOTHING"
2608 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
2596 def _merge_insert_sql(sql, data)
2597   sql << " THEN INSERT"
2598   columns, values = _parse_insert_sql_args(data[:values])
2599   _insert_columns_sql(sql, columns)
2600   if override = data[:override]
2601     sql << override
2602   end
2603   _insert_values_sql(sql, values)
2604 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
2611 def _merge_when_sql(sql)
2612   super
2613   insert_returning_sql(sql) if uses_returning?(:merge)
2614 end

_truncate_sql(table) click to toggle source

Format TRUNCATE statement with PostgreSQL specific options.

     # File lib/sequel/adapters/shared/postgres.rb
2617 def _truncate_sql(table)
2618   to = @opts[:truncate_opts] || OPTS
2619   "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}"
2620 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
2623 def aggreate_dataset_use_from_self?
2624   super || @opts[:values]
2625 end

check_truncation_allowed!() click to toggle source

Allow truncation of multiple source tables.

     # File lib/sequel/adapters/shared/postgres.rb
2628 def check_truncation_allowed!
2629   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
2630   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
2631 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
2874 def compound_dataset_sql_append(sql, ds)
2875   sql << '('
2876   super
2877   sql << ')'
2878 end

default_timestamp_format() click to toggle source

The strftime format to use when literalizing the time.

     # File lib/sequel/adapters/shared/postgres.rb
2634 def default_timestamp_format
2635   "'%Y-%m-%d %H:%M:%S.%6N%z'"
2636 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
2639 def delete_from_sql(sql)
2640   sql << ' FROM '
2641   source_list_append(sql, @opts[:from][0..0])
2642 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
2645 def delete_using_sql(sql)
2646   join_from_sql(:USING, sql)
2647 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
2651 def derived_column_list_sql_append(sql, column_aliases)
2652   c = false
2653   comma = ', '
2654   column_aliases.each do |a|
2655     sql << comma if c
2656     if a.is_a?(Array)
2657       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
2658       a, type = a
2659       identifier_append(sql, a)
2660       sql << " " << db.cast_type_literal(type).to_s
2661     else
2662       identifier_append(sql, a)
2663     end
2664     c ||= true
2665   end
2666 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
2680 def explain_sql_string_origin(opts)
2681   origin = String.new
2682   origin << 'EXPLAIN '
2683 
2684   # :nocov:
2685   if server_version < 90000
2686     if opts[:analyze]
2687       origin << 'ANALYZE '
2688     end
2689 
2690     return origin
2691   end
2692   # :nocov:
2693 
2694   comma = nil
2695   paren = "("
2696 
2697   add_opt = lambda do |str, value|
2698     origin << paren if paren
2699     origin << comma if comma
2700     origin << str
2701     origin << " FALSE" unless value
2702     comma ||= ', '
2703     paren &&= nil
2704   end
2705 
2706   EXPLAIN_BOOLEAN_OPTIONS.each do |key, str|
2707     unless (value = opts[key]).nil?
2708       add_opt.call(str, value)
2709     end
2710   end
2711 
2712   EXPLAIN_NONBOOLEAN_OPTIONS.each do |key, e_opts|
2713     if value = opts[key]
2714       if str = e_opts[value]
2715         add_opt.call(str, true)
2716       else
2717         raise Sequel::Error, "unrecognized value for Dataset#explain #{key.inspect} option: #{value.inspect}"
2718       end
2719     end
2720   end
2721 
2722   origin << ') ' unless paren
2723   origin
2724 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
3003 def full_text_string_join(cols)
3004   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
3005   cols = cols.zip([' '] * cols.length).flatten
3006   cols.pop
3007   SQL::StringExpression.new(:'||', *cols)
3008 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
2727 def insert_conflict_sql(sql)
2728   if opts = @opts[:insert_conflict]
2729     sql << " ON CONFLICT"
2730 
2731     if target = opts[:constraint] 
2732       sql << " ON CONSTRAINT "
2733       identifier_append(sql, target)
2734     elsif target = opts[:target]
2735       sql << ' '
2736       identifier_append(sql, Array(target))
2737       if conflict_where = opts[:conflict_where]
2738         sql << " WHERE "
2739         literal_append(sql, conflict_where)
2740       end
2741     end
2742 
2743     if values = opts[:update]
2744       sql << " DO UPDATE SET "
2745       update_sql_values_hash(sql, values)
2746       if update_where = opts[:update_where]
2747         sql << " WHERE "
2748         literal_append(sql, update_where)
2749       end
2750     else
2751       sql << " DO NOTHING"
2752     end
2753   end
2754 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
2757 def insert_into_sql(sql)
2758   sql << " INTO "
2759   if (f = @opts[:from]) && f.length == 1
2760     identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first))
2761   else
2762     source_list_append(sql, f)
2763   end
2764 end

insert_override_sql(sql) click to toggle source

Support OVERRIDING SYSTEM|USER VALUE in insert statements

     # File lib/sequel/adapters/shared/postgres.rb
2781 def insert_override_sql(sql)
2782   case opts[:override]
2783   when :system
2784     sql << " OVERRIDING SYSTEM VALUE"
2785   when :user
2786     sql << " OVERRIDING USER VALUE"
2787   end
2788 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
2767 def insert_pk
2768   (f = opts[:from]) && !f.empty? && (t = f.first)
2769 
2770   t = t.call(self) if t.is_a? Sequel::SQL::DelayedEvaluation
2771 
2772   case t
2773   when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
2774     if pk = db.primary_key(t)
2775       Sequel::SQL::Identifier.new(pk)
2776     end
2777   end
2778 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
2792 def join_from_sql(type, sql)
2793   if(from = @opts[:from][1..-1]).empty?
2794     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
2795   else
2796     sql << ' ' << type.to_s << ' '
2797     source_list_append(sql, from)
2798     select_join_sql(sql)
2799   end
2800 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
2803 def join_using_clause_using_sql_append(sql, using_columns)
2804   if using_columns.is_a?(SQL::AliasedExpression)
2805     super(sql, using_columns.expression)
2806     sql << ' AS '
2807     identifier_append(sql, using_columns.alias)
2808   else
2809     super
2810   end
2811 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
2814 def literal_blob_append(sql, v)
2815   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
2816 end

literal_false() click to toggle source

PostgreSQL uses FALSE for false values

     # File lib/sequel/adapters/shared/postgres.rb
2819 def literal_false
2820   'false'
2821 end

literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2824 def literal_float(value)
2825   if value.finite?
2826     super
2827   elsif value.nan?
2828     "'NaN'"
2829   elsif value.infinite? == 1
2830     "'Infinity'"
2831   else
2832     "'-Infinity'"
2833   end
2834 end

literal_integer(v) click to toggle source

Handle Ruby integers outside PostgreSQL bigint range specially.

     # File lib/sequel/adapters/shared/postgres.rb
2837 def literal_integer(v)
2838   if v > 9223372036854775807 || v < -9223372036854775808
2839     literal_integer_outside_bigint_range(v)
2840   else
2841     v.to_s
2842   end
2843 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
2848 def literal_integer_outside_bigint_range(v)
2849   raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}"
2850 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
2853 def literal_string_append(sql, v)
2854   sql << "'" << v.gsub("'", "''") << "'"
2855 end

literal_true() click to toggle source

PostgreSQL uses true for true values

     # File lib/sequel/adapters/shared/postgres.rb
2858 def literal_true
2859   'true'
2860 end

multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
2863 def multi_insert_sql_strategy
2864   :values
2865 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
2868 def non_sql_option?(key)
2869   super || key == :cursor || key == :insert_conflict
2870 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
2882 def requires_like_escape?
2883   false
2884 end

select_limit_sql(sql) click to toggle source

Support FETCH FIRST WITH TIES on PostgreSQL 13+.

     # File lib/sequel/adapters/shared/postgres.rb
2887 def select_limit_sql(sql)
2888   l = @opts[:limit]
2889   o = @opts[:offset]
2890 
2891   return unless l || o
2892 
2893   if @opts[:limit_with_ties]
2894     if o
2895       sql << " OFFSET "
2896       literal_append(sql, o)
2897     end
2898 
2899     if l
2900       sql << " FETCH FIRST "
2901       literal_append(sql, l)
2902       sql << " ROWS WITH TIES"
2903     end
2904   else
2905     if l
2906       sql << " LIMIT "
2907       literal_append(sql, l)
2908     end
2909 
2910     if o
2911       sql << " OFFSET "
2912       literal_append(sql, o)
2913     end
2914   end
2915 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
2919 def select_lock_sql(sql)
2920   lock = @opts[:lock]
2921   case lock
2922   when :share
2923     sql << ' FOR SHARE'
2924   when :no_key_update
2925     sql << ' FOR NO KEY UPDATE'
2926   when :key_share
2927     sql << ' FOR KEY SHARE'
2928   else
2929     super
2930   end
2931 
2932   if lock
2933     if @opts[:skip_locked]
2934       sql << " SKIP LOCKED"
2935     elsif @opts[:nowait]
2936       sql << " NOWAIT"
2937     end
2938   end
2939 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
2942 def select_values_sql(sql)
2943   sql << "VALUES "
2944   expression_list_append(sql, opts[:values])
2945 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
2948 def select_with_sql_base
2949   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
2950 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
2953 def select_with_sql_cte(sql, cte)
2954   super
2955   select_with_sql_cte_search_cycle(sql, cte)
2956 end

select_with_sql_cte_search_cycle(sql, cte) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2958 def select_with_sql_cte_search_cycle(sql, cte)
2959   if search_opts = cte[:search]
2960     sql << if search_opts[:type] == :breadth
2961       " SEARCH BREADTH FIRST BY "
2962     else
2963       " SEARCH DEPTH FIRST BY "
2964     end
2965 
2966     identifier_list_append(sql, Array(search_opts[:by]))
2967     sql << " SET "
2968     identifier_append(sql, search_opts[:set] || :ordercol)
2969   end
2970 
2971   if cycle_opts = cte[:cycle]
2972     sql << " CYCLE "
2973     identifier_list_append(sql, Array(cycle_opts[:columns]))
2974     sql << " SET "
2975     identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle)
2976     if cycle_opts.has_key?(:cycle_value)
2977       sql << " TO "
2978       literal_append(sql, cycle_opts[:cycle_value])
2979       sql << " DEFAULT "
2980       literal_append(sql, cycle_opts.fetch(:noncycle_value, false))
2981     end
2982     sql << " USING "
2983     identifier_append(sql, cycle_opts[:path_column] || :path)
2984   end
2985 end

server_version() click to toggle source

The version of the database server

     # File lib/sequel/adapters/shared/postgres.rb
2988 def server_version
2989   db.server_version(@opts[:server])
2990 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
2993 def supports_filtered_aggregates?
2994   server_version >= 90400
2995 end

supports_quoted_function_names?() click to toggle source

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
2998 def supports_quoted_function_names?
2999   true
3000 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
3011 def update_from_sql(sql)
3012   join_from_sql(:FROM, sql)
3013 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
3016 def update_table_sql(sql)
3017   sql << ' '
3018   source_list_append(sql, @opts[:from][0..0])
3019 end