Questions tagged [enum]
The enum tag has no usage guidance.
42
questions
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 ...
- 153
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 ...
- 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 (...
- 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 ...
- 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 ...
- 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 ...
- 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 - "...
- 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 ║ ...
- 369
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 ('...
- 123
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 ...
- 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 ...
- 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 ...
- 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','...
- 11
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 ...
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 ...
- 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
...
- 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,...
- 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 ...
- 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 ...
- 165
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 ...
- 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)?
...
- 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',
'...
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 ...
- 95
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:
...
- 379
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:
...
- 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....
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 ...
- 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....
- 257
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 |
+------...
- 255
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 ...
- 490
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 ...
- 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 ...
- 123
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')),
...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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, ...
- 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 ...
- 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 ...
- 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 ...
- 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.
- 11