PostgreSQL Data Types: An Intro

class: center, middle

# PostgreSQL Data Types: An Intro

---

# What is a data type?

The most important of any database constraints is the data type,
or the *attribute domain* in normalization slang.

By declaring an attribute to be of a certain data type, then
PostgreSQL ensures that this property is always true, and then
implements advanced processing features for each data type, so
that you may push the computation to the data, when needed.

PostgreSQL 10.x includes 73 data types out of the box:

```sql
SELECT
  nspname, typname
FROM
  pg_type t
JOIN pg_namespace n
  ON n.oid = t.typnamespace
WHERE
  nspname = 'pg_catalog'
AND typname !~ '(^_|^pg_|^reg|_handler$)'
ORDER BY
  nspname, typname;
```

NOTE: There's more than these, but these are the ones users care about :)

---

# Boolean

You think you know Boolean; you probably don't.

SQL introduces a **NULL** value in the boolean attribute domain, adding
it to the usual TRUE and FALSE values. That gives us *three-valued logic*.

OK, wtf is *that*?

```sql
SELECT
  a::text, b::text,
  (a=b)::text as "a=b",
  format('%s = %s',
    coalesce(a::text, 'null'),
    coalesce(b::text, 'null')) as op,
  format('is %s',
    coalesce((a=b)::text, 'null')) as result
FROM
  (values(true), (false), (null)) v1(a)
  CROSS JOIN
  (values(true), (false), (null)) v2(b);
```

---

# Boolean (cont)

Here’s the nice truth table we get:

```sql
  a   │   b   │  a=b  │      op       │  result
======|=======|=======|===============|=========
true  │ true  │ true  │ true = true   │ is true
true  │ false │ false │ true = false  │ is false
true  │ ¤     │ ¤     │ true = null   │ is null
false │ true  │ false │ false = true  │ is false
false │ false │ true  │ false = false │ is true
false │ ¤     │ ¤     │ false = null  │ is null
¤     │ true  │ ¤     │ null = true   │ is null
¤     │ false │ ¤     │ null = false  │ is null
¤     │ ¤     │ ¤     │ null = null   │ is null
(9 rows)
```

Think of NULL as meaning *I don’t know what this is* rather
than *no value*.

Accordingly, in SQL *null = null* returns NULL !

---

# Boolean (cont)

That’s why we have other SQL operators to work with data that might
be NULL: they are **IS DISTINCT FROM** and **IS NOT DISTINCT FROM**.
Those two operators *pretend* that NULL is the same thing as NULL:

```sql
SELECT
 a::text as left,
 b::text as right,
 (a = b)::text as "=",
 (a <> b)::text as "<>",
 (a IS DISTINCT FROM b)::text as "is distinct",
 (a IS NOT DISTINCT FROM b)::text as "is not distinct from"
FROM
 (values(true),(false),(null)) t1(a)
 CROSS JOIN
 (values(true),(false),(null)) t2(b);
```

---

# Boolean (cont)

Giving us a new truth table:

```sql
 left │ right │ = │ <> │ is distinct │ is not distinct from
=======╪=======╪=======╪=======╪=============╪=====================
 true │ true │ true │ false │ false │ true
 true │ false │ false │ true │ true │ false
 true │ ¤ │ ¤ │ ¤ │ true │ false
 false │ true │ false │ true │ true │ false
 false │ false │ true │ false │ false │ true
 false │ ¤ │ ¤ │ ¤ │ true │ false
 ¤ │ true │ ¤ │ ¤ │ true │ false
 ¤ │ false │ ¤ │ ¤ │ true │ false

¤ │ ¤ │ ¤ │ ¤ │ false │ true <-- this row!!
(9 rows)
```

Giving us NO NULLS in the last two columns!

Booleans can be aggregated thanks to *bool_and()* and *bool_or()*

Use *IS* to test a boolean, not *=* e.g. *IS true*, *IS false*

---

# Text (and its encoding)

The first notion to understand when processing text in any program
is of course the notion of *encoding*.

An encoding is a particular representation of characters in bits and bytes.
In the ASCII encoding the letter A is encoded as:

* the 7-bits byte 1000001
* 65 in decimal
* 41 in hexadecimal

All those numbers are going to be written the same way on-disk.

PostgreSQL handles encoding both on the *server* side and on the *client*
side, allowing for the greatest flexibility.

---

# Text (and its encoding) (cont)

### Server side encoding

To know which encoding your database is using, run the `psql`
command ``\l`:

```sql
List of databases
   Name    │  Owner   │ Encoding │   Collate   │    Ctype    │ …
