module Sequel::Postgres::DatabaseMethods

Constants

DATABASE_ERROR_REGEXPS
FOREIGN_KEY_LIST_ON_DELETE_MAP
MAX_DATE
MAX_TIMESTAMP
MIN_DATE
MIN_TIMESTAMP
ON_COMMIT
SELECT_CUSTOM_SEQUENCE_SQL: SQL fragment for custom sequences (ones not created by serial primary key), Returning the schema and literal form of the sequence name, by parsing the column defaults table.
SELECT_PK_SQL: SQL fragment for determining primary key column for the given table. Only returns the first primary key if the table has a composite primary key.
SELECT_SERIAL_SEQUENCE_SQL: SQL fragment for getting sequence associated with table’s primary key, assuming it was a serial primary key column.
TYPTYPE_METHOD_MAP
VALID_CLIENT_MIN_MESSAGES

Attributes

conversion_procs[R]

A hash of conversion procs, keyed by type integer (oid) and having callable values for the conversion proc for that type.

Public Instance Methods

add_conversion_proc(oid, callable=nil, &block) click to toggle source

Set a conversion proc for the given oid. The callable can be passed either as a argument or a block.

    # File lib/sequel/adapters/shared/postgres.rb
885 def add_conversion_proc(oid, callable=nil, &block)
886   conversion_procs[oid] = callable || block
887 end

add_named_conversion_proc(name, &block) click to toggle source

Add a conversion proc for a named type, using the given block. This should be used for types without fixed OIDs, which includes all types that are not included in a default PostgreSQL installation.

    # File lib/sequel/adapters/shared/postgres.rb
892 def add_named_conversion_proc(name, &block)
893   unless oid = from(:pg_type).where(:typtype=>['b', 'e'], :typname=>name.to_s).get(:oid)
894     raise Error, "No matching type in pg_type for #{name.inspect}"
895   end
896   add_conversion_proc(oid, block)
897 end

alter_property_graph(name, &block) click to toggle source

Alter the property graph with the given name, supported on PostgreSQL 19+. The block uses a DSL, evaluated by PropertyGraph::Generator::Alter. Example:

DB.alter_property_graph(:my_graph) do
  # PropertyGraph::Generator::Alter
  add_vertex :companies2
  # ALTER PROPERTY GRAPH "my_graph" ADD VERTEX TABLES ("companies2")

  add_edge :works_at2 do
    # PropertyGraph::Generator::Edge
    source :people
    destination :companies2
  end
  # ALTER PROPERTY GRAPH "my_graph" ADD EDGE TABLES
  #   ("works_at2" SOURCE "people" DESTINATION "companies2")

  drop_vertex_tables [:p2], cascade: true
  # ALTER PROPERTY GRAPH "my_graph" DROP VERTEX TABLES ("p2") CASCADE

  drop_edge_tables :e2
  # ALTER PROPERTY GRAPH "my_graph" DROP EDGE TABLES ("e2")

  alter_vertex_table :companies do
    # PropertyGraph::Generator::AlterElement
    add_label :public_company, [:name, :symbol]
    # ALTER PROPERTY GRAPH "my_graph" ALTER VERTEX TABLE "companies"
    #   ADD LABEL "public_company" PROPERTIES ("name", "symbol")

    drop_label :private_company
    # ALTER PROPERTY GRAPH "my_graph" ALTER VERTEX TABLE "companies"
    #   DROP LABEL "private_company"

    add_properties :company, :revenue
    # ALTER PROPERTY GRAPH "my_graph" ALTER VERTEX TABLE "companies"
    #   ALTER LABEL "company" ADD PROPERTIES ("revenue")

    drop_properties :company, :internal_id, cascade: true
    # ALTER PROPERTY GRAPH "my_graph" ALTER VERTEX TABLE "companies"
    #   ALTER LABEL "company" DROP PROPERTIES ("internal_id") CASCADE
  end

  alter_edge_table :works_at do
    # PropertyGraph::Generator::AlterElement
    add_label :employment
  end
  # ALTER PROPERTY GRAPH "my_graph" ALTER EDGE TABLE "works_at"
  #   ADD LABEL "employment" PROPERTIES ALL COLUMNS

  owner_to :new_owner
  # ALTER PROPERTY GRAPH "my_graph" OWNER TO "new_owner"
end

    # File lib/sequel/adapters/shared/postgres.rb
950 def alter_property_graph(name, &block)
951   PropertyGraph::Generator::Alter.new(&block).each do |op|
952     execute_ddl(alter_property_graph_op_sql(name, op).freeze)
953   end
954   nil
955 end

check_constraints(table) click to toggle source

A hash of metadata for CHECK constraints on the table. Keys are CHECK constraint name symbols. Values are hashes with the following keys:

:definition: An SQL fragment for the definition of the constraint
:columns: An array of column symbols for the columns referenced in the constraint, can be an empty array if the database cannot deteremine the column symbols.

    # File lib/sequel/adapters/shared/postgres.rb
966 def check_constraints(table)
967   m = output_identifier_meth
968 
969   hash = {}
970   _check_constraints_ds.where_each(:conrelid=>regclass_oid(table)) do |row|
971     constraint = m.call(row[:constraint])
972     entry = hash[constraint] ||= {:definition=>row[:definition], :columns=>[], :validated=>row[:validated], :enforced=>row[:enforced]}
973     entry[:columns] << m.call(row[:column]) if row[:column]
974   end
975   
976   hash
977 end

commit_prepared_transaction(transaction_id, opts=OPTS) click to toggle source

    # File lib/sequel/adapters/shared/postgres.rb
957 def commit_prepared_transaction(transaction_id, opts=OPTS)
958   run("COMMIT PREPARED #{literal(transaction_id)}".freeze, opts)
959 end

convert_serial_to_identity(table, opts=OPTS) click to toggle source

Convert the first primary key column in the table from being a serial column to being an identity column. If the column is already an identity column, assume it was already converted and make no changes.

Only supported on PostgreSQL 10.2+, since on those versions Sequel will use identity columns instead of serial columns for auto incrementing primary keys. Only supported when running as a superuser, since regular users cannot modify system tables, and there is no way to keep an existing sequence when changing an existing column to be an identity column.

This method can raise an exception in at least the following cases where it may otherwise succeed (there may be additional cases not listed here):

The serial column was added after table creation using PostgreSQL <7.3
A regular index also exists on the column (such an index can probably be dropped as the primary key index should suffice)

Options:

:column: Specify the column to convert instead of using the first primary key column
:server: Run the SQL on the given server

     # File lib/sequel/adapters/shared/postgres.rb
 997 def convert_serial_to_identity(table, opts=OPTS)
 998   raise Error, "convert_serial_to_identity is only supported on PostgreSQL 10.2+" unless server_version >= 100002
 999 
1000   server = opts[:server]
1001   server_hash = server ? {:server=>server} : OPTS
1002   ds = dataset
1003   ds = ds.server(server) if server
1004 
1005   raise Error, "convert_serial_to_identity requires superuser permissions" unless ds.get{current_setting('is_superuser')} == 'on'
1006 
1007   table_oid = regclass_oid(table)
1008   im = input_identifier_meth
1009   unless column = (opts[:column] || ((sch = schema(table).find{|_, sc| sc[:primary_key] && sc[:auto_increment]}) && sch[0]))
1010     raise Error, "could not determine column to convert from serial to identity automatically"
1011   end
1012   column = im.call(column)
1013 
1014   column_num = ds.from(:pg_attribute).
1015     where(:attrelid=>table_oid, :attname=>column).
1016     get(:attnum)
1017 
1018   pg_class = Sequel.cast('pg_class', :regclass)
1019   res = ds.from(:pg_depend).
1020     where(:refclassid=>pg_class, :refobjid=>table_oid, :refobjsubid=>column_num, :classid=>pg_class, :objsubid=>0, :deptype=>%w'a i').
1021     select_map([:objid, Sequel.as({:deptype=>'i'}, :v)])
1022 
1023   case res.length
1024   when 0
1025     raise Error, "unable to find related sequence when converting serial to identity"
1026   when 1
1027     seq_oid, already_identity = res.first
1028   else
1029     raise Error, "more than one linked sequence found when converting serial to identity"
1030   end
1031 
1032   return if already_identity
1033 
1034   transaction(server_hash) do
1035     run("ALTER TABLE #{quote_schema_table(table)} ALTER COLUMN #{quote_identifier(column)} DROP DEFAULT".freeze, server_hash)
1036 
1037     ds.from(:pg_depend).
1038       where(:classid=>pg_class, :objid=>seq_oid, :objsubid=>0, :deptype=>'a').
1039       update(:deptype=>'i')
1040 
1041     ds.from(:pg_attribute).
1042       where(:attrelid=>table_oid, :attname=>column).
1043       update(:attidentity=>'d')
1044   end
1045 
1046   remove_cached_schema(table)
1047   nil
1048 end

create_function(name, definition, opts=OPTS) click to toggle source

Creates the function in the database. Arguments:

name

name of the function to create

definition

string definition of the function, or object file for a dynamically loaded C function.

opts

options hash:

:args

function arguments, can be either a symbol or string specifying a type or an array of 1-3 elements:

1: argument data type
2: argument name
3: argument mode (e.g. in, out, inout)

:behavior

Should be IMMUTABLE, STABLE, or VOLATILE. PostgreSQL assumes VOLATILE by default.

:parallel

The thread safety attribute of the function. Should be SAFE, UNSAFE, RESTRICTED. PostgreSQL assumes UNSAFE by default.

:cost

The estimated cost of the function, used by the query planner.

:language

The language the function uses. SQL is the default.

:link_symbol

For a dynamically loaded see function, the function’s link symbol if different from the definition argument.

:returns

The data type returned by the function. If you are using OUT or INOUT argument modes, this is ignored. Otherwise, if this is not specified, void is used by default to specify the function is not supposed to return a value.

:rows

The estimated number of rows the function will return. Only use if the function returns SETOF something.

:security_definer

Makes the privileges of the function the same as the privileges of the user who defined the function instead of the privileges of the user who runs the function. There are security implications when doing this, see the PostgreSQL documentation.

:set

Configuration variables to set while the function is being run, can be a hash or an array of two pairs. search_path is often used here if :security_definer is used.

:strict

Makes the function return NULL when any argument is NULL.

     # File lib/sequel/adapters/shared/postgres.rb
1071 def create_function(name, definition, opts=OPTS)
1072   self << create_function_sql(name, definition, opts).freeze
1073 end

create_language(name, opts=OPTS) click to toggle source

Create the procedural language in the database. Arguments:

name

Name of the procedural language (e.g. plpgsql)

opts

options hash:

:handler: The name of a previously registered function used as a call handler for this language.
:replace: Replace the installed language if it already exists (on PostgreSQL 9.0+).
:trusted: Marks the language being created as trusted, allowing unprivileged users to create functions using this language.
:validator: The name of previously registered function used as a validator of functions defined in this language.

     # File lib/sequel/adapters/shared/postgres.rb
1082 def create_language(name, opts=OPTS)
1083   self << create_language_sql(name, opts).freeze
1084 end

create_property_graph(name, opts=OPTS, &block) click to toggle source

Create a property graph in the database, supported on PostgreSQL 19+.

Arguments:

name

Name of the property graph

opts

options hash:

:temp: Create the property graph as a temporary property graph.

The block uses a DSL, with classes under PropertyGraph::Generator:

DB.create_property_graph(:my_graph) do
  # PropertyGraph::Generator::Create
  vertex :people

  vertex Sequel.as(:people, :p), properties: []

  vertex Sequel.as(:companies, :c) do
    # PropertyGraph::Generator::Vertex
    key :id
    label :company
    label :c, [:name, (Sequel[:revenue] / 1000).as(:revenue_thousands)]
  end

  edge :works_at do
    # PropertyGraph::Generator::Edge
    source :people
    destination :c
  end

  edge Sequel.as(:employment, :e) do
    source :people do
      # PropertyGraph::Generator::Target
      key :person_id
      references :id
    end
    destination :c do
      # PropertyGraph::Generator::Target
      key :company_id
      references :id
    end
    label :employment
  end
