How to insert multiple rows in a pgsql table, if the table is empty?

Question

I'm developing a Building Block for Blackboard, and have run into a database related issue.

I'm trying to insert four rows into a pgsql table, but only if the table is empty. The query runs as a post-schema update, and is therefore run whenever I re-install the building block. It is vital that I do not simply drop exsisting values and/or replace them (which would be a simple and effective solution otherwise).

Below is my existing query, that does the job, but only for one row. As I mentioned, I'm trying to insert four rows. I can't simply run the insert multiple times, as after the first run, the table would no longer be empty.

Any help will be appriciated.

BEGIN;
    INSERT INTO my_table_name 
    SELECT
        nextval('my_table_name_SEQ'),
        'Some website URL', 
        'Some image URL',
        'Some website name',
        'Y',
        'Y'
    WHERE 
        NOT EXISTS (
            SELECT * FROM my_table_name
        );
    COMMIT;
END;

score 0 · Accepted Answer · answered Mar 21 '18 at 14:55

I managed to fix the issue. In this post, @a_horse_with_no_name suggest using UNION ALL to solve a similar issue.

Also thanks to @Dan for suggesting using COUNT, rather than EXISTS

My final query:

BEGIN;

INSERT INTO my_table (pk1, coll1, coll2, coll3, coll4, coll5)
    SELECT x.pk1, x.coll1, x.coll2, x.coll3, x.coll4, x.coll5
        FROM (
            SELECT 
                nextval('my_table_SEQ') as pk1,
                'Some website URL' as coll1, 
                'Some image URL' as coll2,
                'Some website name' as coll3,
                'Y' as coll4,
                'Y' as coll5
            UNION
            SELECT
                nextval('my_table_SEQ'),
                'Some other website URL', 
                'Some other image URL',
                'Some other website name',
                'Y',
                'N'
            UNION
            SELECT
                nextval('my_table_SEQ'),
                'Some other other website URL', 
                'Some other other image URL',
                'Some other other website name',
                'Y',
                'N'
            UNION
            SELECT
                nextval('my_table_SEQ'),
                'Some other other other website URL', 
                'Some other other other image URL',
                'Some other other other website name',
                'Y',
                'Y'
        ) as x
    WHERE
        (SELECT COUNT(*) FROM my_table) <= 0;

    COMMIT;
END;

score -1 · Answer 2 · answered Mar 20 '18 at 17:17

It is better if you count the rows because it gets the number of input rows.

This should work:

BEGIN;
    INSERT INTO my_table_name 
    SELECT
        nextval('my_table_name_SEQ'),
        'Some website URL', 
        'Some image URL',
        'Some website name',
        'Y',
        'Y'
    WHERE 
        (SELECT COUNT(*) FROM my_table_name)>0
    COMMIT;
END;

Victor Di Leo · Answer 3 · 2018-03-20T17:41:07.313

Inserts won't overwrite, so I'm not understanding that part of your question. Below are two ways to insert multiple rows; the second example is a single sql statement:

create table test (col1 int, col2 varchar(10) ) ;

insert into test select 1, 'A' ;
insert into test select 2, 'B' ;

insert into test (col1, col2)
values (3, 'C'),
       (4, 'D'),
       (5, 'E') ;


select * from test ;

1   "A"
2   "B"
3   "C"
4   "D"
5   "E"

How to insert multiple rows in a pgsql table, if the table is empty?

3 Answers3