Constructing column from different individual fields in same row.

From: froggle2003(at)yahoo(dot)com (Alex Martinoff)
To: pgsql-general(at)postgresql(dot)org
Subject: Constructing column from different individual fields in same row.
Date: 2003-09-10 04:02:21
Message-ID: 34f50f10.0309092002.426246a4@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jan Wieck 2003-09-10 05:31:59 Re: Replaceing records
Previous Message Tom Lane 2003-09-10 03:41:05 Re: How to convert a UnixTimestamp to a PostgreSQL date without using ::abstime ?