From: | arnaud gaboury <arnaud(dot)gaboury(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: query from two tables & concat the result |
Date: | 2016-02-03 13:11:38 |
Message-ID: | CAK1hC9sm9XrzX6qmCG9ZSG4if=PrE_ug+dp8g2jG2_6k6nkBRA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Feb 3, 2016 at 1:51 PM, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:
> 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 .....
Hum hum...
------------------------------------------------------
SELECT u.username ||'@'||d.domain as email_address
FROM email.mailusers u
INNER JOIN
email.domain d
ON
(u.domain_id=d.domain.id)
WHERE id=1;
ERROR: missing FROM-clause entry for table "domain"
LINE 6: (u.domain_id=d.domain.id)
--------------------------------------------------------------
What did I wrong following your solution?
I found this, but again, it is not the expected result, even if not far:
--------------------------------------------------------
SELECT username, domain_id FROM email.mailusers
INNER JOIN
email.domain
ON
email.mailusers.domain_id = email.domain.id;
username | domain_id
---------------------+-----------
MyUser.name | 1
------------------------------------------------------
>
> 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?
Thank you very much for these hints, I will follow your advises. KISS
principles are always good
>
> Ray.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod(at)iol(dot)ie
--
From | Date | Subject | |
---|---|---|---|
Next Message | Raymond O'Donnell | 2016-02-03 13:19:07 | Re: query from two tables & concat the result |
Previous Message | Ricardo Ramírez | 2016-02-03 12:55:29 | Re: query from two tables & concat the result |