Re: query from two tables & concat the result - Mailing list pgsql-general

On Wed, Feb 3, 2016 at 1:51 PM, Raymond O'Donnell <rod@iol.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@iol.ie



--

google.com/+arnaudgabourygabx