PostgreSQL Composite Primary Key and Serial increment?

Question

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

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

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?

MySQL's auto-increment doesn't work like that, and neither does Postgres' serial type. It would be a lot easier if you generate those numbers when displaying the data e.g. using row_number() and just keep the sessionid as an overall unique value. — user330315
– user330315, Commented Dec 3, 2015 at 19:36
I should add that session ID is not meant to be a unique value. It's unique only to the module, so that a module may have any number of sessions that can be differentiated between. Is there a way I can achieve this? — Hexodus
– Hexodus, Commented Dec 3, 2015 at 19:38
You could maybe do something with a BEFORE INSERT trigger...? — IMSoP
– IMSoP, Commented Dec 3, 2015 at 19:43
IMSoP, I'm pretty new to SQL I'll admit. I understand how triggers work but I'm not sure where I'd start in constructing one to achieve the functionality above? Any ideas would be awesome :) — Hexodus
– Hexodus, Commented Dec 3, 2015 at 19:52
@IMSoP the problem is will also need after update/delete triggers, and things can become messy. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Dec 3, 2015 at 19:59

Clodoaldo Neto · Accepted Answer · 2015-12-04 13:38:05Z

2

Show the data as suggested by @Juan

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

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
)

edited Dec 4, 2015 at 13:38

answered Dec 3, 2015 at 20:20

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Juan Carlos Oropeza · Accepted Answer · 2015-12-03 19:40:44Z

1

as hourse said you cant do it on your db. But you can asign those values in the select

 SELECT row_number() over (partition by MODULECODE order by MODULECODE) as SESSIONID, 
        MODULECODE
 FROM YourTable

answered Dec 3, 2015 at 19:40

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

2 Comments

Hexodus Over a year ago

Ah, I see. That gives me the data in the format I want, however, I want another table where a Student is able to select a particular SessionID for each module in the format that this gives. At the moment, SessionID is a unique index in the db which means that students aren't able to pick session 1 or 2 for module B and instead have to pick session 4 or 5. Any thoughts?

Juan Carlos Oropeza Over a year ago

Sorry @Jamie4840, you lost me. Maybe if you update your question with some sample? For start what if you put this select on a table function? and use it instead of real table? But if that doesnt work for you I recommend you create a new question with the whole problem

Collectives™ on Stack Overflow

PostgreSQL Composite Primary Key and Serial increment?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related