anonymous / postgres.sql
Created

Embed URL

HTTPS clone URL

SSH clone URL

You can clone with HTTPS or SSH.

Download Gist

indexes and unique constraints

View postgres.sql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
Create the table with the unique constraint
 
testdb=# CREATE TABLE "main_a_b" (
"a_id" integer,
"b_id" integer,
UNIQUE ("a_id", "b_id")
);
CREATE TABLE
 
Conditional query on first column
 
testdb=# EXPLAIN Select a_id from main_a_b where a_id = 1;
QUERY PLAN
--------------------------------------------------------------------------------------
Bitmap Heap Scan on main_a_b (cost=4.24..14.91 rows=11 width=4)
Recheck Cond: (a_id = 1)
-> Bitmap Index Scan on main_a_b_a_id_b_id_key (cost=0.00..4.24 rows=11 width=0)
Index Cond: (a_id = 1)
(4 rows)
 
Create an explicit index, try again
 
testdb=# CREATE INDEX test_indx ON main_a_b (a_id);
CREATE INDEX
 
testdb=# EXPLAIN Select a_id from main_a_b where a_id = 1;
QUERY PLAN
-------------------------------------------------------------------------
Bitmap Heap Scan on main_a_b (cost=4.24..14.91 rows=11 width=4)
Recheck Cond: (a_id = 1)
-> Bitmap Index Scan on test_indx (cost=0.00..4.24 rows=11 width=0)
Index Cond: (a_id = 1)
(4 rows)
 
Identical plans, identical costs.
View postgres.sql

See SQLite create table documentation:

A UNIQUE constraint is similar to a PRIMARY KEY constraint, except that a single table may have any number of UNIQUE constraints. For each UNIQUE constraint on the table, each row must contain a unique combination of values in the columns identified by the UNIQUE constraint. For the purposes of UNIQUE constraints, NULL values are considered distinct from all other values, including other NULLs.

In most cases, UNIQUE and PRIMARY KEY constraints are implemented by creating a unique index in the database. (The exceptions are INTEGER PRIMARY KEY and PRIMARY KEYs on WITHOUT ROWID tables.) Hence, the following schemas are logically equivalent:

  1. CREATE TABLE t1(a, b UNIQUE);
  2. CREATE TABLE t1(a, b PRIMARY KEY);
  3. CREATE TABLE t1(a, b); CREATE UNIQUE INDEX t1b ON t1(b);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.