Postgres: How to use COPY FROM WITH DELIMITER with CHR function?

Question

I try to import a text file into a single column table, i.e. I don't want a single line of the source file to be delimited into columns. The file contains many different characters (tabs, commas, spaces) that could be recognized as delimiters. Since bell (CHR(7)) doesn't exist in the data file I chose it as delimiter:

COPY data_table(single_column) FROM '/tmp/data' WITH ENCODING 'LATIN1' DELIMITER CHR(7);

Unfortunately, this results in an error:

ERROR: syntax error at or near "chr"

What would be the correct syntax?

psql --version returns psql (PostgreSQL) 13.4 (Ubuntu 13.4-0ubuntu0.21.04.1) — Steffen Bobek
– Steffen Bobek, Commented Sep 23, 2021 at 11:17

jjanes · Accepted Answer · 2021-09-23 14:02:37Z

2

You can't use a function there. Use the escape notation.

 DELIMITER E'\007'

answered Sep 23, 2021 at 14:02

jjanes

45k5 gold badges39 silver badges48 bronze badges

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

1 Comment

Steffen Bobek Over a year ago

Thank you! That's much better than using a hex editor to get the bell character into the script...

Collectives™ on Stack Overflow

Postgres: How to use COPY FROM WITH DELIMITER with CHR function?

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