Skip to Content Skip to Search
Methods
A
C
D
E
F
I
R
S
U
V

Instance Public methods

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.

:using

Specify which index method to use when creating this exclusion constraint (e.g. :btree, :gist etc).

:where

Specify an exclusion constraint on a subset of the table (internally PostgreSQL creates a partial index for this).

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 722
def add_exclusion_constraint(table_name, expression, **options)
  options = exclusion_constraint_options(table_name, expression, options)
  at = build_alter_table_definition(table_name)
  at.add_exclusion_constraint(expression, options)

  execute_alter_table(at)
end

add_foreign_key(from_table, to_table, **options)

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 660
def add_foreign_key(from_table, to_table, **options)
  assert_valid_deferrable(options[:deferrable])

  if options.key?(:enforced) && !supports_enforced_foreign_keys?
    raise ArgumentError, "NOT ENFORCED foreign key constraints require PostgreSQL 18.4+ (got #{database_version})"
  end

  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", nulls_not_distinct: true

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.

:nulls_not_distinct

Create a unique constraint where NULLs are treated equally. Note: only supported by PostgreSQL version 15.0.0 and greater.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 784
def add_unique_constraint(table_name, column_name = nil, **options)
  options = unique_constraint_options(table_name, column_name, options)
  at = build_alter_table_definition(table_name)
  at.add_unique_constraint(column_name, options)

  execute_alter_table(at)
end

change_foreign_key(from_table, to_table = nil, **options)

Changes an existing foreign key constraint on a table.

The enforced option toggles whether PostgreSQL checks referential integrity during DML. Requires PostgreSQL 18.4+.

Like validate_foreign_key, this is a runtime helper rather than a migration command: it is not registered as reversible, so use it from application code or explicit up/down methods. Accepted options are :enforced plus identifying keys (:column, :name, :to_table).

Changes the foreign key on accounts.branch_id to NOT ENFORCED.

change_foreign_key :accounts, :branches, enforced: false

Changes the foreign key on accounts.branch_id back to ENFORCED.

change_foreign_key :accounts, :branches, enforced: true

Changes the foreign key on accounts.owner_id.

change_foreign_key :accounts, column: :owner_id, enforced: false

Changes the foreign key named special_fk_name on the accounts table.

change_foreign_key :accounts, name: :special_fk_name, enforced: false

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 946
def change_foreign_key(from_table, to_table = nil, **options)
  unless supports_enforced_foreign_keys?
    raise ArgumentError, "change_foreign_key requires PostgreSQL 18.4+ (got #{database_version})"
  end

  unless options.key?(:enforced)
    raise ArgumentError, "change_foreign_key requires at least one option (e.g. enforced:)"
  end

  enforced = options[:enforced]
  fk_name = foreign_key_for!(from_table, to_table: to_table, **options.except(:enforced)).name

  execute "ALTER TABLE #{quote_table_name(from_table)} ALTER CONSTRAINT #{quote_column_name(fk_name)} #{enforced ? 'ENFORCED' : 'NOT ENFORCED'}"
end

client_min_messages()

Returns the current client message level.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 262
def client_min_messages
  query_value("SHOW client_min_messages")
end

client_min_messages=(level)

Set the client message level.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 267
def client_min_messages=(level)
  query_command("SET client_min_messages TO '#{level}'", "SCHEMA")
end

collation()

Returns the current database collation.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 193
def collation
  query_value("SELECT datcollate FROM pg_database WHERE datname = current_database()")
end

create_database(name, options = {})

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

Example:

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

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 23
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 :locale_provider
              " LOCALE_PROVIDER = '#{value}'"
            when :locale
              " LOCALE = '#{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, force: nil, if_not_exists: nil)

Creates a schema for the given schema name.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 214
def create_schema(schema_name, force: nil, if_not_exists: nil)
  if force && if_not_exists
    raise ArgumentError, "Options `:force` and `:if_not_exists` cannot be used simultaneously."
  end

  if force
    drop_schema(schema_name, if_exists: true)
  end

  execute("CREATE SCHEMA#{' IF NOT EXISTS' if if_not_exists} #{quote_schema_name(schema_name)}")
end

ctype()

Returns the current database ctype.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 198
def ctype
  query_value("SELECT datctype FROM pg_database WHERE datname = current_database()")
end

current_database()

Returns the current database name.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 170
def current_database
  query_value("SELECT current_database()")
end

current_schema()

Returns the current schema name.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 175
def current_schema
  query_value("SELECT current_schema")
end

drop_database(name)

Drops a PostgreSQL database.

Example:

drop_database 'matt_development'

Note, for PostgreSQL versions >= 13 the SQL statement will include WITH (FORCE) to disconnect clients before dropping the database. This allows you to drop/reset the database without stopping the Rails server etc. See: www.postgresql.org/docs/current/sql-dropdatabase.html

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 63
def drop_database(name)
  statement = "DROP DATABASE IF EXISTS #{quote_table_name(name)}"
  statement += " WITH (FORCE)" if supports_force_drop_database?
  execute statement
end

