Skip to content

Instantly share code, notes, and snippets.

@Skyross
Last active February 19, 2018 09:43
Show Gist options
  • Save Skyross/c6f3bdc094155ce877f39803c195d1bc to your computer and use it in GitHub Desktop.
Save Skyross/c6f3bdc094155ce877f39803c195d1bc to your computer and use it in GitHub Desktop.
PostgreSQL enums testing
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment