Questions tagged [enum]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
1answer
32 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','...
0
votes
0answers
78 views

ERROR: cannot update table “pg_enum” because it does not have a replica identity and publishes updates

I was trying to update the enum label using the following SQL query UPDATE pg_enum SET enumlabel = 'Cash Recivied' WHERE enumtypid = '1203702' AND enumlabel = 'Cash'; but it throws an error ERROR: ...
4
votes
2answers
99 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
1answer
28 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 ...
5
votes
1answer
156 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
464 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
215 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 ...
1
vote
3answers
799 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
1answer
176 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 ...
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 ...
3
votes
1answer
4k 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 | +------...
1
vote
1answer
236 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
422 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
3answers
181 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: ...
-1
votes
1answer
1k 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 ...
6
votes
1answer
1k 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: ...
3
votes
2answers
633 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
401 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....
9
votes
3answers
930 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 ...
0
votes
0answers
300 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 ...
1
vote
1answer
390 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 ...
1
vote
1answer
5k 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.
3
votes
0answers
803 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
vote
2answers
209 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 ...
16
votes
3answers
6k 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 ...
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
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 ...
1
vote
1answer
169 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, ...
2
votes
2answers
230 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 ...