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