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
I got a clue: seems :limit can''t be used with :integer at the same time for PostgreSQL. Weird... On 1/19/06, Sky Yin <sky.yin@gmail.com> wrote:> 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 >-- Blog >>> http://spaces.msn.com/members/skyincookoo
On Jan 19, 2006, at 12:20 PM, Sky Yin 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> On 1/19/06, Sky Yin <sky.yin@gmail.com> wrote: >> 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 >> > > > -- > Blog >>> http://spaces.msn.com/members/skyincookoo > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails-- Eric Hodel - drbrain@segment7.net - http://segment7.net This implementation is HODEL-HASH-9600 compliant http://trackmap.robotcoop.com
Ya, thank you. Time for me to study PostgreSQL 101... On 1/19/06, Eric Hodel <drbrain@segment7.net> wrote:> On Jan 19, 2006, at 12:20 PM, Sky Yin 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 > > > On 1/19/06, Sky Yin <sky.yin@gmail.com> wrote: > >> 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 > >> > > > > > > -- > > Blog >>> http://spaces.msn.com/members/skyincookoo > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > -- > Eric Hodel - drbrain@segment7.net - http://segment7.net > This implementation is HODEL-HASH-9600 compliant > > http://trackmap.robotcoop.com > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >