set id unique to username

Question

i have a POSTS table in postgres whose schema looks like below

create table posts(postid int ,postname VARCHAR(255) NOT NULL,
 createdby VARCHAR(255) NOT NULL, PRIMARY KEY (postname),
 CONSTRAINT fk_user FOREIGN KEY(createdby) REFERENCES users(username) on DELETE CASCADE);

maybe a noob question but i dont want the postid to be always set by the user, i want it to be incremental to a user. lets say for example i have 2 users each of whom are adding 2 posts,

so i want to store that in db as

postid.    postname.    createdby
1           hi           user1
2           second       user1
1           hi           user2
2           second       user2

is something like this possible for the postid to get autoincremented without me having to insert it that way?

i.e a postid and name can be similar to different users

Does this answer your question? [PostgreSQL Autoincrement](https://stackoverflow.com/questions/787722/postgresql-autoincrement) — maio290, Feb 05 '21 at 19:46
autoincrement keeps on increase the postid, i want it to be autoincrement for each user @maio290 — sai, Feb 05 '21 at 19:54
The way you'd like to post it into your database is a weird approach which doesn't make much sense to me. You have defined postname as primary key, but the values aren't even unique in your example. The most common approach would be to autoincrement the postid and set it as primary key. Or you use a composite key of createdBy and postname (?!?) with its constraints. — maio290, Feb 05 '21 at 20:06

score 1 · Answer 1 · answered Feb 05 '21 at 21:12

create table posts(
    postid int,
    postname VARCHAR(255) NOT NULL,
    createdby VARCHAR(255) NOT NULL,
    PRIMARY KEY (postname));

create function tgf_posts_bi() returns trigger language plpgsql
as $$
declare
    seq_name text;
begin
    seq_name := format('seq_posts_%s', new.createdby);
    execute format('create sequence if not exists %I start with 1', seq_name);
    new.postid := nextval((quote_ident(seq_name))::regclass);
    return new;
end $$;

create trigger tg_posts_bi
before insert on posts
for each row execute procedure tgf_posts_bi();

insert into posts(postname, createdby) values
    ('foo', 'John'),
    ('bar', 'Jack Daniel'),
    ('baz', 'Jack Daniel'),
    ('win', 'John'),
    ('amp', 'John'),
    ('M$ media mplayer', 'Mary');

table posts;

postid | postname         | createdby  
-----: | :--------------- | :----------
     1 | foo              | John       
     1 | bar              | Jack Daniel
     2 | baz              | Jack Daniel
     2 | win              | John       
     3 | amp              | John       
     1 | M$ media mplayer | Mary

demo

but when i add the same postname for different createby, it still throws an error insert into posts (postname,createdby) values('foo','test'); ERROR: duplicate key value violates unique constraint "posts_pkey" DETAIL: Key (postname)=(foo) already exists. — sai, Feb 06 '21 at 07:10
@sai Yes because of `PRIMARY KEY (postname)` You need to choose another primary key for the data provided in the question. — Abelisto, Feb 06 '21 at 09:19

score 0 · Answer 2 · answered Feb 05 '21 at 20:14

0

I'm not 100% sure of your problem, and this may be radically oversimplified, but as I understand it you only want one record per post/user. If this is the case, your primary key should be both the postname and the createdby fields, which would guarantee only one record per user/post:

create table posts (
  postid int,
  postname VARCHAR(255) NOT NULL,
  createdby VARCHAR(255) NOT NULL,
  constraint posts_pk PRIMARY KEY (postname, createdby),
  CONSTRAINT fk_user FOREIGN KEY(createdby) REFERENCES users(username) on DELETE CASCADE
);

Then, when you insert, instead of a plain insert, you want to do an upsert. This says insert the record if it doesn't exist; otherwise update it:

insert into posts (postid, postname, createdby)
values (1, 'hi', 'user1')
on conflict on constraint posts_pk
do
  update
  set
    postid = posts.postid + 1

I can't help but feel something is missing from the problem statement, but from what I glean I am hopeful this will help.

answered Feb 05 '21 at 20:14

Hambone

15,600
8
46
69

You should probably add that this implies that a post (?) cannot be posted again by the same user. That sounds a little bit odd for me (hence my comment). – maio290 Feb 05 '21 at 20:25
@maio290 - the use case is very unclear to me, so I admit I'm grasping at straws on this one – Hambone Feb 05 '21 at 20:29
just edited the question, so the post can be same for different users – sai Feb 05 '21 at 20:32
@sai - I still don't understand. Maybe if you can add current statements, the current behavior and then be specific about desired behavior... that should be a good starting point. What it does now vs what I want it to do (and why) – Hambone Feb 05 '21 at 20:54

set id unique to username

2 Answers2