0

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

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.