how import csv file into Postgres with empty values?

Question

I am trying to import one csv file into Postgres which does contain age values, however there are also some empty values, since not all ages are known. I would like to import the columns as real, since the columns contain ages with decimals like 98.45. The empty values for people when age is not known is apparently considered as strings, however I still would like to import the ages values as numbers. So I was wondering how to import real values, even when some cells in the csv are empty and thus are considered according to Postgres as string values.

for creation I used the following code, since I am dealing with decimal values.

Create table psychosocial.age (
  respnr integer Primary key,
  fage real,
  gage real,
  hage real);

after importing csv file, I get the following error

ERROR:  invalid input syntax for integer: "11455, , , "

CONTEXT:  COPY age, line 2, column respnr: "11455, , , "

What do you need `postgis` for? According to your example you don't need the extension at all. — Jim Jones, Jul 11 '18 at 13:27
oh you are right, thanks for the notice, i sometimes name postgis and postgres interchangeable, :) — Joy, Jul 11 '18 at 13:35

score 3 · Answer 1 · edited Mar 07 '19 at 05:20

One problem is that you're trying to import white spaces into numeric fields. So, first you have to pre-process your csv file before importing it.

Below is an example of how you can solve it using awk. From your console execute the following command:

$ cat file.csv | awk '{sub(/^ +/,""); gsub(/, /,",")}1' | psql db -c "COPY psychosocial.age FROM STDIN WITH CSV HEADER"

In case you're wondering how to pipe commands, take a look at these answers. Here a more detailed example on how to use COPY and the STDIN.

You also have to take into account that having quotation marks on integer fields can be problematic, e.g:

"11455, , , "

This will result in an error, since postgres will parse "11455 as a single value and will try to store it in an interger field, which will obviously fail. Instead, format your csv file to be like this:

11455, , ,

or even

11455,,,

You can achieve this also using awk from your console:

$ awk '{gsub(/\"/,"")};1' file.csv

how import csv file into Postgres with empty values?

1 Answers1