pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCOPY tablename FROM and null values
5+ messages / 5 participants
[nested] [flat]
* COPY tablename FROM and null values
@ 2006-03-22 11:18 ivan marchesini <marchesini@unipg.it>
0 siblings, 2 replies; 5+ messages in thread
From: ivan marchesini @ 2006-03-22 11:18 UTC (permalink / raw)
To: pgsql-sql
Dear users,
I'm working on a Postgres 7.4 server
I have a .txt file, containing some tabular data, where data are
delimited by TABs.
there are 3 columns:
column1 int4, column2 float8, column3 float8
the problem is that column3 contains also null values (i.e. sometimes is
empty)
so when I try to use COPY tablename FROM 'filename.txt' I obtain an
error
I have tried also using " WITH NULL AS ' ' " but without good results...
can someone explain me how to solve this problem???
thank you very much
Ivan
--
Ivan Marchesini
Department of Civil and Environmental Engineering
University of Perugia
Via G. Duranti 93/a
06125
Perugia (Italy)
e-mail: marchesini@unipg.it
ivan.marchesini@gmail.com
tel: +39(0)755853760
fax: +39(0)755853756
jabber: geoivan73@jabber.org
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: COPY tablename FROM and null values
@ 2006-03-22 12:27 Luckys <plpgsql@gmail.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 5+ messages in thread
From: Luckys @ 2006-03-22 12:27 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
On 3/22/06, Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
>
> O ivan marchesini Ýãñáøå óôéò Mar 22, 2006 :
>
> > Dear users,
> > I'm working on a Postgres 7.4 server
> >
> > I have a .txt file, containing some tabular data, where data are
> > delimited by TABs.
> > there are 3 columns:
> >
> > column1 int4, column2 float8, column3 float8
> >
> > the problem is that column3 contains also null values (i.e. sometimes is
> > empty)
> >
> > so when I try to use COPY tablename FROM 'filename.txt' I obtain an
> > error
> >
> > I have tried also using " WITH NULL AS ' ' " but without good results...
> >
> > can someone explain me how to solve this problem???
>
> batch edit your file (with sed,awk,perl,C,java,...) and build your
> explicit INSERT statements in some version of your file.
or Replace the empty spaces in the file with some other value (0.0)
>
> > thank you very much
> >
> > Ivan
> >
> >
> >
> >
> >
>
> --
> -Achilleus
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: COPY tablename FROM and null values
@ 2006-03-22 12:33 JSP <jason.palivino@gmail.com>
parent: Luckys <plpgsql@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: JSP @ 2006-03-22 12:33 UTC (permalink / raw)
To: Luckys <plpgsql@gmail.com>; +Cc: marchesini@unipg.it, "Achilleus Mantzios" <achill@matrix.gatewaynet.com>; pgsql-sql
yup... Replace the empty spaces in the file with some other value...
replace null values with \N
then try COPYing again
On 3/22/06, Luckys <plpgsql@gmail.com> wrote:
>
>
>
> On 3/22/06, Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> >
> > O ivan marchesini Ýãñáøå óôéò Mar 22, 2006 :
> >
> > > Dear users,
> > > I'm working on a Postgres 7.4 server
> > >
> > > I have a .txt file, containing some tabular data, where data are
> > > delimited by TABs.
> > > there are 3 columns:
> > >
> > > column1 int4, column2 float8, column3 float8
> > >
> > > the problem is that column3 contains also null values ( i.e. sometimes
> > is
> > > empty)
> > >
> > > so when I try to use COPY tablename FROM 'filename.txt' I obtain an
> > > error
> > >
> > > I have tried also using " WITH NULL AS ' ' " but without good
> > results...
> > >
> > > can someone explain me how to solve this problem???
> >
> > batch edit your file (with sed,awk,perl,C,java,...) and build your
> > explicit INSERT statements in some version of your file.
>
>
> or Replace the empty spaces in the file with some other value (0.0)
>
> >
> > > thank you very much
> > >
> > > Ivan
> > >
> > >
> > >
> > >
> > >
> >
> > --
> > -Achilleus
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
>
>
--
"The trouble with the rat race is that even if you win, you're still a rat."
--- Lily Tomlin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: COPY tablename FROM and null values
@ 2006-03-22 13:29 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: ivan marchesini <marchesini@unipg.it>
1 sibling, 1 reply; 5+ messages in thread
From: Achilleus Mantzios @ 2006-03-22 13:29 UTC (permalink / raw)
To: ivan marchesini <marchesini@unipg.it>; +Cc: pgsql-sql
O ivan marchesini Ýãñáøå óôéò Mar 22, 2006 :
> Dear users,
> I'm working on a Postgres 7.4 server
>
> I have a .txt file, containing some tabular data, where data are
> delimited by TABs.
> there are 3 columns:
>
> column1 int4, column2 float8, column3 float8
>
> the problem is that column3 contains also null values (i.e. sometimes is
> empty)
>
> so when I try to use COPY tablename FROM 'filename.txt' I obtain an
> error
>
> I have tried also using " WITH NULL AS ' ' " but without good results...
>
> can someone explain me how to solve this problem???
batch edit your file (with sed,awk,perl,C,java,...) and build your
explicit INSERT statements in some version of your file.
>
> thank you very much
>
> Ivan
>
>
>
>
>
--
-Achilleus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: COPY tablename FROM and null values
@ 2006-03-22 14:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: ivan marchesini <marchesini@unipg.it>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2006-03-22 14:52 UTC (permalink / raw)
To: marchesini@unipg.it; +Cc: pgsql-sql
ivan marchesini <marchesini@unipg.it> writes:
> I have a .txt file, containing some tabular data, where data are
> delimited by TABs.
> there are 3 columns:
> column1 int4, column2 float8, column3 float8
> the problem is that column3 contains also null values (i.e. sometimes is
> empty)
This should work, unless the file is set up so that the second TAB is
missing when column3 is "empty". If so, you'll need to fix that.
COPY is going to complain if there aren't exactly two TABs on every
line.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-03-22 14:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-22 11:18 COPY tablename FROM and null values ivan marchesini <marchesini@unipg.it>
2006-03-22 13:29 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2006-03-22 12:27 ` Luckys <plpgsql@gmail.com>
2006-03-22 12:33 ` JSP <jason.palivino@gmail.com>
2006-03-22 14:52 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox