I have imported a .csv file into my database table.

It has three columns, for example

(numbers, first_name, last_name)

numbers column's rows start from 1 to 200.

Now, I want to import a new .csv file into that same table. I will do it every day. Each time rows start from 1 to some number.

New .csv file's "numbers" values start at 1 and go to 500.

When I import the new .csv file, it needs to continue from 201. It shouldn't update or delete old rows. Vice versa, it needs to continue rows from 201.

How can I do it? Please help me.

CodePudding user response：

As you never want to use the value provided from the CSV file, define numbers as an identity column:

create table the_table
(
  numbers integer not null generated always as identity,
  first_name text, 
  last_name text
);

Then when you import the CSV, only import first_name and last_name. How you do that, depends on the tool you use to import the file. Unfortunately the built-in copy command or psql's \copy can import only some columns of a file. You an also apply this to an existing table that already contains data.

If you can't import only some columns, you can create a sequence that will always be used through a trigger. This will effectively ignore any value provided for the numbers column:

create sequence numbers_seq
     start with 201; --<< or whatever you need depend

create table the_table
(
  numbers integer not null,
  first_name text, 
  last_name text
);

create function set_number() 
  returns trigger
as
$$
begin
  new.numbers := nextval('stuff.numbers_seq');
  return new;
end;
$$
language plpgsql;

create trigger populate_numbers_trigger
  before insert on the_table
  for each row 
  execute procedure set_number();

During an import (any INSERT actually) the values will always be taken from the sequence, regardless of what was specified in the INSERT statement. This works independently of any tool. The trigger however is slightly slower than the identity column.