module Sequel::Postgres::DatasetMethods
Constants
- LOCK_MODES
- NULL
Public Instance Methods
Source
# File lib/sequel/adapters/shared/postgres.rb 1827 def analyze 1828 explain(:analyze=>true) 1829 end
Return the results of an EXPLAIN ANALYZE query as a string
Source
# 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
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 1869 def disable_insert_returning 1870 clone(:disable_insert_returning=>true) 1871 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 1874 def empty? 1875 return false if @opts[:values] 1876 super 1877 end
Always return false when using VALUES
Source
# 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
Return the results of an EXPLAIN query as a string
Source
# 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
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 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
Insert given values into the database.
Source
# File lib/sequel/adapters/shared/postgres.rb 2001 def insert_conflict(opts=OPTS) 2002 clone(:insert_conflict => opts) 2003 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 2011 def insert_ignore 2012 insert_conflict 2013 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 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
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 2026 def insert_select_sql(*values) 2027 ds = opts[:returning] ? self : returning 2028 ds.insert_sql(*values) 2029 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 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
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 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
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 2062 def merge(&block) 2063 sql = merge_sql 2064 if uses_returning?(:merge) 2065 returning_fetch_rows(sql, &block) 2066 else 2067 execute_ddl(sql) 2068 end 2069 end
Support MERGE RETURNING on PostgreSQL 17+.
Source
# File lib/sequel/adapters/shared/postgres.rb 2080 def merge_delete_when_not_matched_by_source(&block) 2081 _merge_when(:type=>:delete_not_matched_by_source, &block) 2082 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 2093 def merge_do_nothing_when_matched(&block) 2094 _merge_when(:type=>:matched, &block) 2095 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 2106 def merge_do_nothing_when_not_matched(&block) 2107 _merge_when(:type=>:not_matched, &block) 2108 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 2119 def merge_do_nothing_when_not_matched_by_source(&block) 2120 _merge_when(:type=>:not_matched_by_source, &block) 2121 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 2124 def merge_insert(*values, &block) 2125 h = {:type=>:insert, :values=>values} 2126 if @opts[:override] 2127 h[:override] = insert_override_sql(String.new) 2128 end 2129 _merge_when(h, &block) 2130 end
Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.
Source
# File lib/sequel/adapters/shared/postgres.rb 2141 def merge_update_when_not_matched_by_source(values, &block) 2142 _merge_when(:type=>:update_not_matched_by_source, :values=>values, &block) 2143 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 2148 def overriding_system_value 2149 clone(:override=>:system) 2150 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 2154 def overriding_user_value 2155 clone(:override=>:user) 2156 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 2158 def supports_cte?(type=:select) 2159 if type == :select 2160 server_version >= 80400 2161 else 2162 server_version >= 90100 2163 end 2164 end
Source
# File lib/sequel/adapters/shared/postgres.rb 2168 def supports_cte_in_subqueries? 2169 supports_cte? 2170 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 2173 def supports_distinct_on? 2174 true 2175 end
DISTINCT ON is a PostgreSQL extension
Source
# File lib/sequel/adapters/shared/postgres.rb 2178 def supports_group_cube? 2179 server_version >= 90500 2180 end
PostgreSQL 9.5+ supports GROUP CUBE
Source
# File lib/sequel/adapters/shared/postgres.rb 2183 def supports_group_rollup? 2184 server_version >= 90500 2185 end
PostgreSQL 9.5+ supports GROUP ROLLUP
Source
# File lib/sequel/adapters/shared/postgres.rb 2188 def supports_grouping_sets? 2189 server_version >= 90500 2190 end
PostgreSQL 9.5+ supports GROUPING SETS
Source
# File lib/sequel/adapters/shared/postgres.rb 2198 def supports_insert_conflict? 2199 server_version >= 90500 2200 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
Source
# File lib/sequel/adapters/shared/postgres.rb 2193 def supports_insert_select? 2194 !@opts[:disable_insert_returning] 2195 end
True unless insert returning has been disabled for this dataset.
Source
# File lib/sequel/adapters/shared/postgres.rb 2203 def supports_lateral_subqueries? 2204 server_version >= 90300 2205 end
PostgreSQL 9.3+ supports lateral subqueries
Source
# File lib/sequel/adapters/shared/postgres.rb 2213 def supports_merge? 2214 server_version >= 150000 2215 end
PostgreSQL 15+ supports MERGE.
Source
# File lib/sequel/adapters/shared/postgres.rb 2208 def supports_modifying_joins? 2209 true 2210 end
PostgreSQL supports modifying joined datasets
Source
# File lib/sequel/adapters/shared/postgres.rb 2218 def supports_nowait? 2219 true 2220 end
PostgreSQL supports NOWAIT.
Source
# File lib/sequel/adapters/shared/postgres.rb 2233 def supports_regexp? 2234 true 2235 end
PostgreSQL supports pattern matching via regular expressions
Source
# File lib/sequel/adapters/shared/postgres.rb 2224 def supports_returning?(type) 2225 if type == :merge 2226 server_version >= 170000 2227 else 2228 true 2229 end 2230 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 2238 def supports_skip_locked? 2239 server_version >= 90500 2240 end
PostgreSQL 9.5+ supports SKIP LOCKED.
Source
# File lib/sequel/adapters/shared/postgres.rb 2245 def supports_timestamp_timezones? 2246 # SEQUEL6: Remove 2247 true 2248 end
PostgreSQL supports timezones in literal timestamps
Source
# File lib/sequel/adapters/shared/postgres.rb 2252 def supports_window_clause? 2253 server_version >= 80400 2254 end
PostgreSQL 8.4+ supports WINDOW clause.
Source
# File lib/sequel/adapters/shared/postgres.rb 2263 def supports_window_function_frame_option?(option) 2264 case option 2265 when :rows, :range 2266 true 2267 when :offset 2268 server_version >= 90000 2269 when :groups, :exclude 2270 server_version >= 110000 2271 else 2272 false 2273 end 2274 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 2257 def supports_window_functions? 2258 server_version >= 80400 2259 end
PostgreSQL 8.4+ supports window functions
Source
# File lib/sequel/adapters/shared/postgres.rb 2292 def truncate(opts = OPTS) 2293 if opts.empty? 2294 super() 2295 else 2296 clone(:truncate_opts=>opts).truncate 2297 end 2298 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 2303 def with_ties 2304 clone(:limit_with_ties=>true) 2305 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 2313 def _import(columns, values, opts=OPTS) 2314 if @opts[:returning] 2315 # no transaction: our multi_insert_sql_strategy should guarantee 2316 # that there's only ever a single statement. 2317 sql = multi_insert_sql(columns, values)[0] 2318 returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v} 2319 elsif opts[:return] == :primary_key 2320 returning(insert_pk)._import(columns, values, opts) 2321 else 2322 super 2323 end 2324 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 2326 def to_prepared_statement(type, *a) 2327 if type == :insert && !@opts.has_key?(:returning) 2328 returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) 2329 else 2330 super 2331 end 2332 end
Private Instance Methods
Source
# File lib/sequel/adapters/shared/postgres.rb 2347 def _merge_do_nothing_sql(sql, data) 2348 sql << " THEN DO NOTHING" 2349 end
Source
# File lib/sequel/adapters/shared/postgres.rb 2337 def _merge_insert_sql(sql, data) 2338 sql << " THEN INSERT" 2339 columns, values = _parse_insert_sql_args(data[:values]) 2340 _insert_columns_sql(sql, columns) 2341 if override = data[:override] 2342 sql << override 2343 end 2344 _insert_values_sql(sql, values) 2345 end
Append the INSERT sql used in a MERGE
Source
# File lib/sequel/adapters/shared/postgres.rb 2352 def _merge_when_sql(sql) 2353 super 2354 insert_returning_sql(sql) if uses_returning?(:merge) 2355 end
Support MERGE RETURNING on PostgreSQL 17+.
Source
# File lib/sequel/adapters/shared/postgres.rb 2358 def _truncate_sql(table) 2359 to = @opts[:truncate_opts] || OPTS 2360 "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}" 2361 end
Format TRUNCATE statement with PostgreSQL specific options.
Source
# File lib/sequel/adapters/shared/postgres.rb 2364 def aggreate_dataset_use_from_self? 2365 super || @opts[:values] 2366 end
Use from_self for aggregate dataset using VALUES.
Source
# File lib/sequel/adapters/shared/postgres.rb 2369 def check_truncation_allowed! 2370 raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group] 2371 raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join] 2372 end
Allow truncation of multiple source tables.
Source
# File lib/sequel/adapters/shared/postgres.rb 2538 def compound_dataset_sql_append(sql, ds) 2539 sql << '(' 2540 super 2541 sql << ')' 2542 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 2375 def default_timestamp_format 2376 "'%Y-%m-%d %H:%M:%S.%6N%z'" 2377 end
The strftime format to use when literalizing the time.
Source
# File lib/sequel/adapters/shared/postgres.rb 2380 def delete_from_sql(sql) 2381 sql << ' FROM ' 2382 source_list_append(sql, @opts[:from][0..0]) 2383 end
Only include the primary table in the main delete clause
Source
# File lib/sequel/adapters/shared/postgres.rb 2386 def delete_using_sql(sql) 2387 join_from_sql(:USING, sql) 2388 end
Use USING to specify additional tables in a delete query
Source
# File lib/sequel/adapters/shared/postgres.rb 2662 def full_text_string_join(cols) 2663 cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')} 2664 cols = cols.zip([' '] * cols.length).flatten 2665 cols.pop 2666 SQL::StringExpression.new(:'||', *cols) 2667 end
Concatenate the expressions with a space in between
Source
# File lib/sequel/adapters/shared/postgres.rb 2391 def insert_conflict_sql(sql) 2392 if opts = @opts[:insert_conflict] 2393 sql << " ON CONFLICT" 2394 2395 if target = opts[:constraint] 2396 sql << " ON CONSTRAINT " 2397 identifier_append(sql, target) 2398 elsif target = opts[:target] 2399 sql << ' ' 2400 identifier_append(sql, Array(target)) 2401 if conflict_where = opts[:conflict_where] 2402 sql << " WHERE " 2403 literal_append(sql, conflict_where) 2404 end 2405 end 2406 2407 if values = opts[:update] 2408 sql << " DO UPDATE SET " 2409 update_sql_values_hash(sql, values) 2410 if update_where = opts[:update_where] 2411 sql << " WHERE " 2412 literal_append(sql, update_where) 2413 end 2414 else 2415 sql << " DO NOTHING" 2416 end 2417 end 2418 end
Add ON CONFLICT clause if it should be used
Source
# File lib/sequel/adapters/shared/postgres.rb 2421 def insert_into_sql(sql) 2422 sql << " INTO " 2423 if (f = @opts[:from]) && f.length == 1 2424 identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first)) 2425 else 2426 source_list_append(sql, f) 2427 end 2428 end
Include aliases when inserting into a single table on PostgreSQL 9.5+.
Source
# File lib/sequel/adapters/shared/postgres.rb 2445 def insert_override_sql(sql) 2446 case opts[:override] 2447 when :system 2448 sql << " OVERRIDING SYSTEM VALUE" 2449 when :user 2450 sql << " OVERRIDING USER VALUE" 2451 end 2452 end
Support OVERRIDING SYSTEM|USER VALUE in insert statements
Source
# File lib/sequel/adapters/shared/postgres.rb 2431 def insert_pk 2432 (f = opts[:from]) && !f.empty? && (t = f.first) 2433 2434 t = t.call(self) if t.is_a? Sequel::SQL::DelayedEvaluation 2435 2436 case t 2437 when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier 2438 if pk = db.primary_key(t) 2439 Sequel::SQL::Identifier.new(pk) 2440 end 2441 end 2442 end
Return the primary key to use for RETURNING in an INSERT statement
Source
# File lib/sequel/adapters/shared/postgres.rb 2456 def join_from_sql(type, sql) 2457 if(from = @opts[:from][1..-1]).empty? 2458 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 2459 else 2460 sql << ' ' << type.to_s << ' ' 2461 source_list_append(sql, from) 2462 select_join_sql(sql) 2463 end 2464 end
For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.
Source
# File lib/sequel/adapters/shared/postgres.rb 2467 def join_using_clause_using_sql_append(sql, using_columns) 2468 if using_columns.is_a?(SQL::AliasedExpression) 2469 super(sql, using_columns.expression) 2470 sql << ' AS ' 2471 identifier_append(sql, using_columns.alias) 2472 else 2473 super 2474 end 2475 end
Support table aliases for USING columns
Source
# File lib/sequel/adapters/shared/postgres.rb 2478 def literal_blob_append(sql, v) 2479 sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'" 2480 end
Use a generic blob quoting method, hopefully overridden in one of the subadapter methods
Source
# File lib/sequel/adapters/shared/postgres.rb 2483 def literal_false 2484 'false' 2485 end
PostgreSQL uses FALSE for false values
Source
# File lib/sequel/adapters/shared/postgres.rb 2488 def literal_float(value) 2489 if value.finite? 2490 super 2491 elsif value.nan? 2492 "'NaN'" 2493 elsif value.infinite? == 1 2494 "'Infinity'" 2495 else 2496 "'-Infinity'" 2497 end 2498 end
PostgreSQL quotes NaN and Infinity.
Source
# File lib/sequel/adapters/shared/postgres.rb 2501 def literal_integer(v) 2502 if v > 9223372036854775807 || v < -9223372036854775808 2503 literal_integer_outside_bigint_range(v) 2504 else 2505 v.to_s 2506 end 2507 end
Handle Ruby integers outside PostgreSQL bigint range specially.
Source
# File lib/sequel/adapters/shared/postgres.rb 2512 def literal_integer_outside_bigint_range(v) 2513 raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}" 2514 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 2517 def literal_string_append(sql, v) 2518 sql << "'" << v.gsub("'", "''") << "'" 2519 end
Assume that SQL
standard quoting is on, per Sequel’s defaults
Source
# File lib/sequel/adapters/shared/postgres.rb 2522 def literal_true 2523 'true' 2524 end
PostgreSQL uses true for true values
Source
# File lib/sequel/adapters/shared/postgres.rb 2527 def multi_insert_sql_strategy 2528 :values 2529 end
PostgreSQL supports multiple rows in INSERT.
Source
Source
# File lib/sequel/adapters/shared/postgres.rb 2546 def requires_like_escape? 2547 false 2548 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 2551 def select_limit_sql(sql) 2552 l = @opts[:limit] 2553 o = @opts[:offset] 2554 2555 return unless l || o 2556 2557 if @opts[:limit_with_ties] 2558 if o 2559 sql << " OFFSET " 2560 literal_append(sql, o) 2561 end 2562 2563 if l 2564 sql << " FETCH FIRST " 2565 literal_append(sql, l) 2566 sql << " ROWS WITH TIES" 2567 end 2568 else 2569 if l 2570 sql << " LIMIT " 2571 literal_append(sql, l) 2572 end 2573 2574 if o 2575 sql << " OFFSET " 2576 literal_append(sql, o) 2577 end 2578 end 2579 end
Support FETCH FIRST WITH TIES on PostgreSQL 13+.
Source
# File lib/sequel/adapters/shared/postgres.rb 2583 def select_lock_sql(sql) 2584 lock = @opts[:lock] 2585 if lock == :share 2586 sql << ' FOR SHARE' 2587 else 2588 super 2589 end 2590 2591 if lock 2592 if @opts[:skip_locked] 2593 sql << " SKIP LOCKED" 2594 elsif @opts[:nowait] 2595 sql << " NOWAIT" 2596 end 2597 end 2598 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 2601 def select_values_sql(sql) 2602 sql << "VALUES " 2603 expression_list_append(sql, opts[:values]) 2604 end
Support VALUES clause instead of the SELECT clause to return rows.
Source
# File lib/sequel/adapters/shared/postgres.rb 2607 def select_with_sql_base 2608 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 2609 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
Source
# File lib/sequel/adapters/shared/postgres.rb 2612 def select_with_sql_cte(sql, cte) 2613 super 2614 select_with_sql_cte_search_cycle(sql, cte) 2615 end
Support PostgreSQL 14+ CTE SEARCH/CYCLE clauses
Source
# File lib/sequel/adapters/shared/postgres.rb 2617 def select_with_sql_cte_search_cycle(sql, cte) 2618 if search_opts = cte[:search] 2619 sql << if search_opts[:type] == :breadth 2620 " SEARCH BREADTH FIRST BY " 2621 else 2622 " SEARCH DEPTH FIRST BY " 2623 end 2624 2625 identifier_list_append(sql, Array(search_opts[:by])) 2626 sql << " SET " 2627 identifier_append(sql, search_opts[:set] || :ordercol) 2628 end 2629 2630 if cycle_opts = cte[:cycle] 2631 sql << " CYCLE " 2632 identifier_list_append(sql, Array(cycle_opts[:columns])) 2633 sql << " SET " 2634 identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle) 2635 if cycle_opts.has_key?(:cycle_value) 2636 sql << " TO " 2637 literal_append(sql, cycle_opts[:cycle_value]) 2638 sql << " DEFAULT " 2639 literal_append(sql, cycle_opts.fetch(:noncycle_value, false)) 2640 end 2641 sql << " USING " 2642 identifier_append(sql, cycle_opts[:path_column] || :path) 2643 end 2644 end
Source
# File lib/sequel/adapters/shared/postgres.rb 2647 def server_version 2648 db.server_version(@opts[:server]) 2649 end
The version of the database server
Source
# File lib/sequel/adapters/shared/postgres.rb 2652 def supports_filtered_aggregates? 2653 server_version >= 90400 2654 end
PostgreSQL 9.4+ supports the FILTER clause for aggregate functions.
Source
# File lib/sequel/adapters/shared/postgres.rb 2657 def supports_quoted_function_names? 2658 true 2659 end
PostgreSQL supports quoted function names.
Source
# File lib/sequel/adapters/shared/postgres.rb 2670 def update_from_sql(sql) 2671 join_from_sql(:FROM, sql) 2672 end
Use FROM to specify additional tables in an update query
Source
# File lib/sequel/adapters/shared/postgres.rb 2675 def update_table_sql(sql) 2676 sql << ' ' 2677 source_list_append(sql, @opts[:from][0..0]) 2678 end
Only include the primary table in the main update clause