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.

Methods
A
B
C
D
E
I
N
O
P
Q
R
S
T
U
Classes and Modules
Constants
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.

Class Public methods
new(connection, logger, connection_parameters, config)

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
Instance Public methods
active?()

Is this connection alive and ready for queries?

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 282
def active?
  @connection.status == PGconn::CONNECTION_OK
rescue PGError
  false
end
adapter_name()

Returns ‘PostgreSQL’ as adapter name for identification purposes.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 240
def adapter_name
  ADAPTER_NAME
end
add_column(table_name, column_name, type, options = {})

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
begin_db_transaction()

Begins a transaction.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 589
def begin_db_transaction
  execute "BEGIN"
end
change_column(table_name, column_name, type, options = {})

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
change_column_default(table_name, column_name, default)

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
change_column_null(table_name, column_name, null, default = nil)
# 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
clear_cache!()

Clears the prepared statements cache.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 274
def clear_cache!
  @statements.each_value do |value|
    @connection.query "DEALLOCATE #{value}"
  end
  @statements.clear
end
client_min_messages()

Returns the current client message level.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 776
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_adapter.rb, line 781
def client_min_messages=(level)
  execute("SET client_min_messages TO '#{level}'", 'SCHEMA')
end
columns(table_name, name = nil)

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
commit_db_transaction()

Commits a transaction.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 594
def commit_db_transaction
  execute "COMMIT"
end
create_database(name, options = {})

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
create_savepoint()
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 607
def create_savepoint
  execute("SAVEPOINT #{current_savepoint_name}")
end
current_database()

Returns the current database name.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 746
def current_database
  query('select current_database()')[0][0]
end
disconnect!()

Disconnects from the database if already connected. Otherwise, this method does nothing.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 302
def disconnect!
  clear_cache!
  @connection.close rescue nil
end
encoding()

Returns the current database encoding format.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 751
def encoding
  query(          SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database          WHERE pg_database.datname LIKE '#{current_database}')[0][0]
end
escape_bytea(value)

Escapes binary strings for bytea input to the database.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 350
def escape_bytea(value)
  @connection.escape_bytea(value) if value
end
exec_delete(sql, name = 'SQL', binds = [])
This method is also aliased as exec_update
# 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
exec_query(sql, name = 'SQL', binds = [])
# 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
exec_update(sql, name = 'SQL', binds = [])
execute(sql, name = nil)

Executes an SQL statement, returning a PGresult object on success or raising a PGError exception otherwise.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 539
def execute(sql, name = nil)
  log(sql, name) do
    @connection.async_exec(sql)
  end
end
extract_schema_and_table(name)

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
index_name_length()
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 908
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_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
insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)

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
outside_transaction?()
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 603
def outside_transaction?
  @connection.transaction_status == PGconn::PQTRANS_IDLE
end
primary_key(table)

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
quote_table_name(name)

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
reconnect!()

Close then reopen the connection.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 289
def reconnect!
  clear_cache!
  @connection.reset
  configure_connection
end
release_savepoint()
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 615
def release_savepoint
  execute("RELEASE SAVEPOINT #{current_savepoint_name}")
end
rename_column(table_name, column_name, new_column_name)

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
rename_index(table_name, old_name, new_name)
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 904
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(name, new_name)

Renames a table.

Example:

rename_table('octopuses', 'octopi')
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 860
def rename_table(name, new_name)
  execute "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}"
end
reset!()
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 295
def reset!
  clear_cache!
  super
end
rollback_db_transaction()

Aborts a transaction.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 599
def rollback_db_transaction
  execute "ROLLBACK"
end
rollback_to_savepoint()
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 611
def rollback_to_savepoint
  execute("ROLLBACK TO SAVEPOINT #{current_savepoint_name}")
end
schema_search_path()

Returns the active schema search path.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 771
def schema_search_path
  @schema_search_path ||= query('SHOW search_path')[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_adapter.rb, line 763
def schema_search_path=(schema_csv)
  if schema_csv
    execute "SET search_path TO #{schema_csv}"
    @schema_search_path = schema_csv
  end
end
select_rows(sql, name = nil)

Executes a SELECT query and returns an array of rows. Each row is an array of field values.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 468
def select_rows(sql, name = nil)
  select_raw(sql, name).last
end
serial_sequence(table, column)
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 792
def serial_sequence(table, column)
  result = exec_query(          SELECT pg_get_serial_sequence($1, $2), 'SCHEMA', [[nil, table], [nil, column]])
  result.rows.first.first
end
session_auth=(user)

Set the authorized user for this session

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 442
def session_auth=(user)
  clear_cache!
  exec_query "SET SESSION AUTHORIZATION #{user}"
end
set_standard_conforming_strings()

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
sql_for_insert(sql, pk, id_value, sequence_name, binds)
# 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
substitute_at(column, index)
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 545
def substitute_at(column, index)
  Arel.sql("$#{index + 1}")
end
supports_ddl_transactions?()
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 333
def supports_ddl_transactions?
  true
end
supports_insert_with_returning?()
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 329
def supports_insert_with_returning?
  true
end
supports_migrations?()

Returns true, since this connection adapter supports migrations.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 312
def supports_migrations?
  true
end
supports_savepoints?()

Returns true, since this connection adapter supports savepoints.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 338
def supports_savepoints?
  true
end
supports_statement_cache?()

Returns true, since this connection adapter supports prepared statement caching.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 246
def supports_statement_cache?
  true
end
table_alias_length()

Returns the configured supported identifier length supported by PostgreSQL

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 343
def table_alias_length
  @table_alias_length ||= query('SHOW max_identifier_length')[0][0].to_i
end
table_exists?(name)
# 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
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_adapter.rb, line 665
def tables(name = nil)
  query(          SELECT tablename          FROM pg_tables          WHERE schemaname = ANY (current_schemas(false)), 'SCHEMA').map { |row| row[0] }
end
type_cast(value, column)
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 390
def type_cast(value, column)
  return super unless column

  case value
  when String
    return super unless 'bytea' == column.sql_type
    { :value => value, :format => 1 }
  else
    super
  end
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_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
unescape_bytea(value)

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.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 357
def unescape_bytea(value)
  @connection.unescape_bytea(value) if value
end
update_sql(sql, name = nil)

Executes an UPDATE query and returns the number of affected tuples.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 584
def update_sql(sql, name = nil)
  super.cmd_tuples
end
Instance Protected methods
postgresql_version()

Returns the version of the connected PostgreSQL server.

# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 945
def postgresql_version
  @connection.server_version
end
translate_exception(exception, message)
# 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