PostgreSQL复合主键和串行增量？ [英] PostgreSQL Composite Primary Key and Serial increment?

问题描述

我正在尝试创建如下表：

I'm trying to create a table as follows:

CREATE TABLE SCHEDULE (
 SESSIONID                  SERIAL,
 MODULECODE                 VARCHAR(10),
 CONSTRAINT SCHEDULE_FOREIGN_KEY FOREIGN KEY (MODULECODE) REFERENCES MODULES (MODULECODE),
 CONSTRAINT SCHEDULE_PRIMARY_KEY PRIMARY KEY (SESSIONID, MODULECODE));

想法是 SESSION ID 会自动每增加一个新行，但仅在 MODULECODE 本地递增，例如：

The idea being that SESSION ID would auto increment with each new row but only local to MODULECODE, for example:

----------------------
|SESSIONID|MODULECODE|
|---------|----------|
|    1    |    A     |
|    2    |    A     |
|    3    |    A     |
|    1    |    B     |
|    2    |    B     |
|    1    |    C     |
|    2    |    C     |
|--------------------|

我相信这是 AUTO_INCREMENT 的工作方式MySQL，但我怀疑PostgreSQL无法以这种方式工作。

I believe this is how AUTO_INCREMENT functions in MySQL but I suspect PostgreSQL doesn't work this way. How else would I achieve this in PostgreSQL?

推荐答案

按照@Juan的建议显示数据

Show the data as suggested by @Juan

select
    row_number() over (
        partition by modulecode order by modulecode
    ) as sessionid, 
    modulecode
from schedule

然后，当用户从某个模块中请求某个会话ID时， / p>

Then when the user asks for a certain sessionid from a certain module do:

select *
from schedule
where sessionid = (
    select sessionid
    from (
        select
            sessionid,
            row_number() over (order by sessionid) as module_sessionid
        from schedule
        where modulecode = 'B'
    ) s
    where module_sessionid = 2
)

这篇关于PostgreSQL复合主键和串行增量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！