CODEWITHSUNDEEP
postgresqlfilecopy
Sebh1990
Sebh1990

Reputation: 23

how to copy a file in postgres

Hi I am trying to insert regularly a file in a database. This text file is generated by a JBOSS app

The only difficulty is that the lines aren't really structured.

example:

Text1 (tab) text2 (tab) text3
Text1
text1 (tab- text2 (tab) text3
...

The part where i am struggling is the fact that sometimes there is data for text2 and text3 and sometimes not and when a run 

COPY table_name (datumorig,rest0,rest1) FROM 'file/location/filename.txt' with NULL AS '';

I get error no data for rest0.

what am I doing wrong here?

Upvotes: 1

Views: 145

Answers (1)

Nick Pfitzner
Nick Pfitzner

Reputation: 216

The issue is that the file is expecting TAB delimiter for each field by default.

When it hits the 2nd line in the file above, it is experiencing End Of Line (EOL) and therefore won't process because it can't fill the fields rest0 and rest1 (see http://www.postgresql.org/docs/9.3/static/sql-copy.html and scroll down to File Formats).

If you can get the JBOSS file altered to have space delimiter instead of tabs, you could import the entire file into a staging table defined with one text field, then use queries to determine how to move the rows into your destination table e.g.

Text1 text2 text3
Text1
Text1 text2

Is your file content, then 

CREATE TABLE staging (impfield text)

Then and run the import with no delimiter specified, before doing this:

UPDATE DestinationTable
SET datumorig = LEFT(impfield, 5)
, rest0 = SUBSTRING(impfield from 7 for 5)
, rest1 = SUBSTRING(impfield from 13 for 5)
FROM staging

Upvotes: 1

Related Questions