How to continue sequence number during insert based on select - Postgres

Question

I am using PostgreSQL db. I have two tables as shown below

I would like to add the missing person_ids in Table_2 by referring records from Table_1.

If you see the above tables, you can notice that person_id = 2,4 from table_1 is missing in table_2.

Though I am able to do this with help from forum, the problem is with the not null constraint.

Table 2 create table definition is like as shown below

CREATE TABLE Table_2(
   SNO INTEGER (20) UNIQUE NOT NULL,
   PERSON_ID INTERGER (20) NOT NULL,
   date_1 DATE NOT NULL,
   date_2 DATE NOT NULL,
   DEPT VARCHAR (10) NOT NULL
);

This is what I tried

INSERT into Table_2 (person_id,date_1,date_2,Dept) (select distinct person_id,TO_DATE('1900-01-01', 'YYYY-MM-DD'),TO_DATE('2900-12-30', 'YYYY-MM-DD'),'F' from Table_1 where person_id not in (select distinct person_id from Table_2))

This results in error as shown below

`ERROR: null value in column "SNO" violates not-null constraint
DETAIL: Failing row contains (null, 2, 1900-01-01, 2900-12-30, F).
SQL state: 23502`

I expect my output to be like as shown below. Please note that my real data has more than 50K records and the newly added records should continue the sequence as is.

I told you in your last question not to use images for tables. Yet you do it again... — sticky bit
– sticky bit, Commented Jan 13, 2020 at 10:23
Please show the CREATE TABLE for table 1. What is the difference between SNO and person_id in table 1? — Vesa Karjalainen
– Vesa Karjalainen, Commented Jan 13, 2020 at 10:24
Hi @VesaKarjalainen - made some changes to the table. You can see the updated post. now sno column isn't there in table_1 — The Great
– The Great, Commented Jan 13, 2020 at 10:30
Does table_1 have a sno column? Does sno in table_2 have a sequence or is it a serial column? — Jim Jones
– Jim Jones, Commented Jan 13, 2020 at 10:33
That's not a CREATE TABLE command, and that's not table 1. That's not table 2 either, does it have a 'gender' and/or a 'dept'? And do not post images, post code someone can copy-paste. — Vesa Karjalainen
– Vesa Karjalainen, Commented Jan 13, 2020 at 10:36

Jim Jones · Accepted Answer · 2020-01-13 10:56:17Z

1

If sno in table_2 is has a sequence or is a serial column, try this

Sample data

CREATE TABLE table_1 (person_id int, day_of_birth int, month_of_birth int, gender text);
INSERT INTO table_1 
VALUES (4,17,7,'M'),
       (2,15,3,'F');

CREATE TABLE table_2 (sno serial, person_id int, date_1 date, date_2 date, dept text);

INSERT INTO table_2 (person_id, date_1, date_2, dept)
VALUES (1,'1990-02-27','2020-03-13','A'),
       (3,'1990-02-28','2020-02-14','B');

Insert query

INSERT into Table_2 (person_id,date_1,date_2,dept) 
SELECT person_id, 
       TO_DATE('1900-01-01', 'YYYY-MM-DD'), 
       TO_DATE('2900-12-30', 'YYYY-MM-DD'),'F' 
FROM Table_1 WHERE person_id NOT IN (SELECT person_id FROM Table_2);

Result

SELECT * FROM table_2;
 sno | person_id |   date_1   |   date_2   | dept 
-----+-----------+------------+------------+------
   1 |         1 | 1990-02-27 | 2020-03-13 | A
   2 |         3 | 1990-02-28 | 2020-02-14 | B
   3 |         4 | 1900-01-01 | 2900-12-30 | F
   4 |         2 | 1900-01-01 | 2900-12-30 | F
(4 Zeilen)

Edit (see comments)

Create a sequence

CREATE SEQUENCE seq_sno START WITH 3;

After that just place it in your insert using nextval

INSERT INTO Table_2 (sno, person_id,date_1,date_2,dept) 
SELECT nextval('seq_sno'),person_id, 
       TO_DATE('1900-01-01', 'YYYY-MM-DD'), 
       TO_DATE('2900-12-30', 'YYYY-MM-DD'),'F' 
FROM Table_1 WHERE person_id NOT IN (SELECT person_id FROM Table_2);

SELECT * FROM table_2;
 sno | person_id |   date_1   |   date_2   | dept 
-----+-----------+------------+------------+------
   1 |         1 | 1990-02-27 | 2020-03-13 | A
   2 |         3 | 1990-02-28 | 2020-02-14 | B
   3 |         4 | 1900-01-01 | 2900-12-30 | F
   4 |         2 | 1900-01-01 | 2900-12-30 | F
(4 Zeilen)

edited Jan 13, 2020 at 10:56

answered Jan 13, 2020 at 10:46

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

9 Comments

Vesa Karjalainen Over a year ago

That'll fail. SNO is NOT NULL with no DEFAULT, you have to give it a value.

The Great Over a year ago

Is it any different from the code that I have given in the post?

Jim Jones Over a year ago

@TheGreat check the create table statement I posted. It automatically sets the value of sno sequentially without passing it in the insert

The Great Over a year ago

Okay, Unfortunately I cannot use create statement.

Vesa Karjalainen Over a year ago

This would be fine if this was the only way to insert new data to table 2. If there are also other ways to insert new rows, I'd go with a trigger.

|

Gordon Linoff · Accepted Answer · 2020-01-13 13:27:30Z

1

Under most circumstances where a table has an integer primary key, it is assigned using:

serial
generated always as identity
a sequence number, perhaps set automatically using a trigger
means something that is defined by the business

Your table seems to be missing that. I would recommend fixing the table, but if you cannot, you can try:

insert into Table_2 (sno, person_id, date_1, date_2, Dept)
    select t2.max_sno + row_number() over (order by t1.person_id),
           t1.person_id, date '1900-01-01', date '2900-12-30', 'F' 
    from Table_1 t1 cross join
         (select max(t2.sno) as max_sno from table_2 t2) t2
    where not exists (select 1
                      from table_2 t2
                      where t2.person_id = t1.person_id
                     );

I don't really recommend this approach, except as a work-around. The correct approach is to fix the table.

Notes:

Use simple date constants. This uses the date keyword but you can also use '2020-01-01'::date
NOT EXISTS is preferable to NOT IN because it handles NULL values.
The entire WHERE clause is not the best approach to protect against duplicates. A better approach would use on conflict (or even both together).

edited Jan 13, 2020 at 13:27

answered Jan 13, 2020 at 11:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

The Great Over a year ago

Hi, thanks for the response. upvoted. but this throws error ERROR: missing FROM-clause entry for table "t2" but from clause is present

Collectives™ on Stack Overflow

How to continue sequence number during insert based on select - Postgres

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related