Thread: " " around fields with psql

On Fri, Feb 10, 2012 at 11:26 AM, Steve Clark <sclark@netwolves.com> wrote:
> Hello,
>
> Is there a way with psql to get column output to be
> "data1","data2",...,"datan"
>
> I tried -F "," but that left off the first and last quote.
>
> I can't seem to find a way in the man page.

Well, you can do it yourself kinda like this:

select '""||field1||'", "||field2||'" from sometable where yada.

On Fri, Feb 10, 2012 at 11:26 AM, Steve Clark <sclark@netwolves.com> wrote:

Hello,

Is there a way with psql to get column output to be
"data1","data2",...,"datan"

I tried -F "," but that left off the first and last quote.

I can't seem to find a way in the man page.

Well, you can do it yourself kinda like this:

select '""||field1||'", "||field2||'" from sometable where yada.

On Fri, Feb 10, 2012 at 1:33 PM, Steve Clark <sclark@netwolves.com> wrote:
> On 02/10/2012 02:12 PM, Scott Marlowe wrote:
>
> On Fri, Feb 10, 2012 at 11:26 AM, Steve Clark <sclark@netwolves.com> wrote:
>
> Hello,
>
> Is there a way with psql to get column output to be
> "data1","data2",...,"datan"
>
> I tried -F "," but that left off the first and last quote.
>
> I can't seem to find a way in the man page.
>
> Well, you can do it yourself kinda like this:
>
> select '""||field1||'", "||field2||'" from sometable where yada.
>
> Ok that will work

for 9.1+ you can use built in format() function for a lot of fields:
select format('"%s", "%s", "%s", "%s"', procpid, usename, waiting,
query_start) from pg_stat_activity;

also with recent postgres you can use hstore to convert virtually any
query as such:
select '"' || array_to_string(avals(hstore(a)), '", "') || '"' from
pg_stat_activity a;

postgres=# select '"' || array_to_string(avals(hstore(q)), '", "') ||
'"' from (select 1 as a,2 as b,3 as c) q;
   ?column?
---------------
 "1", "2", "3"

etc.
merlin

On 2012-02-10, Steve Clark <sclark@netwolves.com> wrote:

> Is there a way with psql to get column output to be
> "data1","data2",...,"datan"

assuming you are trying to be compatible with CSV:

  copy ( your_query_here ) to stdout with csv header ;


--
⚂⚃ 100% natural

On Sat, Feb 11, 2012 at 2:03 AM, Jasen Betts <jasen@xnet.co.nz> wrote:
> On 2012-02-10, Steve Clark <sclark@netwolves.com> wrote:
>
>> Is there a way with psql to get column output to be
>> "data1","data2",...,"datan"
>
> assuming you are trying to be compatible with CSV:
>
>  copy ( your_query_here ) to stdout with csv header ;

yeah -- that's the best way if you want actual csv,  from psql you'd
probably want to do \copy:
postgres=# \copy (select 1, '"', 'ab,c') to stdout csv header;
?column?,?column?,?column?
1,"""","ab,c"

note that per csv rules columns are only required to be quoted to
protect from unambiguous parsing.  also, double quotes in your field
will be escaped.

merlin

On Sat, Feb 11, 2012 at 2:03 AM, Jasen Betts <jasen@xnet.co.nz> wrote:

On 2012-02-10, Steve Clark <sclark@netwolves.com> wrote:

Is there a way with psql to get column output to be
"data1","data2",...,"datan"

assuming you are trying to be compatible with CSV:

 copy ( your_query_here ) to stdout with csv header ;

yeah -- that's the best way if you want actual csv,  from psql you'd
probably want to do \copy:
postgres=# \copy (select 1, '"', 'ab,c') to stdout csv header;
?column?,?column?,?column?
1,"""","ab,c"

note that per csv rules columns are only required to be quoted to
protect from unambiguous parsing.  also, double quotes in your field
will be escaped.

merlin