Thread: inet/cidr data types
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. Russell
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.
Hi Russel,
it might be more easy and faster if you use the copy comand:
COPY targettable
FROM 'filename' [USING] DELIMITERS 'delimiter'
WITH NULL AS ''
delimiter depends on your data, either ',' or ';' or '\t'
Regards
Tino
--On Freitag, 8. November 2002 12:18 +0000 Russell Aspinwall
<russell.aspinwall@flomerics.co.uk> 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.
>
> Russell
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
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.