Postgresql – Insert multiple rows into a table with id from other table if not exists insert to other table

cteinsertpostgresql

I have done similar task where I can insert a row into a table if data doesn't exists:

WITH 
        user_exists AS (
            Select id from users where username='%s'
        ),
        user_new AS (
            INSERT INTO users (username)
            SELECT w.username FROM (values ('%s')) w(username)
                WHERE not exists
                (SELECT 1 FROM users u WHERE u.username = w.username)
                returning id
        )
INSERT INTO feedbacks ('static_row', userid)
SELECT 
'static_data',
(SELECT id FROM users_exists UNION ALL SELECT id FROM users_new) AS userid

Above works well when we insert a new row to feedbacks table. If user doesn't exists it inserts data in users table and returns id which is used for inserting data to feedbacks table.

But now my use case is, I have to insert multiple rows into the feedback table. Something like this:
user_variable = ['a','b', …]

Insert into feedbacks ('static_row', userid) 
VALUES
('sample_data', (Select if from users where username='a')),
('sample_data', (Select if from users where username='b')),
('sample_data', (Select if from users where username='c'))

For above case, how we can insert a new row to users table if username='b' doesn't exist?

Best Answer

Why not split up your separate goals into separate queries to handle them appropriately? It seems a little dangerous to be modifying data within a CTE that is then used to be read from, though not sure if this is common convention in PostgreSQL.

For example:

WITH CTE_UsersToBeAdded AS
(
    SELECT 'a' AS username
    UNION ALL
    SELECT 'b' AS username
    UNION ALL
    SELECT 'c' AS username
)

INSERT INTO users (username)
SELECT c.username
FROM CTE_UsersToBeAdded c
LEFT JOIN users u
    ON c.username = u.username
WHERE u.usernams IS NULL; -- Filters out already existing usernames

INSERT INTO feedbacks ('static_row', userid) 
VALUES
('sample_data', (Select id from users where username='a')),
('sample_data', (Select id from users where username='b')),
('sample_data', (Select id from users where username='c'))

Related Solutions

Sql-server – Combine – Select Into, with Insert Into Select

When the automation fires off the second time, I get an error because the table already exists and, consequently, the second round of data isn't inserted.

I would suggest you to put a condition to check the existence of the table

If exists (select 1 from sys.tables where name = '' and type = 'U')
begin -- table exists, so insert values in it
insert into table_name values ()
end
else
begin -- table does not exist, so create it
create table table_name (column datatype)
insert into table_name values ()
end

You can even use below sql to create new blank table as the where 1=0 will always be false.

Note that it wont create any indexes from old_table into new_table.

SELECT *
INTO new_table
FROM old_table where 1=0

To capture errors, you can use TRY/CATCH

Postgresql – Postgres : Using WITH … AS insert into to return non-inserted column

One possible solution is adding userid to the staging table you already have:

CREATE TABLE temp_import (
    row_id int,
    name varchar(32),
    email varchar(255),
    userid int not null default nextval('tmp_uid_seq'::regclass)
);

Use the same column definition above as you do in users - this way you use the same sequence, and even the nextval() will be correct after the import.

When doing the COPY, the userid will be filled. This way you have the mapping and the staging all in the same place. Now you have to copy the userid into the final table, too:

INSERT INTO users (userid, name)
SELECT userid, name
  FROM temp_import;

INSERT INTO mapping (userid, row_id)
SELECT userid, row_id
  FROM temp_import;

Notes:

in PostgreSQL, unlimited text and varchar have some performance advantage over the limited types. Use the latter if you really have a limit. With varchar(255) the case is usually 'We don't know how long it can be, but definitely not that long'. Email addresses are usually considered as limited to 254 characters, not 255: https://www.rfc-editor.org/errata_search.php?eid=1690 Also, are you sure that nobody can have a name longer than 32 characters, including spaces, punctuation and so on?
it usually makes sense to use some consistent naming scheme in any software system, including databases. I'm already confused a bit about the different styles used in userid and row_id.
if the import will happen repeatedly or in parallel, just use the same sequence all the time. Sequences are transaction-safe and won't reuse any values until they wrap over (which can be mostly prevented by using bigint/bigserial).

Best Answer

Related Solutions

Sql-server – Combine – Select Into, with Insert Into Select

Postgresql – Postgres : Using WITH … AS insert into to return non-inserted column

Related Question