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?
By simply setting our id column as SERIAL with PRIMARY KEY attached, Postgres will handle all the complicated behind-the-scenes work and automatically increment our id column with a unique, primary key value for every INSERT .
The advantages to using numeric, auto incremented primary keys are numerous, but the most impactful benefits are faster speed when performing queries and data-independence when searching through thousands of records which might contain frequently altered data elsewhere in the table.
In PostgreSQL, a sequence is a special kind of database object that generates a sequence of integers. A sequence is often used as the primary key column in a table. The SERIAL pseudo-type can be used to generate a sequence while creating a new table.
As the PK moves, the data must be shuffled around disk to keep it in the correct order. Depending on the data set MySQL may have to move many physical rows to do this. The same goes for updating your PK. Changing the PK changes the order on disk and requires many rows to be moved.
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
)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With