Define enum as the column names of table PostgreSQL

Question

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?

romainsalles · Accepted Answer · 2021-06-04 08:37:28Z

3

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 
$$

answered Jun 4, 2021 at 8:37

romainsalles

2,14314 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nicholas Chiang Over a year ago

Is there a way to ensure that this only accesses the columns names from public.users (i.e. only tables in the public schema)? I'm using Supabase which defines an auth.users table that I don't want to include in this enum.

Marko Ivkovic · Accepted Answer · 2021-06-06 04:54:47Z

0

Try this one

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 
$$

edited Jun 6, 2021 at 4:54

Marko Ivkovic

1,2981 gold badge11 silver badges14 bronze badges

answered Jun 4, 2021 at 8:52

Kanyesigye Raphael

11 bronze badge

Collectives™ on Stack Overflow

Define enum as the column names of table PostgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related