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

Question

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?

You want to declare the column to be serial so the database handles the auto incrementing. See the numeric types here: postgresql.org/docs/9.4/static/datatype-numeric.html. — Gordon Linoff
– Gordon Linoff, Commented Mar 25, 2015 at 13:38
I forgot to mention, the tables are generated via JPA, so I'm not sure how to achieve this with entity classes. — ioreskovic
– ioreskovic, Commented Mar 25, 2015 at 13:40

Gordon Linoff · Accepted Answer · 2015-03-26 02:20:34Z

2

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.

edited Mar 26, 2015 at 2:20

answered Mar 25, 2015 at 13:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

ioreskovic Over a year ago

Of course, I was simply using Fooa and Bars to hide the real table and column names :D Could you tell me anything about the performace of this way of inserting?

ioreskovic Over a year ago

A question tho: Using this approach the following error occurs: ERROR: column "f.maxid" must appear in the GROUP BY clause or be used in an aggregate function.

ioreskovic Over a year ago

It appears that @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; translates into SERIAL, which makes my life a lot easier :D Thank you very much, answer accepted :D

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related