3 ms·

I agree with this, I don't use enums they are always more trouble than they are worth. They break FDW unless they are pre-created on the importing side. Super

• wojcikstefan 3y ago
  Apologies for my ignorance, but what does "FDW" stand for in your comment? :)
  • Quekid5 3y ago
    Foreign Data Wrapper
    • adgjlsfhk1 3y ago
      specifically, they are one of the coolest features in postgres. Have you ever wanted to pretend that a CSV (or your filesystem or google) or basically anything else was a SQL database? With a couple hundred lines of code, you can do this. The performance won't be great, but it's ridiculously flexible.
      • ComputerGuru 3y ago
        I have a two-liner shell script that turns a csv into a virtual SQLite db (./to_sql.sh foo.csv) and lets me use the full power of SQLite. Usually that’s enough for my purposes, but I do use pgsql‘s copy from csv to generate tables representing foreign data imported from a csv (say census info or NAICS occupation codes).
  • deleted 3y ago
    [deleted]
• efxhoy 3y ago
  I had to implement a workaround for that for our DW which imports from two different app databases. IMO this should be part of import foreign schema but then you would have to qualify the names somehow if you were importing from more than one db. I wrote this as a workaround, it runs every day as part of our data import job. DROP SCHEMA IF EXISTS fdw_enum CASCADE; CREATE SCHEMA fdw_enum; -- Get names of the enums using ::regtype casting and label and sort_order from app_a and app_b. CREATE VIEW fdw_enum.app_a_enums AS SELECT * FROM dblink('fdw_app_a', $QUERY$ SELECT enumtypid::regtype AS name, enumsortorder sort_order, enumlabel label FROM pg_enum; $QUERY$ ) AS t (name text, sort_order integer, label text); CREATE VIEW fdw_enum.app_b_enums AS SELECT * FROM dblink('fdw_app_b', $QUERY$ SELECT enumtypid::regtype AS name, enumsortorder sort_order, enumlabel label FROM pg_enum; $QUERY$ ) AS t (name text, sort_order integer, label text); -- Ensure enums with the same names aren't defined in both app_a and app_b. DO $DO$ DECLARE matching_count integer; BEGIN SELECT COUNT(*) into matching_count FROM fdw_enum.app_a_enums INNER JOIN fdw_enum.app_b_enums USING (name); ASSERT matching_count = 0, 'app_a and app_b-NG have identically named enums. Implement a check that they are identically defined.'; END $DO$; CREATE VIEW fdw_enum.upstream_enums AS SELECT * FROM fdw_enum.app_a_enums UNION ALL SELECT * FROM fdw_enum.app_b_enums; CREATE PROCEDURE fdw_enum.create_type_if_not_exists(name text) LANGUAGE plpgsql AS $PROC$ BEGIN EXECUTE format('CREATE TYPE %s AS ENUM ()', name); EXCEPTION WHEN duplicate_object THEN RAISE NOTICE 'Enum type % already exists, skipping', name; END; $PROC$ ; -- To make this idempotent we create the enums empty then alter them by adding values. -- So instead of `CREATE TYPE foo AS ENUM ('bar', 'baz');` we do -- `CREATE TYPE IF NOT EXISTS foo AS ENUM (); -- `ALTER TYPE foo ADD VALUE IF NOT EXISTS ('bar'); -- `ALTER TYPE foo ADD VALUE IF NOT EXISTS ('baz'); -- This ensure the procedure is re-runnable and can add new values to the enum as they are added upstream. -- Order is ensured by the ORDER BY in the loop query. CREATE PROCEDURE fdw_enum.refresh_upsteram_enums() LANGUAGE plpgsql AS $PROC$ DECLARE rec record; ddl text; BEGIN FOR rec IN SELECT DISTINCT(name) AS name FROM fdw_enum.upstream_enums LOOP CALL fdw_enum.create_type_if_not_exists(rec.name); END LOOP; FOR rec in SELECT * FROM fdw_enum.upstream_enums ORDER BY name, sort_order LOOP ddl := FORMAT('ALTER TYPE %s ADD VALUE IF NOT EXISTS %s', rec.name, quote_literal(rec.label)); EXECUTE ddl; END LOOP; END $PROC$ ; CALL fdw_enum.refresh_upsteram_enums();