===========╪==========╪==========╪=============╪=============╪==
postgres   │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ …
template0  │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ …
template1  │ postgres │ UTF8     │ en_US.UTF-8 │ en_US.UTF-8 │ …
```

(ACLs have been removed for clarity)

The encoding here is UTF8 which is the best choice. You can see that the
collation and ctype are English (UTF8).

The **non-encoding** SQL_ASCII accepts any data you throw at it!!

**Never use SQL_ASCII**

---

# Text (and its encoding) (cont)

### Client side encoding

You can ask PostgreSQL to convert all and any data on the fly between
the server-side encoding and your favorite client-side encoding, thanks
to the client_encoding setting.

```sql
SHOW client_encoding;
```

Not all combinations of server encoding and client encoding make sense.
If the server side dataset includes texts in incompatible encodings,
PostgreSQL will issue an error.

```sql
postgres# SET client_encoding TO latin1;
SET
postgres# SELECT * FROM hello WHERE language ~ 'Georgian';
ERROR:  character with byte sequence 0xe1 0x83 0xa5 in encoding "UTF8" ⏎
has no equivalent in encoding "LATIN1"
postgres# RESET client_encoding ;
RESET
```

---

# Text (processing)

#### TEXT, CHAR, and VARCHAR

Text is a *variable, unlimited length* field

Char is a *blank-padded, fixed length* field

Varchar is a *non-padded, variable but limited length* field

*Character varying is an alias for varchar*

**All** are stored identically internally

(*When using varchar(15) you’re basically telling PostgreSQL to manage
a text column with a check constraint of 15 characters.*)

---

# Text (processing) (cont)

#### Regular Expressions

PostgreSQL has **six** operators:

Case sensitive:
* LIKE
* ~
* !~

Non-case sensitive:
* ILIKE
* ~*
* !~*

PostgreSQL also supports indexing for regular expressions thanks to its
trigram extension: pg_trgm !

---

# Text (processing) (cont)

#### Splitting text

Assume the following data:

```sql
─[ RECORD 1 ]──────────────────────────────────────────────
id          │ IF39599
inventory   │ A 2 037
orig_legend │ Serbie, Monastir Bitolj, Un Turc
legend      │ Un Turc
location    │ Monastir (actuelle Bitola), Macédoine
date        │ mai 1913
operator    │ Auguste Léon
...
themes      │ Habillement > Habillement traditionnel,Etres …
            │…humains > Homme,Etres humains > Portrait,Rela…
            │…tions internationales > Présence étrangère
...
collection  │ Archives de la Planète
...
```

---

# Text (processing) (cont)

First, we are going to split the themes column using a regular expression:

```sql
SELECT id, regexp_split_to_table(themes, ',')
FROM opendata.archives_planete
WHERE id = 'IF39599';
```

We get the following table:

```
    id    │             regexp_split_to_table
 =========|===============================================
  IF39599 │ Habillement > Habillement traditionnel
  IF39599 │ Etres humains > Homme
  IF39599 │ Etres humains > Portrait
  IF39599 │ Relations internationales > Présence étrangère
 (4 rows)
 ```

---

# Text (processing) (cont)

Great, now how about creating arrays from the table we created?

```sql
SELECT id,
       regexp_split_to_array(
         regexp_split_to_table(themes, ','),
         ' > ')
       AS categories
FROM opendata.archives_planete
WHERE id = 'IF39599';
 ```

Which gets us:

```
  id    │                     categories
========|====================================================
IF39599 │ {Habillement,"Habillement traditionnel"}
IF39599 │ {"Etres humains",Homme}
IF39599 │ {"Etres humains",Portrait}
IF39599 │ {"Relations internationales","Présence étrangère"}
(4 rows)
```

---

# Text (processing) (cont)

For the content to be normalized we want to have the categories in their own
separate columns, say category and subcategory:

```sql
WITH categories(id, categories) AS
  (
    SELECT id,
            regexp_split_to_array(
              regexp_split_to_table(themes, ','),
              ' > ')
            AS categories
    FROM opendata.archives_planete
   )
SELECT id,
  categories[1] AS category,
  categories[2] AS subcategory
FROM categories
WHERE id = 'IF39599';
```

---

# Text (processing) (cont)

And now we make sense of the open data:

```
     id    │         category          │       subcategory
  =========|===========================|==========================
   IF39599 │ Habillement               │ Habillement traditionnel
   IF39599 │ Etres humains             │ Homme
   IF39599 │ Etres humains             │ Portrait
   IF39599 │ Relations internationales │ Présence étrangère
  (4 rows)
```

---

# Text (processing) (cont)

So, now that we know how to have a clear view of the dataset, let’s inquire
about the categories used in our dataset:

```sql
WITH categories(id, categories) AS
  (
    SELECT id,
           regexp_split_to_array(
             regexp_split_to_table(themes, ','),
             ' > ')
           AS categories
    FROM opendata.archives_planete
  )
SELECT categories[1] AS category,
       categories[2] AS subcategory,
       count(*)
FROM categories
GROUP BY rollup(category, subcategory);
```

---

# Text (processing) (cont)

That query returns 175 rows, so here’s an extract only:
 
```
 category │ subcategory │ count
 ========================|==============================|=======
 Activite économique │ Agriculture / élevage │ 138
 Activite économique │ Artisanat │ 81
 Activite économique │ Banque / finances │ 2
 Activite économique │ Boutique / magasin │ 39
 Activite économique │ Commerce ambulant │ 5
 Activite économique │ Commerce extérieur │ 1
 Activite économique │ Cueillette / chasse │ 9
