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



1827
1828
1829
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1827

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.



1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1834

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



1869
1870
1871
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1869

def disable_insert_returning
  clone(:disable_insert_returning=>true)
end

#empty? ⇒ Boolean

Always return false when using VALUES

Returns:

  • (Boolean) 


1874
1875
1876
1877
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1874

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

#explain(opts = OPTS) ⇒ Object

Return the results of an EXPLAIN query as a string



1880
1881
1882
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1880

def explain(opts=OPTS)
  with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n")
end

#for_share ⇒ Object

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



1885
1886
1887
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1885

def for_share
  lock_style(: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.



1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1908

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.join(' | ') : 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.



1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 1950

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



2001
2002
2003
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2001

def insert_conflict(opts=OPTS)
  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



2011
2012
2013
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2011

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.



2018
2019
2020
2021
2022
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2018

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.



2026
2027
2028
2029
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2026

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.



2033
2034
2035
2036
2037
2038
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2033

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.



2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2045

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_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



2070
2071
2072
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2070

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



2083
2084
2085
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2083

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

#merge_insert(*values, &block) ⇒ Object

Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.



2088
2089
2090
2091
2092
2093
2094
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2088

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



2099
2100
2101
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2099

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.



2105
2106
2107
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2105

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

#supports_cte?(type = :select) ⇒ Boolean

Returns:

  • (Boolean) 


2109
2110
2111
2112
2113
2114
2115
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2109

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

Returns:

  • (Boolean) 


2119
2120
2121
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2119

def supports_cte_in_subqueries?
  supports_cte?
end

#supports_distinct_on? ⇒ Boolean

DISTINCT ON is a PostgreSQL extension

Returns:

  • (Boolean) 


2124
2125
2126
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2124

def supports_distinct_on?
  true
end

#supports_group_cube? ⇒ Boolean

PostgreSQL 9.5+ supports GROUP CUBE

Returns:

  • (Boolean) 


2129
2130
2131
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2129

def supports_group_cube?
  server_version >= 90500
end

#supports_group_rollup? ⇒ Boolean

PostgreSQL 9.5+ supports GROUP ROLLUP

Returns:

  • (Boolean) 


2134
2135
2136
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2134

def supports_group_rollup?
  server_version >= 90500
end

#supports_grouping_sets? ⇒ Boolean

PostgreSQL 9.5+ supports GROUPING SETS

Returns:

  • (Boolean) 


2139
2140
2141
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2139

def supports_grouping_sets?
  server_version >= 90500
end

#supports_insert_conflict? ⇒ Boolean

PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.

Returns:

  • (Boolean) 


2149
2150
2151
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2149

def supports_insert_conflict?
  server_version >= 90500
end

#supports_insert_select? ⇒ Boolean

True unless insert returning has been disabled for this dataset.

Returns:

  • (Boolean) 


2144
2145
2146
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2144

def supports_insert_select?
  !@opts[:disable_insert_returning]
end

#supports_lateral_subqueries? ⇒ Boolean

PostgreSQL 9.3+ supports lateral subqueries

Returns:

  • (Boolean) 


2154
2155
2156
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2154

def supports_lateral_subqueries?
  server_version >= 90300
end

#supports_merge? ⇒ Boolean

PostgreSQL 15+ supports MERGE.

Returns:

  • (Boolean) 


2164
2165
2166
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2164

def supports_merge?
  server_version >= 150000
end

#supports_modifying_joins? ⇒ Boolean

PostgreSQL supports modifying joined datasets

Returns:

  • (Boolean) 


2159
2160
2161
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2159

def supports_modifying_joins?
  true
end

#supports_nowait? ⇒ Boolean

PostgreSQL supports NOWAIT.

Returns:

  • (Boolean) 


2169
2170
2171
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2169

def supports_nowait?
  true
end

#supports_regexp? ⇒ Boolean

PostgreSQL supports pattern matching via regular expressions

Returns:

  • (Boolean) 


2179
2180
2181
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2179

def supports_regexp?
  true
end

#supports_returning?(type) ⇒ Boolean

Returning is always supported.

Returns:

  • (Boolean) 


2174
2175
2176
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2174

def supports_returning?(type)
  true
end

#supports_skip_locked? ⇒ Boolean

PostgreSQL 9.5+ supports SKIP LOCKED.

Returns:

  • (Boolean) 


2184
2185
2186
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2184

def supports_skip_locked?
  server_version >= 90500
end

#supports_timestamp_timezones? ⇒ Boolean

PostgreSQL supports timezones in literal timestamps

Returns:

  • (Boolean) 


2191
2192
2193
2194
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2191

def supports_timestamp_timezones?
  # SEQUEL6: Remove
  true
end

#supports_window_clause? ⇒ Boolean

PostgreSQL 8.4+ supports WINDOW clause.

Returns:

  • (Boolean) 


2198
2199
2200
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2198

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.

Returns:

  • (Boolean) 


2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2209

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

Returns:

  • (Boolean) 


2203
2204
2205
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2203

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



2238
2239
2240
2241
2242
2243
2244
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2238

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.



2249
2250
2251
 
# File 'lib/sequel/adapters/shared/postgres.rb', line 2249

def with_ties
  clone(:limit_with_ties=>true)
end