PostgreSQL and COPY FROM ... statement

Classic Servoy
1

Hi

I import data into a table with the COPY FROM stmt. This works nice, as long as all columns have a data element

COPY g_stations (id, longitude, latitude, name, altitude)
FROM '/usr/local/pgsql/share/g_stations.csv'
WITH DELIMITER AS '	'
CSV HEADER

But as the altitude column is optional, the column doesn’t always have data, so I tried something like

COPY g_stations (id, longitude, latitude, name, altitude)
FROM '/usr/local/pgsql/share/g_stations.csv'
WITH DELIMITER AS '	'
NULL AS ???
CSV HEADER

but I can’t find a useful string for the NULL AS parameter. Anyone an idea?

Regards, Robert

2

Hi Robert,

You can’t simply use an empty string ?

COPY g_stations (id, longitude, latitude, name, altitude)
FROM '/usr/local/pgsql/share/g_stations.csv'
WITH DELIMITER AS '   '
NULL AS ''
CSV HEADER

Also the copy command is explained here:
http://www.postgresql.org/docs/8.4/static/sql-copy.html

3

Hi Robert

Unfortunatly not, this is how it is described in the manual (I should have said that I tried that already).

I assume that I need something to describe NULL, as ‘’ is not NULL. But I can’t find anywhere a solution to how accomplish that.

Regards, Robert

4

Hi Robert,

have you tried

COALESCE(altitude,0)

if altitude is NULL then the second value, in this case 0 will be used.

Maybe you have to add a temporary field for this to work.

5

Christian

The COPY FROM stmt doesn’t accept COALESCE(altitude, 0) as column name, an error is thrown.

Regards, Robert

6

What about running

ALTER TABLE g_stations ADD COLUMN altitute_tmp TYPE int;
COPY g_stations (id, longitude, latitude, name, altitute_tmp) FROM '/usr/local/pgsql/share/g_stations.csv' WITH DELIMITER AS '   ' NULL AS '' CSV HEADER;
UPDATE g_stations SET COLUMN altitute = COALESCE(altitute_tmp,0);
ALTER TABLE g_stations DROP COLUMN altitute_tmp;
7

Alternatively you could drop the NOTNULL constraint, import data, deal with the NULLs and then put back the NOTNULL constraint.

8

Hi Christian

The problem is with the NULL AS ‘’ which doesn’t work because there is not an empty string in the csv file, but just plain nothing for that column. And may be to make it even a bit worse, it’s the last column. May be that’s no difference, though.

Regards, Robert

swingman:
What about running

ALTER TABLE g_stations ADD COLUMN altitute_tmp TYPE int;

COPY g_stations (id, longitude, latitude, name, altitute_tmp) FROM ‘/usr/local/pgsql/share/g_stations.csv’ WITH DELIMITER AS ’ ’ NULL AS ‘’ CSV HEADER;
UPDATE g_stations SET COLUMN altitute = COALESCE(altitute_tmp,0);
ALTER TABLE g_stations DROP COLUMN altitute_tmp;

9

Hi Robert,

Perhaps the issue is with the delimiter. Can you export to a real CSV format ?

10

I assume you mean by real an empty string like “” or ‘’?
Anyway, it would not help as I get this format from the swiss railway in the current form and have to find a way to use it as it is. Actually we import it for many years already, so that’s not a problem.
The reason I am experimenting with COPY FROM is that we are starting to use PostgreSQL WITH PostGIS, and I have to convert “traditional” data into spatial enabled table data and therefor, COPY FROM is quite handy (or let me say would be if it could handle “real” empty columns in a csv file. That’s why I not yet give up to find a solution for the import of NULL columns in a csv file (and, of course, hope to find a solution). It seems to me quite a normal real world problem, so don’t know why it does not work.

ROCLASI:
Hi Robert,

Perhaps the issue is with the delimiter. Can you export to a real CSV format ?

11

What about replacing COMMA-RETURN by COMMA-something-RETURN in the text file?
Anyway I always used TAB delimited, CSV is more difficult to deal with, all those quotes…

12

We also use/get tab delimited csv files :-)

swingman:
What about replacing COMMA-RETURN by COMMA-something-RETURN in the text file?
Anyway I always used TAB delimited, CSV is more difficult to deal with, all those quotes…