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.

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.

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 *tbl\_departments* the sequence dump looks like this:

```
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";

```

In this particular example the sequence should be set to start with 8, since the last inserted record has iID = 7.

How do I make the pg\_dump set the sequence starting number the next one available for each table?

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

**EDIT:**

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:

```
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")
)

```

The pg\_dump statement is:

```
    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);

```

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.

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).

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.

because the pg\_dump converts the serials to integers the auto-incrementation stops.