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

If S_id column is declared SERIAL just omit it from the INSERT list. — Serg
– Serg, Commented Mar 15, 2021 at 8:52

S-Man · Accepted Answer · 2021-03-15 08:53:37Z

6

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.

answered Mar 15, 2021 at 8:53

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

3 Comments

The Great Over a year ago

S_id is defined as Integer...upvoted.. So, row_number is enough?

S-Man Over a year ago

row_number() OVER () is the correct syntaxt which defines a window which includes all records. If you want to ensure a certain order for the row count you need to specify this like: row_number() OVER (ORDER BY <your order criterion>)

S-Man Over a year ago

Demo fiddles: dbfiddle.uk/…

Gordon Linoff · Accepted Answer · 2021-03-15 12:18:22Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related