hi i just migrated one table from mysql to postgresql and im getting this on query:
operator does not exist: text = bigint
this is my query
SELECT transactions.id,transactions.document,transactions.transaction_date,transactions.ref_no,transactions.status,contacts.supplier_business_name,contacts.id as idcontacto, CONCAT(COALESCE(u.surname, ''),' ',COALESCE(u.first_name, ''),' ',COALESCE(u.last_name,'')) as added_by,SUM(pl.quantity - pl.po_quantity_purchased) as po_qty_remaining,BS.name as location_name,contacts.address_line_1,contacts.custom_field1,contacts.custom_field2,contacts.landline,contacts.mobile,contacts.email
from transactions
LEFT JOIN contacts
ON transactions.contact_id = contacts.id
JOIN business_locations as BS
ON transactions.location_id = BS.id
LEFT JOIN purchase_lines as pl
ON transactions.id = pl.transaction_id
LEFT JOIN users as u
ON transactions.created_by = u.id
WHERE transactions.type = 'purchase_order' GROUP BY transactions.id
SELECT transactions.id,transactions.document,transactions.transaction_date,transactions.ref_no,transactions.status,contacts.supplier_business_name,contacts.id as idcontacto, CONCAT(COALESCE(u.surname, ''),' ',COALESCE(u.first_name, ''),' ',COALESCE(u.last_name,'')) as added_by,SUM(pl.quantity - pl.po_quantity_purchased) as po_qty_remaining,BS.name as location_name,contacts.address_line_1,contacts.custom_field1,contacts.custom_field2,contacts.landline,contacts.mobile,contacts.email
from transactions
LEFT JOIN contacts
ON transactions.contact_id = contacts.id
JOIN business_locations as BS
ON transactions.location_id::bigint = BS.id
LEFT JOIN purchase_lines as pl
ON transactions.id::bigint = pl.transaction_id
LEFT JOIN users as u
ON transactions.created_by::bigint = u.id
WHERE transactions.type = 'purchase_order' GROUP BY transactions.id
im not as clear with that, what should be the logic behind that? i mean i just imported from a mysql db, that was working. when should i use bigint exactly?
Looks like transactions.contact_id is a text and contacts.id could be a bigint(int8). So I made the change below...
However, I cannot tell if pl.transaction_id is also a bigint(int8) - but if it isn't than it should look like ON transactions.id = pl.transaction_id::bigint
SELECT transactions.id,transactions.document,transactions.transaction_date,transactions.ref_no,transactions.status,contacts.supplier_business_name,contacts.id as idcontacto, CONCAT(COALESCE(u.surname, ''),' ',COALESCE(u.first_name, ''),' ',COALESCE(u.last_name,'')) as added_by,SUM(pl.quantity - pl.po_quantity_purchased) as po_qty_remaining,BS.name as location_name,contacts.address_line_1,contacts.custom_field1,contacts.custom_field2,contacts.landline,contacts.mobile,contacts.email
from transactions
LEFT JOIN contacts
ON transactions.contact_id::bigint = contacts.id
JOIN business_locations as BS
ON transactions.location_id = BS.id
so i should but ::bigint to those columns that have int8 as column type?
i may be missing something as i put all ::bigint but still same error
SELECT transactions.id,transactions.document,transactions.transaction_date,transactions.ref_no,transactions.status,contacts.supplier_business_name,contacts.id as idcontacto, CONCAT(COALESCE(u.surname, ''),' ',COALESCE(u.first_name, ''),' ',COALESCE(u.last_name,'')) as added_by,SUM(pl.quantity - pl.po_quantity_purchased) as po_qty_remaining,BS.name as location_name,contacts.address_line_1,contacts.custom_field1,contacts.custom_field2,contacts.landline,contacts.mobile,contacts.email
from transactions
LEFT JOIN contacts
ON transactions.contact_id::bigint = contacts.id
JOIN business_locations as BS
ON transactions.location_id::bigint = BS.id
LEFT JOIN purchase_lines as pl
ON transactions.id = pl.transaction_id::bigint
LEFT JOIN users as u
ON transactions.created_by::bigint = u.id
WHERE transactions.type = 'purchase_order' GROUP BY transactions.id
almost all of my .id are int8, i cant just change to text all that ?