```

---

# Text (processing) (cont)

We must also mention PostgreSQL’s implementation of a **full text search** with support for:
 
* documents
* advanced text search queries
* ranking
* highlighting
* pluggable parsers
* dictionaries _and_ stemmers
* synonyms
* thesaurus

And, it’s possible to configure *all those pieces*.

---

# Dates and Times

Timestamps are always stored internally as UTC in PostgreSQL.

The database's default timezone DOES NOT need to match the user's timezone.

** ALWAYS store your timestamps in a 'timestamp with time zone' !! **

---

# Dates and Times (cont)

Assume we have been storing Git commit info in a table in our database:

```sql
SELECT project, hash, author, ats, committer, cts, subject
FROM commitlog
WHERE project = 'postgres'
ORDER BY ats DESC
LIMIT 1;
```

ats = author commit timestamp,
cts = committer commit timestamp

```
─[ RECORD 1 ]───────────────────────────────────────
project   │ postgres
hash      │ 65a69dfa08e212556d11e44a5a8a1861fd826ccd
author    │ Tom Lane
ats       │ 2018-04-13 00:39:51+02
committer │ Tom Lane
cts       │ 2018-04-13 00:39:51+02
subject   │ Fix bogus affix-merging code.
```

---

# Dates and Times (cont)

### Time based statistics

With timestamps, we can compute time-based reporting, such as how many commits
each project received each year in their whole history:

```sql
SELECT extract(year from ats) AS year,
        count(*) filter(where project = 'postgres') AS postgres,
        count(*) filter(where project = 'pgloader') AS pgloader
FROM commitlog
GROUP BY year
ORDER BY year;
```

```
 year │ postgres │ pgloader
======|==========|==========
 1996 │      876 │        0
 1997 │     1698 │        0
 1998 │     1744 │        0
 1999 │     1788 │        0
 ...
 2013 │     1368 │      385
 2014 │     1745 │      367
```

---

# Dates and Times (cont)

We can also build a reporting on the repartition of commits by weekday from the
beginning of the project:

```sql
SELECT extract(isodow from ats) AS dow,
        to_char(ats, 'Day') AS day,
        count(*) AS commits,
        round(100.0*count(*)/sum(count(*)) over(), 2) AS pct,
        repeat('■', (100*count(*)/sum(count(*)) over())::int) AS hist
FROM commitlog
WHERE project = 'postgres'
GROUP BY dow, day
ORDER BY dow;
```

```
 dow │    day    │ commits │  pct  │       hist
=====|===========|=========|=======|==================
  1 │ Monday    │    6746 │ 15.06 │ ■■■■■■■■■■■■■■■
  2 │ Tuesday   │    7376 │ 16.46 │ ■■■■■■■■■■■■■■■■
  3 │ Wednesday │    6759 │ 15.09 │ ■■■■■■■■■■■■■■■
  4 │ Thursday  │    7357 │ 16.42 │ ■■■■■■■■■■■■■■■■
  5 │ Friday    │    7276 │ 16.24 │ ■■■■■■■■■■■■■■■■
  6 │ Saturday  │    4855 │ 10.84 │ ■■■■■■■■■■■
  7 │ Sunday    │    4434 │  9.90 │ ■■■■■■■■■■
(7 rows)
```

---

# Dates and Times (cont)

### Time Differences and Percentiles

Another report we can build compares the author commit timestamp with the
committer commit timestamp:

```sql
WITH perc_arrays AS
 (
 SELECT project,
 avg(cts-ats) AS average,
 percentile_cont(array[0.5, 0.9, 0.95, 0.99])
 WITHIN GROUP(ORDER BY cts-ats) AS parr
 FROM commitlog
 WHERE ats <> cts
 GROUP BY project
 )
SELECT project, average,
 parr[1] AS median,
 parr[2] AS "%90th",
 parr[3] AS "%95th",
 parr[4] AS "%99th"
FROM perc_arrays;
```

---

# Dates and Times (cont)

### Time Differences and Percentiles
 
```
─[ RECORD 1 ]─────────────────────────────────────
project │ pgloader
average │ @ 4 days 12 hours 43 mins 10.220859 secs
median │ @ 4 mins 50 secs
%90th │ @ 21 hours 49 mins 23.8 secs
%95th │ @ 24 days 38 hours 54.5 secs
%99th │ @ 163 days 35 hours 37 mins 40.84 secs
═[ RECORD 2 ]═════════════════════════════════════
project │ postgres
average │ @ 1 day 18 hours 48 mins 36.053773 secs
median │ @ 2 mins 11 secs
%90th │ @ 3 hours 27 mins 43 secs
%95th │ @ 3 days 6 hours 1 min 31.2 secs
%99th │ @ 49 days 22 hours 40 mins 59.84 secs
```

---

# Dates and Times (cont)

### Time-Based Reporting

We can show the commits for the PostgreSQL project for the 12th of April 2018:

```sql
\set day '2018-04-12'

SELECT ats::time,
 substring(hash from 1 for 8) AS hash,
 substring(subject from 1 for 40) || '…' AS subject
FROM commitlog
WHERE project = 'postgres'
AND ats >= date :'day'
AND ats < date :'day' + INTERVAL '1 day'
ORDER BY ats;
```

It’s tempting to use the `between` SQL operator, but we would then have to
remember that between *includes both its lower and upper bound* and we would
then have to compute the upper bound as the very last instant of the day. Using
explicit greater than or equal and less than operators makes it possible to
always compute the very first time of the day, which is easier.

---

# Dates and Times (cont)

### Time-Based Reporting

Also, using explicit bound checks allows us to use a single date literal in the
query.

```
  ats    │   hash   │                  subject
