The PostgreSQL adapter works both with the native C (ruby.scripting.ca/postgres/) and the pure Ruby (available both as gem and from rubyforge.org/frs/?group_id=234&release_id=1944) drivers.
Options:
-
:host - Defaults to “localhost”.
-
:port - Defaults to 5432.
-
:username - Defaults to nothing.
-
:password - Defaults to nothing.
-
:database - The name of the database. No default, must be provided.
-
:schema_search_path - An optional schema search path for the connection given as a string of comma-separated schema names. This is backward-compatible with the :schema_order option.
-
:encoding - An optional client encoding that is used in a SET client_encoding TO <encoding> call on the connection.
-
:min_messages - An optional client min messages that is used in a SET client_min_messages TO <min_messages> call on the connection.
- A
- B
- C
- D
- E
- I
- N
- O
- P
- Q
- R
- S
- T
- U
ADAPTER_NAME | = | 'PostgreSQL' |
NATIVE_DATABASE_TYPES | = | { :primary_key => "serial primary key", :string => { :name => "character varying", :limit => 255 }, :text => { :name => "text" }, :integer => { :name => "integer" }, :float => { :name => "float" }, :decimal => { :name => "decimal" }, :datetime => { :name => "timestamp" }, :timestamp => { :name => "timestamp" }, :time => { :name => "time" }, :date => { :name => "date" }, :binary => { :name => "bytea" }, :boolean => { :name => "boolean" }, :xml => { :name => "xml" }, :tsvector => { :name => "tsvector" } } |
MONEY_COLUMN_TYPE_OID | = | 790 |
The internal PostgreSQL identifier of the money data type. |
||
BYTEA_COLUMN_TYPE_OID | = | 17 |
The internal PostgreSQL identifier of the BYTEA data type. |
Initializes and connects a PostgreSQL adapter.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 251 def initialize(connection, logger, connection_parameters, config) super(connection, logger) @connection_parameters, @config = connection_parameters, config # @local_tz is initialized as nil to avoid warnings when connect tries to use it @local_tz = nil @table_alias_length = nil @statements = {} connect if postgresql_version < 80200 raise "Your version of PostgreSQL (#{postgresql_version}) is too old, please upgrade!" end @local_tz = execute('SHOW TIME ZONE', 'SCHEMA').first["TimeZone"] end
Is this connection alive and ready for queries?
Returns ‘PostgreSQL’ as adapter name for identification purposes.
Adds a new column to the named table. See TableDefinition#column for details of the options you can use.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 866 def add_column(table_name, column_name, type, options = {}) add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" add_column_options!(add_column_sql, options) execute add_column_sql end
Begins a transaction.
Changes the column of a table.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 874 def change_column(table_name, column_name, type, options = {}) quoted_table_name = quote_table_name(table_name) execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" 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_adapter.rb, line 884 def change_column_default(table_name, column_name, default) execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}" end
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 888 def change_column_null(table_name, column_name, null, default = nil) unless null || default.nil? execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL") end execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL") end
Clears the prepared statements cache.
Returns the current client message level.
Set the client message level.
Returns the list of all column definitions for a table.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 738 def columns(table_name, name = nil) # Limit, precision, and scale are all handled by the superclass. column_definitions(table_name).collect do |column_name, type, default, notnull| PostgreSQLColumn.new(column_name, default, type, notnull == 'f') end end
Commits a transaction.
Create a new PostgreSQL database. Options include :owner, :template, :encoding, :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_adapter.rb, line 633 def create_database(name, options = {}) options = options.reverse_merge(:encoding => "utf8") option_string = options.symbolize_keys.sum do |key, value| case key when :owner " OWNER = \"#{value}\"" when :template " TEMPLATE = \"#{value}\"" when :encoding " ENCODING = '#{value}'" when :tablespace " TABLESPACE = \"#{value}\"" when :connection_limit " CONNECTION LIMIT = #{value}" else "" end end execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}" end
Returns the current database name.
Disconnects from the database if already connected. Otherwise, this method does nothing.
Returns the current database encoding format.
Escapes binary strings for bytea input to the database.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 560 def exec_delete(sql, name = 'SQL', binds = []) log(sql, name, binds) do result = binds.empty? ? exec_no_cache(sql, binds) : exec_cache(sql, binds) affected = result.cmd_tuples result.clear affected end end
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 549 def exec_query(sql, name = 'SQL', binds = []) log(sql, name, binds) do result = binds.empty? ? exec_no_cache(sql, binds) : exec_cache(sql, binds) ret = ActiveRecord::Result.new(result.fields, result_as_array(result)) result.clear return ret end end
Executes an SQL statement, returning a PGresult object on success or raising a PGError exception otherwise.
Extracts the table and schema name from name
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 688 def extract_schema_and_table(name) schema, table = name.split('.', 2) unless table # A table was provided without a schema table = schema schema = nil end if name =~ /^"/ # Handle quoted table names table = name schema = nil end [schema, table] end
Returns an array of indexes for the given table.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 704 def indexes(table_name, name = nil) schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',') result = query( SELECT distinct i.relname, d.indisunique, d.indkey, 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 IN (#{schemas}) ) ORDER BY i.relname, name) result.map do |row| index_name = row[0] unique = row[1] == 't' indkey = row[2].split(" ") oid = row[3] columns = Hash[query( SELECT a.attnum, a.attname FROM pg_attribute a WHERE a.attrelid = #{oid} AND a.attnum IN (#{indkey.join(",")}), "Columns for index #{row[0]} on #{table_name}")] column_names = columns.values_at(*indkey).compact column_names.empty? ? nil : IndexDefinition.new(table_name, index_name, unique, column_names) end.compact end
Executes an INSERT query and returns the new record’s ID
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 473 def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) # Extract the table from the insert sql. Yuck. _, table = extract_schema_and_table(sql.split(" ", 4)[2]) pk ||= primary_key(table) if pk select_value("#{sql} RETURNING #{quote_column_name(pk)}") else super end end
Returns just a table’s primary key
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 843 def primary_key(table) row = exec_query( SELECT DISTINCT(attr.attname) FROM pg_attribute attr INNER JOIN pg_depend dep ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1] WHERE cons.contype = 'p' AND dep.refobjid = $1::regclass, 'SCHEMA', [[nil, table]]).rows.first row && row.first end
Checks the following cases:
-
table_name
-
“table.name”
-
schema_name.table_name
-
schema_name.“table.name”
-
“schema.name”.table_name
-
“schema.name”.“table.name”
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 415 def quote_table_name(name) schema, name_part = extract_pg_identifier_from_name(name.to_s) unless name_part quote_column_name(schema) else table_name, name_part = extract_pg_identifier_from_name(name_part) "#{quote_column_name(schema)}.#{quote_column_name(table_name)}" end end
Close then reopen the connection.
Renames a column in a table.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 896 def rename_column(table_name, column_name, new_column_name) execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}" end
Renames a table.
Example:
rename_table('octopuses', 'octopi')
Aborts a transaction.
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.
Executes a SELECT query and returns an array of rows. Each row is an array of field values.
Set the authorized user for this session
Enable standard-conforming strings if available.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 322 def set_standard_conforming_strings old, self.client_min_messages = client_min_messages, 'panic' execute('SET standard_conforming_strings = on', 'SCHEMA') rescue nil ensure self.client_min_messages = old end
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 571 def sql_for_insert(sql, pk, id_value, sequence_name, binds) unless pk _, table = extract_schema_and_table(sql.split(" ", 4)[2]) pk = primary_key(table) end sql = "#{sql} RETURNING #{quote_column_name(pk)}" if pk [sql, binds] end
Returns true, since this connection adapter supports migrations.
Returns true, since this connection adapter supports savepoints.
Returns true, since this connection adapter supports prepared statement caching.
Returns the configured supported identifier length supported by PostgreSQL
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 673 def table_exists?(name) schema, table = extract_schema_and_table(name.to_s) binds = [[nil, table.gsub(/(^"|"$)/,'')]] binds << [nil, schema] if schema exec_query( SELECT COUNT(*) FROM pg_tables WHERE tablename = $1 #{schema ? "AND schemaname = $2" : ''}, 'SCHEMA', binds).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_adapter.rb, line 913 def type_to_sql(type, limit = nil, precision = nil, scale = nil) return super unless type.to_s == '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 end
Unescapes bytea output from a database to the binary string it represents. NOTE: This is NOT an inverse of escape_bytea! This is only to be used
on escaped binary output from database drive.
Executes an UPDATE query and returns the number of affected tuples.
Returns the version of the connected PostgreSQL server.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 949 def translate_exception(exception, message) case exception.message when /duplicate key value violates unique constraint/ RecordNotUnique.new(message, exception) when /violates foreign key constraint/ InvalidForeignKey.new(message, exception) else super end end