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

the Sonar database can be accessed using 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

show Databases
SELECT datname FROM pg_database WHERE datistemplate = false;
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];
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];

Postgres SQL

Reading a field of type text
select convert_from(loread(lo_open([COLUMN]::int, x'40000'::int), x'40000'::int),  'UTF8') from [TABLE];
dropping a constraint
alter table users drop constraint [constraint];
create table
create table [table]([column] [type]);
rename table
ALTER TABLE [old_name] RENAME TO [new_name];
add forein key constraint
ALTER TABLE ONLY [table] ADD CONSTRAINT [constraint_name] FOREIGN KEY ([column]) REFERENCES [referenced_table]([referenced_column]);
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]
;

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);