-
Notifications
You must be signed in to change notification settings - Fork 0
/
Postgres.html
141 lines (116 loc) · 4.16 KB
/
Postgres.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
<html>
<head>
<link rel="Stylesheet" type="text/css" href="https://maxcdn.bootstrapcdn.com/bootswatch/3.3.7/cosmo/bootstrap.min.css" />
<title>Postgres</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/9.8.0/styles/default.min.css" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/9.8.0/highlight.min.js"></script>
<script>
window.onload = function() {
var allPre, i, j;
allPre = document.getElementsByTagName("pre");
for (i = 0, j = allPre.length; i < j; i++) {
hljs.highlightBlock(allPre[i]);
}
};
</script>
</head>
<body>
<a href="https://github.com/coisnepe/vimwiki_html" class="github-corner" aria-label="View source on Github"><svg width="80" height="80" viewBox="0 0 250 250" style="fill:#151513; color:#fff; position: absolute; top: 0; border: 0; right: 0;" aria-hidden="true"><path d="M0,0 L115,115 L130,115 L142,142 L250,250 L250,0 Z"></path><path d="M128.3,109.0 C113.8,99.7 119.0,89.6 119.0,89.6 C122.0,82.7 120.5,78.6 120.5,78.6 C119.2,72.0 123.4,76.3 123.4,76.3 C127.3,80.9 125.5,87.3 125.5,87.3 C122.9,97.6 130.6,101.9 134.4,103.2" fill="currentColor" style="transform-origin: 130px 106px;" class="octo-arm"></path><path d="M115.0,115.0 C114.9,115.1 118.7,116.5 119.8,115.4 L133.7,101.6 C136.9,99.2 139.9,98.4 142.2,98.6 C133.8,88.0 127.5,74.4 143.8,58.0 C148.5,53.4 154.0,51.2 159.7,51.0 C160.3,49.4 163.2,43.6 171.4,40.1 C171.4,40.1 176.1,42.5 178.8,56.2 C183.1,58.6 187.2,61.8 190.9,65.4 C194.5,69.0 197.7,73.2 200.1,77.6 C213.8,80.2 216.3,84.9 216.3,84.9 C212.7,93.1 206.9,96.0 205.4,96.6 C205.1,102.4 203.0,107.8 198.3,112.5 C181.9,128.9 168.3,122.5 157.7,114.1 C157.9,116.9 156.7,120.9 152.7,124.9 L141.0,136.5 C139.8,137.7 141.6,141.9 141.8,141.8 Z" fill="currentColor" class="octo-body"></path></svg></a>
<a href="index.html">Index</a>
<hr>
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div id="Postgres"><h3 id="Postgres">Postgres</h3></div>
<ul>
<li>
<a href="Postgres.html#psql commands">psql</a>
<li>
<a href="Postgres.html#jsonb">jsonb</a>
<li>
<a href="Postgres.html#sample queries">Sample queries</a>
</ul>
<div id="Postgres-psql commands"><h4 id="psql commands">psql commands</h4></div>
<p>
Command line
</p>
<pre>
$ psql
-c # Execute a command
-l # List databases
-h # Host
-d # Database name
-U # Username
-W # Password
</pre>
<p>
<br />
</p>
<p>
Console
</p>
<pre>
> \? # Help
> \l # List databases
> \c # Connect to a database
> \dt # List tables
> \du # List roles
> \d # List tables, views, sequences (\dS+)
> \d Name # Describe table, views, sequences (\dS+)
> SET search_path = some_schema # set default schema to query
</pre>
<p>
<br />
</p>
<div id="Postgres-jsonb"><h4 id="jsonb">jsonb</h4></div>
<p>
<a href="http://www.postgresql.org/docs/9.6/interactive/functions-json.html#//apple_ref/cpp/Function/jsonb_object_keys">Official Documentation</a>
</p>
<p>
Search and select keys in jsonb columns
</p>
<pre class="sql">
SELECT id, foodfacts->'product_name', foodfacts->'brands'
FROM products
WHERE foodfacts @> '{"id": "12345"}';
# WHERE foodfacts->>'id' = '12345';
</pre>
<p>
List all keys of a record's jsonb column
</p>
<pre class="sql">
SELECT jsonb_object_keys(foodfacts) FROM products;
</pre>
<p>
Display jsonb column as a table
</p>
<pre class="sql">
SELECT * FROM json_each((
SELECT foodfacts::json from products
));
<br />
==== Sample queries ====
Find the closest representations in time, from now
{{{class="sql"
SELECT representations.id, events.id as event_id,
now(),
representations.start_date_time,
(start_date_time - now()) as time_diff
FROM representations
JOIN events ON representations.event_id = events.id
WHERE events.is_display IS true
ORDER BY
abs(
extract(
epoch FROM
(start_date_time - now())))
LIMIT 10;
}}
</pre>
</div>
</div>
</div>
</body>
</html>