module Sequel::Postgres::DatasetMethods
Constants
- LOCK_MODES
- NULL
Public Instance Methods
Return the results of an EXPLAIN ANALYZE query as a string
# File lib/sequel/adapters/shared/postgres.rb 1827 def analyze 1828 explain(:analyze=>true) 1829 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 1834 def complex_expression_sql_append(sql, op, args) 1835 case op 1836 when :^ 1837 j = ' # ' 1838 c = false 1839 args.each do |a| 1840 sql << j if c 1841 literal_append(sql, a) 1842 c ||= true 1843 end 1844 when :ILIKE, :'NOT ILIKE' 1845 sql << '(' 1846 literal_append(sql, args[0]) 1847 sql << ' ' << op.to_s << ' ' 1848 literal_append(sql, args[1]) 1849 sql << ')' 1850 else 1851 super 1852 end 1853 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 1869 def disable_insert_returning 1870 clone(:disable_insert_returning=>true) 1871 end
Always return false when using VALUES
# File lib/sequel/adapters/shared/postgres.rb 1874 def empty? 1875 return false if @opts[:values] 1876 super 1877 end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb 1880 def explain(opts=OPTS) 1881 with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n") 1882 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 1908 def full_text_search(cols, terms, opts = OPTS) 1909 lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 1910 1911 unless opts[:tsvector] 1912 phrase_cols = full_text_string_join(cols) 1913 cols = Sequel.function(:to_tsvector, lang, phrase_cols) 1914 end 1915 1916 unless opts[:tsquery] 1917 phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 1918 1919 query_func = case to_tsquery = opts[:to_tsquery] 1920 when :phrase, :plain 1921 :"#{to_tsquery}to_tsquery" 1922 when :websearch 1923 :"websearch_to_tsquery" 1924 else 1925 (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 1926 end 1927 1928 terms = Sequel.function(query_func, lang, phrase_terms) 1929 end 1930 1931 ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) 1932 1933 if opts[:phrase] 1934 raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 1935 ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 1936 end 1937 1938 if opts[:rank] 1939 ds = ds.reverse{ts_rank_cd(cols, terms)} 1940 end 1941 1942 if opts[:headline] 1943 ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 1944 end 1945 1946 ds 1947 end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb 1950 def insert(*values) 1951 if @opts[:returning] 1952 # Already know which columns to return, let the standard code handle it 1953 super 1954 elsif @opts[:sql] || @opts[:disable_insert_returning] 1955 # Raw SQL used or RETURNING disabled, just use the default behavior 1956 # and return nil since sequence is not known. 1957 super 1958 nil 1959 else 1960 # Force the use of RETURNING with the primary key value, 1961 # unless it has been disabled. 1962 returning(insert_pk).insert(*values){|r| return r.values.first} 1963 end 1964 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 2001 def insert_conflict(opts=OPTS) 2002 clone(:insert_conflict => opts) 2003 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 2011 def insert_ignore 2012 insert_conflict 2013 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 2018 def insert_select(*values) 2019 return unless supports_insert_select? 2020 # Handle case where query does not return a row 2021 server?(:default).with_sql_first(insert_select_sql(*values)) || false 2022 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 2026 def insert_select_sql(*values) 2027 ds = opts[:returning] ? self : returning 2028 ds.insert_sql(*values) 2029 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 2033 def join_table(type, table, expr=nil, options=OPTS, &block) 2034 if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all? 2035 options = options.merge(:join_using=>true) 2036 end 2037 super 2038 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 2045 def lock(mode, opts=OPTS) 2046 if defined?(yield) # perform locking inside a transaction and yield to block 2047 @db.transaction(opts){lock(mode, opts); yield} 2048 else 2049 sql = 'LOCK TABLE '.dup 2050 source_list_append(sql, @opts[:from]) 2051 mode = mode.to_s.upcase.strip 2052 unless LOCK_MODES.include?(mode) 2053 raise Error, "Unsupported lock mode: #{mode}" 2054 end 2055 sql << " IN #{mode} MODE" 2056 @db.execute(sql, opts) 2057 end 2058 nil 2059 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 2070 def merge_do_nothing_when_matched(&block) 2071 _merge_when(:type=>:matched, &block) 2072 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 2083 def merge_do_nothing_when_not_matched(&block) 2084 _merge_when(:type=>:not_matched, &block) 2085 end
Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2088 def merge_insert(*values, &block) 2089 h = {:type=>:insert, :values=>values} 2090 if override = @opts[:override] 2091 h[:override] = insert_override_sql(String.new) 2092 end 2093 _merge_when(h, &block) 2094 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 2099 def overriding_system_value 2100 clone(:override=>:system) 2101 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 2105 def overriding_user_value 2106 clone(:override=>:user) 2107 end
# File lib/sequel/adapters/shared/postgres.rb 2109 def supports_cte?(type=:select) 2110 if type == :select 2111 server_version >= 80400 2112 else 2113 server_version >= 90100 2114 end 2115 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 2119 def supports_cte_in_subqueries? 2120 supports_cte? 2121 end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb 2124 def supports_distinct_on? 2125 true 2126 end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb 2129 def supports_group_cube? 2130 server_version >= 90500 2131 end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb 2134 def supports_group_rollup? 2135 server_version >= 90500 2136 end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb 2139 def supports_grouping_sets? 2140 server_version >= 90500 2141 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2149 def supports_insert_conflict? 2150 server_version >= 90500 2151 end
True unless insert returning has been disabled for this dataset.
# File lib/sequel/adapters/shared/postgres.rb 2144 def supports_insert_select? 2145 !@opts[:disable_insert_returning] 2146 end
PostgreSQL 9.3+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb 2154 def supports_lateral_subqueries? 2155 server_version >= 90300 2156 end
PostgreSQL 15+ supports MERGE.
# File lib/sequel/adapters/shared/postgres.rb 2164 def supports_merge? 2165 server_version >= 150000 2166 end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb 2159 def supports_modifying_joins? 2160 true 2161 end
PostgreSQL supports NOWAIT.
# File lib/sequel/adapters/shared/postgres.rb 2169 def supports_nowait? 2170 true 2171 end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb 2179 def supports_regexp? 2180 true 2181 end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb 2174 def supports_returning?(type) 2175 true 2176 end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb 2184 def supports_skip_locked? 2185 server_version >= 90500 2186 end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb 2191 def supports_timestamp_timezones? 2192 # SEQUEL6: Remove 2193 true 2194 end
PostgreSQL 8.4+ supports WINDOW clause.
# File lib/sequel/adapters/shared/postgres.rb 2198 def supports_window_clause? 2199 server_version >= 80400 2200 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 2209 def supports_window_function_frame_option?(option) 2210 case option 2211 when :rows, :range 2212 true 2213 when :offset 2214 server_version >= 90000 2215 when :groups, :exclude 2216 server_version >= 110000 2217 else 2218 false 2219 end 2220 end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb 2203 def supports_window_functions? 2204 server_version >= 80400 2205 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 2238 def truncate(opts = OPTS) 2239 if opts.empty? 2240 super() 2241 else 2242 clone(:truncate_opts=>opts).truncate 2243 end 2244 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 2249 def with_ties 2250 clone(:limit_with_ties=>true) 2251 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 2259 def _import(columns, values, opts=OPTS) 2260 if @opts[:returning] 2261 # no transaction: our multi_insert_sql_strategy should guarantee 2262 # that there's only ever a single statement. 2263 sql = multi_insert_sql(columns, values)[0] 2264 returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v} 2265 elsif opts[:return] == :primary_key 2266 returning(insert_pk)._import(columns, values, opts) 2267 else 2268 super 2269 end 2270 end
# File lib/sequel/adapters/shared/postgres.rb 2272 def to_prepared_statement(type, *a) 2273 if type == :insert && !@opts.has_key?(:returning) 2274 returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) 2275 else 2276 super 2277 end 2278 end
Private Instance Methods
Append the INSERT sql used in a MERGE
# File lib/sequel/adapters/shared/postgres.rb 2283 def _merge_insert_sql(sql, data) 2284 sql << " THEN INSERT " 2285 columns, values = _parse_insert_sql_args(data[:values]) 2286 _insert_columns_sql(sql, columns) 2287 if override = data[:override] 2288 sql << override 2289 end 2290 _insert_values_sql(sql, values) 2291 end
# File lib/sequel/adapters/shared/postgres.rb 2293 def _merge_matched_sql(sql, data) 2294 sql << " THEN DO NOTHING" 2295 end
Format TRUNCATE statement with PostgreSQL specific options.
# File lib/sequel/adapters/shared/postgres.rb 2299 def _truncate_sql(table) 2300 to = @opts[:truncate_opts] || OPTS 2301 "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}" 2302 end
Use from_self for aggregate dataset using VALUES.
# File lib/sequel/adapters/shared/postgres.rb 2305 def aggreate_dataset_use_from_self? 2306 super || @opts[:values] 2307 end
Allow truncation of multiple source tables.
# File lib/sequel/adapters/shared/postgres.rb 2310 def check_truncation_allowed! 2311 raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group] 2312 raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join] 2313 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 2476 def compound_dataset_sql_append(sql, ds) 2477 sql << '(' 2478 super 2479 sql << ')' 2480 end
The strftime format to use when literalizing the time.
# File lib/sequel/adapters/shared/postgres.rb 2316 def default_timestamp_format 2317 "'%Y-%m-%d %H:%M:%S.%6N%z'" 2318 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/postgres.rb 2321 def delete_from_sql(sql) 2322 sql << ' FROM ' 2323 source_list_append(sql, @opts[:from][0..0]) 2324 end
Use USING to specify additional tables in a delete query
# File lib/sequel/adapters/shared/postgres.rb 2327 def delete_using_sql(sql) 2328 join_from_sql(:USING, sql) 2329 end
Concatenate the expressions with a space in between
# File lib/sequel/adapters/shared/postgres.rb 2600 def full_text_string_join(cols) 2601 cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')} 2602 cols = cols.zip([' '] * cols.length).flatten 2603 cols.pop 2604 SQL::StringExpression.new(:'||', *cols) 2605 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/postgres.rb 2332 def insert_conflict_sql(sql) 2333 if opts = @opts[:insert_conflict] 2334 sql << " ON CONFLICT" 2335 2336 if target = opts[:constraint] 2337 sql << " ON CONSTRAINT " 2338 identifier_append(sql, target) 2339 elsif target = opts[:target] 2340 sql << ' ' 2341 identifier_append(sql, Array(target)) 2342 if conflict_where = opts[:conflict_where] 2343 sql << " WHERE " 2344 literal_append(sql, conflict_where) 2345 end 2346 end 2347 2348 if values = opts[:update] 2349 sql << " DO UPDATE SET " 2350 update_sql_values_hash(sql, values) 2351 if update_where = opts[:update_where] 2352 sql << " WHERE " 2353 literal_append(sql, update_where) 2354 end 2355 else 2356 sql << " DO NOTHING" 2357 end 2358 end 2359 end
Include aliases when inserting into a single table on PostgreSQL 9.5+.
# File lib/sequel/adapters/shared/postgres.rb 2362 def insert_into_sql(sql) 2363 sql << " INTO " 2364 if (f = @opts[:from]) && f.length == 1 2365 identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first)) 2366 else 2367 source_list_append(sql, f) 2368 end 2369 end
Support OVERRIDING SYSTEM|USER VALUE in insert statements
# File lib/sequel/adapters/shared/postgres.rb 2383 def insert_override_sql(sql) 2384 case opts[:override] 2385 when :system 2386 sql << " OVERRIDING SYSTEM VALUE" 2387 when :user 2388 sql << " OVERRIDING USER VALUE" 2389 end 2390 end
Return the primary key to use for RETURNING in an INSERT statement
# File lib/sequel/adapters/shared/postgres.rb 2372 def insert_pk 2373 (f = opts[:from]) && !f.empty? && (t = f.first) 2374 case t 2375 when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier 2376 if pk = db.primary_key(t) 2377 Sequel::SQL::Identifier.new(pk) 2378 end 2379 end 2380 end
For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.
# File lib/sequel/adapters/shared/postgres.rb 2394 def join_from_sql(type, sql) 2395 if(from = @opts[:from][1..-1]).empty? 2396 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 2397 else 2398 sql << ' ' << type.to_s << ' ' 2399 source_list_append(sql, from) 2400 select_join_sql(sql) 2401 end 2402 end
Support table aliases for USING columns
# File lib/sequel/adapters/shared/postgres.rb 2405 def join_using_clause_using_sql_append(sql, using_columns) 2406 if using_columns.is_a?(SQL::AliasedExpression) 2407 super(sql, using_columns.expression) 2408 sql << ' AS ' 2409 identifier_append(sql, using_columns.alias) 2410 else 2411 super 2412 end 2413 end
Use a generic blob quoting method, hopefully overridden in one of the subadapter methods
# File lib/sequel/adapters/shared/postgres.rb 2416 def literal_blob_append(sql, v) 2417 sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'" 2418 end
PostgreSQL uses FALSE for false values
# File lib/sequel/adapters/shared/postgres.rb 2421 def literal_false 2422 'false' 2423 end
PostgreSQL quotes NaN and Infinity.
# File lib/sequel/adapters/shared/postgres.rb 2426 def literal_float(value) 2427 if value.finite? 2428 super 2429 elsif value.nan? 2430 "'NaN'" 2431 elsif value.infinite? == 1 2432 "'Infinity'" 2433 else 2434 "'-Infinity'" 2435 end 2436 end
Handle Ruby integers outside PostgreSQL bigint range specially.
# File lib/sequel/adapters/shared/postgres.rb 2439 def literal_integer(v) 2440 if v > 9223372036854775807 || v < -9223372036854775808 2441 literal_integer_outside_bigint_range(v) 2442 else 2443 v.to_s 2444 end 2445 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 2450 def literal_integer_outside_bigint_range(v) 2451 raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}" 2452 end
Assume that SQL
standard quoting is on, per Sequel’s defaults
# File lib/sequel/adapters/shared/postgres.rb 2455 def literal_string_append(sql, v) 2456 sql << "'" << v.gsub("'", "''") << "'" 2457 end
PostgreSQL uses true for true values
# File lib/sequel/adapters/shared/postgres.rb 2460 def literal_true 2461 'true' 2462 end
PostgreSQL supports multiple rows in INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2465 def multi_insert_sql_strategy 2466 :values 2467 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 2484 def requires_like_escape? 2485 false 2486 end
Support FETCH FIRST WITH TIES on PostgreSQL 13+.
# File lib/sequel/adapters/shared/postgres.rb 2489 def select_limit_sql(sql) 2490 l = @opts[:limit] 2491 o = @opts[:offset] 2492 2493 return unless l || o 2494 2495 if @opts[:limit_with_ties] 2496 if o 2497 sql << " OFFSET " 2498 literal_append(sql, o) 2499 end 2500 2501 if l 2502 sql << " FETCH FIRST " 2503 literal_append(sql, l) 2504 sql << " ROWS WITH TIES" 2505 end 2506 else 2507 if l 2508 sql << " LIMIT " 2509 literal_append(sql, l) 2510 end 2511 2512 if o 2513 sql << " OFFSET " 2514 literal_append(sql, o) 2515 end 2516 end 2517 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 2521 def select_lock_sql(sql) 2522 lock = @opts[:lock] 2523 if lock == :share 2524 sql << ' FOR SHARE' 2525 else 2526 super 2527 end 2528 2529 if lock 2530 if @opts[:skip_locked] 2531 sql << " SKIP LOCKED" 2532 elsif @opts[:nowait] 2533 sql << " NOWAIT" 2534 end 2535 end 2536 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/postgres.rb 2539 def select_values_sql(sql) 2540 sql << "VALUES " 2541 expression_list_append(sql, opts[:values]) 2542 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/postgres.rb 2545 def select_with_sql_base 2546 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 2547 end
Support PostgreSQL 14+ CTE SEARCH/CYCLE clauses
# File lib/sequel/adapters/shared/postgres.rb 2550 def select_with_sql_cte(sql, cte) 2551 super 2552 select_with_sql_cte_search_cycle(sql, cte) 2553 end
# File lib/sequel/adapters/shared/postgres.rb 2555 def select_with_sql_cte_search_cycle(sql, cte) 2556 if search_opts = cte[:search] 2557 sql << if search_opts[:type] == :breadth 2558 " SEARCH BREADTH FIRST BY " 2559 else 2560 " SEARCH DEPTH FIRST BY " 2561 end 2562 2563 identifier_list_append(sql, Array(search_opts[:by])) 2564 sql << " SET " 2565 identifier_append(sql, search_opts[:set] || :ordercol) 2566 end 2567 2568 if cycle_opts = cte[:cycle] 2569 sql << " CYCLE " 2570 identifier_list_append(sql, Array(cycle_opts[:columns])) 2571 sql << " SET " 2572 identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle) 2573 if cycle_opts.has_key?(:cycle_value) 2574 sql << " TO " 2575 literal_append(sql, cycle_opts[:cycle_value]) 2576 sql << " DEFAULT " 2577 literal_append(sql, cycle_opts.fetch(:noncycle_value, false)) 2578 end 2579 sql << " USING " 2580 identifier_append(sql, cycle_opts[:path_column] || :path) 2581 end 2582 end
The version of the database server
# File lib/sequel/adapters/shared/postgres.rb 2585 def server_version 2586 db.server_version(@opts[:server]) 2587 end
PostgreSQL 9.4+ supports the FILTER clause for aggregate functions.
# File lib/sequel/adapters/shared/postgres.rb 2590 def supports_filtered_aggregates? 2591 server_version >= 90400 2592 end
PostgreSQL supports quoted function names.
# File lib/sequel/adapters/shared/postgres.rb 2595 def supports_quoted_function_names? 2596 true 2597 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/postgres.rb 2608 def update_from_sql(sql) 2609 join_from_sql(:FROM, sql) 2610 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/postgres.rb 2613 def update_table_sql(sql) 2614 sql << ' ' 2615 source_list_append(sql, @opts[:from][0..0]) 2616 end