PostgreSQL mini howto

vytvorenie uzivatela
createuser

znicenie uzivatela
destroyuser

vytvorenie DB
createdb

pristup cez siet
postmastera spustiti s parametrom -i
a upravit subor /var/lib/pgsql/pg_hba.conf
ktory je v podstate hosts.allow pre pgsql

pozrenie si uzivatelov
sucnut sa ako postgres super-user (zvyc. postgres)
psql 
select * from pg_user ;

pozrenie si permissnov na \z

pozrenie si vseob helpu \?

pozrenie si syntaxu sql \h 

vylistuje sys tabulky
\dS


o zadavani prikazov a importe/exporte dat

you can use *.sql files in this dir 
using \i in psql 
text file containing data you wish to copy to table on sql server
has to be on host where the sql server is

e.g to create sample table in your db you just have to do this
\i crtable.sql
\i copydat.sql


file containing something like this will create db
CREATE TABLE Employees (
Last		text,
First		text,
Job_Title	text,
Department	text,
EMail		text,
Phone		text
);

file containing something like this will fill db with data
copy employees from '/home/jakub/www/html/db/employees.txt' using delimiters ';' ;

#tento ukaze vsetko co je v pg_class a nie je systemove 'pg_' a nie je index
#to su prakticky vsetky ostatne tabulky aj objekty 
select relname from pg_class where not relname~'pg_.*' and not relkind ='i';

Now bit of SQL Queries
having table rad like
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| id                               | int4 not null                    |     4 |
| name                             | text                             |   var |
+----------------------------------+----------------------------------+-------+
Index:    rad_pkey
with values
id|name  
--+------
 1|ahoj1 
 2|ahoj2 
 3|ahoj3 
 4|ahoj4 
 5|ahoj5 
 7|ahoj7 
 8|ahoj8 
 9|ahoj9 
13|ahoj13
14|ahoj14
15|ahoj15
16|ahoj16
17|ahoj17
(13 rows)

select a.id from rad a where not exists (select b.id from rad b where b.id = a.id +1);
vyberie vsetky id ktore nemaju nad sebou pokracovanie
select a.id+1 from rad a where not exists (select b.id from rad b where b.id = a.id +1);
vyberie toho pokracovatela kt neni


nefungovala history v psql
pi.. kur.. jak som sa s tym dlho je...

preco ?!? preco ??! no lebo pouzivaju na to readline library
rpm -ivh readline-devel (pochopitelne aj readline ako taky)


kompilovanie jdbc zo 7.1.3 src aj ked server je 7.0.2
adresar jdbc zo src/interfaces
vpodstate treba upravit Makefile
ja som len zmenil vrchnu cast

*** Makefile	Sat Oct  6 04:17:59 2001
--- Makefile.bak	Sat Oct  6 02:14:42 2001
***************
*** 9,26 ****
  #-------------------------------------------------------------------------
  
! #subdir = src/interfaces/jdbc
! #top_builddir = ../../..
! #include $(top_builddir)/src/Makefile.global
  
! #majorversion := $(shell echo $(VERSION) | sed 's/^\([0-9][0-9]*\)\..*$$/\1/')
! #minorversion := $(shell echo $(VERSION) | sed 's/^[0-9][0-9]*\.\([0-9][0-9]*\).*$$/\1/')
  
! ANT=$(HOME)/bin/ant
! properties := -Dmajor=7 -Dminor=0 \
! 		-Dfullversion=7.0.2 \
! 		-Ddef_pgport=5432
  
  all:
! 	$(ANT) -verbose -logfile blog.log -buildfile build.xml $(properties)
  
  install: installdirs
--- 9,25 ----
  #-------------------------------------------------------------------------
  
! subdir = src/interfaces/jdbc
! top_builddir = ../../..
! include $(top_builddir)/src/Makefile.global
  
! majorversion := $(shell echo $(VERSION) | sed 's/^\([0-9][0-9]*\)\..*$$/\1/')
! minorversion := $(shell echo $(VERSION) | sed 's/^[0-9][0-9]*\.\([0-9][0-9]*\).*$$/\1/')
  
! properties := -Dmajor=$(majorversion) -Dminor=$(minorversion) \
! 		-Dfullversion=$(VERSION) \
! 		-Ddef_pgport=$(DEF_PGPORT)
  
  all:
! 	$(ANT) -buildfile $(top_srcdir)/build.xml $(properties)
  
  install: installdirs


------------
TRANSACTIONS 

default READ COMMITTED 
na zobrazenie pouziva sa
SHOW TRANSACTION ISOLATION LEVEL;

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT
UPDATE
SELECT
--and we still see only work we have done noone elses
END;

if one serialized transaction deletes a record and commites
and the other one tries to update it
we'll see
update foolist set text = 'jakub' where id = 21;
ERROR:  Can't serialize access due to concurrent update

------------

------------
INTERESTING QUERY WITH SUBSELECT


CREATE TABLE "persons" (
	"pid" int4 NOT NULL,
	"name" character varying(200)
);
CREATE TABLE "messages" (
	"mid" int4  NOT NULL,
	"pid" int4  NOT NULL,
	"text" character varying(200)
);
COPY "persons" FROM stdin;
1	name1
2	name2
\.
COPY "messages" FROM stdin;
1	1	sprava1
2	1	sprava2
3	1	sprava3
4	2	sprava4
\.

select pid,name,(select count(*) from messages m where m.pid  = p.pid ) as msg_count from persons p;
pid | name  | msg_count 
-----+-------+-----------
  1 | name1 |         3
  2 | name2 |         1

MORE 
alter table persons add msg_count int4;

update persons set msg_count = (select count(*) from messages m where m.pid = persons.pid);
select * from persons;
pid | name  | msg_count 
-----+-------+-----------
  1 | name1 |         3
  2 | name2 |         1


------------


#some updates to add a column
alter table ntool_newsletters add column subject varchar(255);
update pg_attribute set attnotnull = 't' where attname = 'from_email' and attrelid = (select oid from pg_class where relname = 'ntool_newsletters');

csv straight from postgres
 psql -t -A -F"," -c "select id,name from big_data where date_created >= '2014-12-01 00:00:00' and date_created <= '2015-01-14 23:59:59' order by date_created" > report.csv

better to set -F field separator to \t depending if your text data could contain the separator

#To see query activity
SELECT * FROM pg_stat_activity;