http://www.phpfreaks.com/postgresqlmanual/page/index.html
http://www.phpfreaks.com/postgresqlmanual/page/sql-revoke.html






===============================================================================
http://www.yolinux.com/TUTORIALS/LinuxTutorialPostgreSQL.html



The PostgreSQL Database - Installation and Configuration: 


Install (rpm -ivh package) or check for the following PostgreSQL RPMs (rpm -q package): 
postgresql-7.1.3-2 
postgresql-libs-7.1.3-2 
postgresql-server-7.1.3-2 
postgresql-perl-7.1.3-2 
Other packages available: 
postgresql-odbc 
postgresql-jdbc 
postgresql-tcl 
postgresql-tk 
postgresql-python 
postgresql-devel 
Install: rpm -ivh postgresql-7.1.3-2.i386.rpm postgresql-libs-7.1.3-2.i386.rpm postgresql-server-7.1.3-2.i386.rpm postgresql-perl-7.1.3-2.i386.rpm 


The user "postgres" should have already been configured by the installation of the RPMs. Info: 
User: postgres 
Home directory: /var/lib/pgsql 
Default shell: /bin/bash 
A password will be missing. As root issue the command: passwd postgres to assign a password for user postgres. 

Login as user postgres: su - postgres 
This will execute the profile: /var/lib/pgsql/.bash_profile 
PGLIB=/usr/lib/pgsql
PGDATA=/var/lib/pgsql/data
export PGLIB PGDATA
     
Initialize PostgreSQL database server: initdb --pgdata=/var/lib/pgsql/data 
This creates a bunch of directories, a template directory and sets up the postgres configuration in the user directory /var/lib/pgsql/. Red Hat start command (service)/script (rc script) will perform this task if it has not already been performed. See next step - Starting the database. 

Starting the database server: As root. (from most to least favorite method) 
service postgresql start 
(If the database has not already been initialized with initdb, this will be performed by the command) 
OR 
/etc/rc.d/init.d/postgresql start 
(If the database has not already been initialized with initdb, this will be performed by the script) 
OR 
/usr/bin/pg_ctl -D /var/lib/pgsql/data -p /usr/bin/postmaster -l logfile start & 
OR 
/usr/bin/postmaster -D /var/lib/pgsql/data & 
Notes: 
Configuration file: /var/lib/pgsql/data/postgresql.conf 
By default there is no network access. See the directive tcpip_socket. (Required for ODBC,JDBC) Also see the postmaster directive "-i". Logging and tuning parameters are specified here. 
Host Access file: /var/lib/pgsql/data/pg_hba.conf 
Authentication/Identification file: /var/lib/pgsql/data/pg_ident.conf 

Create a database: createdb bedrock 
(As Linux user postgres) 

Connect to the database: psql bedrock 
Execute command as Linux user postgres 
You will now be at the PostgreSQL command line prompt. 
[prompt]$ psql             - or "psql bedrock"
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

bedrock=#
bedrock=# \c bedrock
You are now connected to database bedrock.
bedrock=# create table employee (Name char(20),Dept char(20),jobTitle char(20));
CREATE
bedrock=# INSERT INTO employee VALUES ('Fred Flinstone','Quarry Worker','Rock Digger');
INSERT 18733 1
bedrock=# INSERT INTO employee VALUES ('Wilma Flinstone','Finance','Analyst');
bedrock=# INSERT into employee values ('Barney Rubble','Sales','Neighbor');
bedrock=# INSERT INTO employee VALUES ('Betty Rubble','IT','Neighbor');
bedrock=# SELECT * from employee;
         name         |         dept         |       jobtitle       
----------------------+----------------------+----------------------
 Fred Flinstone       | Quarry Worker        | Rock Digger         
 Wilma Flinstone      | Finance              | Analyst             
 Barney Rubble        | Sales                | Neighbor            
 Betty Rubble         | IT                   | Neighbor            
(4 rows)

bedrock=#
bedrock=# \q
    

More info: 
Create a user: 
Command line: [prompt]$ createuser dude 
SQL: CREATE USER dude WITH PASSWORD 'supersecret'; 
Change with ALTER USER 
Grant priveliges: 
SQL: GRANT UPDATE ON table-name to dude 
SQL: GRANT SELECT ON table-name to dude 
SQL: GRANT INSERT ON table-name to dude 
SQL: GRANT DELETE ON table-name to dude 
SQL: GRANT RULE ON table-name to dude 
-- odvzamem privilegij
ameba=# REVOKE INSERT ON ombrografi_vhodna FROM PUBLIC;
REVOKE

