CODEWITHSUNDEEP
ruby-on-railspostgresql
Fillype Farias
Fillype Farias

Reputation: 660

Rails SQL to Postgresql with auto increment

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.

Upvotes: 0

Views: 863

Answers (1)

fool-dev
fool-dev

Reputation: 7777

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

Upvotes: 1

Related Questions