Many to one association converting to postres

Question

I am in the process of converting a rails app form sqlite to postgres so that I can deploy with heroku. I have postgres installed and I ran migrations, however when I try to run a query to find all the housemates associated with a house I get the following error

PG::Error: ERROR:  operator does not exist: character varying = integer
LINE 1: SELECT COUNT(*) FROM "mates"  WHERE "mates"."house_id" = 1
                                                           ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT COUNT(*) FROM "mates"  WHERE "mates"."house_id" = 1

This error originates form a sign-up view that you are redirected to to create and administrator after adding a house. Here is the view code:

Extracted source (around line #4):

1: <div class="container">
2:  <div class="row">
3:      <div class="span5 offset3">
4:          <% if current_house.mates.empty? %>
5:              <h2>Add an Administrator</h2>
6:          <% else %>
7:              <h2>Add a New Housemate</h2>

Thanks for all the help!

possible duplicate of [Converting existing rails app to postres from sqlite](http://stackoverflow.com/questions/17643326/converting-existing-rails-app-to-postres-from-sqlite) — Mike Szyndel, Jul 14 '13 at 21:03

David Aldridge · Answer 1 · 2013-07-14T21:12:57.683

0

You have defined the house_id column in the table mates as a character type, and it should be an integer. Some RDBMS are forgiving of this, but PostgreSQL is not.

You should correct this with a migration, and check the database schema for other such errors as well.

Because it's possible for a character column to contain non-numeric values, when migrating you'll have to issue the SQL statement:

alter table  mates
alter column house_id
type integer using cast(house_id as integer);

edited Jul 14 '13 at 21:12

answered Jul 14 '13 at 21:00

David Aldridge

51,479
8
68
96

When I run a the migration `change_column :mates, :house_id, :integer` I get the following error:`PG::Error: ERROR: column "house_id" cannot be cast automatically to type integer HINT: Specify a USING expression to perform the conversion.` – user2136807 Jul 14 '13 at 21:05

score 0 · Accepted Answer · answered Jul 14 '13 at 21:20

0

Go to add_house_id_to_mates migration and change line 3 from string to integer. After this, remove the 20130628212206_change_type_of_house_id_in_house.rb migration.

Run migrations from zero:

rake db:drop db:create db:migrate

answered Jul 14 '13 at 21:20

rmagnum2002

11,341
8
49
86

20130628212206_change_type_of_house_id_in_house.rb ? – David Aldridge Jul 14 '13 at 21:38

Many to one association converting to postres

2 Answers2

Linked