Question 350930 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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;
Tags:

2

Answers


  1. 0

    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');
    Login or Signup to reply.
  2. 0

    I think, the answer given by @LaurenzAlbe, fully meets your task.
    For interest, I will offer another example for copying with direct conversion without an intermediate table.
    See this also

    COPY (select string_agg(value,';' order by ordinality)
      from
        (select test_table.id,t.*
         from test, lateral jsonb_each_text(to_jsonb(test_table)) with ordinality as t
         where ....
        )x
      group by id
      order by id)
TO '/dir/outfile' (FORMAT 'csv', DELIMITER E't');;

    However, the answer is more about concat(….) than about copy …

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search