I'm trying to set up a test database using data from a live one.
However I seem to have have somehow setup the test DB differently than
the live one because some queries don't work as they should. I think it
has something to do with localization but can't be sure ...
The live database is as follows:
Name | Owner | Encoding
-----------+----------+----------
TAL | postgres | EUC_JP
template0 | postgres | EUC_JP
template1 | postgres | EUC_JP
(3 rows)
The test DB is:
Name | Owner | Encoding
-----------+----------+----------
TEST | postgres | EUC_JP
template0 | postgres | EUC_JP
template1 | postgres | EUC_JP
(3 rows)
However this query works on the live DB but not on the test DB (data on
the two DBs is identical). (I tried to find which part of the query was
causing the problems but couldn't ... my SQL debugging skills are still
lacking)
TEST=# select count(distinct invoice_id) from invoices, invoice_li where
id=invoice_id and dist_invoice is NULL AND not payment_rcvd and NOT
invoices.cancelled and NOT invoice_li.cancelled and NOT shipped AND
payment_type=2 AND delivery_method=(select id from del_methods where
dsc='YAMATO') AND max(req_del_date1, req_del_date2, req_del_date3) -
(select(next_day::text || 'd')::interval from delivery_limit_types where
id=(select limit_id from rel_del_limits_wards where ward_id in (select
id from wards where wards.ward=invoices.ward))) >= current_timestamp;
ERROR: More than one tuple returned by a subselect used as an expression.
ERROR: More than one tuple returned by a subselect used as an expression.
TAL=# select count(distinct invoice_id) from invoices, invoice_li where
id=invoice_id and dist_invoice is NULL AND not payment_rcvd and NOT
invoices.cancelled and NOT invoice_li.cancelled and NOT shipped AND
payment_type=2 AND delivery_method=(select id from del_methods where
dsc='YAMATO') AND max(req_del_date1, req_del_date2, req_del_date3) -
(select(next_day::text || 'd')::interval from delivery_limit_types where
id=(select limit_id from rel_del_limits_wards where ward_id in (select
id from wards where wards.ward=invoices.ward))) >= current_timestamp;
count
-------
0
(1 row)
How can I find out what is different betweeb my live db and TEST db? I
had a look at the postgres.conf files but those were identical.
Thanks,
Jean-Christian Imbeault
