Module: Sequel::Postgres::DatasetMethods

Includes:
UnmodifiedIdentifiers::DatasetMethods
Included in:
JDBC::Postgres::Dataset, Dataset
Defined in:
lib/sequel/adapters/shared/postgres.rb

Constant Summary collapse

NULL = 
LiteralString.new('NULL').freeze
LOCK_MODES = 
['ACCESS SHARE', 'ROW SHARE', 'ROW EXCLUSIVE', 'SHARE UPDATE EXCLUSIVE', 'SHARE', 'SHARE ROW EXCLUSIVE', 'EXCLUSIVE', 'ACCESS EXCLUSIVE'].each(&:freeze).freeze

Instance Method Summary collapse

Instance Method Details

#analyze ⇒ Object

Return the results of an EXPLAIN ANALYZE query as a string



2970
2971
2972
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2970

def analyze
  explain(:analyze=>true)
end

#complex_expression_sql_append(sql, op, args) ⇒ Object

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



2977
2978
2979
2980
2981
2982
2983
2984
2985
2986
2987
2988
2989
2990
2991
2992
2993
2994
2995
2996
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2977

def complex_expression_sql_append(sql, op, args)
  case op
  when :^
    j = ' # '
    c = false
    args.each do |a|
      sql << j if c
      literal_append(sql, a)
      c ||= true
    end
  when :ILIKE, :'NOT ILIKE'
    sql << '('
    literal_append(sql, args[0])
    sql << ' ' << op.to_s << ' '
    literal_append(sql, args[1])
    sql << ')'
  else
    super
  end
end

#disable_insert_returning ⇒ Object

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



3012
3013
3014
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3012

def disable_insert_returning
  clone(:disable_insert_returning=>true)
end

#empty? ⇒ Boolean

Always return false when using VALUES



3017
3018
3019
3020
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3017

def empty?
  return false if @opts[:values]
  super
end

#explain(opts = OPTS) ⇒ Object

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.



3050
3051
3052
3053
3054
3055
3056
3057
3058
3059
3060
3061
3062
3063
3064
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3050

def explain(opts=OPTS)
  rows = clone(:append_sql=>explain_sql_string_origin(opts)).map(:'QUERY PLAN')

  if rows.length == 1
    rows[0]
  elsif rows.all?{|row| String === row}
    rows.join("\r\n") 
  # simplecov:disable
  else
    # This branch is unreachable in tests, but it seems better to just return
    # all rows than throw in error if this case actually happens.
    rows
  # simplecov:enable
  end
end

#for_key_share ⇒ Object

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



3068
3069
3070
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3068

def for_key_share
  cached_lock_style_dataset(:_for_key_share_ds, :key_share)
end

#for_no_key_update ⇒ Object

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



3095
3096
3097
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3095

def for_no_key_update
  cached_lock_style_dataset(:_for_no_key_update_ds, :no_key_update)
end

#for_portion_of(column, range, to = (arg_not_given=true)) ⇒ Object

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



3087
3088
3089
3090
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3087

def for_portion_of(column, range, to=(arg_not_given=true))
  range = [range, to].freeze unless arg_not_given
  clone(:for_portion_of => [column, range].freeze)
end

#for_share ⇒ Object

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



3100
3101
3102
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3100

def for_share
  cached_lock_style_dataset(:_for_share_ds, :share)
end

#full_text_search(cols, terms, opts = OPTS) ⇒ Object

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.



3123
3124
3125
3126
3127
3128
3129
3130
3131
3132
3133
3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145
3146
3147
3148
3149
3150
3151
3152
3153
3154
3155
3156
3157
3158
3159
3160
3161
3162
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3123

def full_text_search(cols, terms, opts = OPTS)
  lang = Sequel.cast(opts[:language] || 'simple', :regconfig)

  unless opts[:tsvector]
    phrase_cols = full_text_string_join(cols)
    cols = Sequel.function(:to_tsvector, lang, phrase_cols)
  end

  unless opts[:tsquery]
    phrase_terms = terms.is_a?(Array) || terms.is_a?(Set) ? Sequel.array_or_set_join(terms, ' | ') : terms

    query_func = case to_tsquery = opts[:to_tsquery]
    when :phrase, :plain
      :"#{to_tsquery}to_tsquery"
    when :websearch
      :"websearch_to_tsquery"
    else
      (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery
    end

    terms = Sequel.function(query_func, lang, phrase_terms)
  end

  ds = where(Sequel.lit(["", " @@ ", ""], cols, terms))

  if opts[:phrase]
    raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery]
    ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true)
  end

  if opts[:rank]
    ds = ds.reverse{ts_rank_cd(cols, terms)}
  end

  if opts[:headline]
    ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)}
  end

  ds
