Re: " " around fields with psql

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(dot)clark(at)netwolves(dot)com
http://www.netwolves.com

On Fri, Feb 10, 2012 at 11:26 AM, Steve Clark <sclark(at)netwolves(dot)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 02/10/2012 02:12 PM, Scott Marlowe wrote:
> On Fri, Feb 10, 2012 at 11:26 AM, Steve Clark<sclark(at)netwolves(dot)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(dot)clark(at)netwolves(dot)com
http://www.netwolves.com

On Fri, Feb 10, 2012 at 1:33 PM, Steve Clark <sclark(at)netwolves(dot)com> wrote:
> On 02/10/2012 02:12 PM, Scott Marlowe wrote:
>
> On Fri, Feb 10, 2012 at 11:26 AM, Steve Clark <sclark(at)netwolves(dot)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(at)netwolves(dot)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(at)xnet(dot)co(dot)nz> wrote:
> On 2012-02-10, Steve Clark <sclark(at)netwolves(dot)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 02/13/2012 02:13 PM, Merlin Moncure wrote:
> On Sat, Feb 11, 2012 at 2:03 AM, Jasen Betts<jasen(at)xnet(dot)co(dot)nz> wrote:
>> On 2012-02-10, Steve Clark<sclark(at)netwolves(dot)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(dot)clark(at)netwolves(dot)com
http://www.netwolves.com