# Migrating from Mysql to PostgreSQL

**URL:** <https://www.ruby-forum.com/t/migrating-from-mysql-to-postgresql/52871>\
**Category:** Rails\
**Created:** [January 19, 2006, 9:43pm UTC](https://www.ruby-forum.com/t/migrating-from-mysql-to-postgresql/52871 "2006-01-19T21:43:52Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![cookoo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c89c15/32.png) [@cookoo](https://www.ruby-forum.com/u/cookoo)\
**Post date:** [January 19, 2006, 9:43pm UTC](https://www.ruby-forum.com/t/migrating-from-mysql-to-postgresql/52871/1 "2006-01-19T21:43:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![cookoo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c89c15/32.png) [@cookoo](https://www.ruby-forum.com/u/cookoo)\
**Post date:** [January 19, 2006, 10:02pm UTC](https://www.ruby-forum.com/t/migrating-from-mysql-to-postgresql/52871/2 "2006-01-19T22:02:00Z")

</div>

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](mailto: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](http://spaces.msn.com/members/skyincookoo)

---

<div class="post-metadata">

**Author:** ![cookoo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c89c15/32.png) [@cookoo](https://www.ruby-forum.com/u/cookoo)\
**Post date:** [January 19, 2006, 10:17pm UTC](https://www.ruby-forum.com/t/migrating-from-mysql-to-postgresql/52871/3 "2006-01-19T22:17:17Z")

</div>

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:

> **[Chapter 8. Data Types](https://www.postgresql.org/docs/8.1/datatype.html)**
>
> Chapter 8. Data Types Table of Contents 8.1. Numeric Types 8.1.1. Integer Types 8.1.2. Arbitrary Precision Numbers 8.1.3. Floating-Point Types 8.1.4. Serial …

> > “”, :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](http://spaces.msn.com/members/skyincookoo)
> 
> * * *
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

–  
Eric H. - removed\_email\_address@domain.invalid - [http://segment7.net](http://segment7.net)  
This implementation is HODEL-HASH-9600 compliant

[http://trackmap.robotcoop.com](http://trackmap.robotcoop.com)

---

<div class="post-metadata">

**Author:** ![cookoo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c89c15/32.png) [@cookoo](https://www.ruby-forum.com/u/cookoo)\
**Post date:** [January 19, 2006, 10:53pm UTC](https://www.ruby-forum.com/t/migrating-from-mysql-to-postgresql/52871/4 "2006-01-19T22:53:30Z")

</div>

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