pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsql can i substitute
10+ messages / 5 participants
[nested] [flat]
* sql can i substitute
@ 2004-12-17 11:25 Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 11:45 ` Re: [despammed] sql can i substitute Andreas Kretschmer <akretschmer@despammed.com>
2004-12-17 12:42 ` Re: sql can i substitute D'Arcy J.M. Cain <darcy@druid.net>
2004-12-17 14:56 ` Re: sql can i substitute Bruno Wolff III <bruno@wolff.to>
0 siblings, 3 replies; 10+ messages in thread
From: Kenneth Gonsalves @ 2004-12-17 11:25 UTC (permalink / raw)
To: pgsql-sql
hi
table:
name varchar(10)
fruit integer
i want to write an sql statement like this:
select fruit from table
which should return 'good' if fruit = 1 and 'bad' if fruit =2 and 'rotten' if
fruit =3
can it be done?
kg
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [despammed] sql can i substitute
2004-12-17 11:25 sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-12-17 11:45 ` Andreas Kretschmer <akretschmer@despammed.com>
2004-12-17 12:18 ` Re: [despammed] sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
2 siblings, 1 reply; 10+ messages in thread
From: Andreas Kretschmer @ 2004-12-17 11:45 UTC (permalink / raw)
To: pgsql-sql
am 17.12.2004, um 16:55:45 +0530 mailte Kenneth Gonsalves folgendes:
> hi
>
> table:
>
> name varchar(10)
> fruit integer
>
> i want to write an sql statement like this:
>
> select fruit from table
>
> which should return 'good' if fruit = 1 and 'bad' if fruit =2 and 'rotten' if
> fruit =3
>
> can it be done?
Okay:
test=# select * from fruit;
name | fruit
--------+-------
Apple | 1
Banana | 2
Cherry | 3
(3 Zeilen)
test=# select name, fruit, case
test-# when fruit = 1 then 'good'
test-# when fruit = 2 then 'bad'
test-# when fruit = 3 then 'rotten' end from fruit;
name | fruit | case
--------+-------+--------
Apple | 1 | good
Banana | 2 | bad
Cherry | 3 | rotten
(3 Zeilen)
Please read
http://www.postgresql.org/docs/7.4/interactive/functions-conditional.html#AEN11381
Regards, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Tel. NL Heynitz: 035242/47212
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [despammed] sql can i substitute
2004-12-17 11:25 sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 11:45 ` Re: [despammed] sql can i substitute Andreas Kretschmer <akretschmer@despammed.com>
@ 2004-12-17 12:18 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 12:24 ` Re: [despammed] sql can i substitute Andreas Kretschmer <akretschmer@despammed.com>
0 siblings, 1 reply; 10+ messages in thread
From: Kenneth Gonsalves @ 2004-12-17 12:18 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@despammed.com>; pgsql-sql
On Friday 17 December 2004 05:15 pm, Andreas Kretschmer wrote:
> test=# select * from fruit;
> name | fruit
> --------+-------
> Apple | 1
> Banana | 2
> Cherry | 3
> (3 Zeilen)
>
>
> test=# select name, fruit, case
> test-# when fruit = 1 then 'good'
> test-# when fruit = 2 then 'bad'
> test-# when fruit = 3 then 'rotten' end from fruit;
> name | fruit | case
> --------+-------+--------
> Apple | 1 | good
> Banana | 2 | bad
> Cherry | 3 | rotten
> (3 Zeilen)
thanks - opened up a whole new world for me. One more thing, the values come
under the column 'case', can i avoid having the column 'case' and get the
'good', 'bad' and 'rotten' under the column 'fruit'?
kg
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [despammed] sql can i substitute
2004-12-17 11:25 sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 11:45 ` Re: [despammed] sql can i substitute Andreas Kretschmer <akretschmer@despammed.com>
2004-12-17 12:18 ` Re: [despammed] sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-12-17 12:24 ` Andreas Kretschmer <akretschmer@despammed.com>
2004-12-17 13:01 ` Re: [despammed] sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
0 siblings, 1 reply; 10+ messages in thread
From: Andreas Kretschmer @ 2004-12-17 12:24 UTC (permalink / raw)
To: pgsql-sql
am 17.12.2004, um 17:48:15 +0530 mailte Kenneth Gonsalves folgendes:
>
> thanks - opened up a whole new world for me. One more thing, the values come
> under the column 'case', can i avoid having the column 'case' and get the
> 'good', 'bad' and 'rotten' under the column 'fruit'?
Yes, simple:
test=# select name, case
test-# when fruit = 1 then 'good'
test-# when fruit = 2 then 'bad'
test-# when fruit = 3 then 'rotten' end as fruit from fruit;
name | fruit
--------+--------
Apple | good
Banana | bad
Cherry | rotten
(3 Zeilen)
Regards, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Tel. NL Heynitz: 035242/47212
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [despammed] sql can i substitute
2004-12-17 11:25 sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 11:45 ` Re: [despammed] sql can i substitute Andreas Kretschmer <akretschmer@despammed.com>
2004-12-17 12:18 ` Re: [despammed] sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 12:24 ` Re: [despammed] sql can i substitute Andreas Kretschmer <akretschmer@despammed.com>
@ 2004-12-17 13:01 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 14:08 ` Re: [despammed] sql can i substitute Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 10+ messages in thread
From: Kenneth Gonsalves @ 2004-12-17 13:01 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@despammed.com>; pgsql-sql
On Friday 17 December 2004 05:54 pm, Andreas Kretschmer wrote:
> Yes, simple:
>
> test=# select name, case
> test-# when fruit = 1 then 'good'
> test-# when fruit = 2 then 'bad'
> test-# when fruit = 3 then 'rotten' end as fruit from fruit;
can one do the same thing for an 'insert' statement?
kg
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [despammed] sql can i substitute
2004-12-17 11:25 sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 11:45 ` Re: [despammed] sql can i substitute Andreas Kretschmer <akretschmer@despammed.com>
2004-12-17 12:18 ` Re: [despammed] sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 12:24 ` Re: [despammed] sql can i substitute Andreas Kretschmer <akretschmer@despammed.com>
2004-12-17 13:01 ` Re: [despammed] sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-12-17 14:08 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 10+ messages in thread
From: Achilleus Mantzios @ 2004-12-17 14:08 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: Andreas Kretschmer <akretschmer@despammed.com>; pgsql-sql
O Kenneth Gonsalves Ýãñáøå óôéò Dec 17, 2004 :
> On Friday 17 December 2004 05:54 pm, Andreas Kretschmer wrote:
>
> > Yes, simple:
> >
> > test=# select name, case
> > test-# when fruit = 1 then 'good'
> > test-# when fruit = 2 then 'bad'
> > test-# when fruit = 3 then 'rotten' end as fruit from fruit;
>
> can one do the same thing for an 'insert' statement?
its an expression, so yes,
INSERT INTO foo3 VALUES (2,case when 't' then 'bar' else 'foo' end);
but whats the point?
>
> kg
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
--
-Achilleus
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: sql can i substitute
2004-12-17 11:25 sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-12-17 12:42 ` D'Arcy J.M. Cain <darcy@druid.net>
2004-12-17 12:52 ` Re: sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
2 siblings, 1 reply; 10+ messages in thread
From: D'Arcy J.M. Cain @ 2004-12-17 12:42 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: pgsql-sql
On Fri, 17 Dec 2004 16:55:45 +0530
Kenneth Gonsalves <lawgon@thenilgiris.com> wrote:
> i want to write an sql statement like this:
>
> select fruit from table
>
> which should return 'good' if fruit = 1 and 'bad' if fruit =2 and
> 'rotten' if fruit =3
An alternative to Andreas' suggestion would be to create a simple lookup
table and join them. This is good if the real life example can get
larger and/or the list can change and you don't want to modify code
every time it does.
[totally made up output]
fstate_id | fstate_name
----------+-------------
1 | good
2 | bad
3 | rotten
SELECT fstate.fstate_name AS "Fruit state"
FROM table, fstate
WHERE table.fstate_id = fstate.fstate_id;
Now you can easily add another state:
INSERT INTO fstate VALUES (4, 'smelly');
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: sql can i substitute
2004-12-17 11:25 sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 12:42 ` Re: sql can i substitute D'Arcy J.M. Cain <darcy@druid.net>
@ 2004-12-17 12:52 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 15:01 ` Re: sql can i substitute Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 10+ messages in thread
From: Kenneth Gonsalves @ 2004-12-17 12:52 UTC (permalink / raw)
To: D'Arcy J.M. Cain <darcy@druid.net>; +Cc: pgsql-sql
On Friday 17 December 2004 06:12 pm, D'Arcy J.M. Cain wrote:
> An alternative to Andreas' suggestion would be to create a simple lookup
> table and join them. This is good if the real life example can get
> larger and/or the list can change and you don't want to modify code
> every time it does.
yes, but in this case the list wont change, and i'm trying to port mysql to
pgsql without disturbing as far as possible the mysql queries. basically
replacing some 'set' and 'enum' datatypes - one table has six of thes, and
adding six tables is not on
kg
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: sql can i substitute
2004-12-17 11:25 sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 12:42 ` Re: sql can i substitute D'Arcy J.M. Cain <darcy@druid.net>
2004-12-17 12:52 ` Re: sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-12-17 15:01 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 10+ messages in thread
From: Bruno Wolff III @ 2004-12-17 15:01 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: D'Arcy J.M. Cain <darcy@druid.net>; pgsql-sql
On Fri, Dec 17, 2004 at 18:22:48 +0530,
Kenneth Gonsalves <lawgon@thenilgiris.com> wrote:
> On Friday 17 December 2004 06:12 pm, D'Arcy J.M. Cain wrote:
>
> > An alternative to Andreas' suggestion would be to create a simple lookup
> > table and join them. This is good if the real life example can get
> > larger and/or the list can change and you don't want to modify code
> > every time it does.
>
> yes, but in this case the list wont change, and i'm trying to port mysql to
> pgsql without disturbing as far as possible the mysql queries. basically
> replacing some 'set' and 'enum' datatypes - one table has six of thes, and
> adding six tables is not on
You might be better off using domains and a constraint to implement
set types. Storing numbers instead of names will save a little space,
but unless you are having some problems with resources, using the strings
directly will be simpler.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: sql can i substitute
2004-12-17 11:25 sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-12-17 14:56 ` Bruno Wolff III <bruno@wolff.to>
2 siblings, 0 replies; 10+ messages in thread
From: Bruno Wolff III @ 2004-12-17 14:56 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: pgsql-sql
On Fri, Dec 17, 2004 at 16:55:45 +0530,
Kenneth Gonsalves <lawgon@thenilgiris.com> wrote:
> hi
>
> table:
>
> name varchar(10)
> fruit integer
>
> i want to write an sql statement like this:
>
> select fruit from table
>
> which should return 'good' if fruit = 1 and 'bad' if fruit =2 and 'rotten' if
> fruit =3
>
> can it be done?
Yes; use a CASE statement.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2004-12-17 15:01 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-17 11:25 sql can i substitute Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 11:45 ` Andreas Kretschmer <akretschmer@despammed.com>
2004-12-17 12:18 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 12:24 ` Andreas Kretschmer <akretschmer@despammed.com>
2004-12-17 13:01 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 14:08 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-12-17 12:42 ` D'Arcy J.M. Cain <darcy@druid.net>
2004-12-17 12:52 ` Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-12-17 15:01 ` Bruno Wolff III <bruno@wolff.to>
2004-12-17 14:56 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox