Fetch records of postgres and store it in different table , if one column is null

Question

I have a table employee(id,empname,salary,dob) If the record of id and empname is null..whole record should be removed in different table...

For example.....

 id  | empname |salary | year
_____________________________
101  |  ishi   |100000 |   null
null | aarush  |200000 |   4
102  | rkr     |2000000|   6
104  |null     |300000 |   3
105  |null     |400000 |   3

If Id column has null value..the record should be fetch and placed in different table say null_table ...same case for empname column the rest record should be placed in different table say success_table

The output should be

success_table:

 id  | empname |salary | year
_____________________________
101  |  ishi   |100000 |   null
102  | rkr     |2000000|   6

null_table:

 id  | empname |salary | year
_____________________________
null | aarush  |200000 |   4
104  |null     |300000 |   3
105  |null     |400000 |   3

I am new to postgres...Any suggestions are appreciated...

Answer 1

Well, normally id is used to implement primary key which is not nullable... But I guess your problem is that you're getting dirty data from another source and try to sort it out ?

You could either design correctly (with all due constraints) your table and use a tool like pg_loader to load your data. It will create a error file with all the lines that don't fit.

Or you can import all your data into a junk table (which is the approach you seem to have chosen) and perform SQL on top of that to sort out your data.

You can try something like that:

/*Processing "nulls" that can be a problem*/
with nullable_rows as
(
  delete
  from input_table
  where id is null
    or  emp is null
    /*Add any other condition here*/
  returning *
)
insert into null_table
(id, emp, salary, year)
select * from nullable_rows;

/*Processing correct rows*/
with correct_rows as
(
  delete
  from input_table
  where id is not null
    and emp is not null
    /*Add any other condition here*/
  returning *
)
insert into sucess_table
(id, emp, salary, year)
select * from correct_rows;

That's if you load your data out of another source.

If another source is inserting dirty data in your database, use constraints to prevent it...