如何在安裝Postgres 8.2到Ubuntu 7.10 Gusty Gibbon

如何在ubuntu 7.10 安裝postgres 8.2
How To Installing Postgres 8.2 on Ubuntu 7.10

$ sudo apt-get install postgresql postgresql-client postgresql-contrib

$ sudo apt-get install pgadmin3

postgres default password is unknown so after your first installation you will need to change it
postgres使用者密碼安裝後的密碼是不可考的 所以安裝後要直接用query改掉

$ sudo su postgres -c psql template1
template1=# ALTER USER postgres WITH PASSWORD 'password';
template1=# \q

$ sudo passwd -d postgres
$ sudo su postgres -c passwd

http://hocuspokus.net/2007/11/05/install-postgresql-on-ubuntu-710/

如何備份以及還原Postgres資料庫

How to Backup and Restore Postgrest 8.2 Database in Ubuntu Linux
如何備份以及還原postgres資料庫

* restore postgres

psql -U joec database1 -f - <> database1.sql.gz

如何在Postgres底下設定PL/SQL 陣列資料型態的功能

How To PL/SQL in Postgres With Array Data Type

如何在postgres底下設定PL/SQL 陣列資料型態的功能
* postgres pl install and array avg function setup

http://frakkle.com/archives/archive_2006-m04.php

幫postgre開啟PL/SQL的功能
joec@celica:~$ createlang plpgsql arraydata1

在postgres底下設定PL/SQL 陣列資料型態
arraydata1=#

CREATE OR REPLACE FUNCTION array_to_set(ar_in numeric[])
RETURNS SETOF numeric AS
$BODY$
declare
rtn numeric;
ar_pointer int4 = 1;
BEGIN
--ar_upper := array_upper( ar_in );
WHILE ( ar_in[ar_pointer] IS NOT NULL ) LOOP
return next ar_in[ar_pointer];
ar_pointer := ar_pointer + 1;
END LOOP;
END
$BODY$
LANGUAGE 'plpgsql' STRICT IMMUTABLE;

postgres底下設定PL/SQL 陣列資料型態的平均，總合，計算次數，標準偏差數的功能

* create sum, count, avg, standard deviation functions
arraydata1=#
CREATE OR REPLACE FUNCTION sum(numeric[])
RETURNS numeric AS
$BODY$
SELECT sum( array_to_set )
FROM array_to_set( $1 )
$BODY$
LANGUAGE 'sql' STRICT IMMUTABLE;

CREATE FUNCTION count_array(numeric[])
RETURNS bigint AS
$BODY$
SELECT count( array_to_set )
FROM array_to_set( $1 )
$BODY$
LANGUAGE 'sql' STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION avg(numeric[])
RETURNS numeric AS
$BODY$
SELECT avg( array_to_set )
FROM array_to_set( $1 )
$BODY$
LANGUAGE 'sql' STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION stddev(numeric[])
RETURNS numeric AS
$BODY$
SELECT stddev( array_to_set )
FROM array_to_set( $1 )
$BODY$
LANGUAGE 'sql' STRICT IMMUTABLE;

arraydata1=# select avg(array_num[32:34]) from data;

How To Use Python To Import SQL Query

如何利用python的postgres模組來寫query

import pgsql (sudo apt-get install pgsql)
cursur
..
...
..