CODEWITHSUNDEEP
javasqlpostgresqlinsertprimary-key
ioreskovic
ioreskovic

Reputation: 5699

Inserting new records into table with row number as id with PostgreSQL

I have a database table, let's call it Foo. Foo has an id column (which is long), and some data columns, let's call them Bar1 and Bar2

There is a script that used to work before there was an id column, that would simply add new stuff into the table like this: 

INSERT INTO Foo
SELECT OldBar1, SUM(OldBar2)
FROM OldFoo
GROUP BY OldBar1

Now, after the id has been added, if I have a new, Foo table empty initially, then I can do this: 

INSERT INTO Foo
SELECT row_number() OVER() as id, OldBar1, SUM(OldBar2)
FROM OldFoo
GROUP BY OldBar1;

And the data in Foo table will have their ids equal row numbers.

However, when there is existing data in the Foo table and I execute the previous query, I get an exception stating that "Key (id)=(1) already exists".

Obviously, I do not want the row_number to look at the source, the OldFoo table, but rather to the new, Foo table.

To draw a parallel example in Java, using HashMap<Integer, Foo> as the repository:

map.put(map.size() + 1, fooObject);

Is there a way to achieve this, to generate the ID which will be the same as row number in the target table?

Upvotes: 0

Views: 5775

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I strongly recommend finding a way to use serial. However, assuming that there are no other insert operations occurring on the table, the following should work for you:

INSERT INTO Foo
    SELECT (f.maxId + row_number() OVER() )as id, OldBar1, SUM(OldBar2)
    FROM OldFoo CROSS JOIN
         (SELECT MAX(id) as MAX(ID) FROM Foo) f
    GROUP BY OldBar1, f.maxId;

A piece of advice: when using INSERT always include the column names. So your query should read something like INSERT INTO Foo(Id, OldBar). This makes the code easier to maintain and to understand.

Upvotes: 2

Related Questions