module Sequel::Postgres::DatasetMethods
Constants
- EXPLAIN_BOOLEAN_OPTIONS
- EXPLAIN_NONBOOLEAN_OPTIONS
- LOCK_MODES
- NULL
Public Instance Methods
Return the results of an EXPLAIN ANALYZE query as a string
# File lib/sequel/adapters/shared/postgres.rb 1914 def analyze 1915 explain(:analyze=>true) 1916 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 1921 def complex_expression_sql_append(sql, op, args) 1922 case op 1923 when :^ 1924 j = ' # ' 1925 c = false 1926 args.each do |a| 1927 sql << j if c 1928 literal_append(sql, a) 1929 c ||= true 1930 end 1931 when :ILIKE, :'NOT ILIKE' 1932 sql << '(' 1933 literal_append(sql, args[0]) 1934 sql << ' ' << op.to_s << ' ' 1935 literal_append(sql, args[1]) 1936 sql << ')' 1937 else 1938 super 1939 end 1940 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 1956 def disable_insert_returning 1957 clone(:disable_insert_returning=>true) 1958 end
Always return false when using VALUES
# File lib/sequel/adapters/shared/postgres.rb 1961 def empty? 1962 return false if @opts[:values] 1963 super 1964 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 1994 def explain(opts=OPTS) 1995 rows = clone(:append_sql=>explain_sql_string_origin(opts)).map(:'QUERY PLAN') 1996 1997 if rows.length == 1 1998 rows[0] 1999 elsif rows.all?{|row| String === row} 2000 rows.join("\r\n") 2001 # :nocov: 2002 else 2003 # This branch is unreachable in tests, but it seems better to just return 2004 # all rows than throw in error if this case actually happens. 2005 rows 2006 # :nocov: 2007 end 2008 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 2019 def for_no_key_update 2020 cached_lock_style_dataset(:_for_no_key_update_ds, :no_key_update) 2021 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 2047 def full_text_search(cols, terms, opts = OPTS) 2048 lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 2049 2050 unless opts[:tsvector] 2051 phrase_cols = full_text_string_join(cols) 2052 cols = Sequel.function(:to_tsvector, lang, phrase_cols) 2053 end 2054 2055 unless opts[:tsquery] 2056 phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 2057 2058 query_func = case to_tsquery = opts[:to_tsquery] 2059 when :phrase, :plain 2060 :"#{to_tsquery}to_tsquery" 2061 when :websearch 2062 :"websearch_to_tsquery" 2063 else 2064 (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 2065 end 2066 2067 terms = Sequel.function(query_func, lang, phrase_terms) 2068 end 2069 2070 ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) 2071 2072 if opts[:phrase] 2073 raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 2074 ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 2075 end 2076 2077 if opts[:rank] 2078 ds = ds.reverse{ts_rank_cd(cols, terms)} 2079 end 2080 2081 if opts[:headline] 2082 ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 2083 end 2084 2085 ds 2086 end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb 2089 def insert(*values) 2090 if @opts[:returning] 2091 # Already know which columns to return, let the standard code handle it 2092 super 2093 elsif @opts[:sql] || @opts[:disable_insert_returning] 2094 # Raw SQL used or RETURNING disabled, just use the default behavior 2095 # and return nil since sequence is not known. 2096 super 2097 nil 2098 else 2099 # Force the use of RETURNING with the primary key value, 2100 # unless it has been disabled. 2101 returning(insert_pk).insert(*values){|r| return r.values.first} 2102 end 2103 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 2140 def insert_conflict(opts=OPTS) 2141 clone(:insert_conflict => opts) 2142 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 2150 def insert_ignore 2151 insert_conflict 2152 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 2157 def insert_select(*values) 2158 return unless supports_insert_select? 2159 # Handle case where query does not return a row 2160 server?(:default).with_sql_first(insert_select_sql(*values)) || false 2161 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 2165 def insert_select_sql(*values) 2166 ds = opts[:returning] ? self : returning 2167 ds.insert_sql(*values) 2168 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 2172 def join_table(type, table, expr=nil, options=OPTS, &block) 2173 if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all? 2174 options = options.merge(:join_using=>true) 2175 end 2176 super 2177 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 2184 def lock(mode, opts=OPTS) 2185 if defined?(yield) # perform locking inside a transaction and yield to block 2186 @db.transaction(opts){lock(mode, opts); yield} 2187 else 2188 sql = 'LOCK TABLE '.dup 2189 source_list_append(sql, @opts[:from]) 2190 mode = mode.to_s.upcase.strip 2191 unless LOCK_MODES.include?(mode) 2192 raise Error, "Unsupported lock mode: #{mode}" 2193 end 2194 sql << " IN #{mode} MODE" 2195 @db.execute(sql, opts) 2196 end 2197 nil 2198 end
Support MERGE RETURNING on PostgreSQL 17+.
# File lib/sequel/adapters/shared/postgres.rb 2201 def merge(&block) 2202 sql = merge_sql 2203 if uses_returning?(:merge) 2204 returning_fetch_rows(sql, &block) 2205 else 2206 execute_ddl(sql) 2207 end 2208 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 2219 def merge_delete_when_not_matched_by_source(&block) 2220 _merge_when(:type=>:delete_not_matched_by_source, &block) 2221 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 2232 def merge_do_nothing_when_matched(&block) 2233 _merge_when(:type=>:matched, &block) 2234 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 2245 def merge_do_nothing_when_not_matched(&block) 2246 _merge_when(:type=>:not_matched, &block) 2247 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 2258 def merge_do_nothing_when_not_matched_by_source(&block) 2259 _merge_when(:type=>:not_matched_by_source, &block) 2260 end
Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2263 def merge_insert(*values, &block) 2264 h = {:type=>:insert, :values=>values} 2265 if @opts[:override] 2266 h[:override] = insert_override_sql(String.new) 2267 end 2268 _merge_when(h, &block) 2269 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 2280 def merge_update_when_not_matched_by_source(values, &block) 2281 _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block) 2282 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 2287 def overriding_system_value 2288 clone(:override=>:system) 2289 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 2293 def overriding_user_value 2294 clone(:override=>:user) 2295 end
# File lib/sequel/adapters/shared/postgres.rb 2297 def supports_cte?(type=:select) 2298 if type == :select 2299 server_version >= 80400 2300 else 2301 server_version >= 90100 2302 end 2303 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 2307 def supports_cte_in_subqueries? 2308 supports_cte? 2309 end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb 2312 def supports_distinct_on? 2313 true 2314 end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb 2317 def supports_group_cube? 2318 server_version >= 90500 2319 end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb 2322 def supports_group_rollup? 2323 server_version >= 90500 2324 end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb 2327 def supports_grouping_sets? 2328 server_version >= 90500 2329 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2337 def supports_insert_conflict? 2338 server_version >= 90500 2339 end
True unless insert returning has been disabled for this dataset.
# File lib/sequel/adapters/shared/postgres.rb 2332 def supports_insert_select? 2333 !@opts[:disable_insert_returning] 2334 end
PostgreSQL 9.3+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb 2342 def supports_lateral_subqueries? 2343 server_version >= 90300 2344 end
PostgreSQL 15+ supports MERGE.
# File lib/sequel/adapters/shared/postgres.rb 2352 def supports_merge? 2353 server_version >= 150000 2354 end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb 2347 def supports_modifying_joins? 2348 true 2349 end
PostgreSQL supports NOWAIT.
# File lib/sequel/adapters/shared/postgres.rb 2357 def supports_nowait? 2358 true 2359 end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb 2372 def supports_regexp? 2373 true 2374 end
MERGE RETURNING is supported on PostgreSQL 17+. Other RETURNING is supported on all supported PostgreSQL versions.
# File lib/sequel/adapters/shared/postgres.rb 2363 def supports_returning?(type) 2364 if type == :merge 2365 server_version >= 170000 2366 else 2367 true 2368 end 2369 end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb 2377 def supports_skip_locked? 2378 server_version >= 90500 2379 end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb 2384 def supports_timestamp_timezones? 2385 # SEQUEL6: Remove 2386 true 2387 end
PostgreSQL 8.4+ supports WINDOW clause.
# File lib/sequel/adapters/shared/postgres.rb 2391 def supports_window_clause? 2392 server_version >= 80400 2393 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 2402 def supports_window_function_frame_option?(option) 2403 case option 2404 when :rows, :range 2405 true 2406 when :offset 2407 server_version >= 90000 2408 when :groups, :exclude 2409 server_version >= 110000 2410 else 2411 false 2412 end 2413 end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb 2396 def supports_window_functions? 2397 server_version >= 80400 2398 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 2431 def truncate(opts = OPTS) 2432 if opts.empty? 2433 super() 2434 else 2435 clone(:truncate_opts=>opts).truncate 2436 end 2437 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 2442 def with_ties 2443 clone(:limit_with_ties=>true) 2444 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 2452 def _import(columns, values, opts=OPTS) 2453 if @opts[:returning] 2454 # no transaction: our multi_insert_sql_strategy should guarantee 2455 # that there's only ever a single statement. 2456 sql = multi_insert_sql(columns, values)[0] 2457 returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v} 2458 elsif opts[:return] == :primary_key 2459 returning(insert_pk)._import(columns, values, opts) 2460 else 2461 super 2462 end 2463 end
# File lib/sequel/adapters/shared/postgres.rb 2465 def to_prepared_statement(type, *a) 2466 if type == :insert && !@opts.has_key?(:returning) 2467 returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) 2468 else 2469 super 2470 end 2471 end
Private Instance Methods
# File lib/sequel/adapters/shared/postgres.rb 2486 def _merge_do_nothing_sql(sql, data) 2487 sql << " THEN DO NOTHING" 2488 end
Append the INSERT sql used in a MERGE
# File lib/sequel/adapters/shared/postgres.rb 2476 def _merge_insert_sql(sql, data) 2477 sql << " THEN INSERT" 2478 columns, values = _parse_insert_sql_args(data[:values]) 2479 _insert_columns_sql(sql, columns) 2480 if override = data[:override] 2481 sql << override 2482 end 2483 _insert_values_sql(sql, values) 2484 end
Support MERGE RETURNING on PostgreSQL 17+.
# File lib/sequel/adapters/shared/postgres.rb 2491 def _merge_when_sql(sql) 2492 super 2493 insert_returning_sql(sql) if uses_returning?(:merge) 2494 end
Format TRUNCATE statement with PostgreSQL specific options.
# File lib/sequel/adapters/shared/postgres.rb 2497 def _truncate_sql(table) 2498 to = @opts[:truncate_opts] || OPTS 2499 "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}" 2500 end
Use from_self for aggregate dataset using VALUES.
# File lib/sequel/adapters/shared/postgres.rb 2503 def aggreate_dataset_use_from_self? 2504 super || @opts[:values] 2505 end
Allow truncation of multiple source tables.
# File lib/sequel/adapters/shared/postgres.rb 2508 def check_truncation_allowed! 2509 raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group] 2510 raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join] 2511 end
PostgreSQL requires parentheses around compound datasets if they use CTEs, and using them in other places doesn’t hurt.
# File lib/sequel/adapters/shared/postgres.rb 2754 def compound_dataset_sql_append(sql, ds) 2755 sql << '(' 2756 super 2757 sql << ')' 2758 end
The strftime format to use when literalizing the time.
# File lib/sequel/adapters/shared/postgres.rb 2514 def default_timestamp_format 2515 "'%Y-%m-%d %H:%M:%S.%6N%z'" 2516 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/postgres.rb 2519 def delete_from_sql(sql) 2520 sql << ' FROM ' 2521 source_list_append(sql, @opts[:from][0..0]) 2522 end
Use USING to specify additional tables in a delete query
# File lib/sequel/adapters/shared/postgres.rb 2525 def delete_using_sql(sql) 2526 join_from_sql(:USING, sql) 2527 end
Handle column aliases containing data types, useful for selecting from functions that return the record data type.
# File lib/sequel/adapters/shared/postgres.rb 2531 def derived_column_list_sql_append(sql, column_aliases) 2532 c = false 2533 comma = ', ' 2534 column_aliases.each do |a| 2535 sql << comma if c 2536 if a.is_a?(Array) 2537 raise Error, "column aliases specified as arrays must have only 2 elements, the first is alias name and the second is data type" unless a.length == 2 2538 a, type = a 2539 identifier_append(sql, a) 2540 sql << " " << db.cast_type_literal(type).to_s 2541 else 2542 identifier_append(sql, a) 2543 end 2544 c ||= true 2545 end 2546 end
A mutable string used as the prefix when explaining a query.
# File lib/sequel/adapters/shared/postgres.rb 2560 def explain_sql_string_origin(opts) 2561 origin = String.new 2562 origin << 'EXPLAIN ' 2563 2564 # :nocov: 2565 if server_version < 90000 2566 if opts[:analyze] 2567 origin << 'ANALYZE ' 2568 end 2569 2570 return origin 2571 end 2572 # :nocov: 2573 2574 comma = nil 2575 paren = "(" 2576 2577 add_opt = lambda do |str, value| 2578 origin << paren if paren 2579 origin << comma if comma 2580 origin << str 2581 origin << " FALSE" unless value 2582 comma ||= ', ' 2583 paren &&= nil 2584 end 2585 2586 EXPLAIN_BOOLEAN_OPTIONS.each do |key, str| 2587 unless (value = opts[key]).nil? 2588 add_opt.call(str, value) 2589 end 2590 end 2591 2592 EXPLAIN_NONBOOLEAN_OPTIONS.each do |key, e_opts| 2593 if value = opts[key] 2594 if str = e_opts[value] 2595 add_opt.call(str, true) 2596 else 2597 raise Sequel::Error, "unrecognized value for Dataset#explain #{key.inspect} option: #{value.inspect}" 2598 end 2599 end 2600 end 2601 2602 origin << ') ' unless paren 2603 origin 2604 end
Concatenate the expressions with a space in between
# File lib/sequel/adapters/shared/postgres.rb 2883 def full_text_string_join(cols) 2884 cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')} 2885 cols = cols.zip([' '] * cols.length).flatten 2886 cols.pop 2887 SQL::StringExpression.new(:'||', *cols) 2888 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/postgres.rb 2607 def insert_conflict_sql(sql) 2608 if opts = @opts[:insert_conflict] 2609 sql << " ON CONFLICT" 2610 2611 if target = opts[:constraint] 2612 sql << " ON CONSTRAINT " 2613 identifier_append(sql, target) 2614 elsif target = opts[:target] 2615 sql << ' ' 2616 identifier_append(sql, Array(target)) 2617 if conflict_where = opts[:conflict_where] 2618 sql << " WHERE " 2619 literal_append(sql, conflict_where) 2620 end 2621 end 2622 2623 if values = opts[:update] 2624 sql << " DO UPDATE SET " 2625 update_sql_values_hash(sql, values) 2626 if update_where = opts[:update_where] 2627 sql << " WHERE " 2628 literal_append(sql, update_where) 2629 end 2630 else 2631 sql << " DO NOTHING" 2632 end 2633 end 2634 end
Include aliases when inserting into a single table on PostgreSQL 9.5+.
# File lib/sequel/adapters/shared/postgres.rb 2637 def insert_into_sql(sql) 2638 sql << " INTO " 2639 if (f = @opts[:from]) && f.length == 1 2640 identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first)) 2641 else 2642 source_list_append(sql, f) 2643 end 2644 end
Support OVERRIDING SYSTEM|USER VALUE in insert statements
# File lib/sequel/adapters/shared/postgres.rb 2661 def insert_override_sql(sql) 2662 case opts[:override] 2663 when :system 2664 sql << " OVERRIDING SYSTEM VALUE" 2665 when :user 2666 sql << " OVERRIDING USER VALUE" 2667 end 2668 end
Return the primary key to use for RETURNING in an INSERT statement
# File lib/sequel/adapters/shared/postgres.rb 2647 def insert_pk 2648 (f = opts[:from]) && !f.empty? && (t = f.first) 2649 2650 t = t.call(self) if t.is_a? Sequel::SQL::DelayedEvaluation 2651 2652 case t 2653 when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier 2654 if pk = db.primary_key(t) 2655 Sequel::SQL::Identifier.new(pk) 2656 end 2657 end 2658 end
For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.
# File lib/sequel/adapters/shared/postgres.rb 2672 def join_from_sql(type, sql) 2673 if(from = @opts[:from][1..-1]).empty? 2674 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 2675 else 2676 sql << ' ' << type.to_s << ' ' 2677 source_list_append(sql, from) 2678 select_join_sql(sql) 2679 end 2680 end
Support table aliases for USING columns
# File lib/sequel/adapters/shared/postgres.rb 2683 def join_using_clause_using_sql_append(sql, using_columns) 2684 if using_columns.is_a?(SQL::AliasedExpression) 2685 super(sql, using_columns.expression) 2686 sql << ' AS ' 2687 identifier_append(sql, using_columns.alias) 2688 else 2689 super 2690 end 2691 end
Use a generic blob quoting method, hopefully overridden in one of the subadapter methods
# File lib/sequel/adapters/shared/postgres.rb 2694 def literal_blob_append(sql, v) 2695 sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'" 2696 end
PostgreSQL uses FALSE for false values
# File lib/sequel/adapters/shared/postgres.rb 2699 def literal_false 2700 'false' 2701 end
PostgreSQL quotes NaN and Infinity.
# File lib/sequel/adapters/shared/postgres.rb 2704 def literal_float(value) 2705 if value.finite? 2706 super 2707 elsif value.nan? 2708 "'NaN'" 2709 elsif value.infinite? == 1 2710 "'Infinity'" 2711 else 2712 "'-Infinity'" 2713 end 2714 end
Handle Ruby integers outside PostgreSQL bigint range specially.
# File lib/sequel/adapters/shared/postgres.rb 2717 def literal_integer(v) 2718 if v > 9223372036854775807 || v < -9223372036854775808 2719 literal_integer_outside_bigint_range(v) 2720 else 2721 v.to_s 2722 end 2723 end
Raise IntegerOutsideBigintRange
when attempting to literalize Ruby integer outside PostgreSQL bigint range, so PostgreSQL doesn’t treat the value as numeric.
# File lib/sequel/adapters/shared/postgres.rb 2728 def literal_integer_outside_bigint_range(v) 2729 raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}" 2730 end
Assume that SQL
standard quoting is on, per Sequel’s defaults
# File lib/sequel/adapters/shared/postgres.rb 2733 def literal_string_append(sql, v) 2734 sql << "'" << v.gsub("'", "''") << "'" 2735 end
PostgreSQL uses true for true values
# File lib/sequel/adapters/shared/postgres.rb 2738 def literal_true 2739 'true' 2740 end
PostgreSQL supports multiple rows in INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2743 def multi_insert_sql_strategy 2744 :values 2745 end
Backslash is supported by default as the escape character on PostgreSQL, and using ESCAPE can break LIKE ANY() usage.
# File lib/sequel/adapters/shared/postgres.rb 2762 def requires_like_escape? 2763 false 2764 end
Support FETCH FIRST WITH TIES on PostgreSQL 13+.
# File lib/sequel/adapters/shared/postgres.rb 2767 def select_limit_sql(sql) 2768 l = @opts[:limit] 2769 o = @opts[:offset] 2770 2771 return unless l || o 2772 2773 if @opts[:limit_with_ties] 2774 if o 2775 sql << " OFFSET " 2776 literal_append(sql, o) 2777 end 2778 2779 if l 2780 sql << " FETCH FIRST " 2781 literal_append(sql, l) 2782 sql << " ROWS WITH TIES" 2783 end 2784 else 2785 if l 2786 sql << " LIMIT " 2787 literal_append(sql, l) 2788 end 2789 2790 if o 2791 sql << " OFFSET " 2792 literal_append(sql, o) 2793 end 2794 end 2795 end
Support FOR SHARE locking when using the :share lock style. Use SKIP LOCKED if skipping locked rows.
# File lib/sequel/adapters/shared/postgres.rb 2799 def select_lock_sql(sql) 2800 lock = @opts[:lock] 2801 case lock 2802 when :share 2803 sql << ' FOR SHARE' 2804 when :no_key_update 2805 sql << ' FOR NO KEY UPDATE' 2806 when :key_share 2807 sql << ' FOR KEY SHARE' 2808 else 2809 super 2810 end 2811 2812 if lock 2813 if @opts[:skip_locked] 2814 sql << " SKIP LOCKED" 2815 elsif @opts[:nowait] 2816 sql << " NOWAIT" 2817 end 2818 end 2819 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/postgres.rb 2822 def select_values_sql(sql) 2823 sql << "VALUES " 2824 expression_list_append(sql, opts[:values]) 2825 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/postgres.rb 2828 def select_with_sql_base 2829 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 2830 end
Support PostgreSQL 14+ CTE SEARCH/CYCLE clauses
# File lib/sequel/adapters/shared/postgres.rb 2833 def select_with_sql_cte(sql, cte) 2834 super 2835 select_with_sql_cte_search_cycle(sql, cte) 2836 end
# File lib/sequel/adapters/shared/postgres.rb 2838 def select_with_sql_cte_search_cycle(sql, cte) 2839 if search_opts = cte[:search] 2840 sql << if search_opts[:type] == :breadth 2841 " SEARCH BREADTH FIRST BY " 2842 else 2843 " SEARCH DEPTH FIRST BY " 2844 end 2845 2846 identifier_list_append(sql, Array(search_opts[:by])) 2847 sql << " SET " 2848 identifier_append(sql, search_opts[:set] || :ordercol) 2849 end 2850 2851 if cycle_opts = cte[:cycle] 2852 sql << " CYCLE " 2853 identifier_list_append(sql, Array(cycle_opts[:columns])) 2854 sql << " SET " 2855 identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle) 2856 if cycle_opts.has_key?(:cycle_value) 2857 sql << " TO " 2858 literal_append(sql, cycle_opts[:cycle_value]) 2859 sql << " DEFAULT " 2860 literal_append(sql, cycle_opts.fetch(:noncycle_value, false)) 2861 end 2862 sql << " USING " 2863 identifier_append(sql, cycle_opts[:path_column] || :path) 2864 end 2865 end
The version of the database server
# File lib/sequel/adapters/shared/postgres.rb 2868 def server_version 2869 db.server_version(@opts[:server]) 2870 end
PostgreSQL 9.4+ supports the FILTER clause for aggregate functions.
# File lib/sequel/adapters/shared/postgres.rb 2873 def supports_filtered_aggregates? 2874 server_version >= 90400 2875 end
PostgreSQL supports quoted function names.
# File lib/sequel/adapters/shared/postgres.rb 2878 def supports_quoted_function_names? 2879 true 2880 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/postgres.rb 2891 def update_from_sql(sql) 2892 join_from_sql(:FROM, sql) 2893 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/postgres.rb 2896 def update_table_sql(sql) 2897 sql << ' ' 2898 source_list_append(sql, @opts[:from][0..0]) 2899 end