edge badge
Methods
A
C
D
E
I
P
R
S
T
Instance Public methods
add_column(table_name, column_name, type, options = {})

Adds a new column to the named table. See ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::TableDefinition#column for details of the options you can use.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 387
def add_column(table_name, column_name, type, options = {})
  clear_cache!
  super
end
change_column(table_name, column_name, type, options = {})

Changes the column of a table.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 393
def change_column(table_name, column_name, type, options = {})
  clear_cache!
  quoted_table_name = quote_table_name(table_name)
  sql_type = type_to_sql(type, options[:limit], options[:precision], options[:scale])
  sql_type << "[]" if options[:array]
  execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{sql_type}"

  change_column_default(table_name, column_name, options[:default]) if options_include_default?(options)
  change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null)
end
change_column_default(table_name, column_name, default)

Changes the default value of a table column.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 405
def change_column_default(table_name, column_name, default)
  clear_cache!
  column = column_for(table_name, column_name)

  execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote_default_value(default, column)}" if column
end
change_column_null(table_name, column_name, null, default = nil)
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 412
def change_column_null(table_name, column_name, null, default = nil)
  clear_cache!
  unless null || default.nil?
    column = column_for(table_name, column_name)
    execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote_default_value(default, column)} WHERE #{quote_column_name(column_name)} IS NULL") if column
  end
  execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL")
end
client_min_messages()

Returns the current client message level.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 261
def client_min_messages
  query('SHOW client_min_messages', 'SCHEMA')[0][0]
end
client_min_messages=(level)

Set the client message level.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 266
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 209
        def collation
          query("            SELECT pg_database.datcollate FROM pg_database WHERE pg_database.datname LIKE '#{current_database}'
", 'SCHEMA')[0][0]
        end
columns(table_name)

Returns the list of all column definitions for a table.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 182
def columns(table_name)
  # Limit, precision, and scale are all handled by the superclass.
  column_definitions(table_name).map do |column_name, type, default, notnull, oid, fmod|
    oid = get_oid_type(oid.to_i, fmod.to_i, column_name)
    PostgreSQLColumn.new(column_name, default, oid, type, notnull == 'f')
  end
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 56
def create_database(name, options = {})
  options = { encoding: 'utf8' }.merge!(options.symbolize_keys)

  option_string = options.sum do |key, value|
    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 234
def create_schema schema_name
  execute "CREATE SCHEMA #{schema_name}"
end
ctype()

Returns the current database ctype.

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

Returns the current database name.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 191
def current_database
  query('select current_database()', 'SCHEMA')[0][0]
end
current_schema()

Returns the current schema name.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 196
def current_schema
  query('SELECT current_schema', 'SCHEMA')[0][0]
end
drop_schema(schema_name)

Drops the schema for the given schema name.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 239
def drop_schema schema_name
  execute "DROP SCHEMA #{schema_name} CASCADE"
end
encoding()

Returns the current database encoding format.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 201
        def encoding
          query("            SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database
            WHERE pg_database.datname LIKE '#{current_database}'
", 'SCHEMA')[0][0]
        end
index_name_exists?(table_name, index_name, default)
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 126
        def index_name_exists?(table_name, index_name, default)
          exec_query("            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
            WHERE i.relkind = 'i'
              AND i.relname = '#{index_name}'
              AND t.relname = '#{table_name}'
              AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) )
", 'SCHEMA').rows.first[0].to_i > 0
        end
index_name_length()
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 441
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 140
        def indexes(table_name, name = nil)
           result = query("             SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid
             FROM pg_class t
             INNER JOIN pg_index d ON t.oid = d.indrelid
             INNER JOIN pg_class i ON d.indexrelid = i.oid
             WHERE i.relkind = 'i'
               AND d.indisprimary = 'f'
               AND t.relname = '#{table_name}'
               AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) )
            ORDER BY i.relname
