Re: Best import approach? Delimiters in strings

On 02/16/2013 09:52 AM, Andrew Taylor wrote:
> Unfortunately my starting point is data in the format of that example
> line. I hasn't spotted the lack of quotes on the first value. Given this
> format, is my best bet to write a script transforming it pre-import to
> postgres? I.e. are there no arguments I could pass to the import process
> to handle this directly? Thanks
>

Right now you are processing as text so:

http://www.postgresql.org/docs/9.2/interactive/sql-copy.html

Text Format

...Backslash characters (\) can be used in the COPY data to quote data
characters that might otherwise be taken as row or column delimiters. In
particular, the following characters must be preceded by a backslash if
they appear as part of a column value: backslash itself, newline,
carriage return, and the current delimiter character...

Option 1

If you want to continue to do that you need to escape the delimiter
character or create the *.txt file with a different delimiter, I usually
use '|'.

Option 2
Use the CSV format. By default the delimiter character is a comma and
the quote character is ". So

copy STOPS_LondonBuses from
'/home/andyt/projects/django-stringer/txc/OId_HY/GTFS/stops.txt' WITH
FORMAT csv;

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com