Thread: query from two tables & concat the result
$ 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).
My first idea is to create a view (or materialized view ?) following
the principle described here[1]
create view postfix_virtual as
select userid, userid as address from users
union all
select userid, address from virtual;
, but I am a little lost when it comes to the UNION stuff (shall I use it ?).
Second possibility would be to create a modified query similar to this[2]:
query = SELECT concat(username,'@',domain) as email FROM users WHERE
username='%s'
From now, I am able to create a view like this:
---------------------------------------------
CREATE VIEW email_address AS
SELECT * FROM
(SELECT username
FROM email.mailusers
WHERE id=2)a,
(SELECT domain
FROM email.domain
WHERE id=1)b;
---------------------------------
I get a table:
username | domain
---------------------+--------------------
myuser.name | mydomain.com
That's fine, but it is far from being generic and satisfying.
Thank you for help and advises.
[1]http://wiki2.dovecot.org/HowTo/DovecotPostgresql/
[2]https://blog.za3k.com/installing-email-with-postfix-and-dovecot/
google.com/+arnaudgabourygabx
On Wed, Feb 3, 2016 at 1:18 PM, arnaud gaboury <arnaud.gaboury@gmail.com> 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). > > My first idea is to create a view (or materialized view ?) following > the principle described here[1] > > create view postfix_virtual as > select userid, userid as address from users > union all > select userid, address from virtual; > > , but I am a little lost when it comes to the UNION stuff (shall I use it ?). > > Second possibility would be to create a modified query similar to this[2]: > > query = SELECT concat(username,'@',domain) as email FROM users WHERE > username='%s' > > From now, I am able to create a view like this: > > --------------------------------------------- > CREATE VIEW email_address AS > SELECT * FROM > (SELECT username > FROM email.mailusers > WHERE id=2)a, > (SELECT domain > FROM email.domain > WHERE id=1)b; > --------------------------------- > I get a table: > username | domain > ---------------------+-------------------- > myuser.name | mydomain.com > > That's fine, but it is far from being generic and satisfying. > > Thank you for help and advises. > > [1]http://wiki2.dovecot.org/HowTo/DovecotPostgresql/ > [2]https://blog.za3k.com/installing-email-with-postfix-and-dovecot/ > EDIT: I deleted my first INSERT in table email.mailusers this way: DELETE * FROM email.mailusers , and now the id is set to 2. I would prefer having the id reset to 1. What is the correct way to get back id to 1 ? -- google.com/+arnaudgabourygabx
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@iol.ie
For resetting the id you may want to take a look at the sequence manipulation functions [1]
Regards,
Ricardo
[1]http://www.postgresql.org/docs/current/static/functions-sequence.html
On Wed, Feb 3, 2016, 06:26 arnaud gaboury <arnaud.gaboury@gmail.com> wrote:
On Wed, Feb 3, 2016 at 1:18 PM, arnaud gaboury <arnaud.gaboury@gmail.com> 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).
>
> My first idea is to create a view (or materialized view ?) following
> the principle described here[1]
>
> create view postfix_virtual as
> select userid, userid as address from users
> union all
> select userid, address from virtual;
>
> , but I am a little lost when it comes to the UNION stuff (shall I use it ?).
>
> Second possibility would be to create a modified query similar to this[2]:
>
> query = SELECT concat(username,'@',domain) as email FROM users WHERE
> username='%s'
>
> From now, I am able to create a view like this:
>
> ---------------------------------------------
> CREATE VIEW email_address AS
> SELECT * FROM
> (SELECT username
> FROM email.mailusers
> WHERE id=2)a,
> (SELECT domain
> FROM email.domain
> WHERE id=1)b;
> ---------------------------------
> I get a table:
> username | domain
> ---------------------+--------------------
> myuser.name | mydomain.com
>
> That's fine, but it is far from being generic and satisfying.
>
> Thank you for help and advises.
>
> [1]http://wiki2.dovecot.org/HowTo/DovecotPostgresql/
> [2]https://blog.za3k.com/installing-email-with-postfix-and-dovecot/
>
EDIT: I deleted my first INSERT in table email.mailusers this way:
DELETE * FROM email.mailusers
, and now the id is set to 2. I would prefer having the id reset to 1.
What is the correct way to get back id to 1 ?
--
google.com/+arnaudgabourygabx
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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
On 03/02/2016 13:11, arnaud gaboury wrote: > 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? In the join condition, replace "d.domain.id" with "d.id" (partly my mistake, I missed that the column is called "id" and not "domain_id" in the domains table). Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Wed, Feb 3, 2016 at 2:19 PM, Raymond O'Donnell <rod@iol.ie> wrote:
> On 03/02/2016 13:11, arnaud gaboury wrote:
>
>> 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?
>
> In the join condition, replace "d.domain.id" with "d.id" (partly my
> mistake, I missed that the column is called "id" and not "domain_id" in
> the domains table).
>
> Ray.
I noticed your mistake but made a wrong change myself :-(
Now working perfectly:
thetradinghall=> SELECT u.username ||'@'||d.domain as email_address
FROM email.mailusers u
INNER JOIN
email.domainlist d
ON
(u.domain_id=d.id);
email_address
-----------------------------------
arnaud.gaboury@thetradinghall.com
(1 row)
-------------------------------------
As for the cleaning of ID, I dropped id and changed both primary keys.
Thank you so much for your prompt answer and help.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod@iol.ie
--
google.com/+arnaudgabourygabx
On Wed, Feb 3, 2016 at 1:55 PM, Ricardo Ramírez <ricardojfr@gmail.com> wrote: > For resetting the id you may want to take a look at the sequence > manipulation functions [1] Problem has been solved by removing the id column. > > Regards, > Ricardo > > [1]http://www.postgresql.org/docs/current/static/functions-sequence.html > > > On Wed, Feb 3, 2016, 06:26 arnaud gaboury <arnaud.gaboury@gmail.com> wrote: >> >> On Wed, Feb 3, 2016 at 1:18 PM, arnaud gaboury <arnaud.gaboury@gmail.com> >> 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). >> > >> > My first idea is to create a view (or materialized view ?) following >> > the principle described here[1] >> > >> > create view postfix_virtual as >> > select userid, userid as address from users >> > union all >> > select userid, address from virtual; >> > >> > , but I am a little lost when it comes to the UNION stuff (shall I use >> > it ?). >> > >> > Second possibility would be to create a modified query similar to >> > this[2]: >> > >> > query = SELECT concat(username,'@',domain) as email FROM users WHERE >> > username='%s' >> > >> > From now, I am able to create a view like this: >> > >> > --------------------------------------------- >> > CREATE VIEW email_address AS >> > SELECT * FROM >> > (SELECT username >> > FROM email.mailusers >> > WHERE id=2)a, >> > (SELECT domain >> > FROM email.domain >> > WHERE id=1)b; >> > --------------------------------- >> > I get a table: >> > username | domain >> > ---------------------+-------------------- >> > myuser.name | mydomain.com >> > >> > That's fine, but it is far from being generic and satisfying. >> > >> > Thank you for help and advises. >> > >> > [1]http://wiki2.dovecot.org/HowTo/DovecotPostgresql/ >> > [2]https://blog.za3k.com/installing-email-with-postfix-and-dovecot/ >> > >> EDIT: I deleted my first INSERT in table email.mailusers this way: >> >> DELETE * FROM email.mailusers >> >> , and now the id is set to 2. I would prefer having the id reset to 1. >> What is the correct way to get back id to 1 ? >> >> >> >> -- >> >> google.com/+arnaudgabourygabx >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general -- google.com/+arnaudgabourygabx
On 03/02/2016 13:57, arnaud gaboury wrote: > On Wed, Feb 3, 2016 at 2:19 PM, Raymond O'Donnell <rod@iol.ie> wrote: >> On 03/02/2016 13:11, arnaud gaboury wrote: >> >>> 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? >> >> In the join condition, replace "d.domain.id" with "d.id" (partly my >> mistake, I missed that the column is called "id" and not "domain_id" in >> the domains table). >> >> Ray. > > I noticed your mistake but made a wrong change myself :-( > Now working perfectly: > > thetradinghall=> SELECT u.username ||'@'||d.domain as email_address > FROM email.mailusers u > INNER JOIN > email.domainlist d > ON > (u.domain_id=d.id); > > email_address > ----------------------------------- > arnaud.gaboury@thetradinghall.com > (1 row) > ------------------------------------- > > As for the cleaning of ID, I dropped id and changed both primary keys. > Thank you so much for your prompt answer and help. You're very welcome - glad to be able to help. R. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
>> >> thetradinghall=> SELECT u.username ||'@'||d.domain as email_address >> FROM email.mailusers u >> INNER JOIN >> email.domainlist d >> ON >> (u.domain_id=d.id); >> >> email_address >> ----------------------------------- >> arnaud.gaboury@thetradinghall.com >> (1 row) >> ------------------------------------- >> >> As for the cleaning of ID, I dropped id and changed both primary keys. >> Thank you so much for your prompt answer and help. In fact I kept the id for table domainlist (changed the name accordingly your advise). If I remove the id column, I will not be able anymore to do the above SELECT , no? The condition (u.domain_id=d.id) will no more be possible. Am I wrong? > > You're very welcome - glad to be able to help. > > R. > > > -- > Raymond O'Donnell :: Galway :: Ireland > rod@iol.ie -- google.com/+arnaudgabourygabx
On 03/02/2016 14:05, arnaud gaboury wrote: >>> >>> thetradinghall=> SELECT u.username ||'@'||d.domain as email_address >>> FROM email.mailusers u >>> INNER JOIN >>> email.domainlist d >>> ON >>> (u.domain_id=d.id); >>> >>> email_address >>> ----------------------------------- >>> arnaud.gaboury@thetradinghall.com >>> (1 row) >>> ------------------------------------- >>> >>> As for the cleaning of ID, I dropped id and changed both primary keys. >>> Thank you so much for your prompt answer and help. > > In fact I kept the id for table domainlist (changed the name > accordingly your advise). If I remove the id column, I will not be > able anymore to do the above SELECT , no? > The condition (u.domain_id=d.id) will no more be possible. > > Am I wrong? You're right - you'll need to use the domain name as the foreign key instead. So your tables will look like this: CREATE TABLE domains ( domain_name text not null primary key, .... ); CREATE TABLE mailusers ( username text not null, password text not null, domain_name text not null, created timestamp with time zone not null default now(), .... constraint users_pk primary key (username, domain_name), constraint users_domains_fk foreign key (domain_name) references domains(domain_name) ); And then your query would look something like this: select u.username ||'@'||d.domain as email_address from mailusers u inner join domains d on (u.domain_name = d.domain_name) ... HTH, Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Wed, Feb 3, 2016 at 8:19 PM, Raymond O'Donnell <rod@iol.ie> wrote: > On 03/02/2016 14:05, arnaud gaboury wrote: >>>> >>>> thetradinghall=> SELECT u.username ||'@'||d.domain as email_address >>>> FROM email.mailusers u >>>> INNER JOIN >>>> email.domainlist d >>>> ON >>>> (u.domain_id=d.id); >>>> >>>> email_address >>>> ----------------------------------- >>>> arnaud.gaboury@thetradinghall.com >>>> (1 row) >>>> ------------------------------------- >>>> >>>> As for the cleaning of ID, I dropped id and changed both primary keys. >>>> Thank you so much for your prompt answer and help. >> >> In fact I kept the id for table domainlist (changed the name >> accordingly your advise). If I remove the id column, I will not be >> able anymore to do the above SELECT , no? >> The condition (u.domain_id=d.id) will no more be possible. >> >> Am I wrong? > > You're right - you'll need to use the domain name as the foreign key > instead. So your tables will look like this: > > CREATE TABLE domains ( > domain_name text not null primary key, > .... > ); > > CREATE TABLE mailusers ( > username text not null, > password text not null, > domain_name text not null, > created timestamp with time zone not null default now(), > .... > constraint users_pk primary key (username, domain_name), > constraint users_domains_fk foreign key (domain_name) > references domains(domain_name) > ); > > And then your query would look something like this: > > select u.username ||'@'||d.domain as email_address > from mailusers u > inner join domains d on (u.domain_name = d.domain_name) Very good. I changed tables accordingly. No more the id columns now. Thank you so much. > ... > > > HTH, > > Ray. > > -- > Raymond O'Donnell :: Galway :: Ireland > rod@iol.ie -- google.com/+arnaudgabourygabx