From: | Raymond O'Donnell <rod(at)iol(dot)ie> |
---|---|
To: | arnaud gaboury <arnaud(dot)gaboury(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: query from two tables & concat the result |
Date: | 2016-02-03 12:51:57 |
Message-ID: | 56B1F7ED.8010603@iol.ie |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 03/02/2016 12:18, arnaud gaboury wrote:
> $ psql -V
> psql (PostgreSQL) 9.4.5
>
> I am quite a newbie in psql. I am setting an email server and need to
> create then query psql tables to get some information: mainly email
> address and mail directory.
>
> For now I have created two tables this way. Both tables are in same
> database and schema. I only insert one row in each.
>
> 1- the first one is just a list of all my domains.
>
> -----------------------------------------
> CREATE TABLE email.domain (
> id SERIAL,
> domain TEXT NOT NULL,
> PRIMARY KEY (id)
> );
> ---------------------------------
>
> 2- second is a list of users
>
> --------------------------------------------------
> CREATE TABLE email.mailusers (
> id SERIAL PRIMARY KEY,
> domain_id INTEGER DEFAULT 1, <<-- This ref to email.domain id=1
> password TEXT NOT NULL,
> username TEXT UNIQUE NOT NULL,
> created TIMESTAMP WITH TIME ZONE DEFAULT now();
> ------------------------------------
>
> 3- Then I added a constraint:
>
> ---------------------------------
> ALTER TABLE email.mailusers
> ADD CONSTRAINT mailuser_domain_id_fkey
> FOREIGN KEY (domain_id)
> REFERENCES email.domain(id)
> ON UPDATE CASCADE
> ON DELETE RESTRICT;
> -------------------------------------
>
>
> Then I need to retrieve from psql to postfix this parameter value:
> - email adress .
> The email address is obviously something like a a concat (username,'@',domain).
You can do a simple join between the tables (the string concatenation
operator is ||):
select u.username || '@' || d.domain as email_address
from mailusers u inner join domain d on (u.domain_id = d.domain_id)
where .....
Note that "domain" is a reserved work, so you'll probably have either to
double-quote it or else rename that column to something else.
Also, you really don't need so many id-type columns... given that the
domain and username are presumably unique in their respective tables,
having additional serial and domain_id columns seems like overkill. Why
not ditch them and use the domain name and username as the primary keys?
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie
From | Date | Subject | |
---|---|---|---|
Next Message | Ricardo Ramírez | 2016-02-03 12:55:29 | Re: query from two tables & concat the result |
Previous Message | FarjadFarid(ChkNet) | 2016-02-03 12:28:19 | Re: SSD gives disappointing speed up on OSX |