INSERT INTO table if exists or else CREATE TABLE

Question

I have a pgscript that runs in a loop.

SET @id = 1;
BEGIN
WHILE @id <= 10

/*
CREATE TABLE tbl_name AS
SELECT * FROM main_tbl
WHERE id = @id
;

INSERT INTO tbl_name
SELECT * FROM main_tbl
WHERE id = @id
*/

SET @id = @id + 1;

END

For the first iteration id=1, I want my script to create a table because the table does not exist. For the upcoming loop id=2...3...4...so on, I want the script to insert into the table that was created.

Currently, I am creating an empty table before running the PgScript. Is there a way to write a script that creates the table for the first loop and insert in the upcoming iterations?

You should make it clear in your question that you are talking about a feature, PgScript, that is pgAdmin specific. — Adrian Klaver
– Adrian Klaver, Commented Dec 22, 2020 at 18:19
"Currently, I am creating an empty table before running the PgScript" - what's wrong with that? You also don't need pgScript or even a loop to do what you want. A simple insert into target_table select * from some_table where id between 1 and 10 will be much more efficient. Plus it would work with any SQL client — user330315
– user330315, Commented Dec 22, 2020 at 18:49

Allan Santos · Accepted Answer · 2020-12-23 14:08:53Z

0

Try this:

CREATE TABLE IF NOT EXISTS words (
    id SERIAL PRIMARY KEY,
    word VARCHAR(500) NOT NULL
);

INSERT INTO words
        VALUES(DEFAULT, '1234');

SELECT *
FROM words;

edited Dec 23, 2020 at 14:08

answered Dec 22, 2020 at 18:20

Allan Santos

895 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Allan Santos Over a year ago

Possible link to help you

Allan Santos Over a year ago

Sorry, my mistake. Now this example will works

Collectives™ on Stack Overflow

INSERT INTO table if exists or else CREATE TABLE

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related