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
1555 def analyze
1556   explain(:analyze=>true)
1557 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
1562 def complex_expression_sql_append(sql, op, args)
1563   case op
1564   when :^
1565     j = ' # '
1566     c = false
1567     args.each do |a|
1568       sql << j if c
1569       literal_append(sql, a)
1570       c ||= true
1571     end
1572   when :ILIKE, :'NOT ILIKE'
1573     sql << '('
1574     literal_append(sql, args[0])
1575     sql << ' ' << op.to_s << ' '
1576     literal_append(sql, args[1])
1577     sql << " ESCAPE "
1578     literal_append(sql, "\\")
1579     sql << ')'
1580   else
1581     super
1582   end
1583 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
1599 def disable_insert_returning
1600   clone(:disable_insert_returning=>true)
1601 end
explain(opts=OPTS)

Return the results of an EXPLAIN query as a string

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

Insert given values into the database.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1672 def insert(*values)
1673   if @opts[:returning]
1674     # Already know which columns to return, let the standard code handle it
1675     super
1676   elsif @opts[:sql] || @opts[:disable_insert_returning]
1677     # Raw SQL used or RETURNING disabled, just use the default behavior
1678     # and return nil since sequence is not known.
1679     super
1680     nil
1681   else
1682     # Force the use of RETURNING with the primary key value,
1683     # unless it has been disabled.
1684     returning(insert_pk).insert(*values){|r| return r.values.first}
1685   end
1686 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
1723 def insert_conflict(opts=OPTS)
1724   clone(:insert_conflict => opts)
1725 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
1733 def insert_ignore
1734   insert_conflict
1735 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
1740 def insert_select(*values)
1741   return unless supports_insert_select?
1742   # Handle case where query does not return a row
1743   server?(:default).with_sql_first(insert_select_sql(*values)) || false
1744 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
1748 def insert_select_sql(*values)
1749   ds = opts[:returning] ? self : returning
1750   ds.insert_sql(*values)
1751 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
1755 def join_table(type, table, expr=nil, options=OPTS, &block)
1756   if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all?
1757     options = options.merge(:join_using=>true)
1758   end
1759   super
1760 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
1767 def lock(mode, opts=OPTS)
1768   if defined?(yield) # perform locking inside a transaction and yield to block
1769     @db.transaction(opts){lock(mode, opts); yield}
1770   else
1771     sql = 'LOCK TABLE '.dup
1772     source_list_append(sql, @opts[:from])
1773     mode = mode.to_s.upcase.strip
1774     unless LOCK_MODES.include?(mode)
1775       raise Error, "Unsupported lock mode: #{mode}"
1776     end
1777     sql << " IN #{mode} MODE"
1778     @db.execute(sql, opts)
1779   end
1780   nil
1781 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
1792 def merge_do_nothing_when_matched(&block)
1793   _merge_when(:type=>:matched, &block)
1794 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
1805 def merge_do_nothing_when_not_matched(&block)
1806   _merge_when(:type=>:not_matched, &block)
1807 end
merge_insert(*values, &block)

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1810 def merge_insert(*values, &block)
1811   h = {:type=>:insert, :values=>values}
1812   if override = @opts[:override]
1813     h[:override] = insert_override_sql(String.new)
1814   end
1815   _merge_when(h, &block)
1816 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
1821 def overriding_system_value
1822   clone(:override=>:system)
1823 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
1827 def overriding_user_value
1828   clone(:override=>:user)
1829 end
supports_cte?(type=:select)
[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1831 def supports_cte?(type=:select)
1832   if type == :select
1833     server_version >= 80400
1834   else
1835     server_version >= 90100
1836   end
1837 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
1841 def supports_cte_in_subqueries?
1842   supports_cte?
1843 end
supports_distinct_on?()

DISTINCT ON is a PostgreSQL extension

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1846 def supports_distinct_on?
1847   true
1848 end
supports_group_cube?()

PostgreSQL 9.5+ supports GROUP CUBE

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

PostgreSQL 9.5+ supports GROUP ROLLUP

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

PostgreSQL 9.5+ supports GROUPING SETS

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

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

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

True unless insert returning has been disabled for this dataset.

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

PostgreSQL 9.3+ supports lateral subqueries

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

PostgreSQL 15+ supports MERGE.

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

PostgreSQL supports modifying joined datasets

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1881 def supports_modifying_joins?
1882   true
1883 end
supports_nowait?()

PostgreSQL supports NOWAIT.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1891 def supports_nowait?
1892   true
1893 end
supports_regexp?()

PostgreSQL supports pattern matching via regular expressions

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1901 def supports_regexp?
1902   true
1903 end
supports_returning?(type)

Returning is always supported.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1896 def supports_returning?(type)
1897   true
1898 end
supports_skip_locked?()

PostgreSQL 9.5+ supports SKIP LOCKED.

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

PostgreSQL supports timezones in literal timestamps

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1911 def supports_timestamp_timezones?
1912   true
1913 end
supports_window_clause?()

PostgreSQL 8.4+ supports WINDOW clause.

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1916 def supports_window_clause?
1917   server_version >= 80400
1918 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
1927 def supports_window_function_frame_option?(option)
1928   case option
1929   when :rows, :range
1930     true
1931   when :offset
1932     server_version >= 90000
1933   when :groups, :exclude
1934     server_version >= 110000
1935   else
1936     false
1937   end
1938 end
supports_window_functions?()

PostgreSQL 8.4+ supports window functions

[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1921 def supports_window_functions?
1922   server_version >= 80400
1923 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
1956 def truncate(opts = OPTS)
1957   if opts.empty?
1958     super()
1959   else
1960     clone(:truncate_opts=>opts).truncate
1961   end
1962 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
1967 def with_ties
1968   clone(:limit_with_ties=>true)
1969 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
1977 def _import(columns, values, opts=OPTS)
1978   if @opts[:returning]
1979     statements = multi_insert_sql(columns, values)
1980     trans_opts = Hash[opts]
1981     trans_opts[:server] = @opts[:server]
1982     @db.transaction(trans_opts) do
1983       statements.map{|st| returning_fetch_rows(st)}
1984     end.first.map{|v| v.length == 1 ? v.values.first : v}
1985   elsif opts[:return] == :primary_key
1986     returning(insert_pk)._import(columns, values, opts)
1987   else
1988     super
1989   end
1990 end
to_prepared_statement(type, *a)
[show source]
     # File lib/sequel/adapters/shared/postgres.rb
1992 def to_prepared_statement(type, *a)
1993   if type == :insert && !@opts.has_key?(:returning)
1994     returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a)
1995   else
1996     super
1997   end
1998 end