1

The problem is simply as follow:

watch "psql -d postgresql://user:pass@host:5432/dbname -c 'select id,name from table where name <> 'not available' order by id;'"

this 'not available' comparison has to be single quoted as is for Postgres. But I cannot figure out a way to escape these singles quotes properly as the psql command (I mean, the select...) is itself already single quoted, and is also inside the doubles quotes required by the call of psql for watch.

How to solve this?
I almost seen every possible syntax errors by using \ or multiple double/single quotes for escaping these single quotes.

5
  • What about creating a function with the psql command? Then you wouldn't have to care about quotings. Commented Sep 23, 2021 at 13:40
  • @schrodigerscatcuriosity, though being able to call the function from within watch would take some gymnastics...
    – ilkkachu
    Commented Sep 23, 2021 at 13:56
  • I didn't explored this track because I wanted something quick'n'dirty (well, not dirty, but kind of a one-liner) to monitor some changes in a database. But if a function is better, let me know, I'm not really used to use bash functions actually.
    – s.k
    Commented Sep 23, 2021 at 14:08
  • To run a bash function with watch, you have to do two things: 1. define the function, 2. export the function. For example, try defining a function called foo: foo () { echo foo ; } - if you run watch foo or watch bash -c foo without exporting it, they will fail. Run export -f foo and now either of them will work. This assumes that you're currently running bash and your login shell is bash, you may have to adapt this if you use another shell.
    – cas
    Commented Sep 24, 2021 at 5:18
  • See How can I single-quote or escape the whole command line in Bash conveniently? and my answer there. Do not miss _prepend_watch() in the answer. Commented Sep 24, 2021 at 6:42

2 Answers 2

4

You can't escape single-quotes inside single-quotes. You can, however, fake it with:

'\''

Explanation: that's an end-single-quote (i.e. to end the current quoting), followed by an escaped single-quote, and then start single-quote again. it works the same as, say, 'a'b'c' (quoted a then an unquoted b then a quoted c - all together, that's just abc...and 'a'\''b' is just a'b)

watch "psql -d postgresql://user:pass@host:5432/dbname -c \
  'select id,name from table
   where name <> '\''not available'\''
   order by id;'"

(newlines added to improve readability. the sql command will work the same with or without them)


Note: when using postgres (or sqlite or mysql, etc) it's best to use a language that supports placeholders, so you don't need to worry about quoting. Their CLIs are good for interactive queries and some scripting (in SQL, not sh) but the nested quoting required to pass sql code from sh is clumsy and easy to get wrong (not impossible, just far more effort than it would be in other languages).

e.g. in perl DBI (i haven't included any of the boilerplate login stuff, just the select with a prepared statement and a placeholder):

# you could use string literals, but i'll use some
# variables for this example
my $exclude = 'not available';
my $sql = 'select id,name from table where name <> ? order by id';

my $sth = $dbh->prepare($sql);
$sth->execute($exclude);

The ? is a placeholder, DBI will replace it with whatever value you provide - quoting it automatically if required, depending on the data type of the column. i.e. just give it the data for the column, let it worry about quoting and escaping.

BTW, you can have more than one, you just have to supply the right types of arguments in the right order. Also, some DBI drivers - including DBD::Pg - allow you to use named placeholders like :name or numbered placeholders like $1, $2 - these look the same, and work roughly the same as shell positional parameters but but they're not, they're placeholders in an sql statement)

my $sql = 'select id,name from table where name <> $1 order by id';

shell is a terrible language for anything that isn't just feeding data and/or filenames into other programs and co-ordinating the execution of other programs. Anything even moderately complicated is going to be a PITA due to the care and attention you have to pay to quoting, whitespace, word-splitting etc issues. You will run into the same kinds of problems with nested quotes in ssh commands, or in a find .. -exec sh -c '...' {} + command and many other instances where you have to nest multiple levels of quotes.

2
  • Sounds good, but tricky, thanks a lot ;-) !
    – s.k
    Commented Sep 23, 2021 at 13:34
  • As for the shell part, it should perhaps be noted that the backslash has special meanings within double-quotes, which in general would need to be taken into account. But since ' isn't special in double-quotes, neither is \', and it's left as-is, so no issue from the double-quotes here. On the other hand, if we wanted to pass \$ as part of the inner quoted string, it'd need to be written as \\\$.
    – ilkkachu
    Commented Sep 23, 2021 at 15:14
1

It is also possible to use a heredoc and avoid the escaping:

psql  "postgresql://[email protected]:5432/db1" <<EOF 
select id,name from table where name <> 'not available' order by id;
EOF

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .