PostgreSQL bigserial&下一个 [英] PostgreSQL bigserial & nextval

问题描述

我有一个PgSQL 9.4.3服务器设置，以前我只是使用公共模式，例如，我创建了一个这样的表：

I've got a PgSQL 9.4.3 server setup and previously I was only using the public schema and for example I created a table like this:

CREATE TABLE ma_accessed_by_members_tracking (
    reference bigserial NOT NULL,
    ma_reference bigint NOT NULL,
    membership_reference bigint NOT NULL,
    date_accessed timestamp without time zone,
    points_awarded bigint NOT NULL
);

使用Windows程序PgAdmin III，我可以看到它创建了正确的信息和顺序。

Using the Windows Program PgAdmin III I can see it created the proper information and sequence.

不过，我最近像以前一样向同一个数据库添加了另一个名为 test的模式，并创建了完全相同的表。

However I've recently added another schema called "test" to the same database and created the exact same table, just like before.

但是这次我看到：

CREATE TABLE test.ma_accessed_by_members_tracking
(
  reference bigint NOT NULL DEFAULT nextval('ma_accessed_by_members_tracking_reference_seq'::regclass),
  ma_reference bigint NOT NULL,
  membership_reference bigint NOT NULL,
  date_accessed timestamp without time zone,
  points_awarded bigint NOT NULL
);

我的问题/好奇心是为什么在公众模式中，引用显示 bigserial ，但在 test 模式中引用显示 bigint 和 nextval ？

My question / curiosity is why in a public schema the reference shows bigserial but in the test schema reference shows bigint with a nextval?

两者均按预期工作。我只是不明白为什么架构的差异会显示不同的表创建。我意识到bigint和bigserial允许使用相同数量的int。

Both work as expected. I just do not understand why the difference in schema's would show different table creations. I realize that bigint and bigserial allow the same volume of ints to be used.

推荐答案

仅是一种符号上的便利

根据序列类型， smallserial ， serial 和 bigserial 不是真实的数据类型。相反，它们是同时创建序列和列，其中默认值指向该序列。

Merely A Notational Convenience

According to the documentation on Serial Types, smallserial, serial, and bigserial are not true data types. Rather, they are a notation to create at once both sequence and column with default value pointing to that sequence.

我在架构 public 上创建了测试表。命令 psql \d 显示 bigint 列类型。也许是PgAdmin的行为？

I created test table on schema public. The command psql \d shows bigint column type. Maybe it's PgAdmin behavior ?

我检查了PgAdmin源代码。在函数 pgColumn :: GetDefinition（）中，它扫描表 pg_depend 以获得自动依赖关系，当找到它时-替换 bigint 与 bigserial 来模拟原始表创建代码。

I checked PgAdmin source code. In function pgColumn::GetDefinition() it scans table pg_depend for auto dependency and when found it - replaces bigint with bigserial to simulate original table create code.

这篇关于PostgreSQL bigserial&下一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！