Index

Postgres

installing postgres
sudo apt-get install postgresql

Configuring Postgres

changing the maximum allowed connections
sudo vi /etc/postgresql/9.3/main/postgresql.conf
max_connections = 500
ip whitelisting
sudo vi /etc/postgresql/9.3/main/pg_hba.conf
host    all             all             10.10.10.10/32        md5

Postgres in terminal

becoming the postgres user
sudo su postgres
postgres terminal:
psql
postgres terminal connected to database [dbname]
psql [dbname]
connecting to a database on login:
psql -h localhost -p 5432 -U [user] [database]
connect to remote database
psql -h [url] -p [port] [database] [username]
running a command on a database from the command line
psql -d [database] -c "DROP SCHEMA public CASCADE;"
create users/ databases:
createuser -P -e [user]
createdb -O [user] [dbname]

Database Dumps

reading an sql file
psql -d [database] -a -f [file]
creating a database text dump file
pg_dump [dbname] > outfile
reading a database text dump file
psql [dbname] < infile
pg_dump flags
-a data only
-f output file
-n schema
-t table
-v verbose
-- inserts (sql insert statements)

-h host
-p port
-U username
reading a database dump
sudo -u postgres pg_restore -d [database] "/tmp/dump.backup"

Postgres Command Line

help
help
postgres help
\?
SQL help
\h
quit
\q
show database in console:
\l
connect to database
\c [database]
show tables, views, sequences
\d
describe table
\d [table]
show tables only
\dt
show sequences only
\ds
show views only
\dv
execute queries from file
\i [FILE]
output query results to file
\o [FILE]

Postgres queries

administration

show Databases
SELECT datname FROM pg_database WHERE datistemplate = false;
show tables
SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
show users
SELECT u.usename AS "User name",
  u.usesysid AS "User ID",
  CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text)
       WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
       WHEN u.usecreatedb THEN CAST('create database' AS pg_catalog.text)
       ELSE CAST('' AS pg_catalog.text)
  END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;
Useful / Used queries
create user
CREATE USER xxx PASSWORD 'yyy';
GRANT ALL ON SCHEMA public TO xxx;
create database
CREATE DATABASE [dbname];
GRANT ALL PRIVILEGES ON [dbname] TO [user];
super user privileges
alter role [user] with superuser;
change password
ALTER ROLE [role] WITH PASSWORD '[passw]';
drop users / databases
drop owned by [role];
drop database [dbname];
drop schema public CASCADE;
drop user [user];

editing tables

describe table
select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = '[table]';
create sequence
CREATE SEQUENCE [name]
CREATE SEQUENCE [name]
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
create sequence if not exists
CREATE SEQUENCE IF NOT EXISTS [name]
create table
create table [table]([column] [type]);
create table if not exists
CREATE TABLE IF NOT EXISTS [table]();
rename table
ALTER TABLE [old_name] RENAME TO [new_name];
add column
ALTER TABLE [table] ADD COLUMN [column] character varying(255);
add column with constraint, integer must be positive
ALTER TABLE [table] ADD COLUMN [column] integer NOT NULL DEFAULT 5 CHECK ([table].[column] >= 0);
drop single column
ALTER TABLE [table] DROP [column];
drop multiple columns
ALTER TABLE [table] DROP [column1] DROP [column2];
dropping a constraint
alter table users drop constraint [constraint];
add forein key constraint
ALTER TABLE ONLY [table] ADD CONSTRAINT [constraint_name] FOREIGN KEY ([column]) REFERENCES [referenced_table]([referenced_column]);

constraints

assigning a name to a constraint
CREATE TABLE products (
    product_no integer CONSTRAINT [name] UNIQUE,
check constraint on column
price numeric CHECK (price > 0)
check constraint on table
CHECK (price > 0)
check constraint with name
price numeric CONSTRAINT positive_price CHECK (price > 0)
check constraint over multiple columns
CREATE TABLE products (
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);
check not empty
CHECK (ads <> '')
check regex (string not blank)
CREATE TABLE mytable(mystring CHAR(6) PRIMARY KEY CHECK (mystring !~'^\s*$'));
foreign key
product_no integer REFERENCES products (product_no),
cascade delete
product_no integer REFERENCES products (product_no) ON DELETE CASCADE,
alter table only [table] add constraint [constraint_name] FOREIGN KEY ([column]) REFERENCES [table] ([column]) ON DELETE CASCADE;
unique
CREATE TABLE products (
    product_no integer UNIQUE

CREATE TABLE products (
    product_no integer,
    UNIQUE (product_no)
);
multi column unique
CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

Postgres CRUD

Reading a field of type text
select convert_from(loread(lo_open([COLUMN]::int, x'40000'::int), x'40000'::int),  'UTF8') from [TABLE];
insert
INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
conditional insert
INSERT INTO [table] SELECT '[value_to_insert]' where not exists (
	select * from [table] where [condition]
);
insert multiple rows
insert into [table] ([column]) values (8), (12);
update single column
update [table] set [column] = '[value]' where [condition];
update multiple columns
update [table] set ([column1], [column2]) = ([value1],[value2]) where [condition];
inner join
select people.* from people
    INNER JOIN pets ON pets.owner = people.id where [condition]
;
get a value from a sequence
SELECT nextval('[name]');

datatypes

https://www.postgresql.org/docs/current/datatype.html
https://www.postgresql.org/docs/current/datatype-numeric.html
https://www.postgresql.org/docs/current/datatype-character.html
https://www.postgresql.org/docs/current/datatype-binary.html
https://www.postgresql.org/docs/current/datatype-datetime.html
https://www.postgresql.org/docs/current/datatype-boolean.html

postgres regex

regex operator
~ 	Matches regular expression, case sensitive
~* 	Matches regular expression, case insensitive
!~ 	Does not match regular expression, case sensitive
!~* 	Does not match regular expression, case insensitive
regex classes
\d 	[[:digit:]]
\s 	[[:space:]]
\w 	[[:alnum:]_] (note underscore is included)
\D 	[^[:digit:]]
\S 	[^[:space:]]
\W 	[^[:alnum:]_] (note underscore is included)
regex syntax
https://www.postgresql.org/docs/current/functions-matching.html

examples

regex is blank
~'^\s*$'
regex 6 digits
~'^\d{6}$'
~'^[0-9]{6}$'
regex with boolean logic
VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$'
regex tester
https://rextester.com/OKFCC50792
regex tester sample code
CREATE TABLE tabq(myCode CHAR(6) PRIMARY KEY CHECK (myCode ~'^\d{6}$'));
INSERT INTO tabq(myCode) VALUES ('0000');
SELECT * FROM tabq;

Java JDBC driver

Get Datasource
PGPoolingDataSource source = new PGPoolingDataSource();
source.setDataSourceName("postgre");
source.setServerName("localhost");
source.setDatabaseName("example");
source.setUser("example");
source.setPassword("example");
source.setMaxConnections(10);