green plum (Postgres SQL)

Question

i have created a table in green plum , below is the script

-- Table: staging.file_data

-- DROP TABLE staging.file_data;

CREATE TABLE staging.file_data
(
  file_name character varying(28),
  line_number integer,
  date_line text
)
WITH (
  OIDS=FALSE
)
DISTRIBUTED BY (file_name);
ALTER TABLE staging.file_data
  OWNER TO dev_staging_develop;

now i need to load this table with some flat files .... the flat file has more columns , but i was asked to load this table with the flat file as 1. the first column will have the name of the file 2. second column is sequential number (line number ) 3 third column will have a string of data (which is nothing but all the data in the first row of the flat file ) and the same for every row .

so if the flat file has 100 records we will have 100 rows in the table .

but im not sure how to import this flat file into this table , can anyone help NOTE : flat file is a text file . and is located on my local machine .

thanks for your time and help in advance.

jason

Have you looked at: http://www.postgresql.org/docs/8.4/interactive/sql-copy.html and http://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgres-table — David Fleeman, Dec 17 '13 at 18:52

score 0 · Answer 1 · answered Jan 03 '14 at 12:50

Well to read a file you need to load it into a table, but because you want extra fields that are not part of the flat file you need to create an intermediate step there. Something like this:

--Create a temporary table to hold the contents of the file
create temp table temp_file (
each_line text
);

copy temp_file from '<path_to_file>/filename' text HEADER; 
--if the file has a header! See also delimiter if needed and choose a char that does not exist in the file

--Now add the line numbers, and the filename
alter table temp_file add column line_no smallint;
alter table temp_file add column filename text;
create sequence temp_file_seq increment by 1 start with 1; --not sure start with 1 or 0, try

update temp_file 
set  line_no  = nextval('temp_file_seq')
    ,filename = 'filename';

--Now populate your table
insert into staging.file_data
select * from temp_file;

--Cleanup
drop table temp_file;

green plum (Postgres SQL)

1 Answers1