end
# CREATE PROPERTY GRAPH "my_graph"
# VERTEX TABLES (
#   "people",
#   "people" AS "p" NO PROPERTIES,
#   "companies" AS "c" KEY ("id")
#     LABEL "company" PROPERTIES ALL COLUMNS
#     LABEL "c" PROPERTIES ("name", ("revenue" / 1000) AS "revenue_thousands"))
# EDGE TABLES (
#   "works_at"
#     SOURCE "people"
#     DESTINATION "c",
#   "employment" AS "e"
#     SOURCE KEY ("person_id") REFERENCES "people" ("id")
#     DESTINATION KEY ("company_id") REFERENCES "c" ("id")
#   LABEL "employment" PROPERTIES ALL COLUMNS)

     # File lib/sequel/adapters/shared/postgres.rb
1143 def create_property_graph(name, opts=OPTS, &block)
1144   execute_ddl(create_property_graph_sql(name, PropertyGraph::Generator::Create.new(&block), opts))
1145 end

create_schema(name, opts=OPTS) click to toggle source

Create a schema in the database. Arguments:

name

Name of the schema (e.g. admin)

opts

options hash:

:if_not_exists: Don’t raise an error if the schema already exists (PostgreSQL 9.3+)
:owner: The owner to set for the schema (defaults to current user if not specified)

     # File lib/sequel/adapters/shared/postgres.rb
1152 def create_schema(name, opts=OPTS)
1153   self << create_schema_sql(name, opts).freeze
1154 end

create_table(name, options=OPTS, &block) click to toggle source

Support partitions of tables using the :partition_of option.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1157 def create_table(name, options=OPTS, &block)
1158   if options[:partition_of]
1159     create_partition_of_table_from_generator(name, CreatePartitionOfTableGenerator.new(&block), options)
1160     return
1161   end
1162 
1163   super
1164 end

create_table?(name, options=OPTS, &block) click to toggle source

Support partitions of tables using the :partition_of option.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1167 def create_table?(name, options=OPTS, &block)
1168   if options[:partition_of]
1169     create_table(name, options.merge!(:if_not_exists=>true), &block)
1170     return
1171   end
1172 
1173   super
1174 end

create_trigger(table, name, function, opts=OPTS) click to toggle source

Create a trigger in the database. Arguments:

table

the table on which this trigger operates

name

the name of this trigger

function

the function to call for this trigger, which should return type trigger.

opts

options hash:

:after: Calls the trigger after execution instead of before.
:args: An argument or array of arguments to pass to the function.
:each_row: Calls the trigger for each row instead of for each statement.
:events: Can be :insert, :update, :delete, or an array of any of those. Calls the trigger whenever that type of statement is used. By default, the trigger is called for insert, update, or delete.
:replace: Replace the trigger with the same name if it already exists (PostgreSQL 14+).
:when: A filter to use for the trigger

     # File lib/sequel/adapters/shared/postgres.rb
1188 def create_trigger(table, name, function, opts=OPTS)
1189   self << create_trigger_sql(table, name, function, opts).freeze
1190 end

database_type() click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1192 def database_type
1193   :postgres
1194 end

defer_constraints(opts=OPTS) click to toggle source

For constraints that are deferrable, defer constraints until transaction commit. Options:

:constraints: An identifier of the constraint, or an array of identifiers for constraints, to apply this change to specific constraints.
:server: The server/shard on which to run the query.

Examples:

DB.defer_constraints
# SET CONSTRAINTS ALL DEFERRED

DB.defer_constraints(constraints: [:c1, Sequel[:sc][:c2]])
# SET CONSTRAINTS "c1", "sc"."s2" DEFERRED

     # File lib/sequel/adapters/shared/postgres.rb
1211 def defer_constraints(opts=OPTS)
1212   _set_constraints(' DEFERRED', opts)
1213 end

do(code, opts=OPTS) click to toggle source

Use PostgreSQL’s DO syntax to execute an anonymous code block. The code should be the literal code string to use in the underlying procedural language. Options:

:language: The procedural language the code is written in. The PostgreSQL default is plpgsql. Can be specified as a string or a symbol.

     # File lib/sequel/adapters/shared/postgres.rb
1220 def do(code, opts=OPTS)
1221   language = opts[:language]
1222   run "DO #{"LANGUAGE #{literal(language.to_s)} " if language}#{literal(code)}".freeze
1223 end

drop_function(name, opts=OPTS) click to toggle source

Drops the function from the database. Arguments:

name

name of the function to drop

opts

options hash:

:args: The arguments for the function. See create_function_sql.
:cascade: Drop other objects depending on this function.
:if_exists: Don’t raise an error if the function doesn’t exist.

     # File lib/sequel/adapters/shared/postgres.rb
1231 def drop_function(name, opts=OPTS)
1232   self << drop_function_sql(name, opts).freeze
1233 end

drop_language(name, opts=OPTS) click to toggle source

Drops a procedural language from the database. Arguments:

name

name of the procedural language to drop

opts

options hash:

:cascade: Drop other objects depending on this function.
:if_exists: Don’t raise an error if the function doesn’t exist.

     # File lib/sequel/adapters/shared/postgres.rb
1240 def drop_language(name, opts=OPTS)
1241   self << drop_language_sql(name, opts).freeze
1242 end

drop_property_graph(name, opts=OPTS) click to toggle source

Drops a property graph from the database. Arguments:

name

name of the property graph to drop

opts

options hash:

:cascade: Drop other objects depending on this property_graph.
:if_exists: Don’t raise an error if the property graph doesn’t exist.

     # File lib/sequel/adapters/shared/postgres.rb
1249 def drop_property_graph(name, opts=OPTS)
1250   self << drop_property_graph_sql(name, opts).freeze
1251 end

drop_schema(name, opts=OPTS) click to toggle source

Drops a schema from the database. Arguments:

name

name of the schema to drop

opts

options hash:

:cascade: Drop all objects in this schema.
:if_exists: Don’t raise an error if the schema doesn’t exist.

     # File lib/sequel/adapters/shared/postgres.rb
1258 def drop_schema(name, opts=OPTS)
1259   self << drop_schema_sql(name, opts).freeze
1260   remove_all_cached_schemas
1261 end

drop_trigger(table, name, opts=OPTS) click to toggle source

Drops a trigger from the database. Arguments:

table

table from which to drop the trigger

name

name of the trigger to drop

opts

options hash:

:cascade: Drop other objects depending on this function.
:if_exists: Don’t raise an error if the function doesn’t exist.

     # File lib/sequel/adapters/shared/postgres.rb
1269 def drop_trigger(table, name, opts=OPTS)
1270   self << drop_trigger_sql(table, name, opts).freeze
1271 end

foreign_key_list(table, opts=OPTS) click to toggle source

Return full foreign key information using the pg system tables, including :name, :on_delete, :on_update, and :deferrable entries in the hashes.

Supports additional options:

:reverse: Instead of returning foreign keys in the current table, return foreign keys in other tables that reference the current table.
:schema: Set to true to have the :table value in the hashes be a qualified identifier. Set to false to use a separate :schema value with the related schema. Defaults to whether the given table argument is a qualified identifier.

     # File lib/sequel/adapters/shared/postgres.rb
1283 def foreign_key_list(table, opts=OPTS)
1284   m = output_identifier_meth
1285   schema, _ = opts.fetch(:schema, schema_and_table(table))
1286 
1287   h = {}
1288   fklod_map = FOREIGN_KEY_LIST_ON_DELETE_MAP 
1289   reverse = opts[:reverse]
1290 
1291   (reverse ? _reverse_foreign_key_list_ds : _foreign_key_list_ds).where_each(Sequel[:cl][:oid]=>regclass_oid(table)) do |row|
1292     if reverse
1293       key = [row[:schema], row[:table], row[:name]]
1294     else
1295       key = row[:name]
1296     end
1297 
1298     if r = h[key]
1299       r[:columns] << m.call(row[:column])
1300       r[:key] << m.call(row[:refcolumn])
1301     else
1302       entry = h[key] = {
1303         :name=>m.call(row[:name]),
1304         :columns=>[m.call(row[:column])],
1305         :key=>[m.call(row[:refcolumn])],
1306         :on_update=>fklod_map[row[:on_update]],
1307         :on_delete=>fklod_map[row[:on_delete]],
1308         :deferrable=>row[:deferrable],
1309         :validated=>row[:validated],
1310         :enforced=>row[:enforced],
1311         :table=>schema ? SQL::QualifiedIdentifier.new(m.call(row[:schema]), m.call(row[:table])) : m.call(row[:table]),
1312       }
1313 
1314       unless schema
1315         # If not combining schema information into the :table entry
1316         # include it as a separate entry.
1317         entry[:schema] = m.call(row[:schema])
1318       end
1319     end
1320   end
1321 
1322   h.values
1323 end

freeze() click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1325 def freeze
1326   server_version
1327   supports_prepared_transactions?
1328   _schema_ds
1329   _select_serial_sequence_ds
1330   _select_custom_sequence_ds
1331   _select_pk_ds
1332   _indexes_ds
1333   _check_constraints_ds
1334   _foreign_key_list_ds
1335   _reverse_foreign_key_list_ds
1336   @conversion_procs.freeze
1337   super
1338 end

graph_table(property_graph_name, initial_vertex_label, initial_vertex_opts=OPTS) click to toggle source

Return a PropertyGraph::Table instance for a property graph search (a GRAPH_TABLE clause for a SELECT query). Supported on PostgreSQL 19+.

Arguments:

property_graph_name: The property graph to query
initial_vertex_label: The label restriction for the initial vertex for the graph pattern (can be nil for no label, or an array or set for restricting to one of multiple labels).
initial_vertex_opts: The options for the initial vertex, see PropertyGraph::Table#link for available options.

The returned instance should be further modified by calling methods on it, using a similar approach to how datasets work, where the methods return a modified copy of the receiver. The available methods:

link: Add a bidirectional link to a new element (vertex or edge)
to: Add a directional link from the last element to the new element
from: Add a direciton link from the new element to last element
columns: Replace the columns the graph table returns
add_columns: Append to the columns the graph table returns.

See PropertyGraph::Table for the details of these methods and the arguments and options they support. Note that for a graph table to be usable in a query, it must return at least one column, and the last element in the graph pattern must be a vertex.

gt = DB.graph_table(:pgn, :iv)
# Not yet usable, does not return any columns

# Set columns for graph table
gt = gt.columns(:c, Sequel[1].as(:d))
# GRAPH_TABLE ("pgn" MATCH (IS "iv") COLUMNS ("c", 1 AS "d"))

# Adds directional link to edge, since last (initial) element was a vertex
gt = gt.link(:e1)
# GRAPH_TABLE ("pgn" MATCH (IS "iv")-[IS "e1"] COLUMNS ("c", 1 AS "d"))

# Adds directional link from edge to vertex, since last element was an edge
gt = gt.to(:v2)
# GRAPH_TABLE ("pgn" MATCH (IS "iv")-[IS "e1"]->(IS "v2") COLUMNS ("c", 1 AS "d"))

# Adds bidirection link from vertex to vertex (overriding the default)
gt = gt.link(:v3, vertex: true)
# GRAPH_TABLE ("pgn" MATCH (IS "iv")-[IS "e1"]->(IS "v2")-(IS "v3") COLUMNS ("c", 1 AS "d"))

# Adds directional link from new edge to last vertex, since last element was an vertex.
# Sets graph pattern variable name and uses it in a WHERE clause for the added element.
gt = gt.from(:e2, var: :a2, where: {Sequel[:a2][:c] => 1})
# GRAPH_TABLE ("pgn" MATCH (IS "iv")-[IS "e1"]->(IS "v2")-(IS "v3")
#   <-["a2" IS "e2" WHERE ("a2"."c" = 1)] COLUMNS ("c", 1 AS "d"))

# Can use nil as a label for no label restriction, both with and without a variable name
gt = gt.to(nil).to(nil, var: :a3)
# GRAPH_TABLE ("pgn" MATCH (IS "iv")-[IS "e1"]->(IS "v2")-(IS "v3")
#   <-["a2" IS "e2" WHERE ("a2"."c" = 1)]->[]->("a3") COLUMNS ("c", 1 AS "d"))

# Can restrict to a one of a set of labels
gt = gt.from([:x, :y], var: :a6)
# GRAPH_TABLE ("pgn" MATCH (IS "iv")-[IS "e1"]->(IS "v2")-(IS "v3")
#   <-["a2" IS "e2" WHERE ("a2"."c" = 1)]->[]->("a3")->["a6" IS "x"|"y"] COLUMNS ("c", 1 AS "d"))

# Add column(s) to the graph table
gt = gt.add_columns(:y)
# GRAPH_TABLE ("pgn" MATCH (IS "iv")-[IS "e1"]->(IS "v2")-(IS "v3")
#   <-["a2" IS "e2" WHERE ("a2"."c" = 1)]->[]->("a3")->["a6" IS "x"|"y"]
#   COLUMNS ("c", 1 AS "d", "y"))

DB.from(gt)
# SELECT * FROM GRAPH_TABLE (...)

DB.from(:x).cross_join(gt)
# SELECT * FROM "x" CROSS JOIN GRAPH_TABLE (...)

     # File lib/sequel/adapters/shared/postgres.rb
1412 def graph_table(property_graph_name, initial_vertex_label, initial_vertex_opts=OPTS)
1413   PropertyGraph::Table.create(property_graph_name, initial_vertex_label, initial_vertex_opts)
1414 end

immediate_constraints(opts=OPTS) click to toggle source

Immediately apply deferrable constraints.

:constraints: An identifier of the constraint, or an array of identifiers for constraints, to apply this change to specific constraints.
:server: The server/shard on which to run the query.

Examples:

DB.immediate_constraints
# SET CONSTRAINTS ALL IMMEDIATE

DB.immediate_constraints(constraints: [:c1, Sequel[:sc][:c2]])
# SET CONSTRAINTS "c1", "sc"."s2" IMMEDIATE

     # File lib/sequel/adapters/shared/postgres.rb
1430 def immediate_constraints(opts=OPTS)
1431   _set_constraints(' IMMEDIATE', opts)
1432 end

indexes(table, opts=OPTS) click to toggle source

Use the pg_* system tables to determine indexes on a table. Options:

:include_partial: Set to true to include partial indexes
:invalid: Set to true or :only to only return invalid indexes. Set to :include to also return both valid and invalid indexes. When not set or other value given, does not return invalid indexes.

     # File lib/sequel/adapters/shared/postgres.rb
1440 def indexes(table, opts=OPTS)
1441   m = output_identifier_meth
1442   cond = {Sequel[:tab][:oid]=>regclass_oid(table, opts)}
1443   cond[:indpred] = nil unless opts[:include_partial]
1444 
1445   case opts[:invalid]
1446   when true, :only
1447     cond[:indisvalid] = false
1448   when :include
1449     # nothing
1450   else
1451     cond[:indisvalid] = true
1452   end
1453 
1454   indexes = {}
1455   _indexes_ds.where_each(cond) do |r|
1456     i = indexes[m.call(r[:name])] ||= {:columns=>[], :unique=>r[:unique], :deferrable=>r[:deferrable]}
1457     i[:columns] << m.call(r[:column])
1458   end
1459   indexes
1460 end

locks() click to toggle source

Dataset containing all current database locks

     # File lib/sequel/adapters/shared/postgres.rb
1463 def locks
1464   dataset.from(:pg_class).join(:pg_locks, :relation=>:relfilenode).select{[pg_class[:relname], Sequel::SQL::ColumnAll.new(:pg_locks)]}
1465 end

notify(channel, opts=OPTS) click to toggle source

Notifies the given channel. See the PostgreSQL NOTIFY documentation. Options:

:payload: The payload string to use for the NOTIFY statement. Only supported in PostgreSQL 9.0+.
:server: The server to which to send the NOTIFY statement, if the sharding support is being used.

     # File lib/sequel/adapters/shared/postgres.rb
1473 def notify(channel, opts=OPTS)
1474   sql = String.new
1475   sql << "NOTIFY "
1476   dataset.send(:identifier_append, sql, channel)
1477   if payload = opts[:payload]
1478     sql << ", "
1479     dataset.literal_append(sql, payload.to_s)
1480   end
1481   execute_ddl(sql, opts)
1482 end

primary_key(table, opts=OPTS) click to toggle source

Return primary key for the given table.

     # File lib/sequel/adapters/shared/postgres.rb
1485 def primary_key(table, opts=OPTS)
1486   quoted_table = quote_schema_table(table)
1487   Sequel.synchronize{return @primary_keys[quoted_table] if @primary_keys.has_key?(quoted_table)}
1488   value = _select_pk_ds.where_single_value(Sequel[:pg_class][:oid] => regclass_oid(table, opts))
1489   Sequel.synchronize{@primary_keys[quoted_table] = value}
1490 end

primary_key_sequence(table, opts=OPTS) click to toggle source

Return the sequence providing the default for the primary key for the given table.

     # File lib/sequel/adapters/shared/postgres.rb
1493 def primary_key_sequence(table, opts=OPTS)
1494   quoted_table = quote_schema_table(table)
1495   Sequel.synchronize{return @primary_key_sequences[quoted_table] if @primary_key_sequences.has_key?(quoted_table)}
1496   cond = {Sequel[:t][:oid] => regclass_oid(table, opts)}
1497   value = if pks = _select_serial_sequence_ds.first(cond)
1498     literal(SQL::QualifiedIdentifier.new(pks[:schema], pks[:sequence]))
1499   elsif pks = _select_custom_sequence_ds.first(cond)
1500     literal(SQL::QualifiedIdentifier.new(pks[:schema], LiteralString.new(pks[:sequence])))
1501   end
1502 
1503   Sequel.synchronize{@primary_key_sequences[quoted_table] = value} if value
1504 end

property_graphs(opts=OPTS, &block) click to toggle source

Array of symbols specifying property graphs in the current database. The dataset used is yielded to the block if one is provided, otherwise, an array of symbols of property graph names is returned. Supported on PostgreSQL 19+, will be an empty array on lower versions.

Options:

:qualify: Return the property graph names as Sequel::SQL::QualifiedIdentifier instances, using the schema the property graph is located in as the qualifier.
:schema: The schema to search
:server: The server to use

     # File lib/sequel/adapters/shared/postgres.rb
1516 def property_graphs(opts=OPTS, &block)
1517   pg_class_relname('g', opts, &block)
1518 end

refresh_view(name, opts=OPTS) click to toggle source

Refresh the materialized view with the given name.

DB.refresh_view(:items_view)
# REFRESH MATERIALIZED VIEW items_view
DB.refresh_view(:items_view, concurrently: true)
# REFRESH MATERIALIZED VIEW CONCURRENTLY items_view

     # File lib/sequel/adapters/shared/postgres.rb
1542 def refresh_view(name, opts=OPTS)
1543   run "REFRESH MATERIALIZED VIEW#{' CONCURRENTLY' if opts[:concurrently]} #{quote_schema_table(name)}".freeze
1544 end

rename_property_graph(old_name, new_name) click to toggle source

Rename a property graph.

DB.rename_property_graph(:x, :y)
# ALTER PROPERTY GRAPH x RENAME TO y

     # File lib/sequel/adapters/shared/postgres.rb
1524 def rename_property_graph(old_name, new_name)
1525   execute_ddl("ALTER PROPERTY GRAPH #{literal(old_name)} RENAME TO #{literal(new_name)}".freeze)
1526 end

rename_schema(name, new_name) click to toggle source

Rename a schema in the database. Arguments:

name: Current name of the schema
opts: New name for the schema

     # File lib/sequel/adapters/shared/postgres.rb
1531 def rename_schema(name, new_name)
1532   self << rename_schema_sql(name, new_name).freeze
1533   remove_all_cached_schemas
1534 end

reset_primary_key_sequence(table) click to toggle source

Reset the primary key sequence for the given table, basing it on the maximum current value of the table’s primary key.

     # File lib/sequel/adapters/shared/postgres.rb
1548 def reset_primary_key_sequence(table)
1549   return unless seq = primary_key_sequence(table)
1550   pk = SQL::Identifier.new(primary_key(table))
1551   db = self
1552   s, t = schema_and_table(table)
1553   table = Sequel.qualify(s, t) if s
1554 
1555   if server_version >= 100000
1556     seq_ds = metadata_dataset.from(:pg_sequence).where(:seqrelid=>regclass_oid(LiteralString.new(seq.freeze)))
1557     increment_by = :seqincrement
1558     min_value = :seqmin
1559   # :nocov:
1560   else
1561     seq_ds = metadata_dataset.from(LiteralString.new(seq))
1562     increment_by = :increment_by
1563     min_value = :min_value
1564   # :nocov:
1565   end
1566 
1567   get{setval(seq, db[table].select(coalesce(max(pk)+seq_ds.select(increment_by), seq_ds.select(min_value))), false)}
1568 end

rollback_prepared_transaction(transaction_id, opts=OPTS) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1570 def rollback_prepared_transaction(transaction_id, opts=OPTS)
1571   run("ROLLBACK PREPARED #{literal(transaction_id)}".freeze, opts)
1572 end

serial_primary_key_options() click to toggle source

PostgreSQL uses SERIAL psuedo-type instead of AUTOINCREMENT for managing incrementing primary keys.

     # File lib/sequel/adapters/shared/postgres.rb
1576 def serial_primary_key_options
1577   # :nocov:
1578   auto_increment_key = server_version >= 100002 ? :identity : :serial
1579   # :nocov:
1580   {:primary_key => true, auto_increment_key => true, :type=>Integer}
1581 end

server_version(server=nil) click to toggle source

The version of the PostgreSQL server, used for determining capability.

     # File lib/sequel/adapters/shared/postgres.rb
1584 def server_version(server=nil)
1585   return @server_version if @server_version
1586   ds = dataset
1587   ds = ds.server(server) if server
1588   @server_version = swallow_database_error{ds.with_sql("SELECT CAST(current_setting('server_version_num') AS integer) AS v").single_value} || 0
1589 end

set_property_graph_schema(old_name, new_name, opts=OPTS) click to toggle source

Change the schema for a property graph. Options:

:if_exists: Use the IF EXISTS clause to not raise an error if the property graph does not exist.

DB.set_property_graph_schema(:x, :y)
# ALTER PROPERTY GRAPH x SET SCHEMA y

     # File lib/sequel/adapters/shared/postgres.rb
1597 def set_property_graph_schema(old_name, new_name, opts=OPTS)
1598   execute_ddl("ALTER PROPERTY GRAPH#{" IF EXISTS" if opts[:if_exists]} #{literal(old_name)} SET SCHEMA #{literal(new_name)}".freeze)
1599 end

supports_create_table_if_not_exists?() click to toggle source

PostgreSQL supports CREATE TABLE IF NOT EXISTS on 9.1+

     # File lib/sequel/adapters/shared/postgres.rb
1602 def supports_create_table_if_not_exists?
1603   server_version >= 90100
1604 end

supports_deferrable_constraints?() click to toggle source

PostgreSQL 9.0+ supports some types of deferrable constraints beyond foreign key constraints.

     # File lib/sequel/adapters/shared/postgres.rb
1607 def supports_deferrable_constraints?
1608   server_version >= 90000
1609 end

supports_deferrable_foreign_key_constraints?() click to toggle source

PostgreSQL supports deferrable foreign key constraints.

     # File lib/sequel/adapters/shared/postgres.rb
1612 def supports_deferrable_foreign_key_constraints?
1613   true
1614 end

supports_drop_table_if_exists?() click to toggle source

PostgreSQL supports DROP TABLE IF EXISTS

     # File lib/sequel/adapters/shared/postgres.rb
1617 def supports_drop_table_if_exists?
1618   true
1619 end

supports_partial_indexes?() click to toggle source

PostgreSQL supports partial indexes.

     # File lib/sequel/adapters/shared/postgres.rb
1622 def supports_partial_indexes?
1623   true
1624 end

supports_prepared_transactions?() click to toggle source

PostgreSQL supports prepared transactions (two-phase commit) if max_prepared_transactions is greater than 0.

     # File lib/sequel/adapters/shared/postgres.rb
1633 def supports_prepared_transactions?
1634   return @supports_prepared_transactions if defined?(@supports_prepared_transactions)
1635   @supports_prepared_transactions = self['SHOW max_prepared_transactions'].get.to_i > 0
1636 end

supports_savepoints?() click to toggle source

PostgreSQL supports savepoints

     # File lib/sequel/adapters/shared/postgres.rb
1639 def supports_savepoints?
1640   true
1641 end

supports_transaction_isolation_levels?() click to toggle source

PostgreSQL supports transaction isolation levels

     # File lib/sequel/adapters/shared/postgres.rb
1644 def supports_transaction_isolation_levels?
1645   true
1646 end

