jensraaby/postgres-til.md

## postgres-til.md

      
    Raw
  

              postgres-til.md
            
          
    Indexes

https://www.postgresql.org/docs/9.1/sql-createindex.html
Concurrently

Indexing locks up the table for writes/deletes but allows reads. You can use CONCURRENTLY to prevent the locking
Replacing primary keys

Creating a new primary key involves a unique index; dropping the old primary key constraint; then adding the index as a new primary key constraint.
CREATE UNIQUE INDEX idxname ON tablename(col1,col2);

ALTER TABLE tablename DROP CONSTRAINT tablename_pkey;
ALTER TABLE tablename ADD CONSTRAINT tablename_pkey PRIMARY KEY USING INDEX idxname; 
Listing all constraints

https://dba.stackexchange.com/questions/214863/how-to-list-all-constraints-of-a-table-in-postgresql/243625
Creating composite primary keys

Order the fields so that the most restrictive filter first (i.e. the field that varies the most)
(mpxn,msn,date) would result in (date,msn,mpxn)