", 'SCHEMA')

          result.map do |row|
            index_name = row[0]
            unique = row[1] == 't'
            indkey = row[2].split(" ")
            inddef = row[3]
            oid = row[4]

            columns = Hash[query("            SELECT a.attnum, a.attname
            FROM pg_attribute a
            WHERE a.attrelid = #{oid}
            AND a.attnum IN (#{indkey.join(",")})
", "SCHEMA")]

            column_names = columns.values_at(*indkey).compact

            unless column_names.empty?
              # add info on sort order for columns (only desc order is explicitly specified, asc is the default)
              desc_order_columns = inddef.scan(/(\w+) DESC/).flatten
              orders = desc_order_columns.any? ? Hash[desc_order_columns.map {|order_column| [order_column, :desc]}] : {}
              where = inddef.scan(/WHERE (.+)$/).flatten[0]
              using = inddef.scan(/USING (.+?) /).flatten[0].to_sym

              IndexDefinition.new(table_name, index_name, unique, column_names, [], orders, where, nil, using)
            end
          end.compact
        end
primary_key(table)

Returns just a table's primary key

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 355
        def primary_key(table)
          row = exec_query("            SELECT attr.attname
            FROM pg_attribute attr
            INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1]
            WHERE cons.contype = 'p'
              AND cons.conrelid = '#{quote_table_name(table)}'::regclass
", 'SCHEMA').rows.first

          row && row.first
        end
rename_column(table_name, column_name, new_column_name)

Renames a column in a table.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 422
def rename_column(table_name, column_name, new_column_name)
  clear_cache!
  execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}"
  rename_column_indexes(table_name, column_name, new_column_name)
end
rename_index(table_name, old_name, new_name)
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 437
def rename_index(table_name, old_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 matches the Active Record default.

Example:

rename_table('octopuses', 'octopi')
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 373
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 == "#{table_name}_#{pk}_seq"
    new_seq = "#{new_name}_#{pk}_seq"
    execute "ALTER TABLE #{quote_table_name(seq)} RENAME TO #{quote_table_name(new_seq)}"
  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 118
        def schema_exists?(name)
          exec_query("            SELECT COUNT(*)
            FROM pg_namespace
            WHERE nspname = '#{name}'
", 'SCHEMA').rows.first[0].to_i > 0
        end
schema_names()

Returns an array of schema names.

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

Returns the active schema search path.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 256
def schema_search_path
  @schema_search_path ||= query('SHOW search_path', 'SCHEMA')[0][0]
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 248
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 279
        def serial_sequence(table, column)
          result = exec_query("            SELECT pg_get_serial_sequence('#{table}', '#{column}')
", 'SCHEMA')
          result.rows.first.first
        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 103
        def table_exists?(name)
          schema, table = Utils.extract_schema_and_table(name.to_s)
          return false unless table

          exec_query("              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 = '#{table.gsub(/(^"|"$)/,'')}'
              AND n.nspname = #{schema ? "'#{schema}'" : 'ANY (current_schemas(false))'}
", 'SCHEMA').rows.first[0].to_i > 0
        end
tables(name = nil)

Returns the list of all tables in the schema search path or a specified schema.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 92
        def tables(name = nil)
          query("            SELECT tablename
            FROM pg_tables
            WHERE schemaname = ANY (current_schemas(false))
", 'SCHEMA').map { |row| row[0] }
        end
type_to_sql(type, limit = nil, precision = nil, scale = nil)

Maps logical Rails types to PostgreSQL-specific data types.

# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 446
def type_to_sql(type, limit = nil, precision = nil, scale = nil)
  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'
    return 'integer' unless limit

    case limit
      when 1, 2; 'smallint'
      when 3, 4; 'integer'
      when 5..8; 'bigint'
      else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.")
    end
  when 'datetime'
    return super unless precision

    case precision
      when 0..6; "timestamp(#{precision})"
      else raise(ActiveRecordError, "No timestamp type has precision of #{precision}. The allowed range of precision is from 0 to 6")
    end
  else
    super
  end
end