PostgreSQL enums testing

postgresql_types.sql

CREATE TYPE MOOD AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE person_with_type (
  name         TEXT,
  current_mood MOOD DEFAULT 'ok'
);

INSERT INTO person_with_type (name, current_mood)
  SELECT
    name,
    current_mood
  FROM (
         SELECT
           generate_series(1, 100000000)                      AS id,
           md5(random() :: TEXT)                              AS name,
           ('{sad,ok,happy}' :: MOOD []) [ceil(random() * 3)] AS current_mood
       ) AS data_table;

-------------------------------------------------------------------------------

CREATE TABLE person_with_text (
  name         TEXT,
  current_mood TEXT DEFAULT 'ok'
);


INSERT INTO person_with_text (name, current_mood)
  SELECT
    name,
    current_mood
  FROM (
         SELECT
           generate_series(1, 100000000)                      AS id,
           md5(random() :: TEXT)                              AS name,
           ('{sad,ok,happy}' :: TEXT []) [ceil(random() * 3)] AS current_mood
       ) AS data_table;

-------------------------------------------------------------------------------

SELECT
  *,
  pg_size_pretty(total_bytes) AS total,
  pg_size_pretty(index_bytes) AS INDEX,
  pg_size_pretty(toast_bytes) AS toast,
  pg_size_pretty(table_bytes) AS TABLE
FROM (
       SELECT
         *,
         total_bytes - index_bytes - COALESCE(toast_bytes, 0) AS table_bytes
       FROM (
              SELECT
                c.oid,
                nspname                               AS table_schema,
                relname                               AS TABLE_NAME,
                c.reltuples                           AS row_estimate,
                pg_total_relation_size(c.oid)         AS total_bytes,
                pg_indexes_size(c.oid)                AS index_bytes,
                pg_total_relation_size(reltoastrelid) AS toast_bytes
              FROM pg_class c
                LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
              WHERE relkind = 'r'
            ) a
     ) a
WHERE TABLE_NAME LIKE 'person_with_%';

-------------------------------------------------------------------------------
-- Cleaning
-------------------------------------------------------------------------------

DROP TABLE person_with_text, person_with_type;
DROP TYPE MOOD;
VACUUM FULL;