supports_transactional_ddl?() click to toggle source

PostgreSQL supports transaction DDL statements.

     # File lib/sequel/adapters/shared/postgres.rb
1649 def supports_transactional_ddl?
1650   true
1651 end

supports_trigger_conditions?() click to toggle source

PostgreSQL 9.0+ supports trigger conditions.

     # File lib/sequel/adapters/shared/postgres.rb
1627 def supports_trigger_conditions?
1628   server_version >= 90000
1629 end

tables(opts=OPTS, &block) click to toggle source

Array of symbols specifying table names in the current database. The dataset used is yielded to the block if one is provided, otherwise, an array of symbols of table names is returned.

Options:

:qualify: Return the tables as Sequel::SQL::QualifiedIdentifier instances, using the schema the table is located in as the qualifier.
:schema: The schema to search
:server: The server to use

     # File lib/sequel/adapters/shared/postgres.rb
1662 def tables(opts=OPTS, &block)
1663   pg_class_relname(['r', 'p'], opts, &block)
1664 end

type_supported?(type) click to toggle source

Check whether the given type name string/symbol (e.g. :hstore) is supported by the database.

     # File lib/sequel/adapters/shared/postgres.rb
1668 def type_supported?(type)
1669   Sequel.synchronize{return @supported_types[type] if @supported_types.has_key?(type)}
1670   supported = from(:pg_type).where(:typtype=>'b', :typname=>type.to_s).count > 0
1671   Sequel.synchronize{return @supported_types[type] = supported}
1672 end

values(v) click to toggle source

Creates a dataset that uses the VALUES clause:

DB.values([[1, 2], [3, 4]])
# VALUES ((1, 2), (3, 4))

DB.values([[1, 2], [3, 4]]).order(:column2).limit(1, 1)
# VALUES ((1, 2), (3, 4)) ORDER BY column2 LIMIT 1 OFFSET 1

     # File lib/sequel/adapters/shared/postgres.rb
1681 def values(v)
1682   raise Error, "Cannot provide an empty array for values" if v.empty?
1683   @default_dataset.clone(:values=>v)
1684 end

views(opts=OPTS) click to toggle source

Array of symbols specifying view names in the current database.

Options:

:materialized: Return materialized views
:qualify: Return the views as Sequel::SQL::QualifiedIdentifier instances, using the schema the view is located in as the qualifier.
:schema: The schema to search
:server: The server to use

     # File lib/sequel/adapters/shared/postgres.rb
1694 def views(opts=OPTS)
1695   relkind = opts[:materialized] ? 'm' : 'v'
1696   pg_class_relname(relkind, opts)
1697 end

with_advisory_lock(lock_id, opts=OPTS) { || ... } click to toggle source

Attempt to acquire an exclusive advisory lock with the given lock_id (which should be a 64-bit integer). If successful, yield to the block, then release the advisory lock when the block exits. If unsuccessful, raise a Sequel::AdvisoryLockError.

DB.with_advisory_lock(1347){DB.get(1)}
# SELECT pg_try_advisory_lock(1357) LIMIT 1
# SELECT 1 AS v LIMIT 1
# SELECT pg_advisory_unlock(1357) LIMIT 1

Options:

:wait: Do not raise an error, instead, wait until the advisory lock can be acquired.

     # File lib/sequel/adapters/shared/postgres.rb
1710 def with_advisory_lock(lock_id, opts=OPTS)
1711   ds = dataset
1712   if server = opts[:server]
1713     ds = ds.server(server)
1714   end
1715 
1716   synchronize(server) do |c|
1717     begin
1718       if opts[:wait]
1719         ds.get{pg_advisory_lock(lock_id)}
1720         locked = true
1721       else
1722         unless locked = ds.get{pg_try_advisory_lock(lock_id)}
1723           raise AdvisoryLockError, "unable to acquire advisory lock #{lock_id.inspect}"
1724         end
1725       end
1726 
1727       yield
1728     ensure
1729       ds.get{pg_advisory_unlock(lock_id)} if locked
1730     end
1731   end
1732 end

Private Instance Methods

__foreign_key_list_ds(reverse) click to toggle source

Build dataset used for foreign key list methods.

     # File lib/sequel/adapters/shared/postgres.rb
1760 def __foreign_key_list_ds(reverse)
1761   if reverse
1762     ctable = Sequel[:att2]
1763     cclass = Sequel[:cl2]
1764     rtable = Sequel[:att]
1765     rclass = Sequel[:cl]
1766   else
1767     ctable = Sequel[:att]
1768     cclass = Sequel[:cl]
1769     rtable = Sequel[:att2]
1770     rclass = Sequel[:cl2]
1771   end
1772 
1773   if server_version >= 90500
1774     cpos = Sequel.expr{array_position(co[:conkey], ctable[:attnum])}
1775     rpos = Sequel.expr{array_position(co[:confkey], rtable[:attnum])}
1776   # :nocov:
1777   else
1778     range = 0...32
1779     cpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:conkey], [x]), x]}, 32, ctable[:attnum])}
1780     rpos = Sequel.expr{SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(co[:confkey], [x]), x]}, 32, rtable[:attnum])}
1781   # :nocov:
1782   end
1783 
1784   ds = metadata_dataset.
1785     from{pg_constraint.as(:co)}.
1786     join(Sequel[:pg_class].as(cclass), :oid=>:conrelid).
1787     join(Sequel[:pg_attribute].as(ctable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
1788     join(Sequel[:pg_class].as(rclass), :oid=>Sequel[:co][:confrelid]).
1789     join(Sequel[:pg_attribute].as(rtable), :attrelid=>:oid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:confkey])).
1790     join(Sequel[:pg_namespace].as(:nsp), :oid=>Sequel[:cl2][:relnamespace]).
1791     order{[co[:conname], cpos]}.
1792     where{{
1793       cl[:relkind]=>%w'r p',
1794       co[:contype]=>'f',
1795       cpos=>rpos
1796     }}.
1797     select{[
1798       co[:conname].as(:name),
1799       ctable[:attname].as(:column),
1800       co[:confupdtype].as(:on_update),
1801       co[:confdeltype].as(:on_delete),
1802       cl2[:relname].as(:table),
1803       rtable[:attname].as(:refcolumn),
1804       SQL::BooleanExpression.new(:AND, co[:condeferrable], co[:condeferred]).as(:deferrable),
1805       nsp[:nspname].as(:schema)
1806     ]}
1807 
1808   if reverse
1809     ds = ds.order_append(Sequel[:nsp][:nspname], Sequel[:cl2][:relname])
1810   end
1811 
1812   _add_validated_enforced_constraint_columns(ds)
1813 end

_add_validated_enforced_constraint_columns(ds) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
1815 def _add_validated_enforced_constraint_columns(ds)
1816   validated_cond = if server_version >= 90100
1817     Sequel[:convalidated]
1818   # :nocov:
1819   else
1820     Sequel.cast(true, TrueClass)
1821   # :nocov:
1822   end
1823   ds = ds.select_append(validated_cond.as(:validated))
1824 
1825   enforced_cond = if server_version >= 180000
1826     Sequel[:conenforced]
1827   # :nocov:
1828   else
1829     Sequel.cast(true, TrueClass)
1830   # :nocov:
1831   end
1832   ds = ds.select_append(enforced_cond.as(:enforced))
1833 
1834   ds
1835 end

_check_constraints_ds() click to toggle source

Dataset used to retrieve CHECK constraint information

     # File lib/sequel/adapters/shared/postgres.rb
1737 def _check_constraints_ds
1738   @_check_constraints_ds ||= begin
1739     ds = metadata_dataset.
1740       from{pg_constraint.as(:co)}.
1741       left_join(Sequel[:pg_attribute].as(:att), :attrelid=>:conrelid, :attnum=>SQL::Function.new(:ANY, Sequel[:co][:conkey])).
1742       where(:contype=>'c').
1743       select{[co[:conname].as(:constraint), att[:attname].as(:column), pg_get_constraintdef(co[:oid]).as(:definition)]}
1744 
1745     _add_validated_enforced_constraint_columns(ds)
1746   end
1747 end

_foreign_key_list_ds() click to toggle source

Dataset used to retrieve foreign keys referenced by a table

     # File lib/sequel/adapters/shared/postgres.rb
1750 def _foreign_key_list_ds
1751   @_foreign_key_list_ds ||= __foreign_key_list_ds(false)
1752 end

_indexes_ds() click to toggle source

Dataset used to retrieve index information

     # File lib/sequel/adapters/shared/postgres.rb
1838 def _indexes_ds
1839   @_indexes_ds ||= begin
1840     if server_version >= 90500
1841       order = [Sequel[:indc][:relname], Sequel.function(:array_position, Sequel[:ind][:indkey], Sequel[:att][:attnum])]
1842     # :nocov:
1843     else
1844       range = 0...32
1845       order = [Sequel[:indc][:relname], SQL::CaseExpression.new(range.map{|x| [SQL::Subscript.new(Sequel[:ind][:indkey], [x]), x]}, 32, Sequel[:att][:attnum])]
1846     # :nocov:
1847     end
1848 
1849     attnums = SQL::Function.new(:ANY, Sequel[:ind][:indkey])
1850 
1851     ds = metadata_dataset.
1852       from{pg_class.as(:tab)}.
1853       join(Sequel[:pg_index].as(:ind), :indrelid=>:oid).
1854       join(Sequel[:pg_class].as(:indc), :oid=>:indexrelid).
1855       join(Sequel[:pg_attribute].as(:att), :attrelid=>Sequel[:tab][:oid], :attnum=>attnums).
1856       left_join(Sequel[:pg_constraint].as(:con), :conname=>Sequel[:indc][:relname]).
1857       where{{
1858         indc[:relkind]=>%w'i I',
1859         ind[:indisprimary]=>false,
1860         :indexprs=>nil}}.
1861       order(*order).
1862       select{[indc[:relname].as(:name), ind[:indisunique].as(:unique), att[:attname].as(:column), con[:condeferrable].as(:deferrable)]}
1863 
1864     # :nocov:
1865     ds = ds.where(:indisready=>true) if server_version >= 80300
1866     ds = ds.where(:indislive=>true) if server_version >= 90300
1867     # :nocov:
1868 
1869     ds
1870   end
1871 end

_reverse_foreign_key_list_ds() click to toggle source

Dataset used to retrieve foreign keys referencing a table

     # File lib/sequel/adapters/shared/postgres.rb
1755 def _reverse_foreign_key_list_ds
1756   @_reverse_foreign_key_list_ds ||= __foreign_key_list_ds(true)
1757 end

_schema_ds() click to toggle source

Dataset used to get schema for tables

     # File lib/sequel/adapters/shared/postgres.rb
1934 def _schema_ds
1935   @_schema_ds ||= begin
1936     ds = metadata_dataset.select{[
1937         pg_attribute[:attname].as(:name),
1938         SQL::Cast.new(pg_attribute[:atttypid], :integer).as(:oid),
1939         SQL::Cast.new(basetype[:oid], :integer).as(:base_oid),
1940         SQL::Function.new(:col_description, pg_class[:oid], pg_attribute[:attnum]).as(:comment),
1941         SQL::Function.new(:format_type, basetype[:oid], pg_type[:typtypmod]).as(:db_base_type),
1942         SQL::Function.new(:format_type, pg_type[:oid], pg_attribute[:atttypmod]).as(:db_type),
1943         SQL::Function.new(:pg_get_expr, pg_attrdef[:adbin], pg_class[:oid]).as(:default),
1944         SQL::BooleanExpression.new(:NOT, pg_attribute[:attnotnull]).as(:allow_null),
1945         SQL::Function.new(:COALESCE, SQL::BooleanExpression.from_value_pairs(pg_attribute[:attnum] => SQL::Function.new(:ANY, pg_index[:indkey])), false).as(:primary_key),
1946         Sequel[:pg_type][:typtype],
1947         (~Sequel[Sequel[:elementtype][:oid]=>nil]).as(:is_array),
1948       ]}.
1949       from(:pg_class).
1950       join(:pg_attribute, :attrelid=>:oid).
1951       join(:pg_type, :oid=>:atttypid).
1952       left_outer_join(Sequel[:pg_type].as(:basetype), :oid=>:typbasetype).
1953       left_outer_join(Sequel[:pg_type].as(:elementtype), :typarray=>Sequel[:pg_type][:oid]).
1954       left_outer_join(:pg_attrdef, :adrelid=>Sequel[:pg_class][:oid], :adnum=>Sequel[:pg_attribute][:attnum]).
1955       left_outer_join(:pg_index, :indrelid=>Sequel[:pg_class][:oid], :indisprimary=>true).
1956       where{{pg_attribute[:attisdropped]=>false}}.
1957       where{pg_attribute[:attnum] > 0}.
1958       order{pg_attribute[:attnum]}
1959 
1960     # :nocov:
1961     if server_version > 100000
1962     # :nocov:
1963       ds = ds.select_append{pg_attribute[:attidentity]}
1964 
1965       # :nocov:
1966       if server_version > 120000
1967       # :nocov:
1968         ds = ds.select_append{Sequel.~(pg_attribute[:attgenerated]=>'').as(:generated)}
1969       end
1970     end
1971 
1972     ds
1973   end
1974 end

_select_custom_sequence_ds() click to toggle source

Dataset used to determine custom serial sequences for tables

     # File lib/sequel/adapters/shared/postgres.rb
1874 def _select_custom_sequence_ds
1875   @_select_custom_sequence_ds ||= metadata_dataset.
1876     from{pg_class.as(:t)}.
1877     join(:pg_namespace, {:oid => :relnamespace}, :table_alias=>:name).
1878     join(:pg_attribute, {:attrelid => Sequel[:t][:oid]}, :table_alias=>:attr).
1879     join(:pg_attrdef, {:adrelid => :attrelid, :adnum => :attnum}, :table_alias=>:def).
1880     join(:pg_constraint, {:conrelid => :adrelid, Sequel[:cons][:conkey].sql_subscript(1) => :adnum}, :table_alias=>:cons).
1881     where{{cons[:contype] => 'p', pg_get_expr(self.def[:adbin], attr[:attrelid]) => /nextval/i}}.
1882     select{
1883       expr = split_part(pg_get_expr(self.def[:adbin], attr[:attrelid]), "'", 2)
1884       [
1885         name[:nspname].as(:schema),
1886         Sequel.case({{expr => /./} => substr(expr, strpos(expr, '.')+1)}, expr).as(:sequence)
1887       ]
1888     }
1889 end

_select_pk_ds() click to toggle source

Dataset used to determine primary keys for tables

     # File lib/sequel/adapters/shared/postgres.rb
1920 def _select_pk_ds
1921   @_select_pk_ds ||= metadata_dataset.
1922     from(:pg_class, :pg_attribute, :pg_index, :pg_namespace).
1923     where{[
1924       [pg_class[:oid], pg_attribute[:attrelid]],
1925       [pg_class[:relnamespace], pg_namespace[:oid]],
1926       [pg_class[:oid], pg_index[:indrelid]],
1927       [pg_index[:indkey].sql_subscript(0), pg_attribute[:attnum]],
1928       [pg_index[:indisprimary], 't']
1929     ]}.
1930     select{pg_attribute[:attname].as(:pk)}
1931 end

_select_serial_sequence_ds() click to toggle source

Dataset used to determine normal serial sequences for tables

     # File lib/sequel/adapters/shared/postgres.rb
1892 def _select_serial_sequence_ds
1893   @_serial_sequence_ds ||= metadata_dataset.
1894     from{[
1895       pg_class.as(:seq),
1896       pg_attribute.as(:attr),
1897       pg_depend.as(:dep),
1898       pg_namespace.as(:name),
1899       pg_constraint.as(:cons),
1900       pg_class.as(:t)
1901     ]}.
1902     where{[
1903       [seq[:oid], dep[:objid]],
1904       [seq[:relnamespace], name[:oid]],
1905       [seq[:relkind], 'S'],
1906       [attr[:attrelid], dep[:refobjid]],
1907       [attr[:attnum], dep[:refobjsubid]],
1908       [attr[:attrelid], cons[:conrelid]],
1909       [attr[:attnum], cons[:conkey].sql_subscript(1)],
1910       [attr[:attrelid], t[:oid]],
1911       [cons[:contype], 'p']
1912     ]}.
1913     select{[
1914       name[:nspname].as(:schema),
1915       seq[:relname].as(:sequence)
1916     ]}
1917 end

_set_constraints(type, opts) click to toggle source

Internals of defer_constraints/immediate_constraints

     # File lib/sequel/adapters/shared/postgres.rb
1977 def _set_constraints(type, opts)
1978   execute_ddl(_set_constraints_sql(type, opts), opts)
1979 end

_set_constraints_sql(type, opts) click to toggle source

SQL to use for SET CONSTRAINTS

     # File lib/sequel/adapters/shared/postgres.rb
1982 def _set_constraints_sql(type, opts)
1983   sql = String.new
1984   sql << "SET CONSTRAINTS "
1985   if constraints = opts[:constraints]
1986     dataset.send(:source_list_append, sql, Array(constraints))
1987   else
1988     sql << "ALL"
1989   end
1990   sql << type
1991 end

_table_exists?(ds) click to toggle source

Consider lock or statement timeout errors as evidence that the table exists but is locked.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
1995 def _table_exists?(ds)
1996   super
1997 rescue DatabaseError => e    
1998   raise e unless /canceling statement due to (?:statement|lock) timeout/ =~ e.message 
1999 end

alter_property_graph_element_table_sql(op) click to toggle source

SQL fragment for the ALTER PROPERTY GRAPH ALTER {VERTEX|EDGE} TABLE prefix

     # File lib/sequel/adapters/shared/postgres.rb
2053 def alter_property_graph_element_table_sql(op)
2054   "ALTER #{op[:kind] == :vertex ? 'VERTEX' : 'EDGE'} TABLE #{quote_identifier(op[:name])}"
2055 end

alter_property_graph_op_sql(name, op) click to toggle source

SQL statement for a single ALTER PROPERTY GRAPH operation.

     # File lib/sequel/adapters/shared/postgres.rb
2002 def alter_property_graph_op_sql(name, op)
2003   sql = String.new << "ALTER PROPERTY GRAPH " << quote_schema_table(name) << " "
2004 
2005   case op_type = op[:op]
2006   when :add_vertex_tables
2007     sql << "ADD VERTEX TABLES (" <<
2008       op[:tables].map do |vertex|
2009         create_property_graph_table_sql(vertex) <<
2010           create_property_graph_labels_sql(vertex.labels)
2011       end.join(', ') << ")"
2012   when :add_edge_tables
2013     sql << "ADD EDGE TABLES (" <<
2014       op[:tables].map do |edge|
2015         create_property_graph_table_sql(edge) <<
2016           " SOURCE " << create_property_graph_edge_side_sql(edge.source) <<
2017           " DESTINATION " << create_property_graph_edge_side_sql(edge.destination) <<
2018           create_property_graph_labels_sql(edge.labels)
2019       end.join(', ') << ")"
2020   when :drop_vertex_tables, :drop_edge_tables
2021     sql << (op_type == :drop_vertex_tables ? "DROP VERTEX TABLES " : "DROP EDGE TABLES ") <<
2022       literal(op[:aliases])
2023   when :add_label
2024     sql << alter_property_graph_element_table_sql(op)
2025     op[:labels].each do |label_name, properties|
2026       sql << " ADD LABEL " << quote_identifier(label_name) <<
2027         create_property_graph_properties_clause_sql(properties)
2028     end
2029   when :drop_label
2030     sql << alter_property_graph_element_table_sql(op) <<
2031       " DROP LABEL " << quote_identifier(op[:label])
2032   when :add_properties
2033     sql << alter_property_graph_element_table_sql(op) <<
2034       " ALTER LABEL " << quote_identifier(op[:label]) << " ADD PROPERTIES " <<
2035       literal(op[:properties])
2036   when :drop_properties
2037     sql << alter_property_graph_element_table_sql(op) <<
2038       " ALTER LABEL " << quote_identifier(op[:label]) << " DROP PROPERTIES " <<
2039       literal(op[:properties])
2040   else # when :set_owner
2041     sql << "OWNER TO " << literal(op[:owner])
2042   end
2043 
2044   case op_type
2045   when :drop_vertex_tables, :drop_edge_tables, :drop_label, :drop_properties
2046     sql << " CASCADE" if op[:cascade]
2047   end
2048 
2049   sql
2050 end

alter_table_add_column_sql(table, op) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2057 def alter_table_add_column_sql(table, op)
2058   "ADD COLUMN#{' IF NOT EXISTS' if op[:if_not_exists]} #{column_definition_sql(op)}"
2059 end

alter_table_alter_constraint_sql(table, op) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2061 def alter_table_alter_constraint_sql(table, op)
2062   sql = String.new
2063   sql << "ALTER CONSTRAINT #{quote_identifier(op[:name])}"
2064   
2065   constraint_deferrable_sql_append(sql, op[:deferrable])
2066 
2067   case op[:enforced]
2068   when nil
2069   when false
2070     sql << " NOT ENFORCED"
2071   else
2072     sql << " ENFORCED"
2073   end
2074 
2075   case op[:inherit]
2076   when nil
2077   when false
2078     sql << " NO INHERIT"
2079   else
2080     sql << " INHERIT"
2081   end
2082 
2083   sql
2084 end

alter_table_drop_column_sql(table, op) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2104 def alter_table_drop_column_sql(table, op)
2105   "DROP COLUMN #{'IF EXISTS ' if op[:if_exists]}#{quote_identifier(op[:name])}#{' CASCADE' if op[:cascade]}"
2106 end

alter_table_generator_class() click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2086 def alter_table_generator_class
2087   Postgres::AlterTableGenerator
2088 end

alter_table_rename_constraint_sql(table, op) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2090 def alter_table_rename_constraint_sql(table, op)
2091   "RENAME CONSTRAINT #{quote_identifier(op[:name])} TO #{quote_identifier(op[:new_name])}"
2092 end

alter_table_set_column_type_sql(table, op) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2094 def alter_table_set_column_type_sql(table, op)
2095   s = super
2096   if using = op[:using]
2097     using = Sequel::LiteralString.new(using) if using.is_a?(String)
2098     s += ' USING '
2099     s << literal(using)
2100   end
2101   s
2102 end

alter_table_validate_constraint_sql(table, op) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2108 def alter_table_validate_constraint_sql(table, op)
2109   "VALIDATE CONSTRAINT #{quote_identifier(op[:name])}"
2110 end

begin_new_transaction(conn, opts) click to toggle source

If the :synchronous option is given and non-nil, set synchronous_commit appropriately. Valid values for the :synchronous option are true, :on, false, :off, :local, and :remote_write.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2115 def begin_new_transaction(conn, opts)
2116   super
2117   if opts.has_key?(:synchronous)
2118     case sync = opts[:synchronous]
2119     when true
2120       sync = :on
2121     when false
2122       sync = :off
2123     when nil
2124       return
2125     end
2126 
2127     log_connection_execute(conn, "SET LOCAL synchronous_commit = #{sync}")
2128   end
2129 end

begin_savepoint(conn, opts) click to toggle source

Set the READ ONLY transaction setting per savepoint, as PostgreSQL supports that.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2132 def begin_savepoint(conn, opts)
2133   super
2134 
2135   unless (read_only = opts[:read_only]).nil?
2136     log_connection_execute(conn, "SET TRANSACTION READ #{read_only ? 'ONLY' : 'WRITE'}")
2137   end
2138 end

column_definition_add_references_sql(sql, column) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2286 def column_definition_add_references_sql(sql, column)
2287   super
2288   if column[:not_enforced]
2289     sql << " NOT ENFORCED"
2290   end
2291 end

column_definition_append_include_sql(sql, constraint) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2140 def column_definition_append_include_sql(sql, constraint)
2141   if include_cols = constraint[:include]
2142     sql << " INCLUDE " << literal(Array(include_cols))
2143   end
2144 end

column_definition_append_primary_key_sql(sql, constraint) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2146 def column_definition_append_primary_key_sql(sql, constraint)
2147   super
2148   column_definition_append_include_sql(sql, constraint)
2149 end

column_definition_append_unique_sql(sql, constraint) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2151 def column_definition_append_unique_sql(sql, constraint)
2152   super
2153   column_definition_append_include_sql(sql, constraint)
2154 end

column_definition_collate_sql(sql, column) click to toggle source

Literalize non-String collate options. This is because unquoted collatations are folded to lowercase, and PostgreSQL used mixed case or capitalized collations.

     # File lib/sequel/adapters/shared/postgres.rb
2158 def column_definition_collate_sql(sql, column)
2159   if collate = column[:collate]
2160     collate = literal(collate) unless collate.is_a?(String)
2161     sql << " COLLATE #{collate}"
2162   end
2163 end

column_definition_default_sql(sql, column) click to toggle source

Support identity columns, but only use the identity SQL syntax if no default value is given.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2167 def column_definition_default_sql(sql, column)
2168   super
2169   if !column[:serial] && !['smallserial', 'serial', 'bigserial'].include?(column[:type].to_s) && !column[:default]
2170     if (identity = column[:identity])
2171       sql << " GENERATED "
2172       sql << (identity == :always ? "ALWAYS" : "BY DEFAULT")
2173       sql << " AS IDENTITY"
2174     elsif (generated = column[:generated_always_as])
2175       sql << " GENERATED ALWAYS AS (#{literal(generated)}) #{column[:virtual] ? 'VIRTUAL' : 'STORED'}"
2176     end
2177   end
2178 end

column_definition_null_sql(sql, column) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2293 def column_definition_null_sql(sql, column)
2294   constraint = column[:not_null]
2295   constraint = nil unless constraint.is_a?(Hash)
2296   if constraint && (name = constraint[:name])
2297     sql << " CONSTRAINT #{quote_identifier(name)}"
2298   end
2299   super
2300   if constraint && constraint[:no_inherit]
2301     sql << " NO INHERIT"
2302   end
2303 end

column_references_add_period(cols) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2320 def column_references_add_period(cols)
2321   cols= cols.dup
2322   cols[-1] = Sequel.lit("PERIOD #{quote_identifier(cols[-1])}".freeze)
2323   cols
2324 end

column_references_append_key_sql(sql, column) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2314 def column_references_append_key_sql(sql, column)
2315   cols = Array(column[:key])
2316   cols = column_references_add_period(cols) if column[:period]
2317   sql << "(#{cols.map{|x| quote_identifier(x)}.join(', ')})"
2318 end

column_references_table_constraint_sql(constraint) click to toggle source

Handle :period option

     # File lib/sequel/adapters/shared/postgres.rb
2306 def column_references_table_constraint_sql(constraint)
2307   sql = String.new
2308   sql << "FOREIGN KEY "
2309   cols = constraint[:columns]
2310   cols = column_references_add_period(cols) if constraint[:period]
2311   sql << literal(cols) << column_references_sql(constraint)
2312 end

column_schema_normalize_default(default, type) click to toggle source

