Thread: " " around fields with psql

" " around fields with psql

From
Steve Clark
Date:
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.

Thanks,
--
Stephen Clark
NetWolves
Director of Technology
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.clark@netwolves.com
http://www.netwolves.com

Re: " " around fields with psql

From
Scott Marlowe
Date:
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.

Re: " " around fields with psql

From
Steve Clark
Date:
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

Thanks,

--
Stephen Clark
NetWolves
Director of Technology
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.clark@netwolves.com
http://www.netwolves.com

Re: " " around fields with psql

From
Merlin Moncure
Date:
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

Re: " " around fields with psql

From
Jasen Betts
Date:
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

Re: " " around fields with psql

From
Merlin Moncure
Date:
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

Re: " " around fields with psql

From
Steve Clark
Date:
On 02/13/2012 02:13 PM, Merlin Moncure wrote:
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

Thanks to all that replied.

--
Stephen Clark
NetWolves
Director of Technology
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.clark@netwolves.com
http://www.netwolves.com