CODEWITHSUNDEEP
postgresqlbatch-filepsql
ConanTheGerbil
ConanTheGerbil

Reputation: 787

How to split PSQL command lines over multiple lines?

I'm using a windows batch file to connect to postgres using psql. I'm issuing commands like this....

SET PGPASSWORD=postgres
psql -U postgres -d postgres -c "DROP USER IF EXISTS foo;"

This works fine for running one, short SQL command against the database. But I'm having trouble with two related issues

  1. How to continue a single long SQL command over multiple lines, and
  2. How to run multiple commands.

Example 1.....

psql -U postgres -d postgres -c "CREATE DATABASE foo
WITH OWNER = bar
ENCODING = 'UTF8' 
TABLESPACE = mytabspace;"

Example 2.....

psql -U postgres -d postgres -c "
ALTER TABLE one ALTER COLUMN X TYPE INTEGER;
ALTER TABLE two ALTER COLUMN Y TYPE INTEGER;"

Neither of these will work as shown, I've done some googling and found some suggestions for doing this with linux, and have experimented with various carats, backslashes and underscores, but just don't seem to be able to split the commands across lines.

I'm aware of the -f option to run a file, but I'm trying to avoid that.

Any suggestions?

Upvotes: 4

Views: 10841

Answers (1)

user6811411
user6811411

Reputation:

The line continuation character in batch is the ^. See this Q&A

  • So end the line with space+caret ^ and make sure the following line begins with a space.

  • You will also have to escape double quoted areas that span several lines with a caret for this to work.

    • Since the line is unquoted then for the batch parser you will also have to escape any special chararacters like <>|& also with a caret.
psql -U postgres -d postgres -c ^"CREATE DATABASE foo ^
 WITH OWNER = bar ^
 ENCODING = 'UTF8' ^
 TABLESPACE = mytabspace;"

psql -U postgres -d postgres -c ^" ^
 ALTER TABLE one ALTER COLUMN X TYPE INTEGER; ^
 ALTER TABLE two ALTER COLUMN Y TYPE INTEGER;"

Upvotes: 8

Related Questions