[SOLVED] How to change the select output delimeter from '|' to ',' on Postgres through command line?

How to change the select output delimeter from '|' to ',' on Postgres through command line?

I am trying to generate a file with insert commands, using information about different servers, databases and tables, through a bash script. I have managed to do it, however a have a problem with formatting.

I would like to get this format:

insert into mytable values('host name',5432,'database name','text sample 00:00:00');

But I am getting this:

insert into mytable values(host name |             5432 | database name          | text sample 00:00:00);

I know I have to change the delimiter, but I have tried it how I know and it hasn't worked.

I added the option -F ',' to my line of code in my bash script like below, but it made no difference.

PGPASSWORD="$pass" /usr/bin/psql -P pager=off -q -t -F ',' -h "$i" -p "$j" -U "$user" -d "$k" -f /appl/my_folder/first_select.sql >> /appl/my_folder/first_result.txt

Here is the select I am using to generate the insert command:

select 'insert into my_table values('||:'HOST',inet_server_port(),current_database(),txt_sample||');' from spec_table;

Any tip on how I could achieve it?

Solution

You should use the format() function to properly escape string literals:

SELECT format(
   'INSERT INTO my_table VALUES (%L, %s, %L, %L)',
   :'HOST',
   :'PORT',
   current_database(),
   txt_sample
) FROM spec_table;