在PostgreSQL中使用哪种数据类型来引用SERIAL数据类型？ [英] Which data type to use to reference SERIAL data type in PostgreSQL?

问题描述

我在PostgreSQL中有两个表。第一个应该具有第二个引用的自动递增ID字段：

I have two tables in PostgreSQL. The first one should have an auto-incrementing ID field that the second one references:

CREATE TABLE tableA (id SERIAL NOT NULL PRIMARY KEY, ...)
CREATE TABLE tableB (parent INTEGER NOT NULL REFERENCES tableA(id), ...)

根据文档， SERIAL 用作无符号4字节整数，而 INTEGER 被签名：

According to documentation, SERIAL acts as unsigned 4-byte integer while INTEGER is signed:

serial      4 bytes     autoincrementing integer    1 to 2147483647
integer     4 bytes     typical choice for integer  -2147483648 to [phone]

如果我理解正确，我使用的数据类型不兼容，但是PostgreSQL显然缺少无符号整数。我知道我可能不会使用超过2 * 10 ^ 9个ID（如果这样做的话，我总是可以使用 BIGSERIAL ），但这并不是那么重要，但是对我来说，有符号整数引用一个无符号整数似乎有点不干净。我确定肯定有更好的方法-我缺少什么吗？

If I understand correctly, the data types that I have used are not compatible, but PostgreSQL apparently lacks unsigned integers. I know I probably won't use more than 2*10^9 IDs (and if I did, I could always use BIGSERIAL), and it's not all that important, but it seems a bit unclean to me to have signed integer reference an unsigned one. I am sure there must be a better way - am I missing something?

推荐答案

A 序列 是一个整数，不是无符号。自动创建的序列恰好从1开始-仅此而已。列的数据类型仍为整数（如果需要，您可以将序列从-2147483648开始）。

A serial is an integer and it's not "unsigned". The sequence that is created automatically just happens to start at 1 - that's all. The column's data type is still an integer (you could make the sequence start at -2147483648 if you wanted to).

从手册中引用

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;

（强调我的意思）

这篇关于在PostgreSQL中使用哪种数据类型来引用SERIAL数据类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！