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
2970 def analyze
2971   explain(:analyze=>true)
2972 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
2977 def complex_expression_sql_append(sql, op, args)
2978   case op
2979   when :^
2980     j = ' # '
2981     c = false
2982     args.each do |a|
2983       sql << j if c
2984       literal_append(sql, a)
2985       c ||= true
2986     end
2987   when :ILIKE, :'NOT ILIKE'
2988     sql << '('
2989     literal_append(sql, args[0])
2990     sql << ' ' << op.to_s << ' '
2991     literal_append(sql, args[1])
2992     sql << ')'
2993   else
2994     super
2995   end
2996 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
3012 def disable_insert_returning
3013   clone(:disable_insert_returning=>true)
3014 end

empty?() click to toggle source

Always return false when using VALUES

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
3017 def empty?
3018   return false if @opts[:values]
3019   super
3020 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
3050 def explain(opts=OPTS)
3051   rows = clone(:append_sql=>explain_sql_string_origin(opts)).map(:'QUERY PLAN')
3052 
3053   if rows.length == 1
3054     rows[0]
3055   elsif rows.all?{|row| String === row}
3056     rows.join("\r\n") 
3057   # :nocov:
3058   else
3059     # This branch is unreachable in tests, but it seems better to just return
3060     # all rows than throw in error if this case actually happens.
3061     rows
3062   # :nocov:
3063   end
3064 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
3068 def for_key_share
3069   cached_lock_style_dataset(:_for_key_share_ds, :key_share)
3070 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
3095 def for_no_key_update
3096   cached_lock_style_dataset(:_for_no_key_update_ds, :no_key_update)
3097 end

for_portion_of(column, range, to=(arg_not_given=true)) click to toggle source

Set FOR PORTION OF clause for UPDATE and DELETE statements. The first argument is the range or multirange column. If two arguments are provided, the second argument is an expression with the same database type as the first argument. If three arguments are provided, the second specifies the inclusive start of the portion to update and the third specifies the exclusive end of portion to update. When using the three argument form, nil can be provided as the second or third argument to have the start or end of the portion be unbounded. Supported on PostgreSQL 19+. Example:

DB[:t].for_portion_of(:rc, Sequel.function(:int4range, 1, 2)).update(c: 3)
# UPDATE t FOR PORTION OF rc (int4range(1, 2)) SET c = 3

DB[:t].for_portion_of(:rc, 1, 2).update(c: 3)
# UPDATE t FOR PORTION OF rc FROM 1 TO 2 SET c = 3

     # File lib/sequel/adapters/shared/postgres.rb
3087 def for_portion_of(column, range, to=(arg_not_given=true))
3088   range = [range, to].freeze unless arg_not_given
3089   clone(:for_portion_of => [column, range].freeze)
3090 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
3100 def for_share
3101   cached_lock_style_dataset(:_for_share_ds, :share)
3102 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
3123 def full_text_search(cols, terms, opts = OPTS)
3124   lang = Sequel.cast(opts[:language] || 'simple', :regconfig)
3125 
3126   unless opts[:tsvector]
3127     phrase_cols = full_text_string_join(cols)
3128     cols = Sequel.function(:to_tsvector, lang, phrase_cols)
3129   end
3130 
3131   unless opts[:tsquery]
3132     phrase_terms = terms.is_a?(Array) || terms.is_a?(Set) ? Sequel.array_or_set_join(terms, ' | ') : terms
3133 
3134     query_func = case to_tsquery = opts[:to_tsquery]
3135     when :phrase, :plain
3136       :"#{to_tsquery}to_tsquery"
3137     when :websearch
3138       :"websearch_to_tsquery"
3139     else
3140       (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery
3141     end
3142 
3143     terms = Sequel.function(query_func, lang, phrase_terms)
3144   end
3145 
3146   ds = where(Sequel.lit(["", " @@ ", ""], cols, terms))
3147 
3148   if opts[:phrase]
3149     raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery]
3150     ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true)
3151   end
3152 
3153   if opts[:rank]
3154     ds = ds.reverse{ts_rank_cd(cols, terms)}
3155   end
3156 
3157   if opts[:headline]
3158     ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)}
3159   end
3160 
3161   ds
3162 end

insert(*values) click to toggle source

