how to insert a serial number through select query in postgresql?

Question

I have a table called student with the below columns as shown below

S_id, person_id, subject_id, start_date, end_date, count

While, I have written a query to extract data from other tables to feed it into the person table

The query that I wrote looks like below

INSERT INTO student (S_id, person_id, subject_id, start_date, end_date, count)
SELECT
    person_id
    , subject_id,
    , MIN(exam_start_date) AS start_date
    , end_date 
    , COUNT(*) AS count
FROM main_table
GROUP BY person_id, subject_id, end_date 
ORDER BY person_id, subject_id

As you can see my select query fetches data only from person_id and for other columns.

How can I create an autoincrement or serial for the S_id column in the select statement so I can insert the data?

Currently, I receive an error

ERROR: null value in column "S_id" violates not-null constraint DETAIL: Failing row contains (null, 1234, 77670, 2139-04-22, 2139-04-22, 1).

Answer 1

If your s_id column is not already defined as serial or something (otherwise just leave it from the column list of your INSERT statement):

Are you searching for the row_number() window function? This creates an incrementing row count to your query starting with 1.

SELECT
    row_number() OVER () as s_id, -- <---------------
    person_id
    , subject_id,
    , MIN(exam_start_date) AS start_date
    , end_date 
    , COUNT(*) AS count
FROM main_table
GROUP BY person_id, subject_id, end_date 
ORDER BY person_id, subject_id

Maybe you should add the current maximum s_id from your actual students table if there are already some records to avoid conflicts with existing records with maybe same id values.

Answer 2

Your student table should be defined as either:

create table student (
    s_id int primary key generated always as identity,
    . . .
);

or:

create table student (
    s_id serial primary key ,
    . . .
);

Then your INSERT simply omits the column:

INSERT INTO student (person_id, subject_id, start_date, end_date, count)
    SELECT person_id, subject_id, MIN(exam_start_date) AS start_date,
           end_date, COUNT(*) AS count
    FROM main_table
    GROUP BY person_id, subject_id, end_date 
    ORDER BY person_id, subject_id;

And even this seems questionable, because a single person could have multiple rows in the table for different subjects. And even duplicated subjects based on the end date. Perhaps the issue is that student is just a bad name for the table and it should really be called something else, such as person_classes.