end

#insert(*values) ⇒ Object

Insert given values into the database.



3165
3166
3167
3168
3169
3170
3171
3172
3173
3174
3175
3176
3177
3178
3179
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3165

def insert(*values)
  if @opts[:returning]
    # Already know which columns to return, let the standard code handle it
    super
  elsif @opts[:sql] || @opts[:disable_insert_returning]
    # Raw SQL used or RETURNING disabled, just use the default behavior
    # and return nil since sequence is not known.
    super
    nil
  else
    # Force the use of RETURNING with the primary key value,
    # unless it has been disabled.
    returning(insert_pk).insert(*values){|r| return r.values.first}
  end
end

#insert_conflict(opts = OPTS) ⇒ Object

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



3229
3230
3231
3232
3233
3234
3235
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3229

def insert_conflict(opts=OPTS)
  if opts[:select] && opts[:update]
    raise Error, "Cannot provide both :select and :update options to insert_conflict"
  end

  clone(:insert_conflict => opts)
end

#insert_ignore ⇒ Object

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



3243
3244
3245
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3243

def insert_ignore
  insert_conflict
end

#insert_select(*values) ⇒ Object

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.



3250
3251
3252
3253
3254
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3250

def insert_select(*values)
  return unless supports_insert_select?
  # Handle case where query does not return a row
  server?(:default).with_sql_first(insert_select_sql(*values)) || false
end

#insert_select_sql(*values) ⇒ Object

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



3258
3259
3260
3261
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3258

def insert_select_sql(*values)
  ds = opts[:returning] ? self : returning
  ds.insert_sql(*values)
end

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

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



3265
3266
3267
3268
3269
3270
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3265

def join_table(type, table, expr=nil, options=OPTS, &block)
  if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
    options = options.merge(:join_using=>true)
  end
  super
end

#lock(mode, opts = OPTS) ⇒ Object

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.



3277
3278
3279
3280
3281
3282
3283
3284
3285
3286
3287
3288
3289
3290
3291
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3277

def lock(mode, opts=OPTS)
  if defined?(yield) # perform locking inside a transaction and yield to block
    @db.transaction(opts){lock(mode, opts); yield}
  else
    sql = 'LOCK TABLE '.dup
    source_list_append(sql, @opts[:from])
    mode = mode.to_s.upcase.strip
    unless LOCK_MODES.include?(mode)
      raise Error, "Unsupported lock mode: #{mode}"
    end
    sql << " IN #{mode} MODE"
    @db.execute(sql, opts)
  end
  nil
end

#merge(&block) ⇒ Object

Support MERGE RETURNING on PostgreSQL 17+.



3294
3295
3296
3297
3298
3299
3300
3301
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3294

def merge(&block)
  sql = merge_sql
  if uses_returning?(:merge)
    returning_fetch_rows(sql, &block)
  else
    execute_ddl(sql)
  end
end

#merge_delete_when_not_matched_by_source(&block) ⇒ Object

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



3312
3313
3314
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3312

def merge_delete_when_not_matched_by_source(&block)
  _merge_when(:type=>:delete_not_matched_by_source, &block)
end

#merge_do_nothing_when_matched(&block) ⇒ Object

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



3325
3326
3327
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3325

def merge_do_nothing_when_matched(&block)
  _merge_when(:type=>:matched, &block)
end

#merge_do_nothing_when_not_matched(&block) ⇒ Object

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



3338
3339
3340
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3338

def merge_do_nothing_when_not_matched(&block)
  _merge_when(:type=>:not_matched, &block)
end

#merge_do_nothing_when_not_matched_by_source(&block) ⇒ Object

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



3351
3352
3353
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3351

def merge_do_nothing_when_not_matched_by_source(&block)
  _merge_when(:type=>:not_matched_by_source, &block)
end

#merge_insert(*values, &block) ⇒ Object

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.



3356
3357
3358
3359
3360
3361
3362
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3356

def merge_insert(*values, &block)
  h = {:type=>:insert, :values=>values}
  if @opts[:override]
    h[:override] = insert_override_sql(String.new)
  end
  _merge_when(h, &block)
end

#merge_update_when_not_matched_by_source(values, &block) ⇒ Object

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



3373
3374
3375
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3373

def merge_update_when_not_matched_by_source(values, &block)
  _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block)