Insert given values into the database.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
3165 def insert(*values)
3166   if @opts[:returning]
3167     # Already know which columns to return, let the standard code handle it
3168     super
3169   elsif @opts[:sql] || @opts[:disable_insert_returning]
3170     # Raw SQL used or RETURNING disabled, just use the default behavior
3171     # and return nil since sequence is not known.
3172     super
3173     nil
3174   else
3175     # Force the use of RETURNING with the primary key value,
3176     # unless it has been disabled.
3177     returning(insert_pk).insert(*values){|r| return r.values.first}
3178   end
3179 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
3216 def insert_conflict(opts=OPTS)
3217   clone(:insert_conflict => opts)
3218 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
3226 def insert_ignore
3227   insert_conflict
3228 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
3233 def insert_select(*values)
3234   return unless supports_insert_select?
3235   # Handle case where query does not return a row
3236   server?(:default).with_sql_first(insert_select_sql(*values)) || false
3237 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
3241 def insert_select_sql(*values)
3242   ds = opts[:returning] ? self : returning
3243   ds.insert_sql(*values)
3244 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
3248 def join_table(type, table, expr=nil, options=OPTS, &block)
3249   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
3250     options = options.merge(:join_using=>true)
3251   end
3252   super
3253 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
3260 def lock(mode, opts=OPTS)
3261   if defined?(yield) # perform locking inside a transaction and yield to block
3262     @db.transaction(opts){lock(mode, opts); yield}
3263   else
3264     sql = 'LOCK TABLE '.dup
3265     source_list_append(sql, @opts[:from])
3266     mode = mode.to_s.upcase.strip
3267     unless LOCK_MODES.include?(mode)
3268       raise Error, "Unsupported lock mode: #{mode}"
3269     end
3270     sql << " IN #{mode} MODE"
3271     @db.execute(sql, opts)
3272   end
3273   nil
3274 end

merge(&block) click to toggle source

Support MERGE RETURNING on PostgreSQL 17+.

     # File lib/sequel/adapters/shared/postgres.rb
3277 def merge(&block)
3278   sql = merge_sql
3279   if uses_returning?(:merge)
3280     returning_fetch_rows(sql, &block)
3281   else
3282     execute_ddl(sql)
3283   end
3284 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
3295 def merge_delete_when_not_matched_by_source(&block)
3296   _merge_when(:type=>:delete_not_matched_by_source, &block)
3297 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
3308 def merge_do_nothing_when_matched(&block)
3309   _merge_when(:type=>:matched, &block)
3310 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
3321 def merge_do_nothing_when_not_matched(&block)
3322   _merge_when(:type=>:not_matched, &block)
3323 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
3334 def merge_do_nothing_when_not_matched_by_source(&block)
3335   _merge_when(:type=>:not_matched_by_source, &block)
3336 end

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

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
3339 def merge_insert(*values, &block)
3340   h = {:type=>:insert, :values=>values}
3341   if @opts[:override]
3342     h[:override] = insert_override_sql(String.new)
3343   end
3344   _merge_when(h, &block)
3345 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
3356 def merge_update_when_not_matched_by_source(values, &block)
3357   _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block)
3358 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
3363 def overriding_system_value
3364   clone(:override=>:system)
3365 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
3369 def overriding_user_value
3370   clone(:override=>:user)
3371 end

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

     # File lib/sequel/adapters/shared/postgres.rb
3373 def supports_cte?(type=:select)
3374   if type == :select
3375     server_version >= 80400
3376   else
3377     server_version >= 90100
3378   end
3379 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
3383 def supports_cte_in_subqueries?
3384   supports_cte?
3385 end

supports_distinct_on?() click to toggle source

DISTINCT ON is a PostgreSQL extension

     # File lib/sequel/adapters/shared/postgres.rb
3388 def supports_distinct_on?
3389   true
3390 end

supports_group_cube?() click to toggle source

PostgreSQL 9.5+ supports GROUP CUBE

     # File lib/sequel/adapters/shared/postgres.rb
3393 def supports_group_cube?
3394   server_version >= 90500
3395 end

supports_group_rollup?() click to toggle source

PostgreSQL 9.5+ supports GROUP ROLLUP

     # File lib/sequel/adapters/shared/postgres.rb
