Insert Data and Set Foreign Keys with Postgres

Insert data and set foreign keys with Postgres

The table users must have some primary key that you did not disclose. For the purpose of this answer I will name it users_id.

You can solve this rather elegantly with data-modifying CTEs introduced with PostgreSQL 9.1:

country is unique

The whole operation is rather trivial in this case:

WITH i AS (
    INSERT INTO addresses (country) 
    SELECT country
    FROM   users
    WHERE  address_id IS NULL 
    RETURNING id, country
    )
UPDATE users u
SET    address_id = i.id
FROM   i
WHERE  i.country = u.country;

You mention version 8.3 in your question. Upgrade! Postgres 8.3 has reached end of life.

Be that as it may, this is simple enough with version 8.3. You just need two statements:

INSERT INTO addresses (country) 
SELECT country
FROM   users
WHERE  address_id IS NULL;

UPDATE users u
SET    address_id = a.id
FROM   addresses a
WHERE  address_id IS NULL 
AND    a.country = u.country;

country is not unique

That's more challenging. You could just create one address and link to it multiple times. But you did mention a 1:1 relationship that rules out such a convenient solution.

WITH s AS (
    SELECT users_id, country
         , row_number() OVER (PARTITION BY country) AS rn
    FROM   users
    WHERE  address_id IS NULL 
    )
    , i AS (
    INSERT INTO addresses (country) 
    SELECT country
    FROM   s
    RETURNING id, country
    )
    , r AS (
    SELECT *
         , row_number() OVER (PARTITION BY country) AS rn
    FROM   i
    )
UPDATE users u
SET    address_id = r.id
FROM   r
JOIN   s USING (country, rn)    -- select exactly one id for every user
WHERE  u.users_id = s.users_id
AND    u.address_id IS NULL;

As there is no way to unambiguously assign exactly one id returned from the INSERT to every user in a set with identical country, I use the window function row_number() to make them unique.

Not as straight forward with Postgres 8.3. One possible way:

INSERT INTO addresses (country) 
SELECT DISTINCT country -- pick just one per set of dupes
FROM   users
WHERE  address_id IS NULL;

UPDATE users u
SET    address_id = a.id
FROM   addresses a
WHERE  a.country = u.country
AND    u.address_id IS NULL
AND NOT EXISTS (
    SELECT * FROM addresses b
    WHERE  b.country = a.country
    AND    b.users_id < a.users_id
    ); -- effectively picking the smallest users_id per set of dupes

Repeat this until the last NULL value is gone from users.address_id.

Insert a value from a table in another table as foreign key

You have two options.

The first one is using the lastval() function which returns the value of the last generated sequence value:

insert into cinema(name, is_active) values ('Cinema One', true); 
insert into theater(cinema_id) values (lastval());

Alternatively you can pass the sequence name to the currval() function:

insert into theater(cinema_id) 
values (currval(pg_get_serial_sequence('cinema', 'id')));

Alternatively you can chain the two statements using a CTE and the returning clause:

with new_cinema as (
   insert into cinema (name, is_active)   
   values ('Cinema One', true)
   returning id
)
insert into theater (cinema_id)
select id
from new_cinema;

In both statements I assume theater.id is also a generated value.

How to automaticaly fill a foreign key when a row is inserted in Postgresql

You can make a subquery in insert statement

INSERT INTO contacts(customer_id, contact_name ) VALUES (
(select customer_id from customers where customer_name = 'my_first_customer') ,'thomas');

This query will fail if there is more than one customer with given name, and insert a null value for customer_id if there is no customers with given name

Different approach (proposed by @wildplasser)

INSERT INTO contacts(customer_id, contact_name ) 
select customer_id,'thomas' from customers where customer_name = 'my_first_customer';

In this case, when there is no customer with given name, no row will be created. When there is more than one customer with given name, for each of them record will be created.

Or you can create view with INSTEAD OF trigger.

create view v_contacts as 
select customer_name, contact_name from customers 
  join contacts on customers.customer_id = contacts.customer_id;

CREATE FUNCTION emp () RETURNS trigger AS $$
BEGIN
    
  INSERT INTO contacts(customer_id, contact_name ) 
    VALUES((select customer_id from customers where customer_name = 
       NEW.customer_NAME),NEW.contact_NAME);
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER v_contactst
    INSTEAD OF INSERT ON v_contacts
    FOR EACH ROW
EXECUTE FUNCTION emp();    

Insert statement

INSERT INTO v_contacts(customer_name, contact_name ) VALUES('my_first_customer','thomas')

And just a note, that columns customer_name and contact_name are not unique so this code is not safe and throw error if customer_name didn't exists or there is more than one record with this customer_name

How postgresql multiple insert works when there is foreign key constraint in the table itself?

Foreign key constraints are implemented with triggers in PostgreSQL. This is not directly documented, but you can see it indirectly here:

One can disable or enable a single trigger specified by name, or all triggers on the table, or only user triggers (this option excludes internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints).

You can also see it with

SELECT * FROM pg_trigger
WHERE tgrelid = 'schools'::regclass;

The firing rules for triggers are documented and apply to foreign keys as well:

Row-level BEFORE triggers fire immediately before a particular row is operated on, while row-level AFTER triggers fire at the end of the statement (but before any statement-level AFTER triggers).

(Emphasis mine)

So foreign keys are validated after the complete statement is done.

Postgres - how to bulk insert table with foreign keys

I was able to resolve this issue using update referencing another table.

Basically, I updated the geo_location_id column using

 UPDATE temp_inverter_location SET geo_location_id = geo_location.id FROM geo_location WHERE geo_location.location_name = temp_inverter_location.location_name;

and updated the project_info_id using

UPDATE load_table SET project_info_id = project_info.id FROM project_info WHERE project_info.operation_name = load_table.location_name;

It seems to have worked.

---

Related Topics