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
-
- schema_exists?,
- schema_search_path,
- schema_search_path=,
- select_rows,
- serial_sequence,
- session_auth=,
- set_standard_conforming_strings,
- sql_for_insert,
- substitute_at,
- supports_ddl_transactions?,
- supports_explain?,
- supports_index_sort_order?,
- supports_insert_with_returning?,
- supports_migrations?,
- supports_savepoints?,
- supports_statement_cache?
- T
- U
Classes and Modules
- MODULE ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::Utils
- CLASS ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::BindSubstitution
- CLASS ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::ExplainPrettyPrinter
- CLASS ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::StatementPool
- CLASS ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::TableDefinition
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" } } |
Class Public methods
Initializes and connects a PostgreSQL adapter.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 312 312: def initialize(connection, logger, connection_parameters, config) 313: super(connection, logger) 314: 315: if config.fetch(:prepared_statements) { true } 316: @visitor = Arel::Visitors::PostgreSQL.new self 317: else 318: @visitor = BindSubstitution.new self 319: end 320: 321: connection_parameters.delete :prepared_statements 322: 323: @connection_parameters, @config = connection_parameters, config 324: 325: # @local_tz is initialized as nil to avoid warnings when connect tries to use it 326: @local_tz = nil 327: @table_alias_length = nil 328: 329: connect 330: @statements = StatementPool.new @connection, 331: config.fetch(:statement_limit) { 1000 } 332: 333: if postgresql_version < 80200 334: raise "Your version of PostgreSQL (#{postgresql_version}) is too old, please upgrade!" 335: end 336: 337: @local_tz = execute('SHOW TIME ZONE', 'SCHEMA').first["TimeZone"] 338: end
Instance Public methods
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 1029 1029: def add_column(table_name, column_name, type, options = {}) 1030: clear_cache! 1031: 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])}" 1032: add_column_options!(add_column_sql, options) 1033: 1034: execute add_column_sql 1035: end
Begins a transaction.
Changes the column of a table.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 1038 1038: def change_column(table_name, column_name, type, options = {}) 1039: clear_cache! 1040: quoted_table_name = quote_table_name(table_name) 1041: 1042: execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" 1043: 1044: change_column_default(table_name, column_name, options[:default]) if options_include_default?(options) 1045: change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null) 1046: end
Changes the default value of a table column.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 1049 1049: def change_column_default(table_name, column_name, default) 1050: clear_cache! 1051: execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}" 1052: end
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 1054 1054: def change_column_null(table_name, column_name, null, default = nil) 1055: clear_cache! 1056: unless null || default.nil? 1057: execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL") 1058: end 1059: execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL") 1060: 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 862 862: def columns(table_name, name = nil) 863: # Limit, precision, and scale are all handled by the superclass. 864: column_definitions(table_name).collect do |column_name, type, default, notnull| 865: PostgreSQLColumn.new(column_name, default, type, notnull == 'f') 866: end 867: 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 748 748: def create_database(name, options = {}) 749: options = options.reverse_merge(:encoding => "utf8") 750: 751: option_string = options.symbolize_keys.sum do |key, value| 752: case key 753: when :owner 754: " OWNER = \"#{value}\"" 755: when :template 756: " TEMPLATE = \"#{value}\"" 757: when :encoding 758: " ENCODING = '#{value}'" 759: when :tablespace 760: " TABLESPACE = \"#{value}\"" 761: when :connection_limit 762: " CONNECTION LIMIT = #{value}" 763: else 764: "" 765: end 766: end 767: 768: execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}" 769: end
Returns the current database name.
Returns the current schema 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.
This method is also aliased as
exec_update
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 673 673: def exec_delete(sql, name = 'SQL', binds = []) 674: log(sql, name, binds) do 675: result = binds.empty? ? exec_no_cache(sql, binds) : 676: exec_cache(sql, binds) 677: affected = result.cmd_tuples 678: result.clear 679: affected 680: end 681: end
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 662 662: def exec_query(sql, name = 'SQL', binds = []) 663: log(sql, name, binds) do 664: result = binds.empty? ? exec_no_cache(sql, binds) : 665: exec_cache(sql, binds) 666: 667: ret = ActiveRecord::Result.new(result.fields, result_as_array(result)) 668: result.clear 669: return ret 670: end 671: end
Alias for exec_delete
Executes an SQL statement, returning a PGresult object on success or raising a PGError exception otherwise.
DATABASE STATEMENTS ======================================
Returns an array of indexes for the given table.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 821 821: def indexes(table_name, name = nil) 822: result = query("SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid\nFROM pg_class t\nINNER JOIN pg_index d ON t.oid = d.indrelid\nINNER JOIN pg_class i ON d.indexrelid = i.oid\nWHERE i.relkind = 'i'\nAND d.indisprimary = 'f'\nAND t.relname = '\#{table_name}'\nAND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) )\nORDER BY i.relname\n", name) 823: 824: 825: result.map do |row| 826: index_name = row[0] 827: unique = row[1] == 't' 828: indkey = row[2].split(" ") 829: inddef = row[3] 830: oid = row[4] 831: 832: columns = Hash[query("SELECT a.attnum, a.attname\nFROM pg_attribute a\nWHERE a.attrelid = \#{oid}\nAND a.attnum IN (\#{indkey.join(\",\")})\n", "Columns for index #{row[0]} on #{table_name}")] 833: 834: column_names = columns.values_at(*indkey).compact 835: 836: # add info on sort order for columns (only desc order is explicitly specified, asc is the default) 837: desc_order_columns = inddef.scan(/(\w+) DESC/).flatten 838: orders = desc_order_columns.any? ? Hash[desc_order_columns.map {|order_column| [order_column, :desc]}] : {} 839: 840: column_names.empty? ? nil : IndexDefinition.new(table_name, index_name, unique, column_names, [], orders) 841: end.compact 842: end
Executes an INSERT query and returns the new record’s ID
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 585 585: def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) 586: unless pk 587: # Extract the table from the insert sql. Yuck. 588: table_ref = extract_table_ref_from_insert_sql(sql) 589: pk = primary_key(table_ref) if table_ref 590: end 591: 592: if pk 593: select_value("#{sql} RETURNING #{quote_column_name(pk)}") 594: else 595: super 596: end 597: end
Returns just a table’s primary key
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 1004 1004: def primary_key(table) 1005: row = exec_query("SELECT DISTINCT(attr.attname)\nFROM pg_attribute attr\nINNER JOIN pg_depend dep ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid\nINNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1]\nWHERE cons.contype = 'p'\nAND dep.refobjid = $1::regclass\n", 'SCHEMA', [[nil, table]]).rows.first 1006: 1007: row && row.first 1008: 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 485 485: def quote_table_name(name) 486: schema, name_part = extract_pg_identifier_from_name(name.to_s) 487: 488: unless name_part 489: quote_column_name(schema) 490: else 491: table_name, name_part = extract_pg_identifier_from_name(name_part) 492: "#{quote_column_name(schema)}.#{quote_column_name(table_name)}" 493: end 494: end
Close then reopen the connection.
Renames a column in a table.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 1063 1063: def rename_column(table_name, column_name, new_column_name) 1064: clear_cache! 1065: execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}" 1066: end
Renames a table.
Example:
rename_table('octopuses', 'octopi')
Aborts a transaction.
Returns true if schema exists.
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.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 922 922: def serial_sequence(table, column) 923: result = exec_query("SELECT pg_get_serial_sequence($1, $2)\n", 'SCHEMA', [[nil, table], [nil, column]]) 924: result.rows.first.first 925: end
Set the authorized user for this session
Enable standard-conforming strings if available.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 387 387: def set_standard_conforming_strings 388: old, self.client_min_messages = client_min_messages, 'panic' 389: execute('SET standard_conforming_strings = on', 'SCHEMA') rescue nil 390: ensure 391: self.client_min_messages = old 392: end
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 684 684: def sql_for_insert(sql, pk, id_value, sequence_name, binds) 685: unless pk 686: # Extract the table from the insert sql. Yuck. 687: table_ref = extract_table_ref_from_insert_sql(sql) 688: pk = primary_key(table_ref) if table_ref 689: end 690: 691: sql = "#{sql} RETURNING #{quote_column_name(pk)}" if pk 692: 693: [sql, binds] 694: end
Returns true.
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
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_adapter.rb, line 792 792: def table_exists?(name) 793: schema, table = Utils.extract_schema_and_table(name.to_s) 794: return false unless table 795: 796: binds = [[nil, table]] 797: binds << [nil, schema] if schema 798: 799: exec_query("SELECT COUNT(*)\nFROM pg_class c\nLEFT JOIN pg_namespace n ON n.oid = c.relnamespace\nWHERE c.relkind in ('v','r')\nAND c.relname = $1\nAND n.nspname = \#{schema ? '$2' : 'ANY (current_schemas(false))'}\n", 'SCHEMA', binds).rows.first[0].to_i > 0 800: end
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 460 460: def type_cast(value, column) 461: return super unless column 462: 463: case value 464: when String 465: return super unless 'bytea' == column.sql_type 466: { :value => value, :format => 1 } 467: else 468: super 469: end 470: end
Maps logical Rails types to PostgreSQL-specific data types.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 1081 1081: def type_to_sql(type, limit = nil, precision = nil, scale = nil) 1082: case type.to_s 1083: when 'binary' 1084: # PostgreSQL doesn't support limits on binary (bytea) columns. 1085: # The hard limit is 1Gb, because of a 32-bit size field, and TOAST. 1086: case limit 1087: when nil, 0..0x3fffffff; super(type) 1088: else raise(ActiveRecordError, "No binary type has byte size #{limit}.") 1089: end 1090: when 'integer' 1091: return 'integer' unless limit 1092: 1093: case limit 1094: when 1, 2; 'smallint' 1095: when 3, 4; 'integer' 1096: when 5..8; 'bigint' 1097: else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.") 1098: end 1099: else 1100: super 1101: end 1102: 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.
Instance Protected methods
Returns the version of the connected PostgreSQL server.
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 1147 1147: def translate_exception(exception, message) 1148: case exception.message 1149: when /duplicate key value violates unique constraint/ 1150: RecordNotUnique.new(message, exception) 1151: when /violates foreign key constraint/ 1152: InvalidForeignKey.new(message, exception) 1153: else 1154: super 1155: end 1156: end