title: pg_dump setting of sequences
body: <p>I've recently started developing apps with PostgreSQL as backend DB (imposed on me) with no previous experience of Postgres. So far it hasn't been too bad, but now I run into a problem to which I cannot find answer for.</p>
  
  <p>I created a batch scripts that runs a pg_dump command for a particular database on the server. This batch file is executed on schedule by the pgAgent.</p>
  
  <p>The pg_dump itself seems to work ok. All the database structure and data are dumped to a file. However the sequences are all set to 1. For example for table <em>tbl_departments</em> the sequence dump looks like this:</p>
  
  <pre><code>CREATE SEQUENCE "tbl_departments_iID_seq"
      START WITH 1
      INCREMENT BY 1
      NO MINVALUE
      NO MAXVALUE
      CACHE 1;
  
  
  ALTER TABLE "tbl_departments_iID_seq" OWNER TO postgres;
  
  ALTER SEQUENCE "tbl_departments_iID_seq" OWNED BY tbl_departments."iID";
  </code></pre>
  
  <p>In this particular example the sequence should be set to start with 8, since the last inserted record has iID = 7.</p>
  
  <p>How do I make the pg_dump set the sequence starting number the next one available for each table?</p>
  
  <p>The command for dump is:
  %PGBIN%pg_dump -h 192.168.0.112 -U postgres -F p -b -v --inserts -f "\\192.168.0.58\PostgresDB\backup\internals_db.sql" Internals</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I think I have found the issue, although I still don't know how to resolve this:
  If I open pgAdmin and generate CREATE script for tbl_departments, it look like this:</p>
  
  <pre><code>CREATE TABLE tbl_departments
  (
    "iID" serial NOT NULL, -- id, autoincrement
    "c150Name" character varying(150) NOT NULL, -- human readable name for department
    "bRetired" boolean NOT NULL DEFAULT false, -- if TRUE that it is no longer active
    "iParentDept" integer NOT NULL DEFAULT 0, -- ID of the parent department
    CONSTRAINT tbl_departments_pkey PRIMARY KEY ("iID")
  )
  </code></pre>
  
  <p>The pg_dump statement is:</p>
  
  <pre><code>    CREATE TABLE tbl_departments (
          "iID" integer NOT NULL,
          "c150Name" character varying(150) NOT NULL,
          "bRetired" boolean DEFAULT false NOT NULL,
          "iParentDept" integer DEFAULT 0 NOT NULL
      );
  
  
      ALTER TABLE tbl_departments OWNER TO postgres;
  
      COMMENT ON TABLE tbl_departments IS 'list of departments';
  
      COMMENT ON COLUMN tbl_departments."iID" IS 'id, autoincrement';
  
      COMMENT ON COLUMN tbl_departments."c150Name" IS 'human readable name for department';
  
  
      COMMENT ON COLUMN tbl_departments."bRetired" IS 'if TRUE that it is no longer active';
  
      COMMENT ON COLUMN tbl_departments."iParentDept" IS 'ID of the parent department';
  
      CREATE SEQUENCE "tbl_departments_iID_seq"
          START WITH 1
          INCREMENT BY 1
          NO MINVALUE
          NO MAXVALUE
          CACHE 1;
  
          ALTER TABLE "tbl_departments_iID_seq" OWNER TO postgres;
  
          ALTER SEQUENCE "tbl_departments_iID_seq" OWNED BY tbl_departments."iID";
  
      INSERT INTO tbl_departments VALUES (1, 'Information Technologies', false, 0);
      INSERT INTO tbl_departments VALUES (2, 'Quality Control', false, 0);
      INSERT INTO tbl_departments VALUES (3, 'Engineering', false, 0);
      INSERT INTO tbl_departments VALUES (5, 'Quality Assurance', false, 0);
      INSERT INTO tbl_departments VALUES (6, 'Production', false, 2);
  
      ALTER TABLE ONLY tbl_departments
          ADD CONSTRAINT tbl_departments_pkey PRIMARY KEY ("iID");
  
  SELECT pg_catalog.setval('"tbl_departments_iID_seq"', 1, false);
  </code></pre>
  
  <p>the pg_dump sets the iID column to integer rather than serial, which disabled the auto-incrementation. The setval is also set to 1 rather than 7 as one would expect.</p>
  
  <p>When I open the front-end application and go to add new department it fails because all I am providing is: name of new department, active/disabled (true/false), ID of parent dept. (0 if no parent).</p>
  
  <p>I am expecting for the new record primary key iID to be created automatically by the DB, which as far as I know is an expected basic feature of any RDBMS.</p>
  
  <p>because the pg_dump converts the serials to integers the auto-incrementation stops.</p>