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.Value = Guid.NewGuid();command.Parameters.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.ColumnName, result.Columns.DataType.Name);}Console.WriteLine("Query result:");for (int i = 0; i < rows; i++){DataRow row = result.Rows;for (int j = 0; j < cols; j++){Console.Write("{0}\t", row.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;
页:
[1]