CODEWITHSUNDEEP
sqldatabasepostgresqlpostgresql-9.1
Hexodus
Hexodus

Reputation: 368

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

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?

Upvotes: 10

Views: 4003

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

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
)

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

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

Upvotes: 1

Related Questions