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@xxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
