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

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
2076 def disable_insert_returning
2077   clone(:disable_insert_returning=>true)
2078 end
empty?()

Always return false when using VALUES

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2081 def empty?
2082   return false if @opts[:values]
2083   super
2084 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
2114 def explain(opts=OPTS)
2115   rows = clone(:append_sql=>explain_sql_string_origin(opts)).map(:'QUERY PLAN')
2116 
2117   if rows.length == 1
2118     rows[0]
2119   elsif rows.all?{|row| String === row}
2120     rows.join("\r\n") 
2121   # :nocov:
2122   else
2123     # This branch is unreachable in tests, but it seems better to just return
2124     # all rows than throw in error if this case actually happens.
2125     rows
2126   # :nocov:
2127   end
2128 end
for_key_share()

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
2132 def for_key_share
2133   cached_lock_style_dataset(:_for_key_share_ds, :key_share)
2134 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
2159 def for_no_key_update
2160   cached_lock_style_dataset(:_for_no_key_update_ds, :no_key_update)
2161 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
2151 def for_portion_of(column, range, to=(arg_not_given=true))
2152   range = [range, to].freeze unless arg_not_given
2153   clone(:for_portion_of => [column, range].freeze)
2154 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
2164 def for_share
2165   cached_lock_style_dataset(:_for_share_ds, :share)
2166 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
2187 def full_text_search(cols, terms, opts = OPTS)
2188   lang = Sequel.cast(opts[:language] || 'simple', :regconfig)
2189 
2190   unless opts[:tsvector]
2191     phrase_cols = full_text_string_join(cols)
2192     cols = Sequel.function(:to_tsvector, lang, phrase_cols)
2193   end
2194 
2195   unless opts[:tsquery]
2196     phrase_terms = terms.is_a?(Array) || terms.is_a?(Set) ? Sequel.array_or_set_join(terms, ' | ') : terms
2197 
2198     query_func = case to_tsquery = opts[:to_tsquery]
2199     when :phrase, :plain
2200       :"#{to_tsquery}to_tsquery"
2201     when :websearch
2202       :"websearch_to_tsquery"
2203     else
2204       (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery
2205     end
2206 
2207     terms = Sequel.function(query_func, lang, phrase_terms)
2208   end
2209 
2210   ds = where(Sequel.lit(["", " @@ ", ""], cols, terms))
2211 
2212   if opts[:phrase]
2213     raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery]
2214     ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true)
2215   end
2216 
2217   if opts[:rank]
2218     ds = ds.reverse{ts_rank_cd(cols, terms)}
2219   end
2220 
2221   if opts[:headline]
2222     ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)}
2223   end
2224 
2225   ds
2226 end
insert(*values)

Insert given values into the database.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2229 def insert(*values)
2230   if @opts[:returning]
2231     # Already know which columns to return, let the standard code handle it
2232     super
2233   elsif @opts[:sql] || @opts[:disable_insert_returning]
2234     # Raw SQL used or RETURNING disabled, just use the default behavior
2235     # and return nil since sequence is not known.
2236     super
2237     nil
2238   else
2239     # Force the use of RETURNING with the primary key value,
2240     # unless it has been disabled.
2241     returning(insert_pk).insert(*values){|r| return r.values.first}
2242   end
2243 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 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)
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2280 def insert_conflict(opts=OPTS)
2281   clone(:insert_conflict => opts)
2282 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
2290 def insert_ignore
2291   insert_conflict
2292 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
2297 def insert_select(*values)
2298   return unless supports_insert_select?
2299   # Handle case where query does not return a row
2300   server?(:default).with_sql_first(insert_select_sql(*values)) || false
2301 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
2305 def insert_select_sql(*values)
2306   ds = opts[:returning] ? self : returning
2307   ds.insert_sql(*values)
2308 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
2312 def join_table(type, table, expr=nil, options=OPTS, &block)
2313   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
2314     options = options.merge(:join_using=>true)
2315   end
2316   super
2317 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
2324 def lock(mode, opts=OPTS)
2325   if defined?(yield) # perform locking inside a transaction and yield to block
2326     @db.transaction(opts){lock(mode, opts); yield}
2327   else
2328     sql = 'LOCK TABLE '.dup
2329     source_list_append(sql, @opts[:from])
2330     mode = mode.to_s.upcase.strip
2331     unless LOCK_MODES.include?(mode)
2332       raise Error, "Unsupported lock mode: #{mode}"
2333     end
2334     sql << " IN #{mode} MODE"
2335     @db.execute(sql, opts)
2336   end
2337   nil
2338 end
merge(&block)

