Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Location
    Texas
    Posts
    12

    Talking Unanswered: Reset sequence after import

    Hello,

    So I downloaded PostgreSQL 8.2 yesterday, and got it installed. I successfully imported all 54 tables and 278,993 records from my MS SQL database. I successfully updated all of my indexes, primary keys, foreign keys, etc. The one thing that I lack figuring out how (and have not found it in the docs yet), is to reset the sequence for my ID fields to the appropriate number (using a dynamic value as opposed to hard-coding it). Obviously I can go through and do this by hand using my GUI (navicat), but I'd much rather do it by script. Here is what I have tried, but each returns a syntax error:

    ALTER SEQUENCE seq_name RESET WITH (SELECT MAX(id) FROM myTable);

    ALTER SEQUENCE seq_name RESET WITH SELECT MAX(id) FROM myTable;

    ALTER SEQUENCE seq_name RESET WITH 'SELECT MAX(id) FROM myTable';

    SELECT MAX(id) AS the_count FROM myTable; ALTER SEQUENCE seq_name RESET WITH the_count;

    Each one of the ALTER statements fails at character 67, which is the first non-whitespace character after WITH.

    Any help would be much appreciated.


    Thanks in advance.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    First, you have an error in your SQL. You use RESTART, not RESET.
    (Also, better add 1 to the current max value...)

    However, I got the same error even when using the correct syntax. Per a post in the pg community mail archives, this appears to be an issue with Alter Sequence.

    The workaround would be to build a string and execute, or use setval in a select statement.

    The following statement worked to reset a sequence in my development server.
    Code:
    select setval('tds_schema.tbl_test_fld_int_seq',(SELECT max(fld_int) +1 from tds_schema.tbl_test))
    Last edited by loquin; 01-12-07 at 17:03.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jan 2007
    Location
    Texas
    Posts
    12
    Thank you Loquin. I actually used RESTART WITH; wrote that off the top of my head when I made the post. I will give your solution a shot and post back the results.

  4. #4
    Join Date
    Jan 2007
    Location
    Texas
    Posts
    12
    That did the trick. Thank you!

    I do have to ask though... why would you set the current value to one more than the max? I had originally planned to do it that way myself, but when I checked the value on a number of play tables that I created from scratch and inserted stuff into, each one of them had the current value equal to the max.


    Thanks again.

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    That's because I misread the documentation! Unless you specify a third, optional argument to setval, it WILL increment the value you enter by the increment value (default=1) for the next value the sequence generates. (ran a quick check to verify this behavior)

    BTW. You can shorten the statement a bit. Rather than use a subquery, you can issue the MAX function as a part of the same query:

    select setval('tbl_test_fld_int_seq', max(fld_int)) from tbl_test

    also works just fine.
    Last edited by loquin; 01-13-07 at 02:43.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Jan 2007
    Location
    Texas
    Posts
    12
    Heh... you're forgiven

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •