Having the following schema: I am trying to a unique contraint to the column. But this runs into an error: I can understand that the column is already indexed because I have created the index by myself. But I want the column to be unique. Is there a way to do this? Answer This is indeed an undocumented limitation in
Tag: unique
Store Pricing Combinations and Sums w/ SQL
I have a table that looks something like and I would like to create all possible combinations with the sum of the prices, including the unique values to create something like what would be the quick and dirtiest way of accomplishing this? Answer Use three joins:
Counting identical records in a SQL table with respect to a column
Problem Description I am looking for a SQL query that can create table 2 using table 1. I’ve thought of way to do this but it’s not very eloquent and would prefer something simpler if possible. TABLE 1: ID Col_1 Col_2 Col_3 Col_4 01 A Q R U 02 A Q R P 03 A Q R U 04 A
Generating never used before random number in SQL
I am using the function below to generate a random number between 0 and 99999999999. However, I need to make sure that the returned number has never been used before in the same app. In .net I would create a while loop and keep looping until the returned value is not found in a table that stores previously used values.
non UNIQUE constraints
So, I got in this situation: I create a table (let say model_a), in this table I have 2 cols (let say col1 and col2). I need a constraint that satisfy this reality: It’s not UNIQUE constraint, you can duplicate the first row. The only one time this constraint active is col1 is the same but col2 is different. Need
SQL query with having function to only includes parts of another column?
I am trying to find fact_order_id’s that have beverage only items. This would make the fact_order_id not include any food items. I have used 2 separate SQL Queries below. When I go back and check my work, I notice some fact_order_id’s in the output aren’t beverage only orders. Here are the 2 separate SQL Queries that give me the same
How to enforce that there is only one `true` value in a column per names (in an enum) of another column?
I have the following structure with an enum { ‘ready’, ‘set’, ‘go’} on name: How can I put a constraint on it so that there will only ever be 3 true’s (one on ready, one on set, and one on go)? Answer You can use filtered unique indexes (or as Postgres calls them “partial indexes”):
PostgreSQL Partial Indexes and UPSERT
After googling a lot my question is described below: Attempting to insert values: Results in: Altho this works(per spec): PostgreSQL documentation stands that it should work PostgreSQL v9.5 My aim is to find way to create unique index on this table on multiple nullable columns and update old rows with new ones on UPSERT Answer The conflict_target used in on
Can I use VARCHAR as the PRIMARY KEY?
I have a table for storing coupons/discounts, and I want to use the coupon_code column as the primary key, which is a VARCHAR. My rationale is that, each coupon will have a unique code, and the only commands I will be running are SELECT … FROM … WHERE coupon_code=’..’ I won’t be doing any joins or indexing, and I don’t
Get number of values that only appear once in a column
Firstly, if it is relevant, I’m using MySQL, though I assume a solution would work across DB products. My problem is thus: I have a simple table with a single column. There are no constraints on the column. Within this column there is some simple data, e.g. I need to get the number/count of values that only appear once. From