On Thu, 6 Oct 2011, Chris Travers wrote:
INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated, sellprice, fxsellprice, discount, assemblyitem, unit, project_id, deliverydate, serialnumber, notes) SELECT id, trans_id, parts_id, description, qty, allocated, sellprice, fxsellprice, discount, assemblyitem, unit, project_id, deliverydate, serialnumber, notes FROM lsmb12.invoice; psql:sql/upgrade/1.2-1.3-manual.sql:474: ERROR: insert or update on table "invoice" violates foreign key constraint "invoice_trans_id_fkey" DETAIL: Key (trans_id)=(16796) is not present in table "transactions". Which I can't quite figure out since it seems transactions is empty.Checking this out. I think this is done in a transaction so it would roll back. But check to see if there is an RI issue here: SELECT trans_id FROM invoice WHERE trans_id NOT IN (select id in ar union select id from ap); Best Wishes, Chris Travers
Good God man do you ever sleep ! :-)Changing the query slightly and running against my production 1.2 produces:
lsmbprod=# SELECT trans_id FROM invoice WHERE trans_id NOT IN (select id from ar union select id from ap);
trans_id
----------
16796
16796
16796
16796
(4 rows)
There are invoice.id 16755, 16756, 16757, 16758 from an invoice from
2007-01-05 which looks like it was a duplicate that was cleaned up--
somehow.
ap.id 16798 looks like it is the invoice that 16796 *should* have been.It looks like I could 'delete from invoice where trans_id = 16796;' if there would be no further ramifications.
Did I mention this was SQL-Ledger 1.6 which made it to SL 2.4.x before moving to LedgerSMB? This may be one of the oldest datasets trying to purely upgrade. It was in Quickbooks before that and the oldest ap invoice is from 1997.
Grateful for your help, Louis