0

I have a table in a PostgreSQL database called reviews that has an id, comment, latitude, longitude, address and username column. I'm updating the contents of the table via input from a Spring Boot application therefore I will have a query inside the application that will pass variables into the table via the query. My issue is that I cannot update the id from the application so I would like to have the row's id to automatically update by one when a new row is added/inserted. This will be the query in the applicaiton (where the values will be represented by java variables based on the user input).

INSERT INTO reviews (id, comment, longitude, latitude, address, "user")
VALUES (nextval(id), 'Test 2 ',6.204510300000038, 53.3660165, '16 Curzon Street, Dublin', 'user1234');

So based on this I have two questions:

  1. Is it possible to create a sequence for a table that already exists so I can update the id by one everytime a new row is added?
  2. And do I have to create this sequence before every query from the application or do I create one and the database knows to update the value by one based on each query?

I find the documentation to be quite broad for this topic and I'm a novice with SQL/PostgreSQL so forgive me if I've explained this inefficiently.

Degsy
  • 53
  • 8
  • **Unrelated comment**: consider using PostGIS for storing these coordinates and take into account that your lat/long precision is far too high (unless you're tracking movement of bacteria ;-)). – Jim Jones Apr 25 '18 at 14:45
  • I plan to use PostGIS to query the reviews when I'm getting them from the database (as the reviews will be returned based on the user's selected coordinates). Silly question but can I put these in a generic PostgreSQL table and use postGIS to query the database? – Degsy Apr 25 '18 at 14:50
  • Have you consider using `serial` or `bigserial` as datatype for `id`? If you still can change it, I believe it would be the easiest solution, since `serial` and `bigserial` already take care of the sequence for you. – Jim Jones Apr 25 '18 at 14:50
  • yes, you can. Believe me, the installation of PostGIS is painless and you will have over 1.4k kickass functions at your reach. I couldn't recommend it more. – Jim Jones Apr 25 '18 at 14:53
  • My id in the table is already of type serial but I'm not sure how I can update this value? – Degsy Apr 25 '18 at 15:01
  • Like this? `INSERT INTO reviews (comment, longitude, latitude, address, "user") VALUES ('Test 2 ',6.204510300000038, 53.3660165, '16 Curzon Street, Dublin', 'user1234');` – Jim Jones Apr 25 '18 at 15:02
  • I've added `nextval('reviews_id_seq'::regclass)` instead of `nextval(id)` and it seems to have worked? Apologies for wasting your time @JimJones – Degsy Apr 25 '18 at 15:05
  • I will add a possible solution in an answer. – Jim Jones Apr 25 '18 at 15:05

0 Answers0