PostgreSQL - how to use copy command (importing text file) when the line ends with delimiter

Question

I am trying to import few large files to PostgreSQL database, the files have data in the following format, values separated by delimiter |. But the problem arises when you import using copy statement giving an error

ERROR: extra data after last expected column

After some testing I found that last character in the line is the cause of the problem, every line ends with delimiter. I found a workaround i.e. raplacing every character prior to line feed character. I am looking for COPY statement which works fine ignoring last character

8712678|MC|AMC355911||||355911|||AZ|AZ|2002|484|0|0|||C|CLOSED|CLOSED|239|DECLARED NULL & VOID|2002-01-31 00:00:00|2006-07-28 12:11:55|384101|LODE CLAIM|||2005-10-19 00:00:00|STELLAR METALS SM #2|01 05-10-1872;017STAT0091;30USC26,28,34|AMC355910|200103806|2001-09-07 00:00:00||||38||Y||||2011-07-19 12:55:52|

Vao Tsun · Accepted Answer · 2018-05-06 20:07:49Z

1

there's no such COPY. You need to use workaround, either the one you already use smth like sed 's/|$//g' or just alter table blah add column dummy to the end of your table, so COPY would populate it with nulls instead of error

answered May 6, 2018 at 20:07

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

1 Comment

Nagendra Putti Over a year ago

I will add a dummy column, thats much easier workaround than one currently use i.e modifying files

Collectives™ on Stack Overflow

PostgreSQL - how to use copy command (importing text file) when the line ends with delimiter

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