Hi Vince,
Thanks for the tip, I have modified my nawk script to write NULL instead
of '' and have test loaded the first recent batch 70,000 records without
a problem.
Many thanks
Russell
Vince Vielhaber wrote:
>
> On Fri, 8 Nov 2002, Russell Aspinwall wrote:
>
> > Hi,
> >
> > I need to store 2GB of accumulated data into a database. I have built
> > v7.2.3 with Java, Tcl/Tk, Perl, C++ language extensions. I have written
> > nawk scripts to convert csv data into the appropiate INSERT INTO
> > commands, then adding the data is a simple matter of running psql -d
> > <database> -f date.psql
> >
> > Some of the fields are of type inet or cidr , however these fields do
> > not always contain a value. I need to be able to set the field to null
> > in my INSERT INTO command the empty values are represented by ''.
> > However during the INSERT command, the command can fail with a message
> > like. I would like to be able to queries on these fields but
> > automatically exclude the null values.
> >
> > psql:test.psql:1: ERROR: invalid CIDR value ''
> >
> > Any help would be appreciated.
>
> Give your inet type a default of null (not sure if this is necessary):
>
> create table foo(a int, n inet default null);
>
> An insert statement with an inet value:
> insert into foo(a,n) values(1,'1.1.1.1');
>
> An insert statement without an inet value:
> insert into foo(a,n) values(1,NULL);
>
> template1=# select * from foo;
> a | n
> ---+---------
> 1 | 1.1.1.1
> 1 |
> (2 rows)
>
> So when you put your insert statement together, replace the '' with NULL
> and you should be fine. Note there are no single quotes around NULL.
>
> Vince.
> --
> http://www.meanstreamradio.com http://www.unknown-artists.com
> Internet radio: It's not file sharing, it's just radio.