123456789_123456789_123456789_123456789_123456789_

Module: ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements

Relationships & Source Files
Extension / Inclusion / Inheritance Descendants
Included In:
Defined in: activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb

Instance Attribute Summary

Instance Method Summary

Instance Attribute Details

#client_min_messages (rw)

Returns the current client message level.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 239

def client_min_messages
  query_value("SHOW client_min_messages", "SCHEMA")
end

#client_min_messages=(level) (rw)

Set the client message level.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 244

def client_min_messages=(level)
  internal_execute("SET client_min_messages TO '#{level}'")
end

#schema_search_path (rw)

Returns the active schema search path.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 234

def schema_search_path
  @schema_search_path ||= query_value("SHOW search_path", "SCHEMA")
end

#schema_search_path=(schema_csv) (rw)

Sets the schema search path to a string of comma-separated schema names. Names beginning with $ have to be quoted (e.g. $user => ‘$user’). See: www.postgresql.org/docs/current/static/ddl-schemas.html

This should be not be called manually but set in database.yml.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 226

def schema_search_path=(schema_csv)
  if schema_csv
    internal_execute("SET search_path TO #{schema_csv}")
    @schema_search_path = schema_csv
  end
end

Instance Method Details

#add_exclusion_constraint(table_name, expression, **options)

Adds a new exclusion constraint to the table. expression is a ::String representation of a list of exclusion elements and operators.

add_exclusion_constraint :products, "price WITH =, availability_range WITH &&", using: :gist, name: "price_check"

generates:

ALTER TABLE "products" ADD CONSTRAINT price_check EXCLUDE USING gist (price WITH =, availability_range WITH &&)

The options hash can include the following keys:

:name

The constraint name. Defaults to excl_rails_<identifier>.

:deferrable

Specify whether or not the exclusion constraint should be deferrable. Valid values are false or :immediate or :deferred to specify the default behavior. Defaults to false.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 695

def add_exclusion_constraint(table_name, expression, **options)
  options = exclusion_constraint_options(table_name, expression, options)
  at = create_alter_table(table_name)
  at.add_exclusion_constraint(expression, options)

  execute schema_creation.accept(at)
end

#add_foreign_key(from_table, to_table, **options)

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 526

def add_foreign_key(from_table, to_table, **options)
  if options[:deferrable] == true
    ActiveRecord.deprecator.warn(<<~MSG)
      `deferrable: true` is deprecated in favor of `deferrable: :immediate`, and will be removed in Rails 7.2.
    MSG

    options[:deferrable] = :immediate
  end

  assert_valid_deferrable(options[:deferrable])

  super
end

#add_unique_constraint(table_name, column_name = nil, **options)

Adds a new unique constraint to the table.

add_unique_constraint :sections, [:position], deferrable: :deferred, name: "unique_position"

generates:

ALTER TABLE "sections" ADD CONSTRAINT unique_position UNIQUE (position) DEFERRABLE INITIALLY DEFERRED

If you want to change an existing unique index to deferrable, you can use :using_index to create deferrable unique constraints.

add_unique_constraint :sections, deferrable: :deferred, name: "unique_position", using_index: "index_sections_on_position"

The options hash can include the following keys:

:name

The constraint name. Defaults to uniq_rails_<identifier>.

:deferrable

Specify whether or not the unique constraint should be deferrable. Valid values are false or :immediate or :deferred to specify the default behavior. Defaults to false.

:using_index

To specify an existing unique index name. Defaults to nil.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 746

def add_unique_constraint(table_name, column_name = nil, **options)
  options = unique_constraint_options(table_name, column_name, options)
  at = create_alter_table(table_name)
  at.add_unique_constraint(column_name, options)

  execute schema_creation.accept(at)
end

#collation

Returns the current database collation.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 191

def collation
  query_value("SELECT datcollate FROM pg_database WHERE datname = current_database()", "SCHEMA")
end

#create_database(name, options = {})

Create a new ::ActiveRecord::ConnectionAdapters::PostgreSQL database. Options include :owner, :template, :encoding (defaults to utf8), :collation, :ctype, :tablespace, and :connection_limit (note that ::ActiveRecord::ConnectionAdapters::MySQL uses :charset while ::ActiveRecord::ConnectionAdapters::PostgreSQL uses :encoding).

Example:

create_database config[:database], config
create_database 'foo_development', encoding: 'unicode'
[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 22

def create_database(name, options = {})
  options = { encoding: "utf8" }.merge!(options.symbolize_keys)

  option_string = options.each_with_object(+"") do |(key, value), memo|
    memo << case key
            when :owner
              " OWNER = \"#{value}\""
            when :template
              " TEMPLATE = \"#{value}\""
            when :encoding
              " ENCODING = '#{value}'"
            when :collation
              " LC_COLLATE = '#{value}'"
            when :ctype
              " LC_CTYPE = '#{value}'"
            when :tablespace
              " TABLESPACE = \"#{value}\""
            when :connection_limit
              " CONNECTION LIMIT = #{value}"
            else
              ""
    end
  end

  execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}"
end

#create_schema(schema_name)

Creates a schema for the given schema name.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 212

def create_schema(schema_name)
  execute "CREATE SCHEMA #{quote_schema_name(schema_name)}"
end

#ctype

Returns the current database ctype.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 196

def ctype
  query_value("SELECT datctype FROM pg_database WHERE datname = current_database()", "SCHEMA")
end

#current_database

Returns the current database name.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 176

def current_database
  query_value("SELECT current_database()", "SCHEMA")
end

#current_schema

Returns the current schema name.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 181

def current_schema
  query_value("SELECT current_schema", "SCHEMA")
end

#drop_schema(schema_name, **options)

Drops the schema for the given schema name.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 217

def drop_schema(schema_name, **options)
  execute "DROP SCHEMA#{' IF EXISTS' if options[:if_exists]} #{quote_schema_name(schema_name)} CASCADE"
end

#encoding

Returns the current database encoding format.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 186

def encoding
  query_value("SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = current_database()", "SCHEMA")
end

#exclusion_constraints(table_name)

Returns an array of exclusion constraints for the given table. The exclusion constraints are represented as ExclusionConstraintDefinition objects.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 619

def exclusion_constraints(table_name)
  scope = quoted_scope(table_name)

  exclusion_info = internal_exec_query(<<-SQL, "SCHEMA")
    SELECT conname, pg_get_constraintdef(c.oid) AS constraintdef, c.condeferrable, c.condeferred
    FROM pg_constraint c
    JOIN pg_class t ON c.conrelid = t.oid
    JOIN pg_namespace n ON n.oid = c.connamespace
    WHERE c.contype = 'x'
      AND t.relname = #{scope[:name]}
      AND n.nspname = #{scope[:schema]}
  SQL

  exclusion_info.map do |row|
    method_and_elements, predicate = row["constraintdef"].split(" WHERE ")
    method_and_elements_parts = method_and_elements.match(/EXCLUDE(?: USING (?<using>\S+))? \((?<expression>.+)\)/)
    predicate.remove!(/ DEFERRABLE(?: INITIALLY (?:IMMEDIATE|DEFERRED))?/) if predicate
    predicate = predicate.from(2).to(-3) if predicate # strip 2 opening and closing parentheses

    deferrable = extract_constraint_deferrable(row["condeferrable"], row["condeferred"])

    options = {
      name: row["conname"],
      using: method_and_elements_parts["using"].to_sym,
      where: predicate,
      deferrable: deferrable
    }

    ExclusionConstraintDefinition.new(table_name, method_and_elements_parts["expression"], options)
  end
end

#foreign_keys(table_name)

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 540

def foreign_keys(table_name)
  scope = quoted_scope(table_name)
  fk_info = internal_exec_query(<<~SQL, "SCHEMA", allow_retry: true, materialize_transactions: false)
    SELECT t2.oid::regclass::text AS to_table, a1.attname AS column, a2.attname AS primary_key, c.conname AS name, c.confupdtype AS on_update, c.confdeltype AS on_delete, c.convalidated AS valid, c.condeferrable AS deferrable, c.condeferred AS deferred, c.conkey, c.confkey, c.conrelid, c.confrelid
    FROM pg_constraint c
    JOIN pg_class t1 ON c.conrelid = t1.oid
    JOIN pg_class t2 ON c.confrelid = t2.oid
    JOIN pg_attribute a1 ON a1.attnum = c.conkey[1] AND a1.attrelid = t1.oid
    JOIN pg_attribute a2 ON a2.attnum = c.confkey[1] AND a2.attrelid = t2.oid
    JOIN pg_namespace t3 ON c.connamespace = t3.oid
    WHERE c.contype = 'f'
      AND t1.relname = #{scope[:name]}
      AND t3.nspname = #{scope[:schema]}
    ORDER BY c.conname
  SQL

  fk_info.map do |row|
    to_table = Utils.unquote_identifier(row["to_table"])
    conkey = row["conkey"].scan(/\d+/).map(&:to_i)
    confkey = row["confkey"].scan(/\d+/).map(&:to_i)

    if conkey.size > 1
      column = column_names_from_column_numbers(row["conrelid"], conkey)
      primary_key = column_names_from_column_numbers(row["confrelid"], confkey)
    else
      column = Utils.unquote_identifier(row["column"])
      primary_key = row["primary_key"]
    end

    options = {
      column: column,
      name: row["name"],
      primary_key: primary_key
    }

    options[:on_delete] = extract_foreign_key_action(row["on_delete"])
    options[:on_update] = extract_foreign_key_action(row["on_update"])
    options[:deferrable] = extract_constraint_deferrable(row["deferrable"], row["deferred"])

    options[:validate] = row["valid"]

    ForeignKeyDefinition.new(table_name, to_table, options)
  end
end

#foreign_table_exists?(table_name) ⇒ Boolean

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 589

def foreign_table_exists?(table_name)
  query_values(data_source_sql(table_name, type: "FOREIGN TABLE"), "SCHEMA").any? if table_name.present?
end

#foreign_tables

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 585

def foreign_tables
  query_values(data_source_sql(type: "FOREIGN TABLE"), "SCHEMA")
end

#index_name_exists?(table_name, index_name) ⇒ Boolean

Verifies existence of an index with a given name.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 68

def index_name_exists?(table_name, index_name)
  table = quoted_scope(table_name)
  index = quoted_scope(index_name)

  query_value(<<~SQL, "SCHEMA").to_i > 0
    SELECT COUNT(*)
    FROM pg_class t
    INNER JOIN pg_index d ON t.oid = d.indrelid
    INNER JOIN pg_class i ON d.indexrelid = i.oid
    LEFT JOIN pg_namespace n ON n.oid = t.relnamespace
    WHERE i.relkind IN ('i', 'I')
      AND i.relname = #{index[:name]}
      AND t.relname = #{table[:name]}
      AND n.nspname = #{table[:schema]}
  SQL
end

#remove_exclusion_constraint(table_name, expression = nil, **options)

Removes the given exclusion constraint from the table.

remove_exclusion_constraint :products, name: "price_check"

The expression parameter will be ignored if present. It can be helpful to provide this in a migration’s change method so it can be reverted. In that case, expression will be used by #add_exclusion_constraint.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 718

def remove_exclusion_constraint(table_name, expression = nil, **options)
  excl_name_to_delete = exclusion_constraint_for!(table_name, expression: expression, **options).name

  at = create_alter_table(table_name)
  at.drop_exclusion_constraint(excl_name_to_delete)

  execute schema_creation.accept(at)
end

#remove_unique_constraint(table_name, column_name = nil, **options)

Removes the given unique constraint from the table.

remove_unique_constraint :sections, name: "unique_position"

The column_name parameter will be ignored if present. It can be helpful to provide this in a migration’s change method so it can be reverted. In that case, column_name will be used by #add_unique_constraint.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 773

def remove_unique_constraint(table_name, column_name = nil, **options)
  unique_name_to_delete = unique_constraint_for!(table_name, column: column_name, **options).name

  at = create_alter_table(table_name)
  at.drop_unique_constraint(unique_name_to_delete)

  execute schema_creation.accept(at)
end

#rename_index(table_name, old_name, new_name)

Renames an index of a table. Raises error if length of new index name is greater than allowed limit.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 514

def rename_index(table_name, old_name, new_name)
  validate_index_length!(table_name, new_name)

  schema, = extract_schema_qualified_name(table_name)
  execute "ALTER INDEX #{quote_table_name(schema) + '.' if schema}#{quote_column_name(old_name)} RENAME TO #{quote_table_name(new_name)}"
end

#rename_table(table_name, new_name, **options)

Renames a table. Also renames a table’s primary key sequence if the sequence name exists and matches the Active Record default.

Example:

