Adding a custom column data type in Active Record

Question

On my local machine I develop my Rails application using MySQL, but on deployment I am using Heroku which uses PostgreSQL. I am in need of creating a new data type, specifically I wish to call it longtext, and it is going to need to map to separate column types in either database.

I have been searching for this. My basic idea is that I am going to need to override some hash inside of the ActiveRecord::ConnectionAdapters::*SQL adapter(s) but I figured I would consult the wealth of knowledge here to see if this is a good approach (and, if possible, pointers on how to do it) or if there is a quick win another way.

Right now the data type is "string" and I am getting failed inserts because the data type is too long. I want the same functionality on both MySQL and PgSQL, but it looks like there is no common data type that gives me an unlimited text blob column type?

The idea is that I want to have this application working correctly (with migrations) for both database technologies.

Much appreciated.

Frank Heikens · Accepted Answer · 2011-02-11 09:39:39Z

3

Why don't you install PostgreSQL on your dev machine? Download it, click "ok" a few times and you're up and running. It isn't rocket science :-)

http://www.postgresql.org/download/

PostgreSQL doesn't have limitations on datatypes, you can create anything you want, it's up to your imagination:

CREATE DOMAIN (simple stuff only)

CREATE TYPE (unlimited)

answered Feb 11, 2011 at 9:39

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Bellone Over a year ago

I want to be sure that compatibility works between database technologies. Since I am running PGSQL on Production I'd prefer to be able to fall back to something, if need be. MySQL just so happens to be what I know best.

John Bellone Over a year ago

Also, I don't have the ability (unless I am mistaken) to execute commands in a PostgreSQL console through Heroku (free).

mu is too short Over a year ago

You might not have PostgreSQL console access to Heroku but you can put any SQL you want inside a migration.

John Bellone Over a year ago

Is there a way to only execute that migration in PostgreSQL?

John Bellone · Accepted Answer · 2011-02-18 04:04:56Z

The SQL that Frank mentioned is actually the answer, but I really was looking for a more specific way to do RDBMS specific Rails migrations. The reason is that I want to maintain the fact that my application can run on both PostgreSQL and MySQL.

class AddLongtextToPostgresql < ActiveRecord::Migration
  def self.up
    case ActiveRecord::Base.connection.adapter_name
    when 'PostgreSQL'
      execute "CREATE DOMAIN longtext as text"
      execute "ALTER TABLE chapters ALTER COLUMN html TYPE longtext"
      execute "ALTER TABLE chapters ALTER COLUMN body TYPE longtext"
    else
      puts "This migration is not supported on this platform."
    end
  end

  def self.down
  end
end

That is effectively what I was looking for.

Collectives™ on Stack Overflow

Adding a custom column data type in Active Record

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related