So far, it\’s RoR

Ruby on Rails, PostgreSQL, SVN, etc….

Archive for the ‘PostgreSQL’ Category

Mainly using PostgreSQL 8.2.4 and up

“Multiple default values” migration error with PostgreSQL 8.2.5

Posted by Chirag Patel on December 16, 2007

PostgreSQL 8.2.5 error

== CreateHeartrates: migrating ================================================

— create_table(:heartrates)

NOTICE: CREATE TABLE will create implicit sequence “heartrates_id_seq” for serial column “”

rake aborted!

PGError: ERROR: multiple default values specified for column “id” of table “heartrates”

: CREATE TABLE heartrates (“id” serial primary key DEFAULT NULL, “user_id” integer DEFAULT NULL, “timestamp” timestamp with time zone DEFAULT NULL, “heartrate” smallint NOT NULL)




I don’t see the above error on my Lenovo Windows machine because it’s running PostgreSQL 8.2.4. The issue occurs with PostgreSQL 8.2.5


Add this to the create table migration


t.column :id, :primary_key, :null => false



This is probably the simplest way to deal with this defect.



Post from: Migrations and PostgreSQL Primary Keys) – Rails Trac – Trac:

I tried to find a good place to patch the code, but everywhere I did, I could easily imagine someone yelling, “you can’t touch that”!


For instance, if scheme_definitions.rb’s primary_key could set :null => false, then everyone should be happy, even other db’s. Except, that of course you change all db’s to ‘NOT NULL’ versus ‘DEFAULT NULL’. I don’t know why you’d want to say ‘DEFAULT NULL’ though, it seems vaguely wrong.


This seems to be still in rails 2.0 too, and is still in 1.2.6.


Posted in PostgreSQL, Ruby on Rails | Leave a Comment »

Making (and verifying) the migration to PostgreSQL in Rails

Posted by Chirag Patel on August 20, 2007


After installing PostgreSQL on Windows, I converted my Rails application from MySQL to PostgreSQL with the following steps:

  1. Create a new database in PostgreSQL
  2. Installed postgres-pr gem (Ruby driver) because the postgres gem (native driver) didn’t work. Will figure out why later on.
  3. Changed the appropriate Rails config stuff in database.yml
      adapter: postgresql
      database: database_name
      username: postgres
      password: password
      host: localhost
  4. I ran only my first Rails migration to test that it worked using this command: rake db:migrate VERSION=1. My first migration (001_create_heartrates.rb) creates a table in the database so it will be easy to test.

Robby explains a quick way to migrate the data as well


You can test that the migration worked via 2 methods (GUI or command line)

  1. Using following commands with the psql command line utility (Start Menu -> PostgreSQL -> psql to postgres):
    psql commmand Description
    \? help
    \c database_name connect to a database (many other commands won’t work until you execute this)
    \dt show tables (execute \c first)
    \d table_name describes a particular table (e.g. table_name)
  2. Using the pgAdmin III GUI:
    • The database table is deeply nested in the tree, I missed it at first. Go to PostgreSQL Database Server 8.2 -> Databases -> database_name -> Schemas -> public -> Tables -> table_name

Posted in PostgreSQL, Ruby on Rails | Leave a Comment »

Settings for creating a new database in PostgreSQL

Posted by Chirag Patel on August 20, 2007

I wanted to quickly create a new database via pgAdmin III, so I used the following properties

Note: initdb (which ran during step 6 of the installation process) creates two initial databases, namely postgres and template1. Also, the cluster created by initdb also comes with two templates (template0 and template1). template0 is a backup for template1.

Setting Value Description
Owner: postgres I assume postgres refers to the database superuser, not the Windows login.
Encoding: UTF8 Because a PostgreSQL dialog box suggested it would be most flexible in case other clients decided to access the data (instead of SQL_ASCII)
Template: template1 schema extensions and objects are usually loaded into template1
Tablespace: pg_default The pg_default tablespace is the default tablespace of the template1 and template0 databases (and, therefore, will be the default tablespace for other databases as well, unless overridden by a TABLESPACE clause in CREATE DATABASE).Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. Two tablespaces are automatically created by initdb .

  1. The pg_global tablespace is used for shared system catalogs.
  2. The pg_default (described above)

One of the things that the tablespace defines is the file path for the data as shown with this command: CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';

Posted in PostgreSQL | 3 Comments »

Installing PostgreSQL on Windows Vista

Posted by Chirag Patel on August 15, 2007

Just a few notes to be very careful when installing PostgreSQL 8.2.4 on Windows Vista:

