3

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?

Braiam
  • 1
  • 11
  • 47
  • 78
ConanTheGerbil
  • 677
  • 8
  • 21

1 Answers1

6

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;"
  • 1
    Thanks @LotPings - that does the trick. It's a shame computers don't give you a 'getting warmer' message when you're really close - I'd spent a good 90 minutes trying variations of this. There is another little 'gotcha' to watch for - make sure any spaces after the carets have been removed, wasted another 5 minutes before I spotted this. – ConanTheGerbil Jun 30 '18 at 16:58
  • Well computers/software are picky on details. I thought `So end the line with space+caret` was unambiguous. ;-) –  Jun 30 '18 at 18:15
  • 1
    "I thought So end the line with space+caret was unambiguous" true, but it looks very similar to space+caret plus random whitespace! – ConanTheGerbil Jun 30 '18 at 21:19