View source for Linux/Postgres

== Comment ==
<pre>
-- single line comment
/* multi line comment */
</pre>

== Data types ==
{| class="wikitable"
|+
!Type
!min
!max
!comment
|-
|boolean
|
|
|
|-
|char(n)
|
|
|
|-
|varchar(n)
|
|
|
|-
|text
|
|
|
|-
|smallint 
| -32768
|32767
|
|-
|int
|
|
|
|-
|serial
|
|
|(~auto_increment in mysql)
|-
|float(n)
|
|
|
|-
|real / float8
|
|
|
|-
|numeric(p,s)
|
|
|
|-
|date
|
|
|
|-
|time
|
|
|
|-
|timestamp
|
|
|
|-
|timestamptz
|
|
|
|-
|interval
|
|
|
|-
|json
|
|
|
|-
|jsonb
|
|
|
|-
|uuid
|
|
|
|-
| + special
|
|
|}

== Operators ==
{| class="wikitable"
|+
!
!
!Example
|-
|=
|equal
|WHERE "time" = <code>'2024-09-28 15:46:09.602492+00'</code>
|-
|<>, !=
|not equal
|
|-
|<, >
|less/greater than
|
|-
|<=, >=
|less/greater than or equal
|
|-
|
|
|
|-
|IS NULL
|
|
|-
|IS NOT NULL
|
|
|-
|
|
|
|-
|LIKE
|
|WHERE "time
|-
|
|_ = single char
|escaped = \_
|-
|
|% = 0 or more chars
|escaped = \%
|-
|NOT LIKE
|
|
|-
|ILIKE
|case-insensitive LIKE
|
|-
|
|
|
|-
|IN
|
|WHERE name IN ('Joe', 'Bob', 'Jane')
|-
|
|
|WHERE user_id IN (SELECT user_id FROM orders)
|-
|NOT IN
|
|
|-
|
|
|
|-
|~
|regex
|
|-
|!~
|not regex
|
|}

== Databases ==
* List all databases
<blockquote>
<pre>
SELECT datname FROM pg_database
WHERE datistemplate = false;
</pre>
</blockquote>

== Tables ==
* List all tables
<blockquote>
<pre>
SELECT table_catalog,table_schema,table_name FROM information_schema.tables 
WHERE table_type = 'BASE TABLE' AND table_schema = 'public' 
ORDER BY table_type, table_name
</pre>
</blockquote>

* Biggest tables by size
<blockquote>
<pre>
SELECT
  nspname || '.' || relname AS "Object Name", relkind As "Object Type",
  pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;
</pre>
</blockquote>

== List all users==
<blockquote>
<pre>
SELECT * FROM pg_user;
</pre>
</blockquote>

== Links ==
* https://www.w3schools.com/postgresql/
* https://www.timescale.com/learn/postgres-cheat-sheet
* https://www.timescale.com/learn/postgres-basics
* https://www.timescale.com/learn/postgres-guides
* https://www.timescale.com/learn/postgres-best-practices

[[Category:Linux/Services]]
[[Category:Linux]]
[[Category:Programming]]