Re: query from two tables & concat the result

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