query from two tables & concat the result

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 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

For resetting the id you may want to take a look at the sequence
manipulation functions [1]http://www.postgresql.org/docs/current/static/functions-sequence.html

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: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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
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 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

--
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: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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
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 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general