Postgresql: UUID的使用

hb_sz

　　默认安装的 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=&quot;Program.cs&quot; company=&quot;Xinsoft.org&quot;>//     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=&quot;args&quot;>console arguments</param>internal static void Main(string[] args){using (DatabaseOperation db = new DatabaseOperation()){db.OpenConnection();Console.WriteLine(db.ConnState);string sql = &quot;insert into demotable(id, name) values(@id, @name);&quot;;using (Npgsql.NpgsqlCommand command = db.Connection.CreateCommand()){command.CommandText = sql;command.CommandType = CommandType.Text;command.Parameters.Add(&quot;@id&quot;, NpgsqlTypes.NpgsqlDbType.Uuid);command.Parameters.Add(&quot;@name&quot;, 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(&quot;{0} {1}&quot;, i, DateTime.Now.ToString(&quot;yyyy-MM-dd HH:mm:ss.fff&quot;));int effects = command.ExecuteNonQuery();Console.WriteLine(string.Format(&quot;INSERT: {0} rows effected.&quot;, effects));}}sql = &quot;select id, name from demotable;&quot;;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(&quot;Rows: {0}&quot;, rows);for (int i = 0; i < cols; i++){Console.WriteLine(&quot;Column[{0}]: {1}\t| {2}&quot;, i, result.Columns.ColumnName, result.Columns.DataType.Name);}Console.WriteLine(&quot;Query result:&quot;);for (int i = 0; i < rows; i++){DataRow row = result.Rows;for (int j = 0; j < cols; j++){Console.Write(&quot;{0}\t&quot;, row[j].ToString());}Console.WriteLine(string.Empty);}}sql = &quot;delete from demotable;&quot;;using (Npgsql.NpgsqlCommand command = db.Connection.CreateCommand()){command.CommandText = sql;command.CommandType = CommandType.Text;int effects = command.ExecuteNonQuery();Console.WriteLine(string.Format(&quot;DELETE: {0} rows effected.&quot;, effects));}db.CloseConnection();}}}}
　　上述例子用到的表结构如下：
　　CREATE TABLE demotable(id uuid NOT NULL,&quot;name&quot; character varying(25),CONSTRAINT pk_demotable PRIMARY KEY (id))WITH (OIDS=FALSE);ALTER TABLE demotable OWNER TO agentusr;