module Sequel::Postgres::DatasetMethods

  1. lib/sequel/adapters/shared/postgres.rb

Constants

EXPLAIN_BOOLEAN_OPTIONS = {}  
EXPLAIN_NONBOOLEAN_OPTIONS = { :serialize => {:none=>"SERIALIZE NONE", :text=>"SERIALIZE TEXT", :binary=>"SERIALIZE BINARY"}.freeze, :format => {:text=>"FORMAT TEXT", :xml=>"FORMAT XML", :json=>"FORMAT JSON", :yaml=>"FORMAT YAML"}.freeze }.freeze  
LOCK_MODES = ['ACCESS SHARE', 'ROW SHARE', 'ROW EXCLUSIVE', 'SHARE UPDATE EXCLUSIVE', 'SHARE', 'SHARE ROW EXCLUSIVE', 'EXCLUSIVE', 'ACCESS EXCLUSIVE'].each(&:freeze).freeze  
NULL = LiteralString.new('NULL').freeze  

Public Instance methods

analyze()

Return the results of an EXPLAIN ANALYZE query as a string

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2970 def analyze
2971   explain(:analyze=>true)
2972 end
complex_expression_sql_append(sql, op, args)

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

[show source] 
     # 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()

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

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3012 def disable_insert_returning
3013   clone(:disable_insert_returning=>true)
3014 end
empty?()

Always return false when using VALUES

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3017 def empty?
3018   return false if @opts[:values]
3019   super
3020 end
explain(opts=OPTS)

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.

[show source] 
     # 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   # simplecov:disable
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   # simplecov:enable
3063   end
3064 end
for_key_share()

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

[show source] 
     # 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()

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

[show source] 
     # 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))

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
[show source] 
     # 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()

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

[show source] 
     # 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)

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.

[show source] 
     # 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)

Insert given values into the database.

[show source] 
     # 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)

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
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3229 def insert_conflict(opts=OPTS)
3230   if opts[:select] && opts[:update]
3231     raise Error, "Cannot provide both :select and :update options to insert_conflict"
3232   end
3233 
3234   clone(:insert_conflict => opts)
3235 end
insert_ignore()

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
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3243 def insert_ignore
3244   insert_conflict
3245 end
insert_select(*values)

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.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3250 def insert_select(*values)
3251   return unless supports_insert_select?
3252   # Handle case where query does not return a row
3253   server?(:default).with_sql_first(insert_select_sql(*values)) || false
3254 end
insert_select_sql(*values)

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

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3258 def insert_select_sql(*values)
3259   ds = opts[:returning] ? self : returning
3260   ds.insert_sql(*values)
3261 end
join_table(type, table, expr=nil, options=OPTS, &block)

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

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3265 def join_table(type, table, expr=nil, options=OPTS, &block)
3266   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
3267     options = options.merge(:join_using=>true)
3268   end
3269   super
3270 end
lock(mode, opts=OPTS)

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.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3277 def lock(mode, opts=OPTS)
3278   if defined?(yield) # perform locking inside a transaction and yield to block
3279     @db.transaction(opts){lock(mode, opts); yield}
3280   else
3281     sql = 'LOCK TABLE '.dup
3282     source_list_append(sql, @opts[:from])
3283     mode = mode.to_s.upcase.strip
3284     unless LOCK_MODES.include?(mode)
3285       raise Error, "Unsupported lock mode: #{mode}"
3286     end
3287     sql << " IN #{mode} MODE"
3288     @db.execute(sql, opts)
3289   end
3290   nil
3291 end
merge(&block)

Support MERGE RETURNING on PostgreSQL 17+.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3294 def merge(&block)
3295   sql = merge_sql
3296   if uses_returning?(:merge)
3297     returning_fetch_rows(sql, &block)
3298   else
3299     execute_ddl(sql)
3300   end
3301 end
merge_delete_when_not_matched_by_source(&block)

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
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3312 def merge_delete_when_not_matched_by_source(&block)
3313   _merge_when(:type=>:delete_not_matched_by_source, &block)
3314 end
merge_do_nothing_when_matched(&block)

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
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3325 def merge_do_nothing_when_matched(&block)
3326   _merge_when(:type=>:matched, &block)
3327 end
merge_do_nothing_when_not_matched(&block)

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
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3338 def merge_do_nothing_when_not_matched(&block)
3339   _merge_when(:type=>:not_matched, &block)
3340 end
merge_do_nothing_when_not_matched_by_source(&block)

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
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3351 def merge_do_nothing_when_not_matched_by_source(&block)
3352   _merge_when(:type=>:not_matched_by_source, &block)
3353 end
merge_insert(*values, &block)

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3356 def merge_insert(*values, &block)
3357   h = {:type=>:insert, :values=>values}
3358   if @opts[:override]
3359     h[:override] = insert_override_sql(String.new)
3360   end
3361   _merge_when(h, &block)
3362 end
merge_update_when_not_matched_by_source(values, &block)

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
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3373 def merge_update_when_not_matched_by_source(values, &block)
3374   _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block)
3375 end
overriding_system_value()

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.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3380 def overriding_system_value
3381   clone(:override=>:system)
3382 end
overriding_user_value()

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

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3386 def overriding_user_value
3387   clone(:override=>:user)
3388 end
supports_cte?(type=:select)
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3390 def supports_cte?(type=:select)
3391   if type == :select
3392     server_version >= 80400
3393   else
3394     server_version >= 90100
3395   end
3396 end
supports_cte_in_subqueries?()

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

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3400 def supports_cte_in_subqueries?
3401   supports_cte?
3402 end
supports_distinct_on?()

DISTINCT ON is a PostgreSQL extension

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3405 def supports_distinct_on?
3406   true
3407 end
supports_group_cube?()

PostgreSQL 9.5+ supports GROUP CUBE

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3410 def supports_group_cube?
3411   server_version >= 90500
3412 end
supports_group_rollup?()

PostgreSQL 9.5+ supports GROUP ROLLUP

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3415 def supports_group_rollup?
3416   server_version >= 90500
3417 end
supports_grouping_sets?()

PostgreSQL 9.5+ supports GROUPING SETS

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3420 def supports_grouping_sets?
3421   server_version >= 90500
3422 end
supports_insert_conflict?()

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3430 def supports_insert_conflict?
3431   server_version >= 90500
3432 end
supports_insert_select?()

True unless insert returning has been disabled for this dataset.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3425 def supports_insert_select?
3426   !@opts[:disable_insert_returning]
3427 end
supports_lateral_subqueries?()

PostgreSQL 9.3+ supports lateral subqueries

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3435 def supports_lateral_subqueries?
3436   server_version >= 90300
3437 end
supports_merge?()

PostgreSQL 15+ supports MERGE.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3445 def supports_merge?
3446   server_version >= 150000
3447 end
supports_modifying_joins?()

PostgreSQL supports modifying joined datasets

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3440 def supports_modifying_joins?
3441   true
3442 end
supports_nowait?()

PostgreSQL supports NOWAIT.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3450 def supports_nowait?
3451   true
3452 end
supports_regexp?()

PostgreSQL supports pattern matching via regular expressions

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3465 def supports_regexp?
3466   true
3467 end
supports_returning?(type)

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

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3456 def supports_returning?(type)
3457   if type == :merge
3458     server_version >= 170000
3459   else
3460     true
3461   end
3462 end
supports_skip_locked?()

PostgreSQL 9.5+ supports SKIP LOCKED.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3470 def supports_skip_locked?
3471   server_version >= 90500
3472 end
supports_timestamp_timezones?()

PostgreSQL supports timezones in literal timestamps

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3477 def supports_timestamp_timezones?
3478   # SEQUEL6: Remove
3479   true
3480 end
supports_window_clause?()

PostgreSQL 8.4+ supports WINDOW clause.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3484 def supports_window_clause?
3485   server_version >= 80400
3486 end
supports_window_function_frame_option?(option)

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

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3495 def supports_window_function_frame_option?(option)
3496   case option
3497   when :rows, :range
3498     true
3499   when :offset
3500     server_version >= 90000
3501   when :groups, :exclude
3502     server_version >= 110000
3503   else
3504     false
3505   end
3506 end
supports_window_functions?()

PostgreSQL 8.4+ supports window functions

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3489 def supports_window_functions?
3490   server_version >= 80400
3491 end
truncate(opts = OPTS)

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
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3524 def truncate(opts = OPTS)
3525   if opts.empty?
3526     super()
3527   else
3528     clone(:truncate_opts=>opts).truncate
3529   end
3530 end
with_ties()

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.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3535 def with_ties
3536   clone(:limit_with_ties=>true)
3537 end

Protected Instance methods

_import(columns, values, opts=OPTS)

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.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3545 def _import(columns, values, opts=OPTS)
3546   if @opts[:returning]
3547     # no transaction: our multi_insert_sql_strategy should guarantee
3548     # that there's only ever a single statement.
3549     sql = multi_insert_sql(columns, values)[0]
3550     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
3551   elsif opts[:return] == :primary_key
3552     returning(insert_pk)._import(columns, values, opts)
3553   else
3554     super
3555   end
3556 end
to_prepared_statement(type, *a)
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
3558 def to_prepared_statement(type, *a)
3559   if type == :insert && !@opts.has_key?(:returning)
3560     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
3561   else
3562     super
3563   end
3564 end