How do I insert multiple rows at once which contains two foreign keys from two different tables in postgres?

Question

I have 3 tables:

CREATE TABLE IF NOT EXISTS site_shipping_method_configuration (        
              id INT GENERATED ALWAYS AS IDENTITY, 
              shipping_method_id uuid DEFAULT uuid_generate_v4(),
              custom_id VARCHAR(255),
              PRIMARY KEY(shipping_method_id);

CREATE TABLE IF NOT EXISTS public.channel (
              channel_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
              channel_name VARCHAR(50),
              PRIMARY KEY(channel_id));

CREATE TABLE IF NOT EXISTS public.shipping_method_channel_mapping (
              id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
              channel_id integer NOT NULL,
              shipping_method_id integer NOT NULL,
              PRIMARY KEY(id),
              FOREIGN KEY (channel_id) REFERENCES channel (channel_id),
              FOREIGN KEY (shipping_method_id) REFERENCES site_shipping_method_configuration (id));

I have to insert multiple values at once in 3rd table which contains two foreign keys from two tables (1st and 2nd). I already have values in first two tables.

how do i insert multiple values in 3rd table at once? Any optimized query is there! — max, Mar 31 '22 at 08:33
This might help you: https://stackoverflow.com/questions/20815028/how-do-i-insert-multiple-values-into-a-postgres-table-at-once — Bjarni Ragnarsson, Mar 31 '22 at 08:45

score 0 · Answer 1 · answered Mar 31 '22 at 10:13

The problem is that the column site_shipping_method_configuration.id, which is referenced by a foreign key in, is not unique.
We solve this by adding UNIQUE in the table definition.

Everything now works correctly.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
/* added to avoid function not found error  */

CREATE TABLE IF NOT EXISTS site_shipping_method_configuration (
id INT GENERATED ALWAYS AS IDENTITY UNIQUE, /* ==========   UNIQUE CONSTRAINT ADDED  ======   */
shipping_method_id uuid DEFAULT uuid_generate_v4() , 
custom_id VARCHAR(255), 
PRIMARY KEY(shipping_method_id));

CREATE TABLE IF NOT EXISTS public.channel ( 
channel_id integer NOT NULL GENERATED ALWAYS AS IDENTITY, 
channel_name VARCHAR(50), 
PRIMARY KEY(channel_id));

CREATE TABLE IF NOT EXISTS public.shipping_method_channel_mapping ( 
id integer NOT NULL GENERATED ALWAYS AS IDENTITY, 
channel_id integer NOT NULL, 
shipping_method_id integer NOT NULL, 
PRIMARY KEY(id), 
FOREIGN KEY (channel_id) REFERENCES channel (channel_id), 
FOREIGN KEY (shipping_method_id) REFERENCES site_shipping_method_configuration (id));

INSERT INTO site_shipping_method_configuration (custom_id) VALUES (1);
INSERT INTO public.channel (channel_name) VALUES ('test channel');

INSERT INTO public.shipping_method_channel_mapping 
(channel_id, shipping_method_id ) VALUES
(1,1);

SELECT * FROM site_shipping_method_configuration;
SELECT * FROM public.channel;
SELECT * FROM public.shipping_method_channel_mapping ;

id | shipping_method_id                   | custom_id
-: | :----------------------------------- | :--------
 1 | 2a1ac9aa-3b96-4e43-968a-06a2e18d2be1 | 1        

channel_id | channel_name
---------: | :-----------
         1 | test channel

id | channel_id | shipping_method_id
-: | ---------: | -----------------:
 1 |          1 |                  1

db<>fiddle here

How do I insert multiple rows at once which contains two foreign keys from two different tables in postgres?

1 Answers1