You can create an enumeration type in SQL Server using a XML Schema.
For example Colors.
create xml schema collection ColorsEnum as '
You have a table like,
CREATE TABLE research(colors)
AS VALUES ('Blue'), ('Orange'), ('Yellow');
You have an enumerated list of colors. So the easy thing here would be to use an ENUM type
CREATE TYPE colors AS ENUM ('Red','Orange','Yellow','Green','Blue');
ALTER TABLE research
ALTER COLUMN colors -- myColorsColumn
I know that you are not asking about database security per se, but you can do what you want using database security. You can even use this in a web app. If you don't want to use database security, then the schemas still apply.
You want column-level security, row-level security, and probably hierarchical role management. Role-Based security is much easier ...
Since you are apparently using SQL Server 2016, I'd like to throw out another 'possible' option - SESSION_CONTEXT.
Leonard Lobel's article, Sharing State in SQL Server 2016 with SESSION_CONTEXT has some very good information about this new functionality in SQL Server 2016.
Summarizing some key points:
If you’ve ever wanted to share session state across ...
As often with such questions, the \set ECHO_HIDDEN on command of psql helps. \dT+ will show the possible values of the enum, if the type in question is an enum. The query behind the output is rather complex, but one can simplify it to fit your needs like
SELECT format_type(t.oid, NULL) AS name,
array_agg(e.enumlabel ORDER BY e.enumsortorder) AS ...
Aren't enums already ordered?
This commit, from 2014, implies that BRIN indexes should work for ENUM types.
That's actually not what that commit says. From the commit-message on the link you provided
This type of operator class we call "Minmax", and we
supply a bunch of them for most data types with B-tree opclasses.
Since the BRIN code is ...
As demonstrated in the related question, JOINS on enums are "by-value". Constraints, on the other hand, is validated "by-position". This behaviour strikes me as rather counter-intuitive. Using a similar setup as Evan:
CREATE TABLE foo ( a ENUM ('A', 'B') not null primary key );
CREATE TABLE bar ( a ENUM ('X', 'Y', 'Z') not null primary key );
ALTER TABLE ...
The SELECT in your example should be consistent with the INSERT with the way it uses quotes. The following works:
select b,b='1',b='0' from t;
MySQL's enum documentation explains that an enum is an index of strings - every entry has both a numeric index and a string value. The SELECT query in your question is using the numeric index, and the ...
What @Jack said. Plus, if all you need is the list of registered values for an enum type, there are some Enum Support Functions to do that. Based on Jack's example:
That's simpler and resilient against (unlikely) changes in future major Postgres versions that might break your query.
In SQL Server, no (though I recall creating constants in Oracle packages back in 1998 and have kinda missed having them in SQL Server).
AND, I just tested and found that you cannot even do this with SQLCLR, at least not in the sense that it would work in all cases. The hold up is the restrictions on Stored Procedure parameters. It seems that you cannot have ...
Is this bad practice?
No, not at all, modifying the catalog is bad practice but there is no reason not to query it.
But in your query…
SELECT distinct pg_type.typname AS enum_type FROM pg_type JOIN pg_enum ON pg_enum.enumtypid = pg_type.oid;
…there isn't any need to join pg_enum and use distinct:
create type mood as enum ('sad', 'ok', 'happy');
Have you considered using the Access Control List PostgreSQL extension?
It contains the native PostgreSQL data type ACE and a set of functions that allow you to check if a user has the permission to access data. It works either with the PostgreSQL roles system or with abstract numbers (or UUIDs) representing your application user/role IDs.
In your case, ...
I think you have a misconception about ENUM because of MySQL breaking the spec and being goofy. They're different in both MySQL and PostgreSQL, but they're more apparently different in PostgreSQL. You're trying to seamlessly query an internal representation detail. I'm actually glad PostgreSQL prevents it.
Instead use the ENUM support functions
CREATE TYPE ...
You can do something like this,
WHERE enumtypid = pg_typeof('You'::foo)::regtype
FETCH FIRST ROW ONLY;
You can create a simple function that does that too,
CREATE FUNCTION is_enum(x regtype)
WHERE enumtypid = x
FETCH FIRST ROW ONLY;
$$ LANGUAGE sql
What are the cons of using ALTER TYPE instead of creating a new table and using JOIN? I really don't want to add a JOIN to my queries just because of a status field.
This is your typical pro/con enum vs normalization question.
Great if the range is fixed or only requires modification by the DBA.
Get a really nice query syntax which optimized ...
This is pretty simple to code using standard SQL if that is what you prefer.
CREATE TABLE dbo.seller
CREATE TABLE dbo.productsold
-- Sample sellers
INSERT INTO dbo.seller VALUES (1, 'Frank');
INSERT INTO dbo.seller VALUES (2, 'Sally');
-- Sample transactions
Generally enums are for static lists, i.e. lists that never change. Departments is a bad choice because these can change at any time. Continents is good because these are highly unlikely to change, but countries would be bad because these do change. Think of things that are constants, such as seasons, months, day of week etc... You want to keep enums small. ...
Enumerations are physically stored in the table has a number which maps to the positional value of the enumeration.
You can modify your column to change the enumeration and shouldn't experience issues with stored data values (it is always better to be safe than sorry - make a backup of the table just in case).
Enumerations come with disadvantages that you ...
Well, I am an advocate of ENUM -- at least in limited use.
I would use it for status with a small, reasonably static, list of possible values. I would start with unknown to catch things that are typos. ALTER TABLE has long been optimized to add a new option on the end of an ENUM list.
I would not use ENUM for continents. If there are standard ...
Two tables can't have the same ENUMs. MySQL assumes all ENUM keys are globally unique..
CREATE TABLE foo ( a ENUM ('A', 'B', 'C'), x int );
CREATE TABLE bar ( a ENUM ('A', 'C', 'D'), y int );
INSERT INTO foo ( a, x ) VALUES ('A',1), ('B',2), ('C',3);
INSERT INTO bar ( a, y ) VALUES ('A',4), ('C',5), ('D',6);
SELECT * FROM foo JOIN bar USING(a);
When is it beneficial to move duplicate data to a reference table?
When the referenced data exists independently from the referencing data. In this example, is it useful in the problem domain to know what the possible life states are even when there are no rows in persons.
When the values can only be drawn from a known, finite set. This is often referred to ...
This answer focuses primarily on the use of the ENUM type as it applies to MySQL, because it's where I have seem them used.
You've answered your own question by way of the thread that you highlighted at the bottom of your own post - it's all explained here i.e. ENUMs are evil :-)!
Data in databases should be stored in tables - data concerning the ...
ENUM datatype can be seen as a "human-readable" numeric value, commonly used for convenience and data storage efficiency.
Let's take your example with a table listing the countries and their respective continents.
CREATE TABLE country_continent (
INSERT INTO country_continent VALUES ('...
Why you're in a hole (example-specific)
The question here is principally how do you differentiate between "not known" and "known to be unknown". If someone had a death_date then,
When IS NOT NULL you could know for certain that they were dead, you would actually know when they died. (Assuming that ever death could be attributed with a date)
When IS NULL ...
MySQL doesn't have an ENUM type. It has something called ENUM(), but it's not an ENUM. It's a translation and a column constraint. Because of this the maintenance on using the pseudo-type goes up. If it's used in two places (ie, tables),
you now have to maintain two places
the effects on JOIN is absurd
the effects on relational integrity and FOREIGN KEY is ...
If the intention here is to provide a fixed alternate table name (rather than record a source, which may vary), one option would be to implement a non persisted virtual column (calculated column in SQL Server).
What I recommend is that you have a fact_source table:
CREATE TABLE fact_source
fact_source_id INTEGER, -- auto-increment.
fact_source_name VARCHAR (50)
Use your own RDBMS's (you didn't say which) own auto incrementing key system to generate the fact_source_id)
INSERT INTO fact_source fact_source_name) VALUES ('Sales'), ('Testing');
and use ...
I can think of another possibility to encode this, the relational one
If you don't need the permission_per_item tableyou can skip it and connect Permissions and Items directly to the item_per_user_permissions table.
CHAR has a nasty side. ANALYSE() predates character sets, and its code was probably not updated to take into account that English text in CHAR(...) utf8mb4 wastes 3/4 of the space! Also, there was some utility of MyISAM row_format=fixed, but such is now useless in the default InnoDB.
Bottom line: ignore its advice about CHAR unless you (1) really have ...