New answers tagged sequence
Final code: (It works) ALTER TABLE public.companies ADD COLUMN client_code_increment integer; ALTER TABLE public.companies ALTER COLUMN client_code_increment SET NOT NULL; ALTER TABLE public.companies ALTER COLUMN client_code_increment SET DEFAULT 1000; COMMIT TRANSACTION; BEGIN; -- Creating the function CREATE OR REPLACE FUNCTION users_code_seq() ...
Good that you went with the view row_number() solution If you were stuck on hard coding I know this works in MSSQL but have not tested with postgresql insert into table (article, user_id, id) select @article, @user_id, isnull(max(id), 0)+1 as [id] from table where user_id = @user_id
This has been asked many times. The long and the short of it: Don't try it. You'd run into all kinds of problems with concurrency, gaps introduced by deleting rows, etc. Make id a plain serial column and create a VIEW to display a running number per user_id. CREATE VIEW v_user_article AS SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY id) AS ...
This sounds like you need to use a windowing function. something like: SELECT user_id, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY article) FROM [table] Just an additional note, you'd have to make sure you the sequencing is static and you get he same ordering based on the ORDER BY clause in the windowing function.
Top 50 recent answers are included