Methods
C
D
E
F
I
R
S
T
Instance Public methods
client_min_messages()

Returns the current client message level.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 326
def client_min_messages
  select_value('SHOW client_min_messages', 'SCHEMA')
end
client_min_messages=(level)

Set the client message level.

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

Returns the current database collation.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 278
def collation
  select_value("SELECT datcollate FROM pg_database WHERE datname LIKE '#{current_database}'", 'SCHEMA')
end
create_database(name, options = {})

Create a new PostgreSQL database. Options include :owner, :template, :encoding (defaults to utf8), :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'
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 38
def create_database(name, options = {})
  options = { encoding: 'utf8' }.merge!(options.symbolize_keys)

  option_string = options.inject("") do |memo, (key, value)|
    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.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 299
def create_schema schema_name
  execute "CREATE SCHEMA #{quote_schema_name(schema_name)}"
end
ctype()

Returns the current database ctype.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 283
def ctype
  select_value("SELECT datctype FROM pg_database WHERE datname LIKE '#{current_database}'", 'SCHEMA')
end
current_database()

Returns the current database name.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 263
def current_database
  select_value('select current_database()', 'SCHEMA')
end
current_schema()

Returns the current schema name.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 268
def current_schema
  select_value('SELECT current_schema', 'SCHEMA')
