CODEWITHSUNDEEP
sqlpostgresqlconditional-statementsalter
Jason
Jason

Reputation: 359

Using Postgres: Create a Column of Binary Data Conditional on Data in Another Table

I have a table with a list of unique ID's and columns of data describing characteristics of those ID's. It takes the form:

ID  Tall  Funny  Weight
1   1      0     200
2   0      0     180
3   1      1     250

and so on. I have another table which is simply a list of ID's of people who have a characteristic, such as income over 100k.

Rich
1 
3

What I would like to do is create a column in the first table that = 1 if they are in the second table and 0 otherwise. I can do this in R like this:

TableA$Rich <- TableA$ID %in% TableB

but it's incredibly slow, if for no other reason because my postgres (ParAccel/PaDB) cluster has more resources than where I can run R. Can you help me accomplish this?

I tried doing a left outer join, like...

create table c as(select a.id, tall, funny, weight,b.id as rich
from tablea a
left outer join tableb b 
on a.id = b.id);

but it produced unexpected results. It gives me

ID  Tall  Funny  Weight  Rich
1   1      0     200     1
2   0      0     180     2
3   1      1     250     3

even though it should be "1, NULL, 3" and I would also prefer 1's and 0's. I was concerned that it may be an error with the data, but the data looks correct. I tried the same thing with a case when statement and got the same results but with "TRUE" for all values of Rich.

Upvotes: 0

Views: 581

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

A case statement solves your problem:

create table c as
    select a.id, tall, funny, weight,
           (case when b.id is null then 0 else 1 end) as rich
    from tablea a left outer join
         tableb b
         on a.id = b.id;

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125424

select
    a.id, tall, funny, weight,
    (b.id is not null)::integer as rich
from
    tablea a
    left outer join
    tableb b on a.id = b.id

Upvotes: 1

Related Questions