end

#overriding_system_value ⇒ Object

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.



3380
3381
3382
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3380

def overriding_system_value
  clone(:override=>:system)
end

#overriding_user_value ⇒ Object

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



3386
3387
3388
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3386

def overriding_user_value
  clone(:override=>:user)
end

#supports_cte?(type = :select) ⇒ Boolean 



3390
3391
3392
3393
3394
3395
3396
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3390

def supports_cte?(type=:select)
  if type == :select
    server_version >= 80400
  else
    server_version >= 90100
  end
end

#supports_cte_in_subqueries? ⇒ Boolean

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



3400
3401
3402
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3400

def supports_cte_in_subqueries?
  supports_cte?
end

#supports_distinct_on? ⇒ Boolean

DISTINCT ON is a PostgreSQL extension



3405
3406
3407
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3405

def supports_distinct_on?
  true
end

#supports_group_cube? ⇒ Boolean

PostgreSQL 9.5+ supports GROUP CUBE



3410
3411
3412
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3410

def supports_group_cube?
  server_version >= 90500
end

#supports_group_rollup? ⇒ Boolean

PostgreSQL 9.5+ supports GROUP ROLLUP



3415
3416
3417
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3415

def supports_group_rollup?
  server_version >= 90500
end

#supports_grouping_sets? ⇒ Boolean

PostgreSQL 9.5+ supports GROUPING SETS



3420
3421
3422
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3420

def supports_grouping_sets?
  server_version >= 90500
end

#supports_insert_conflict? ⇒ Boolean

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.



3430
3431
3432
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3430

def supports_insert_conflict?
  server_version >= 90500
end

#supports_insert_select? ⇒ Boolean

True unless insert returning has been disabled for this dataset.



3425
3426
3427
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3425

def supports_insert_select?
  !@opts[:disable_insert_returning]
end

#supports_lateral_subqueries? ⇒ Boolean

PostgreSQL 9.3+ supports lateral subqueries



3435
3436
3437
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3435

def supports_lateral_subqueries?
  server_version >= 90300
end

#supports_merge? ⇒ Boolean

PostgreSQL 15+ supports MERGE.



3445
3446
3447
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3445

def supports_merge?
  server_version >= 150000
end

#supports_modifying_joins? ⇒ Boolean

PostgreSQL supports modifying joined datasets



3440
3441
3442
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3440

def supports_modifying_joins?
  true
end

#supports_nowait? ⇒ Boolean

PostgreSQL supports NOWAIT.



3450
3451
3452
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3450

def supports_nowait?
  true
end

#supports_regexp? ⇒ Boolean

PostgreSQL supports pattern matching via regular expressions



3465
3466
3467
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3465

def supports_regexp?
  true
end

#supports_returning?(type) ⇒ Boolean

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



3456
3457
3458
3459
3460
3461
3462
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3456

def supports_returning?(type)
  if type == :merge
    server_version >= 170000
  else
    true
  end
end

#supports_skip_locked? ⇒ Boolean

PostgreSQL 9.5+ supports SKIP LOCKED.



3470
3471
3472
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3470

def supports_skip_locked?
  server_version >= 90500
end

#supports_timestamp_timezones? ⇒ Boolean

PostgreSQL supports timezones in literal timestamps



3477
3478
3479
3480
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3477

def supports_timestamp_timezones?
  # SEQUEL6: Remove
  true
end

#supports_window_clause? ⇒ Boolean

PostgreSQL 8.4+ supports WINDOW clause.



3484
3485
3486
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3484

def supports_window_clause?
  server_version >= 80400
end

#supports_window_function_frame_option?(option) ⇒ Boolean

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



3495
3496
3497
3498
3499
3500
3501
3502
3503
3504
3505
3506
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3495

def supports_window_function_frame_option?(option)
  case option
  when :rows, :range
    true
  when :offset
    server_version >= 90000
  when :groups, :exclude
    server_version >= 110000
  else
    false
  end
end

#supports_window_functions? ⇒ Boolean

PostgreSQL 8.4+ supports window functions



3489
3490
3491
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3489

def supports_window_functions?
  server_version >= 80400
end

#truncate(opts = OPTS) ⇒ Object

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



3524
3525
3526
3527
3528
3529
3530
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3524

def truncate(opts = OPTS)
  if opts.empty?
    super()
  else
    clone(:truncate_opts=>opts).truncate
  end
end

#with_ties ⇒ Object

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.



3535
3536
3537
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 3535

def with_ties
  clone(:limit_with_ties=>true)
end