pg_dump串行数据类型问题 [英] pg_dump serial datatype issues

问题描述

有人可以向我解释为什么使用以下脚本创建PostgreSQL表:

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

由pg_dump以以下格式转储:

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.

为什么pg_dump将数据类型 serial 转换为 integer ?当我从转储的SQL文件中还原数据库时，由于自动增量停止工作，它几乎变得无用了，并且从前端表单添加新记录时，它失败并出现"id字段不能为空"的消息，很明显因为它是设置为不为null的主键，但是应该自动增加，并使用序列中的下一个值填充字段.

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?

推荐答案

来自文档:

数据类型smallserial，serial和bigserial不是真实类型，而仅仅是创建唯一标识符列的方便符号(类似于某些其他数据库支持的AUTO_INCREMENT属性).在当前的实现中，指定:

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

等同于指定:

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;

因此，我们创建了一个整数列，并安排其默认值从序列生成器中分配.应用NOT NULL约束以确保不能插入null值. (在大多数情况下，您还希望附加一个UNIQUE或PRIMARY KEY约束，以防止意外插入重复的值，但这不是自动的.)最后，该序列被标记为列拥有者"，因此该列如果删除了列或表，则将删除它们.

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.

这篇关于pg_dump串行数据类型问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！