=========|==========|===========================================
00:11:29 │ d1e90792 │ Ignore nextOid when replaying an ONLINE …
02:27:12 │ 9e9befac │ Set relispartition correctly for index p…
12:02:45 │ c9c875a2 │ Rename IndexInfo.ii_KeyAttrNumbers array…
12:22:56 │ 08ea7a22 │ Revert MERGE patch…
15:37:22 │ c266ed31 │ Cleanup covering infrastructure…
16:25:13 │ 52405459 │ Fix interference between covering indexe…
16:38:48 │ 3e110a37 │ Fix YA parallel-make hazard, this one in…
20:08:10 │ a4d56f58 │ Use the right memory context for partkey…
21:12:06 │ 2fe97771 │ YA attempt to stabilize the results of t…
21:51:55 │ 181ccbb5 │ Add comment about default partition in c…
21:53:27 │ b8ca984b │ Revert lowering of lock level for ATTACH…
(11 rows)
```

---

# Network Addresses

Today we’re going to introduce network address types.

PostgreSQL includes support for cidr, inet, and macaddr data types. Again,
those types are bundled with indexing support and advanced functions and
operator support.

**The native network address types support both IPv4 and IPv6**

---

# Network Addresses (cont)

### Network Address Types

Web servers logs are a classic source of data to process where we find network
address types:

```conf
211.141.115.145 - - [13/Mar/2005:04:10:18 -0500] "GET / HTTP/1.1" 403 2898 "-"
"Mozilla/4.0 (compatible; MSIE 5.5; Windows 98)"
```

We will transform this into:

```conf
"211.141.115.145";"2005-05-13 04:10:18 -0500";"GET / HTTP/1.1";"403"
```

For loading into our table:

```
CREATE TABLE access_log
 (
 ip inet, <== Network address type
 ts timestamptz,
 request text,
 status integer
 );
```

---

# Network Addresses (cont)

### Network Address Masks & CIDR

One of the things that’s possible to implement thanks to the PostgreSQL inet
data type is an analysis of /24 networks that are to be found in the logs. To
enable that analysis, we can use the `set_masklen()` function which allows us
to transforms an IP address into an arbitrary CIDR network address:

```sql
SELECT DISTINCT ON (ip)
 ip,
 set_masklen(ip, 24) AS inet_24,
 set_masklen(ip::cidr, 24) AS cidr_24 <== note the CAST
FROM access_log
LIMIT 10;
```

```sql
      ip       │     inet_24      │     cidr_24
===============╪==================╪================
 4.35.221.243  │ 4.35.221.243/24  │ 4.35.221.0/24
 4.152.207.126 │ 4.152.207.126/24 │ 4.152.207.0/24
 4.152.207.238 │ 4.152.207.238/24 │ 4.152.207.0/24
```

---

# Network Addresses (cont)

### Network Address Masks & CIDR

Of course, note that you could be analyzing other networks than /24:

```SQL
SELECT DISTINCT ON (ip)
       ip,
       set_masklen(ip::cidr, 27) AS cidr_27,
       set_masklen(ip::cidr, 28) AS cidr_28
FROM access_log
LIMIT 10;
```

Giving:

```SQL
      ip       │     cidr_27      │     cidr_28
===============╪==================╪==================
 4.35.221.243  │ 4.35.221.224/27  │ 4.35.221.240/28
 4.152.207.126 │ 4.152.207.96/27  │ 4.152.207.112/28
 4.152.207.238 │ 4.152.207.224/27 │ 4.152.207.224/28
 4.249.111.162 │ 4.249.111.160/27 │ 4.249.111.160/28
 12.1.223.132  │ 12.1.223.128/27  │ 12.1.223.128/28
```

---

# Network Addresses (cont)

### Network Mask Based Reporting

Equipped with this `set_masklen()` function, it’s now easy to analyze our
access logs using arbitrary CIDR network definitions:

```SQL
SELECT set_masklen(ip::cidr, 24) AS network,
      count(*) AS requests,
      array_length(array_agg(distinct ip), 1) AS ipcount
FROM access_log
GROUP BY network
HAVING array_length(array_agg(distinct ip), 1) > 1
ORDER BY requests DESC, ipcount DESC;
```

```SQL
     network      │ requests │ ipcount
==================╪==========╪=========
 4.152.207.0/24   │      140 │       2
 222.95.35.0/24   │       59 │       2
 211.59.0.0/24    │       32 │       2
 61.10.7.0/24     │       25 │      25
 222.166.160.0/24 │       25 │      24
 ```

---

# Range

Range types are a unique feature of PostgreSQL

Managing two dimensions of data in a single column

The main example is the *daterange* data type

Stores a lower and an upper bound of the range as a single value

This allows PostgreSQL to implement a concurrent safe check against overlapping
ranges.

---

# Range (cont)

The International Monetary Fund publishes exchange rate archives by month.

An exchange rate is relevant from its publication until the next rate is
published.

```SQL
CREATE TABLE raw.rates
 (
  currency text,
  date     date,
  rate     numeric
 );
 ```

(Nothing new here. Just loading the raw rates)

---

# Range (cont)

Once the data is loaded into this table, we can transform it into something
more interesting to work with from an application, the *rates* table:

```SQL
CREATE TABLE rates
 (
 currency text,
 validity daterange, <== our new friend
 rate numeric,

EXCLUDE USING gist (currency WITH =,
                      validity WITH &&)
 );

