1 安裝及配置
1.1 安裝
在windowXP下安裝 PostgreSQL 9.1:
Installation Directory àC:\Program Files\PostgreSQL\9.1
Data Directory à D:\PostgreSQL\9.1\data
Password à postgres
Port à5432
Locale à [Default locale]
1.2 配置
1.1.1遠(yuǎn)程連接:
修改文件：D:\PostgreSQL\9.1\data\pg_hba.conf, 增加:
**********************************
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.0.0/24 md5**********************************
使得ip在 192.168.0.0 到 192.168.0.255 之間的客戶端可以通過密碼訪問數(shù)據(jù)庫.
修改文件：D:\PostgreSQL\9.1\data\postgresql.conf，確保:
**********************************
listen_address = '*'
**********************************
1.1.2 表空間訪問權(quán)限
創(chuàng)建表空間文件夾: E:\PostgreSQL\mytablespace，修改文件夾權(quán)限，使postgres用戶有讀寫權(quán)限。
2 創(chuàng)建數(shù)據(jù)庫腳本
2.1 創(chuàng)建用戶及表空間腳本
CreateTableSpace.sql
**********************************
CREATE ROLE myuser LOGIN
PASSWORD 'mypassword'
SUPERUSER INHERIT NOCREATEDB NOCREATEROLE REPLICATION;
CREATE TABLESPACE mytablespace
OWNER myuser
LOCATION 'E:/PostgreSQL/mytablespace';**********************************
2.2 創(chuàng)建數(shù)據(jù)庫腳本
CreateTableSpace.sql
**********************************
CREATE DATABASE mydb
WITH OWNER = myuser
ENCODING = 'UTF8'
TABLESPACE = mytablespace
LC_COLLATE = 'Chinese_People''s Republic of China.936'
LC_CTYPE = 'Chinese_People''s Republic of China.936'
CONNECTION LIMIT = -1;**********************************
2.3 建表腳本
**********************************
CREATE TABLE mytable
(
id integer unique not null,
value varchar(50)
);
ALTER TABLE mytable OWNER TO myuser;
**********************************
2.4 導(dǎo)入缺省數(shù)據(jù)
Insert.sql
**********************************
insert into mytable (id, value) values (1, 'V1');
insert into mytable (id, value) values (2, 'V2');
**********************************
2.5 批處理文件
**********************************
set PGUSER=postgres
set PGPASSWORD=postgres
c:\Progra~1\PostgreSQL\9.1\bin\psql.exe -f CreateTableSpace.sql
c:\Progra~1\PostgreSQL\9.1\bin\psql.exe -d mydb -f CreateTable.sql
c:\Progra~1\PostgreSQL\9.1\bin\psql.exe -d mydb -f Insert.sql
**********************************
3 sql語句與Oracle的區(qū)別
3.1 數(shù)據(jù)類型
Oracle
PostgreSQL
VARCHAR2
VARCHAR （character varying）
Long、 CLOB
TEXT
DATE
DATE/TIME/TIMESTAMP
NUMBER
SMALLINT/INTEGER/BIGINT/NUMERIC/REAL/DOUBLE PRECISION
BLOB
SMALLINT/INTEGER/BIGINT/NUMERIC/REAL/DOUBLE PRECISION
sysdate
now()、O_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')、CURRENT_TIMESTAMP
Tips: PostgreSQL 中字段名區(qū)分大小寫，為保證兼容行，強(qiáng)烈建議腳本中的字符均用小寫，這樣在Sql語句中將忽略大小寫。
3.2 方法
Function
Returns
Example
to_char(timestamp, text)
text
to_char(timestamp 'now','HH12:MI:SS')
to_char(interval, text)
text
to_char(interval '15h 2m 12s','HH24:MI:SS')
to_char(int, text)
text
to_char(125, '999')
to_char(double precision, text)
text
to_char(125.8, '999D9')
to_char(numeric, text)
text
to_char(numeric '-125.8', '999D99S')
to_date(text, text)
date
to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text)
timestamp
to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)
numeric
to_number('12,454.8-', '99G999D9S')
3.3 sql
Oracle
PostgreSQL
Constraint
alter table schema.prefix_info add (
constraint pk_prefix_info primary key (info_id));
alter table schema.prefix_info add constraint prefix_info_pkey primary key(info_id);
Default Maximun in sequence
create sequence prefix_info_sequence
increment by 1
start with 582
minvalue 1
maxvalue 9999999999999999999999999999
nocycle
cache 20
noorder;
create sequence schema.prefix_info_sequence
increment 1
minvalue 1
maxvalue 9223372036854775807
start 582
cache 20;
||
select a||b from table1;
returns null when there is one null value in a and b.
Dual table
select sysdate from dual;
select now();
Associated query
select count(distinct(a.col1)) as rcount from
schema.prefix_table1 a,schema.prefix_table2 b
where 1 = 1
and a.col2 = b.col2(+)
and a.col3 > 0
and a.col4 = '1'
select count(distinct(a.col1)) as rcount from
schema.prefix_table1 a left outer join schema.prefix_table2 b on (a.col2 = b.col2)
where 1 = 1
select count(distinct(a.col1)) as rcount from
schema.prefix_table1 a,schema.prefix_table2 b,schema.prefix_table3 c,schema.prefix_table4 d
where 1 = 1
and a.col2 = b.col2
and a.col3 = c.col3(+)
and a.col4 = d.col4(+)
and a.col5 > 0
and a.col6 = '1'
select count(distinct(a.col1)) as rcount from
schema.prefix_table1 a inner join schema.prefix_table2 b on (a.col2 = b.col2)
left outer join schema.prefix_table3 c on (a.col3 = c.col3)
left outer join schema.prefix_table4 d on (a.col4 = d.col4)
where 1 = 1
and a.col5 > 0
and a.col6 = '1'
Subquery
oracle:
select * from (
select * from (
select * from schema.prefix_table order by col1
) where x=1 order by col2
) where y=2 order by col3
select * from (
select * from (
select * from schema.prefix_table order by col1 alias1
) where x=1 order by col2 alias2
) where y=2 order by col3
Rownum
Vs
limit
select * from ( select * from (select * from schema.prefix_table1 order by col1 desc,col2 asc) where rownum <= 50 order by col3 asc,col4 desc)
where rownum <= 20 order by col5 desc,col6 asc;
select * from ( select * from (select * from schema.prefix_table1 order by col1 desc,col2 asc) selb order by col3 asc,col4 desc limit 50 ) sela
order by col5 desc,col6 asc limit 20;
limit must be used after order by
sequence
select schema.prefix_table1_sequence.nextval as ncode from dual
select nextval('schema.prefix_table1_sequence') as ncode
AS
select a.col1 a_col1，a.col2 a_col2 from a_table a
select a.col1 as a_col1，a.col2 as a_col2 from a_table a
NVL
select nvl(sum(value11),0) fs_value1, nvl(sum(value21),0) fs_value2 from field_sum
select coalesce(sum(value11),0) as fs_value1,coalesce(sum(value21),0) as fs_value2
from field_sum
Decode
select decode(endflag,'1','a','b') endflagfrom test
select (caseendflag when '1' then 'a'else 'b' end) as endflag from test
3.4 函數(shù) / 存儲(chǔ)過程
例子:
1. CREATE OR REPLACE FUNCTION message_deletes(ids "varchar", userid int8)
2. RETURNS int4 AS
3. $BODY$
4. DECLARE
5. r RECORD;
6. del bool;
7. num int4 := 0;
8. sql "varchar";
9. BEGIN
10. sql := 'select id,receiveuserid,senduserid,senddelete,receivedelete from message where id in (' || ids || ')';
11. FOR r IN EXECUTE sql LOOP
12. del := false;
13. IF r.receiveuserid=userid and r.senduserid=userid THEN
14. del := true;
15. ELSEIF r.receiveuserid=userid THEN
16. IF r.senddelete=false THEN
17. update message set receivedelete=true where id = r.id;
18. ELSE
19. del := true;
20. END IF;
21. ELSEIF r.senduserid=userid THEN
22. IF r.receivedelete=false THEN
23. update message set senddelete=true where id = r.id;
24. ELSE
25. del := true;
26. END IF;
27. END IF;
28. IF del THEN
29. delete from message where id = r.id;
30. num := num + 1;
31. END IF;
32. END LOOP;
33. return num;
34. END;
35. $BODY$
36. LANGUAGE 'plpgsql' VOLATILE;
4 C#里的連接及查詢語句
4.1 Npgsql
下載 Npgsql .Net Data Provider for Postgresql 組件 根據(jù) .Net famework 的版本選擇正確地Npgsql版本
URL：
http://pgfoundry.org/frs/?group_id=1000140解壓zip 文件, 復(fù)制 Npgsql.dll、Mono.Security.dll 文件到 C# 工程的obj目錄下，在 VS2010中把Npgsql.dll文件加入到 References.
在需要使用Npgsql的C#頭文件加入如下 using 語句.
[c-sharp]
view plaincopy1. using Npgsql;
4.2 連接字符串
創(chuàng)建 PostgreSQL 數(shù)據(jù)庫連接
[c-sharp]
view plaincopy1.
2. string connectionString = "Server=127.0.0.1;Port=5432;User Id=myuser;Password=mypassword;Database=mydb;"
3. NpgsqlConnection conn = new NpgsqlConnection(string connectionString);
4. // 打開一個(gè)數(shù)據(jù)庫連接，在執(zhí)行相關(guān)SQL之前調(diào)用
5. conn.Open();
6. //關(guān)閉一個(gè)數(shù)據(jù)庫連接，在執(zhí)行完相關(guān)SQL之后調(diào)用
7. conn.Close();
4.3 查詢方法
用 NpgsqlCommand.ExecuteScalar() 方法獲得唯一值的檢索結(jié)果.
[c-sharp]
view plaincopy1. try
2. {
3. string sql = "select count(*) from mytable";
4. conn.Open();
5. NpgSqlCommand objCommand = new NpgSqlCommand(sql, conn);
6. int count = Convert.ToInt32(objCommand.ExecuteScalar());
7. }
8. finally
9. {
10. conn.Close();
11. }
用 NpgsqlCommand.ExecuteReader() 方法獲得一個(gè)結(jié)果集的檢索結(jié)果.
[c-sharp]
view plaincopy1. string sql = "select * from mytable";
2. NpgsqlCommand objCommand = new NpgsqlCommand(sql,conn);
3. NpgsqlDataReader dr = command.ExecuteReader();
4. while(dr.Read())
5. {
6. for (i = 0; i < dr.FieldCount; i++)
7. {
8. Console.Write("{0} /t", dr[i]); //獲得字段名
9. }
10. int testId = dr["id"]; // 獲得指定字段的值。（id是test表的一個(gè)字段）
11. ……
12. Console.WriteLine();
13. }
14. dr.Close();
4.4 修改方法
用 NpgsqlCommand.ExecuteNonQuery() 方法獲得對(duì)指定表進(jìn)行添加、更新和刪除一條記錄的操作
添加記錄
[c-sharp]
view plaincopy1. string sql = "insert mytable values (3,'V3')";
2. NpgsqlCommandobjCommand = new NpgsqlCommand(sql, conn);
3. objCommand.ExecuteNonQuery();
更新記錄
[c-sharp]
view plaincopy1. sql = "update mytable set value='V30' where id=3";
2. NpgsqlCommandobjCommand = new NpgsqlCommand(sql, conn);
3. objCommand.ExecuteNonQuery();
刪除記錄
[c-sharp]
view plaincopy1. sql = "delete from mytable where id=1";
2. NpgsqlCommandobjCommand = new NpgsqlCommand(sql, conn);
3. objCommand.ExecuteNonQuery();
用NpgsqlDataAdapter.Fill方法，把檢索的結(jié)果集放到DataSet object中，這樣可以使用DataSet object設(shè)置DotNet的DataGridView控件的DataSource屬性，這樣在DataGridView中顯示從表中取出的所有記錄。
[c-sharp]
view plaincopy1. string sql = "select id,value from test";
2. DataSet ds = new DataSet();
3. NpgsqlDataAdapter objAdapter = new NpgsqlDataAdapter(sql, conn);
4. objAdapter.Fill(ds, "a"); //“a”這個(gè)表是自定義的
5. dgvBaseResult.DataSource = ds.Tables["a"]; //dgvBaseResult是DataGridView的一個(gè)Object。
