StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I use the following modification of Arturo's solution:</p> <p><code>psql -lqt | cut -d \| -f 1 | grep -qw <db_name></code></p> <hr> <h2>What it does</h2> <p><code>psql -l</code> outputs something like the following:</p> <pre><code> List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+-----------+----------+------------+------------+----------------------- my_db | my_user | UTF8 | en_US.UTF8 | en_US.UTF8 | postgres | postgres | LATIN1 | en_US | en_US | template0 | postgres | LATIN1 | en_US | en_US | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | LATIN1 | en_US | en_US | =c/postgres + | | | | | postgres=CTc/postgres (4 rows) </code></pre> <p>Using the naive approach means that searching for a database called "List, "Access" or "rows" will succeed. So we pipe this output through a bunch of built-in command line tools to only search in the first column.</p> <hr> <p>The <code>-t</code> flag removes headers and footers:</p> <pre><code> my_db | my_user | UTF8 | en_US.UTF8 | en_US.UTF8 | postgres | postgres | LATIN1 | en_US | en_US | template0 | postgres | LATIN1 | en_US | en_US | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | LATIN1 | en_US | en_US | =c/postgres + | | | | | postgres=CTc/postgres </code></pre> <hr> <p>The next bit, <code>cut -d \| -f 1</code> splits the output by the vertical pipe <code>|</code> character (escaped from the shell with a backslash), and selects field 1. This leaves:</p> <pre><code> my_db postgres template0 template1 </code></pre> <hr> <p><code>grep -w</code> matches whole words, and so won't match if you are searching for <code>temp</code> in this scenario. The <code>-q</code> option suppresses any output written to the screen, so if you want to run this interactively at a command prompt you may with to exclude the <code>-q</code> so something gets displayed immediately.</p> <p>Note that <code>grep -w</code> matches alphanumeric, digits and the underscore, which is exactly the set of characters allowed in unquoted database names in postgresql (hyphens are not legal in unquoted identifiers). If you are using other characters, <code>grep -w</code> won't work for you.</p> <hr> <p>The exit status of this whole pipeline will be <code>0</code> (success) if the database exists or <code>1</code> (failure) if it doesn't. Your shell will set the special variable <code>$?</code> to the exit status of the last command. You can also test the status directly in a conditional:</p> <pre><code>if psql -lqt | cut -d \| -f 1 | grep -qw <db_name>; then # database exists # $? is 0 else # ruh-roh # $? is 1 fi </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload