Postgresql: UUID的使用

默认安装的 Postgresql 是不带 UUID 函数的，为了生成一个 UUID，我们不得不在客户端生成。其实在 Postgresql 的安装目录下已经存在这样的函数定义，我们只需将其 import 进来即可。

 

在安装目录的 share/contrib 下面，可以找到 uuid-ossp.sql，使用以下命令：

psql -d pisces -U postgres -f D:/Service/PostgreSQL/9.0/share/contrib/uuid-ossp.sql

 

 

参数说明：

-d: 数据库名

-U: 用户名

-f: 要import的文件

 

以下截屏说明导入正确：

 

然后我们可以尝试用导入的函数生成一个UUID：

select uuid_generate_v4();

 

 

更多uuid函数，请在postgresql文档中搜索：

uuid-ossp

在C#中使用uuid类型的例子：

 

//---------------------------------------------------------------------------// <copyright file="Program.cs" company="Xinsoft.org">// Copyright (c) Yaping Xin. All rights reserved.// </copyright>// <Description>Entry class for the testing.</Description>//---------------------------------------------------------------------------namespace Org.Xinsoft.Demo.Postgresql{ using System; using System.Data; using Npgsql; /// <summary> /// Entry class for the testing. /// </summary> internal class TestUuidProgram { /// <summary> /// Entry point for the testing. /// </summary> /// <param name="args">console arguments</param> internal static void Main(string[] args) { using (DatabaseOperation db = new DatabaseOperation()) { db.OpenConnection(); Console.WriteLine(db.ConnState); string sql = "insert into demotable(id, name) values(@id, @name);"; using (Npgsql.NpgsqlCommand command = db.Connection.CreateCommand()) { command.CommandText = sql; command.CommandType = CommandType.Text; command.Parameters.Add("@id", NpgsqlTypes.NpgsqlDbType.Uuid); command.Parameters.Add("@name", NpgsqlTypes.NpgsqlDbType.Varchar, 25); command.Prepare(); for (int i = 0; i < 10; i++) { command.Parameters[0].Value = Guid.NewGuid(); command.Parameters[1].Value = string.Format("{0} {1}", i, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")); int effects = command.ExecuteNonQuery(); Console.WriteLine(string.Format("INSERT: {0} rows effected.", effects)); } } sql = "select id, name from demotable;"; using (Npgsql.NpgsqlCommand command = db.Connection.CreateCommand()) { command.CommandText = sql; command.CommandType = CommandType.Text; command.Prepare(); DataTable result = DatabaseHelper.GetDataTable(command.ExecuteReader()); int rows = result.Rows.Count; int cols = result.Columns.Count; Console.WriteLine("Rows: {0}", rows); for (int i = 0; i < cols; i++) { Console.WriteLine("Column[{0}]: {1}/t| {2}", i, result.Columns[i].ColumnName, result.Columns[i].DataType.Name); } Console.WriteLine("Query result:"); for (int i = 0; i < rows; i++) { DataRow row = result.Rows[i]; for (int j = 0; j < cols; j++) { Console.Write("{0}/t", row[j].ToString()); } Console.WriteLine(string.Empty); } } sql = "delete from demotable;"; using (Npgsql.NpgsqlCommand command = db.Connection.CreateCommand()) { command.CommandText = sql; command.CommandType = CommandType.Text; int effects = command.ExecuteNonQuery(); Console.WriteLine(string.Format("DELETE: {0} rows effected.", effects)); } db.CloseConnection(); } } }}

 

上述例子用到的表结构如下：

 

CREATE TABLE demotable( id uuid NOT NULL, "name" character varying(25), CONSTRAINT pk_demotable PRIMARY KEY (id))WITH ( OIDS=FALSE);ALTER TABLE demotable OWNER TO agentusr;