On Tue, May 3, 2016 at 5:53 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 05/03/2016 02:27 PM, drum.lu...@gmail.com wrote: > >> >> >> On 4 May 2016 at 01:18, Melvin Davidson <melvin6...@gmail.com >> <mailto:melvin6...@gmail.com>> wrote: >> >> >> >> On Tue, May 3, 2016 at 1:21 AM, David G. Johnston >> <david.g.johns...@gmail.com <mailto:david.g.johns...@gmail.com>> >> wrote: >> >> >> Well.. I don't need to add a constraint if I already have a >> default value, that's right... >> >> >> Wrong >> David J. >> >> >> What you need is a TRIGGER function & TRIGGER that will select and >> assign the next users_code based on company_id. >> I'm not going to write the whole thing for you, but here is part of >> the trigger function logic. >> >> eg: IF NEW.company_id = 1 THEN >> NEW.users_code = NEXTVAL(c1_users_code_seq); >> ELSEIF NEW.company.id <http://NEW.company.id> = 2 THEN >> NEW.users_code = NEXTVAL(c2_users_code_seq); >> ELSEIF NEW.company.id <http://NEW.company.id> = 3 THEN >> NEW.users_code = NEXTVAL(c3_users_code_seq); >> ... >> ... >> ELSE >> < something bad happened because NEW.company_id was >> not valid ? >> END IF; >> >> >> >> >> Do I have to have one sequence peer company_id ? There will be >> thousands.. isn't there a better way to do that? >> > > A sequence just keeps on incrementing. If you want a gapless sequence for > each company that each increment independently of each other then yes you > will need a separate sequence for each. > > For an alternate strategy see this related thread: > > > http://www.postgresql.org/message-id/2926b083-33c9-4648-8635-bc293c70e...@ravnalaska.net > > in particular this link from the thread: > > http://www.postgresql.org/message-id/44e376f6.7010...@seaworthysys.com > > > >> Seriously, get yourself the books I have recommended and study them >> BEFORE you continue attempting to design your database. >> You need a firm understanding of logical design & flow, otherwise >> you will be wasting your time. >> >> >> That's what I'm doing.. Studying.. asking for some help to get a better >> understand.... isn't this the purpose of this mail list? >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >Do I have to have one sequence peer company_id ? There will be thousands.. isn't there a better way to do that? I agree that having thousands of sequences can be hard to manage, especially in a function, but you did not state that fact before, only that you wanted separate sequences for each company. That being said, here is an alternate solution. 1. CREATE TABLE company_seqs (company_id bigint NOT NULL, last_seq bigint NOT NULL, CONSTRAINT company_seqs_pk PRIMARY KEY (company_id) ); 2. Every time you create a new company, you must insert the corresponding company_id and last_seq [which will be 1} into the company_seqs table. 3. Change the trigger function logic to something like below: DECLARE v_seq_num INTEGER; BEGIN SELECT last_seq FROM company_seqs WHERE company_id = NEW.company_id INTO v_seq_num; UPDATE company_seqs SET last_seq = last_seq + 1 WHERE company_id = NEW.company_id; new.users_code = v_seq_num; Now, just a quick comment. As has been said before, wanting a sequence with no gaps for each user in each company is a bit unrealistic and serves no purpose. For example, company_id 3 has 10 users, 1 > 10. What do you do when user 3 leaves and is deleted? As long as you have a unique user_code for each user, it does not matter. >... Studying.. asking for some help to get a better understand.... isn't this the purpose of this mail list? Yes, but at the same time, it is evident that you are trying to design the database before you have a valid understanding of database design. To wit, you are putting the cart before the horse. While this list is here to help you, it is not meant as a DATABASE 101 course. -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
