Hot answers tagged

9

You can create an enumeration type in SQL Server using a XML Schema. For example Colors. create xml schema collection ColorsEnum as ' <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="Color"> <xs:simpleType> <xs:restriction base="xs:string"> <xs:enumeration value="...


8

Sample data You have a table like, CREATE TABLE research(colors) AS VALUES ('Blue'), ('Orange'), ('Yellow'); ENUM type 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'); Then ALTER TABLE research ALTER COLUMN colors -- myColorsColumn ...


7

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 ...


6

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 ...


5

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 ...


5

Aren't enums already ordered? No. 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 ...


5

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 ...


5

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; Here's proof. 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 ...


5

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: SELECT enum_range(null::mood); enum_range -------------- {sad,ok,happy} That's simpler and resilient against (unlikely) changes in future major Postgres versions that might break your query. ...


4

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 ...


4

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'); select *...


4

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, ...


4

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 ...


4

You can do something like this, SELECT true FROM pg_enum 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) RETURNS bool AS $$ SELECT true FROM pg_enum WHERE enumtypid = x FETCH FIRST ROW ONLY; $$ LANGUAGE sql IMMUTABLE; SELECT is_enum(...


3

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. ENUM type Great if the range is fixed or only requires modification by the DBA. Get a really nice query syntax which optimized ...


3

This is pretty simple to code using standard SQL if that is what you prefer. CREATE TABLE dbo.seller (SellerID INT, SellerName NVARCHAR(100)); CREATE TABLE dbo.productsold (SellerID INT, SaleDate DATE, ProductEnum TINYINT); -- Sample sellers INSERT INTO dbo.seller VALUES (1, 'Frank'); INSERT INTO dbo.seller VALUES (2, 'Sally'); -- Sample transactions ...


3

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. ...


2

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 ...


2

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 ...


2

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); +------+----...


2

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 ...


2

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 ...


2

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. Using VARCHAR CREATE TABLE country_continent ( country VARCHAR(100), continent VARCHAR(100) ); INSERT INTO country_continent VALUES ('...


1

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 ...


1

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 ...


1

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).


1

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 ...


1

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. legend diagram


1

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 ...


1

I think you want this: ALTER TABLE TRUCK ADD (ttype VARCHAR2(6) CONSTRAINT con_type CHECK (ttype in('SMALL','MEDIUM','LARGE')) );


Only top voted, non community-wiki answers of a minimum length are eligible