-- tranform the data
INSERT INTO rates(currency, validity, rate)
  SELECT currency,
        daterange(date,
         LEAD(date) OVER(partition BY currency
          ORDER BY date),
         '[)'
        ) AS validity,
        rate
  FROM raw.rates
  ORDER BY date;
```

---

# Range (cont)

### Range Exclusion constraints

The rates table registers the rate value for a currency and a validity period,
and uses an **exclusion constraint** that guarantees non-overlapping validity
periods for any given currency:

```SQL
exclude using gist (currency with =, validity with &&)
```

This expression reads: exclude any tuple where the currency is = to an existing
currency in our table AND where the validity is overlapping with (&&) any
existing validity in our table.

(Needs the btree_gist extension)

The query uses the `lead()` window function to implement the specification: an
exchange rate is relevant from its publication until the next rate is published.

---

# Range (cont)

### Querying Ranges

```SQL
SELECT currency, validity, rate
FROM rates
WHERE currency = 'Euro'
ORDER BY validity
LIMIT 10;
```

We can see that the validity is a range of dates, and the standard output for
this type is a closed range which includes the first entry and excludes the
second one:

```SQL
 currency │        validity         │   rate
==========╪=========================╪==========
 Euro     │ [2017-05-02,2017-05-03) │ 1.254600
 Euro     │ [2017-05-03,2017-05-04) │ 1.254030
 Euro     │ [2017-05-04,2017-05-05) │ 1.252780
 Euro     │ [2017-05-05,2017-05-08) │ 1.250510
 Euro     │ [2017-05-08,2017-05-09) │ 1.252880
 Euro     │ [2017-05-09,2017-05-10) │ 1.255280
```

---

# Range (cont)

### Querying Ranges

An application needing the rate for a specific time to write the following
query:

```SQL
SELECT rate
FROM rates
WHERE currency = 'Euro'
AND validity @> date '2017-05-18'; <== the 'contains' operator
```

Returns:

```SQL
   rate
==========
 1.240740
(1 row)
```

---

# Arrays

Arrays can be used to denormalize data and avoid lookup tables.

A good rule of thumb for using them is that you mostly use the array
as a whole, even if you might at times search for elements in the array.

Heavier processing is going to be more complex than a lookup table.

---

# Arrays (cont)

Let's assume we're Twitter:

```SQL
CREATE TABLE tweet
 (
   id         bigint primary key,
   date       date,
   hour       time,
   uname      text,
   nickname   text,
   bio        text,
   message    text,
   favs       bigint,
   rts        bigint,
   latitude   double precision,
   longitude  double precision,
   country    text,
   place      text,
   picture    text,
   followers  bigint,
   following  bigint,
   listed     bigint,
   lang       text,
   url        text
 );
 ```

---

# Arrays (cont)

And a row in that table looks like:

<pre>
─[ RECORD 1 ]────────────────────────────────────────────────────────
id │ 721318437075685382
date │ 2016-04-16
hour │ 12:44:00
uname │ Bill Schulhoff
nickname │ BillSchulhoff
bio │ Husband,Dad,GrandDad,Ordained Minister, Umpire, Poker Pla…
 │…yer, Mets, Jets, Rangers, LI Ducks, Sons of Anarchy, Surv…
 │…ivor, Apprentice, O&A, & a good cigar
message │ Wind 3.2 mph NNE. Barometer 30.20 in, Rising slowly. Temp…
 │…erature 49.3 °F. Rain today 0.00 in. Humidity 32%
favs │ ¤
rts │ ¤
latitude │ 40.76027778
longitude │ -72.95472222
country │ US
place │ East Patchogue, NY
picture │ http://pbs.twimg.com/profile_images/378800000718469152/53…
 │…5032cf772ca04524e0fe075d3b4767_normal.jpeg
followers │ 386
following │ 705
listed │ 24
lang │ en
url │ http://www.twitter.com/BillSchulhoff/status/7213184370756…
 │…85382
</pre>

---

# Arrays (cont)

This is not a good fit for PostgreSQL capabilities.

The date and hour fields are separated for no good reason, and it makes
processing them harder than if they were a *timestamptz*.

PostgreSQL knowa how to handle longitude and latitude as a single *point*
entry, which allows much more interesting processing.

We can do better.

---

# Arrays (cont)

### Intro to Arrays

Extract all the tags from the Twitter messages as an array of text:

```SQL
create table hashtag
 (
   id         bigint primary key,
   date       timestamptz,
   uname      text,
   message    text,
   location   point,
   hashtags   text[]
 );
```

---

# Arrays (cont)

### Intro to Arrays

```SQL
with matches as (
  select id,
         regexp_matches(message, '(#[^ ,]+)', 'g') as match
    from tweet
),
    hashtags as (
  select id,
         array_agg(match[1] order by match[1]) as hashtags
    from matches
group by id
)
insert into hashtag(id, date, uname, message, location, hashtags)
     select id,
            date + hour as date,
            uname,
            message,
            point(longitude, latitude),
            hashtags
       from      hashtags
            join tweet using(id);