Support MERGE RETURNING on PostgreSQL 17+.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2341 def merge(&block)
2342   sql = merge_sql
2343   if uses_returning?(:merge)
2344     returning_fetch_rows(sql, &block)
2345   else
2346     execute_ddl(sql)
2347   end
2348 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
2359 def merge_delete_when_not_matched_by_source(&block)
2360   _merge_when(:type=>:delete_not_matched_by_source, &block)
2361 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
2372 def merge_do_nothing_when_matched(&block)
2373   _merge_when(:type=>:matched, &block)
2374 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
2385 def merge_do_nothing_when_not_matched(&block)
2386   _merge_when(:type=>:not_matched, &block)
2387 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
2398 def merge_do_nothing_when_not_matched_by_source(&block)
2399   _merge_when(:type=>:not_matched_by_source, &block)
2400 end
merge_insert(*values, &block)

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2403 def merge_insert(*values, &block)
2404   h = {:type=>:insert, :values=>values}
2405   if @opts[:override]
2406     h[:override] = insert_override_sql(String.new)
2407   end
2408   _merge_when(h, &block)
2409 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
2420 def merge_update_when_not_matched_by_source(values, &block)
2421   _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block)
2422 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
2427 def overriding_system_value
2428   clone(:override=>:system)
2429 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
2433 def overriding_user_value
2434   clone(:override=>:user)
2435 end
supports_cte?(type=:select)
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2437 def supports_cte?(type=:select)
2438   if type == :select
2439     server_version >= 80400
2440   else
2441     server_version >= 90100
2442   end
2443 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
2447 def supports_cte_in_subqueries?
2448   supports_cte?
2449 end
supports_distinct_on?()

DISTINCT ON is a PostgreSQL extension

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2452 def supports_distinct_on?
2453   true
2454 end
supports_group_cube?()

PostgreSQL 9.5+ supports GROUP CUBE

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

PostgreSQL 9.5+ supports GROUP ROLLUP

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

PostgreSQL 9.5+ supports GROUPING SETS

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

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

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

True unless insert returning has been disabled for this dataset.

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

PostgreSQL 9.3+ supports lateral subqueries

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

PostgreSQL 15+ supports MERGE.

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

PostgreSQL supports modifying joined datasets

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2487 def supports_modifying_joins?
2488   true
2489 end
supports_nowait?()

PostgreSQL supports NOWAIT.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2497 def supports_nowait?
2498   true
2499 end
supports_regexp?()

PostgreSQL supports pattern matching via regular expressions

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2512 def supports_regexp?
2513   true
2514 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
2503 def supports_returning?(type)
2504   if type == :merge
2505     server_version >= 170000
2506   else
2507     true
2508   end
2509 end
supports_skip_locked?()

PostgreSQL 9.5+ supports SKIP LOCKED.

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

PostgreSQL supports timezones in literal timestamps

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2524 def supports_timestamp_timezones?
2525   # SEQUEL6: Remove
2526   true
2527 end
supports_window_clause?()

PostgreSQL 8.4+ supports WINDOW clause.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2531 def supports_window_clause?
2532   server_version >= 80400
2533 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
2542 def supports_window_function_frame_option?(option)
2543   case option
2544   when :rows, :range
2545     true
2546   when :offset
2547     server_version >= 90000
2548   when :groups, :exclude
2549     server_version >= 110000
2550   else
2551     false
2552   end
2553 end
supports_window_functions?()

PostgreSQL 8.4+ supports window functions

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2536 def supports_window_functions?
2537   server_version >= 80400
2538 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
2571 def truncate(opts = OPTS)
2572   if opts.empty?
2573     super()
2574   else
2575     clone(:truncate_opts=>opts).truncate
2576   end
2577 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
2582 def with_ties
2583   clone(:limit_with_ties=>true)
2584 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
2592 def _import(columns, values, opts=OPTS)
2593   if @opts[:returning]
2594     # no transaction: our multi_insert_sql_strategy should guarantee
2595     # that there's only ever a single statement.
2596     sql = multi_insert_sql(columns, values)[0]
2597     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
2598   elsif opts[:return] == :primary_key
2599     returning(insert_pk)._import(columns, values, opts)
2600   else
2601     super
2602   end
2603 end
to_prepared_statement(type, *a)
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
2605 def to_prepared_statement(type, *a)
2606   if type == :insert && !@opts.has_key?(:returning)
2607     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
2608   else
2609     super
2610   end
2611 end