PostgreSQL

Installation

• CentOS

  yum install http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm
  yum install postgresql94-server postgresql94-contrib
  service postgresql-9.4 initdb
  chkconfig postgresql-9.4 on
  

Start DB service

/etc/init.d/postgresql-9.4 restart

Switch to superuser (postgres)

An user named postgres will be created automatically after installation, who has been granted all privilege in PostgreSQL.
su -l postgres

Quick Tips

Command under OS

CLI (/usr/pgsql-$ver/bin)

Enter to interactive terminal with specified database

psql -d $DB_NAME
connect to a local postgreSQL server

psql -h ${HOSTNAME or IP} -U $USERNAME -d $DB_NAME
connect to a remote postgreSQL server

• psql - a command for entering PostgreSQL interactive terminal
• psql is located under /usr/pgsql-9.4/bin/psql

Backup and Restore (under OS cli)

• Backup
  • pg_dump $DBNAME -f $DBBACK_FN
• Restore
  • psql $NEW_DBNAME < $DBBACK_FN
    • $NEW_DBNAME must be created first.

Create an super user

createuser --superuser $USER_NAME

Drop a database

dropdb $DB_NAME

Execute a script file from cli

psql -d $DB_NAME -a -f $SCRIPT_FN

Command under PostgreSQL CLI

Alter an user with Superuser privilege

ALTER USER $USER_NAME WITH SUPERUSER;

Alter user password

ALTER ROLE $USER_NAME WITH PASSWORD '$PWD';

Export table to tsv

Server side
This approach runs and outputs entirely on the server.

COPY (
    select *
    from
        tmp_ecgoods
) TO '/tmp/ecgoods.tsv' DELIMITER E'\t'
;

Client
copy table from server to local with empty string for NULL cell.
\copy (select * from scv) to '/tmp/itri/svc.tsv' with NULL E'';

Import table from tsv

COPY $TABLE_NAME FROM '$FILE_PATH' DELIMITER E'\t' CSV;

Listing psql slash commands

\?

List all databases

\l

Connect to a specified database

\c $DB_NAME

Delete all values from a column

UPDATE "$TABLE_NAME" SET "$COLUMN_NAME" = null;

Set query result to a variable

• table of group2categ

  group_id | category_code |        update_time         
  ----------+---------------+----------------------------
  g01      | c_01          | 2016-01-22 16:11:10.512816
  g02      | c_02          | 2016-01-22 16:11:37.392994
  g03      | c_03          | 2016-01-22 16:11:44.838046
  g01      | c_04          | 2016-01-23 17:05:26.653016
  

• set the latest update_time to $latest_dt
  \set latest_dt 'SELECT MAX(update_time::date) FROM group2categ'

• Query with variable condition
  SELECT * from group2categ WHERE update_time > (:latest_dt);

  group_id | category_code |        update_time         
  ----------+---------------+----------------------------
  g01      | c_04          | 2016-01-23 17:05:26.653016
  

Functions and Operators

Cast timestamp to date

select '2016-01-01 12:23:19'::timestamp;
select '2016-01-01 12:23:19'::timestamp::date;

Data Type Formatting

to_char(last_update, 'YYYY-MM-DD HH24:MI:SS') => 2016-01-06 01:10:06

String concatenation

SELECT a::text || ', ' || b::text FROM foo;
SELECT concat_ws('-', a::text, b::text) FROM foo;

Conditional Expressions

select 
    cust_no, 
    case when "SEX"='1' then 'M' 
         when "SEX"='2' then 'F' 
         else 'O' 
    END 
from customer

Aggregate Functions

concatenated all rows of a fields into a string, separated by a space (delimiter).

SELECT 
    "GOODS_CODE", 
    string_agg("CATEGORY_NAME", ' ') 
FROM 
    eccatecode 
GROUP BY 
    "GOODS_CODE"