end
data_source_exists?(name)
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 107
        def data_source_exists?(name)
          name = Utils.extract_schema_qualified_name(name.to_s)
          return false unless name.identifier

          select_value("              SELECT COUNT(*)
              FROM pg_class c
              LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
              WHERE c.relkind IN ('r','v','m') -- (r)elation/table, (v)iew, (m)aterialized view
              AND c.relname = '#{name.identifier}'
              AND n.nspname = #{name.schema ? "'#{name.schema}'" : 'ANY (current_schemas(false))'}
", 'SCHEMA').to_i > 0
        end
data_sources()
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 84
        def data_sources # :nodoc
          select_values("            SELECT c.relname
            FROM pg_class c
            LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r', 'v','m') -- (r)elation/table, (v)iew, (m)aterialized view
            AND n.nspname = ANY (current_schemas(false))
", 'SCHEMA')
        end
drop_schema(schema_name, options = {})

Drops the schema for the given schema name.

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

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 273
def encoding
  select_value("SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname LIKE '#{current_database}'", 'SCHEMA')
end
fetch_type_metadata(column_name, sql_type, oid, fmod)
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 674
def fetch_type_metadata(column_name, sql_type, oid, fmod)
  cast_type = get_oid_type(oid, fmod, column_name, sql_type)
  simple_type = SqlTypeMetadata.new(
    sql_type: sql_type,
    type: cast_type.type,
    limit: cast_type.limit,
    precision: cast_type.precision,
    scale: cast_type.scale,
  )
  PostgreSQLTypeMetadata.new(simple_type, oid: oid, fmod: fmod)
end
foreign_keys(table_name)
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 587
        def foreign_keys(table_name)
          fk_info = select_all("            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
            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 = #{quote(table_name)}
              AND t3.nspname = ANY (current_schemas(false))
            ORDER BY c.conname
".strip_heredoc, 'SCHEMA')

          fk_info.map do |row|
            options = {
              column: row['column'],
              name: row['name'],
              primary_key: row['primary_key']
            }

            options[:on_delete] = extract_foreign_key_action(row['on_delete'])
            options[:on_update] = extract_foreign_key_action(row['on_update'])

            ForeignKeyDefinition.new(table_name, row['to_table'], options)
          end
        end
index_name_exists?(table_name, index_name, default)

Verifies existence of an index with a given name.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 155
        def index_name_exists?(table_name, index_name, default)
          table = Utils.extract_schema_qualified_name(table_name.to_s)
          index = Utils.extract_schema_qualified_name(index_name.to_s)

          select_value("            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 = i.relnamespace
            WHERE i.relkind = 'i'
              AND i.relname = '#{index.identifier}'
              AND t.relname = '#{table.identifier}'
              AND n.nspname = #{index.schema ? "'#{index.schema}'" : 'ANY (current_schemas(false))'}
", 'SCHEMA').to_i > 0
        end
index_name_length()
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 624
def index_name_length
  63
end
indexes(table_name, name = nil)

Returns an array of indexes for the given table.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 173
        def indexes(table_name, name = nil)
          table = Utils.extract_schema_qualified_name(table_name.to_s)

          result = query("            SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid,
                            pg_catalog.obj_description(i.oid, 'pg_class') AS comment,
            (SELECT COUNT(*) FROM pg_opclass o
               JOIN (SELECT unnest(string_to_array(d.indclass::text, ' '))::int oid) c
                 ON o.oid = c.oid WHERE o.opcdefault = 'f')
            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 = i.relnamespace
            WHERE i.relkind = 'i'
              AND d.indisprimary = 'f'
              AND t.relname = '#{table.identifier}'
              AND n.nspname = #{table.schema ? "'#{table.schema}'" : 'ANY (current_schemas(false))'}
            ORDER BY i.relname
", 'SCHEMA')

          result.map do |row|
            index_name = row[0]
            unique = row[1]
            indkey = row[2].split(" ").map(&:to_i)
            inddef = row[3]
            oid = row[4]
            comment = row[5]
            opclass = row[6]

            using, expressions, where = inddef.scan(/ USING (\w+?) \((.+?)\)(?: WHERE (.+))?\z/).flatten

            if indkey.include?(0) || opclass > 0
              columns = expressions
            else
              columns = Hash[query("                SELECT a.attnum, a.attname
                FROM pg_attribute a
                WHERE a.attrelid = #{oid}
                AND a.attnum IN (#{indkey.join(",")})
".strip_heredoc, "SCHEMA")].values_at(*indkey).compact

              # add info on sort order for columns (only desc order is explicitly specified, asc is the default)
              orders = Hash[
                expressions.scan(/(\w+) DESC/).flatten.map { |order_column| [order_column, :desc] }
              ]
            end

            IndexDefinition.new(table_name, index_name, unique, columns, [], orders, where, nil, using.to_sym, comment.presence)
          end.compact
        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.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 581
def rename_index(table_name, old_name, new_name)
  validate_index_length!(table_name, new_name)

  execute "ALTER INDEX #{quote_column_name(old_name)} RENAME TO #{quote_table_name(new_name)}"
end
rename_table(table_name, new_name)

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')
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 460
def rename_table(table_name, new_name)
  clear_cache!
  execute "ALTER TABLE #{quote_table_name(table_name)} RENAME TO #{quote_table_name(new_name)}"
  pk, seq = pk_and_sequence_for(new_name)
  if seq && seq.identifier == "#{table_name}_#{pk}_seq"
    new_seq = "#{new_name}_#{pk}_seq"
    idx = "#{table_name}_pkey"
    new_idx = "#{new_name}_pkey"
    execute "ALTER TABLE #{seq.quoted} RENAME TO #{quote_table_name(new_seq)}"
    execute "ALTER INDEX #{quote_table_name(idx)} RENAME TO #{quote_table_name(new_idx)}"
  end

  rename_table_indexes(table_name, new_name)
end
schema_exists?(name)

Returns true if schema exists.

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

Returns an array of schema names.

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

Returns the active schema search path.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 321
def schema_search_path
  @schema_search_path ||= select_value('SHOW search_path', 'SCHEMA')
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.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 313
def schema_search_path=(schema_csv)
  if schema_csv
    execute("SET search_path TO #{schema_csv}", 'SCHEMA')
    @schema_search_path = schema_csv
  end
end
serial_sequence(table, column)
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 344
def serial_sequence(table, column)
  select_value("SELECT pg_get_serial_sequence('#{table}', '#{column}')", 'SCHEMA')
end
table_exists?(name)

Returns true if table exists. If the schema is not specified as part of name then it will only find tables within the current schema search path (regardless of permissions to access tables in other schemas)

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 97
        def table_exists?(name)
          ActiveSupport::Deprecation.warn("            #table_exists? currently checks both tables and views.
            This behavior is deprecated and will be changed with Rails 5.1 to only check tables.
            Use #data_source_exists? instead.
".squish)

          data_source_exists?(name)
        end
tables(name = nil)

Returns the list of all tables in the schema search path.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 74
        def tables(name = nil)
          if name
            ActiveSupport::Deprecation.warn("              Passing arguments to #tables is deprecated without replacement.
".squish)
          end

          select_values("SELECT tablename FROM pg_tables WHERE schemaname = ANY(current_schemas(false))", 'SCHEMA')
        end
type_to_sql(type, limit = nil, precision = nil, scale = nil, array = nil)

Maps logical Rails types to PostgreSQL-specific data types.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 629
def type_to_sql(type, limit = nil, precision = nil, scale = nil, array = nil)
  sql = case type.to_s
  when 'binary'
    # PostgreSQL doesn't support limits on binary (bytea) columns.
    # The hard limit is 1GB, because of a 32-bit size field, and TOAST.
    case limit
    when nil, 0..0x3fffffff; super(type)
    else raise(ActiveRecordError, "No binary type has byte size #{limit}.")
    end
  when 'text'
    # PostgreSQL doesn't support limits on text columns.
    # The hard limit is 1GB, according to section 8.3 in the manual.
    case limit
    when nil, 0..0x3fffffff; super(type)
    else raise(ActiveRecordError, "The limit on text can be at most 1GB - 1byte.")
    end
  when 'integer'
    case limit
    when 1, 2; 'smallint'
    when nil, 3, 4; 'integer'
    when 5..8; 'bigint'
    else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with scale 0 instead.")
    end
  else
    super(type, limit, precision, scale)
  end

  sql << '[]' if array && type != :primary_key
  sql
end