3398 def supports_group_rollup?
3399   server_version >= 90500
3400 end

supports_grouping_sets?() click to toggle source

PostgreSQL 9.5+ supports GROUPING SETS

     # File lib/sequel/adapters/shared/postgres.rb
3403 def supports_grouping_sets?
3404   server_version >= 90500
3405 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
3413 def supports_insert_conflict?
3414   server_version >= 90500
3415 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
3408 def supports_insert_select?
3409   !@opts[:disable_insert_returning]
3410 end

supports_lateral_subqueries?() click to toggle source

PostgreSQL 9.3+ supports lateral subqueries

     # File lib/sequel/adapters/shared/postgres.rb
3418 def supports_lateral_subqueries?
3419   server_version >= 90300
3420 end

supports_merge?() click to toggle source

PostgreSQL 15+ supports MERGE.

     # File lib/sequel/adapters/shared/postgres.rb
3428 def supports_merge?
3429   server_version >= 150000
3430 end

supports_modifying_joins?() click to toggle source

PostgreSQL supports modifying joined datasets

     # File lib/sequel/adapters/shared/postgres.rb
3423 def supports_modifying_joins?
3424   true
3425 end

supports_nowait?() click to toggle source

PostgreSQL supports NOWAIT.

     # File lib/sequel/adapters/shared/postgres.rb
3433 def supports_nowait?
3434   true
3435 end

supports_regexp?() click to toggle source

PostgreSQL supports pattern matching via regular expressions

     # File lib/sequel/adapters/shared/postgres.rb
3448 def supports_regexp?
3449   true
3450 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
3439 def supports_returning?(type)
3440   if type == :merge
3441     server_version >= 170000
3442   else
3443     true
3444   end
3445 end

supports_skip_locked?() click to toggle source

PostgreSQL 9.5+ supports SKIP LOCKED.

     # File lib/sequel/adapters/shared/postgres.rb
3453 def supports_skip_locked?
3454   server_version >= 90500
3455 end

supports_timestamp_timezones?() click to toggle source

PostgreSQL supports timezones in literal timestamps

     # File lib/sequel/adapters/shared/postgres.rb
3460 def supports_timestamp_timezones?
3461   # SEQUEL6: Remove
3462   true
3463 end

supports_window_clause?() click to toggle source

PostgreSQL 8.4+ supports WINDOW clause.

     # File lib/sequel/adapters/shared/postgres.rb
3467 def supports_window_clause?
3468   server_version >= 80400
3469 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
3478 def supports_window_function_frame_option?(option)
3479   case option
3480   when :rows, :range
3481     true
3482   when :offset
3483     server_version >= 90000
3484   when :groups, :exclude
3485     server_version >= 110000
3486   else
3487     false
3488   end
3489 end

supports_window_functions?() click to toggle source

PostgreSQL 8.4+ supports window functions

     # File lib/sequel/adapters/shared/postgres.rb
3472 def supports_window_functions?
3473   server_version >= 80400
3474 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
3507 def truncate(opts = OPTS)
3508   if opts.empty?
3509     super()
3510   else
3511     clone(:truncate_opts=>opts).truncate
3512   end
3513 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
3518 def with_ties
3519   clone(:limit_with_ties=>true)
3520 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
3528 def _import(columns, values, opts=OPTS)
3529   if @opts[:returning]
3530     # no transaction: our multi_insert_sql_strategy should guarantee
3531     # that there's only ever a single statement.
3532     sql = multi_insert_sql(columns, values)[0]
3533     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
3534   elsif opts[:return] == :primary_key
3535     returning(insert_pk)._import(columns, values, opts)
3536   else
3537     super
3538   end
3539 end

to_prepared_statement(type, *a) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
3541 def to_prepared_statement(type, *a)
3542   if type == :insert && !@opts.has_key?(:returning)
3543     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
3544   else
3545     super
3546   end
3547 end

Private Instance Methods

_merge_do_nothing_sql(sql, data) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
3562 def _merge_do_nothing_sql(sql, data)
3563   sql << " THEN DO NOTHING"
3564 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
3552 def _merge_insert_sql(sql, data)
3553   sql << " THEN INSERT"
3554   columns, values = _parse_insert_sql_args(data[:values])
3555   _insert_columns_sql(sql, columns)
3556   if override = data[:override]
3557     sql << override
3558   end
3559   _insert_values_sql(sql, values)
3560 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
3567 def _merge_when_sql(sql)
3568   super
3569   insert_returning_sql(sql) if uses_returning?(:merge)
3570 end

