CODEWITHSUNDEEP
postgresqlsequencepgadminpg-dump
user2505169
user2505169

Reputation: 23

some sequences not in dump from pg_dump

I want to pg_dump my database and have the problem that some sequences are generated in the dump file and some are not.

With the table infrastruktur_pictures it works with the table hoehenprofile it doesn't work. Here are the infos about the tables from pgadmin3:

hoehenprofile

CREATE SEQUENCE "hoehenprofile_HID_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 14289
  CACHE 1;
ALTER TABLE "hoehenprofile_HID_seq";

CREATE TABLE hoehenprofile
(
  "HID" serial NOT NULL,
  hoehenprofil character varying(255),
  CONSTRAINT "HID" PRIMARY KEY ("HID")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE hoehenprofile ADD COLUMN "HID" integer;
ALTER TABLE hoehenprofile ALTER COLUMN "HID" SET NOT NULL;
ALTER TABLE hoehenprofile ALTER COLUMN "HID" SET DEFAULT nextval('"hoehenprofile_HID_seq"'::regclass);

infrastruktur_pictures

CREATE SEQUENCE "infrastruktur_pictures_IPID_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE "infrastruktur_pictures_IPID_seq";

CREATE TABLE infrastruktur_pictures
(
  "IPID" serial NOT NULL,
  picture character varying(255) NOT NULL,
  picture_text text,
  fotograf text,
  CONSTRAINT prim PRIMARY KEY ("IPID")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE infrastruktur_pictures ADD COLUMN "IPID" integer;
ALTER TABLE infrastruktur_pictures ALTER COLUMN "IPID" SET NOT NULL;
ALTER TABLE infrastruktur_pictures ALTER COLUMN "IPID" SET DEFAULT nextval('"infrastruktur_pictures_IPID_seq"'::regclass);

In the dump file the generated Code for hoehenprofile looks like this(no sequence is generated):

--
-- TOC entry 145 (class 1259 OID 67719)
-- Name: hoehenprofile; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE hoehenprofile (
    "HID" integer DEFAULT nextval('"hoehenprofile_HID_seq"'::regclass) NOT NULL,
    hoehenprofil character varying(255)
);

The code for infrastruktur_pictures looks like this:

-
-- TOC entry 152 (class 1259 OID 67750)
-- Name: infrastruktur_pictures; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE infrastruktur_pictures (
    "IPID" integer NOT NULL,
    picture character varying(255) NOT NULL,
    picture_text text,
    fotograf text
);


--
-- TOC entry 4230 (class 0 OID 0)
-- Dependencies: 152
-- Name: TABLE infrastruktur_pictures; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE infrastruktur_pictures IS 'Bilder im Infrastruktur Bereich des Backends';


--
-- TOC entry 153 (class 1259 OID 67756)
-- Name: infrastruktur_pictures_IPID_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE "infrastruktur_pictures_IPID_seq"
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 4231 (class 0 OID 0)
-- Dependencies: 153
-- Name: infrastruktur_pictures_IPID_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE "infrastruktur_pictures_IPID_seq" OWNED BY infrastruktur_pictures."IPID";

The only difference between this two tables i can see in pgadmin3. When i click on the specific cloumn(HID, IPID) in the object browser and look at the properties Tab right of it i can see that at the HID column the sequence attribute is not set.

Upvotes: 2

Views: 3208

Answers (1)

user80168
user80168

Reputation:

The difference is that in the second case sequence is owned by table. Check last line of pg_dump.

This is because, when you create table using serial pseudo-datatype, the ownership is automatically added. But when you created sequence manually, you didn't set "OWNED BY".

Upvotes: 2

Related Questions