module Sequel::Postgres::DatasetMethods

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

Constants

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
1827 def analyze
1828   explain(:analyze=>true)
1829 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
1834 def complex_expression_sql_append(sql, op, args)
1835   case op
1836   when :^
1837     j = ' # '
1838     c = false
1839     args.each do |a|
1840       sql << j if c
1841       literal_append(sql, a)
1842       c ||= true
1843     end
1844   when :ILIKE, :'NOT ILIKE'
1845     sql << '('
1846     literal_append(sql, args[0])
1847     sql << ' ' << op.to_s << ' '
1848     literal_append(sql, args[1])
1849     sql << ')'
1850   else
1851     super
1852   end
1853 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
1869 def disable_insert_returning
1870   clone(:disable_insert_returning=>true)
1871 end
empty?()

Always return false when using VALUES

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1874 def empty?
1875   return false if @opts[:values]
1876   super
1877 end
explain(opts=OPTS)

Return the results of an EXPLAIN query as a string

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1880 def explain(opts=OPTS)
1881   with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n")
1882 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
1885 def for_share
1886   lock_style(:share)
1887 end
insert(*values)

Insert given values into the database.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1950 def insert(*values)
1951   if @opts[:returning]
1952     # Already know which columns to return, let the standard code handle it
1953     super
1954   elsif @opts[:sql] || @opts[:disable_insert_returning]
1955     # Raw SQL used or RETURNING disabled, just use the default behavior
1956     # and return nil since sequence is not known.
1957     super
1958     nil
1959   else
1960     # Force the use of RETURNING with the primary key value,
1961     # unless it has been disabled.
1962     returning(insert_pk).insert(*values){|r| return r.values.first}
1963   end
1964 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
2001 def insert_conflict(opts=OPTS)
2002   clone(:insert_conflict => opts)
2003 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
2011 def insert_ignore
2012   insert_conflict
2013 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
2018 def insert_select(*values)
2019   return unless supports_insert_select?
2020   # Handle case where query does not return a row
2021   server?(:default).with_sql_first(insert_select_sql(*values)) || false
2022 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
2026 def insert_select_sql(*values)
2027   ds = opts[:returning] ? self : returning
2028   ds.insert_sql(*values)
2029 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
2033 def join_table(type, table, expr=nil, options=OPTS, &block)
2034   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
2035     options = options.merge(:join_using=>true)
2036   end
2037   super
2038 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
2045 def lock(mode, opts=OPTS)
2046   if defined?(yield) # perform locking inside a transaction and yield to block
2047     @db.transaction(opts){lock(mode, opts); yield}
2048   else
2049     sql = 'LOCK TABLE '.dup
2050     source_list_append(sql, @opts[:from])
2051     mode = mode.to_s.upcase.strip
2052     unless LOCK_MODES.include?(mode)
2053       raise Error, "Unsupported lock mode: #{mode}"
2054     end
2055     sql << " IN #{mode} MODE"
2056     @db.execute(sql, opts)
2057   end
2058   nil
2059 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
2070 def merge_do_nothing_when_matched(&block)
2071   _merge_when(:type=>:matched, &block)
2072 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
2083 def merge_do_nothing_when_not_matched(&block)
2084   _merge_when(:type=>:not_matched, &block)
2085 end
merge_insert(*values, &block)

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2088 def merge_insert(*values, &block)
2089   h = {:type=>:insert, :values=>values}
2090   if override = @opts[:override]
2091     h[:override] = insert_override_sql(String.new)
2092   end
2093   _merge_when(h, &block)
2094 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
2099 def overriding_system_value
2100   clone(:override=>:system)
2101 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
2105 def overriding_user_value
2106   clone(:override=>:user)
2107 end
supports_cte?(type=:select)
[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2109 def supports_cte?(type=:select)
2110   if type == :select
2111     server_version >= 80400
2112   else
2113     server_version >= 90100
2114   end
2115 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
2119 def supports_cte_in_subqueries?
2120   supports_cte?
2121 end
supports_distinct_on?()

DISTINCT ON is a PostgreSQL extension

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2124 def supports_distinct_on?
2125   true
2126 end
supports_group_cube?()

PostgreSQL 9.5+ supports GROUP CUBE

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

PostgreSQL 9.5+ supports GROUP ROLLUP

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

PostgreSQL 9.5+ supports GROUPING SETS

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

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

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

True unless insert returning has been disabled for this dataset.

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

PostgreSQL 9.3+ supports lateral subqueries

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

PostgreSQL 15+ supports MERGE.

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

PostgreSQL supports modifying joined datasets

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2159 def supports_modifying_joins?
2160   true
2161 end
supports_nowait?()

PostgreSQL supports NOWAIT.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2169 def supports_nowait?
2170   true
2171 end
supports_regexp?()

PostgreSQL supports pattern matching via regular expressions

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2179 def supports_regexp?
2180   true
2181 end
supports_returning?(type)

Returning is always supported.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2174 def supports_returning?(type)
2175   true
2176 end
supports_skip_locked?()

PostgreSQL 9.5+ supports SKIP LOCKED.

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

PostgreSQL supports timezones in literal timestamps

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2191 def supports_timestamp_timezones?
2192   # SEQUEL6: Remove
2193   true
2194 end
supports_window_clause?()

PostgreSQL 8.4+ supports WINDOW clause.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2198 def supports_window_clause?
2199   server_version >= 80400
2200 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
2209 def supports_window_function_frame_option?(option)
2210   case option
2211   when :rows, :range
2212     true
2213   when :offset
2214     server_version >= 90000
2215   when :groups, :exclude
2216     server_version >= 110000
2217   else
2218     false
2219   end
2220 end
supports_window_functions?()

PostgreSQL 8.4+ supports window functions

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2203 def supports_window_functions?
2204   server_version >= 80400
2205 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
2238 def truncate(opts = OPTS)
2239   if opts.empty?
2240     super()
2241   else
2242     clone(:truncate_opts=>opts).truncate
2243   end
2244 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
2249 def with_ties
2250   clone(:limit_with_ties=>true)
2251 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
2259 def _import(columns, values, opts=OPTS)
2260   if @opts[:returning]
2261     # no transaction: our multi_insert_sql_strategy should guarantee
2262     # that there's only ever a single statement.
2263     sql = multi_insert_sql(columns, values)[0]
2264     returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v}
2265   elsif opts[:return] == :primary_key
2266     returning(insert_pk)._import(columns, values, opts)
2267   else
2268     super
2269   end
2270 end
to_prepared_statement(type, *a)
[show source]
     # File lib/sequel/adapters/shared/postgres.rb
2272 def to_prepared_statement(type, *a)
2273   if type == :insert && !@opts.has_key?(:returning)
2274     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
2275   else
2276     super
2277   end
2278 end