Constructing column from different individual fields in same row.

[Alex Martinoff]

I'm wondering if it's possible to have a query construct a column
where the value of the column at each row is taken from another field
in that same row. For example, suppose you have a table like:

create table users (
uid serial,
nickname varchar(20),
realname varchar(30),
prefname int2,
primary key (uid)
);

insert into users (nickname, realname, prefname)
values ('Stevo', 'Steve Sullivan', 1);

insert into users (nickname, realname, prefname)
values ('Johnny Boy', 'John Fisk', 2);
A prefname of 1 means the user prefers their nickname, while 2 means
they prefer their realname.

Is there a query I can perform that would return:

uid | Preferred Name
-----+----------------
1 | Stevo
2 | John Fisk

Or, is it necessary to waste storage by making prefname varchar(30)
and then duplicating the preferred name into the prefname field?

Thanks for any answers.

[Bruno Wolff III]

On Tue, Sep 09, 2003 at 21:02:21 -0700,
Alex Martinoff <fr*********@yahoo.com> wrote:

I'm wondering if it's possible to have a query construct a column
where the value of the column at each row is taken from another field
in that same row. For example, suppose you have a table like:

You can use CASE to do this.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Mike Mascari]

Alex Martinoff wrote:

I'm wondering if it's possible to have a query construct a column
where the value of the column at each row is taken from another field
in that same row. For example, suppose you have a table like:

create table users (
uid serial,
nickname varchar(20),
realname varchar(30),
prefname int2,
primary key (uid)
);

insert into users (nickname, realname, prefname)
values ('Stevo', 'Steve Sullivan', 1);

insert into users (nickname, realname, prefname)
values ('Johnny Boy', 'John Fisk', 2);
A prefname of 1 means the user prefers their nickname, while 2 means
they prefer their realname.

Is there a query I can perform that would return:

uid | Preferred Name
-----+----------------
1 | Stevo
2 | John Fisk

SELECT uid, (CASE WHEN prefname = 1 THEN nickname ELSE realname END)
AS "Preferred Name"
FROM users
WHERE ...

HTH,

Mike Mascari
ma*****@mascari.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Bruce Momjian]

Bruno Wolff III wrote:

On Tue, Sep 09, 2003 at 21:02:21 -0700,
Alex Martinoff <fr*********@yahoo.com> wrote:

I'm wondering if it's possible to have a query construct a column
where the value of the column at each row is taken from another field
in that same row. For example, suppose you have a table like:

You can use CASE to do this.

You can also use UNION:

SELECT col1 ...
UNION
SELECT col2 ...

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Mike Mascari]

Alex Martinoff wrote:

I'm wondering if it's possible to have a query construct a column
where the value of the column at each row is taken from another field
in that same row. For example, suppose you have a table like:

create table users (
uid serial,
nickname varchar(20),
realname varchar(30),
prefname int2,
primary key (uid)
);

insert into users (nickname, realname, prefname)
values ('Stevo', 'Steve Sullivan', 1);

insert into users (nickname, realname, prefname)
values ('Johnny Boy', 'John Fisk', 2);
A prefname of 1 means the user prefers their nickname, while 2 means
they prefer their realname.

Is there a query I can perform that would return:

uid | Preferred Name
-----+----------------
1 | Stevo
2 | John Fisk

SELECT uid, (CASE WHEN prefname = 1 THEN nickname ELSE realname END)
AS "Preferred Name"
FROM users
WHERE ...

HTH,

Mike Mascari
ma*****@mascari.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org