brian wrote:
> postgresql 8.1, fedora core 4
>
> I'm trying to update a database with a few new tables and insert some
> data. However, psql is refusing to insert some of the data, leading to
> errors when trying to refer to the sequence in the next insert (to a
> cross table).
>
Sure, less than ten minutes since i sent out my plea, i figured it out
for myself. For the curious:
>
> -- snip --
> DROP TABLE funding_type CASCADE;
> CREATE TABLE funding_type (
> id SERIAL PRIMARY KEY,
> name VARCHAR(16) NOT NULL
> );
>
> INSERT INTO funding_type (name) VALUES ('Grant');
> SELECT set_id('Grant ', CAST(currval('funding_type_id_seq') AS INT));
> INSERT INTO funding_type (name) VALUES ('Award');
> SELECT set_id('Award', CAST(currval('funding_type_id_seq') AS INT));
> INSERT INTO funding_type (name) VALUES ('Residency');
> SELECT set_id('Residency ', CAST(currval('funding_type_id_seq') AS INT));
> INSERT INTO funding_type (name) VALUES ('Special');
> SELECT set_id('Special ', CAST(currval('funding_type_id_seq') AS INT));
> INSERT INTO funding_type (name) VALUES ('Other');
> SELECT set_id('Other ', CAST(currval('funding_type_id_seq') AS INT));
>
Note the extra spaces after the variable names i'm using:
set_id('Grant ', ...
Because psql was not writing the errors to the file, i was relying on
what i saw in my terminal. The very last insert into arts_funder was
followed by 10 subsequent inserts into arts_funder_discipline. I'd
missed the very first error:
psql:funders.sql:1171: ERROR: null value in column "funding_type_id"
violates not-null constraint
Which is the error on insert into arts_funder. The construct
CAST(get_id('Grant') AS INT) was returning NULL because of the
whitespace, above.
I'm still confused as to why errors are not written to the output file
(\o out.txt). There must be some way to capture these, aside from a
quickly scrolling terminal window.
brian