Combining multiple postgres scripts in one file

Question

I have two files that both work fine when executing them in Postgres.

File 1

CREATE TABLE ContractStatusEnum (
    id              SERIAL PRIMARY KEY,
    description         VARCHAR(4000) NOT NULL
);

*File 2

DO $$ BEGIN

    IF NOT EXISTS (SELECT * FROM ContractStatusEnum WHERE id = 1) THEN
    INSERT INTO ContractStatusEnum (id, description) VALUES
        (1, 'Ordered'),
        (2, 'Active'),
        (3, 'Stopped'),
        (4, 'Canceled'),
        (10, 'Inactive');
    END IF;
END $$

However, if I just put them together in one file, it fails with

ERROR: syntax error at or near "DO"
LINE 6: DO $$ BEGIN

On MS SQL I can solve this by creating a new context by putting a GO statement between two scripts. Any clue how to do it in Postgres 9?

Do is only valid in pgplsql. You can omit the do. Just concatenate the fragments (possibly each statement terminated by a semicolon. The IF NOT EXISTS syntax is invalid: use a WHERE clause instead. — wildplasser
– wildplasser, Commented Mar 14, 2012 at 11:14
The IF NOT EXISTS statement runs fine when I execute the second file in postgres without concatenating. Should I run the complete script in pgplsql? — Jan Jongboom
– Jan Jongboom, Commented Mar 14, 2012 at 11:16
Yes, you can even redirect stdin for psql and use a pipe or '<' to submit a whole script file. Ehh: no, you don't need pgplsql just to submit sql code. just the psql "terminal" executable. — wildplasser
– wildplasser, Commented Mar 14, 2012 at 11:20

user330315 · Accepted Answer · 2012-03-14 11:40:29Z

3

You need to terminate your DO block with a semicolon.

The following works for me:

DO $$ BEGIN

    IF NOT EXISTS (SELECT * FROM ContractStatusEnum WHERE id = 1) THEN
    INSERT INTO ContractStatusEnum (id, description) VALUES
        (1, 'Ordered'),
        (2, 'Active'),
        (3, 'Stopped'),
        (4, 'Canceled'),
        (10, 'Inactive');
    END IF;
END $$
;

I saved the statements in f1.sql (create table) and f2.sql (do block):

psql (9.1.3)
Type "help" for help.

postgres=> \i f1.sql
CREATE TABLE
postgres=> \i f2.sql
DO
postgres=>

answered Mar 14, 2012 at 11:40

user330315

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

Comments

wildplasser · Accepted Answer · 2013-11-27 10:59:55Z

2

Just concatenate the fragments. No / or GO is needed. (in interactive mode for psql a \g may be needed)

CREATE TABLE ContractStatusEnum (
    id              SERIAL PRIMARY KEY,
    description         VARCHAR(4000) NOT NULL
);

    -- File 2

 INSERT INTO ContractStatusEnum (id, description) VALUES 
     (1, 'Ordered'),
     (2, 'Active'),
     (3, 'Stopped'),
     (4, 'Canceled'),
     (10, 'Inactive')
  ;

EDIT: removed the WHERE clause (it is not even valid here) The PK constraint on the table will avoid multiple initialisations anyway.

Another way of concatenation is by means of inclusion. You can create a "master" script, and include the fragments from there. Comes in handy when you want to "comment-out" whole portions.

-- Master script.

\i tables_create.sql
\i procedures_and_functions.sql
\i populate_the_tables.sql
\i do_some_checks.sql

-- EOF

edited Nov 27, 2013 at 10:59

answered Mar 14, 2012 at 11:19

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

4 Comments

Jan Jongboom Over a year ago

I understand I can get rid of it by omitting the pgplsql, but I have more complicated scripts that require running in that mode.

wildplasser Over a year ago

I don't think pgplsql is valid outside procedures/functions. I would certainly not want it.

user330315 Over a year ago

@wildplasser: it is certainly valid inside a DO block

wildplasser Over a year ago

Oops. I stand corrected. I never use it; the INSERT will fail because of the PK constraint. (this appears to be a table creation+initialisation script)

Collectives™ on Stack Overflow

Combining multiple postgres scripts in one file

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related