```

---

# Arrays (cont)

### Intro to Arrays

The PostgreSQL matching function `regexp_matches()` implements what we need
here, with the 'g' flag to return every match found and not just the first.
Those multiple matches are returned one per row, so we then group by tweet id
and array_agg over them, building our array of tags:

```SQL
         id         │             hashtags
====================╪===================================
 720553447402160128 │ {#CriminalMischief,#ocso,#orlpol}
 720553457015324672 │ {#txwx}
 720553458596757504 │ {#DrugViolation,#opd,#orlpol}
 720553466804989952 │ {#Philadelphia,#quiz}
 720553475923271680 │ {#Retail,#hiring!,#job}
```

---

# Arrays (cont)

### Indexing Arrays for Statistics and Profit

Before processing the tags, we create a specialized GIN index. This index
access method allows PostgreSQL to index the contents of the arrays, the tags
themselves, rather than each array as an opaque value:

```SQL
CREATE INDEX ON hashtag USING gin (hashtags);
```

A popular tag in the dataset is #job, and we can easily see how many times it’s
been used, and confirm that our previous index makes sense for looking inside
the hashtags array:

```SQL
EXPLAIN (analyze, verbose, costs off, buffers)
 SELECT count(*)
 FROM hashtag
 WHERE hashtags @> array['#job'];
```

---

# Arrays (cont)

```SQL
QUERY PLAN
══════════════════════════════════════════════════════════════════════
Aggregate (actual time=27.227..27.227 rows=1 loops=1)
  Output: count(*)
  Buffers: shared hit=3715
  ->  Bitmap Heap Scan on public.hashtag (actual time=13.023..23.453 rows=17763 loops=1)
    Output: id, date, uname, message, location, hashtags
    Recheck Cond: (hashtag.hashtags @> '{#job}'::text[])
    Heap Blocks: exact=3707
    Buffers: shared hit=3715
    ->  Bitmap Index Scan on hashtag_hashtags_idx (actual time=11.030..11.030 rows=17763 loops=1)
      Index Cond: (hashtag.hashtags @> '{#job}'::text[])
      Buffers: shared hit=8
Planning time: 0.596 ms
Execution time: 27.313 ms
```

---

# Arrays (cont)

OK, but what if we don't know what hashtags are there to choose from?

```SQL
SELECT tag, count(*)
FROM hashtag, unnest(hashtags) as t(tag) <== ooh, shiny
GROUP BY tag
ORDER BY count DESC
LIMIT 10;
```

The `unnest()` function is a must-have when dealing with arrays in PostgreSQL,
as it allows processing the array’s content as if it were just another relation.

```SQL
     tag      │ count
==============╪=======
 #Hiring      │ 37964
 #Jobs        │ 24776
 #CareerArc   │ 21845
 #Job         │ 21368
 #job         │ 17763
 #Retail      │  7867
 #Hospitality │  7664
```

---

# Arrays (cont)

Hiring seems huge in this dataset. Who is hiring in the #Retail sector and where?

```SQL
SELECT name,
 substring(timezone, '/(.*)') AS tz,
 count(*)
FROM hashtag
LEFT JOIN LATERAL
 (
 SELECT *
 FROM geonames
 ORDER BY location <-> hashtag.location <== find the closest geo
 LIMIT 1
 ) AS geoname
ON true
WHERE hashtags @> array['#Hiring', '#Retail'] <== ANDed together
GROUP BY name, tz
ORDER BY count DESC
LIMIT 10;
```

---

# Arrays (cont)

Which gives us:

```SQL
                       name                       │     tz      │ count
==================================================╪=============╪=======
 San Jose City Hall                               │ Los_Angeles │    31
 Sleep Inn & Suites Intercontinental Airport East │ Chicago     │    19
 Los Angeles                                      │ Los_Angeles │    14
 Dallas City Hall Plaza                           │ Chicago     │    12
 New York City Hall                               │ New_York    │    11
 Jw Marriott Miami Downtown                       │ New_York    │    11
 Gold Spike Hotel & Casino                        │ Los_Angeles │    10
 San Antonio                                      │ Chicago     │    10
 Shoppes at 104                                   │ New_York    │     9
 Fruitville Elementary School                     │ New_York    │     8
```

---

# XML

The SQL standard defines SQL/XML as a datatype with constructors, several
routines, functions, and XML-to-SQL data type mappings.

PostgreSQL implements this essentially just to be standards compliant. XML
processing and indexing is actually pretty limited in Pg.

You don't see a whole lot of XML in production databases out there. The best
option when you need to process XML documents might be the XSLT transformation
language for XML. It should be no surprise that a PostgreSQL extension allows
writing stored procedures in this language. If you have to deal with XML
documents in your database, check out PL/XSLT.

---

# XML (cont)

An example of a PL/XSLT function follows:

```SQL
CREATE EXTENSION plxslt;

CREATE OR REPLACE FUNCTION striptags(xml) RETURNS text
	LANGUAGE xslt
AS $$<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns="http://www.w3.org/1999/xhtml"
>

<xsl:output method="text" omit-xml-declaration="yes"/>

<xsl:template match="/">
 <xsl:apply-templates/>
 </xsl:template>

</xsl:stylesheet>
$$;
```

---

# XML (cont)

And can be used like:

```SQL
CREATE TABLE docs
 (
   id      serial primary key,
   content xml
 );

INSERT INTO docs(content)
 VALUES ('<?xml version="1.0"?>
<html xmlns="http://www.w3.org/1999/xhtml">
<body>hello</body>
</html>');

SELECT id, striptags(content) <== our function
FROM docs;
```

Which returns:

```SQL
 id │ striptags
====╪===========
  1 │ hello
```

(I didn't say it was a *useful* function)

---

# JSON (also know as the new sexy)

PostgreSQL implemented a *very* simple JSON datatype back in the 9.2 release.

The JSON datatype is actually text under the hood, with a verification that the
format is valid json input (much like XML).

Pg later implemented a binary version of the JSON datatype, this time with a
full set of operators and functions to work with.

JSONB - the B is for Better :)

---

# JSON (cont)

There are some incompatibilities in between the text-based json datatype and
the newer jsonb version of it:

* The json datatype, being a text datatype, stores the data presentation
exactly as it is sent to PostgreSQL, including whitespace and indentation, and
also multiple-keys when present (no processing at all is done on the content,
only form validation).

* The jsonb datatype is an advanced binary storage format with full processing,
indexing and searching capabilities, and as such pre-processes the JSON data to
an internal format, which does include a single value per key; and also isn’t
sensitive to extra whitespace or indentation.

Choose wisely (choose JSONB)

---

# JSON (cont)

Example difference:

```SQL
CREATE TABLE js(id serial primary key, extra json);
INSERT INTO js(extra)
     VALUES ('[1, 2, 3, 4]'),
            ('[2, 3, 5, 8]'),
            ('{"key": "value"}');
```

Now query:

```SQL
SELECT * FROM js WHERE extra @> '2';
ERROR:  operator does not exist: json @> unknown
LINE 1: select * from js where extra @> '2';
```

---

# JSON (cont)

Convert to JSONB and query:

```SQL
ALTER TABLE js ALTER COLUMN extra TYPE jsonb;

SELECT * FROM js WHERE extra @> '2';

id │    extra
====╪==============
  1 │ [1, 2, 3, 4]
  2 │ [2, 3, 5, 8]
 ```

We can also now search for JSON arrays containing another JSON array:

```SQL
SELECT * FROM js WHERE extra @> '[2,4]';

id │    extra
====╪==============
  1 │ [1, 2, 3, 4]
```

---

# JSON (cont)

Two use cases for JSON in PostgreSQL are very commonly found:

* The application needs to manage a set of documents that happen to be
formatted in JSON.

* Application designers and developers aren’t too sure about the exact set of
fields needed for a part of the data model, and want this data model to be
very easily extensible.

See 'jsonb Indexing' in the PostgreSQL documentation for more information about
the `jsonb_path_ops` which provides a very good general purpose
index for the @> operator:

```SQL
CREATE INDEX ON js USING gin (extra jsonb_path_ops);
```

---

# ENUM

This data type has been added to PostgreSQL in order to make it easier to
support migrations from MySQL.

Proper relational design would use a reference table and a foreign key instead.

Be aware that in MySQL there’s no create type statement for enum types, so each
column using an enum is assigned its own data type.

When using PostgreSQL, each ENUM type is registered in the system catalogs and
can be used anywhere PostgreSQL expects a type name.

Internally, the ENUM values are stored as integers.

Remember that enum are to be used against a very static type definition: a list
of values that you expect never to change in the life time of your application!

---

# ENUM (cont)

An example:

```SQL
CREATE TYPE color_t AS ENUM('blue', 'red', 'gray', 'black');

CREATE TABLE cars
 (
 brand text,
 model text,
 color color_t <== our enum type
 );

INSERT INTO cars(brand, model, color)
  VALUES ('ferari', 'testarosa', 'red'),
      ('aston martin', 'db2', 'blue'),
      ('bentley', 'mulsanne', 'gray'),
      ('ford', 'T', 'black');

INSERT INTO cars(brand, model, color)
  VALUES ('chevy','camero','grey');

ERROR:  22P02: invalid input value for enum color_t: "grey"
```

---

# Point

Geometric data types represent two-dimensional spatial objects.

Points are the fundamental two-dimensional building block for geometric types.

Values of type point are specified using either of the following syntaxes:

```
( x , y )
  x , y
```

where x and y are the respective coordinates, as floating-point numbers.

Points are output using the first syntax.

---

# Point (cont)

Let's create a table w/ some download GIS data:

```SQL
CREATE TABLE geoname.geoname
 (
 geonameid bigint primary key,
 name text,
 location point, <== looky here
 isocode integer,
 regcode text,
 discode text,
 class char(1),
 feature text,
 population bigint,
 elevation bigint,
 timezone text,
);

CREATE INDEX ON geoname.geoname USING gist(location);
 ```

---

# Point (cont)

We can see that the GeoNames data is highly skewed towards Asia, North America,
and then Europe. Of course, the Antartica data is not very dense:

```SQL
select continent.name,
         count(*),
         round(100.0 * count(*) / sum(count(*)) over(), 2) as pct,
         repeat('■', (100 * count(*) / sum(count(*)) over())::int) as hist
    from geoname.geoname
         join geoname.country using(isocode)
         join geoname.continent
           on continent.code = country.continent
group by continent.name order by continent.name;

name      │  count  │  pct  │               hist
===============╪=========╪=======╪===================================
 Africa        │ 1170043 │ 10.14 │ ■■■■■■■■■■
 Antarctica    │   21125 │  0.18 │
 Asia          │ 3772195 │ 32.70 │ ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
 Europe        │ 2488807 │ 21.58 │ ■■■■■■■■■■■■■■■■■■■■■■
 North America │ 3210802 │ 27.84 │ ■■■■■■■■■■■■■■■■■■■■■■■■■■■■
 Oceania       │  354325 │  3.07 │ ■■■
 South America │  517347 │  4.49 │ ■■■■
```

---

# Point (cont)

Given that 'location' is a point, and that we built a GIST index, we can easily
find the nearest neighbor (kNN) for any point:

```SQL
SELECT id,
 ROUND((hashtag.location <-> geoname.location)::numeric, 3) AS dist,
 country.iso,
 region.name AS region,
 district.name AS district
FROM hashtag
LEFT JOIN LATERAL
 (
 SELECT geonameid, isocode, regcode, discode, location
 FROM geoname.geoname
 ORDER BY location <-> hashtag.location <== the <-> computes distance
 LIMIT 1 <== limits us to the closest
 ) AS geoname
ON true
LEFT JOIN geoname.country using(isocode)
LEFT JOIN geoname.region using(isocode, regcode)
LEFT JOIN geoname.district using(isocode, regcode, discode)
ORDER BY id
LIMIT 5;
```

---

# Point (cont)

Which gives us:

```
         id         │ dist  │ iso │    region    │      district
====================╪=======╪=====╪==============╪=====================
 720553447402160128 │ 0.004 │ US  │ Florida      │ Orange County
 720553457015324672 │ 0.004 │ US  │ Texas        │ Smith County
 720553458596757504 │ 0.001 │ US  │ Florida      │ Orange County
 720553466804989952 │ 0.001 │ US  │ Pennsylvania │ Philadelphia County
 720553475923271680 │ 0.000 │ US  │ New York     │ Nassau County
```

---

# Numerics

By request!

Numeric types consist of two-, four-, and eight-byte integers, four- and
eight-byte floating-point numbers, and selectable-precision decimals.

```
Name, Size, Range
==========================================================================================
smallint, 2 bytes, -32768 to +32767
integer, 4 bytes,	-2147483648 to +2147483647
bigint, 8 bytes, -9223372036854775808 to +9223372036854775807

decimal, variable (exact), <= 131072 digits before decimal; <= 16383 digits after decimal
numeric, variable (exact), <= 131072 digits before decimal; <= 16383 digits after decimal

real, 4 bytes	(inexact), 6 decimal digits precision
double precision, 8 bytes (inexact), 15 decimal digits precision

smallserial, 2 bytes,	1 to 32767
serial,	4 bytes, 1 to 2147483647
bigserial, 8 bytes,	1 to 9223372036854775807
```

---

# Numerics (cont)

The types smallint, integer, and bigint store whole numbers only

SQL only specifies the integer types integer (or int), smallint, and bigint.

Pg allows int2, int4, and int8 as aliases.

The type `numeric` can store numbers with a very large number of digits. It is
especially recommended for storing monetary amounts and other quantities where
*exactness is required*.

Calculations with numeric values yield exact results where possible and are
**very slow** compared to the integer types, or to the floating-point types.

---

# Numerics (cont)

The scale of a numeric is the count of decimal digits in the fractional part,
to the right of the decimal point.

The precision of a numeric is the total count of significant digits in the
entire number.

```
NUMERIC(precision, scale) <== precision must be positive, the scale zero or positive

NUMERIC(precision) <== scale of 0

NUMERIC <== any precision/scale
```

`NUMERIC(6,2)` holds 1234.56 not 123456.0

Numeric allows for 'not a number'! `UPDATE table SET x = 'NaN'`

`DECIMAL` is an alias for `NUMERIC`

---

# Numerics (cont)

The data types real and double precision are inexact, variable-precision numeric
types.

Inexact means that some values cannot be converted exactly to the internal
format and are stored as approximations.

Comparing two floating-point values for equality might not always work as expected

In addition to ordinary numeric values, the floating-point types have several special values:

```
Infinity
-Infinity
NaN
```

---

# Numerics (cont)

PostgreSQL also supports the SQL-standard notations `float` and `float(p)` for
specifying inexact numeric types.

Here, p specifies the minimum acceptable precision in binary digits.

`float(1)` to `float(24)` == `real`

`float(25)` to `float(53)` == `double precision`

`float` with no precision specified is taken to mean `double precision`

---

# Numerics (cont)

I LIED.

The data types `smallserial`, `serial` and `bigserial` are not true types!

Shorthand for creating unique identifier columns

```SQL
CREATE TABLE tablename (
    colname SERIAL
);
```

is really:

```SQL
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
```

**Any value allocated from the sequence is still "used up" even if aborted or
rolled back. THIS CAN CAUSE GAPS**

</optgroup>