rename_table('octopuses', 'octopi')
[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 382

def rename_table(table_name, new_name, **options)
  validate_table_length!(new_name) unless options[:_uses_legacy_table_name]
  clear_cache!
  schema_cache.clear_data_source_cache!(table_name.to_s)
  schema_cache.clear_data_source_cache!(new_name.to_s)
  execute "ALTER TABLE #{quote_table_name(table_name)} RENAME TO #{quote_table_name(new_name)}"
  pk, seq = pk_and_sequence_for(new_name)
  if pk
    # PostgreSQL automatically creates an index for PRIMARY KEY with name consisting of
    # truncated table name and "_pkey" suffix fitting into max_identifier_length number of characters.
    max_pkey_prefix = max_identifier_length - "_pkey".size
    idx = "#{table_name[0, max_pkey_prefix]}_pkey"
    new_idx = "#{new_name[0, max_pkey_prefix]}_pkey"
    execute "ALTER INDEX #{quote_table_name(idx)} RENAME TO #{quote_table_name(new_idx)}"

    # PostgreSQL automatically creates a sequence for PRIMARY KEY with name consisting of
    # truncated table name and "#{primary_key}_seq" suffix fitting into max_identifier_length number of characters.
    max_seq_prefix = max_identifier_length - "_#{pk}_seq".size
    if seq && seq.identifier == "#{table_name[0, max_seq_prefix]}_#{pk}_seq"
      new_seq = "#{new_name[0, max_seq_prefix]}_#{pk}_seq"
      execute "ALTER TABLE #{seq.quoted} RENAME TO #{quote_table_name(new_seq)}"
    end
  end
  rename_table_indexes(table_name, new_name, **options)
end

#schema_exists?(name) ⇒ Boolean

Returns true if schema exists.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 63

def schema_exists?(name)
  query_value("SELECT COUNT(*) FROM pg_namespace WHERE nspname = #{quote(name)}", "SCHEMA").to_i > 0
end

#schema_names

Returns an array of schema names.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 201

def schema_names
  query_values(<<~SQL, "SCHEMA")
    SELECT nspname
      FROM pg_namespace
     WHERE nspname !~ '^pg_.*'
       AND nspname NOT IN ('information_schema')
     ORDER by nspname;
  SQL
end

#serial_sequence(table, column)

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 259

def serial_sequence(table, column)
  query_value("SELECT pg_get_serial_sequence(#{quote(table)}, #{quote(column)})", "SCHEMA")
end

#unique_constraints(table_name)

Returns an array of unique constraints for the given table. The unique constraints are represented as UniqueConstraintDefinition objects.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 653

def unique_constraints(table_name)
  scope = quoted_scope(table_name)

  unique_info = internal_exec_query(<<~SQL, "SCHEMA", allow_retry: true, materialize_transactions: false)
    SELECT c.conname, c.conrelid, c.conkey, c.condeferrable, c.condeferred
    FROM pg_constraint c
    JOIN pg_class t ON c.conrelid = t.oid
    JOIN pg_namespace n ON n.oid = c.connamespace
    WHERE c.contype = 'u'
      AND t.relname = #{scope[:name]}
      AND n.nspname = #{scope[:schema]}
  SQL

  unique_info.map do |row|
    conkey = row["conkey"].delete("{}").split(",").map(&:to_i)
    columns = column_names_from_column_numbers(row["conrelid"], conkey)

    deferrable = extract_constraint_deferrable(row["condeferrable"], row["condeferred"])

    options = {
      name: row["conname"],
      deferrable: deferrable
    }

    UniqueConstraintDefinition.new(table_name, columns, options)
  end
end

#validate_check_constraint(table_name, **options)

Validates the given check constraint.

validate_check_constraint :products, name: "price_check"

The options hash accepts the same keys as add_check_constraint.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 879

def validate_check_constraint(table_name, **options)
  chk_name_to_validate = check_constraint_for!(table_name, **options).name

  validate_constraint table_name, chk_name_to_validate
end

#validate_constraint(table_name, constraint_name)

Validates the given constraint.

Validates the constraint named constraint_name on accounts.

validate_constraint :accounts, :constraint_name
[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 846

def validate_constraint(table_name, constraint_name)
  at = create_alter_table table_name
  at.validate_constraint constraint_name

  execute schema_creation.accept(at)
end

#validate_foreign_key(from_table, to_table = nil, **options)

Validates the given foreign key.

Validates the foreign key on accounts.branch_id.

validate_foreign_key :accounts, :branches

Validates the foreign key on accounts.owner_id.

validate_foreign_key :accounts, column: :owner_id

Validates the foreign key named special_fk_name on the accounts table.

validate_foreign_key :accounts, name: :special_fk_name

The options hash accepts the same keys as #add_foreign_key.

[ GitHub ]

  
# File 'activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb', line 868

def validate_foreign_key(from_table, to_table = nil, **options)
  fk_name_to_validate = foreign_key_for!(from_table, to_table: to_table, **options).name

  validate_constraint from_table, fk_name_to_validate
end