Postgresql
Published: 13:21, Thursday 14 July 2011
Notes
What's this? See my article about Notes.
View as plain text file.
install
sudo apt-get install postgresql
sudo apt-get install pgadmin3
start service: sudo service postgresql start
password
sudo -u postgres psql
pgdb
login: psql -h localhost -U postgres database
types:
numeric(a,b)
date/time functions:
age(timestamp, timestamp) -> interval
age(timestamp)
extract(field from interval) -> double precision
today: current_date
check null: where col is null
like pattern
% is like *
string concat
||
'preformatted % string', val
length()
conditional expr: case where e1 then r1 ... else rn end
average: avg(*)
operations:
create database:
from command line createdb, dropdb
show databases: select datname from pg_database;
execute
execute script
sudo -u postgres psql -f script.sql databaseName
\i filename.sql
show tables: select table_name from information_schema.tables where table_type = 'BASE TABLE' and table_schema not in ('pg_catalog', 'information_schema');
PL/pgSQL functions:
structure:
drop function fname(type,...)
create [or replace] function fname(arg type, ...)
returns retype as $$
[ declare
< declarations > ]
begin
< statements >
end;
$$ language plpgsql;
retype: integer, table...
declaration:
quantity integer := 30;
tbl_row tbl%rowtype;
statement:
quantity := 4;
if quantity < 5 then
quantity := 5;
end if;
while quantity < 10 loop
quantity := quantity + 1
end loop;
for tblrow in select * from tb loop
use tblrow.colname
end loop;
select count (*) into varname from ...
return varname (can return multiple times)
raise warning string
usage:
select fname(val);
select * from fname(vals);
triggers:
structure:
create trigger name
before/after insert/delete or update
on table
for each row
body
in an insert use new.cols
Write a Comment
* These fields are mandatory.