Re: inet/cidr data types

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.