Because PostgreSQL needs to be installed as a Windows service, it needs to create an account that will be granted Logon On as a Service permission. When I first installed it, I allowed the installation wizard to use a default account name (postgres) and it generated a password for me. The password was extremely long (25 random chars) and difficult to remember, and for that matter, I could not copy and paste their auto-generated password from the the dialog box that came up. It installed successfully, but when I tried to change the password with the GUI (pgAdmin III), it was unsuccessful. So, I uninstalled PostgreSQL and re-installed it with my own Windows login (instead of the default postgres account) and that’s when things went terribly wrong. After that point, the PostgreSQL would not allow me to reinstall (Error message: “another installation in process”) and on top of that Windows would not allow me to delete the files manually because of permissions rights on the C:\Program Files\PostgreSQL directory. I finally had to do a System Restore and got it working.

To summarize:

  1. Do not use a member of the local administrators group as your Windows login for the service (such as your own personal Windows login) in step 5 of the installation guide!! Using their default postgres is the easiest. But use your own password instead of an autogenerated one. The FAQ (topic 2.3) explains why a non-administrator account is needed for security reasons. By the way, you can start and stop the PostgreSQL service within the Services control panel and also from the PostgreSQL group under the Start Menu.
  2. I would go ahead and allow it to create a database cluster in step 6 of the installation guide. I used the HTTP port number they suggested (5432) and allowed only localhost to access the database. In this step, the account name is the superuser (i.e. root access) of the database cluster.
    Here’s PostgreSQL’s explanation of a database cluster:

    Before you can do anything, you must initialize a database storage area on disk. We call this a database cluster. (SQL uses the term catalog cluster.) A database cluster is a collection of databases that is managed by a single instance of a running database server. After initialization, a database cluster will contain a database named postgres, which is meant as a default database for use by utilities, users and third party applications. The database server itself does not require the postgres database to exist, but many external utility programs assume it exists.

  3. After I successfully installed PostgreSQL, I used these settings to create a new database

Last, see the FAQ for the installation process for further explanations.

Posted in PostgreSQL, Windows | 1 Comment »

Timestamp support for Rails and MySQL/PostgreSQL/Oracle

Posted by Chirag Patel on August 13, 2007

Troel has written a great summary of the SQL standard for the timestamp data type along with its implementation in the big 5 databases (MySQL, PostgreSQL, Microsoft SQL Server, Oracle, and DB2).


It turns out that MySQL does not support sub-second time or date formats. There is a MICROSECOND function in MySQL allowed to run on temporal values, but sub-second precision (including millisecond) is not stored in the database.

A microseconds part is allowable in temporal values in some contexts, such as in literal values, and in the arguments to or return values from some temporal functions. Microseconds are specified as a trailing .uuuuuu part in the value. …. However, microseconds cannot be stored into a column of any temporal data type. Any microseconds part is discarded.

There is also a May 26, 2007 post on Kevin Burton’s blog in which a MySQL developer appears to confirm that MySQL will not store milliseconds (which obviously means microseconds can’t be stored either). It’s odd considering that MySQL’s DATETIME data type is 8 bytes and milliseconds can’t be stored. Interestingly, PostgreSQL’s timestamp datatype is also 8 bytes and can support microsecond resolution.

Here’s the mapping of the Rails data types (the symbols used in Rails migrations) to MySQL data types. This mapping can be found in mysql_adapter.rb in Rail’s ActiveRecord:

      def native_database_types #:nodoc:
          :primary_key => "int(11) DEFAULT NULL auto_increment PRIMARY KEY",
          :string      => { :name => "varchar", :limit => 255 },
          :text        => { :name => "text" },
          :integer     => { :name => "int", :limit => 11 },
          :float       => { :name => "float" },
          :decimal     => { :name => "decimal" },
          :datetime    => { :name => "datetime" },
          :timestamp   => { :name => "datetime" },
          :time        => { :name => "time" },
          :date        => { :name => "date" },
          :binary      => { :name => "blob" },
          :boolean     => { :name => "tinyint", :limit => 1 }


A potential solution to the MySQL sub-second limitation is to use force Rails to use the bigint type in MySQL (along with creating your own custom timestamp format to fit in the bigint type). This is an alternative to modifying mysql_adapter.rb in Rail’s ActiveRecord, which could pose problems later on. There is a plug-in MySQL Bigint Rails Plugin that supports multiple integer types. Not only is bigint supported, it also allows tinyint and mediumint. After installing, it magically transforms the :limit parameter so that it specifies the number of bytes for the column stored instead of specifying the maximum value expected for that column. For example, the following statement will be mapped to MySQL’s mediumint instead of a regular int.

    t.column "col2", :integer, :limit => 3, :null=> false

