I want to load the data from a flat file with delimiter "~,~" into a PostgreSQL table. I have tried it as below but looks like there is a restriction for the delimiter. If COPY statement doesn't allow multiple chars for delimiter, is there any alternative to do this?
metadb=# \COPY public.CME_DATA_STAGE_TRANS FROM 'E:\Infor\Outbound_Marketing\7.2.1\EM\metadata\pgtrans.log' WITH DELIMITER AS '~,~' ERROR: COPY delimiter must be a single one-byte character \copy: 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! BEGIN myfile := pg_read_file(datafile, 0, 100000000); -- arbitrary 100 MB max. INSERT INTO public.my_tbl SELECT ('(' || 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;
Not quite sure if you're looking for a postgresql solution or just a general one.
If it were me, I would open up a copy of vim (or gvim) and run the commend
That replaces all "~,~" with "~".
you can use a single character delimiter, open
~,~ with something will not interfere. like