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.

  • 2
    Why is ID a string?
    – gbn
    May 24, 2013 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, 2013 at 11:30

2 Answers 2


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:

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:

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));

FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id = t2.id::int;

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

 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.

  • 3
    Change the "this may not use the index" to "this will not use the index" May 24, 2013 at 12:25
  • 2
    ... or you may set up an index on second_table.id::integer as well May 24, 2013 at 12:58
  • 2
    This may not work at all. I provided an explanation in my answer. May 24, 2013 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, 2013 at 13:25
  • @mustaccio Handy PostgreSQL installation: sqlfiddle.com May 25, 2013 at 4:03

Your Answer

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

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