Run 'DESCRIBE table;' in the MySQL command line to verify that it worked.


PostgreSQL, on the other hand, has 1 microsecond resolution on all of its time and timestamp types. Here’s the mapping in of Rails data types to PostgreSQL data types (line 92 of postgresql_adapter.rb in Rail’s ActiveRecord)

      def 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" }

In PostgreSQL, the timestamp data type is referred to as the "timestamp without time zone". It will use this by default, unless you explicitly specify the timestamp with time zone. In either case, the timestamp is accepted and queried in string format: (e.g. '2003-07-29 13:19:30.532').

Also, PostgreSQL’s timestamp data type accepts a parameter p indicating the the number of fractional digits retained in the seconds field. To my understanding, p does not have any influence on the total bytes stored, which is always 8. Internally, it’s an 8-byte int or an 8-byte float depending on whether or not you compiled with enable-integer-datetimes. The default is 8-byte float.

If server was compiled with enable-integer-datetimes, PostgreSQL stores the timestamp as the total number of microseconds from midnight 2000-01-01 (in an int8). If not, it’s stored (in a float8) and the precision degrades as the date moves away from midnight 2000-01-01.

To make it all work, I added a :timestamp_with_time_zone data type by overriding PostgreSQL adapter class found in postgresql_adapter.rb

To override this class, include the preceding in a file such as lib\postgre_extensions.rb and add “require ‘postgre_extensions’” to the top of each migration file. Or you can add require ‘postgre_extensions’ to the bottom of config\environment.rb

class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
  def 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" },
      :timestamp_with_time_zone => { :name => "timestamp with time zone" },
      :time        => { :name => "time" },
      :date        => { :name => "date" },
      :binary      => { :name => "bytea" },
      :boolean     => { :name => "boolean" }

While I was at it, I tried to create a smallint type in PostgreSQL with the following line of Migrations code and the MySQL Bigint Rails Plugin (described above):

t.column :heartrate, :integer, :limit => 1, :null=> false

The author claims that it should work in other databases outside of MySQL and it did for this example! In this particular case, a smallint (2 bytes) was used instead of an integer (4 bytes). The tinyint data type (1 byte) is not available in PostgreSQL like it is in MySQL unfortunately.


Oracle, too, appears to supports millisecond precision, but the Rails mapping goes to DATE instead of TIMESTAMP as Paul Greer pointed out in ActiveRecord’s oracle.rb:

        def native_database_types #:nodoc:
            :primary_key => "NUMBER(38) NOT NULL PRIMARY KEY",
            :string      => { :name => "VARCHAR2", :limit => 255 },
            :text        => { :name => "CLOB" },
            :integer     => { :name => "NUMBER", :limit => 38 },
            :float       => { :name => "NUMBER" },
            :decimal     => { :name => "DECIMAL" },
            :datetime    => { :name => "DATE" },
            :timestamp   => { :name => "DATE" },
            :time        => { :name => "DATE" },
            :date        => { :name => "DATE" },
            :binary      => { :name => "BLOB" },
            :boolean     => { :name => "NUMBER", :limit => 1 }

Timestamp SQL queries

Use single quotes '' for timestamp! He are are some examples

  1. SELECT AVG(value) FROM numerics WHERE timestamp > '2007-08-23 19:48:09-04';
  2. SELECT AVG(value) FROM numerics WHERE timestamp BETWEEN '2007-08-23 19:48:09-04' AND '2007-08-23 19:48:09-04';
  3. SELECT AVG(value) FROM numerics WHERE timestamp > '2007-08-23 19:48:09-04'::timestamp;
  4. EXPLAIN SELECT AVG(value) FROM numerics WHERE timestamp > '2007-08-23 19:48:09-04';
    Traditional practice for EXPLAIN is to measure the costs in units of disk page fetches;

P. S. It’s a bad idea to title a column ‘timestamp’, because this is a datatype-identifier – but it works.

Useful Rails timestamp-related code

# First and last records from a model ordered by a specified column (e.g. timestamp)
ending = Users.find(:first, :order => 'timestamp DESC')
beginning = Users.find(:first, :order => 'timestamp ASC')

#resolve Ruby variable name (e.g. start_time) in string using the #{...} string interpolation mechanism
average = User.average(:age, :conditions => "timestamp > #{start_time} AND timestamp < #{end_time}")

#converts string to time object


Posted in Database, PostgreSQL, Ruby on Rails | Leave a Comment »