CODEWITHSUNDEEP
databasepostgresqlcsv
SMARZ
SMARZ

Reputation: 45

How to timestamp data copied into a postgresql database

I have a shell script pulling data from a server into a postgresql table.

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


psql -h localhost -d metrics -U postgres -c "copy tablename from STDIN with delimiter as ',';" < /data/metric.csv

Displays as:

      filesystem      | gb_blocks |  free  | %used | iused | %iused |   mounted_on
      /dev/hd2             | 16.75     | 12.60  | 25%   | 79098 | 3%     | /usr
      /dev/hd9var          | 8.00      | 6.00   | 25%   | 11965 | 1%     | /var
      /dev/hd3             | 36.75     | 18.83  | 49%   | 5614  | 1%     | /tmp
      /dev/hd1             | 3.25      | 3.11   | 5%    | 674   | 1%     | /home
      /dev/hd11admin       | 0.25      | 0.25   | 1%    | 16    | 1%     | /admin
      /proc                | -         | -      | -     | -     | -      | /proc

I'm working with Postgresql on an Ubuntu OS and pulling the info from an AIX server. I'd like to add a column with a timestamp for every time new data is added to the table because right now it just all blends together. I've tried to add another column for timestamp and give it a timestamp value but the timestamp isn't in the csv file and I'm not sure how to add it either. I appreciate an help I can get to solve this.

Upvotes: 0

Views: 785

Answers (1)

Rj_N
Rj_N

Reputation: 204

Create table and add date column with Default value like current_Date/now() ).

CREATE TABLE IF NOT EXISTS metrics
(
    filesystem text ,
    gb_blocks text ,
    free text ,
    per_used text ,
    iused text ,
    per_iused text ,
    mounted_on text ,
    load_dttm timestamp without time zone DEFAULT now()
);

mention columnswith table while loading data as below command

psql -h localhost -d metrics -U postgres -c "copy metrics(filesystem,gb_blocks,free,per_used,iused,per_iused,mounted_on) from STDIN with delimiter as ',';" < /data/metric.csv

Upvotes: 1

Related Questions