Handle PostgreSQL specific default format.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2181 def column_schema_normalize_default(default, type)
2182   if m = /\A(?:B?('.*')::[^']+|\((-?\d+(?:\.\d+)?)\))\z/.match(default)
2183     default = m[1] || m[2]
2184   end
2185   super(default, type)
2186 end

combinable_alter_table_op?(op) click to toggle source

PostgreSQL can’t combine rename_column operations, and it can combine validate_constraint and alter_constraint operations.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2200 def combinable_alter_table_op?(op)
2201   (super || op[:op] == :validate_constraint || op[:op] == :alter_constraint) && op[:op] != :rename_column
2202 end

commit_transaction(conn, opts=OPTS) click to toggle source

If the :prepare option is given and we aren’t in a savepoint, prepare the transaction for a two-phase commit.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2190 def commit_transaction(conn, opts=OPTS)
2191   if (s = opts[:prepare]) && savepoint_level(conn) <= 1
2192     log_connection_execute(conn, "PREPARE TRANSACTION #{literal(s)}")
2193   else
2194     super
2195   end
2196 end

connection_configuration_sqls(opts=@opts) click to toggle source

The SQL queries to execute when starting a new connection.

     # File lib/sequel/adapters/shared/postgres.rb
2206 def connection_configuration_sqls(opts=@opts)
2207   sqls = []
2208 
2209   sqls << "SET standard_conforming_strings = ON" if typecast_value_boolean(opts.fetch(:force_standard_strings, true))
2210 
2211   cmm = opts.fetch(:client_min_messages, :warning)
2212   if cmm && !cmm.to_s.empty?
2213     cmm = cmm.to_s.upcase.strip
2214     unless VALID_CLIENT_MIN_MESSAGES.include?(cmm)
2215       raise Error, "Unsupported client_min_messages setting: #{cmm}"
2216     end
2217     sqls << "SET client_min_messages = '#{cmm.to_s.upcase}'"
2218   end
2219 
2220   if search_path = opts[:search_path]
2221     case search_path
2222     when String
2223       search_path = search_path.split(",").map(&:strip)
2224     when Array
2225       # nil
2226     else
2227       raise Error, "unrecognized value for :search_path option: #{search_path.inspect}"
2228     end
2229     sqls << "SET search_path = #{search_path.map{|s| "\"#{s.gsub('"', '""')}\""}.join(',')}"
2230   end
2231 
2232   sqls
2233 end

constraint_definition_sql(constraint) click to toggle source

Handle PostgreSQL-specific constraint features.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2236 def constraint_definition_sql(constraint)
2237   case type = constraint[:type]
2238   when :exclude
2239     elements = constraint[:elements].map{|c, op| "#{literal(c)} WITH #{op}"}.join(', ')
2240     sql = String.new
2241     sql << "CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]
2242     sql << "EXCLUDE USING #{constraint[:using]||'gist'} (#{elements})"
2243     column_definition_append_include_sql(sql, constraint)
2244     sql << " WHERE #{filter_expr(constraint[:where])}" if constraint[:where]
2245     constraint_deferrable_sql_append(sql, constraint[:deferrable])
2246     sql
2247   when :primary_key, :unique
2248     sql = String.new
2249     sql << "CONSTRAINT #{quote_identifier(constraint[:name])} " if constraint[:name]
2250 
2251     if type == :primary_key
2252       sql << primary_key_constraint_sql_fragment(constraint)
2253     else
2254       sql << unique_constraint_sql_fragment(constraint)
2255     end
2256 
2257     if using_index = constraint[:using_index]
2258       sql << " USING INDEX " << quote_identifier(using_index)
2259     else
2260       cols = literal(constraint[:columns])
2261       cols.insert(-2, " WITHOUT OVERLAPS") if constraint[:without_overlaps]
2262       sql << " " << cols
2263 
2264       if include_cols = constraint[:include]
2265         sql << " INCLUDE " << literal(Array(include_cols))
2266       end
2267     end
2268 
2269     constraint_deferrable_sql_append(sql, constraint[:deferrable])
2270     sql
2271   else # when :foreign_key, :check
2272     sql = super
2273     if constraint[:no_inherit]
2274       sql << " NO INHERIT"
2275     end
2276     if constraint[:not_enforced]
2277       sql << " NOT ENFORCED"
2278     end
2279     if constraint[:not_valid]
2280       sql << " NOT VALID"
2281     end
2282     sql
2283   end
2284 end

copy_into_sql(table, opts) click to toggle source

SQL for doing fast table insert from stdin.

     # File lib/sequel/adapters/shared/postgres.rb
2355 def copy_into_sql(table, opts)
2356   sql = String.new
2357   sql << "COPY #{literal(table)}"
2358   if cols = opts[:columns]
2359     sql << literal(Array(cols))
2360   end
2361   sql << " FROM STDIN"
2362   if opts[:options] || opts[:format]
2363     sql << " ("
2364     sql << "FORMAT #{opts[:format]}" if opts[:format]
2365     sql << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
2366     sql << ')'
2367   end
2368   sql
2369 end

copy_table_sql(table, opts) click to toggle source

SQL for doing fast table output to stdout.

     # File lib/sequel/adapters/shared/postgres.rb
2372 def copy_table_sql(table, opts)
2373   if table.is_a?(String)
2374     table
2375   else
2376     if opts[:options] || opts[:format]
2377       options = String.new
2378       options << " ("
2379       options << "FORMAT #{opts[:format]}" if opts[:format]
2380       options << "#{', ' if opts[:format]}#{opts[:options]}" if opts[:options]
2381       options << ')'
2382     end
2383     table = if table.is_a?(::Sequel::Dataset)
2384       "(#{table.sql})"
2385     else
2386       literal(table)
2387     end
2388     "COPY #{table} TO STDOUT#{options}"
2389   end
2390 end

create_function_sql(name, definition, opts=OPTS) click to toggle source

SQL statement to create database function.

     # File lib/sequel/adapters/shared/postgres.rb
2393       def create_function_sql(name, definition, opts=OPTS)
2394         args = opts[:args]
2395         in_out = %w'OUT INOUT'
2396         if (!opts[:args].is_a?(Array) || !opts[:args].any?{|a| Array(a).length == 3 && in_out.include?(a[2].to_s)})
2397           returns = opts[:returns] || 'void'
2398         end
2399         language = opts[:language] || 'SQL'
2400         <<-END
2401         CREATE#{' OR REPLACE' if opts[:replace]} FUNCTION #{name}#{sql_function_args(args)}
2402         #{"RETURNS #{returns}" if returns}
2403         LANGUAGE #{language}
2404         #{opts[:behavior].to_s.upcase if opts[:behavior]}
2405         #{'STRICT' if opts[:strict]}
2406         #{'SECURITY DEFINER' if opts[:security_definer]}
2407         #{"PARALLEL #{opts[:parallel].to_s.upcase}" if opts[:parallel]}
2408         #{"COST #{opts[:cost]}" if opts[:cost]}
2409         #{"ROWS #{opts[:rows]}" if opts[:rows]}
2410         #{opts[:set].map{|k,v| " SET #{k} = #{v}"}.join("\n") if opts[:set]}
2411         AS #{literal(definition.to_s)}#{", #{literal(opts[:link_symbol].to_s)}" if opts[:link_symbol]}
2412         END
2413       end

create_language_sql(name, opts=OPTS) click to toggle source

SQL for creating a procedural language.

     # File lib/sequel/adapters/shared/postgres.rb
2416 def create_language_sql(name, opts=OPTS)
2417   "CREATE#{' OR REPLACE' if opts[:replace] && server_version >= 90000}#{' TRUSTED' if opts[:trusted]} LANGUAGE #{name}#{" HANDLER #{opts[:handler]}" if opts[:handler]}#{" VALIDATOR #{opts[:validator]}" if opts[:validator]}"
2418 end

create_partition_of_table_from_generator(name, generator, options) click to toggle source

Create a partition of another table, used when the create_table with the :partition_of option is given.

     # File lib/sequel/adapters/shared/postgres.rb
2422 def create_partition_of_table_from_generator(name, generator, options)
2423   execute_ddl(create_partition_of_table_sql(name, generator, options))
2424 end

create_partition_of_table_sql(name, generator, options) click to toggle source

SQL for creating a partition of another table.

     # File lib/sequel/adapters/shared/postgres.rb
2427 def create_partition_of_table_sql(name, generator, options)
2428   sql = create_table_prefix_sql(name, options).dup
2429 
2430   sql << " PARTITION OF #{quote_schema_table(options[:partition_of])}"
2431 
2432   case generator.partition_type
2433   when :range
2434     from, to = generator.range
2435     sql << " FOR VALUES FROM #{literal(from)} TO #{literal(to)}"
2436   when :list
2437     sql << " FOR VALUES IN #{literal(generator.list)}"
2438   when :hash
2439     mod, remainder = generator.hash_values
2440     sql << " FOR VALUES WITH (MODULUS #{literal(mod)}, REMAINDER #{literal(remainder)})"
2441   else # when :default
2442     sql << " DEFAULT"
2443   end
2444 
2445   sql << create_table_suffix_sql(name, options)
2446 
2447   sql
2448 end

create_property_graph_edge_side_sql(side) click to toggle source

SQL fragment for the SOURCE or DESTINATION clause of an edge in a property graph.

     # File lib/sequel/adapters/shared/postgres.rb
2482 def create_property_graph_edge_side_sql(side)
2483   sql = String.new
2484   if side.key
2485     sql << "KEY " << literal(side.key) << " REFERENCES "
2486   end
2487   sql << quote_identifier(side.name)
2488   if side.references
2489     sql << " " << literal(side.references)
2490   end
2491   sql
2492 end

create_property_graph_labels_sql(labels) click to toggle source

SQL fragment for the LABEL/PROPERTIES clauses used for vertices and edges in a property graph.

     # File lib/sequel/adapters/shared/postgres.rb
2509 def create_property_graph_labels_sql(labels)
2510   labels.map do |name, properties|
2511     sql = String.new
2512     sql << " LABEL " << quote_identifier(name) if name
2513     sql << create_property_graph_properties_clause_sql(properties)
2514     sql
2515   end.join
2516 end

create_property_graph_properties_clause_sql(properties) click to toggle source

SQL fragment for the NO PROPERTIES, PROPERTIES ALL COLUMNS, or PROPERTIES (…) clause for a property graph element or label.

     # File lib/sequel/adapters/shared/postgres.rb
2520 def create_property_graph_properties_clause_sql(properties)
2521   case properties
2522   when nil, :all
2523     " PROPERTIES ALL COLUMNS"
2524   when false, :none, [].freeze
2525     " NO PROPERTIES"
2526   else
2527     " PROPERTIES #{literal(properties)}"
2528   end
2529 end

create_property_graph_sql(name, data, opts=OPTS) click to toggle source

SQL statement for creating a property graph.

     # File lib/sequel/adapters/shared/postgres.rb
2451 def create_property_graph_sql(name, data, opts=OPTS)
2452   sql = String.new
2453   sql << "CREATE "
2454   sql << "TEMPORARY " if opts[:temp]
2455   sql << "PROPERTY GRAPH "
2456   sql << quote_schema_table(name)
2457 
2458   unless data.vertices.empty?
2459     sql << " VERTEX TABLES ("
2460     sql << data.vertices.map do |vertex|
2461       create_property_graph_table_sql(vertex) <<
2462         create_property_graph_labels_sql(vertex.labels)
2463     end.join(', ')
2464     sql << ")"
2465   end
2466 
2467   unless data.edges.empty?
2468     sql << " EDGE TABLES ("
2469     sql << data.edges.map do |edge|
2470       create_property_graph_table_sql(edge) <<
2471         " SOURCE " << create_property_graph_edge_side_sql(edge.source) <<
2472         " DESTINATION " << create_property_graph_edge_side_sql(edge.destination) <<
2473         create_property_graph_labels_sql(edge.labels)
2474     end.join(', ')
2475     sql << ")"
2476   end
2477 
2478   sql
2479 end

create_property_graph_table_sql(element) click to toggle source

SQL fragment for the table name and KEY clause used for vertices and edges in a property graph.

     # File lib/sequel/adapters/shared/postgres.rb
2496 def create_property_graph_table_sql(element)
2497   sql = String.new
2498   sql << literal(element.name)
2499 
2500   if key = element.key
2501     sql << " KEY " << literal(key)
2502   end
2503 
2504   sql
2505 end

create_schema_sql(name, opts=OPTS) click to toggle source

SQL for creating a schema.

     # File lib/sequel/adapters/shared/postgres.rb
2532 def create_schema_sql(name, opts=OPTS)
2533   "CREATE SCHEMA #{'IF NOT EXISTS ' if opts[:if_not_exists]}#{quote_identifier(name)}#{" AUTHORIZATION #{literal(opts[:owner])}" if opts[:owner]}"
2534 end

create_table_as_sql(name, sql, options) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2590 def create_table_as_sql(name, sql, options)
2591   result = create_table_prefix_sql name, options
2592   if on_commit = options[:on_commit]
2593     result += " ON COMMIT #{ON_COMMIT[on_commit]}"
2594   end
2595   result += " AS #{sql}"
2596 end

create_table_generator_class() click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2598 def create_table_generator_class
2599   Postgres::CreateTableGenerator
2600 end

create_table_prefix_sql(name, options) click to toggle source

DDL statement for creating a table with the given name, columns, and options

     # File lib/sequel/adapters/shared/postgres.rb
2537 def create_table_prefix_sql(name, options)
2538   prefix_sql = if options[:temp]
2539     raise(Error, "can't provide both :temp and :unlogged to create_table") if options[:unlogged]
2540     raise(Error, "can't provide both :temp and :foreign to create_table") if options[:foreign]
2541     temporary_table_sql
2542   elsif options[:foreign]
2543     raise(Error, "can't provide both :foreign and :unlogged to create_table") if options[:unlogged]
2544     'FOREIGN '
2545   elsif options.fetch(:unlogged){typecast_value_boolean(@opts[:unlogged_tables_default])}
2546     'UNLOGGED '
2547   end
2548 
2549   "CREATE #{prefix_sql}TABLE#{' IF NOT EXISTS' if options[:if_not_exists]} #{create_table_table_name_sql(name, options)}"
2550 end

create_table_sql(name, generator, options) click to toggle source

SQL for creating a table with PostgreSQL specific options

     # File lib/sequel/adapters/shared/postgres.rb
2553 def create_table_sql(name, generator, options)
2554   "#{super}#{create_table_suffix_sql(name, options)}"
2555 end

create_table_suffix_sql(name, options) click to toggle source

Handle various PostgreSQl specific table extensions such as inheritance, partitioning, tablespaces, and foreign tables.

     # File lib/sequel/adapters/shared/postgres.rb
2559 def create_table_suffix_sql(name, options)
2560   sql = String.new
2561 
2562   if inherits = options[:inherits]
2563     sql << " INHERITS (#{Array(inherits).map{|t| quote_schema_table(t)}.join(', ')})"
2564   end
2565 
2566   if partition_by = options[:partition_by]
2567     sql << " PARTITION BY #{options[:partition_type]||'RANGE'} #{literal(Array(partition_by))}"
2568   end
2569 
2570   if on_commit = options[:on_commit]
2571     raise(Error, "can't provide :on_commit without :temp to create_table") unless options[:temp]
2572     raise(Error, "unsupported on_commit option: #{on_commit.inspect}") unless ON_COMMIT.has_key?(on_commit)
2573     sql << " ON COMMIT #{ON_COMMIT[on_commit]}"
2574   end
2575 
2576   if tablespace = options[:tablespace]
2577     sql << " TABLESPACE #{quote_identifier(tablespace)}"
2578   end
2579 
2580   if server = options[:foreign]
2581     sql << " SERVER #{quote_identifier(server)}"
2582     if foreign_opts = options[:options]
2583       sql << " OPTIONS (#{foreign_opts.map{|k, v| "#{k} #{literal(v.to_s)}"}.join(', ')})"
2584     end
2585   end
2586 
2587   sql
2588 end

create_trigger_sql(table, name, function, opts=OPTS) click to toggle source

SQL for creating a database trigger.

     # File lib/sequel/adapters/shared/postgres.rb
2603 def create_trigger_sql(table, name, function, opts=OPTS)
2604   events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete]
2605   whence = opts[:after] ? 'AFTER' : 'BEFORE'
2606   if filter = opts[:when]
2607     raise Error, "Trigger conditions are not supported for this database" unless supports_trigger_conditions?
2608     filter = " WHEN #{filter_expr(filter)}"
2609   end
2610   "CREATE #{'OR REPLACE ' if opts[:replace]}TRIGGER #{name} #{whence} #{events.map{|e| e.to_s.upcase}.join(' OR ')} ON #{quote_schema_table(table)}#{' FOR EACH ROW' if opts[:each_row]}#{filter} EXECUTE PROCEDURE #{function}(#{Array(opts[:args]).map{|a| literal(a)}.join(', ')})"
2611 end

create_view_prefix_sql(name, options) click to toggle source

DDL fragment for initial part of CREATE VIEW statement

     # File lib/sequel/adapters/shared/postgres.rb
2614 def create_view_prefix_sql(name, options)
2615   sql = create_view_sql_append_columns("CREATE #{'OR REPLACE 'if options[:replace]}#{'TEMPORARY 'if options[:temp]}#{'RECURSIVE ' if options[:recursive]}#{'MATERIALIZED ' if options[:materialized]}VIEW #{quote_schema_table(name)}", options[:columns] || options[:recursive])
2616 
2617   if options[:security_invoker]
2618     sql += " WITH (security_invoker)"
2619   end
2620 
2621   if tablespace = options[:tablespace]
2622     sql += " TABLESPACE #{quote_identifier(tablespace)}"
2623   end
2624 
2625   sql
2626 end

database_error_regexps() click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2350 def database_error_regexps
2351   DATABASE_ERROR_REGEXPS
2352 end

database_specific_error_class_from_sqlstate(sqlstate) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2326 def database_specific_error_class_from_sqlstate(sqlstate)
2327   if sqlstate == '23P01'
2328     ExclusionConstraintViolation
2329   elsif sqlstate == '40P01'
2330     SerializationFailure
2331   elsif sqlstate == '55P03'
2332     DatabaseLockTimeout
2333   else
2334     super
2335   end
2336 end

drop_function_sql(name, opts=OPTS) click to toggle source

SQL for dropping a function from the database.

     # File lib/sequel/adapters/shared/postgres.rb
2629 def drop_function_sql(name, opts=OPTS)
2630   "DROP FUNCTION#{' IF EXISTS' if opts[:if_exists]} #{name}#{sql_function_args(opts[:args])}#{' CASCADE' if opts[:cascade]}"
2631 end

drop_index_sql(table, op) click to toggle source

Support :if_exists, :cascade, and :concurrently options.

     # File lib/sequel/adapters/shared/postgres.rb
2634 def drop_index_sql(table, op)
2635   sch, _ = schema_and_table(table)
2636   "DROP INDEX#{' CONCURRENTLY' if op[:concurrently]}#{' IF EXISTS' if op[:if_exists]} #{"#{quote_identifier(sch)}." if sch}#{quote_identifier(op[:name] || default_index_name(table, op[:columns]))}#{' CASCADE' if op[:cascade]}"
2637 end

drop_language_sql(name, opts=OPTS) click to toggle source

SQL for dropping a procedural language from the database.

     # File lib/sequel/adapters/shared/postgres.rb
2640 def drop_language_sql(name, opts=OPTS)
2641   "DROP LANGUAGE#{' IF EXISTS' if opts[:if_exists]} #{name}#{' CASCADE' if opts[:cascade]}"
2642 end

drop_property_graph_sql(name, opts=OPTS) click to toggle source

SQL for dropping a property graph from the database.

     # File lib/sequel/adapters/shared/postgres.rb
2645 def drop_property_graph_sql(name, opts=OPTS)
2646   "DROP PROPERTY GRAPH#{' IF EXISTS' if opts[:if_exists]} #{literal(name)}#{' CASCADE' if opts[:cascade]}"
2647 end

drop_schema_sql(name, opts=OPTS) click to toggle source

SQL for dropping a schema from the database.

     # File lib/sequel/adapters/shared/postgres.rb
2650 def drop_schema_sql(name, opts=OPTS)
2651   "DROP SCHEMA#{' IF EXISTS' if opts[:if_exists]} #{quote_identifier(name)}#{' CASCADE' if opts[:cascade]}"
2652 end

drop_table_sql(name, options) click to toggle source

Support :foreign tables

     # File lib/sequel/adapters/shared/postgres.rb
2660 def drop_table_sql(name, options)
2661   "DROP#{' FOREIGN' if options[:foreign]} TABLE#{' IF EXISTS' if options[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if options[:cascade]}"
2662 end

drop_trigger_sql(table, name, opts=OPTS) click to toggle source

SQL for dropping a trigger from the database.

     # File lib/sequel/adapters/shared/postgres.rb
2655 def drop_trigger_sql(table, name, opts=OPTS)
2656   "DROP TRIGGER#{' IF EXISTS' if opts[:if_exists]} #{name} ON #{quote_schema_table(table)}#{' CASCADE' if opts[:cascade]}"
2657 end

drop_view_sql(name, opts=OPTS) click to toggle source

SQL for dropping a view from the database.

     # File lib/sequel/adapters/shared/postgres.rb
2665 def drop_view_sql(name, opts=OPTS)
2666   "DROP #{'MATERIALIZED ' if opts[:materialized]}VIEW#{' IF EXISTS' if opts[:if_exists]} #{quote_schema_table(name)}#{' CASCADE' if opts[:cascade]}"
2667 end

filter_schema(ds, opts) click to toggle source

If opts includes a :schema option, use it, otherwise restrict the filter to only the currently visible schemas.

     # File lib/sequel/adapters/shared/postgres.rb
2671 def filter_schema(ds, opts)
2672   expr = if schema = opts[:schema]
2673     if schema.is_a?(SQL::Identifier)
2674       schema.value.to_s
2675     else
2676       schema.to_s
2677     end
2678   else
2679     Sequel.function(:any, Sequel.function(:current_schemas, false))
2680   end
2681   ds.where{{pg_namespace[:nspname]=>expr}}
2682 end

index_definition_sql(table_name, index) click to toggle source

     # File lib/sequel/adapters/shared/postgres.rb
2684 def index_definition_sql(table_name, index)
2685   cols = index[:columns]
2686   index_name = index[:name] || default_index_name(table_name, cols)
2687 
2688   expr = if o = index[:opclass]
2689     "(#{Array(cols).map{|c| "#{literal(c)} #{o}"}.join(', ')})"
2690   else
2691     literal(Array(cols))
2692   end
2693 
2694   if_not_exists = " IF NOT EXISTS" if index[:if_not_exists]
2695   unique = "UNIQUE " if index[:unique]
2696   index_type = index[:type]
2697   filter = index[:where] || index[:filter]
2698   filter = " WHERE #{filter_expr(filter)}" if filter
2699   nulls_distinct = " NULLS#{' NOT' if index[:nulls_distinct] == false} DISTINCT" unless index[:nulls_distinct].nil?
2700 
2701   case index_type
2702   when :full_text
2703     expr = "(to_tsvector(#{literal(index[:language] || 'simple')}::regconfig, #{literal(dataset.send(:full_text_string_join, cols))}))"
2704     index_type = index[:index_type] || :gin
2705   when :spatial
2706     index_type = :gist
2707   end
2708 
2709   "CREATE #{unique}INDEX#{' CONCURRENTLY' if index[:concurrently]}#{if_not_exists} #{quote_identifier(index_name)} ON#{' ONLY' if index[:only]} #{quote_schema_table(table_name)} #{"USING #{index_type} " if index_type}#{expr}#{" INCLUDE #{literal(Array(index[:include]))}" if index[:include]}#{nulls_distinct}#{" TABLESPACE #{quote_identifier(index[:tablespace])}" if index[:tablespace]}#{filter}"
2710 end

initialize_postgres_adapter() click to toggle source

Setup datastructures shared by all postgres adapters.

     # File lib/sequel/adapters/shared/postgres.rb
2713 def initialize_postgres_adapter
2714   @primary_keys = {}
2715   @primary_key_sequences = {}
2716   @supported_types = {}
2717   procs = @conversion_procs = CONVERSION_PROCS.dup
2718   procs[1184] = procs[1114] = method(:to_application_timestamp)
2719 end

pg_class_relname(type, opts) { || ... } click to toggle source

Backbone of the tables and views support.

     # File lib/sequel/adapters/shared/postgres.rb
2722 def pg_class_relname(type, opts)
2723   ds = metadata_dataset.from(:pg_class).where(:relkind=>type).select(:relname).server(opts[:server]).join(:pg_namespace, :oid=>:relnamespace)
2724   ds = filter_schema(ds, opts)
2725   m = output_identifier_meth
2726   if defined?(yield)
2727     yield(ds)
2728   elsif opts[:qualify]
2729     ds.select_append{pg_namespace[:nspname]}.map{|r| Sequel.qualify(m.call(r[:nspname]).to_s, m.call(r[:relname]).to_s)}
2730   else
2731     ds.map{|r| m.call(r[:relname])}
2732   end
2733 end

regclass_oid(expr, opts=OPTS) click to toggle source

Return an expression the oid for the table expr. Used by the metadata parsing code to disambiguate unqualified tables.

     # File lib/sequel/adapters/shared/postgres.rb
2737 def regclass_oid(expr, opts=OPTS)
2738   if expr.is_a?(String) && !expr.is_a?(LiteralString)
2739     expr = Sequel.identifier(expr)
2740   end
2741 
2742   sch, table = schema_and_table(expr)
2743   sch ||= opts[:schema]
2744   if sch
2745     expr = Sequel.qualify(sch, table)
2746   end
2747   
2748   expr = if ds = opts[:dataset]
2749     ds.literal(expr)
2750   else
2751     literal(expr)
2752   end
2753 
2754   Sequel.cast(expr.to_s,:regclass).cast(:oid)
2755 end

remove_all_cached_schemas() click to toggle source

Clear all cached schema information

     # File lib/sequel/adapters/shared/postgres.rb
2768 def remove_all_cached_schemas
2769   @primary_keys.clear
2770   @primary_key_sequences.clear
2771   @schemas.clear
2772 end

remove_cached_schema(table) click to toggle source

Remove the cached entries for primary keys and sequences when a table is changed.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2758 def remove_cached_schema(table)
2759   tab = quote_schema_table(table)
2760   Sequel.synchronize do
2761     @primary_keys.delete(tab)
2762     @primary_key_sequences.delete(tab)
2763   end
2764   super
2765 end

rename_schema_sql(name, new_name) click to toggle source

SQL for renaming a schema.

     # File lib/sequel/adapters/shared/postgres.rb
2775 def rename_schema_sql(name, new_name)
2776   "ALTER SCHEMA #{quote_identifier(name)} RENAME TO #{quote_identifier(new_name)}"
2777 end

rename_table_sql(name, new_name) click to toggle source

SQL DDL statement for renaming a table. PostgreSQL doesn’t allow you to change a table’s schema in a rename table operation, so specifying a new schema in new_name will not have an effect.

     # File lib/sequel/adapters/shared/postgres.rb
2781 def rename_table_sql(name, new_name)
2782   "ALTER TABLE #{quote_schema_table(name)} RENAME TO #{quote_identifier(schema_and_table(new_name).last)}"
2783 end

schema_array_type(db_type) click to toggle source

The schema :type entry to use for array types.

     # File lib/sequel/adapters/shared/postgres.rb
2798 def schema_array_type(db_type)
2799   :array
2800 end

schema_column_type(db_type) click to toggle source

Handle interval and citext types.

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2786 def schema_column_type(db_type)
2787   case db_type
2788   when /\Ainterval\z/i
2789     :interval
2790   when /\Acitext\z/i
2791     :string
2792   else
2793     super
2794   end
2795 end

schema_composite_type(db_type) click to toggle source

The schema :type entry to use for row/composite types.

     # File lib/sequel/adapters/shared/postgres.rb
2803 def schema_composite_type(db_type)
2804   :composite
2805 end

schema_enum_type(db_type) click to toggle source

The schema :type entry to use for enum types.

     # File lib/sequel/adapters/shared/postgres.rb
2808 def schema_enum_type(db_type)
2809   :enum
2810 end

schema_multirange_type(db_type) click to toggle source

The schema :type entry to use for multirange types.

     # File lib/sequel/adapters/shared/postgres.rb
2818 def schema_multirange_type(db_type)
2819   :multirange
2820 end

schema_parse_table(table_name, opts) click to toggle source

The dataset used for parsing table schemas, using the pg_* system catalogs.

     # File lib/sequel/adapters/shared/postgres.rb
2835 def schema_parse_table(table_name, opts)
2836   m = output_identifier_meth(opts[:dataset])
2837 
2838   _schema_ds.where_all(Sequel[:pg_class][:oid]=>regclass_oid(table_name, opts)).map do |row|
2839     row[:default] = nil if blank_object?(row[:default])
2840     if row[:base_oid]
2841       row[:domain_oid] = row[:oid]
2842       row[:oid] = row.delete(:base_oid)
2843       row[:db_domain_type] = row[:db_type]
2844       row[:db_type] = row.delete(:db_base_type)
2845     else
2846       row.delete(:base_oid)
2847       row.delete(:db_base_type)
2848     end
2849 
2850     db_type = row[:db_type]
2851     row[:type] = if row.delete(:is_array)
2852       schema_array_type(db_type)
2853     else
2854       send(TYPTYPE_METHOD_MAP[row.delete(:typtype)], db_type)
2855     end
2856     identity = row.delete(:attidentity)
2857     if row[:primary_key]
2858       row[:auto_increment] = !!(row[:default] =~ /\A(?:nextval)/i) || identity == 'a' || identity == 'd'
2859     end
2860 
2861     # :nocov:
2862     if server_version >= 90600
2863     # :nocov:
2864       case row[:oid]
2865       when 1082
2866         row[:min_value] = MIN_DATE
2867         row[:max_value] = MAX_DATE
2868       when 1184, 1114
2869         if Sequel.datetime_class == Time
2870           row[:min_value] = MIN_TIMESTAMP
2871           row[:max_value] = MAX_TIMESTAMP
2872         end
2873       end
2874     end
2875 
2876     [m.call(row.delete(:name)), row]
2877   end
2878 end

schema_range_type(db_type) click to toggle source

The schema :type entry to use for range types.

     # File lib/sequel/adapters/shared/postgres.rb
2813 def schema_range_type(db_type)
2814   :range
2815 end

set_transaction_isolation(conn, opts) click to toggle source

Set the transaction isolation level on the given connection

     # File lib/sequel/adapters/shared/postgres.rb
2881 def set_transaction_isolation(conn, opts)
2882   level = opts.fetch(:isolation, transaction_isolation_level)
2883   read_only = opts[:read_only]
2884   deferrable = opts[:deferrable]
2885   if level || !read_only.nil? || !deferrable.nil?
2886     sql = String.new
2887     sql << "SET TRANSACTION"
2888     sql << " ISOLATION LEVEL #{Sequel::Database::TRANSACTION_ISOLATION_LEVELS[level]}" if level
2889     sql << " READ #{read_only ? 'ONLY' : 'WRITE'}" unless read_only.nil?
2890     sql << " #{'NOT ' unless deferrable}DEFERRABLE" unless deferrable.nil?
2891     log_connection_execute(conn, sql)
2892   end
2893 end

sql_function_args(args) click to toggle source

Turns an array of argument specifiers into an SQL fragment used for function arguments. See create_function_sql.

     # File lib/sequel/adapters/shared/postgres.rb
2896 def sql_function_args(args)
2897   "(#{Array(args).map{|a| Array(a).reverse.join(' ')}.join(', ')})"
2898 end

supports_combining_alter_table_ops?() click to toggle source

PostgreSQL can combine multiple alter table ops into a single query.

     # File lib/sequel/adapters/shared/postgres.rb
2901 def supports_combining_alter_table_ops?
2902   true
2903 end

supports_create_or_replace_view?() click to toggle source

PostgreSQL supports CREATE OR REPLACE VIEW.

     # File lib/sequel/adapters/shared/postgres.rb
2906 def supports_create_or_replace_view?
2907   true
2908 end

type_literal_generic_bignum_symbol(column) click to toggle source

Handle bigserial type if :serial option is present

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2911 def type_literal_generic_bignum_symbol(column)
2912   column[:serial] ? :bigserial : super
2913 end

type_literal_generic_file(column) click to toggle source

PostgreSQL uses the bytea data type for blobs

     # File lib/sequel/adapters/shared/postgres.rb
2916 def type_literal_generic_file(column)
2917   :bytea
2918 end

type_literal_generic_integer(column) click to toggle source

Handle serial type if :serial option is present

Calls superclass method

     # File lib/sequel/adapters/shared/postgres.rb
2921 def type_literal_generic_integer(column)
2922   column[:serial] ? :serial : super
2923 end

type_literal_generic_string(column) click to toggle source

PostgreSQL prefers the text datatype. If a fixed size is requested, the char type is used. If the text type is specifically disallowed or there is a size specified, use the varchar type. Otherwise use the text type.

     # File lib/sequel/adapters/shared/postgres.rb
2929 def type_literal_generic_string(column)
2930   if column[:text]
2931     :text
2932   elsif column[:fixed]
2933     "char(#{column[:size]||default_string_column_size})"
2934   elsif column[:text] == false || column[:size]
2935     "varchar(#{column[:size]||default_string_column_size})"
2936   else
2937     :text
2938   end
2939 end

unique_constraint_sql_fragment(constraint) click to toggle source

Support :nulls_not_distinct option.

     # File lib/sequel/adapters/shared/postgres.rb
2942 def unique_constraint_sql_fragment(constraint)
2943   if constraint[:nulls_not_distinct]
2944     'UNIQUE NULLS NOT DISTINCT'
2945   else
2946     'UNIQUE'
2947   end
2948 end

view_with_check_option_support() click to toggle source

PostgreSQL 9.4+ supports views with check option.

     # File lib/sequel/adapters/shared/postgres.rb
2951 def view_with_check_option_support
2952   # :nocov:
2953   :local if server_version >= 90400
2954   # :nocov:
2955 end