Thread: bash & postgres
Hi, I'm trying to pass variables on a bash script embedded with psql commands. cat header.txt "to1","from1","subject1" "to2","from2","subject2" "to3","from3","subject3" "to4","from4","subject4" cat b.sh #!/bin/bash two="2" psql -h localhost -U postgres -d mobile -c "create temp table header ( field_1 text not null, field_2 text not null, field_3 text not null ); \\copy header FROM header.txt CSV SELECT * FROM header limit "$two"; " When I execute b.sh ================================ ERROR: syntax error at or near "\" LINE 10: \copy header FROM header.txt CSV ^ How do I use \c (or any other psql commands beginning with a "\") in a bash script? Thanks.
On Mar 22, 2009, at 9:03 PM, Greenhorn wrote: > Hi, > > I'm trying to pass variables on a bash script embedded with psql > commands. > > cat header.txt > > "to1","from1","subject1" > "to2","from2","subject2" > "to3","from3","subject3" > "to4","from4","subject4" > > cat b.sh > > #!/bin/bash > two="2" > > psql -h localhost -U postgres -d mobile -c "create temp table header ( > > field_1 text not null, > field_2 text not null, > field_3 text not null > > ); > > \\copy header FROM header.txt CSV > > SELECT * FROM header limit "$two"; " > > > When I execute b.sh > ================================ > ERROR: syntax error at or near "\" > LINE 10: \copy header FROM header.txt CSV > ^ > > How do I use \c (or any other psql commands beginning with a "\") in a > bash script? For multi-line input to a psql call in a bash (or any decent shell) script, I'd use a here document: #!/bin/bash #!/bin/bash two="2" psql -d pagila <<COPYTEST create temp table header ( field_1 text not null, field_2 text not null, field_3 text not null ); \copy header FROM header.txt CSV SELECT * FROM header limit $two; COPYTEST $ ./tst.sh Null display is "\N". Timing is on. CREATE TABLE Time: 7.568 ms Time: 2.374 ms field_1 | field_2 | field_3 ---------+---------+---------- to1 | from1 | subject1 to2 | from2 | subject2 (2 rows) Time: 1.011 ms (P.S. Your quotes around $two in your original are not needed, in fact they're straight up broken as $two is already inside of a double- quoted string). Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
Erik Jones <ejones@engineyard.com> writes: > On Mar 22, 2009, at 9:03 PM, Greenhorn wrote: >> How do I use \c (or any other psql commands beginning with a "\") in a >> bash script? > For multi-line input to a psql call in a bash (or any decent shell) > script, I'd use a here document: Or echo/cat the script into psql's stdin, if you prefer that type of notation. The reason you have to do this is that psql doesn't recognize backslash commands in a -c string. There's a school of thought that doesn't want us to allow multiple commands in a -c string, even. regards, tom lane
On Mar 23, 2009, at 7:05 AM, Tom Lane wrote: > Erik Jones <ejones@engineyard.com> writes: >> On Mar 22, 2009, at 9:03 PM, Greenhorn wrote: >>> How do I use \c (or any other psql commands beginning with a "\") >>> in a >>> bash script? > >> For multi-line input to a psql call in a bash (or any decent shell) >> script, I'd use a here document: > > Or echo/cat the script into psql's stdin, if you prefer that type of > notation. The reason you have to do this is that psql doesn't > recognize > backslash commands in a -c string. There's a school of thought that > doesn't want us to allow multiple commands in a -c string, even. Hmm... Apparently it does recognize them as long as the backslash is the first character in the command string: $ psql -c '\d' postgres No relations found. $ psql -c ' \d' postgres ERROR: syntax error at or near "\" LINE 1: \d ^ Is that expected behavior? Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
Erik Jones <ejones@engineyard.com> writes: > On Mar 23, 2009, at 7:05 AM, Tom Lane wrote: >> The reason you have to do this is that psql doesn't recognize >> backslash commands in a -c string. There's a school of thought that >> doesn't want us to allow multiple commands in a -c string, even. > Hmm... Apparently it does recognize them as long as the backslash is > the first character in the command string: Hmm, maybe I was just misremembering the details. What's certainly true is that psql doesn't run a -c string through the same extensive parsing that data from stdin gets. regards, tom lane