Search code examples
postgresqlreplacecastingdelimiterrecord

Convert record to string with custom delimiter

I can convert record to string by just doing myrecord::text. My problem is that my record has text fields which contain commas, and the resulting type cast text is comma separated. Thus, I cannot separate the resulting text in a good way.

Solution would be to define a custom delimiter when casting record to text (for example tab or semicolon). How can I achieve this?

Googling gives me answers like use functions string_agg, unnest, array_to_string, etc. None of those functions exist in my installation (I am using PostgreSQL version 12.4 on a Windows machine).

replace(myrecord::text, ',', '\t') puts tab inside of my text fields that should have commas.

Example code:

DECLARE
myrecord RECORD;
BEGIN
FOR myrecord IN
SELECT * FROM mytable
LOOP
RAISE INFO '%',replace(myrecord::text, ',', '\t');
END LOOP;
END;
Solution

  • If you need a tab separated output of the contents of a table, you could use COPY:

    COPY mytable TO '/dir/outfile' (FORMAT 'csv', DELIMITER E'\t');