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 1726 def analyze 1727 explain(:analyze=>true) 1728 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 1733 def complex_expression_sql_append(sql, op, args) 1734 case op 1735 when :^ 1736 j = ' # ' 1737 c = false 1738 args.each do |a| 1739 sql << j if c 1740 literal_append(sql, a) 1741 c ||= true 1742 end 1743 when :ILIKE, :'NOT ILIKE' 1744 sql << '(' 1745 literal_append(sql, args[0]) 1746 sql << ' ' << op.to_s << ' ' 1747 literal_append(sql, args[1]) 1748 sql << ')' 1749 else 1750 super 1751 end 1752 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 1768 def disable_insert_returning 1769 clone(:disable_insert_returning=>true) 1770 end
Always return false when using VALUES
# File lib/sequel/adapters/shared/postgres.rb 1773 def empty? 1774 return false if @opts[:values] 1775 super 1776 end
Return the results of an EXPLAIN query as a string
# File lib/sequel/adapters/shared/postgres.rb 1779 def explain(opts=OPTS) 1780 with_sql((opts[:analyze] ? 'EXPLAIN ANALYZE ' : 'EXPLAIN ') + select_sql).map(:'QUERY PLAN').join("\r\n") 1781 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 or :phrase 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 1807 def full_text_search(cols, terms, opts = OPTS) 1808 lang = Sequel.cast(opts[:language] || 'simple', :regconfig) 1809 1810 unless opts[:tsvector] 1811 phrase_cols = full_text_string_join(cols) 1812 cols = Sequel.function(:to_tsvector, lang, phrase_cols) 1813 end 1814 1815 unless opts[:tsquery] 1816 phrase_terms = terms.is_a?(Array) ? terms.join(' | ') : terms 1817 1818 query_func = case to_tsquery = opts[:to_tsquery] 1819 when :phrase, :plain 1820 :"#{to_tsquery}to_tsquery" 1821 else 1822 (opts[:phrase] || opts[:plain]) ? :plainto_tsquery : :to_tsquery 1823 end 1824 1825 terms = Sequel.function(query_func, lang, phrase_terms) 1826 end 1827 1828 ds = where(Sequel.lit(["", " @@ ", ""], cols, terms)) 1829 1830 if opts[:phrase] 1831 raise Error, "can't use :phrase with either :tsvector or :tsquery arguments to full_text_search together" if opts[:tsvector] || opts[:tsquery] 1832 ds = ds.grep(phrase_cols, "%#{escape_like(phrase_terms)}%", :case_insensitive=>true) 1833 end 1834 1835 if opts[:rank] 1836 ds = ds.reverse{ts_rank_cd(cols, terms)} 1837 end 1838 1839 if opts[:headline] 1840 ds = ds.select_append{ts_headline(lang, phrase_cols, terms).as(:headline)} 1841 end 1842 1843 ds 1844 end
Insert given values into the database.
# File lib/sequel/adapters/shared/postgres.rb 1847 def insert(*values) 1848 if @opts[:returning] 1849 # Already know which columns to return, let the standard code handle it 1850 super 1851 elsif @opts[:sql] || @opts[:disable_insert_returning] 1852 # Raw SQL used or RETURNING disabled, just use the default behavior 1853 # and return nil since sequence is not known. 1854 super 1855 nil 1856 else 1857 # Force the use of RETURNING with the primary key value, 1858 # unless it has been disabled. 1859 returning(insert_pk).insert(*values){|r| return r.values.first} 1860 end 1861 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 1898 def insert_conflict(opts=OPTS) 1899 clone(:insert_conflict => opts) 1900 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 1908 def insert_ignore 1909 insert_conflict 1910 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 1915 def insert_select(*values) 1916 return unless supports_insert_select? 1917 # Handle case where query does not return a row 1918 server?(:default).with_sql_first(insert_select_sql(*values)) || false 1919 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 1923 def insert_select_sql(*values) 1924 ds = opts[:returning] ? self : returning 1925 ds.insert_sql(*values) 1926 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 1930 def join_table(type, table, expr=nil, options=OPTS, &block) 1931 if expr.is_a?(SQL::AliasedExpression) && expr.expression.is_a?(Array) && !expr.expression.empty? && expr.expression.all? 1932 options = options.merge(:join_using=>true) 1933 end 1934 super 1935 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 1942 def lock(mode, opts=OPTS) 1943 if defined?(yield) # perform locking inside a transaction and yield to block 1944 @db.transaction(opts){lock(mode, opts); yield} 1945 else 1946 sql = 'LOCK TABLE '.dup 1947 source_list_append(sql, @opts[:from]) 1948 mode = mode.to_s.upcase.strip 1949 unless LOCK_MODES.include?(mode) 1950 raise Error, "Unsupported lock mode: #{mode}" 1951 end 1952 sql << " IN #{mode} MODE" 1953 @db.execute(sql, opts) 1954 end 1955 nil 1956 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 1967 def merge_do_nothing_when_matched(&block) 1968 _merge_when(:type=>:matched, &block) 1969 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 1980 def merge_do_nothing_when_not_matched(&block) 1981 _merge_when(:type=>:not_matched, &block) 1982 end
Support OVERRIDING USER|SYSTEM VALUE for MERGE INSERT.
# File lib/sequel/adapters/shared/postgres.rb 1985 def merge_insert(*values, &block) 1986 h = {:type=>:insert, :values=>values} 1987 if override = @opts[:override] 1988 h[:override] = insert_override_sql(String.new) 1989 end 1990 _merge_when(h, &block) 1991 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 1996 def overriding_system_value 1997 clone(:override=>:system) 1998 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 2002 def overriding_user_value 2003 clone(:override=>:user) 2004 end
# File lib/sequel/adapters/shared/postgres.rb 2006 def supports_cte?(type=:select) 2007 if type == :select 2008 server_version >= 80400 2009 else 2010 server_version >= 90100 2011 end 2012 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 2016 def supports_cte_in_subqueries? 2017 supports_cte? 2018 end
DISTINCT ON is a PostgreSQL extension
# File lib/sequel/adapters/shared/postgres.rb 2021 def supports_distinct_on? 2022 true 2023 end
PostgreSQL 9.5+ supports GROUP CUBE
# File lib/sequel/adapters/shared/postgres.rb 2026 def supports_group_cube? 2027 server_version >= 90500 2028 end
PostgreSQL 9.5+ supports GROUP ROLLUP
# File lib/sequel/adapters/shared/postgres.rb 2031 def supports_group_rollup? 2032 server_version >= 90500 2033 end
PostgreSQL 9.5+ supports GROUPING SETS
# File lib/sequel/adapters/shared/postgres.rb 2036 def supports_grouping_sets? 2037 server_version >= 90500 2038 end
PostgreSQL 9.5+ supports the ON CONFLICT clause to INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2046 def supports_insert_conflict? 2047 server_version >= 90500 2048 end
True unless insert returning has been disabled for this dataset.
# File lib/sequel/adapters/shared/postgres.rb 2041 def supports_insert_select? 2042 !@opts[:disable_insert_returning] 2043 end
PostgreSQL 9.3+ supports lateral subqueries
# File lib/sequel/adapters/shared/postgres.rb 2051 def supports_lateral_subqueries? 2052 server_version >= 90300 2053 end
PostgreSQL 15+ supports MERGE.
# File lib/sequel/adapters/shared/postgres.rb 2061 def supports_merge? 2062 server_version >= 150000 2063 end
PostgreSQL supports modifying joined datasets
# File lib/sequel/adapters/shared/postgres.rb 2056 def supports_modifying_joins? 2057 true 2058 end
PostgreSQL supports NOWAIT.
# File lib/sequel/adapters/shared/postgres.rb 2066 def supports_nowait? 2067 true 2068 end
PostgreSQL supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/postgres.rb 2076 def supports_regexp? 2077 true 2078 end
Returning is always supported.
# File lib/sequel/adapters/shared/postgres.rb 2071 def supports_returning?(type) 2072 true 2073 end
PostgreSQL 9.5+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/postgres.rb 2081 def supports_skip_locked? 2082 server_version >= 90500 2083 end
PostgreSQL supports timezones in literal timestamps
# File lib/sequel/adapters/shared/postgres.rb 2086 def supports_timestamp_timezones? 2087 true 2088 end
PostgreSQL 8.4+ supports WINDOW clause.
# File lib/sequel/adapters/shared/postgres.rb 2091 def supports_window_clause? 2092 server_version >= 80400 2093 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 2102 def supports_window_function_frame_option?(option) 2103 case option 2104 when :rows, :range 2105 true 2106 when :offset 2107 server_version >= 90000 2108 when :groups, :exclude 2109 server_version >= 110000 2110 else 2111 false 2112 end 2113 end
PostgreSQL 8.4+ supports window functions
# File lib/sequel/adapters/shared/postgres.rb 2096 def supports_window_functions? 2097 server_version >= 80400 2098 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 2131 def truncate(opts = OPTS) 2132 if opts.empty? 2133 super() 2134 else 2135 clone(:truncate_opts=>opts).truncate 2136 end 2137 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 2142 def with_ties 2143 clone(:limit_with_ties=>true) 2144 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 2152 def _import(columns, values, opts=OPTS) 2153 if @opts[:returning] 2154 # no transaction: our multi_insert_sql_strategy should guarantee 2155 # that there's only ever a single statement. 2156 sql = multi_insert_sql(columns, values)[0] 2157 returning_fetch_rows(sql).map{|v| v.length == 1 ? v.values.first : v} 2158 elsif opts[:return] == :primary_key 2159 returning(insert_pk)._import(columns, values, opts) 2160 else 2161 super 2162 end 2163 end
# File lib/sequel/adapters/shared/postgres.rb 2165 def to_prepared_statement(type, *a) 2166 if type == :insert && !@opts.has_key?(:returning) 2167 returning(insert_pk).send(:to_prepared_statement, :insert_pk, *a) 2168 else 2169 super 2170 end 2171 end
Private Instance Methods
Append the INSERT sql used in a MERGE
# File lib/sequel/adapters/shared/postgres.rb 2176 def _merge_insert_sql(sql, data) 2177 sql << " THEN INSERT " 2178 columns, values = _parse_insert_sql_args(data[:values]) 2179 _insert_columns_sql(sql, columns) 2180 if override = data[:override] 2181 sql << override 2182 end 2183 _insert_values_sql(sql, values) 2184 end
# File lib/sequel/adapters/shared/postgres.rb 2186 def _merge_matched_sql(sql, data) 2187 sql << " THEN DO NOTHING" 2188 end
Format TRUNCATE statement with PostgreSQL specific options.
# File lib/sequel/adapters/shared/postgres.rb 2192 def _truncate_sql(table) 2193 to = @opts[:truncate_opts] || OPTS 2194 "TRUNCATE TABLE#{' ONLY' if to[:only]} #{table}#{' RESTART IDENTITY' if to[:restart]}#{' CASCADE' if to[:cascade]}" 2195 end
Use from_self for aggregate dataset using VALUES.
# File lib/sequel/adapters/shared/postgres.rb 2198 def aggreate_dataset_use_from_self? 2199 super || @opts[:values] 2200 end
Allow truncation of multiple source tables.
# File lib/sequel/adapters/shared/postgres.rb 2203 def check_truncation_allowed! 2204 raise(InvalidOperation, "Grouped datasets cannot be truncated") if opts[:group] 2205 raise(InvalidOperation, "Joined datasets cannot be truncated") if opts[:join] 2206 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 2364 def compound_dataset_sql_append(sql, ds) 2365 sql << '(' 2366 super 2367 sql << ')' 2368 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/postgres.rb 2209 def delete_from_sql(sql) 2210 sql << ' FROM ' 2211 source_list_append(sql, @opts[:from][0..0]) 2212 end
Use USING to specify additional tables in a delete query
# File lib/sequel/adapters/shared/postgres.rb 2215 def delete_using_sql(sql) 2216 join_from_sql(:USING, sql) 2217 end
Concatenate the expressions with a space in between
# File lib/sequel/adapters/shared/postgres.rb 2488 def full_text_string_join(cols) 2489 cols = Array(cols).map{|x| SQL::Function.new(:COALESCE, x, '')} 2490 cols = cols.zip([' '] * cols.length).flatten 2491 cols.pop 2492 SQL::StringExpression.new(:'||', *cols) 2493 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/postgres.rb 2220 def insert_conflict_sql(sql) 2221 if opts = @opts[:insert_conflict] 2222 sql << " ON CONFLICT" 2223 2224 if target = opts[:constraint] 2225 sql << " ON CONSTRAINT " 2226 identifier_append(sql, target) 2227 elsif target = opts[:target] 2228 sql << ' ' 2229 identifier_append(sql, Array(target)) 2230 if conflict_where = opts[:conflict_where] 2231 sql << " WHERE " 2232 literal_append(sql, conflict_where) 2233 end 2234 end 2235 2236 if values = opts[:update] 2237 sql << " DO UPDATE SET " 2238 update_sql_values_hash(sql, values) 2239 if update_where = opts[:update_where] 2240 sql << " WHERE " 2241 literal_append(sql, update_where) 2242 end 2243 else 2244 sql << " DO NOTHING" 2245 end 2246 end 2247 end
Include aliases when inserting into a single table on PostgreSQL 9.5+.
# File lib/sequel/adapters/shared/postgres.rb 2250 def insert_into_sql(sql) 2251 sql << " INTO " 2252 if (f = @opts[:from]) && f.length == 1 2253 identifier_append(sql, server_version >= 90500 ? f.first : unaliased_identifier(f.first)) 2254 else 2255 source_list_append(sql, f) 2256 end 2257 end
Support OVERRIDING SYSTEM|USER VALUE in insert statements
# File lib/sequel/adapters/shared/postgres.rb 2271 def insert_override_sql(sql) 2272 case opts[:override] 2273 when :system 2274 sql << " OVERRIDING SYSTEM VALUE" 2275 when :user 2276 sql << " OVERRIDING USER VALUE" 2277 end 2278 end
Return the primary key to use for RETURNING in an INSERT statement
# File lib/sequel/adapters/shared/postgres.rb 2260 def insert_pk 2261 (f = opts[:from]) && !f.empty? && (t = f.first) 2262 case t 2263 when Symbol, String, SQL::Identifier, SQL::QualifiedIdentifier 2264 if pk = db.primary_key(t) 2265 Sequel::SQL::Identifier.new(pk) 2266 end 2267 end 2268 end
For multiple table support, PostgreSQL requires at least two from tables, with joins allowed.
# File lib/sequel/adapters/shared/postgres.rb 2282 def join_from_sql(type, sql) 2283 if(from = @opts[:from][1..-1]).empty? 2284 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 2285 else 2286 sql << ' ' << type.to_s << ' ' 2287 source_list_append(sql, from) 2288 select_join_sql(sql) 2289 end 2290 end
Support table aliases for USING columns
# File lib/sequel/adapters/shared/postgres.rb 2293 def join_using_clause_using_sql_append(sql, using_columns) 2294 if using_columns.is_a?(SQL::AliasedExpression) 2295 super(sql, using_columns.expression) 2296 sql << ' AS ' 2297 identifier_append(sql, using_columns.alias) 2298 else 2299 super 2300 end 2301 end
Use a generic blob quoting method, hopefully overridden in one of the subadapter methods
# File lib/sequel/adapters/shared/postgres.rb 2304 def literal_blob_append(sql, v) 2305 sql << "'" << v.gsub(/[\000-\037\047\134\177-\377]/n){|b| "\\#{("%o" % b[0..1].unpack("C")[0]).rjust(3, '0')}"} << "'" 2306 end
PostgreSQL uses FALSE for false values
# File lib/sequel/adapters/shared/postgres.rb 2309 def literal_false 2310 'false' 2311 end
PostgreSQL quotes NaN and Infinity.
# File lib/sequel/adapters/shared/postgres.rb 2314 def literal_float(value) 2315 if value.finite? 2316 super 2317 elsif value.nan? 2318 "'NaN'" 2319 elsif value.infinite? == 1 2320 "'Infinity'" 2321 else 2322 "'-Infinity'" 2323 end 2324 end
Handle Ruby integers outside PostgreSQL bigint range specially.
# File lib/sequel/adapters/shared/postgres.rb 2327 def literal_integer(v) 2328 if v > 9223372036854775807 || v < -9223372036854775808 2329 literal_integer_outside_bigint_range(v) 2330 else 2331 v.to_s 2332 end 2333 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 2338 def literal_integer_outside_bigint_range(v) 2339 raise IntegerOutsideBigintRange, "attempt to literalize Ruby integer outside PostgreSQL bigint range: #{v}" 2340 end
Assume that SQL
standard quoting is on, per Sequel's defaults
# File lib/sequel/adapters/shared/postgres.rb 2343 def literal_string_append(sql, v) 2344 sql << "'" << v.gsub("'", "''") << "'" 2345 end
PostgreSQL uses true for true values
# File lib/sequel/adapters/shared/postgres.rb 2348 def literal_true 2349 'true' 2350 end
PostgreSQL supports multiple rows in INSERT.
# File lib/sequel/adapters/shared/postgres.rb 2353 def multi_insert_sql_strategy 2354 :values 2355 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 2372 def requires_like_escape? 2373 false 2374 end
Support FETCH FIRST WITH TIES on PostgreSQL 13+.
# File lib/sequel/adapters/shared/postgres.rb 2377 def select_limit_sql(sql) 2378 l = @opts[:limit] 2379 o = @opts[:offset] 2380 2381 return unless l || o 2382 2383 if @opts[:limit_with_ties] 2384 if o 2385 sql << " OFFSET " 2386 literal_append(sql, o) 2387 end 2388 2389 if l 2390 sql << " FETCH FIRST " 2391 literal_append(sql, l) 2392 sql << " ROWS WITH TIES" 2393 end 2394 else 2395 if l 2396 sql << " LIMIT " 2397 literal_append(sql, l) 2398 end 2399 2400 if o 2401 sql << " OFFSET " 2402 literal_append(sql, o) 2403 end 2404 end 2405 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 2409 def select_lock_sql(sql) 2410 lock = @opts[:lock] 2411 if lock == :share 2412 sql << ' FOR SHARE' 2413 else 2414 super 2415 end 2416 2417 if lock 2418 if @opts[:skip_locked] 2419 sql << " SKIP LOCKED" 2420 elsif @opts[:nowait] 2421 sql << " NOWAIT" 2422 end 2423 end 2424 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/postgres.rb 2427 def select_values_sql(sql) 2428 sql << "VALUES " 2429 expression_list_append(sql, opts[:values]) 2430 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/postgres.rb 2433 def select_with_sql_base 2434 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 2435 end
Support PostgreSQL 14+ CTE SEARCH/CYCLE clauses
# File lib/sequel/adapters/shared/postgres.rb 2438 def select_with_sql_cte(sql, cte) 2439 super 2440 select_with_sql_cte_search_cycle(sql, cte) 2441 end
# File lib/sequel/adapters/shared/postgres.rb 2443 def select_with_sql_cte_search_cycle(sql, cte) 2444 if search_opts = cte[:search] 2445 sql << if search_opts[:type] == :breadth 2446 " SEARCH BREADTH FIRST BY " 2447 else 2448 " SEARCH DEPTH FIRST BY " 2449 end 2450 2451 identifier_list_append(sql, Array(search_opts[:by])) 2452 sql << " SET " 2453 identifier_append(sql, search_opts[:set] || :ordercol) 2454 end 2455 2456 if cycle_opts = cte[:cycle] 2457 sql << " CYCLE " 2458 identifier_list_append(sql, Array(cycle_opts[:columns])) 2459 sql << " SET " 2460 identifier_append(sql, cycle_opts[:cycle_column] || :is_cycle) 2461 if cycle_opts.has_key?(:cycle_value) 2462 sql << " TO " 2463 literal_append(sql, cycle_opts[:cycle_value]) 2464 sql << " DEFAULT " 2465 literal_append(sql, cycle_opts.fetch(:noncycle_value, false)) 2466 end 2467 sql << " USING " 2468 identifier_append(sql, cycle_opts[:path_column] || :path) 2469 end 2470 end
The version of the database server
# File lib/sequel/adapters/shared/postgres.rb 2473 def server_version 2474 db.server_version(@opts[:server]) 2475 end
PostgreSQL 9.4+ supports the FILTER clause for aggregate functions.
# File lib/sequel/adapters/shared/postgres.rb 2478 def supports_filtered_aggregates? 2479 server_version >= 90400 2480 end
PostgreSQL supports quoted function names.
# File lib/sequel/adapters/shared/postgres.rb 2483 def supports_quoted_function_names? 2484 true 2485 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/postgres.rb 2496 def update_from_sql(sql) 2497 join_from_sql(:FROM, sql) 2498 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/postgres.rb 2501 def update_table_sql(sql) 2502 sql << ' ' 2503 source_list_append(sql, @opts[:from][0..0]) 2504 end