1

I'm trying to migrate my sqlite3 db to Postgresql (i'm following the Railscasts #342, but i'm on ubuntu). After a rake db:create:all on my aperitime_development database i started a Sinatra server with Taps (to push mi sqlite3 db), but when i try to do

taps pull postgres://willy:piero@localhost/aperitime_development http://willy:ciao@localhost:5000'

the datas are not copied on postgres db and console doesn't terminate properly:

Receiving schema
Schema:          0% |                                          | ETA:  --:--:--
Schema:         25% |==========                                | ETA:  00:00:07
Schema:         50% |=====================                     | ETA:  00:00:03
Schema:         75% |===============================           | ETA:  00:00:01
Schema:        100% |==========================================| Time: 00:00:04
Receiving data
4 tables, 800 records
/usr/lib/ruby/gems/1.9.1/gems/sequel-3.20.0/lib/sequel/adapters/postgres.rb:175:in 'async_exec': PG::Error: ERROR:  integer out of range (Sequel::DatabaseError)
from /usr/lib/ruby/gems/1.9.1/gems/sequel-3.20.0/lib/sequel/adapters/postgres.rb:175:in `block (2 levels) in execute'
from /usr/lib/ruby/gems/1.9.1/gems/sequel-3.20.0/lib/sequel/database/logging.rb:28:in `log_yield'
from /usr/lib/ruby/gems/1.9.1/gems/sequel-3.20.0/lib/sequel/adapters/postgres.rb:175:in `block in execute'
from /usr/lib/ruby/gems/1.9.1/gems/sequel-3.20.0/lib/sequel/adapters/postgres.rb:158:in `check_disconnect_errors'
from /usr/lib/ruby/gems/1.9.1/gems/sequel-3.20.0/lib/sequel/adapters/postgres.rb:175:in `execute'

and erros go on.

any ideas?

That's my database.yml

development:
adapter: postgresql
encoding: unicode
database: aperitime_development
pool: 5
username: willy
password: piero

test:
adapter: postgresql
encoding: unicode
database: aperitime_test
pool: 5
username: willy
password: piero

here is my schema.rb

ActiveRecord::Schema.define(:version => 20120630154954) do

create_table "locals", :force => true do |t|
t.string   "nome"
t.string   "indirizzo"
t.text     "descrizione"
t.integer  "Tel", :limit => 8
t.integer  "voto"
t.string   "mappa"
t.datetime "created_at",  :null => false
t.datetime "updated_at",  :null => false
end

create_table "users", :force => true do |t|
t.string   "nome"
t.string   "email"
t.datetime "created_at",                         :null => false
t.datetime "updated_at",                         :null => false
t.string   "password_digest"
t.string   "remember_token"
t.boolean  "admin",           :default => false
end

add_index "users", ["email"], :name => "index_users_on_email", :unique => true
add_index "users", ["remember_token"], :name => "index_users_on_remember_token"

end
bugman
  • 315
  • 2
  • 5
  • 16
  • 1
    it appears that you are inserting data into an integer field that is overflowing it? Do you compare the schema of the Postgresql DB along with the values you are trying to insert to see if the are out of bounds. – Doon Jul 20 '12 at 14:18
  • In some cases i've inserted some phone numbers, could them be these strange values? how can i change them? – bugman Jul 20 '12 at 14:26
  • is Tel a telephone number? Should it really be an integer? Wouldn't a string be better? – Doon Jul 20 '12 at 14:46
  • A string should be better? and the datas will be migrated from integer to string, or type string is only for new datas? – bugman Jul 20 '12 at 14:52
  • is this for dev only? or is it production as well. If dev only just change the schema and regen the dev data?. but you can use: ALTER TABLE locals ALTER COLUMN Tel type character varying(255); that should adjust the existing data.. – Doon Jul 20 '12 at 15:01
  • i'm using postgres both for dev and production (i'm deploying on heroku). I've changed the schema with t.string "Tel" but the error is still the same, what do you mean with regen dev data? For changing ALTER COLUMNS and ALTER TABLE you mean all the columns of schema or just the "Tel" column? – bugman Jul 20 '12 at 15:11
  • the schema is just the representation of the database, changing it doesn't do anything unless you then re-create the db from the schema. You can build a migration to execute the schema change for the Tel column. I'll put this in an answer... – Doon Jul 20 '12 at 15:26

2 Answers2

1

Here's the magic incantation in your migration when you declare the column:

create_table :example do |t|  
 t.integer :field, :limit => 8 
end

The

:limit => 8 is the magic in this case as postgres only does signed 4-byte integers when you just say integer. This uses 8-byte signed integers.

Paul Rubel

Justin D.
  • 4,946
  • 5
  • 36
  • 69
1

The Tel column is set as integer, some of your data is overflowing postgresql's integer type Normally I see phone numbers being stored as Strings in the database. since at some point you might need an extension or the like that won't work as an int.

To change your existing table from integer to character varying in posgresql you create a migration with the following.

def up 
    execute <<-SQL
        ALTER TABLE locals ALTER COLUMN Tel type character varying(255)
    SQL
end

This should change the column type to rails default string representation. It should also convert the existing integers over. But be sure to test.

Doon
  • 19,719
  • 3
  • 40
  • 44
  • ok i've understand the causes of my error, but could you be more specific about what steps i have to do? just rails g migration and? Thank you – bugman Jul 20 '12 at 15:46
  • i create the migration correctly now "Tel" is a string but the error is still the same unfortunately – bugman Jul 20 '12 at 19:48
  • can you figure out what the exact record that is causing the error? and look at those values, and match them up against the table schema ? or is there more debug info you can enable to get more info about the statement that pgsql is complaining about. – Doon Jul 21 '12 at 18:21
  • at the end I reset the db and create a new one, thank you anyway – bugman Jul 21 '12 at 21:56