Thread: COPY tablename FROM and null values
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
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
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
yup... Replace the empty spaces in the file with some other value...
replace null values with \N
then try COPYing again
--
"The trouble with the rat race is that even if you win, you're still a rat." --- Lily Tomlin
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
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