Snipt.net is closing on December 31st, 2016

For more information, please visit this blog post.

snipt

Ctrl+h for KB shortcuts

PostgreSQL SQL dialect

postgres > select all custom plpgsql functions

1
2
3
4
5
6
7
8
select 
  pp.proname, 
  pg_get_functiondef(pp.oid)
from pg_proc pp
inner join pg_namespace pn on (pp.pronamespace = pn.oid)
inner join pg_language pl on (pp.prolang = pl.oid)
where pl.lanname IN ('plpgsql') 
  and pn.nspname <> 'information_schema';
https://snipt.net/embed/b0d97086d193266305bee10c8f8adbc4/
https://snipt.net/raw/b0d97086d193266305bee10c8f8adbc4/
b0d97086d193266305bee10c8f8adbc4
postgresql
PostgreSQL SQL dialect
8
2016-12-09T23:20:10
True
False
False
/api/public/snipt/148005/
postgres-select-all-custom-functions
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a> <a href="#L-5">5</a> <a href="#L-6">6</a> <a href="#L-7">7</a> <a href="#L-8">8</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">select</span> </span><span id="L-2"><a name="L-2"></a> <span class="n">pp</span><span class="mf">.</span><span class="n">proname</span><span class="p">,</span> </span><span id="L-3"><a name="L-3"></a> <span class="n">pg_get_functiondef</span><span class="p">(</span><span class="n">pp</span><span class="mf">.</span><span class="n">oid</span><span class="p">)</span> </span><span id="L-4"><a name="L-4"></a><span class="k">from</span> <span class="n">pg_proc</span> <span class="n">pp</span> </span><span id="L-5"><a name="L-5"></a><span class="k">inner</span> <span class="k">join</span> <span class="n">pg_namespace</span> <span class="n">pn</span> <span class="k">on</span> <span class="p">(</span><span class="n">pp</span><span class="mf">.</span><span class="n">pronamespace</span> <span class="o">=</span> <span class="n">pn</span><span class="mf">.</span><span class="n">oid</span><span class="p">)</span> </span><span id="L-6"><a name="L-6"></a><span class="k">inner</span> <span class="k">join</span> <span class="n">pg_language</span> <span class="n">pl</span> <span class="k">on</span> <span class="p">(</span><span class="n">pp</span><span class="mf">.</span><span class="n">prolang</span> <span class="o">=</span> <span class="n">pl</span><span class="mf">.</span><span class="n">oid</span><span class="p">)</span> </span><span id="L-7"><a name="L-7"></a><span class="k">where</span> <span class="n">pl</span><span class="mf">.</span><span class="n">lanname</span> <span class="k">IN</span> <span class="p">(</span><span class="s1">&#39;plpgsql&#39;</span><span class="p">)</span> </span><span id="L-8"><a name="L-8"></a> <span class="k">and</span> <span class="n">pn</span><span class="mf">.</span><span class="n">nspname</span> <span class="o">&lt;&gt;</span> <span class="s1">&#39;information_schema&#39;</span><span class="p">;</span> </span></pre></div> </td></tr></table>
postgres, postgresql, psql

Text only

PostgreSQL: Create a user and a database, set as owner, and grant privileges.

1
2
3
CREATE USER bob WITH PASSWORD 'bazzy';
CREATE DATABASE baz WITH OWNER bob;
GRANT ALL PRIVILEGES ON DATABASE baz to foo;
https://snipt.net/embed/77daad8174ebf6c04b747a8d2617ce17/
https://snipt.net/raw/77daad8174ebf6c04b747a8d2617ce17/
77daad8174ebf6c04b747a8d2617ce17
text
Text only
3
2016-12-10T07:42:10
True
False
False
Dec 17, 2015 at 02:24 PM
/api/public/snipt/140056/
postgresql-create-a-user-and-a-database-set-as-owner-and-grant-privileges-5a568550
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a>CREATE USER bob WITH PASSWORD &#39;bazzy&#39;; </span><span id="L-2"><a name="L-2"></a>CREATE DATABASE baz WITH OWNER bob; </span><span id="L-3"><a name="L-3"></a>GRANT ALL PRIVILEGES ON DATABASE baz to foo; </span></pre></div> </td></tr></table>
postgres, postgresql, psql

Bash

psql > list databases

$ psql -l
https://snipt.net/embed/235856f12c616a3577752c1c5d7d09bc/
https://snipt.net/raw/235856f12c616a3577752c1c5d7d09bc/
235856f12c616a3577752c1c5d7d09bc
bash
Bash
1
2016-12-09T11:52:43
True
False
False
/api/public/snipt/136197/
psql-list-databases-8ad5d699
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a>$ psql -l </span></pre></div> </td></tr></table>
database, postgres, postgresql, psql

