6

I'm doing an insert from a query which joins two tables, then I want the new ID from the inserted row, and a field from the original row which is not involved in the insert. Is it possible? I get a "column doesn't exist" error.

INSERT INTO new_table (x,y) 
select A.x,B.y 
from A 
  join B on A.w = B.z 
RETURNING id,B.z;

The new_table has a unique constraint on (x,y).

The new_id and B.z are needed to insert into a second table.

8

Perhaps there's a better alternative but I can only think of joining back to the 2 tables.

This assumes that new_table has a unique constraint on (x,y) and that these columns are not nullable:

with ins (id, x, y) as
( insert into new_table (x, y) 
  select A.x, B.y 
  from A join B on A.w = B.z 
  returning id, x, y
)
-- insert into another_table (id, z)
select 
    ins.id, B.z        -- whatever columns from the 3 tables
from ins 
  join A on A.x = ins.x 
  join B on B.y = ins.y and A.w = B.z ;
|improve this answer|||||
  • 1
    Never would have crossed my mind to use an insert statement as a subquery. – bklaric Jul 27 '18 at 20:24
2

I think the best option is to use nexval on the sequence of the new table.

with x as (
select nextval('new_table_id_seq') as new_id, A.x, B.y
from A JOIN  B ON A.w = B.z
),
y as (
    insert into new_table (id, x,y) select new_id, x,y from x
)
select new_id, x,y from x;
|improve this answer|||||
  • I think you want select new_id, x, y from y; on your last line there? – Joishi Bodio Jan 26 '15 at 18:59
1

I have been facing the same problem. I have not found a way to work out the problem without inserting into the table an unique identifier so it can returned and used later.

An example that I built up and macthes my own problem was :

Part A : sample data ; basically a list of groups with users, their phone numbers and emails. This list is to be migrated to a set of tables. Assumptions : a user belongs to only one group.

-- The already existing data 
DROP TABLE IF EXISTS sourceData;
CREATE TABLE sourceData (
    -- This table holds the data that is to be migrated to groups, users, emails
    gid serial PRIMARY KEY,
    groupName varchar (50) NOT NULL,
    personName varchar (255) ,
    emailAddress varchar(255),
    phone varchar (20) 
);

INSERT INTO sourceData
    (groupName, personName, emailAddress, phone)
    VALUES
    ('Group A','Philippe','phil@psql.org', NULL),
    ('Group A','Philippe',NULL,'0123456789'),
    ('Group A','Francis','francis@gmail.com', NULL),
    ('Group B','Patrick','patrick@kickmyass.com','0147852369'),
    ('Group B','Robert',NULL,'0963852741'),
    ('Group B','Alice', NULL, NULL)
;

-- 

DROP TABLE IF EXISTS groups CASCADE;
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS emails CASCADE;
DROP TABLE IF EXISTS phones CASCADE;

CREATE TABLE groups (
    -- table with names of groups
    gid serial PRIMARY KEY,
    group_name varchar(50) NOT NULL
);

CREATE TABLE users (
    -- table to join companies with various contact adresses, which may be emails, phone numbers, etc ...
    -- each person may have several of them
    gid serial PRIMARY KEY,
    gid_groups integer NOT NULL,
    personName varchar(255) NOT NULL,
    CONSTRAINT fkt2 FOREIGN KEY (gid_groups) REFERENCES groups (gid)
);

CREATE TABLE emails (
    -- table with email adresses
    gid serial PRIMARY KEY,
    gid_users integer NOT NULL,
    email_address varchar(255) NOT NULL,
    CONSTRAINT fkt3 FOREIGN KEY (gid_users) REFERENCES users (gid)
);

CREATE TABLE phones (
    -- table with phone numbers
    gid serial PRIMARY KEY,
    gid_users integer NOT NULL,
    phone_number varchar(20) NOT NULL,
    CONSTRAINT fkt4 FOREIGN KEY (gid_users) REFERENCES users (gid)
);

-- Now we start with the names already inserted
INSERT INTO groups (group_name)
    SELECT DISTINCT groupName FROM sourceData ;
-- we are not even sure in which order they are inserted

Part B : migrating the data.

-- Insertion of users and their email adresses for those who have some
WITH selectedData  AS 
    (   -- selection of the data that needs to be inserted
        SELECT 
            gp.gid AS gp_gid, sourceD.personName , sourceD.emailAddress
            FROM groups AS gp
            JOIN sourceData AS sourceD
            ON gp.group_name = sourceD.groupName
            WHERE sourceD.emailAddress IS NOT NULL
    ),
insertUsers AS
    (
        INSERT INTO users
            ( gid_groups , personName  )
            SELECT 
                gp_gid , personName
                FROM selectedData
            RETURNING gid_groups AS gp_gid , gid AS user_gid , personName
    )
INSERT INTO emails
    ( gid_users , email_address )
    SELECT 
        insertUsers.user_gid , selectedData.emailAddress
        FROM insertUsers
        JOIN selectedData
        ON insertUsers.personName = selectedData.personName
;

-- Insertion of phone numbers for users who already exist in the database
WITH selectedData  AS 
    (   -- selection of the data that needs to be inserted
        SELECT 
            gp.gid AS gp_gid, sourceD.personName , sourceD.phone
            FROM groups AS gp
            JOIN sourceData AS sourceD
            ON gp.group_name = sourceD.groupName
            WHERE sourceD.phone IS NOT NULL
    )
INSERT INTO phones
    ( gid_users , phone_number  )
    SELECT 
        users.gid , selectedData.phone
        FROM selectedData
        INNER JOIN users -- ensures that only already existing users are selected
        ON selectedData.personName = users.personName
;

-- Insertion of users with their phone numbers for those who have no email address and thus do not exist yet in the database
WITH selectedData  AS 
    (   -- selection of the data that needs to be inserted
        SELECT 
            gp.gid AS gp_gid, sourceD.personName , sourceD.phone
            FROM groups AS gp
            JOIN sourceData AS sourceD
            ON gp.group_name = sourceD.groupName
            WHERE sourceD.phone IS NOT NULL
    ),
insertUsers AS
    (
        INSERT INTO users
            ( gid_groups , personName  )
            SELECT 
                selectedData.gp_gid , selectedData.personName
                FROM selectedData
                LEFT JOIN users
                ON selectedData.personName = users.personName
                WHERE  users.personName IS NULL -- ensures that only users not already in database are inserted
            RETURNING gid_groups AS gp_gid , gid AS user_gid , personName
    )
INSERT INTO phones
    ( gid_users , phone_number )
    SELECT 
        insertUsers.user_gid , selectedData.phone
        FROM insertUsers
        JOIN selectedData
        ON insertUsers.personName = selectedData.personName
;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.