Questions tagged [enum]
52 questions
20
votes
3 answers
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 talking about database access permissions here, but…
JohnCand
- 525
- 1
- 5
- 10
12
votes
2 answers
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 using MySQL8.0, on databases that are not that big,…
Florent Poujol
- 223
- 2
- 4
10
votes
2 answers
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:
SELECT color, count(*)
FROM research
GROUP BY color…
Amanda
- 305
- 1
- 3
- 13
10
votes
3 answers
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 procedures, table-valued functions, and used in…
Edmund
- 733
- 3
- 10
- 23
8
votes
1 answer
Order by certain enum values first
I've got a table like:
╔═══╦════════════════════╦═════════════╗
║ID ║ type (enum) ║ updated_at ║
╠═══╬════════════════════╬═════════════╣
║ 1 ║ friend_request ║ ║
║ 2 ║ new_article ║ ║
║ 3 ║ article_read …
Milkncookiez
- 479
- 2
- 6
- 16
7
votes
2 answers
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 appropriate time to use ENUM. A couple of the examples…
Alexis Morales
- 73
- 1
- 1
- 4
7
votes
1 answer
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 |
+------+
| b |
| c |
+------+
With the following…
user3112092
- 275
- 2
- 5
- 11
6
votes
1 answer
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 constraint I put on the field completed_at.
`DO $$…
Pierre
- 163
- 1
- 4
6
votes
2 answers
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',
'Where Everybody Knows Your Name'
);
With a table…
Dominique Pennington
- 63
- 4
5
votes
1 answer
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
);
CREATE INDEX test_index ON my_table using…
karldw
- 153
- 3
5
votes
2 answers
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 might look something like this:
...
WHERE…
db2
- 9,708
- 4
- 37
- 58
5
votes
3 answers
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 status_id int and name varchar, and in orders table…
Victor
- 173
- 1
- 9
4
votes
2 answers
How is enum stored in MariaDB
I have created a database where one of the fields of a table is defined as enum('M', 'B'). The tool that I am using, converts it to a variant type. I would like to override it and treat it like a character. So the question is, is it stored as a…
Rohit Gupta
- 2,116
- 8
- 19
- 25
4
votes
1 answer
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 ('a'::MY_ENUM), ('b'::MY_ENUM);
These work just…
Eugene Pakhomov
- 143
- 1
- 5
4
votes
1 answer
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,4) DEFAULT 0.0,
transaction_currency currency NOT…
Nimit
- 41
- 1
- 6