On Wed, Feb 3, 2016 at 1:55 PM, Ricardo Ramírez <ricardojfr@xxxxxxxxx> 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@xxxxxxxxx> wrote: >> >> On Wed, Feb 3, 2016 at 1:18 PM, arnaud gaboury <arnaud.gaboury@xxxxxxxxx> >> 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@xxxxxxxxxxxxxx) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general -- google.com/+arnaudgabourygabx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
