I am using enum in Postgresql. I have used a enum called city in my database. My requirement is how to get a enum by using conditional statement. I used the query
SELECT 'Bengaluru'::CITY as TEXT
it works fine but if the text is not found in enum then it throws a error. How should I make/change the sql query so that it doesn't throw the error.
You need to check the system tables for that. Assuming an enum defined as:
create type city as enum ('Munich', 'Berlin');
To find out if a specific label was defined for an enum type, you can use the following:
select exists (select *
from pg_enum e
join pg_type t on t.oid = e.enumtypid
join pg_namespace s on s.oid = t.typnamespace
where s.nspname = 'public' --<< adjust here for the correct schema
and t.typname = 'city'
and e.enumlabel = 'Bengaluru');
The correct solution is to not use enums, but a proper one-to-many relationship instead.
create table city
(
id integer primary key,
name varchar(100) not null unique --<< the "unique" mimics the enuma
);
insert into city (id, name)
values (1, 'Munich'), (2, 'Berlin');
To use the "enum", use a foreign key:
create table some_table_using_city
(
... other columns ...
city_id integer references city
);
To check if a city name exists, simply use:
select id
from city
where name = 'Bengaluru';
SELECT
CASE WHEN 'Bengaluru'::CITY !=NULL THEN 'Bengaluru'::CITY ELSE "Not Found"
END as TEXT
CASE can be used here.
For more details regarding syntax you can find here.
citytable e.g.create table city (id integer primary key, name text not null). Then use a foreign key referencing the city table instead.