Question 90877 in PostgreSQL Question posted in
The official documentation can be found here.

Create foreign key row if it doesn't exist yet when inserting row referencing it – Postgresql

I have a table of users and a table of values which have a foreign key to the users table. I want to insert values into the values table which either reference an existing user or create one. I’m using PostgreSQL 12.

The tables are:

CREATE TABLE users (
    id SERIAL NOT NULL, 
    tenant TEXT NOT NULL, 
    name TEXT NOT NULL, 
    PRIMARY KEY (id), 
    UNIQUE (tenant, name), 
)

CREATE TABLE values (
    user_id INTEGER NOT NULL, 
    value TEXT NOT NULL, 
    UNIQUE (user_id, value), 
    FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE
)

Given a tenant, name and value, how can I insert that into the values table in ideally a single query or at least in an atomic way?

Tags:

3

Answers


  1. 0

    I think you in this situation you should insert the user first into the users tables ,then insert a value of it like that

    insert into users (1,'me','sohaib')
    insert into values (1,'sohaib is the my first name')

    Because you can’t do it in one query .

    Login or Signup to reply.
  2. 0

    To do this atomically, you need to wrap your insert statements in a transaction block.

        BEGIN;

    insert into users (1, "tenant", "name");
    insert into values (1, "value");

    COMMIT;
    Login or Signup to reply.
  3. 0

    You do not need to know the generated users.id, You can obtain it by selecting given the tenant and name. Additionally, Postgres provides on conflict clause to handle existing users. The following procedure provides what is needed to create both users and values in a single call. (see demo)

    create or replace procedure create_User_value(tenant_in text
                                             , name_in  text
                                             , value_in text 
                                             )
  language sql 
as $$
insert into users(tenant, name) 
     values(tenant_in, name_in)
         on conflict(tenant, name)
         do nothing;

insert into values( user_id, value)
  select u.id, value_in
    from users u
   where tenant = tenant_in
     and name = name_in
 on conflict ( user_id, value)
 do nothing;
$$;

    NOTE: Values and value are very poor choices for a table and column name. Both a SQL Standard reserved words and values a restricted use in Postgres. About the best you can expect is confusion.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search