Re: query from two tables & concat the result

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(dot)gaboury(at)gmail(dot)com> wrote:

> On Wed, Feb 3, 2016 at 1:18 PM, arnaud gaboury <arnaud(dot)gaboury(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>