Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [enum]

The tag has no usage guidance.

5
votes
1answer
48 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 ...
3
votes
0answers
38 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,...
0
votes
0answers
101 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 ...
2
votes
1answer
1k 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 ...
1
vote
1answer
129 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)? ...
6
votes
2answers
268 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
1answer
83 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 ...
1
vote
3answers
165 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: ...
5
votes
1answer
493 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: ...
2
votes
2answers
307 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....
1
vote
3answers
549 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 ...
1
vote
3answers
295 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....
2
votes
1answer
2k 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 | +------...
0
votes
0answers
197 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 ...
9
votes
3answers
535 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 ...
1
vote
1answer
255 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 ...
3
votes
0answers
573 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')), ...
-1
votes
1answer
966 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 ...
1
vote
2answers
162 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 ...
0
votes
0answers
21 views

Query Enum result replacement

I'm building a View for a database that contains test results, and I have an enumerate for the Result of each tests. Querying for the enum gives-me this enum SELECT COLUMN_TYPE FROM ...
0
votes
2answers
4k 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 ...
1
vote
1answer
1k 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 ...
1
vote
1answer
168 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, ...
15
votes
3answers
5k 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 talking ...
2
votes
2answers
227 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 ...
0
votes
0answers
1k 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 ...
1
vote
1answer
4k 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.