1

I am working on a project with Matlab-2017b and PosgreSQL(via ODBC drivers). In the code there are some files that needed to be renamed with the database table's corresponding id number.

While doing some testing with the scenario where the sequence's current value is 1 test results shows this weird behaviour.

If the pgAdmin-4 Gui is used to open sequence properties and set the current value to 1 after the code executes the sequence returns 2.

Sequence current value altered via pgAdminGui The newly inserted element's id is 2

But if the query below from MATLAB is run:

ALTER SEQUENCE seq RESTART WITH 1;

then code executed, the newly inserted element id's column is 1.

The newly inserted element's row id after the query executed

While the code already handled to work properly, there are still some curiosity about what goes on behind this?

Semih SÜZEN
  • 163
  • 1
  • 2
  • 14
  • You need to [edit] your question and show us the code that shows the "weird behaviour". –  Oct 15 '18 at 07:52
  • The file goes from a set of validations before renaming then inserted to database. I do not have the values in the table before the renaming step. – Semih SÜZEN Oct 15 '18 at 07:53
  • 2
    But you can get the sequence value using `currval()` or `lastval()` in your code before actually inserting the data. Resetting a sequence is not something that should be done on a regular basis - only if you remove all rows from the table that uses the sequence –  Oct 15 '18 at 07:55
  • I already fixed the problematic part with the functions you suggested. Thanks for that. But what causes the difference when restarting sequence is done from the code and from the pgAdmin? – Semih SÜZEN Oct 15 '18 at 08:44
  • 1
    That looks fine to me. `RESTART WITH 1;` means that the next call to `nextval()` will return 1 (which is clearly documented in the manual) –  Oct 15 '18 at 08:47

1 Answers1

2

In the 1st case (PgAdmin), you have set the current value to 1, so the next one that will be fetch will be 2.

In the 2nd case (restart sequence), you set the next value to 1, so the next one that will be fetch will be 1.

JGH
  • 15,928
  • 4
  • 31
  • 48