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.gabo...@gmail.com> wrote: > On Wed, Feb 3, 2016 at 1:18 PM, arnaud gaboury <arnaud.gabo...@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 >
