test_postgres_enum.sql

DROP TABLE IF EXISTS public.with_enum;
DROP TABLE IF EXISTS public.with_text;
DROP TYPE IF EXISTS my_enum;

CREATE TYPE public.my_enum AS ENUM ('axyzxyz', 'bxyzxyz', 'cxyzxyz', 'dxyzxyz');

CREATE TABLE public.with_enum (
  id serial NOT NULL PRIMARY KEY,
  blah integer,
  thecolumn my_enum
);

CREATE TABLE public.with_text
(
  id serial NOT NULL PRIMARY KEY,
  blah integer,
  thecolumn text
);

INSERT INTO public.with_enum(blah, thecolumn)
SELECT random() * 10000, (CASE (random() * 3)::integer WHEN 0 THEN 'axyzxyz' WHEN 1 THEN 'bxyzxyz' WHEN 2 THEN 'cxyzxyz' ELSE 'dxyzxyz' END)::my_enum FROM generate_series(1,5000000);

INSERT INTO public.with_text(blah, thecolumn)
SELECT random() * 10000, CASE (random() * 3)::integer WHEN 0 THEN 'axyzxyz' WHEN 1 THEN 'bxyzxyz' WHEN 2 THEN 'cxyzxyz' ELSE 'dxyzxyz' END FROM generate_series(1,5000000);

SELECT table_schema,
       table_name,
       pg_size_pretty(total_bytes) AS total,
       pg_size_pretty(index_bytes) AS index,
       pg_size_pretty(toast_bytes) AS toast,
       pg_size_pretty(total_bytes-index_bytes-COALESCE(toast_bytes,0)) AS table
FROM (
  SELECT nspname AS table_schema,
         relname AS table_name,
         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' AND nspname = 'public'
) a;