Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [enum]

41 questions
19
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
  • 465
  • 1
  • 4
  • 10
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
  • 673
  • 2
  • 9
  • 22
8
votes
1 answer

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
  • 275
  • 2
  • 12
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
  • 3
6
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
  • 255
  • 1
  • 5
  • 11
5
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
  • 53
  • 1
  • 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,448
  • 2
  • 30
  • 56
4
votes
0 answers

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
3
votes
2 answers

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 correct in this thinking, and if not then what this…
Mark
  • 31
  • 1
3
votes
2 answers

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 uses a generic "join table". This join table…
Jason
  • 35
  • 5
3
votes
1 answer

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 validating integrity based solely on the global ENUM…
NO WAR WITH RUSSIA
  • 54,954
  • 34
  • 200
  • 411
3
votes
2 answers

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.enumlabel AS enmu_label FROM pg_type JOIN pg_enum ON…
Alexander Kleinhans
  • 341
  • 2
  • 5
  • 14
3
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
  • 153
  • 8
3
votes
0 answers

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')), UNIQUE KEY (issue_id, issue_type) ); CREATE TABLE…
Vasek
  • 31
  • 2
1
2 3