Join on different types

In a database I have two tables:

• The first has a field named taxonomy_id that is an integer

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

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;

StackExchange Database Administrators Q#42979, answer score: 13