module Sequel::Postgres::DatabaseMethods

  1. lib/sequel/adapters/shared/postgres.rb

Constants

DATABASE_ERROR_REGEXPS = [ # Add this check first, since otherwise it's possible for users to control # which exception class is generated. [/invalid input syntax/, DatabaseError], [/duplicate key value violates unique constraint/, UniqueConstraintViolation], [/violates foreign key constraint/, ForeignKeyConstraintViolation], [/violates check constraint/, CheckConstraintViolation], [/violates not-null constraint/, NotNullConstraintViolation], [/conflicting key value violates exclusion constraint/, ExclusionConstraintViolation], [/could not serialize access/, SerializationFailure], [/could not obtain lock on row in relation/, DatabaseLockTimeout], ].freeze  
FOREIGN_KEY_LIST_ON_DELETE_MAP = {'a'=>:no_action, 'r'=>:restrict, 'c'=>:cascade, 'n'=>:set_null, 'd'=>:set_default}.freeze  
MAX_DATE = Date.new(5874897, 12, 31)  
MAX_TIMESTAMP = (Time.utc(294277) - Rational(1, 1000000)).freeze  
MIN_DATE = Date.new(-4713, 11, 24)  
MIN_TIMESTAMP = Time.utc(-4713, 11, 24).freeze  
ON_COMMIT = {:drop => 'DROP', :delete_rows => 'DELETE ROWS', :preserve_rows => 'PRESERVE ROWS'}.freeze  
SELECT_CUSTOM_SEQUENCE_SQL = (<<-end_sql SELECT name.nspname AS "schema", CASE WHEN split_part(pg_get_expr(def.adbin, attr.attrelid), '''', 2) ~ '.' THEN substr(split_part(pg_get_expr(def.adbin, attr.attrelid), '''', 2), strpos(split_part(pg_get_expr(def.adbin, attr.attrelid), '''', 2), '.')+1) ELSE split_part(pg_get_expr(def.adbin, attr.attrelid), '''', 2) END AS "sequence" FROM pg_class t JOIN pg_namespace name ON (t.relnamespace = name.oid) JOIN pg_attribute attr ON (t.oid = attrelid) JOIN pg_attrdef def ON (adrelid = attrelid AND adnum = attnum) JOIN pg_constraint cons ON (conrelid = adrelid AND adnum = conkey[1]) WHERE cons.contype = 'p' AND pg_get_expr(def.adbin, attr.attrelid) ~* 'nextval' end_sql ).strip.gsub(/\s+/, ' ').freeze  

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 = (<<-end_sql SELECT pg_attribute.attname AS pk FROM pg_class, pg_attribute, pg_index, pg_namespace WHERE pg_class.oid = pg_attribute.attrelid AND pg_class.relnamespace = pg_namespace.oid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[0] = pg_attribute.attnum AND pg_index.indisprimary = 't' end_sql ).strip.gsub(/\s+/, ' ').freeze  

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 = (<<-end_sql SELECT name.nspname AS "schema", seq.relname AS "sequence" FROM pg_class seq, pg_attribute attr, pg_depend dep, pg_namespace name, pg_constraint cons, pg_class t WHERE seq.oid = dep.objid AND seq.relnamespace = name.oid AND seq.relkind = 'S' AND attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid AND attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1] AND attr.attrelid = t.oid AND cons.contype = 'p' end_sql ).strip.gsub(/\s+/, ' ').freeze  

SQL fragment for getting sequence associated with table’s primary key, assuming it was a serial primary key column.
TYPTYPE_METHOD_MAP = { 'c' => :schema_composite_type, 'e' => :schema_enum_type, 'r' => :schema_range_type, 'm' => :schema_multirange_type, }  
VALID_CLIENT_MIN_MESSAGES = %w'DEBUG5 DEBUG4 DEBUG3 DEBUG2 DEBUG1 LOG NOTICE WARNING ERROR FATAL PANIC'.freeze.each(&:freeze)  

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)

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

[show source] 
    # 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)

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.

[show source] 
    # 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)

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
[show source] 
    # 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)

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.

[show source] 
    # 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)
[show 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)

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

[show source] 
     # 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)

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.

[show source] 
     # 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)

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.

[show source] 
     # 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)

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)
[show source] 
     # 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)

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)

[show source] 
     # 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)

Support partitions of tables using the :partition_of option.

[show source] 
     # 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)

Support partitions of tables using the :partition_of option.

[show source] 
     # 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)

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

[show source] 
     # 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()
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1192 def database_type
1193   :postgres
1194 end
defer_constraints(opts=OPTS)

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
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1211 def defer_constraints(opts=OPTS)
1212   _set_constraints(' DEFERRED', opts)
1213 end
do(code, opts=OPTS)

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.

[show source] 
     # 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)

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.

[show source] 
     # 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)

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.

[show source] 
     # 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)

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.

[show source] 
     # 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)

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.

[show source] 
     # 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)

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.

[show source] 
     # 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)

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.

[show source] 
     # 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()
[show source] 
     # 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)

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 (...)
[show source] 
     # 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)

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
[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1430 def immediate_constraints(opts=OPTS)
1431   _set_constraints(' IMMEDIATE', opts)
1432 end
indexes(table, opts=OPTS)

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.

[show source] 
     # 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()

Dataset containing all current database locks

[show source] 
     # 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)

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.

[show source] 
     # 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)

Return primary key for the given table.

[show source] 
     # 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)

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

[show source] 
     # 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)

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

[show source] 
     # 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)

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
[show source] 
     # 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)

Rename a property graph.

DB.rename_property_graph(:x, :y)
# ALTER PROPERTY GRAPH x RENAME TO y
[show source] 
     # 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)

Rename a schema in the database. Arguments:

name

Current name of the schema

opts

New name for the schema

[show source] 
     # 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)

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

[show source] 
     # 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)
[show 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()

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

[show source] 
     # 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)

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

[show source] 
     # 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)

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
[show source] 
     # 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?()

PostgreSQL supports CREATE TABLE IF NOT EXISTS on 9.1+

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1602 def supports_create_table_if_not_exists?
1603   server_version >= 90100
1604 end
supports_deferrable_constraints?()

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

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1607 def supports_deferrable_constraints?
1608   server_version >= 90000
1609 end
supports_deferrable_foreign_key_constraints?()

PostgreSQL supports deferrable foreign key constraints.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1612 def supports_deferrable_foreign_key_constraints?
1613   true
1614 end
supports_drop_table_if_exists?()

PostgreSQL supports DROP TABLE IF EXISTS

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1617 def supports_drop_table_if_exists?
1618   true
1619 end
supports_partial_indexes?()

PostgreSQL supports partial indexes.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1622 def supports_partial_indexes?
1623   true
1624 end
supports_prepared_transactions?()

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

[show source] 
     # 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?()

PostgreSQL supports savepoints

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1639 def supports_savepoints?
1640   true
1641 end
supports_transaction_isolation_levels?()

PostgreSQL supports transaction isolation levels

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1644 def supports_transaction_isolation_levels?
1645   true
1646 end
supports_transactional_ddl?()

PostgreSQL supports transaction DDL statements.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1649 def supports_transactional_ddl?
1650   true
1651 end
supports_trigger_conditions?()

PostgreSQL 9.0+ supports trigger conditions.

[show source] 
     # File lib/sequel/adapters/shared/postgres.rb
1627 def supports_trigger_conditions?
1628   server_version >= 90000
1629 end
tables(opts=OPTS, &block)

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

[show source] 
     # 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)

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

[show source] 
     # 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)

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
[show source] 
     # 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)

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

[show source] 
     # 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)

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.

[show source] 
     # 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