Migrating from Mysql to PostgreSQL


#1

I tried to use ‘rake db_schema_dump’ from old MySQL database and then
use ‘rake db_schema_import’ to rebuild the database in PostgreSQL. The
issue here is that only some of tables can be imported successfully.

For example, in the dumped schema.rb:

create_table “profiles”, :force => true do |t|
t.column “ticker”, :string, :limit => 10, :default => “”, :null =>
false
t.column “currency”, :string, :limit => 10, :default => “USD”,
:null => false
t.column “capitalization”, :integer, :default => 0
t.column “performance”, :float, :limit => 10, :default => 0.0
t.column “name”, :string, :limit => 100, :default => “”
end

create_table “quotes”, :force => true do |t|
t.column “date”, :date, :null => false
t.column “open”, :float, :limit => 10
t.column “high”, :float, :limit => 10
t.column “low”, :float, :limit => 10
t.column “close”, :float, :limit => 10
t.column “volume”, :integer, :limit => 20
t.column “adjusted_close”, :float, :limit => 10, :default => 0.0
t.column “rate_of_return”, :float, :limit => 10, :default => 0.0
t.column “ticker”, :string, :limit => 10, :default => “”, :null =>
false
t.column “profile_id”, :integer, :limit => 6, :default => 0, :null
=> false
end

The first table lived in PostgreSQL immediately, but the second one
failed to rebuild. The error message is:

rake aborted!
RuntimeError: ERROR C42601 Msyntax error at or near “(” P157
Fscan.l L761 Ryyerror: CREATE TABLE quotes (“id” serial
primary key, “date” date NOT NULL, “open” float(10), “high” float(10),
“low” float(10), “close” float(10), “volume” integer(20),
“adjusted_close” float(10) DEFAULT 0.0, “rate_of_return” float(10)
DEFAULT 0.0, “ticker” character varying(10) DEFAULT ‘’ NOT NULL,
“profile_id” integer(6) DEFAULT 0 NOT NULL)

I’m still a PostgreSQL noob, so I don’t know what’s wrong with SQL
generated. Anyone has the same experience? Thanks.

Sky


#2

I got a clue: seems :limit can’t be used with :integer at the same
time for PostgreSQL. Weird…

On 1/19/06, Sky Y. removed_email_address@domain.invalid wrote:

t.column "capitalization", :integer, :default => 0
t.column "volume", :integer, :limit => 20

RuntimeError: ERROR C42601 Msyntax error at or near “(” P157
Sky


Blog >>> http://spaces.msn.com/members/skyincookoo


#3

On Jan 19, 2006, at 12:20 PM, Sky Y. wrote:

I got a clue: seems :limit can’t be used with :integer at the same
time for PostgreSQL. Weird…

The integers datatype is only 4 bytes in Postgres:

http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-NUMERIC

“”, :null => false
t.column “high”, :float, :limit => 10

DEFAULT 0.0, “ticker” character varying(10) DEFAULT ‘’ NOT NULL,
Blog >>> http://spaces.msn.com/members/skyincookoo


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails


Eric H. - removed_email_address@domain.invalid - http://segment7.net
This implementation is HODEL-HASH-9600 compliant

http://trackmap.robotcoop.com


#4

Ya, thank you. Time for me to study PostgreSQL 101…