Could someone explain to me why a PostgreSQL table created with the following scripts:
CREATE TABLE users
(
"id" serial NOT NULL,
"name" character varying(150) NOT NULL,
"surname" character varying (250) NOT NULL,
"dept_id" integer NOT NULL,
CONSTRAINT users_pkey PRIMARY KEY ("id")
)
gets dumped by pg_dump
in the following format:
CREATE TABLE users(
"id" integer NOT NULL,
"name" character varying(150) NOT NULL,
"surname" character varying (250) NOT NULL,
"dept_id" integer NOT NULL
);
ALTER TABLE users OWNER TO postgres;
CREATE SEQUENCE "users_id_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE "users_id_seq" OWNER TO postgres;
ALTER SEQUENCE "users_id_seq" OWNED BY users."id";
ALTER TABLE ONLY users
ADD CONSTRAINT users_pkey PRIMARY KEY ("id");
Obviously the above is only a small extract from the dump file.
Why does a pg_dump
convert the datatypes serial to integer? When I restore the database from the dumped SQL file, it pretty much becomes useless because the autoincrementation stops working, and when adding new records from a front-end form, it fails with message along the lines 'id field cannot be empty', obviously because it is a primary key set to not null, but auto-incrementation should kick in and populate the field with the next value in the sequence.
Am I missing something here?
The only impact of pg_dump are the increased I/O load and the long running transaction it creates. The long transaction will keep autovacuum from reclaimimg dead tuples for the duration of the dump. Normally that is no big problem unless you have very high write activity in the database.
pg_dump compress option has the following description: Specify the compression level to use. Zero means no compression. For the custom archive format, this specifies compression of individual table-data segments, and the default is to compress at a moderate level.
pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently.
PostgreSQL has a special kind of database object generator called SERIAL. It is used to generate a sequence of integers which are often used as the Primary key of a table. Syntax: variable_name SERIAL.
From docs:
The data types smallserial
, serial
and bigserial
are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:
CREATE TABLE tablename (
colname SERIAL
);
is equivalent to specifying:
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator. A NOT NULL constraint is applied to ensure that a null value cannot be inserted. (In most cases you would also want to attach a UNIQUE or PRIMARY KEY constraint to prevent duplicate values from being inserted by accident, but this is not automatic.) Lastly, the sequence is marked as "owned by" the column, so that it will be dropped if the column or table is dropped.
Well, it works here. Test snippet:
DROP SCHEMA tmpdump ;
CREATE SCHEMA tmpdump ;
set search_path = tmpdump ;
-- SELECT version();
DROP TABLE lusers;
CREATE TABLE lusers
(
"id" serial NOT NULL
, "name" character varying(150) NOT NULL
, "surname" character varying (250) NOT NULL
, "dept_id" integer NOT NULL
, CONSTRAINT lusers_pkey PRIMARY KEY ("id")
);
INSERT INTO lusers ("name", "surname", "dept_id") VALUES
('Joop', 'Zoetemelk', 2) , ('Jan', 'Jansen', 3)
, ('Ard', 'Schenk', 4) , ('Kees', 'Verkerk', 5);
Dump only the tmpdump
schema with:
pg_dump -U someusername yourdbname -n tmpdump -F p --inserts | less
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With