psql_good2know.md

PSQL Cheat-Sheet

Some Facts

• NULLis never equal to anything.
• 7 = NULLand 7 <> NULL always yield NULL
• 7 IS DISTINCT FROM NULL is TRUE
• 7 IS NOT DISTINCT FROM NULL is FALSE
• Match UNKNOWN via IS (NOT) UNKNOWN
• Only records with a true conditional in a WHERE statement are matched (and hence fetched).

Truth Tables

AND	t	u	f
t	t	u	f
u	u	u	f
f	f	f	f

OR	t	u	f
t	t	t	t
u	t	u	u
f	t	u	f

Negating UNKNNOWN yields UNKNOWN.

Example

Create a temp. test table called foobar with one culumn foo:

CREATE TEMPORARY TABLE FOOBAR (foo INT)
insert into foobar (foo) values (1);
insert into foobar (foo) values (22);
insert into foobar (foo) values (null);

Group table by column foo:

select foo, count(*) from foobar group by foo;

 foo | count
-----+-------
     |     1
  1  |     1
  22 |     1
(3 rows)

Group table by column foo having a foo values > 0:

select foo, count(*) from foobar group by foo having foo > 0;

 foo | count
-----+-------
  1  |     1
  22 |     1
(2 rows)