PostgreSQL Developer Best Practices

1. Prefix ALL literals with an Escape
   EG:  SELECT E'This is a \'quoted literal \'';
        SELECT E'This is an unquoted literal';

   Doing so will prevent the annoying "WARNING:  nonstandard use of escape in a string literal"

2. End ALL queries with a semi-colon (;)
   EG: SELECT some_column FROM a_table;
   
   Although autocommit is on by default, it is always a good idea to signal the query processor that a statement is complete with the semicolon. Failure to do so could result in <IDLE IN TRANSACTION>, which will 
hold locks on the tables involved and prevent other queries from being processed.

3. Avoid using "SELECT *" whenever possible. Always specify only the columns needed. Doing so will 
    reduce the query execution time. 
   
4. Format ALL statements so that they are human readable.
   EG: 
   SELECT column1,
          column2
     FROM a_table 
    WHERE column2 = 'some_value';

   UPDATE a_table
      SET column1 = 'value1',
          column2 = 'value2'
   WHERE column3 = 'some_value';
 
   DELETE FROM a_table
    WHERE column1 = 'some_value';

5. Do NOT use CamelCase for object names (schema, table, column, etc.). PostgreSQL will 
    convert all entries to lowercase by default unless quoted. So unless you like having to quote 
    objects, it is best to use all lowercase. Users cannot and should not see database objects, 
    so the names are transparent to them.

6. Although it is legal to use the form "column TYPE PRIMARY KEY," It is best to specify as a CONSTRAINT, 
   that way YOU get to choose the name, otherwise postgres assigns a default name which may not be to your liking.
   EG: , CONSTRAINT accounts_pk PRIMARY KEY (account_id)

7. Use COMMENTs as a way of self documentation.
     EG:
     COMMENT ON TABLE accounts IS ‘Information about customer accounts’;
     COMMENT ON COLUMN accounts.account_id IS ‘Primary key for accounts table.’;
 
8. Do not use "id" as the primary key name in every table. Make it something 
    meaningful.
    EG: For accounts table, use account_id.
        For addresses table, use address_id 

9. Do NOT arbitrarily assign an "id" column to a table as a primary key when other columns
    are perfectly suited as a unique primary key.
    
    EG: Bad example:
        CREATE TABLE accounts
        ( id         bigint NOT NULL DEFAULT nextval('seq_id'::regclass),
          account_id bigint NOT NULL ,
          ....
          ....
          CONSTRAINT accounts_pk PRIMARY KEY (id)
        );
         CREATE UNIQUE INDEX accounts_id_idx ON accounts
           USING BTREE (account_id);
        
        Good example:
        CREATE TABLE accounts
        ( accout_id bigint NOT NULL ,
          ....
          ....
          CONSTRAINT accounts_pk PRIMARY KEY (account_id)
        );

10.  Standardize Index names with the form table_name + col(s) + “idx”
        EG: For accounts table:
                accounts_name_idx
                accounts_city_state_idx