_truncate_sql(table) click to toggle source

Format TRUNCATE statement with PostgreSQL specific options.

     # File lib/sequel/adapters/shared/postgres.rb
3573 def _truncate_sql(table)
3574   to = @opts[:truncate_opts] || OPTS
3575   "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}"
3576 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
3579 def aggreate_dataset_use_from_self?
3580   super || @opts[:values]
3581 end

check_truncation_allowed!() click to toggle source

Allow truncation of multiple source tables.

     # File lib/sequel/adapters/shared/postgres.rb
3584 def check_truncation_allowed!
3585   raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group]
3586   raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join]
3587 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
3857 def compound_dataset_sql_append(sql, ds)
3858   sql << '('
3859   super
3860   sql << ')'
3861 end

default_timestamp_format() click to toggle source

The strftime format to use when literalizing the time.

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

delete_from_sql(sql) click to toggle source

Only include the primary table in the main delete clause. Support FOR PORTION OF.

     # File lib/sequel/adapters/shared/postgres.rb
3596 def delete_from_sql(sql)
3597   sql << ' FROM '
3598   table_for_portion_of_sql_append(sql)
3599 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
3602 def delete_using_sql(sql)
3603   join_from_sql(:USING, sql)
3604 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
3608 def derived_column_list_sql_append(sql, column_aliases)
3609   c = false
3610   comma = ', '
3611   column_aliases.each do |a|
3612     sql << comma if c
3613     if a.is_a?(Array)
3614       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
3615       a, type = a
3616       identifier_append(sql, a)
3617       sql << " " << db.cast_type_literal(type).to_s
3618     else
3619       identifier_append(sql, a)
3620     end
3621     c ||= true
3622   end
3623 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
3637 def explain_sql_string_origin(opts)
3638   origin = String.new
3639   origin << 'EXPLAIN '
3640 
3641   # :nocov:
3642   if server_version < 90000
3643     if opts[:analyze]
3644       origin << 'ANALYZE '
3645     end
3646 
3647     return origin
3648   end
3649   # :nocov:
3650 
3651   comma = nil
3652   paren = "("
3653 
3654   add_opt = lambda do |str, value|
3655     origin << paren if paren
3656     origin << comma if comma
3657     origin << str
3658     origin << " FALSE" unless value
3659     comma ||= ', '
3660     paren &&= nil
3661   end
3662 
3663   EXPLAIN_BOOLEAN_OPTIONS.each do |key, str|
3664     unless (value = opts[key]).nil?
3665       add_opt.call(str, value)
3666     end
3667   end
3668 
3669   EXPLAIN_NONBOOLEAN_OPTIONS.each do |key, e_opts|
3670     if value = opts[key]
3671       if str = e_opts[value]
3672         add_opt.call(str, true)
3673       else
3674         raise Sequel::Error, "unrecognized value for Dataset#explain #{key.inspect} option: #{value.inspect}"
3675       end
3676     end
3677   end
3678 
3679   origin << ') ' unless paren
3680   origin
3681 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
3986 def full_text_string_join(cols)
3987   cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')}
3988   cols = cols.zip([' '] * cols.length).flatten
3989   cols.pop
3990   SQL::StringExpression.new(:'||', *cols)
3991 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
3710 def insert_conflict_sql(sql)
3711   if opts = @opts[:insert_conflict]
3712     sql << " ON CONFLICT"
3713 
3714     if target = opts[:constraint] 
3715       sql << " ON CONSTRAINT "
3716       identifier_append(sql, target)
3717     elsif target = opts[:target]
3718       sql << ' '
3719       identifier_append(sql, Array(target))
3720       if conflict_where = opts[:conflict_where]
3721         sql << " WHERE "
3722         literal_append(sql, conflict_where)
3723       end
3724     end
3725 
3726     if values = opts[:update]
3727       sql << " DO UPDATE SET "
3728       update_sql_values_hash(sql, values)
3729       if update_where = opts[:update_where]
3730         sql << " WHERE "
3731         literal_append(sql, update_where)
3732       end
3733     else
3734       sql << " DO NOTHING"
3735     end
3736   end
3737 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
3740 def insert_into_sql(sql)
3741   sql << " INTO "
3742   if (f = @opts[:from]) && f.length == 1
3743     identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first))
3744   else
3745     source_list_append(sql, f)
3746   end
3747 end

