Methods
Public Instance
- analyze
- complex_expression_sql_append
- disable_insert_returning
- empty?
- explain
- for_key_share
- for_no_key_update
- 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 2021 def analyze 2022 explain(:analyze=>true) 2023 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 2028 def complex_expression_sql_append(sql, op, args) 2029 case op 2030 when :^ 2031 j = ' # ' 2032 c = false 2033 args.each do |a| 2034 sql << j if c 2035 literal_append(sql, a) 2036 c ||= true 2037 end 2038 when :ILIKE, :'NOT ILIKE' 2039 sql << '(' 2040 literal_append(sql, args[0]) 2041 sql << ' ' << op.to_s << ' ' 2042 literal_append(sql, args[1]) 2043 sql << ')' 2044 else 2045 super 2046 end 2047 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 2063 def disable_insert_returning 2064 clone(:disable_insert_returning=>true) 2065 end
Always return false when using VALUES
# File lib/sequel/adapters/shared/postgres.rb 2068 def empty? 2069 return false if @opts[:values] 2070 super 2071 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 2101 def explain(opts=OPTS) 2102 rows = clone(:append_sql=>explain_sql_string_origin(opts)).map(:'QUERY PLAN') 2103 2104 if rows.length == 1 2105 rows[0] 2106 elsif rows.all?{|row| String === row} 2107 rows.join("\r\n") 2108 # :nocov: 2109 else 2110 # This branch is unreachable in tests, but it seems better to just return 2111 # all rows than throw in error if this case actually happens. 2112 rows 2113 # :nocov: 2114 end 2115 end
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+.
# File lib/sequel/adapters/shared/postgres.rb 2126 def for_no_key_update 2127 cached_lock_style_dataset(:_for_no_key_update_ds, :no_key_update) 2128 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 2154 def full_text_search(cols, terms, opts = OPTS) 2155 lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 2156 2157 unless opts[:tsvector] 2158 phrase_cols = full_text_string_join(cols) 2159 cols = Sequel.function(:to_tsvector, lang, phrase_cols) 2160 end 2161 2162 unless opts[:tsquery] 2163 phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 2164 2165 query_func = case to_tsquery = opts[:to_tsquery] 2166 when :phrase, :plain 2167 :"#{to_tsquery}to_tsquery" 2168 when :websearch 2169 :"websearch_to_tsquery" 2170 else 2171 (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 2172 end 2173 2174 terms = Sequel.function(query_func, lang, phrase_terms) 2175 end 2176 2177 ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) 2178 2179 if opts[:phrase] 2180 raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 2181 ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 2182 end 2183 2184 if opts[:rank] 2185 ds = ds.reverse{ts_rank_cd(cols, terms)} 2186 end 2187 2188 if opts[:headline] 2189 ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 2190 end 2191 2192 ds 2193 end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb 2196 def insert(*values) 2197 if @opts[:returning] 2198 # Already know which columns to return, let the standard code handle it 2199 super 2200 elsif @opts[:sql] || @opts[:disable_insert_returning] 2201 # Raw SQL used or RETURNING disabled, just use the default behavior 2202 # and return nil since sequence is not known. 2203 super 2204 nil 2205 else 2206 # Force the use of RETURNING with the primary key value, 2207 # unless it has been disabled. 2208 returning(insert_pk).insert(*values){|r| return r.values.first} 2209 end 2210 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 2247 def insert_conflict(opts=OPTS) 2248 clone(:insert_conflict => opts) 2249 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 2257 def insert_ignore 2258 insert_conflict 2259 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 2264 def insert_select(*values) 2265 return unless supports_insert_select? 2266 # Handle case where query does not return a row 2267 server?(:default).with_sql_first(insert_select_sql(*values)) || false 2268 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 2272 def insert_select_sql(*values) 2273 ds = opts[:returning] ? self : returning 2274 ds.insert_sql(*values) 2275 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 2279 def join_table(type, table, expr=nil, options=OPTS, &block) 2280 if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all? 2281 options = options.merge(:join_using=>true) 2282 end 2283 super 2284 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 2291 def lock(mode, opts=OPTS) 2292 if defined?(yield) # perform locking inside a transaction and yield to block 2293 @db.transaction(opts){lock(mode, opts); yield} 2294 else 2295 sql = 'LOCK TABLE '.dup 2296 source_list_append(sql, @opts[:from]) 2297 mode = mode.to_s.upcase.strip 2298 unless LOCK_MODES.include?(mode) 2299 raise Error, "Unsupported lock mode: #{mode}" 2300 end 2301 sql << " IN #{mode} MODE" 2302 @db.execute(sql, opts) 2303 end 2304 nil 2305 end
Support MERGE RETURNING on PostgreSQL 17+.
# File lib/sequel/adapters/shared/postgres.rb 2308 def merge(&block) 2309 sql = merge_sql 2310 if uses_returning?(:merge) 2311 returning_fetch_rows(sql, &block) 2312 else 2313 execute_ddl(sql) 2314 end 2315 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 2326 def merge_delete_when_not_matched_by_source(&block) 2327 _merge_when(:type=>:delete_not_matched_by_source, &block) 2328 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 2339 def merge_do_nothing_when_matched(&block) 2340 _merge_when(:type=>:matched, &block) 2341 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 2352 def merge_do_nothing_when_not_matched(&block) 2353 _merge_when(:type=>:not_matched, &block) 2354 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 2365 def merge_do_nothing_when_not_matched_by_source(&block) 2366 _merge_when(:type=>:not_matched_by_source, &block) 2367 end
Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2370 def merge_insert(*values, &block) 2371 h = {:type=>:insert, :values=>values} 2372 if @opts[:override] 2373 h[:override] = insert_override_sql(String.new) 2374 end 2375 _merge_when(h, &block) 2376 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 2387 def merge_update_when_not_matched_by_source(values, &block) 2388 _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block) 2389 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 2394 def overriding_system_value 2395 clone(:override=>:system) 2396 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 2400 def overriding_user_value 2401 clone(:override=>:user) 2402 end
# File lib/sequel/adapters/shared/postgres.rb 2404 def supports_cte?(type=:select) 2405 if type == :select 2406 server_version >= 80400 2407 else 2408 server_version >= 90100 2409 end 2410 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 2414 def supports_cte_in_subqueries? 2415 supports_cte? 2416 end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb 2419 def supports_distinct_on? 2420 true 2421 end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb 2424 def supports_group_cube? 2425 server_version >= 90500 2426 end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb 2429 def supports_group_rollup? 2430 server_version >= 90500 2431 end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb 2434 def supports_grouping_sets? 2435 server_version >= 90500 2436 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2444 def supports_insert_conflict? 2445 server_version >= 90500 2446 end
True unless insert returning has been disabled for this dataset.
# File lib/sequel/adapters/shared/postgres.rb 2439 def supports_insert_select? 2440 !@opts[:disable_insert_returning] 2441 end
PostgreSQL 9.3+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb 2449 def supports_lateral_subqueries? 2450 server_version >= 90300 2451 end
PostgreSQL 15+ supports MERGE.
# File lib/sequel/adapters/shared/postgres.rb 2459 def supports_merge? 2460 server_version >= 150000 2461 end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb 2454 def supports_modifying_joins? 2455 true 2456 end
PostgreSQL supports NOWAIT.
# File lib/sequel/adapters/shared/postgres.rb 2464 def supports_nowait? 2465 true 2466 end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb 2479 def supports_regexp? 2480 true 2481 end
MERGE RETURNING is supported on PostgreSQL 17+. Other RETURNING is supported on all supported PostgreSQL versions.
# File lib/sequel/adapters/shared/postgres.rb 2470 def supports_returning?(type) 2471 if type == :merge 2472 server_version >= 170000 2473 else 2474 true 2475 end 2476 end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb 2484 def supports_skip_locked? 2485 server_version >= 90500 2486 end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb 2491 def supports_timestamp_timezones? 2492 # SEQUEL6: Remove 2493 true 2494 end
PostgreSQL 8.4+ supports WINDOW clause.
# File lib/sequel/adapters/shared/postgres.rb 2498 def supports_window_clause? 2499 server_version >= 80400 2500 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 2509 def supports_window_function_frame_option?(option) 2510 case option 2511 when :rows, :range 2512 true 2513 when :offset 2514 server_version >= 90000 2515 when :groups, :exclude 2516 server_version >= 110000 2517 else 2518 false 2519 end 2520 end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb 2503 def supports_window_functions? 2504 server_version >= 80400 2505 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 2538 def truncate(opts = OPTS) 2539 if opts.empty? 2540 super() 2541 else 2542 clone(:truncate_opts=>opts).truncate 2543 end 2544 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 2549 def with_ties 2550 clone(:limit_with_ties=>true) 2551 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 2559 def _import(columns, values, opts=OPTS) 2560 if @opts[:returning] 2561 # no transaction: our multi_insert_sql_strategy should guarantee 2562 # that there's only ever a single statement. 2563 sql = multi_insert_sql(columns, values)[0] 2564 returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v} 2565 elsif opts[:return] == :primary_key 2566 returning(insert_pk)._import(columns, values, opts) 2567 else 2568 super 2569 end 2570 end
# File lib/sequel/adapters/shared/postgres.rb 2572 def to_prepared_statement(type, *a) 2573 if type == :insert && !@opts.has_key?(:returning) 2574 returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) 2575 else 2576 super 2577 end 2578 end