Define enum as the column names of table PostgreSQL

Is there a way to define an enum using a table’s column names?

I have a users table defined as follows:

create table public.users (
  "id" serial not null unique primary key,
  "name" text not null check(length(name) > 1),
  "photo" text,
  "phone" text not null unique,
  "email" text not null unique,
  "bio" text,
  "socials" social[] not null
);

And then I want to define a required_profile_fields column in another table to be an enum containing only the column names in that public.users table:

create type profile_fields as enum('name', 'photo', 'email', 'phone');
create table public.orgs (
  "required_profile_fields" profile_fields[] not null check(array_length(required_profile_fields) > 0)
);

Is there a way that I can define that profile_fields as an enum from the column names in public.users?

Answer

You can try something like this:

DO
$$
BEGIN
EXECUTE (
  SELECT
    format('CREATE TYPE profile_fields AS ENUM (%s)', 
            string_agg( DISTINCT quote_literal(column_name), ', ') )
  FROM
    INFORMATION_SCHEMA.COLUMNS
  WHERE
    TABLE_NAME = 'users'
);
END 
$$