Upgrading ec_transaction failes under PostgreSQL 8.4:

ec_store module
Update #6412
* ALTER TABLE {ec_transaction} ADD COLUMN allocated numeric(10, 2) NOT NULL default 0
* ALTER TABLE {ec_transaction} ADD COLUMN balance numeric(10, 2) NOT NULL default 0
* Failed: UPDATE {ec_transaction} t SET t.allocated = (SELECT SUM(a.amount) FROM {ec_receipt_allocation} a WHERE a.type = 'transaction' AND a.etid = t.txnid)
* UPDATE {ec_transaction} SET balance = (gross - allocated)

The error comes from a.etid = t.txnid because they are from a different type.
ERROR: The operator does not exist: character varying = integer

MySQL will cast data automatically.
PostgreSQL and SQL99 does not allow to make a join on two different types.

Even if it works in MySQL, I don't think it is safe to make such a cast.

Anyway, I am new to the project, so don't consider my point of view.
The best would be to cast data using standard SQL.

Comments

grub3’s picture

grub3 commented

Sorry, eCommerce is not installed anylonguer on my system.
I don't have the database here, so I cannot tell which type should be casted

Use CAST (type AS INTEGER).
The solution is pretty easy: http://drupal.org/node/555536 .

I wrote a guide about MySQL and PostgreSQL
in the developer book: http://drupal.org/node/555514

gordon’s picture

gordon commented
Status: Active » Fixed

Thanks for the information. I have made the change and I hope that is doesn't cause any other issues.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.