Re: 1.2 to 1.3 Upgrade fun

[Lyle <..hidden..>]

My first problem:

On 23/03/2014 16:21, Lyle wrote:

psql:/tmp/ledgersmb/1.2-1.3-upgrade_.sql:28: ERROR:  null value in column "heading" violates not-null constraint
DETAIL:  Failing row contains (2, 0010, Freehold Property, A, , null, f, f).
CONTEXT:  SQL statement "INSERT INTO account (accno, description, category, gifi_accno,
            heading, contra, tax)
        VALUES (in_accno, in_description, in_category, in_gifi_accno,
            t_heading_id, in_contra, in_tax)"
PL/pgSQL function account_save(integer,text,text,character,text,integer,boolean,boolean,text[]) line 48 at SQL statement

The actual code for this is:
SELECT account_save(id, accno, description, category, gifi_accno, NULL, contra,
                    CASE WHEN link like '%tax%' THEN true ELSE false END,
                    string_to_array(link,':'))
  FROM lsmb12.chart
 WHERE charttype = 'A';

At first it looked very odd as you can see NULL being passed explicitly for the heading column. On closer inspection of the table definition in Pg-database.sql:43 I can see it's a foreign key field. Looking at the account_save function definition in Accounts.sql:74 I can see it checks for NULL and swaps it for an ID from the account_headings table. So my problem is actually the SQL above:

INSERT INTO account_heading(id, accno, description)
SELECT id, accno, description
  FROM lsmb12.chart WHERE charttype = 'H';

That isn't actually inserting anything:
# SELECT id, accno, description
#   FROM lsmb12.chart WHERE charttype = 'H';
 id | accno | description
----+-------+-------------
(0 rows)

# SELECT DISTINCT charttype FROM lsmb12.chart;
charttype
-----------
 A
(1 row)

I don't have any H charttypes, only A. What should I do here?


Lyle