Cross match error: operator does not exist smallint = text

Question

The table schema I am upload is the following:

designation,text,
source_id,text,
ra,double precision,
dec,double precision,

The data looks like this:

designation, source_id, ra,dec
J171847.73-453957.5, 2598m455_ac51-020044, 259.6988822, -45.6659737
J162809.53-460730.1, 2471m455_ac51-005359, 247.0397382, -46.1250301
J163117.82-463044.9, 2487m470_ac51-038490, 247.8242855, -46.5124888

The query is attempting to get VHS components matched with ALLWISE source_id:

SELECT vhs.*
FROM vhs_dr5.vhs_cat_v3 as vhs, vhs_dr5.x1p5__vhs_cat_v3__allwise__source as xm, mydb://mytable as aw

WHERE aw.source_id=xm.id2 AND xm.id1=vhs.sourceid

The mydb creation and upload works fine. But when I go to do the match on the aw.source_id=xm.id2 both of which are strings I get the following error:

Error: operator does not exist: smallint = text
LINE 1: ...704_4408_vhs_allwise as aw where aw.source_id=xm.id2 an...

Not sure where smallint is being interpreted but its clearly incorrect as per the schema definitions of both tables and the data being presented?

Answer 1

I'm not able to completely reproduce the problem since your mydb tables no longer exist in the DB, although the foreign data links do.  From there I can see that the 'source_id' is indeed a small integer, and the 'id2' in the xmatch table is a text.   There are a number of mydb table links in the system for your account that aren't reachable due to invalid characters, i.e. a '-' in the table name is invalid in the database.  The one table that does still exist doesn't have the same name as your query so might not make for a valid comparison.

Please try the import and crossmatch again but be careful of the table name.  You might need to cast the datatype in the WHERE clause with something like "WHERE aw.source_id::text = xm.id2 .....".