PostgreSQL SQL dialect

psql force drop database

1
2
3
4
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
  AND pid <> pg_backend_pid();
https://snipt.net/embed/cb2252f2e6851df987ee16e35bcb99a8/
https://snipt.net/raw/cb2252f2e6851df987ee16e35bcb99a8/
cb2252f2e6851df987ee16e35bcb99a8
postgresql
PostgreSQL SQL dialect
4
2016-12-10T05:24:59
True
False
False
/api/public/snipt/136146/
psql-force-drop-database-b1947d50
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="n">pg_terminate_backend</span><span class="p">(</span><span class="n">pg_stat_activity</span><span class="mf">.</span><span class="n">pid</span><span class="p">)</span> </span><span id="L-2"><a name="L-2"></a><span class="k">FROM</span> <span class="n">pg_stat_activity</span> </span><span id="L-3"><a name="L-3"></a><span class="k">WHERE</span> <span class="n">pg_stat_activity</span><span class="mf">.</span><span class="n">datname</span> <span class="o">=</span> <span class="s1">&#39;TARGET_DB&#39;</span> </span><span id="L-4"><a name="L-4"></a> <span class="k">AND</span> <span class="n">pid</span> <span class="o">&lt;&gt;</span> <span class="n">pg_backend_pid</span><span class="p">();</span> </span></pre></div> </td></tr></table>
drop, psql

Bash

Import a .pgdump into an existing PSQL DB.

$ psql dbname < file.pgdump
https://snipt.net/embed/c87929b6bd8df6ce764c25956f976436/
https://snipt.net/raw/c87929b6bd8df6ce764c25956f976436/
c87929b6bd8df6ce764c25956f976436
bash
Bash
1
2016-12-04T07:25:26
True
False
False
Jul 19, 2012 at 04:50 PM
/api/public/snipt/44160/
import-a-pgdump-into-an-existing-psql-db
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a>$ psql dbname &lt; file.pgdump </span></pre></div> </td></tr></table>
pgdump, postgresql, psql

SQL

Monitor active PostgreSQL queries against a database

1
2
3
4
SELECT procpid, current_query, waiting
FROM   pg_stat_activity
WHERE  datname = 'your_database_name'
AND    current_query <> '<IDLE>';
https://snipt.net/embed/47684408f6eae77c0384c2e2644d693d/
https://snipt.net/raw/47684408f6eae77c0384c2e2644d693d/
47684408f6eae77c0384c2e2644d693d
sql
SQL
4
2016-12-10T07:37:53
True
False
False
/api/public/snipt/13311/
monitor-active-postgresql-queries-against-a-database
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="n">procpid</span><span class="p">,</span> <span class="n">current_query</span><span class="p">,</span> <span class="n">waiting</span> </span><span id="L-2"><a name="L-2"></a><span class="k">FROM</span> <span class="n">pg_stat_activity</span> </span><span id="L-3"><a name="L-3"></a><span class="k">WHERE</span> <span class="n">datname</span> <span class="o">=</span> <span class="s1">&#39;your_database_name&#39;</span> </span><span id="L-4"><a name="L-4"></a><span class="k">AND</span> <span class="n">current_query</span> <span class="o">&lt;&gt;</span> <span class="s1">&#39;&lt;IDLE&gt;&#39;</span><span class="p">;</span> </span></pre></div> </td></tr></table>
db, psql, query

Text only

copy psql db from local to remote

pg_dump -C dbname | bzip2 | ssh  [email protected] -p 99 "bunzip2 | psql dbname"
https://snipt.net/embed/42502787170385059b0bf15f30bd5313/
https://snipt.net/raw/42502787170385059b0bf15f30bd5313/
42502787170385059b0bf15f30bd5313
text
Text only
2
2016-12-06T03:00:00
True
False
False
/api/public/snipt/10416/
copy-psql-db
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a>pg_dump -C dbname | bzip2 | ssh [email protected] -p 99 &quot;bunzip2 | psql dbname&quot; </span></pre></div> </td></tr></table>
db, dump, psql

Bash

Output PostgreSQL command as CSV

psql -F\; -A --pset footer -f input_file.sql -o output_file.csv database_name
https://snipt.net/embed/73e4d7ff8cf84e68e2485ca81586ee45/
https://snipt.net/raw/73e4d7ff8cf84e68e2485ca81586ee45/
73e4d7ff8cf84e68e2485ca81586ee45
bash
Bash
1
2016-12-10T07:38:12
True
False
False
/api/public/snipt/9837/
output-postgresql-command-as-csv
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a>psql -F<span class="se">\;</span> -A --pset footer -f input_file.sql -o output_file.csv database_name </span></pre></div> </td></tr></table>
csv, postgresql, psql
Copyrighted, illegal, or inappropriate content? Email [email protected].