ERROR: COPY delimiter must be a single one-byte character

If you are using Vertica, you could use E'\t'or U&'\0009'

To indicate a non-printing delimiter character (such as a tab), specify the character in extended string syntax (E'...'). If your database has StandardConformingStrings enabled, use a Unicode string literal (U&'...'). For example, use either E'\t' or U&'\0009' to specify tab as the delimiter.

Unfortunatelly there is no way to load flat file with multiple characters delimiter ~,~ in Postgres unless you want to modify source code (and recompile of course) by yourself in some (terrific) way:

/* Only single-byte delimiter strings are supported. */if (strlen(cstate->delim) != 1)    ereport(ERROR,        (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),    errmsg("COPY delimiter must be a single one-byte character")));

What you want is to preprocess your input file with some external tool, for example sed might to be best companion on GNU/Linux platfom, for example:

sed s/~,~/\\t/g inputFile

The obvious thing to do is what all other answers advised. Edit import file. I would do that, too.

However, as a proof of concept, here are two ways to accomplish this without additional tools.

1) General solution

CREATE OR REPLACE FUNCTION f_import_file(OUT my_count integer)  RETURNS integer AS$BODY$DECLARE    myfile    text;  -- read xml file into that var.    datafile text := '\path\to\file.txt'; -- !pg_read_file only accepts relative path in database dir!BEGINmyfile := pg_read_file(datafile, 0, 100000000);  -- arbitrary 100 MB max.INSERT INTO public.my_tblSELECT ('(' || regexp_split_to_table(replace(myfile, '~,~', ','), E'\n') || ')')::public.my_tbl;-- !depending on file format, you might need additional quotes to create a valid format.GET DIAGNOSTICS my_count = ROW_COUNT;END;$BODY$  LANGUAGE plpgsql VOLATILE;

This uses a number of pretty advanced features. If anybody is actually interested and needs an explanation, leave a comment to this post and I will elaborate.

2) Special case

If you can guarantee that '~' is only present in the delimiter '~,~', then you can go ahead with a plain COPY in this special case. Just treat ',' in '~,~' as an additional columns.Say, your table looks like this:

CREATE TABLE foo (a int, b int, c int);

Then you can (in one transaction):

CREATE TEMP TABLE foo_tmp ON COMMIT DROP ( a int, tmp1 "char",b int, tmp2 "char",c int);COPY foo_tmp FROM '\path\to\file.txt' WITH DELIMITER AS '~';ALTER TABLE foo_tmp DROP COLUMN tmp1;ALTER TABLE foo_tmp DROP COLUMN tmp2;INSERT INTO foo SELECT * FROM foo_tmp;