drop_schema(schema_name, **options)

Drops the schema for the given schema name.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 227
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.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 188
def encoding
  query_value("SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = current_database()")
end

exclusion_constraint_exists?(table_name, **options)

Checks to see if an exclusion constraint exists on a table for a given exclusion constraint definition.

exclusion_constraint_exists?(:invoices, name: "invoices_date_overlap")

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 755
def exclusion_constraint_exists?(table_name, **options)
  if !options.key?(:name) && !options.key?(:expression)
    raise ArgumentError, "At least one of :name or :expression must be supplied"
  end
  exclusion_constraint_for(table_name, **options).present?
end

exclusion_constraints(table_name)

Returns an array of exclusion constraints for the given table, or a Hash of them keyed by table name when given an Array of tables. The exclusion constraints are represented as ExclusionConstraintDefinition objects.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 691
def exclusion_constraints(table_name)
  result = fetch_exclusion_constraints(Array(table_name).map(&:to_s))
  table_name.is_a?(Array) ? result : result[table_name.to_s]
end

foreign_keys(table_name)

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 670
def foreign_keys(table_name)
  result = fetch_foreign_keys(Array(table_name).map(&:to_s))
  table_name.is_a?(Array) ? result : result[table_name.to_s]
end

foreign_table_exists?(table_name)

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 679
def foreign_table_exists?(table_name)
  query_values(data_source_sql(table_name, type: "FOREIGN TABLE")).any? if table_name.present?
end

foreign_tables()

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 675
def foreign_tables
  query_values(data_source_sql(type: "FOREIGN TABLE"))
end

index_name_exists?(table_name, index_name)

Verifies existence of an index with a given name.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 80
        def index_name_exists?(table_name, index_name)
          table = quoted_scope(table_name)
          index = quoted_scope(index_name)

          query_value(<<~SQL).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.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 745
def remove_exclusion_constraint(table_name, expression = nil, **options)
  excl_name_to_delete = exclusion_constraint_for!(table_name, expression: expression, **options).name

  remove_constraint(table_name, excl_name_to_delete)
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.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 811
def remove_unique_constraint(table_name, column_name = nil, **options)
  unique_name_to_delete = unique_constraint_for!(table_name, column: column_name, **options).name

  remove_constraint(table_name, unique_name_to_delete)
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.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 648
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_schema(schema_name, new_name)

Renames the schema for the given schema name.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 232
def rename_schema(schema_name, new_name)
  execute "ALTER SCHEMA #{quote_schema_name(schema_name)} RENAME TO #{quote_schema_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')

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 534
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)

Returns true if schema exists.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 75
def schema_exists?(name)
  query_value("SELECT COUNT(*) FROM pg_namespace WHERE nspname = #{quote(name)}").to_i > 0
end

schema_names()

Returns an array of schema names.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 203
        def schema_names
          query_values(<<~SQL)
            SELECT nspname
              FROM pg_namespace
             WHERE nspname !~ '^pg_.*'
               AND nspname NOT IN ('information_schema')
             ORDER by nspname;
          SQL
        end

schema_search_path()

Returns the active schema search path.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 255
def schema_search_path
  @schema_search_path ||=
    with_raw_connection { |conn| conn.parameter_status("search_path") } ||
    query_value("SHOW search_path")
end

schema_search_path=(schema_csv)

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.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 241
def schema_search_path=(schema_csv)
  return if schema_csv == @schema_search_path
  if schema_csv
    # Check parameter_status to skip redundant SET when the server
    # already has the desired search_path (e.g. on initial connection).
    current = with_raw_connection(materialize_transactions: false) { |conn| conn.parameter_status("search_path") }
    unless current == schema_csv
      query_command("SET search_path TO #{schema_csv}", "SCHEMA")
    end
    @schema_search_path = schema_csv
  end
end

serial_sequence(table, column)

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 282
def serial_sequence(table, column)
  query_value("SELECT pg_get_serial_sequence(#{quote(table)}, #{quote(column)})")
end

unique_constraint_exists?(table_name, **options)

Checks to see if a unique constraint exists on a table for a given unique constraint definition.

unique_constraint_exists?(:sections, name: "unique_position")

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 821
def unique_constraint_exists?(table_name, **options)
  if !options.key?(:name) && !options.key?(:column)
    raise ArgumentError, "At least one of :name or :column must be supplied"
  end
  unique_constraint_for(table_name, **options).present?
end

unique_constraints(table_name)

Returns an array of unique constraints for the given table, or a Hash of them keyed by table name when given an Array of tables. The unique constraints are represented as UniqueConstraintDefinition objects.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 699
def unique_constraints(table_name)
  result = fetch_unique_constraints(Array(table_name).map(&:to_s))
  table_name.is_a?(Array) ? result : result[table_name.to_s]
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.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 966
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

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 892
def validate_constraint(table_name, constraint_name)
  at = build_alter_table_definition table_name
  at.validate_constraint constraint_name

  execute_alter_table(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 SchemaStatements#add_foreign_key.

Source: show | on GitHub 

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 914
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