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 1828 def analyze 1829 explain(:analyze=>true) 1830 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 1835 def complex_expression_sql_append(sql, op, args) 1836 case op 1837 when :^ 1838 j = ' # ' 1839 c = false 1840 args.each do |a| 1841 sql << j if c 1842 literal_append(sql, a) 1843 c ||= true 1844 end 1845 when :ILIKE, :'NOT ILIKE' 1846 sql << '(' 1847 literal_append(sql, args[0]) 1848 sql << ' ' << op.to_s << ' ' 1849 literal_append(sql, args[1]) 1850 sql << ')' 1851 else 1852 super 1853 end 1854 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 1870 def disable_insert_returning 1871 clone(:disable_insert_returning=>true) 1872 end
Always return false when using VALUES
# File lib/sequel/adapters/shared/postgres.rb 1875 def empty? 1876 return false if @opts[:values] 1877 super 1878 end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb 1881 def explain(opts=OPTS) 1882 with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n") 1883 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 1909 def full_text_search(cols, terms, opts = OPTS) 1910 lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 1911 1912 unless opts[:tsvector] 1913 phrase_cols = full_text_string_join(cols) 1914 cols = Sequel.function(:to_tsvector, lang, phrase_cols) 1915 end 1916 1917 unless opts[:tsquery] 1918 phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 1919 1920 query_func = case to_tsquery = opts[:to_tsquery] 1921 when :phrase, :plain 1922 :"#{to_tsquery}to_tsquery" 1923 when :websearch 1924 :"websearch_to_tsquery" 1925 else 1926 (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 1927 end 1928 1929 terms = Sequel.function(query_func, lang, phrase_terms) 1930 end 1931 1932 ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) 1933 1934 if opts[:phrase] 1935 raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 1936 ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 1937 end 1938 1939 if opts[:rank] 1940 ds = ds.reverse{ts_rank_cd(cols, terms)} 1941 end 1942 1943 if opts[:headline] 1944 ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 1945 end 1946 1947 ds 1948 end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb 1951 def insert(*values) 1952 if @opts[:returning] 1953 # Already know which columns to return, let the standard code handle it 1954 super 1955 elsif @opts[:sql] || @opts[:disable_insert_returning] 1956 # Raw SQL used or RETURNING disabled, just use the default behavior 1957 # and return nil since sequence is not known. 1958 super 1959 nil 1960 else 1961 # Force the use of RETURNING with the primary key value, 1962 # unless it has been disabled. 1963 returning(insert_pk).insert(*values){|r| return r.values.first} 1964 end 1965 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 2002 def insert_conflict(opts=OPTS) 2003 clone(:insert_conflict => opts) 2004 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 2012 def insert_ignore 2013 insert_conflict 2014 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 2019 def insert_select(*values) 2020 return unless supports_insert_select? 2021 # Handle case where query does not return a row 2022 server?(:default).with_sql_first(insert_select_sql(*values)) || false 2023 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 2027 def insert_select_sql(*values) 2028 ds = opts[:returning] ? self : returning 2029 ds.insert_sql(*values) 2030 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 2034 def join_table(type, table, expr=nil, options=OPTS, &block) 2035 if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all? 2036 options = options.merge(:join_using=>true) 2037 end 2038 super 2039 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 2046 def lock(mode, opts=OPTS) 2047 if defined?(yield) # perform locking inside a transaction and yield to block 2048 @db.transaction(opts){lock(mode, opts); yield} 2049 else 2050 sql = 'LOCK TABLE '.dup 2051 source_list_append(sql, @opts[:from]) 2052 mode = mode.to_s.upcase.strip 2053 unless LOCK_MODES.include?(mode) 2054 raise Error, "Unsupported lock mode: #{mode}" 2055 end 2056 sql << " IN #{mode} MODE" 2057 @db.execute(sql, opts) 2058 end 2059 nil 2060 end
Support MERGE RETURNING on PostgreSQL 17+.
# File lib/sequel/adapters/shared/postgres.rb 2063 def merge(&block) 2064 sql = merge_sql 2065 if uses_returning?(:merge) 2066 returning_fetch_rows(sql, &block) 2067 else 2068 execute_ddl(sql) 2069 end 2070 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 2081 def merge_delete_when_not_matched_by_source(&block) 2082 _merge_when(:type=>:delete_not_matched_by_source, &block) 2083 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 2094 def merge_do_nothing_when_matched(&block) 2095 _merge_when(:type=>:matched, &block) 2096 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 2107 def merge_do_nothing_when_not_matched(&block) 2108 _merge_when(:type=>:not_matched, &block) 2109 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 2120 def merge_do_nothing_when_not_matched_by_source(&block) 2121 _merge_when(:type=>:not_matched_by_source, &block) 2122 end
Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2125 def merge_insert(*values, &block) 2126 h = {:type=>:insert, :values=>values} 2127 if @opts[:override] 2128 h[:override] = insert_override_sql(String.new) 2129 end 2130 _merge_when(h, &block) 2131 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 2142 def merge_update_when_not_matched_by_source(values, &block) 2143 _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block) 2144 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 2149 def overriding_system_value 2150 clone(:override=>:system) 2151 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 2155 def overriding_user_value 2156 clone(:override=>:user) 2157 end
# File lib/sequel/adapters/shared/postgres.rb 2159 def supports_cte?(type=:select) 2160 if type == :select 2161 server_version >= 80400 2162 else 2163 server_version >= 90100 2164 end 2165 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 2169 def supports_cte_in_subqueries? 2170 supports_cte? 2171 end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb 2174 def supports_distinct_on? 2175 true 2176 end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb 2179 def supports_group_cube? 2180 server_version >= 90500 2181 end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb 2184 def supports_group_rollup? 2185 server_version >= 90500 2186 end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb 2189 def supports_grouping_sets? 2190 server_version >= 90500 2191 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2199 def supports_insert_conflict? 2200 server_version >= 90500 2201 end
True unless insert returning has been disabled for this dataset.
# File lib/sequel/adapters/shared/postgres.rb 2194 def supports_insert_select? 2195 !@opts[:disable_insert_returning] 2196 end
PostgreSQL 9.3+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb 2204 def supports_lateral_subqueries? 2205 server_version >= 90300 2206 end
PostgreSQL 15+ supports MERGE.
# File lib/sequel/adapters/shared/postgres.rb 2214 def supports_merge? 2215 server_version >= 150000 2216 end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb 2209 def supports_modifying_joins? 2210 true 2211 end
PostgreSQL supports NOWAIT.
# File lib/sequel/adapters/shared/postgres.rb 2219 def supports_nowait? 2220 true 2221 end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb 2234 def supports_regexp? 2235 true 2236 end
MERGE RETURNING is supported on PostgreSQL 17+. Other RETURNING is supported on all supported PostgreSQL versions.
# File lib/sequel/adapters/shared/postgres.rb 2225 def supports_returning?(type) 2226 if type == :merge 2227 server_version >= 170000 2228 else 2229 true 2230 end 2231 end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb 2239 def supports_skip_locked? 2240 server_version >= 90500 2241 end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb 2246 def supports_timestamp_timezones? 2247 # SEQUEL6: Remove 2248 true 2249 end
PostgreSQL 8.4+ supports WINDOW clause.
# File lib/sequel/adapters/shared/postgres.rb 2253 def supports_window_clause? 2254 server_version >= 80400 2255 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 2264 def supports_window_function_frame_option?(option) 2265 case option 2266 when :rows, :range 2267 true 2268 when :offset 2269 server_version >= 90000 2270 when :groups, :exclude 2271 server_version >= 110000 2272 else 2273 false 2274 end 2275 end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb 2258 def supports_window_functions? 2259 server_version >= 80400 2260 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 2293 def truncate(opts = OPTS) 2294 if opts.empty? 2295 super() 2296 else 2297 clone(:truncate_opts=>opts).truncate 2298 end 2299 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 2304 def with_ties 2305 clone(:limit_with_ties=>true) 2306 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 2314 def _import(columns, values, opts=OPTS) 2315 if @opts[:returning] 2316 # no transaction: our multi_insert_sql_strategy should guarantee 2317 # that there's only ever a single statement. 2318 sql = multi_insert_sql(columns, values)[0] 2319 returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v} 2320 elsif opts[:return] == :primary_key 2321 returning(insert_pk)._import(columns, values, opts) 2322 else 2323 super 2324 end 2325 end
# File lib/sequel/adapters/shared/postgres.rb 2327 def to_prepared_statement(type, *a) 2328 if type == :insert && !@opts.has_key?(:returning) 2329 returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) 2330 else 2331 super 2332 end 2333 end
Private Instance Methods
# File lib/sequel/adapters/shared/postgres.rb 2348 def _merge_do_nothing_sql(sql, data) 2349 sql << " THEN DO NOTHING" 2350 end
Append the INSERT sql used in a MERGE
# File lib/sequel/adapters/shared/postgres.rb 2338 def _merge_insert_sql(sql, data) 2339 sql << " THEN INSERT" 2340 columns, values = _parse_insert_sql_args(data[:values]) 2341 _insert_columns_sql(sql, columns) 2342 if override = data[:override] 2343 sql << override 2344 end 2345 _insert_values_sql(sql, values) 2346 end
Support MERGE RETURNING on PostgreSQL 17+.
# File lib/sequel/adapters/shared/postgres.rb 2353 def _merge_when_sql(sql) 2354 super 2355 insert_returning_sql(sql) if uses_returning?(:merge) 2356 end
Format TRUNCATE statement with PostgreSQL specific options.
# File lib/sequel/adapters/shared/postgres.rb 2359 def _truncate_sql(table) 2360 to = @opts[:truncate_opts] || OPTS 2361 "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}" 2362 end
Use from_self for aggregate dataset using VALUES.
# File lib/sequel/adapters/shared/postgres.rb 2365 def aggreate_dataset_use_from_self? 2366 super || @opts[:values] 2367 end
Allow truncation of multiple source tables.
# File lib/sequel/adapters/shared/postgres.rb 2370 def check_truncation_allowed! 2371 raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group] 2372 raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join] 2373 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 2558 def compound_dataset_sql_append(sql, ds) 2559 sql << '(' 2560 super 2561 sql << ')' 2562 end
The strftime format to use when literalizing the time.
# File lib/sequel/adapters/shared/postgres.rb 2376 def default_timestamp_format 2377 "'%Y-%m-%d %H:%M:%S.%6N%z'" 2378 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/postgres.rb 2381 def delete_from_sql(sql) 2382 sql << ' FROM ' 2383 source_list_append(sql, @opts[:from][0..0]) 2384 end
Use USING to specify additional tables in a delete query
# File lib/sequel/adapters/shared/postgres.rb 2387 def delete_using_sql(sql) 2388 join_from_sql(:USING, sql) 2389 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 2393 def derived_column_list_sql_append(sql, column_aliases) 2394 c = false 2395 comma = ', ' 2396 column_aliases.each do |a| 2397 sql << comma if c 2398 if a.is_a?(Array) 2399 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 2400 a, type = a 2401 identifier_append(sql, a) 2402 sql << " " << db.cast_type_literal(type).to_s 2403 else 2404 identifier_append(sql, a) 2405 end 2406 c ||= true 2407 end 2408 end
Concatenate the expressions with a space in between
# File lib/sequel/adapters/shared/postgres.rb 2682 def full_text_string_join(cols) 2683 cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')} 2684 cols = cols.zip([' '] * cols.length).flatten 2685 cols.pop 2686 SQL::StringExpression.new(:'||', *cols) 2687 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/postgres.rb 2411 def insert_conflict_sql(sql) 2412 if opts = @opts[:insert_conflict] 2413 sql << " ON CONFLICT" 2414 2415 if target = opts[:constraint] 2416 sql << " ON CONSTRAINT " 2417 identifier_append(sql, target) 2418 elsif target = opts[:target] 2419 sql << ' ' 2420 identifier_append(sql, Array(target)) 2421 if conflict_where = opts[:conflict_where] 2422 sql << " WHERE " 2423 literal_append(sql, conflict_where) 2424 end 2425 end 2426 2427 if values = opts[:update] 2428 sql << " DO UPDATE SET " 2429 update_sql_values_hash(sql, values) 2430 if update_where = opts[:update_where] 2431 sql << " WHERE " 2432 literal_append(sql, update_where) 2433 end 2434 else 2435 sql << " DO NOTHING" 2436 end 2437 end 2438 end
Include aliases when inserting into a single table on PostgreSQL 9.5+.
# File lib/sequel/adapters/shared/postgres.rb 2441 def insert_into_sql(sql) 2442 sql << " INTO " 2443 if (f = @opts[:from]) && f.length == 1 2444 identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first)) 2445 else 2446 source_list_append(sql, f) 2447 end 2448 end
Support OVERRIDING SYSTEM|USER VALUE in insert statements
# File lib/sequel/adapters/shared/postgres.rb 2465 def insert_override_sql(sql) 2466 case opts[:override] 2467 when :system 2468 sql << " OVERRIDING SYSTEM VALUE" 2469 when :user 2470 sql << " OVERRIDING USER VALUE" 2471 end 2472 end
Return the primary key to use for RETURNING in an INSERT statement
# File lib/sequel/adapters/shared/postgres.rb 2451 def insert_pk 2452 (f = opts[:from]) && !f.empty? && (t = f.first) 2453 2454 t = t.call(self) if t.is_a? Sequel::SQL::DelayedEvaluation 2455 2456 case t 2457 when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier 2458 if pk = db.primary_key(t) 2459 Sequel::SQL::Identifier.new(pk) 2460 end 2461 end 2462 end
For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.
# File lib/sequel/adapters/shared/postgres.rb 2476 def join_from_sql(type, sql) 2477 if(from = @opts[:from][1..-1]).empty? 2478 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 2479 else 2480 sql << ' ' << type.to_s << ' ' 2481 source_list_append(sql, from) 2482 select_join_sql(sql) 2483 end 2484 end
Support table aliases for USING columns
# File lib/sequel/adapters/shared/postgres.rb 2487 def join_using_clause_using_sql_append(sql, using_columns) 2488 if using_columns.is_a?(SQL::AliasedExpression) 2489 super(sql, using_columns.expression) 2490 sql << ' AS ' 2491 identifier_append(sql, using_columns.alias) 2492 else 2493 super 2494 end 2495 end
Use a generic blob quoting method, hopefully overridden in one of the subadapter methods
# File lib/sequel/adapters/shared/postgres.rb 2498 def literal_blob_append(sql, v) 2499 sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'" 2500 end
PostgreSQL uses FALSE for false values
# File lib/sequel/adapters/shared/postgres.rb 2503 def literal_false 2504 'false' 2505 end
PostgreSQL quotes NaN and Infinity.
# File lib/sequel/adapters/shared/postgres.rb 2508 def literal_float(value) 2509 if value.finite? 2510 super 2511 elsif value.nan? 2512 "'NaN'" 2513 elsif value.infinite? == 1 2514 "'Infinity'" 2515 else 2516 "'-Infinity'" 2517 end 2518 end
Handle Ruby integers outside PostgreSQL bigint range specially.
# File lib/sequel/adapters/shared/postgres.rb 2521 def literal_integer(v) 2522 if v > 9223372036854775807 || v < -9223372036854775808 2523 literal_integer_outside_bigint_range(v) 2524 else 2525 v.to_s 2526 end 2527 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 2532 def literal_integer_outside_bigint_range(v) 2533 raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}" 2534 end
Assume that SQL
standard quoting is on, per Sequel's defaults
# File lib/sequel/adapters/shared/postgres.rb 2537 def literal_string_append(sql, v) 2538 sql << "'" << v.gsub("'", "''") << "'" 2539 end
PostgreSQL uses true for true values
# File lib/sequel/adapters/shared/postgres.rb 2542 def literal_true 2543 'true' 2544 end
PostgreSQL supports multiple rows in INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2547 def multi_insert_sql_strategy 2548 :values 2549 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 2566 def requires_like_escape? 2567 false 2568 end
Support FETCH FIRST WITH TIES on PostgreSQL 13+.
# File lib/sequel/adapters/shared/postgres.rb 2571 def select_limit_sql(sql) 2572 l = @opts[:limit] 2573 o = @opts[:offset] 2574 2575 return unless l || o 2576 2577 if @opts[:limit_with_ties] 2578 if o 2579 sql << " OFFSET " 2580 literal_append(sql, o) 2581 end 2582 2583 if l 2584 sql << " FETCH FIRST " 2585 literal_append(sql, l) 2586 sql << " ROWS WITH TIES" 2587 end 2588 else 2589 if l 2590 sql << " LIMIT " 2591 literal_append(sql, l) 2592 end 2593 2594 if o 2595 sql << " OFFSET " 2596 literal_append(sql, o) 2597 end 2598 end 2599 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 2603 def select_lock_sql(sql) 2604 lock = @opts[:lock] 2605 if lock == :share 2606 sql << ' FOR SHARE' 2607 else 2608 super 2609 end 2610 2611 if lock 2612 if @opts[:skip_locked] 2613 sql << " SKIP LOCKED" 2614 elsif @opts[:nowait] 2615 sql << " NOWAIT" 2616 end 2617 end 2618 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/postgres.rb 2621 def select_values_sql(sql) 2622 sql << "VALUES " 2623 expression_list_append(sql, opts[:values]) 2624 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/postgres.rb 2627 def select_with_sql_base 2628 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 2629 end
Support PostgreSQL 14+ CTE SEARCH/CYCLE clauses
# File lib/sequel/adapters/shared/postgres.rb 2632 def select_with_sql_cte(sql, cte) 2633 super 2634 select_with_sql_cte_search_cycle(sql, cte) 2635 end
# File lib/sequel/adapters/shared/postgres.rb 2637 def select_with_sql_cte_search_cycle(sql, cte) 2638 if search_opts = cte[:search] 2639 sql << if search_opts[:type] == :breadth 2640 " SEARCH BREADTH FIRST BY " 2641 else 2642 " SEARCH DEPTH FIRST BY " 2643 end 2644 2645 identifier_list_append(sql, Array(search_opts[:by])) 2646 sql << " SET " 2647 identifier_append(sql, search_opts[:set] || :ordercol) 2648 end 2649 2650 if cycle_opts = cte[:cycle] 2651 sql << " CYCLE " 2652 identifier_list_append(sql, Array(cycle_opts[:columns])) 2653 sql << " SET " 2654 identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle) 2655 if cycle_opts.has_key?(:cycle_value) 2656 sql << " TO " 2657 literal_append(sql, cycle_opts[:cycle_value]) 2658 sql << " DEFAULT " 2659 literal_append(sql, cycle_opts.fetch(:noncycle_value, false)) 2660 end 2661 sql << " USING " 2662 identifier_append(sql, cycle_opts[:path_column] || :path) 2663 end 2664 end
The version of the database server
# File lib/sequel/adapters/shared/postgres.rb 2667 def server_version 2668 db.server_version(@opts[:server]) 2669 end
PostgreSQL 9.4+ supports the FILTER clause for aggregate functions.
# File lib/sequel/adapters/shared/postgres.rb 2672 def supports_filtered_aggregates? 2673 server_version >= 90400 2674 end
PostgreSQL supports quoted function names.
# File lib/sequel/adapters/shared/postgres.rb 2677 def supports_quoted_function_names? 2678 true 2679 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/postgres.rb 2690 def update_from_sql(sql) 2691 join_from_sql(:FROM, sql) 2692 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/postgres.rb 2695 def update_table_sql(sql) 2696 sql << ' ' 2697 source_list_append(sql, @opts[:from][0..0]) 2698 end