Trying to import a CSV file into a database in Postgres on Linux(Ubuntu 20.04)

Question

Using the Linux command df -Ph > metric.csv to create this output:

Filesystem      Size  Used Avail Use% Mounted on
udev            3.9G     0  3.9G   0% /dev
tmpfs           786M  2.2M  783M   1% /run'''

I login to postgres and CREATE TABLE:

CREATE TABLE metric(filesystem varchar,size varchar,used 
    varchar,available varchar,use varchar,mounted_on varchar, PRIMARY 
    KEY(filesystem));

Next I'm using the COPY command to insert data into this table:

COPY metrics (filesystem,size,used,available,use,mounted_on) FROM '/data/metric.csv' CSV HEADER DELIMITER ',';

I'm getting the following error:

ERROR:  missing data for column "size"
CONTEXT:  COPY metrics, line 2: "udev            3.9G     0  3.9G   0% /dev"

I'd appreciate any help or advice as to how to correct this so I can insert my data into the database.

Well ... Just because you call it metric.csv doesn't make it a CSV file. You explicitly specify , as a delimiter, and there are no commas in your df output; seems logical that postgres can't find the size (or any other column, for that matter). — tink
– tink, Commented Dec 28, 2021 at 19:48

tink · Accepted Answer · 2021-12-28 20:21:22Z

1

You may want to try this:

df -Ph | awk 'BEGIN{OFS=","}NR>1{$1=$1; print}' > metric.csv

As I mentioned in the comment above, your csv file isn't a csv. The awk command gets rid of the "header" (which I assume you don't want ingested in the table) and replaces the spaces with commas.

A few explanatory words regarding the awk - statement.

In the BEGIN{} section processing takes place before awk actually tackles any input. What we tell awk here to use , as the output field separator (OFS). By default awk uses any number of consecutive whitespace.

NR>1 tells awk to skip the first line (NR == number of record), thus omitting the header.

{$1=$1;print} - the assigning of the first field to itself is unfortunately necessary for awk to process the line; the print just does exactly what you think it does.

edited Dec 28, 2021 at 20:21

answered Dec 28, 2021 at 19:51

tink

15.6k5 gold badges54 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tink Over a year ago

@Zedu - does the explanation help?

Collectives™ on Stack Overflow

Trying to import a CSV file into a database in Postgres on Linux(Ubuntu 20.04)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest