postgreSql - newgeekorder/TechWiki GitHub Wiki

Installation

sudo ./postgresql-9.4.1-3-linux-x64.bin

add to $Path

/opt/PostgreSQL/9.4/bin

Postgresql commands typically need to run as postgress

sudo su postgres

Postgresql Commands

  • pg_ctl - postgresql control

Starting/Stopping the Server. To stop the server, use:

$ pg_ctl stop

where $PGDATA is expected to be the location of the data folder or

Creating a Database

Again as postgres user

$ plsql

will take you to the shell and

CREATE DATABASE testdata;

(!) Note: it seems only lowercase database names are supported

Connect to the created database

\connect testdata

to quit the terminal enter

\q

Creating a Table

CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);

Useful Admin psql commands

sudo su postgres
psql

to start the psql console The ? lists a number of admin commands like \du . that lists the users and roles.

  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
  \db[+]  [PATTERN]      list tablespaces
  \dc[S+] [PATTERN]      list conversions
  \dC[+]  [PATTERN]      list casts
  \dd[S]  [PATTERN]      show object descriptions not displayed elsewhere
  \ddp    [PATTERN]      list default privileges
  \dD[S+] [PATTERN]      list domains
  \det[+] [PATTERN]      list foreign tables
  \des[+] [PATTERN]      list foreign servers
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
  \df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions
  \dF[+]  [PATTERN]      list text search configurations
  \dFd[+] [PATTERN]      list text search dictionaries
  \dFp[+] [PATTERN]      list text search parsers
  \dFt[+] [PATTERN]      list text search templates
  \dg[+]  [PATTERN]      list roles
  \di[S+] [PATTERN]      list indexes
  \dl                    list large objects, same as \lo_list
  \dL[S+] [PATTERN]      list procedural languages
  \dm[S+] [PATTERN]      list materialized views
  \dn[S+] [PATTERN]      list schemas
  \do[S]  [PATTERN]      list operators
  \dO[S+] [PATTERN]      list collations
  \dp     [PATTERN]      list table, view, and sequence access privileges
  \drds [PATRN1 [PATRN2]] list per-database role settings
  \ds[S+] [PATTERN]      list sequences
  \dt[S+] [PATTERN]      list tables
  \dT[S+] [PATTERN]      list data types
  \du[+]  [PATTERN]      list roles
  \dv[S+] [PATTERN]      list views
  \dE[S+] [PATTERN]      list foreign tables
  \dx[+]  [PATTERN]      list extensions
  \dy     [PATTERN]      list event triggers
  \l[+]   [PATTERN]      list databases
  \sf[+] FUNCNAME        show a function's definition
  \z      [PATTERN]      same as \dp

Postgres DataTypes

Name Aliases Description
bigint int8 signed eight-byte integer
bigserial serial8 autoincrementing eight-byte integer
bit [ (n) ]   fixed-length bit string
bit varying [ (n) ] varbit variable-length bit string
boolean bool logical Boolean (true/false)
box   rectangular box on a plane
bytea   binary data ("byte array")
character [ (n) ] char [ (n) ] fixed-length character string
character varying [ (n) ] varchar [ (n) ] variable-length character string
cidr   IPv4 or IPv6 network address
circle   circle on a plane
date   calendar date (year, month, day)
double precision float8 double precision floating-point number (8 bytes)
inet   IPv4 or IPv6 host address
integer int, int4 signed four-byte integer
interval [ fields ] [ (p) ]   time span
json   textual JSON data
jsonb   binary JSON data, decomposed
line   infinite line on a plane
lseg   line segment on a plane
macaddr   MAC (Media Access Control) address
money   currency amount
numeric [ (p, s) ] decimal [ (p, s) ] exact numeric of selectable precision
path   geometric path on a plane
pg_lsn   PostgreSQL Log Sequence Number
point   geometric point on a plane
polygon   closed geometric path on a plane
real float4 single precision floating-point number (4 bytes)
smallint int2 signed two-byte integer
smallserial serial2 autoincrementing two-byte integer
serial serial4 autoincrementing four-byte integer
text   variable-length character string
time [ (p) ] [ without time zone ]   time of day (no time zone)
time [ (p) ] with time zone timetz time of day, including time zone
timestamp [ (p) ] [ without time zone ]   date and time (no time zone)
timestamp [ (p) ] with time zone timestamptz date and time, including time zone
tsquery   text search query
tsvector   text search document
txid_snapshot   user-level transaction ID snapshot
uuid   universally unique identifier
xml   XML data

##Trees One of the more useful and unique features of Postgres is the supporting of tree structures. In order to use the ltree extension it needs to be enabled by super user:

CREATE EXTENSION ltree;

Then trees can be created

CREATE TABLE test (path ltree);
INSERT INTO test VALUES ('All');
INSERT INTO test VALUES ('All.All_TV');
INSERT INTO test VALUES ('All.All_TV.Linear');
INSERT INTO test VALUES ('All.All_TV.Linear.Pay');
INSERT INTO test VALUES ('All.All_TV.On_Demand.Subscription.Pull.Movies_SVOD_Window');
CREATE INDEX path_gist_idx ON test USING gist(path);
CREATE INDEX path_idx ON test USING btree(path);

and queried

SELECT path FROM test WHERE path <@ 'All.All_TV';

Special Tree Query Language

  • ltree stores a label path

  • lquery represents a regular-expression-like pattern for matching ltree values

| foo | Match the exact label path foo | | .foo. | Match any label path containing the label foo | | *.foo | Match any label path whose last label is foo |

Star symbols can also be quantified to restrict how many labels they can match:

*{n} Match exactly n labels *{n,} Match at least n labels *{n,m} Match at least n but not more than m labels *{,m} Match at most m labels — same as *{0,m} There are several modifiers that can be put at the end of a non-star label in lquery to make it match more than just the exact match:

@ Match case-insensitively, for example a@ matches A

  •       Match any label with this prefix, for example foo* matches foobar

% Match initial underscore-separated words

Key Value Store - hstore

This module implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value.

This can be useful in various scenarios, such as rows with many attributes that are rarely examined, or semi-structured data. Keys and values are simply text strings.

http://www.postgresql.org/docs/current/static/hstore.html

Full Text Search

ndexing for full-text search

PostgreSQL includes a capable full-text search (FTS) package, available as a core feature starting in 8.3. Both GIN and GiST indexes can be used to accelerate text searches done that way. The basic idea is that GIN is better suited for relatively static data, while GiST performs better with frequently updated, dynamic text.

Index Types

Postgres allows the support of different indexes that have various performance/flexibility options depending on the type of data being stored

Unique indices are enforced by the Primary Key word :

CREATE TABLE t(k serial PRIMARY KEY,v integer);

CREATE TABLE t(k serial,v integer);
ALTER TABLE t ADD CONSTRAINT k_key UNIQUE (k);
  • B-Tree - Balenced Tree (both left and right) makes it fast to find both text and numeric values using > or < including text searches that begin with specific values. More complex text searches may wish to use a different index.
  • Hash - is a key value lookup optimal when you are doing equality (not range) lookups.
  • GiN - Generalized Inverted Index (GIN) .for matching single or multiple keys in a row
  • GiST - A Generalized Search Tree (GiST) provide a way to build a balanced tree structure for storing data. GiST can also be used for full-text search, and it too has a very rich library of contrib modules that use its features.

Links and References