SQL - Do it all: GRANT ALL PRIVILEGES ON table-name to public 
Delete a user: 
Command line: [prompt]$ dropuser SuperDude 
Delete a database: 
Command line: 
[prompt]$ destroydb bedrock 
[prompt]$ dropdb bedrock 
SQL: DROP DATABASE bedrock; 
Create a database: 
Command line: [prompt]$ createdb bedrock -U user-name -W 
You will be prompted for a password. (or execute as Linux user postgres without -U and -W options) 
SQL: CREATE DATABASE bedrock 
Backup a database: 
[prompt]$ pg_dumpall > outfile 
[prompt]$ pg_dumpall -Fc dbname > outfile 
Version Upgrades: 
Dump: [prompt]$ postgresql-dump -t /var/lib/pgsql/backup/db.bak -p /var/lib/pgsql/backup/old -d 
Restore: [prompt]$ psql -e template1 < /var/lib/pgsql/backup/db.bak 
The table template1 is the default administrative database. 
If one would like to generate a GUI interface to the database, I would recommend a web form to a Java servlet back-end or use a JAVA GUI program and JDBC. See the YoLinux tutorial on accessing PostgreSQL with JDBC in a Tomcat servlet example. 

Links/Info: 

PostgreSQL documentation: 
/usr/share/doc/postgresql-7.1.3/html/index.html (local html) 
/usr/share/doc/postgresql-7.1.3/ (local directory) 
Resources: /usr/share/pgsql/ 

select x(geometry) as x_os from idmm_geom_tmp;
select astext(geometry) as xy from idmm_geom_tmp;

==================

select leto,mesec,dan,datum,round(k8_maksimalna_temperatura/10.0,1) as tmax,round(k9_minimalna_temperatura/10.0,1) as tmin,round(k10_minimalna_temperatura_5cm/10.0,1) as tmin5,CASE WHEN (k11_temperatura_suhi_7h>-999)and(k12_temperatura_suhi_14h>-999)and(k13_temperatura_suhi_21h>-999) then trunc(round((k11_temperatura_suhi_7h+k12_temperatura_suhi_14h+2*k13_temperatura_suhi_21h)/4.0)/10.0,1) else -999 end as tem_pov, CASE WHEN (k20_rel_vlaga_7h > -9 and k21_rel_vlaga_14h > -9 and k22_rel_vlaga_21h > -9) then round((k20_rel_vlaga_7h+ k21_rel_vlaga_14h+k22_rel_vlaga_21h)*1.0/3.0,0) else -9 end as vla_pov, round(k45_trajanje_sonca/10.0,1) as son,round(k49_padavine/10.0,1) as pad,k51_sneg_skupaj as ss,k52_sneg_novi as sn, round(k11_temperatura_suhi_7h/10.0,1) as ts7,round(k12_temperatura_suhi_14h/10.0,1) as ts14,round(k13_temperatura_suhi_21h/10.0,1) as ts21, k20_rel_vlaga_7h as rv7,k21_rel_vlaga_14h as rv14,k22_rel_vlaga_21h as rv21, k70_snezna_odeja as s,idmm,tip from klima_vhodna where postaja in(192,249,268,355,464) and datum between '1.10.2004' and '24.11.2004';

 pg_dump -x -o -t amp_datum ameba |gzip >amp_datum.gz
 pg_dump -x -o -t amp_datum -p 6543 ameba |gzip >amp_datum.gz
 gunzip -c amp_datum.gz | psql miharadb

gzip ime
gunzip ime.gz


 ssh base@virga

 psql -p 6543 -U postgres ameba
 mv uuuu virga_stare_tabele.txt

 unix2dos uuuu
less uuuu

 cd ~mihad/

[zvicar@virga aa]$ unix2dos a.a
unix2dos: converting file a.a to DOS format ...


-----------------------------------------------------------
Kako preimenujes fajle, das prozornost sliki, spremenis format jpg - gif
rename image iiko_xg *
for i in *.gif; do convert $i -transparent-color \#ffffff prozorne/$i ; done
for i in *.jpg; do convert $i prozorne/`basename $i .jpg`.gif ; done