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.
- :allow_concurrency - If true, use async query methods so Ruby threads don‘t deadlock; otherwise, use blocking query methods.
- active?
- adapter_name
- add_column
- begin_db_transaction
- change_column
- change_column_default
- change_column_null
- client_min_messages
- client_min_messages=
- columns
- commit_db_transaction
- create_database
- create_savepoint
- current_database
- disconnect!
- encoding
- escape_bytea
- execute
- index_name_length
- indexes
- insert
- new
- outside_transaction?
- postgresql_version
- primary_key
- quote_table_name
- reconnect!
- release_savepoint
- rename_column
- rename_table
- rollback_db_transaction
- rollback_to_savepoint
- schema_search_path
- schema_search_path=
- select_rows
- set_standard_conforming_strings
- supports_ddl_transactions?
- supports_insert_with_returning?
- supports_migrations?
- supports_savepoints?
- table_alias_length
- tables
- type_to_sql
- unescape_bytea
- update_sql
ADAPTER_NAME | = | 'PostgreSQL'.freeze |
NATIVE_DATABASE_TYPES | = | { :primary_key => "serial primary key".freeze, :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" } |
Initializes and connects a PostgreSQL adapter.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 213 213: def initialize(connection, logger, connection_parameters, config) 214: super(connection, logger) 215: @connection_parameters, @config = connection_parameters, config 216: 217: connect 218: end
Is this connection alive and ready for queries?
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 221 221: def active? 222: if @connection.respond_to?(:status) 223: @connection.status == PGconn::CONNECTION_OK 224: else 225: # We're asking the driver, not ActiveRecord, so use @connection.query instead of #query 226: @connection.query 'SELECT 1' 227: true 228: end 229: # postgres-pr raises a NoMethodError when querying if no connection is available. 230: rescue PGError, NoMethodError 231: false 232: end
Returns ‘PostgreSQL’ as adapter name for identification purposes.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 208 208: def adapter_name 209: ADAPTER_NAME 210: end
Adds a new column to the named table. See TableDefinition#column for details of the options you can use.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 815 815: def add_column(table_name, column_name, type, options = {}) 816: default = options[:default] 817: notnull = options[:null] == false 818: 819: # Add the column. 820: execute("ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}") 821: 822: change_column_default(table_name, column_name, default) if options_include_default?(options) 823: change_column_null(table_name, column_name, false, default) if notnull 824: end
Begins a transaction.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 535 535: def begin_db_transaction 536: execute "BEGIN" 537: end
Changes the column of a table.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 827 827: def change_column(table_name, column_name, type, options = {}) 828: quoted_table_name = quote_table_name(table_name) 829: 830: begin 831: execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" 832: rescue ActiveRecord::StatementInvalid => e 833: raise e if postgresql_version > 80000 834: # This is PostgreSQL 7.x, so we have to use a more arcane way of doing it. 835: begin 836: begin_db_transaction 837: tmp_column_name = "#{column_name}_ar_tmp" 838: add_column(table_name, tmp_column_name, type, options) 839: execute "UPDATE #{quoted_table_name} SET #{quote_column_name(tmp_column_name)} = CAST(#{quote_column_name(column_name)} AS #{type_to_sql(type, options[:limit], options[:precision], options[:scale])})" 840: remove_column(table_name, column_name) 841: rename_column(table_name, tmp_column_name, column_name) 842: commit_db_transaction 843: rescue 844: rollback_db_transaction 845: end 846: end 847: 848: change_column_default(table_name, column_name, options[:default]) if options_include_default?(options) 849: change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null) 850: end
Changes the default value of a table column.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 853 853: def change_column_default(table_name, column_name, default) 854: execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}" 855: end
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 857 857: def change_column_null(table_name, column_name, null, default = nil) 858: unless null || default.nil? 859: execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL") 860: end 861: execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL") 862: end
Returns the current client message level.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 715 715: def client_min_messages 716: query('SHOW client_min_messages')[0][0] 717: end
Set the client message level.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 720 720: def client_min_messages=(level) 721: execute("SET client_min_messages TO '#{level}'") 722: end
Returns the list of all column definitions for a table.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 676 676: def columns(table_name, name = nil) 677: # Limit, precision, and scale are all handled by the superclass. 678: column_definitions(table_name).collect do |name, type, default, notnull| 679: PostgreSQLColumn.new(name, default, type, notnull == 'f') 680: end 681: end
Commits a transaction.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 540 540: def commit_db_transaction 541: execute "COMMIT" 542: end
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'
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 583 583: def create_database(name, options = {}) 584: options = options.reverse_merge(:encoding => "utf8") 585: 586: option_string = options.symbolize_keys.sum do |key, value| 587: case key 588: when :owner 589: " OWNER = \"#{value}\"" 590: when :template 591: " TEMPLATE = \"#{value}\"" 592: when :encoding 593: " ENCODING = '#{value}'" 594: when :tablespace 595: " TABLESPACE = \"#{value}\"" 596: when :connection_limit 597: " CONNECTION LIMIT = #{value}" 598: else 599: "" 600: end 601: end 602: 603: execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}" 604: end
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 557 557: def create_savepoint 558: execute("SAVEPOINT #{current_savepoint_name}") 559: end
Returns the current database name.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 684 684: def current_database 685: query('select current_database()')[0][0] 686: end
Close the connection.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 246 246: def disconnect! 247: @connection.close rescue nil 248: end
Returns the current database encoding format.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 689 689: def encoding 690: query("SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database\nWHERE pg_database.datname LIKE '\#{current_database}'\n")[0][0] 691: end
Escapes binary strings for bytea input to the database.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 293 293: def escape_bytea(original_value) 294: if @connection.respond_to?(:escape_bytea) 295: self.class.instance_eval do 296: define_method(:escape_bytea) do |value| 297: @connection.escape_bytea(value) if value 298: end 299: end 300: elsif PGconn.respond_to?(:escape_bytea) 301: self.class.instance_eval do 302: define_method(:escape_bytea) do |value| 303: PGconn.escape_bytea(value) if value 304: end 305: end 306: else 307: self.class.instance_eval do 308: define_method(:escape_bytea) do |value| 309: if value 310: result = '' 311: value.each_byte { |c| result << sprintf('\\\\%03o', c) } 312: result 313: end 314: end 315: end 316: end 317: escape_bytea(original_value) 318: end
Executes an SQL statement, returning a PGresult object on success or raising a PGError exception otherwise.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 519 519: def execute(sql, name = nil) 520: log(sql, name) do 521: if @async 522: @connection.async_exec(sql) 523: else 524: @connection.exec(sql) 525: end 526: end 527: end
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 873 873: def index_name_length 874: 63 875: end
Returns the list of all indexes for a table.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 635 635: def indexes(table_name, name = nil) 636: schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',') 637: result = query("SELECT distinct i.relname, d.indisunique, d.indkey, t.oid\nFROM pg_class t, pg_class i, pg_index d\nWHERE i.relkind = 'i'\nAND d.indexrelid = i.oid\nAND d.indisprimary = 'f'\nAND t.oid = d.indrelid\nAND t.relname = '\#{table_name}'\nAND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN (\#{schemas}) )\nORDER BY i.relname\n", name) 638: 639: 640: indexes = [] 641: 642: indexes = result.map do |row| 643: index_name = row[0] 644: unique = row[1] == 't' 645: indkey = row[2].split(" ") 646: oid = row[3] 647: 648: columns = query("SELECT a.attname, a.attnum\nFROM pg_attribute a\nWHERE a.attrelid = \#{oid}\nAND a.attnum IN (\#{indkey.join(\",\")})\n", "Columns for index #{row[0]} on #{table_name}").inject({}) {|attlist, r| attlist[r[1]] = r[0]; attlist} 649: 650: column_names = indkey.map {|attnum| columns[attnum] } 651: IndexDefinition.new(table_name, index_name, unique, column_names) 652: 653: end 654: 655: indexes 656: end
Executes an INSERT query and returns the new record‘s ID
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 452 452: def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) 453: # Extract the table from the insert sql. Yuck. 454: table = sql.split(" ", 4)[2].gsub('"', '') 455: 456: # Try an insert with 'returning id' if available (PG >= 8.2) 457: if supports_insert_with_returning? 458: pk, sequence_name = *pk_and_sequence_for(table) unless pk 459: if pk 460: id = select_value("#{sql} RETURNING #{quote_column_name(pk)}") 461: clear_query_cache 462: return id 463: end 464: end 465: 466: # Otherwise, insert then grab last_insert_id. 467: if insert_id = super 468: insert_id 469: else 470: # If neither pk nor sequence name is given, look them up. 471: unless pk || sequence_name 472: pk, sequence_name = *pk_and_sequence_for(table) 473: end 474: 475: # If a pk is given, fallback to default sequence name. 476: # Don't fetch last insert id for a table without a pk. 477: if pk && sequence_name ||= default_sequence_name(table, pk) 478: last_insert_id(table, sequence_name) 479: end 480: end 481: end
The ruby-pg driver supports inspecting the transaction status, while the ruby-postgres driver does not.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 552 552: def outside_transaction? 553: @connection.transaction_status == PGconn::PQTRANS_IDLE 554: end
Returns just a table‘s primary key
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 803 803: def primary_key(table) 804: pk_and_sequence = pk_and_sequence_for(table) 805: pk_and_sequence && pk_and_sequence.first 806: 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"
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 397 397: def quote_table_name(name) 398: schema, name_part = extract_pg_identifier_from_name(name.to_s) 399: 400: unless name_part 401: quote_column_name(schema) 402: else 403: table_name, name_part = extract_pg_identifier_from_name(name_part) 404: "#{quote_column_name(schema)}.#{quote_column_name(table_name)}" 405: end 406: end
Close then reopen the connection.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 235 235: def reconnect! 236: if @connection.respond_to?(:reset) 237: @connection.reset 238: configure_connection 239: else 240: disconnect! 241: connect 242: end 243: end
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 565 565: def release_savepoint 566: execute("RELEASE SAVEPOINT #{current_savepoint_name}") 567: end
Renames a column in a table.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 865 865: def rename_column(table_name, column_name, new_column_name) 866: execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}" 867: end
Renames a table.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 809 809: def rename_table(name, new_name) 810: execute "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}" 811: end
Aborts a transaction.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 545 545: def rollback_db_transaction 546: execute "ROLLBACK" 547: end
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 561 561: def rollback_to_savepoint 562: execute("ROLLBACK TO SAVEPOINT #{current_savepoint_name}") 563: end
Returns the active schema search path.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 710 710: def schema_search_path 711: @schema_search_path ||= query('SHOW search_path')[0][0] 712: 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.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 702 702: def schema_search_path=(schema_csv) 703: if schema_csv 704: execute "SET search_path TO #{schema_csv}" 705: @schema_search_path = schema_csv 706: end 707: end
Executes a SELECT query and returns an array of rows. Each row is an array of field values.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 447 447: def select_rows(sql, name = nil) 448: select_raw(sql, name).last 449: end
Enable standard-conforming strings if available.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 265 265: def set_standard_conforming_strings 266: old, self.client_min_messages = client_min_messages, 'panic' 267: execute('SET standard_conforming_strings = on') rescue nil 268: ensure 269: self.client_min_messages = old 270: end
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 276 276: def supports_ddl_transactions? 277: true 278: end
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 272 272: def supports_insert_with_returning? 273: postgresql_version >= 80200 274: end
Does PostgreSQL support migrations?
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 255 255: def supports_migrations? 256: true 257: end
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 280 280: def supports_savepoints? 281: true 282: end
Returns the configured supported identifier length supported by PostgreSQL, or report the default of 63 on PostgreSQL 7.x.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 286 286: def table_alias_length 287: @table_alias_length ||= (postgresql_version >= 80000 ? query('SHOW max_identifier_length')[0][0].to_i : 63) 288: end
Returns the list of all tables in the schema search path or a specified schema.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 624 624: def tables(name = nil) 625: schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',') 626: query("SELECT tablename\nFROM pg_tables\nWHERE schemaname IN (\#{schemas})\n", name).map { |row| row[0] } 627: end
Maps logical Rails types to PostgreSQL-specific data types.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 878 878: def type_to_sql(type, limit = nil, precision = nil, scale = nil) 879: return super unless type.to_s == 'integer' 880: 881: case limit 882: when 1..2; 'smallint' 883: when 3..4, nil; 'integer' 884: when 5..8; 'bigint' 885: else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.") 886: end 887: 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.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 323 323: def unescape_bytea(original_value) 324: # In each case, check if the value actually is escaped PostgreSQL bytea output 325: # or an unescaped Active Record attribute that was just written. 326: if @connection.respond_to?(:unescape_bytea) 327: self.class.instance_eval do 328: define_method(:unescape_bytea) do |value| 329: @connection.unescape_bytea(value) if value 330: end 331: end 332: elsif PGconn.respond_to?(:unescape_bytea) 333: self.class.instance_eval do 334: define_method(:unescape_bytea) do |value| 335: PGconn.unescape_bytea(value) if value 336: end 337: end 338: else 339: raise 'Your PostgreSQL connection does not support unescape_bytea. Try upgrading to pg 0.9.0 or later.' 340: end 341: unescape_bytea(original_value) 342: end
Executes an UPDATE query and returns the number of affected tuples.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 530 530: def update_sql(sql, name = nil) 531: super.cmd_tuples 532: end
Returns the version of the connected PostgreSQL version.
[ show source ]
# File activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb, line 926 926: def postgresql_version 927: @postgresql_version ||= 928: if @connection.respond_to?(:server_version) 929: @connection.server_version 930: else 931: # Mimic PGconn.server_version behavior 932: begin 933: query('SELECT version()')[0][0] =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/ 934: ($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i 935: rescue 936: 0 937: end 938: end 939: end