insert_override_sql(sql) click to toggle source

Support OVERRIDING SYSTEM|USER VALUE in insert statements

     # File lib/sequel/adapters/shared/postgres.rb
3764 def insert_override_sql(sql)
3765   case opts[:override]
3766   when :system
3767     sql << " OVERRIDING SYSTEM VALUE"
3768   when :user
3769     sql << " OVERRIDING USER VALUE"
3770   end
3771 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
3750 def insert_pk
3751   (f = opts[:from]) && !f.empty? && (t = f.first)
3752 
3753   t = t.call(self) if t.is_a? Sequel::SQL::DelayedEvaluation
3754 
3755   case t
3756   when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier
3757     if pk = db.primary_key(t)
3758       Sequel::SQL::Identifier.new(pk)
3759     end
3760   end
3761 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
3775 def join_from_sql(type, sql)
3776   if(from = @opts[:from][1..-1]).empty?
3777     raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join]
3778   else
3779     sql << ' ' << type.to_s << ' '
3780     source_list_append(sql, from)
3781     select_join_sql(sql)
3782   end
3783 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
3786 def join_using_clause_using_sql_append(sql, using_columns)
3787   if using_columns.is_a?(SQL::AliasedExpression)
3788     super(sql, using_columns.expression)
3789     sql << ' AS '
3790     identifier_append(sql, using_columns.alias)
3791   else
3792     super
3793   end
3794 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
3797 def literal_blob_append(sql, v)
3798   sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'"
3799 end

literal_false() click to toggle source

PostgreSQL uses FALSE for false values

     # File lib/sequel/adapters/shared/postgres.rb
3802 def literal_false
3803   'false'
3804 end

literal_float(value) click to toggle source

PostgreSQL quotes NaN and Infinity.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
3807 def literal_float(value)
3808   if value.finite?
3809     super
3810   elsif value.nan?
3811     "'NaN'"
3812   elsif value.infinite? == 1
3813     "'Infinity'"
3814   else
3815     "'-Infinity'"
3816   end
3817 end

literal_integer(v) click to toggle source

Handle Ruby integers outside PostgreSQL bigint range specially.

     # File lib/sequel/adapters/shared/postgres.rb
3820 def literal_integer(v)
3821   if v > 9223372036854775807 || v < -9223372036854775808
3822     literal_integer_outside_bigint_range(v)
3823   else
3824     v.to_s
3825   end
3826 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
3831 def literal_integer_outside_bigint_range(v)
3832   raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}"
3833 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
3836 def literal_string_append(sql, v)
3837   sql << "'" << v.gsub("'", "''") << "'"
3838 end

literal_true() click to toggle source

PostgreSQL uses true for true values

     # File lib/sequel/adapters/shared/postgres.rb
3841 def literal_true
3842   'true'
3843 end

multi_insert_sql_strategy() click to toggle source

PostgreSQL supports multiple rows in INSERT.

     # File lib/sequel/adapters/shared/postgres.rb
3846 def multi_insert_sql_strategy
3847   :values
3848 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
3851 def non_sql_option?(key)
3852   super || key == :cursor || key == :insert_conflict
3853 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
3865 def requires_like_escape?
3866   false
3867 end

select_limit_sql(sql) click to toggle source

Support FETCH FIRST WITH TIES on PostgreSQL 13+.

     # File lib/sequel/adapters/shared/postgres.rb
