module ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements

Public Instance Methods

change_column(table_name, column_name, type, options = {}) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 438
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]
  sql = "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{sql_type}"
  sql << " USING #{options[:using]}" if options[:using]
  if options[:cast_as]
    sql << " USING CAST(#{quote_column_name(column_name)} AS #{type_to_sql(options[:cast_as], options[:limit], options[:precision], options[:scale])})"
  end
  execute sql

  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

Changes the column of a table.

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

  alter_column_query = "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} %s"
  if default.nil?
    # <tt>DEFAULT NULL</tt> results in the same behavior as <tt>DROP DEFAULT</tt>. However, PostgreSQL will
    # cast the default to the columns type, which leaves us with a default like "default NULL::character varying".
    execute alter_column_query % "DROP DEFAULT"
  else
    execute alter_column_query % "SET DEFAULT #{quote_default_value(default, column)}"
  end
end

Changes the default value of a table column.

change_column_null(table_name, column_name, null, default = nil) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 470
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() Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 280
def client_min_messages
  query('SHOW client_min_messages', 'SCHEMA')[0][0]
end

Returns the current client message level.

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

Set the client message level.

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

Returns the current database collation.

columns(table_name) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 195
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, type)
    default_value = extract_value_from_default(oid, default)
    default_function = extract_default_function(default_value, default)
    new_column(column_name, default_value, oid, type, notnull == 'f', default_function)
  end
end

Returns the list of all column definitions for a table.

create_database(name, options = {}) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 54
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 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'
create_schema(schema_name) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 253
def create_schema schema_name
  execute "CREATE SCHEMA #{schema_name}"
end

Creates a schema for the given schema name.

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

Returns the current database ctype.

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

Returns the current database name.

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

Returns the current schema name.

data_source_exists?(name)
Alias for: table_exists?
data_sources() Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 98
        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) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 258
def drop_schema schema_name
  execute "DROP SCHEMA #{schema_name} CASCADE"
end

Drops the schema for the given schema name.

drop_table(table_name, options = {}) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 126
def drop_table(table_name, options = {})
  execute "DROP TABLE #{quote_table_name(table_name)}#{' CASCADE' if options[:force] == :cascade}"
end
encoding() Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 220
        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

Returns the current database encoding format.

foreign_keys(table_name) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 501
        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

          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) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 139
        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() Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 538
def index_name_length
  63
end
indexes(table_name, name = nil) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 153
        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

Returns an array of indexes for the given table.

primary_key(table) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 399
        def primary_key(table)
          pks = exec_query("            SELECT attr.attname
            FROM pg_attribute attr
            INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = any(cons.conkey)
            WHERE cons.contype = 'p'
              AND cons.conrelid = '#{quote_table_name(table)}'::regclass
", 'SCHEMA').rows
          return nil unless pks.count == 1
          pks[0][0]
        end

Returns just a table's primary key

rename_index(table_name, old_name, new_name) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 495
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) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 417
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 #{quote_table_name(seq)} 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

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')
schema_exists?(name) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 131
        def schema_exists?(name)
          exec_query("            SELECT COUNT(*)
            FROM pg_namespace
            WHERE nspname = '#{name}'
", 'SCHEMA').rows.first[0].to_i > 0
        end

Returns true if schema exists.

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

Returns an array of schema names.

schema_search_path() Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 275
def schema_search_path
  @schema_search_path ||= query('SHOW search_path', 'SCHEMA')[0][0]
end

Returns the active schema search path.

schema_search_path=(schema_csv) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 267
def schema_search_path=(schema_csv)
  if schema_csv
    execute("SET search_path TO #{schema_csv}", 'SCHEMA')
    @schema_search_path = schema_csv
  end
end

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.

serial_sequence(table, column) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 298
        def serial_sequence(table, column)
          result = exec_query("            SELECT pg_get_serial_sequence('#{table}', '#{column}')
", 'SCHEMA')
          result.rows.first.first
        end
table_exists?(name) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 111
        def table_exists?(name)
          name = Utils.extract_schema_qualified_name(name.to_s)
          return false unless name.identifier

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

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)

Also aliased as: data_source_exists?
tables(name = nil) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 90
        def tables(name = nil)
          query("            SELECT tablename
            FROM pg_tables
            WHERE schemaname = ANY (current_schemas(false))
", 'SCHEMA').map { |row| row[0] }
        end

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

type_to_sql(type, limit = nil, precision = nil, scale = nil) Show source
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 543
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 scale 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

Maps logical Rails types to PostgreSQL-specific data types.

Calls superclass method

© 2004–2018 David Heinemeier Hansson
Licensed under the MIT License.