module Sequel::Postgres::DatasetMethods
Constants
- LOCK_MODES
- NULL
Public Instance Methods
Source
# File lib/sequel/adapters/shared/postgres.rb 1839 def analyze 1840 explain(:analyze=>true) 1841 end
Return the results of an EXPLAIN ANALYZE query as a string
Source
# File lib/sequel/adapters/shared/postgres.rb 1846 def complex_expression_sql_append(sql, op, args) 1847 case op 1848 when :^ 1849 j = ' # ' 1850 c = false 1851 args.each do |a| 1852 sql << j if c 1853 literal_append(sql, a) 1854 c ||= true 1855 end 1856 when :ILIKE, :'NOT ILIKE' 1857 sql << '(' 1858 literal_append(sql, args[0]) 1859 sql << ' ' << op.to_s << ' ' 1860 literal_append(sql, args[1]) 1861 sql << ')' 1862 else 1863 super 1864 end 1865 end
Handle converting the ruby xor operator (^) into the PostgreSQL xor operator (#), and use the ILIKE and NOT ILIKE operators.
Source
# File lib/sequel/adapters/shared/postgres.rb 1881 def disable_insert_returning 1882 clone(:disable_insert_returning=>true) 1883 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).
Source
# File lib/sequel/adapters/shared/postgres.rb 1886 def empty? 1887 return false if @opts[:values] 1888 super 1889 end
Always return false when using VALUES
Source
# File lib/sequel/adapters/shared/postgres.rb 1892 def explain(opts=OPTS) 1893 with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n") 1894 end
Return the results of an EXPLAIN query as a string
Source
# File lib/sequel/adapters/shared/postgres.rb 1920 def full_text_search(cols, terms, opts = OPTS) 1921 lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 1922 1923 unless opts[:tsvector] 1924 phrase_cols = full_text_string_join(cols) 1925 cols = Sequel.function(:to_tsvector, lang, phrase_cols) 1926 end 1927 1928 unless opts[:tsquery] 1929 phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 1930 1931 query_func = case to_tsquery = opts[:to_tsquery] 1932 when :phrase, :plain 1933 :"#{to_tsquery}to_tsquery" 1934 when :websearch 1935 :"websearch_to_tsquery" 1936 else 1937 (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 1938 end 1939 1940 terms = Sequel.function(query_func, lang, phrase_terms) 1941 end 1942 1943 ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) 1944 1945 if opts[:phrase] 1946 raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 1947 ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 1948 end 1949 1950 if opts[:rank] 1951 ds = ds.reverse{ts_rank_cd(cols, terms)} 1952 end 1953 1954 if opts[:headline] 1955 ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 1956 end 1957 1958 ds 1959 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.
Source
# File lib/sequel/adapters/shared/postgres.rb 1962 def insert(*values) 1963 if @opts[:returning] 1964 # Already know which columns to return, let the standard code handle it 1965 super 1966 elsif @opts[:sql] || @opts[:disable_insert_returning] 1967 # Raw SQL used or RETURNING disabled, just use the default behavior 1968 # and return nil since sequence is not known. 1969 super 1970 nil 1971 else 1972 # Force the use of RETURNING with the primary key value, 1973 # unless it has been disabled. 1974 returning(insert_pk).insert(*values){|r| return r.values.first} 1975 end 1976 end
Insert given values into the database.
Source
# File lib/sequel/adapters/shared/postgres.rb 2013 def insert_conflict(opts=OPTS) 2014 clone(:insert_conflict => opts) 2015 end
Handle uniqueness violations when inserting, by updating the conflicting row, using ON CONFLICT. With no options, uses ON CONFLICT DO NOTHING. Options:
- :conflict_where
-
The index filter, when using a partial index to determine uniqueness.
- :constraint
-
An explicit constraint name, has precendence over :target.
- :target
-
The column name or expression to handle uniqueness violations on.
- :update
-
A hash of columns and values to set. Uses ON CONFLICT DO UPDATE.
- :update_where
-
A WHERE condition to use for the update.
Examples:
DB[:table].insert_conflict.insert(a: 1, b: 2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT DO NOTHING DB[:table].insert_conflict(constraint: :table_a_uidx).insert(a: 1, b: 2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT ON CONSTRAINT table_a_uidx DO NOTHING DB[:table].insert_conflict(target: :a).insert(a: 1, b: 2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT (a) DO NOTHING DB[:table].insert_conflict(target: :a, conflict_where: {c: true}).insert(a: 1, b: 2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT (a) WHERE (c IS TRUE) DO NOTHING DB[:table].insert_conflict(target: :a, update: {b: Sequel[:excluded][:b]}).insert(a: 1, b: 2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT (a) DO UPDATE SET b = excluded.b DB[:table].insert_conflict(constraint: :table_a_uidx, update: {b: Sequel[:excluded][:b]}, update_where: {Sequel[:table][:status_id] => 1}).insert(a: 1, b: 2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT ON CONSTRAINT table_a_uidx # DO UPDATE SET b = excluded.b WHERE (table.status_id = 1)
Source
# File lib/sequel/adapters/shared/postgres.rb 2023 def insert_ignore 2024 insert_conflict 2025 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
Source
# File lib/sequel/adapters/shared/postgres.rb 2030 def insert_select(*values) 2031 return unless supports_insert_select? 2032 # Handle case where query does not return a row 2033 server?(:default).with_sql_first(insert_select_sql(*values)) || false 2034 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.
Source
# File lib/sequel/adapters/shared/postgres.rb 2038 def insert_select_sql(*values) 2039 ds = opts[:returning] ? self : returning 2040 ds.insert_sql(*values) 2041 end
The SQL
to use for an insert_select
, adds a RETURNING clause to the insert unless the RETURNING clause is already present.
Source
# File lib/sequel/adapters/shared/postgres.rb 2045 def join_table(type, table, expr=nil, options=OPTS, &block) 2046 if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all? 2047 options = options.merge(:join_using=>true) 2048 end 2049 super 2050 end
Support SQL::AliasedExpression
as expr to setup a USING join with a table alias for the USING columns.
Source
# File lib/sequel/adapters/shared/postgres.rb 2057 def lock(mode, opts=OPTS) 2058 if defined?(yield) # perform locking inside a transaction and yield to block 2059 @db.transaction(opts){lock(mode, opts); yield} 2060 else 2061 sql = 'LOCK TABLE '.dup 2062 source_list_append(sql, @opts[:from]) 2063 mode = mode.to_s.upcase.strip 2064 unless LOCK_MODES.include?(mode) 2065 raise Error, "Unsupported lock mode: #{mode}" 2066 end 2067 sql << " IN #{mode} MODE" 2068 @db.execute(sql, opts) 2069 end 2070 nil 2071 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.
Source
# File lib/sequel/adapters/shared/postgres.rb 2074 def merge(&block) 2075 sql = merge_sql 2076 if uses_returning?(:merge) 2077 returning_fetch_rows(sql, &block) 2078 else 2079 execute_ddl(sql) 2080 end 2081 end
Support MERGE RETURNING on PostgreSQL 17+.
Source
# File lib/sequel/adapters/shared/postgres.rb 2092 def merge_delete_when_not_matched_by_source(&block) 2093 _merge_when(:type=>:delete_not_matched_by_source, &block) 2094 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
Source
# File lib/sequel/adapters/shared/postgres.rb 2105 def merge_do_nothing_when_matched(&block) 2106 _merge_when(:type=>:matched, &block) 2107 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
Source
# File lib/sequel/adapters/shared/postgres.rb 2118 def merge_do_nothing_when_not_matched(&block) 2119 _merge_when(:type=>:not_matched, &block) 2120 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
Source
# File lib/sequel/adapters/shared/postgres.rb 2131 def merge_do_nothing_when_not_matched_by_source(&block) 2132 _merge_when(:type=>:not_matched_by_source, &block) 2133 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
Source
# File lib/sequel/adapters/shared/postgres.rb 2136 def merge_insert(*values, &block) 2137 h = {:type=>:insert, :values=>values} 2138 if @opts[:override] 2139 h[:override] = insert_override_sql(String.new) 2140 end 2141 _merge_when(h, &block) 2142 end
Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.
Source
# File lib/sequel/adapters/shared/postgres.rb 2153 def merge_update_when_not_matched_by_source(values, &block) 2154 _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block) 2155 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
Source
# File lib/sequel/adapters/shared/postgres.rb 2160 def overriding_system_value 2161 clone(:override=>:system) 2162 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.
Source
# File lib/sequel/adapters/shared/postgres.rb 2166 def overriding_user_value 2167 clone(:override=>:user) 2168 end
Use OVERRIDING USER VALUE for INSERT statements, so that identity columns always use the sequence value instead of the user supplied value.
Source
# File lib/sequel/adapters/shared/postgres.rb 2170 def supports_cte?(type=:select) 2171 if type == :select 2172 server_version >= 80400 2173 else 2174 server_version >= 90100 2175 end 2176 end
Source
# File lib/sequel/adapters/shared/postgres.rb 2180 def supports_cte_in_subqueries? 2181 supports_cte? 2182 end
PostgreSQL supports using the WITH clause in subqueries if it supports using WITH at all (i.e. on PostgreSQL 8.4+).
Source
# File lib/sequel/adapters/shared/postgres.rb 2185 def supports_distinct_on? 2186 true 2187 end
DISTINCT ON is a PostgreSQL extension
Source
# File lib/sequel/adapters/shared/postgres.rb 2190 def supports_group_cube? 2191 server_version >= 90500 2192 end
PostgreSQL 9.5+ supports GROUP CUBE
Source
# File lib/sequel/adapters/shared/postgres.rb 2195 def supports_group_rollup? 2196 server_version >= 90500 2197 end
PostgreSQL 9.5+ supports GROUP ROLLUP
Source
# File lib/sequel/adapters/shared/postgres.rb 2200 def supports_grouping_sets? 2201 server_version >= 90500 2202 end
PostgreSQL 9.5+ supports GROUPING SETS
Source
# File lib/sequel/adapters/shared/postgres.rb 2210 def supports_insert_conflict? 2211 server_version >= 90500 2212 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
Source
# File lib/sequel/adapters/shared/postgres.rb 2205 def supports_insert_select? 2206 !@opts[:disable_insert_returning] 2207 end
True unless insert returning has been disabled for this dataset.
Source
# File lib/sequel/adapters/shared/postgres.rb 2215 def supports_lateral_subqueries? 2216 server_version >= 90300 2217 end
PostgreSQL 9.3+ supports lateral subqueries
Source
# File lib/sequel/adapters/shared/postgres.rb 2225 def supports_merge? 2226 server_version >= 150000 2227 end
PostgreSQL 15+ supports MERGE.
Source
# File lib/sequel/adapters/shared/postgres.rb 2220 def supports_modifying_joins? 2221 true 2222 end
PostgreSQL supports modifying joined datasets
Source
# File lib/sequel/adapters/shared/postgres.rb 2230 def supports_nowait? 2231 true 2232 end
PostgreSQL supports NOWAIT.
Source
# File lib/sequel/adapters/shared/postgres.rb 2245 def supports_regexp? 2246 true 2247 end
PostgreSQL supports pattern matching via regular expressions
Source
# File lib/sequel/adapters/shared/postgres.rb 2236 def supports_returning?(type) 2237 if type == :merge 2238 server_version >= 170000 2239 else 2240 true 2241 end 2242 end
MERGE RETURNING is supported on PostgreSQL 17+. Other RETURNING is supported on all supported PostgreSQL versions.
Source
# File lib/sequel/adapters/shared/postgres.rb 2250 def supports_skip_locked? 2251 server_version >= 90500 2252 end
PostgreSQL 9.5+ supports SKIP LOCKED.
Source
# File lib/sequel/adapters/shared/postgres.rb 2257 def supports_timestamp_timezones? 2258 # SEQUEL6: Remove 2259 true 2260 end
PostgreSQL supports timezones in literal timestamps
Source
# File lib/sequel/adapters/shared/postgres.rb 2264 def supports_window_clause? 2265 server_version >= 80400 2266 end
PostgreSQL 8.4+ supports WINDOW clause.
Source
# File lib/sequel/adapters/shared/postgres.rb 2275 def supports_window_function_frame_option?(option) 2276 case option 2277 when :rows, :range 2278 true 2279 when :offset 2280 server_version >= 90000 2281 when :groups, :exclude 2282 server_version >= 110000 2283 else 2284 false 2285 end 2286 end
Base support added in 8.4, offset supported added in 9.0, GROUPS and EXCLUDE support added in 11.0.
Source
# File lib/sequel/adapters/shared/postgres.rb 2269 def supports_window_functions? 2270 server_version >= 80400 2271 end
PostgreSQL 8.4+ supports window functions
Source
# File lib/sequel/adapters/shared/postgres.rb 2304 def truncate(opts = OPTS) 2305 if opts.empty? 2306 super() 2307 else 2308 clone(:truncate_opts=>opts).truncate 2309 end 2310 end
Truncates the dataset. Returns nil.
Options:
- :cascade
-
whether to use the CASCADE option, useful when truncating tables with foreign keys.
- :only
-
truncate using ONLY, so child tables are unaffected
- :restart
-
use RESTART IDENTITY to restart any related sequences
:only and :restart only work correctly on PostgreSQL 8.4+.
Usage:
DB[:table].truncate # TRUNCATE TABLE "table" DB[:table].truncate(cascade: true, only: true, restart: true) # TRUNCATE TABLE ONLY "table" RESTART IDENTITY CASCADE
Source
# File lib/sequel/adapters/shared/postgres.rb 2315 def with_ties 2316 clone(:limit_with_ties=>true) 2317 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.
Protected Instance Methods
Source
# File lib/sequel/adapters/shared/postgres.rb 2325 def _import(columns, values, opts=OPTS) 2326 if @opts[:returning] 2327 # no transaction: our multi_insert_sql_strategy should guarantee 2328 # that there's only ever a single statement. 2329 sql = multi_insert_sql(columns, values)[0] 2330 returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v} 2331 elsif opts[:return] == :primary_key 2332 returning(insert_pk)._import(columns, values, opts) 2333 else 2334 super 2335 end 2336 end
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.
Source
# File lib/sequel/adapters/shared/postgres.rb 2338 def to_prepared_statement(type, *a) 2339 if type == :insert && !@opts.has_key?(:returning) 2340 returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) 2341 else 2342 super 2343 end 2344 end
Private Instance Methods
Source
# File lib/sequel/adapters/shared/postgres.rb 2359 def _merge_do_nothing_sql(sql, data) 2360 sql << " THEN DO NOTHING" 2361 end
Source
# File lib/sequel/adapters/shared/postgres.rb 2349 def _merge_insert_sql(sql, data) 2350 sql << " THEN INSERT" 2351 columns, values = _parse_insert_sql_args(data[:values]) 2352 _insert_columns_sql(sql, columns) 2353 if override = data[:override] 2354 sql << override 2355 end 2356 _insert_values_sql(sql, values) 2357 end
Append the INSERT sql used in a MERGE
Source
# File lib/sequel/adapters/shared/postgres.rb 2364 def _merge_when_sql(sql) 2365 super 2366 insert_returning_sql(sql) if uses_returning?(:merge) 2367 end
Support MERGE RETURNING on PostgreSQL 17+.
Source
# File lib/sequel/adapters/shared/postgres.rb 2370 def _truncate_sql(table) 2371 to = @opts[:truncate_opts] || OPTS 2372 "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}" 2373 end
Format TRUNCATE statement with PostgreSQL specific options.
Source
# File lib/sequel/adapters/shared/postgres.rb 2376 def aggreate_dataset_use_from_self? 2377 super || @opts[:values] 2378 end
Use from_self for aggregate dataset using VALUES.
Source
# File lib/sequel/adapters/shared/postgres.rb 2381 def check_truncation_allowed! 2382 raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group] 2383 raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join] 2384 end
Allow truncation of multiple source tables.
Source
# File lib/sequel/adapters/shared/postgres.rb 2569 def compound_dataset_sql_append(sql, ds) 2570 sql << '(' 2571 super 2572 sql << ')' 2573 end
PostgreSQL requires parentheses around compound datasets if they use CTEs, and using them in other places doesn’t hurt.
Source
# File lib/sequel/adapters/shared/postgres.rb 2387 def default_timestamp_format 2388 "'%Y-%m-%d %H:%M:%S.%6N%z'" 2389 end
The strftime format to use when literalizing the time.
Source
# File lib/sequel/adapters/shared/postgres.rb 2392 def delete_from_sql(sql) 2393 sql << ' FROM ' 2394 source_list_append(sql, @opts[:from][0..0]) 2395 end
Only include the primary table in the main delete clause
Source
# File lib/sequel/adapters/shared/postgres.rb 2398 def delete_using_sql(sql) 2399 join_from_sql(:USING, sql) 2400 end
Use USING to specify additional tables in a delete query
Source
# File lib/sequel/adapters/shared/postgres.rb 2404 def derived_column_list_sql_append(sql, column_aliases) 2405 c = false 2406 comma = ', ' 2407 column_aliases.each do |a| 2408 sql << comma if c 2409 if a.is_a?(Array) 2410 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 2411 a, type = a 2412 identifier_append(sql, a) 2413 sql << " " << db.cast_type_literal(type).to_s 2414 else 2415 identifier_append(sql, a) 2416 end 2417 c ||= true 2418 end 2419 end
Handle column aliases containing data types, useful for selecting from functions that return the record data type.
Source
# File lib/sequel/adapters/shared/postgres.rb 2693 def full_text_string_join(cols) 2694 cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')} 2695 cols = cols.zip([' '] * cols.length).flatten 2696 cols.pop 2697 SQL::StringExpression.new(:'||', *cols) 2698 end
Concatenate the expressions with a space in between
Source
# File lib/sequel/adapters/shared/postgres.rb 2422 def insert_conflict_sql(sql) 2423 if opts = @opts[:insert_conflict] 2424 sql << " ON CONFLICT" 2425 2426 if target = opts[:constraint] 2427 sql << " ON CONSTRAINT " 2428 identifier_append(sql, target) 2429 elsif target = opts[:target] 2430 sql << ' ' 2431 identifier_append(sql, Array(target)) 2432 if conflict_where = opts[:conflict_where] 2433 sql << " WHERE " 2434 literal_append(sql, conflict_where) 2435 end 2436 end 2437 2438 if values = opts[:update] 2439 sql << " DO UPDATE SET " 2440 update_sql_values_hash(sql, values) 2441 if update_where = opts[:update_where] 2442 sql << " WHERE " 2443 literal_append(sql, update_where) 2444 end 2445 else 2446 sql << " DO NOTHING" 2447 end 2448 end 2449 end
Add ON CONFLICT clause if it should be used
Source
# File lib/sequel/adapters/shared/postgres.rb 2452 def insert_into_sql(sql) 2453 sql << " INTO " 2454 if (f = @opts[:from]) && f.length == 1 2455 identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first)) 2456 else 2457 source_list_append(sql, f) 2458 end 2459 end
Include aliases when inserting into a single table on PostgreSQL 9.5+.
Source
# File lib/sequel/adapters/shared/postgres.rb 2476 def insert_override_sql(sql) 2477 case opts[:override] 2478 when :system 2479 sql << " OVERRIDING SYSTEM VALUE" 2480 when :user 2481 sql << " OVERRIDING USER VALUE" 2482 end 2483 end
Support OVERRIDING SYSTEM|USER VALUE in insert statements
Source
# File lib/sequel/adapters/shared/postgres.rb 2462 def insert_pk 2463 (f = opts[:from]) && !f.empty? && (t = f.first) 2464 2465 t = t.call(self) if t.is_a? Sequel::SQL::DelayedEvaluation 2466 2467 case t 2468 when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier 2469 if pk = db.primary_key(t) 2470 Sequel::SQL::Identifier.new(pk) 2471 end 2472 end 2473 end
Return the primary key to use for RETURNING in an INSERT statement
Source
# File lib/sequel/adapters/shared/postgres.rb 2487 def join_from_sql(type, sql) 2488 if(from = @opts[:from][1..-1]).empty? 2489 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 2490 else 2491 sql << ' ' << type.to_s << ' ' 2492 source_list_append(sql, from) 2493 select_join_sql(sql) 2494 end 2495 end
For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.
Source
# File lib/sequel/adapters/shared/postgres.rb 2498 def join_using_clause_using_sql_append(sql, using_columns) 2499 if using_columns.is_a?(SQL::AliasedExpression) 2500 super(sql, using_columns.expression) 2501 sql << ' AS ' 2502 identifier_append(sql, using_columns.alias) 2503 else 2504 super 2505 end 2506 end
Support table aliases for USING columns
Source
# File lib/sequel/adapters/shared/postgres.rb 2509 def literal_blob_append(sql, v) 2510 sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'" 2511 end
Use a generic blob quoting method, hopefully overridden in one of the subadapter methods
Source
# File lib/sequel/adapters/shared/postgres.rb 2514 def literal_false 2515 'false' 2516 end
PostgreSQL uses FALSE for false values
Source
# File lib/sequel/adapters/shared/postgres.rb 2519 def literal_float(value) 2520 if value.finite? 2521 super 2522 elsif value.nan? 2523 "'NaN'" 2524 elsif value.infinite? == 1 2525 "'Infinity'" 2526 else 2527 "'-Infinity'" 2528 end 2529 end
PostgreSQL quotes NaN and Infinity.
Source
# File lib/sequel/adapters/shared/postgres.rb 2532 def literal_integer(v) 2533 if v > 9223372036854775807 || v < -9223372036854775808 2534 literal_integer_outside_bigint_range(v) 2535 else 2536 v.to_s 2537 end 2538 end
Handle Ruby integers outside PostgreSQL bigint range specially.
Source
# File lib/sequel/adapters/shared/postgres.rb 2543 def literal_integer_outside_bigint_range(v) 2544 raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}" 2545 end
Raise IntegerOutsideBigintRange
when attempting to literalize Ruby integer outside PostgreSQL bigint range, so PostgreSQL doesn’t treat the value as numeric.
Source
# File lib/sequel/adapters/shared/postgres.rb 2548 def literal_string_append(sql, v) 2549 sql << "'" << v.gsub("'", "''") << "'" 2550 end
Assume that SQL
standard quoting is on, per Sequel’s defaults
Source
# File lib/sequel/adapters/shared/postgres.rb 2553 def literal_true 2554 'true' 2555 end
PostgreSQL uses true for true values
Source
# File lib/sequel/adapters/shared/postgres.rb 2558 def multi_insert_sql_strategy 2559 :values 2560 end
PostgreSQL supports multiple rows in INSERT.
Source
Source
# File lib/sequel/adapters/shared/postgres.rb 2577 def requires_like_escape? 2578 false 2579 end
Backslash is supported by default as the escape character on PostgreSQL, and using ESCAPE can break LIKE ANY() usage.
Source
# File lib/sequel/adapters/shared/postgres.rb 2582 def select_limit_sql(sql) 2583 l = @opts[:limit] 2584 o = @opts[:offset] 2585 2586 return unless l || o 2587 2588 if @opts[:limit_with_ties] 2589 if o 2590 sql << " OFFSET " 2591 literal_append(sql, o) 2592 end 2593 2594 if l 2595 sql << " FETCH FIRST " 2596 literal_append(sql, l) 2597 sql << " ROWS WITH TIES" 2598 end 2599 else 2600 if l 2601 sql << " LIMIT " 2602 literal_append(sql, l) 2603 end 2604 2605 if o 2606 sql << " OFFSET " 2607 literal_append(sql, o) 2608 end 2609 end 2610 end
Support FETCH FIRST WITH TIES on PostgreSQL 13+.
Source
# File lib/sequel/adapters/shared/postgres.rb 2614 def select_lock_sql(sql) 2615 lock = @opts[:lock] 2616 if lock == :share 2617 sql << ' FOR SHARE' 2618 else 2619 super 2620 end 2621 2622 if lock 2623 if @opts[:skip_locked] 2624 sql << " SKIP LOCKED" 2625 elsif @opts[:nowait] 2626 sql << " NOWAIT" 2627 end 2628 end 2629 end
Support FOR SHARE locking when using the :share lock style. Use SKIP LOCKED if skipping locked rows.
Source
# File lib/sequel/adapters/shared/postgres.rb 2632 def select_values_sql(sql) 2633 sql << "VALUES " 2634 expression_list_append(sql, opts[:values]) 2635 end
Support VALUES clause instead of the SELECT clause to return rows.
Source
# File lib/sequel/adapters/shared/postgres.rb 2638 def select_with_sql_base 2639 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 2640 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
Source
# File lib/sequel/adapters/shared/postgres.rb 2643 def select_with_sql_cte(sql, cte) 2644 super 2645 select_with_sql_cte_search_cycle(sql, cte) 2646 end
Support PostgreSQL 14+ CTE SEARCH/CYCLE clauses
Source
# File lib/sequel/adapters/shared/postgres.rb 2648 def select_with_sql_cte_search_cycle(sql, cte) 2649 if search_opts = cte[:search] 2650 sql << if search_opts[:type] == :breadth 2651 " SEARCH BREADTH FIRST BY " 2652 else 2653 " SEARCH DEPTH FIRST BY " 2654 end 2655 2656 identifier_list_append(sql, Array(search_opts[:by])) 2657 sql << " SET " 2658 identifier_append(sql, search_opts[:set] || :ordercol) 2659 end 2660 2661 if cycle_opts = cte[:cycle] 2662 sql << " CYCLE " 2663 identifier_list_append(sql, Array(cycle_opts[:columns])) 2664 sql << " SET " 2665 identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle) 2666 if cycle_opts.has_key?(:cycle_value) 2667 sql << " TO " 2668 literal_append(sql, cycle_opts[:cycle_value]) 2669 sql << " DEFAULT " 2670 literal_append(sql, cycle_opts.fetch(:noncycle_value, false)) 2671 end 2672 sql << " USING " 2673 identifier_append(sql, cycle_opts[:path_column] || :path) 2674 end 2675 end
Source
# File lib/sequel/adapters/shared/postgres.rb 2678 def server_version 2679 db.server_version(@opts[:server]) 2680 end
The version of the database server
Source
# File lib/sequel/adapters/shared/postgres.rb 2683 def supports_filtered_aggregates? 2684 server_version >= 90400 2685 end
PostgreSQL 9.4+ supports the FILTER clause for aggregate functions.
Source
# File lib/sequel/adapters/shared/postgres.rb 2688 def supports_quoted_function_names? 2689 true 2690 end
PostgreSQL supports quoted function names.
Source
# File lib/sequel/adapters/shared/postgres.rb 2701 def update_from_sql(sql) 2702 join_from_sql(:FROM, sql) 2703 end
Use FROM to specify additional tables in an update query
Source
# File lib/sequel/adapters/shared/postgres.rb 2706 def update_table_sql(sql) 2707 sql << ' ' 2708 source_list_append(sql, @opts[:from][0..0]) 2709 end
Only include the primary table in the main update clause