Create unique sequence per set of rows sharing the same foreign key value
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 per_user_id
FROM   user_article;


A column article with 'some notes' hardly makes sense for your setup. And ordering by it makes even less sense, since the numbers would change with every new article that does not happen to sort last. To get a stable sort order and stable numbers (except for deletes!) use the serial column to determine the sort order.

Details:


Serial numbers per group of rows for compound key