activerecord-import: #import raises PG::NotNullViolation for id field on PostgreSQL

This happened to me too, but after much debugging, it turns out the issue stemmed from importing a Postgres dump.

To resolve, make sure sequences are owned by the correct column:

alter sequence mytable_id_seq owned by mytable.id;
select pg_get_serial_sequence('mytable', 'id');

Then try running Model.import and you should be good.

@jabley Thanks for pointing that out.

The issue is: when you new a record, the id attribute is added with value nil, and there is no mechanism to remove this column if sequence_name.nil? or connection.prefetch_primary_key? == falsefor id using uuid . Probably better to add some code about this?

I find a work around for it. So instead of just passing the records, I also pass column_names excluding the id column:

columns_without_id = YourModel.column_names.reject { |column| column == 'id' }
import(columns_without_id, records)

Thanks @caifara, Just added rescue block if table doesn’t id sequence.

def change
   tables = ActiveRecord::Base.connection.tables - %w(schema_migrations)

   tables.each do |table_name|
     begin
         ActiveRecord::Base.connection.execute("alter sequence #{table_name}_id_seq owned by #{table_name}.id;")
    rescue => e
      p " Error -- #{e}"
    end

   end
 end

just to lower the bar somewhat, had the same issue, this migration solved that:

  def change
    tables = ActiveRecord::Base.connection.tables - %w(schema_migrations)

    tables.each do |table_name|
      ActiveRecord::Base.connection.execute("alter sequence #{table_name}_id_seq owned by #{table_name}.id;")
    end
  end

I have this issue with Postgresql as well on a database a table like this:

CREATE TABLE answers (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    answer character varying,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);

When AR generates SQL for these records, it simply doesn’t specify the id column at all. For example:

INSERT INTO "answers" ("answer", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id"  [["answer", "Yes"], ["created_at", "2016-06-16 04:18:20.508992"], ["updated_at", "2016-06-16 04:18:20.508992"]]

Whereas import generates the following (abbreviated):

INSERT INTO "answers" ("id", "answer", "created_at", "updated_at") VALUES (NULL, 'Yes', '2016-06-16 04:14:21.168674', '2016-06-16 04:14:21.168791')