Passing variable from Bash to psql

Question

I'm having some trouble with passing a variable from Bash to a psql command. Can't figure out where to place quotes or parantheses where needed

I'm essentially trying to insert today's date into a date column on a PostGres table

today=$(date +"%m-%d-%Y")

echo $today
#10-05-2015

psql -h hostname -U admin -d db-p 5439 -c \
"INSERT INTO public.log_table (day, rowcount) VALUES ("$today", $rowcount)";

I've tried calling $today without the quotes and a few other variations, but can't get it to pass what I want, which is 10-05-2015. Instead, the value -2010 is being inserted, which is essentialy 10 minus 5 minus 2015...

Answer 1

You need to escape the quotes:

"INSERT INTO public.log_table (day, rowcount) VALUES (\"$today\", $rowcount)";

Without escaping them, they are being interpreted by the shell as the end of the string. Then the date is being inserted (as the shell is concatenating the previous string with this non-quoted-string), and finally, after the date, the shell sees another string for the rest of the SQL command and concatenates with the others.

So what psql was receiving was

INSERT INTO public.log_table (day, rowcount) VALUES (10-05-2015, <rowcount_value>)

and it sees it as integers, so psql resolves that you want to insert the integer -2010.

After properly quoting it, psql receives:

INSERT INTO public.log_table (day, rowcount) VALUES ("10-05-2015", <rowcount_value>)