3870 def select_limit_sql(sql)
3871   l = @opts[:limit]
3872   o = @opts[:offset]
3873 
3874   return unless l || o
3875 
3876   if @opts[:limit_with_ties]
3877     if o
3878       sql << " OFFSET "
3879       literal_append(sql, o)
3880     end
3881 
3882     if l
3883       sql << " FETCH FIRST "
3884       literal_append(sql, l)
3885       sql << " ROWS WITH TIES"
3886     end
3887   else
3888     if l
3889       sql << " LIMIT "
3890       literal_append(sql, l)
3891     end
3892 
3893     if o
3894       sql << " OFFSET "
3895       literal_append(sql, o)
3896     end
3897   end
3898 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
3902 def select_lock_sql(sql)
3903   lock = @opts[:lock]
3904   case lock
3905   when :share
3906     sql << ' FOR SHARE'
3907   when :no_key_update
3908     sql << ' FOR NO KEY UPDATE'
3909   when :key_share
3910     sql << ' FOR KEY SHARE'
3911   else
3912     super
3913   end
3914 
3915   if lock
3916     if @opts[:skip_locked]
3917       sql << " SKIP LOCKED"
3918     elsif @opts[:nowait]
3919       sql << " NOWAIT"
3920     end
3921   end
3922 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
3925 def select_values_sql(sql)
3926   sql << "VALUES "
3927   expression_list_append(sql, opts[:values])
3928 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
3931 def select_with_sql_base
3932   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
3933 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
3936 def select_with_sql_cte(sql, cte)
3937   super
3938   select_with_sql_cte_search_cycle(sql, cte)
3939 end

select_with_sql_cte_search_cycle(sql, cte) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
3941 def select_with_sql_cte_search_cycle(sql, cte)
3942   if search_opts = cte[:search]
3943     sql << if search_opts[:type] == :breadth
3944       " SEARCH BREADTH FIRST BY "
3945     else
3946       " SEARCH DEPTH FIRST BY "
3947     end
3948 
3949     identifier_list_append(sql, Array(search_opts[:by]))
3950     sql << " SET "
3951     identifier_append(sql, search_opts[:set] || :ordercol)
3952   end
3953 
3954   if cycle_opts = cte[:cycle]
3955     sql << " CYCLE "
3956     identifier_list_append(sql, Array(cycle_opts[:columns]))
3957     sql << " SET "
3958     identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle)
3959     if cycle_opts.has_key?(:cycle_value)
3960       sql << " TO "
3961       literal_append(sql, cycle_opts[:cycle_value])
3962       sql << " DEFAULT "
3963       literal_append(sql, cycle_opts.fetch(:noncycle_value, false))
3964     end
3965     sql << " USING "
3966     identifier_append(sql, cycle_opts[:path_column] || :path)
3967   end
3968 end

server_version() click to toggle source

The version of the database server

     # File lib/sequel/adapters/shared/postgres.rb
3971 def server_version
3972   db.server_version(@opts[:server])
3973 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
3976 def supports_filtered_aggregates?
3977   server_version >= 90400
3978 end

supports_quoted_function_names?() click to toggle source

PostgreSQL supports quoted function names.

     # File lib/sequel/adapters/shared/postgres.rb
3981 def supports_quoted_function_names?
3982   true
3983 end

table_for_portion_of_sql_append(sql) click to toggle source

Add FOR PORTION OF SQL if the dataset uses it.

     # File lib/sequel/adapters/shared/postgres.rb
3684 def table_for_portion_of_sql_append(sql)
3685   fpo_column, fpo_range = @opts[:for_portion_of]
3686   if fpo_column
3687     table, aliaz = split_alias(@opts[:from].first)
3688     source_list_append(sql, [table])
3689     sql << ' FOR PORTION OF '
3690     literal_append(sql, fpo_column)
3691 
3692     if fpo_range.is_a?(Array)
3693       fpo_start, fpo_end = fpo_range
3694       sql << ' FROM '
3695       literal_append(sql, fpo_start)
3696       sql << ' TO '
3697       literal_append(sql, fpo_end)
3698     else
3699       sql << ' ('
3700       literal_append(sql, fpo_range)
3701       sql << ')'
3702     end
3703     as_sql_append(sql, aliaz) if aliaz
3704   else
3705     source_list_append(sql, @opts[:from][0..0])
3706   end
3707 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
3994 def update_from_sql(sql)
3995   join_from_sql(:FROM, sql)
3996 end

update_table_sql(sql) click to toggle source

Support FOR PORTION OF.

     # File lib/sequel/adapters/shared/postgres.rb
3999 def update_table_sql(sql)
4000   sql << ' '
4001   table_for_portion_of_sql_append(sql)
4002 end