Postgres Pro
Downloads
Home   >   mailing lists

Thread: Comma delimited results

Comma delimited results

From
"Chad Thompson"
Date:
Is there a way to display all the results of a row as one big comma delimited string?
 
ie.
select * & ","
from mytable
 
results
3,my house, wife, 8
 
 
IOW, I dont want to have to know the column names of mytable.  I just want one big comma delimited result for each row.
 
While Im pretty sure we are venturing outside standard SQL, I would like to keep it as close as possible.
 
Thanks
Chad

Re: Comma delimited results

From
Tom Lane
Date:
"Chad Thompson" <chad@weblinkservices.com> writes:
> Is there a way to display all the results of a row as one big comma delimit=
> ed string?

In psql, you can do:

regression=# select * from tenk1 limit 2;
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even |
stringu1| stringu2 | string4 

---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
    8800 |       0 |   0 |    0 |   0 |      0 |       0 |      800 |         800 |      3800 |     8800 |   0 |    1 |
MAAAAA  | AAAAAA   | AAAAxx 
    1891 |       1 |   1 |    3 |   1 |     11 |      91 |      891 |        1891 |      1891 |     1891 | 182 |  183 |
TUAAAA  | BAAAAA   | HHHHxx 
(2 rows)

regression=# \a
Output format is unaligned.
regression=# \f ,
Field separator is ','.
regression=# \t
Showing only tuples.
regression=# select * from tenk1 limit 2;
8800,0,0,0,0,0,0,800,800,3800,8800,0,1,MAAAAA,AAAAAA,AAAAxx
1891,1,1,3,1,11,91,891,1891,1891,1891,182,183,TUAAAA,BAAAAA,HHHHxx
regression=#

Dunno if that's quite what you wanted ... but a little digging in the
psql reference page seems warranted ...

            regards, tom lane

money type

From
"Reshat Sabiq"
Date:
Is there any config setting that can make queries like

Select * from money_field where cost > 25

be valid in postgreSQL? I guess not, but just want to make sure. Since I
want my code to stay the same between Access, Oracle, and postgreSQL,
that leaves me only one option (suggested by docs too): convert currency
types in other DBs into numeric in postgres.

P.S. I guess I don't have an option to select numeric(10, 2) or the like
in pgAdmin, however. It has to be just numeric... Am I right? Is it
possible to alter table afterwards?

Thanks,
r.

altering scale of numeric field

From
"Reshat Sabiq"
Date:
I believe Oracle allows that and it would look something like:
alter table mytable modify (fee numeric(10,2));
Although I haven't tried this in action.

Since there is no modify subcommand, I tried variations like:
alter table mytable alter column fee set numeric(10,2);

but it appears it's not doable in postgres. Any suggestions?

P.S. I do format it programmatically anyway, but it would be nicer in
long run to have it as 2 decimals in the DB too.

Thanks,
Reshat.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Monday, December 23, 2002 2:49 PM
To: Chad Thompson
Cc: pgsql-novice
Subject: Re: [NOVICE] Comma delimited results

"Chad Thompson" <chad@weblinkservices.com> writes:
> Is there a way to display all the results of a row as one big comma
delimit=
> ed string?

In psql, you can do:

regression=# select * from tenk1 limit 2;
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand |
twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 |
string4
---------+---------+-----+------+-----+--------+---------+----------+---
----------+-----------+----------+-----+------+----------+----------+---
------
    8800 |       0 |   0 |    0 |   0 |      0 |       0 |      800 |
800 |      3800 |     8800 |   0 |    1 | MAAAAA   | AAAAAA   | AAAAxx
    1891 |       1 |   1 |    3 |   1 |     11 |      91 |      891 |
1891 |      1891 |     1891 | 182 |  183 | TUAAAA   | BAAAAA   | HHHHxx
(2 rows)

regression=# \a
Output format is unaligned.
regression=# \f ,
Field separator is ','.
regression=# \t
Showing only tuples.
regression=# select * from tenk1 limit 2;
8800,0,0,0,0,0,0,800,800,3800,8800,0,1,MAAAAA,AAAAAA,AAAAxx
1891,1,1,3,1,11,91,891,1891,1891,1891,182,183,TUAAAA,BAAAAA,HHHHxx
regression=#

Dunno if that's quite what you wanted ... but a little digging in the
psql reference page seems warranted ...

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly