Questions tagged [enum]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
5 votes
2 answers
97 views

Are enums still evil (in MySQL 8.0)?

So I have a disagreement with a coworker about using enums vs lookup tables. This very old article from 2011 and MyQL5.5 still gets referenced again and again by those not liking enums. But we are ...
user avatar
0 votes
1 answer
103 views

iterate through Postgres enum values?

I have a Postgres enumerated type used to describe the node class (order sensitive) of a source/destination relationship table. Question is, how can I dynamically fetch the next value (if any) of an ...
user avatar
  • 113
0 votes
1 answer
46 views

Does the collation of ENUM columns impact their size?

What does it mean when we say that an ENUM has a charset of utf8mb4, versus latin1? My understanding is that the ENUM holds a numeric index (1, 2, 3, 4...) that identifies which of the ENUM values (...
user avatar
  • 609
0 votes
3 answers
270 views

Mysql: Any way to properly index 3 ENUM columns with the same options ? (A OR B OR C)

I have 3 enum() columns with the same option values inside. I first tried to use the "set" datatype which originally was meant to do that (hold multiple values from a set) but it seems that ...
user avatar
  • 362
1 vote
1 answer
2k views

Postgres: Unsafe use of new value of enum type

I want to alter a constraint after I have added a new value to an enum I use. So I originally have an enum called activity_state where I want to add a new value. But I also need to change the ...
user avatar
  • 111
0 votes
1 answer
83 views

Using PostgreSQL to hold an array of data

I am trying to use a postgreSQL data base column to store values from an array (or point to an enum table which does this). I will get an array of data like ['one','two','five']. The array can ...
user avatar
  • 115
0 votes
1 answer
281 views

"ENUM" is not vallid at his position, expecting ')' [closed]

I'm keeping this error and I'm very new to mySQL so I don't know what is going on. The only way I found to remove this error message is to put ')' before the ENUM and then the message change - "...
user avatar
  • 1
1 vote
1 answer
2k views

Order by certain enum values first

I've got a table like: ╔═══╦════════════════════╦═════════════╗ ║ID ║ type (enum) ║ updated_at ║ ╠═══╬════════════════════╬═════════════╣ ║ 1 ║ friend_request ║ <date_time> ║ ║ 2 ║ ...
user avatar
2 votes
1 answer
1k views

PostgreSQL ignores implicit casts in some situations

The setup (tried only on PostgreSQL 9.6): CREATE TYPE MY_ENUM AS ENUM ('a', 'b'); CREATE CAST (CHARACTER VARYING AS MY_ENUM) WITH INOUT AS IMPLICIT; CREATE TABLE t (x MY_ENUM); INSERT INTO t VALUES ('...
user avatar
1 vote
2 answers
118 views

Unique constraint on 3 different columns but a specific value in 3rd column allows duplicate row entries

I have a 'Users' table with columns user_email, user_company_id and user_status. The user_status column is an enum with values '1' or '0' which represents the users being either active or inactive. Is ...
user avatar
  • 13
3 votes
2 answers
3k views

ENUM and ID as a Foreign Key

Using PostgreSQL 11. I've tried digging through both Stack Overflow and here and was unable to find an answer on best practices. I'm working on a database design and have arrived at a schema that ...
user avatar
  • 35
0 votes
1 answer
198 views

PDO returning 0 rows on WHERE with enum

I am trying to build a query for a report. The report will have a few fields that can be filtered, one of which is a jobStatus field, it is an enum. Here is my query SELECT * FROM jobs j LEFT JOIN ...
user avatar
  • 109
1 vote
1 answer
125 views

ENUM column auto set

I have two tables "using" foreign keys: Referencing table CREATE TABLE `requests` ( `request_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, -- `status` enum('new','...
user avatar
5 votes
2 answers
7k views

What would you use ENUM for in SQL?

I am trying to complete an assignment for my CIS class and I am required to include a data type called ENUM. I know that this stands for enumerated lists, but I am not sure when would be an ...
user avatar
1 vote
1 answer
52 views

Constrain input values

I am working on limiting input in certain fields in my Postgres database, and I have a working solution using ENUM like so: CREATE TYPE label AS ENUM ('foo','bar'); However, I was browsing around ...
user avatar
  • 13
5 votes
1 answer
393 views

Do BRIN indexes support ENUM types?

BRIN indexes seem useful, but I'm not sure how to use one on an ENUM type. I thought this code would work: CREATE TYPE test_enum AS ENUM ('a', 'b'); CREATE TEMPORARY TABLE my_table ( x test_enum ...
user avatar
  • 153
4 votes
0 answers
3k views

PostgreSQL Enum : Search on ENUM type

Defined an ENUM CREATE TYPE currency AS ENUM('GBP', 'EUR', 'USD'); Using this as a type in a table CREATE TABLE if not exists transaction( id BIGSERIAL NOT NULL PRIMARY KEY , amount NUMERIC(35,...
user avatar
  • 41
5 votes
2 answers
2k views

Elegantly handling 'enum-like' magic numbers in T-SQL code

We run Dynamics GP, and anybody that's dealt with GP will be familiar with all of its "magic number" columns representing the various enum values inside the application. For example, a reporting query ...
user avatar
  • 9,478
0 votes
0 answers
691 views

Is there any performance benefit of using ENUM instead of TinyInt in big data for MySQL?

I read a lot of articles about ENUM. but most of them are comparing ENUM to joins not tinyint. I need fields with 2-5 different statuses. In this situation is there any performance or storage benefit ...
user avatar
3 votes
1 answer
2k views

How does MariaDB handle ENUM types used in FOREIGN KEY constraints?

Off the heels of this question, How does MySQL handle joins on ENUMs? How does MySQL work if the referencing table has more and less values in the ENUM than the table being referenced? Is it ...
user avatar
  • 56.2k
1 vote
1 answer
648 views

How does MariaDB handle joins on ENUMs?

I'm just wondering because MySQL ENUMs are essentially tied to the table, how do they handle JOINs if two tables have the same ENUM? Are they join based on the ENUM value's or the string-key (input)? ...
user avatar
  • 56.2k
6 votes
2 answers
826 views

Determine if user-defined type is ENUM

Is there a way to determine whether or not a user-defined type in PostgreSQL is an ENUM? Essentially we have the following: CREATE TYPE foo AS ENUM ( 'Sometimes', 'You', 'Wanna', 'Go', '...
user avatar
1 vote
1 answer
217 views

Status fields: ALTER TYPE vs. INSERT INTO?

I have a simple table with a status column: create type status as enum ( 'approved', 'pending_approval', 'rejected' ); create table document ( id serial primary key, uuid ...
user avatar
1 vote
3 answers
366 views

When is it beneficial to move duplicate data to a reference table?

If there is column with values from some finite set S, for example S = { alive, dead, unknown } for a life_state column in a persons table, when is it beneficial to add a reference table? Options: ...
user avatar
8 votes
1 answer
5k views

ORDER BY gives ERROR: function array_position(text[], character varying) does not exist

I have a pretty basic categorical column in a Postgres database that is currently stored as VARCHAR. I can select a count of each with: I though adding an ORDER BY array_position() would do it: ...
user avatar
  • 275
3 votes
2 answers
1k views

Is it a bad practice to query pg_type for enums on a regular basis?

In Postgres, I'm thinking of query pg_type for an up-to-date list of enumerations I'm using on a regular basis. I'd be using something like this: SELECT pg_type.typname AS enum_type, pg_enum....
user avatar
4 votes
3 answers
2k views

One-to-many dictionary (lookup) table vs varchar vs enum?

Imagine we have orders table, and an order has a status. Which of these three options is the best? Use varchar for status column Use enum for status column Use separate status table, which has ...
user avatar
  • 163
1 vote
3 answers
780 views

Should a Fact table contain a column with a single unique value?

In order to simplify reporting, I am wondering if for each of the Fact tables we have, whether I can include a column that states the name of the Fact table, albeit in a more user friendly format e.g....
user avatar
6 votes
1 answer
11k views

How do I query PostgreSQL enums like in MySQL?

With the following table in MySQL: CREATE TABLE bob(foo ENUM('a','b','c')); INSERT INTO bob (foo) VALUES ('a'),('b'),('c'),('a'),('a'); SELECT * FROM bob WHERE foo >= 2; +------+ | foo | +------...
user avatar
0 votes
0 answers
702 views

Mysql: enum part of unique Index

Is it possible to have an unique index on multiple columns, of which a column with type enum is part of? Let's say I have 3 columns, which are an unique index, combined: date type companyId date ...
user avatar
10 votes
3 answers
2k views

Create database level constants (enumerations) without using CLR?

I have a several SQL objects that need to take alternate actions based on a desired state of the request. Is there a way to create database level constants (enumerations) that can be passed to stored ...
user avatar
  • 683
1 vote
1 answer
1k views

Can I alter ENUM value from lowercase to studlyCase without reindex?

I have a several big tables, and enum in each of them (example): ENUM ('webmasters', 'stackexchange', 'stackoverflow') Documenation says, that return value is in lettercase of ENUM definition, so I ...
user avatar
3 votes
0 answers
2k views

Supertype table with disjoint subtypes in MySQL

I want to have base parent table with few disjoint subtypes. To illustrate what I mean: CREATE TABLE issues ( issue_id SERIAL PRIMARY_KEY, issue_type CHAR(1) CHECK (issue_type IN ('B', 'F')), ...
user avatar
  • 31
-1 votes
1 answer
2k views

Using value of a column in another table for ENUM/SET values

I have two tables tbl_events with a column location_id. Then I have a table specifically to hold locations and their IDs,the columns in table tbl_locations are location_id and name. I want the ...
user avatar
  • 305
1 vote
2 answers
238 views

PROCEDURE ANALYSE suggests to turn timestamp into CHAR(19)

Using Procedure Analyse() under MySQL 5.6.30 on a table with about 4 million rows makes the following recommendations: change timestamp to char(19) tinyint to ENUM (I noticed it's overzealous with ...
user avatar
  • 413
1 vote
2 answers
6k views

Add ENum Attribute to existing table using Alter

I am using oracle XE and trying to add a attribute that restricts to only three available value 'Small','Medium','large' to an existing table using Alter Table and Enum. Tried doing, ALTER TABLE ...
user avatar
  • 13
1 vote
1 answer
2k views

How to Sum Each Enum Value in a Different Column and Don't Repeat Person's Name?

Let's say I have a table with SellerId, DateTime, ProductType to register each time a a Seller sells a product. The ProductType is from ENUM type with { 0=Phone, 1=Recharge, 2=SimCard }. Then I have a ...
user avatar
  • 157
1 vote
1 answer
181 views

MySQL Enums have non-intuitive behaviour, why?

This very helpful article explains some oddities about how MySQL Enums behave: http://melp.nl/2009/05/mysql-enums-and-booleans/ There is no discussiong about why that behaviour exists. Furthermore, ...
user avatar
  • 1,048
19 votes
3 answers
8k views

How to implement business logic permissions in PostgreSQL (or SQL in general)?

Let's assume I have a table of items: CREATE TABLE items ( item serial PRIMARY KEY, ... ); Now, I want to introduce the concept of "permissions" for each item (please note, I'm not ...
user avatar
  • 475
3 votes
2 answers
245 views

Is this an example of an Enum (Enumerated Type)

I'm looking at a code base that I have inherited. I'm new to Postgres (but have an Oracle background), and I think this in one of the queries is an example of an enum. Can someone confirm if I'm ...
user avatar
  • 31
0 votes
0 answers
2k views

Using an enum as part of an index definition

When I use an ENUM column in a table the ORDER BY uses the ordinal of the ENUM not its value. I wasn't aware of that. So my question is what are the subtle issues when an ENUM is part of an index ...
user avatar
  • 953
1 vote
1 answer
6k views

How to change a column type from SET to an ENUM

I have a table with a SET column type that need to be changed to ENUM type. Can I do it with a simple ALTER or will this mess up my data? NB: there are no multiple values (a,b,c) in the table.
user avatar