- A
- C
- D
- E
- F
- I
- P
- R
- S
- T
Adds a new column to the named table. See ActiveRecord::ConnectionAdapters::TableDefinition#column for details of the options you can use.
Changes the column of a table.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 429 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 default value of a table column.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 446 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
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 461 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
Returns the current client message level.
Set the client message level.
Returns the current database collation.
Returns the list of all column definitions for a table.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 184 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
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 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
Creates a schema for the given schema name.
Returns the current database ctype.
Returns the current database name.
Returns the current schema name.
Drops the schema for the given schema name.
Returns the current database encoding format.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 493 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
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 128 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
Returns an array of indexes for the given table.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 142 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 just a table's primary key
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 388 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
Renames a column in a table.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 471 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
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 486 def rename_index(table_name, old_name, new_name) if new_name.length > allowed_index_name_length raise ArgumentError, "Index name '#{new_name}' on table '#{table_name}' is too long; the limit is #{allowed_index_name_length} characters" end execute "ALTER INDEX #{quote_column_name(old_name)} RENAME TO #{quote_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')
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 406 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
Returns true if schema exists.
Returns an array of schema names.
Returns the active schema search path.
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.
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 101 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 the list of all tables in the schema search path or a specified schema.
Maps logical Rails types to PostgreSQL-specific data types.
# File activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb, line 535 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