Rails SQL to Postgresql with auto increment

Question

I'm migrating a SQL database in the following way:

class CitiesRelation < ActiveRecord::Migration[5.0]
  def change

    self.connection.execute %Q(

    -- Create Table --

CREATE TABLE Cities (
  Id INT NOT NULL AUTO_INCREMENT,
  Code INT NOT NULL,
  Name VARCHAR(255) NOT NULL,
  State CHAR(2) NOT NULL,
  PRIMARY KEY (Id)
);

-- Insert Data --

Insert into Cities (Code, Name, State) values ('1100015','City_1', 'A');
Insert into Cities (Code, Name, State) values ('1100023','City_2', 'B');
Insert into Cities (Code, Name, State) values ('1100031','City_3', 'C');

)

end 

end

And after the migration shows this error message:

PG::SyntaxError: ERROR:  syntax error at or near "AUTO_INCREMENT"
LINE 6:   Id INT NOT NULL AUTO_INCREMENT,
                          ^

What is the best approach to solve this problem? I'm wondering to do it via seed but I don't know how. Thank you.

If you're going to execute raw sql, make sure you supply code in the corresponding dialect. This looks like mysql dialect. — Sergio Tulentsev
– Sergio Tulentsev, Commented Mar 1, 2018 at 14:07
Hi @SergioTulentsev, sorry but I don't know where it is specified the dialect or how it can influence the result, and what you mean with activerecord migration DSL? — Fillype Farias
– Fillype Farias, Commented Mar 1, 2018 at 14:21
I mean, you're sending a flavor of sql that your postgresql won't understand. You have an example of AR DSL in the accepted answer (the create_table :cities) — Sergio Tulentsev
– Sergio Tulentsev, Commented Mar 1, 2018 at 14:36

fool-dev · Accepted Answer · 2018-03-01 14:34:22Z

1

Try the following pg 9.6

CREATE TABLE Cities (
    Id serial PRIMARY KEY,
    Code INT NOT NULL,
    Name VARCHAR(255) NOT NULL,
    State CHAR(2) NOT NULL
);

pg 10 or latter

CREATE TABLE staff (
    Id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    Code INT NOT NULL,
    Name VARCHAR(255) NOT NULL,
    State CHAR(2) NOT NULL
);

Why you not trying rails normal way like

class CitiesRelation < ActiveRecord::Migration[5.0]
  def change
    create_table :cities do |t|
      t.string :Code, null: false
      t.string :Name, null: false
      t.string :State, null: false

      t.timestamps
    end
  end
end

Rails by default creating id

Then rake db:migrate

Here are the rails doc

Hope it helps

edited Mar 1, 2018 at 14:34

answered Mar 1, 2018 at 14:04

fool-dev

7,7759 gold badges45 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fool-dev Over a year ago

@FillypeFarias, I have edited the answer, check it out, please let me know is this helped you

Fillype Farias Over a year ago

Your last post answered the question and sorry for that.

Collectives™ on Stack Overflow

Rails SQL to Postgresql with auto increment

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related