how to copy a file in postgres

Question

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?

Nick Pfitzner · Accepted Answer · 2015-05-13 12:53:22Z

1

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

answered May 13, 2015 at 12:53

Nick Pfitzner

2162 silver badges9 bronze badges

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

1 Comment

Nick Barnes Over a year ago

If you can't change the input file, you could probably still import into staging with some suitably unlikely choice of delimiter (e.g. DELIMITER E'\1'). Then you can still use the tabs to carve it up (e.g. rest0 = split_part(impfield, E'\t', 2), assuming there are no escaped tabs in the fields themselves...).

Collectives™ on Stack Overflow

how to copy a file in postgres

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

Related