Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In a database I have two tables:

  1. The first has a field named taxonomy_id that is an integer
  2. The latter has a field named ID that is a character varying

The two tables are related: if it exists a row in the first one with taxonomy_id = N it will exist a row in the second one with ID = N.toString.

Now, I would like to do the join between this two tables; naturally the "normal" join doesn't work because of the type mismatch.

Can you help me solving this?

I'm using PostgreSQL.

share|improve this question
2  
Why is ID a string? –  gbn May 24 '13 at 11:16
    
I'm querying a DataWarehouse, it seems that during the creation phase this kind of queries hadn't been taken into account :( –  Aslan986 May 24 '13 at 11:30
add comment

2 Answers

Not valid integer types

If the values in the field are not actually valid integer types (digits and leading - only), a JOIN like @mustaccio suggested would fail with an exception: you couldn't cast to integer at all.

It would have to be the other (even more expensive) way round:

SELECT *
FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id::varchar = t2.id;

Also, since @mustaccio misleadingly suggested to use int8: Don't. integer equals int4. int8 would be bigint.

You can support that with a functional index:

CREATE INDEX tbl1_taxonomy_id_idx ON tbl1 (cast(taxonomy_id AS varchar));

Valid integer types

If we are dealing with valid integer types, you could just convert your column id to integer - if your setup allows that.

ALTER TABLE tbl2 ALTER COLUMN id TYPE integer USING id::int;

Then your problem is gone for good:

SELECT *
FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id = t2.id;

Barring that (if you cannot convert the column for some reason), a functional index would help:

CREATE INDEX tbl2_id_idx ON tbl2 (cast(id AS int));

SELECT *
FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id = t2.id::int;
share|improve this answer
add comment

If it's a one-time task, I think you can do something like

...
from
 first_table join second_table
  on first_table.taxonomy_id = cast(second_table.id as int8)
...

However, this may not use the index on second_table.id if it exists, so performance will suffer. In the long run you will be better off converting the id column in second_table to the proper int data type.

Disclaimer: I don't have a Postgres installation handy to verify the example.

share|improve this answer
3  
Change the "this may not use the index" to "this will not use the index" –  ypercube May 24 '13 at 12:25
2  
... or you may set up an index on second_table.id::integer as well –  dezso May 24 '13 at 12:58
2  
This may not work at all. I provided an explanation in my answer. –  Erwin Brandstetter May 24 '13 at 13:03
    
I don't know Postgres well enough, so you're probably right. Some other databases, DB2 for example, may still use an index in such a situation. –  mustaccio May 24 '13 at 13:25
    
@mustaccio Handy PostgreSQL installation: sqlfiddle.com –  Craig Ringer May 25 '13 at 4:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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