7
Unique PostgreSQL index for a set of specific values
source link: https://www.codesd.com/item/unique-postgresql-index-for-a-set-of-specific-values.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Unique PostgreSQL index for a set of specific values
advertisements
Let's say there is a table AVATARS and among other columns it has user_id (foreign key to users table) and state where state can be any value from 0 to 6. I want to create a constraint so that user can have only 2 avatars, where one has state = 0 and the other has any state != 0. I wonder if it's possible to implement with a unique index? Or I am gonna have to add some boolean column?
You could use a unique index with an expression:
CREATE UNIQUE INDEX avatars_userid_state_0_idx ON avatars(user_id,(state<>0));
(or state = 0, the end result is the same).
Related Articles
Is it possible to have a unique mysql index for a specific column value?
Sorry for the weird title, couldn't write it better... Take a look at the image above. In mysql, is it possible/feasible to have column UNIQUE based on user_id? Let me rephrase this, is it possible to have order column values all unique only for spec
What is the best performing table index for a set of history records?
I have a simple history table that I am developing a new lookup for. I am wondering what is the best index (if any) to add to this table so that the lookups are as fast as possible. The history table is a simple set of records of actions taken. Each
Oracle: unique index with verification of a specific value
I have a simplified table with two columns (group, key). A key must be unique within a group, but it also have to be unique compared to a 'reference' group. So I created a unique index (group, key) for the first constraint, but is it possible to crea
How to search for a set of specific pattern strings using a regular expression?
I want to search a set of specific string from a word using PHP regular expression. The pattern is like this The string is of 6 character length First 5 characters of this string are number 0-9 Last 1 character is a A-Z or a-z alphabets there are mor
Update the Magento index for one or more specific products
I want to know if there is a way to update a magento index for one or more products. So for example, I have 100 products that I have just programmatically updated the price... and now I need to kick of the indexer so that it reindexes the price for t
How do I get COUNT from a column on a set of specific values from multiple columns?
+------+-------+-------+---------+---------+ | R_ID | BU | DIV | Product | C_Func | +------+-------+-------+---------+---------+ | 1 | BU1 | Div1 | P1 | Dev | | 2 | BU2 | Div2 | P2 | Dev | | 3 | BU2 | Div2 | P2 | BA | | 4 | BU3 | Div3 | P3 | Dev | |
If the input field is blank, set a specific value
I'm designing a shopping cart system for my site. I have an input to enter the quantity for each item, with the value 1 automatically put in. The problem is, if someone clicks in the input and deletes the 1, leaving the field blank, then the item isn
How to query a C # dictionary and return a set of specific values
I have a dictionary in my C# program that contains a list of Key + Values. The values are itemid, Month, Year, Count I would like to query the dictionary by comparing a set of values (itemid, Month, Year) and return true or false if a the specific it
The PostgreSQL query for a list of allowed values in a constraint?
Given a PostgreSQL table named requests with a column named status and a constraint like this: ALTER TABLE requests ADD CONSTRAINT allowed_status_types CHECK (status IN ( 'pending', -- request has not been attempted 'success', -- request succeeded 'f
A Spec Clojure for a set with false values in them
For some reason the below spec does say that false isn't a valid ::a-thing even though it is part of the given set. (require '[clojure.spec.alpha :as spec]) (spec/def ::a-thing #{:a :b :c false}) (spec/valid? ::a-thing :a) ; => true (spec/valid? ::a-
Unable to set a specific value in a list menu
I have a playlist and a player in one script. When click any one of the list it passes a value which i take with GET method($_GET['List_id']) and then play this specific video. But i need a highlighter to highlight the list item which is playing now.
Sumifs, but only once for each set of corresponding values
I am extracting sums from a table, and there is one thing I can't figure out how to do: I need to get the results only once no matter how many times there is a match. For example, I need to sum the total of column C for all rows where A and B both ma
Set a specific value to select an entry on jQuery return false?
I have a Select input droplist: <select class="redblue"> <option value="Red or Blue?" selected>Red or Blue?</option> <option value="RED">RED</option> <option value="BLUE">BLUE<
Do I need one or two exclusive key indexes for this table?
I'm trying to create a Users table that only has OpenId registrations, exactly like StackOverflow. I'm storing in the table OpenId Identifier (their login name) Alias (which is the display name to show to the public) Some other openId stuff So... I w
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK