Methods
Public Instance
- analyze
- complex_expression_sql_append
- disable_insert_returning
- empty?
- explain
- for_share
- full_text_search
- insert
- insert_conflict
- insert_ignore
- insert_select
- insert_select_sql
- join_table
- lock
- merge
- merge_delete_when_not_matched_by_source
- merge_do_nothing_when_matched
- merge_do_nothing_when_not_matched
- merge_do_nothing_when_not_matched_by_source
- merge_insert
- merge_update_when_not_matched_by_source
- overriding_system_value
- overriding_user_value
- supports_cte?
- supports_cte_in_subqueries?
- supports_distinct_on?
- supports_group_cube?
- supports_group_rollup?
- supports_grouping_sets?
- supports_insert_conflict?
- supports_insert_select?
- supports_lateral_subqueries?
- supports_merge?
- supports_modifying_joins?
- supports_nowait?
- supports_regexp?
- supports_returning?
- supports_skip_locked?
- supports_timestamp_timezones?
- supports_window_clause?
- supports_window_function_frame_option?
- supports_window_functions?
- truncate
- with_ties
Protected Instance
Included modules
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
Return the results of an EXPLAIN ANALYZE query as a string
# File lib/sequel/adapters/shared/postgres.rb 1839 def analyze 1840 explain(:analyze=>true) 1841 end
Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#), and use the ILIKE and NOT ILIKE operators.
# File lib/sequel/adapters/shared/postgres.rb 1846 def complex_expression_sql_append(sql, op, args) 1847 case op 1848 when :^ 1849 j = ' # ' 1850 c = false 1851 args.each do |a| 1852 sql << j if c 1853 literal_append(sql, a) 1854 c ||= true 1855 end 1856 when :ILIKE, :'NOT ILIKE' 1857 sql << '(' 1858 literal_append(sql, args[0]) 1859 sql << ' ' << op.to_s << ' ' 1860 literal_append(sql, args[1]) 1861 sql << ')' 1862 else 1863 super 1864 end 1865 end
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).
# File lib/sequel/adapters/shared/postgres.rb 1881 def disable_insert_returning 1882 clone(:disable_insert_returning=>true) 1883 end
Always return false when using VALUES
# File lib/sequel/adapters/shared/postgres.rb 1886 def empty? 1887 return false if @opts[:values] 1888 super 1889 end
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.
# File lib/sequel/adapters/shared/postgres.rb 1919 def explain(opts=OPTS) 1920 rows = clone(:append_sql=>explain_sql_string_origin(opts)).map(:'QUERY PLAN') 1921 1922 if rows.length == 1 1923 rows[0] 1924 elsif rows.all?{|row| String === row} 1925 rows.join("\r\n") 1926 # :nocov: 1927 else 1928 # This branch is unreachable in tests, but it seems better to just return 1929 # all rows than throw in error if this case actually happens. 1930 rows 1931 # :nocov: 1932 end 1933 end
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. |
# File lib/sequel/adapters/shared/postgres.rb 1959 def full_text_search(cols, terms, opts = OPTS) 1960 lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 1961 1962 unless opts[:tsvector] 1963 phrase_cols = full_text_string_join(cols) 1964 cols = Sequel.function(:to_tsvector, lang, phrase_cols) 1965 end 1966 1967 unless opts[:tsquery] 1968 phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 1969 1970 query_func = case to_tsquery = opts[:to_tsquery] 1971 when :phrase, :plain 1972 :"#{to_tsquery}to_tsquery" 1973 when :websearch 1974 :"websearch_to_tsquery" 1975 else 1976 (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 1977 end 1978 1979 terms = Sequel.function(query_func, lang, phrase_terms) 1980 end 1981 1982 ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) 1983 1984 if opts[:phrase] 1985 raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 1986 ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 1987 end 1988 1989 if opts[:rank] 1990 ds = ds.reverse{ts_rank_cd(cols, terms)} 1991 end 1992 1993 if opts[:headline] 1994 ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 1995 end 1996 1997 ds 1998 end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb 2001 def insert(*values) 2002 if @opts[:returning] 2003 # Already know which columns to return, let the standard code handle it 2004 super 2005 elsif @opts[:sql] || @opts[:disable_insert_returning] 2006 # Raw SQL used or RETURNING disabled, just use the default behavior 2007 # and return nil since sequence is not known. 2008 super 2009 nil 2010 else 2011 # Force the use of RETURNING with the primary key value, 2012 # unless it has been disabled. 2013 returning(insert_pk).insert(*values){|r| return r.values.first} 2014 end 2015 end
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)
# File lib/sequel/adapters/shared/postgres.rb 2052 def insert_conflict(opts=OPTS) 2053 clone(:insert_conflict => opts) 2054 end
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
# File lib/sequel/adapters/shared/postgres.rb 2062 def insert_ignore 2063 insert_conflict 2064 end
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.
# File lib/sequel/adapters/shared/postgres.rb 2069 def insert_select(*values) 2070 return unless supports_insert_select? 2071 # Handle case where query does not return a row 2072 server?(:default).with_sql_first(insert_select_sql(*values)) || false 2073 end
The SQL to use for an insert_select
, adds a RETURNING clause to the insert unless the RETURNING clause is already present.
# File lib/sequel/adapters/shared/postgres.rb 2077 def insert_select_sql(*values) 2078 ds = opts[:returning] ? self : returning 2079 ds.insert_sql(*values) 2080 end
Support SQL::AliasedExpression as expr to setup a USING join with a table alias for the USING columns.
# File lib/sequel/adapters/shared/postgres.rb 2084 def join_table(type, table, expr=nil, options=OPTS, &block) 2085 if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all? 2086 options = options.merge(:join_using=>true) 2087 end 2088 super 2089 end
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.
# File lib/sequel/adapters/shared/postgres.rb 2096 def lock(mode, opts=OPTS) 2097 if defined?(yield) # perform locking inside a transaction and yield to block 2098 @db.transaction(opts){lock(mode, opts); yield} 2099 else 2100 sql = 'LOCK TABLE '.dup 2101 source_list_append(sql, @opts[:from]) 2102 mode = mode.to_s.upcase.strip 2103 unless LOCK_MODES.include?(mode) 2104 raise Error, "Unsupported lock mode: #{mode}" 2105 end 2106 sql << " IN #{mode} MODE" 2107 @db.execute(sql, opts) 2108 end 2109 nil 2110 end
Support MERGE RETURNING on PostgreSQL 17+.
# File lib/sequel/adapters/shared/postgres.rb 2113 def merge(&block) 2114 sql = merge_sql 2115 if uses_returning?(:merge) 2116 returning_fetch_rows(sql, &block) 2117 else 2118 execute_ddl(sql) 2119 end 2120 end
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
# File lib/sequel/adapters/shared/postgres.rb 2131 def merge_delete_when_not_matched_by_source(&block) 2132 _merge_when(:type=>:delete_not_matched_by_source, &block) 2133 end
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
# File lib/sequel/adapters/shared/postgres.rb 2144 def merge_do_nothing_when_matched(&block) 2145 _merge_when(:type=>:matched, &block) 2146 end
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
# File lib/sequel/adapters/shared/postgres.rb 2157 def merge_do_nothing_when_not_matched(&block) 2158 _merge_when(:type=>:not_matched, &block) 2159 end
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
# File lib/sequel/adapters/shared/postgres.rb 2170 def merge_do_nothing_when_not_matched_by_source(&block) 2171 _merge_when(:type=>:not_matched_by_source, &block) 2172 end
Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2175 def merge_insert(*values, &block) 2176 h = {:type=>:insert, :values=>values} 2177 if @opts[:override] 2178 h[:override] = insert_override_sql(String.new) 2179 end 2180 _merge_when(h, &block) 2181 end
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
# File lib/sequel/adapters/shared/postgres.rb 2192 def merge_update_when_not_matched_by_source(values, &block) 2193 _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block) 2194 end
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.
# File lib/sequel/adapters/shared/postgres.rb 2199 def overriding_system_value 2200 clone(:override=>:system) 2201 end
Use OVERRIDING USER VALUE for INSERT statements, so that identity columns always use the sequence value instead of the user supplied value.
# File lib/sequel/adapters/shared/postgres.rb 2205 def overriding_user_value 2206 clone(:override=>:user) 2207 end
# File lib/sequel/adapters/shared/postgres.rb 2209 def supports_cte?(type=:select) 2210 if type == :select 2211 server_version >= 80400 2212 else 2213 server_version >= 90100 2214 end 2215 end
PostgreSQL supports using the WITH clause in subqueries if it supports using WITH at all (i.e. on PostgreSQL 8.4+).
# File lib/sequel/adapters/shared/postgres.rb 2219 def supports_cte_in_subqueries? 2220 supports_cte? 2221 end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb 2224 def supports_distinct_on? 2225 true 2226 end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb 2229 def supports_group_cube? 2230 server_version >= 90500 2231 end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb 2234 def supports_group_rollup? 2235 server_version >= 90500 2236 end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb 2239 def supports_grouping_sets? 2240 server_version >= 90500 2241 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2249 def supports_insert_conflict? 2250 server_version >= 90500 2251 end
True unless insert returning has been disabled for this dataset.
# File lib/sequel/adapters/shared/postgres.rb 2244 def supports_insert_select? 2245 !@opts[:disable_insert_returning] 2246 end
PostgreSQL 9.3+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb 2254 def supports_lateral_subqueries? 2255 server_version >= 90300 2256 end
PostgreSQL 15+ supports MERGE.
# File lib/sequel/adapters/shared/postgres.rb 2264 def supports_merge? 2265 server_version >= 150000 2266 end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb 2259 def supports_modifying_joins? 2260 true 2261 end
PostgreSQL supports NOWAIT.
# File lib/sequel/adapters/shared/postgres.rb 2269 def supports_nowait? 2270 true 2271 end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb 2284 def supports_regexp? 2285 true 2286 end
MERGE RETURNING is supported on PostgreSQL 17+. Other RETURNING is supported on all supported PostgreSQL versions.
# File lib/sequel/adapters/shared/postgres.rb 2275 def supports_returning?(type) 2276 if type == :merge 2277 server_version >= 170000 2278 else 2279 true 2280 end 2281 end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb 2289 def supports_skip_locked? 2290 server_version >= 90500 2291 end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb 2296 def supports_timestamp_timezones? 2297 # SEQUEL6: Remove 2298 true 2299 end
PostgreSQL 8.4+ supports WINDOW clause.
# File lib/sequel/adapters/shared/postgres.rb 2303 def supports_window_clause? 2304 server_version >= 80400 2305 end
Base support added in 8.4, offset supported added in 9.0, GROUPS and EXCLUDE support added in 11.0.
# File lib/sequel/adapters/shared/postgres.rb 2314 def supports_window_function_frame_option?(option) 2315 case option 2316 when :rows, :range 2317 true 2318 when :offset 2319 server_version >= 90000 2320 when :groups, :exclude 2321 server_version >= 110000 2322 else 2323 false 2324 end 2325 end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb 2308 def supports_window_functions? 2309 server_version >= 80400 2310 end
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
# File lib/sequel/adapters/shared/postgres.rb 2343 def truncate(opts = OPTS) 2344 if opts.empty? 2345 super() 2346 else 2347 clone(:truncate_opts=>opts).truncate 2348 end 2349 end
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.
# File lib/sequel/adapters/shared/postgres.rb 2354 def with_ties 2355 clone(:limit_with_ties=>true) 2356 end
Protected Instance methods
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.
# File lib/sequel/adapters/shared/postgres.rb 2364 def _import(columns, values, opts=OPTS) 2365 if @opts[:returning] 2366 # no transaction: our multi_insert_sql_strategy should guarantee 2367 # that there's only ever a single statement. 2368 sql = multi_insert_sql(columns, values)[0] 2369 returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v} 2370 elsif opts[:return] == :primary_key 2371 returning(insert_pk)._import(columns, values, opts) 2372 else 2373 super 2374 end 2375 end
# File lib/sequel/adapters/shared/postgres.rb 2377 def to_prepared_statement(type, *a) 2378 if type == :insert && !@opts.has_key?(:returning) 2379 returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) 2380 else 2381 super 2382 end 2383 end