方法1:使用create extension命令
win7需要通过管理员权限打开pgAdmin,输入命令:
create extension "uuid-ossp"
安装扩展成功以后,就可以通过uuid_generate_v4()或uuid_generate_v1()查询
方法2:
默认安装的 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;
方法2引用:http://blog.csdn.net/yapingxin/article/details/6365055