天道酬勤,学无止境

Deferrable, case-insensitive unique constraint

Is it possible in PostgreSQL to create a deferrable unique constraint on a character column, but case-insensitive?

Let's assume the following basic table:

CREATE TABLE sample_table ( 
   my_column VARCHAR(100)
);

If deferrable constraint is not needed, it is as simple as creating unique index with function, e.g.:

CREATE UNIQUE INDEX my_unique_index ON sample_table(UPPER(my_column));

Deferred constraint check requires creating the constraint explicitly, e.g.:

ALTER TABLE sample_table 
 ADD CONSTRAINT my_unique_constraint UNIQUE(my_column)
 DEFERRABLE INITIALLY IMMEDIATE;

And unfortunately it is not possible to use arbitrary functions in unique constraint.

One possible workaround would be to create additional column with the same content as my_column, but upper case, updated via a trigger after each update/insert, then create a deferrable unique constraint on this artificial column. This, however, sounds like a really ugly hack.

Alternatively, it should be possible to use CREATE CONSTRAINT TRIGGER and manually check for case-insensitive uniqueness (of course a regular index would still be necessary). This sounds a bit overcomplicated for such a simple (and popular, I suppose) requirement.

Is there any simpler and/or more elegant way around this limitation?

评论

You can circumvent the restriction by using the special type citext provided by the additional module of the same name. Quoting the manual:

The citext module provides a case-insensitive character string type, citext. Essentially, it internally calls lower when comparing values. Otherwise, it behaves almost exactly like text.

It addresses your case exactly. Run once per database:

CREATE EXTENSION citext;

Then you can:

CREATE TABLE sample_table ( 
   my_column citext
  ,CONSTRAINT my_unique_constraint UNIQUE(my_column)
   DEFERRABLE INITIALLY IMMEDIATE
);

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • 约束定义的“可立即初始化”是否仍被延迟?(Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?)
    问题 关于这个答案,我偶然发现了一个我无法解释的现象。 版本: x86_64-unknown-linux-gnu上的PostgreSQL 9.1.2,由gcc-4.4.real(Debian 4.4.5-8)4.4.5,64位编译 测试平台: CREATE TEMP TABLE t ( id integer , txt text , CONSTRAINT t_pkey PRIMARY KEY (id) DEFERRABLE INITIALLY IMMEDIATE ); INSERT INTO t VALUES (1, 'one') , (2, 'two'); 1) UPDATE语句修改多行: UPDATE t SET id = t_old.id FROM t t_old WHERE (t.id, t_old.id) IN ((1,2), (2,1)); 上面的UPDATE可以正常运行,尽管它预期不会这样做。 该约束定义为INITIALLY IMMEDIATE ,我没有使用SET CONSTRAINTS 。 我是否缺少某些东西,或者这是一个(无害的)错误? 2)数据修改CTE 因此,修改CTE的数据也可以工作。 虽然它失败并显示NOT DEFERRED pk: WITH x AS (UPDATE t SET id = 1 WHERE id = 2) UPDATE t SET id =
  • Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
    In connection with this answer I stumbled upon a phenomenon I cannot explain. Version: PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit Testbed: CREATE TEMP TABLE t ( id integer , txt text , CONSTRAINT t_pkey PRIMARY KEY (id) DEFERRABLE INITIALLY IMMEDIATE ); INSERT INTO t VALUES (1, 'one') , (2, 'two'); 1) UPDATE statement modifying multiple rows: UPDATE t SET id = t_old.id FROM t t_old WHERE (t.id, t_old.id) IN ((1,2), (2,1)); The above UPDATE works, though it expected it should not. The constraint is defined INITIALLY IMMEDIATE and I did
  • NOT DEFERRABLE versus DEFERRABLE INITIALLY IMMEDIATE
    I read this about the SQL keyword DEFERRABLE in Database Systems - The Complete Book. The latter [NOT DEFERRABLE] is default, and means that every time a database modification statement is executed, the constraint is checked immediately afterwards, if the modification could violate the foreign-key constraint. However, if we declare a constraint to be DEFERRABLE, then we have the option of having it wait until a transaction is complete before checking the constraint. We follow the keyword DEFERRABLE by either INITIALLY DEFERRED or INITIALLY IMMEDIATE. In the former case, checking will be
  • Deferrable Constraints in SQL Server
    Do any versions of SQL Server support deferrable constraints (DC)? Since about version 8.0, Oracle has supported deferrable constraints - constraints that are only evaluated when you commit a statement group, not when you insert or update individual tables. Deferrable constraints differ from just disabling/enabling constraints, in that the constraints are still active - they are just evaluated later (when the batch is committed). The benefit of DC is that they allow updates that individually would be illegal to be evaluated that cummulatively result in a valid end state. An example is creating
  • 在SQL中,两个表相互引用是否可以?(In SQL, is it OK for two tables to refer to each other?)
    问题 在此系统中,我们存储产品,产品图像(产品可能有很多图像)和产品的默认图像。 数据库: CREATE TABLE `products` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `NAME` varchar(255) NOT NULL, `DESCRIPTION` text NOT NULL, `ENABLED` tinyint(1) NOT NULL DEFAULT '1', `DATEADDED` datetime NOT NULL, `DEFAULT_PICTURE_ID` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`ID`), KEY `Index_2` (`DATEADDED`), KEY `FK_products_1` (`DEFAULT_PICTURE_ID`), CONSTRAINT `FK_products_1` FOREIGN KEY (`DEFAULT_PICTURE_ID`) REFERENCES `products_pictures` (`ID`) ON DELETE SET NULL ON UPDATE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8; CREATE
  • sql statement error: “column .. does not exist”
    Im trying from postgres console this command: select sim.id as idsim, num.id as idnum from main_sim sim left join main_number num on (FK_Numbers_id=num.id); and I've got this response: ERROR: column "fk_numbers_id" does not exist LINE 1: ...m from main_sim sim left join main_number num on (FK_Numbers... but if I simply check my table with: dbMobile=# \d main_sim id | integer | not null default Iccid | character varying(19) | not null ... FK_Device_id | integer | FK_Numbers_id | integer | Indexes: "main_sim_pkey" PRIMARY KEY, btree (id) "main_sim_FK_Numbers_id_key" UNIQUE, btree ("FK_Numbers_id
  • sql语句错误:“列..不存在”(sql statement error: “column .. does not exist”)
    问题 我试图从postgres控制台此命令: select sim.id as idsim, num.id as idnum from main_sim sim left join main_number num on (FK_Numbers_id=num.id); 我得到了这个回应: ERROR: column "fk_numbers_id" does not exist LINE 1: ...m from main_sim sim left join main_number num on (FK_Numbers... 但是如果我只是用以下方法检查我的桌子: dbMobile=# \d main_sim id | integer | not null default Iccid | character varying(19) | not null ... FK_Device_id | integer | FK_Numbers_id | integer | Indexes: "main_sim_pkey" PRIMARY KEY, btree (id) "main_sim_FK_Numbers_id_key" UNIQUE, btree ("FK_Numbers_id") "main_sim_Iccid_key" UNIQUE, btree ("Iccid") "main_sim
  • 禁用PostgreSQL外键检查以进行迁移(Disable PostgreSQL foreign key checks for migrations)
    问题 我正在创建许多在PostgreSQL 9.4中具有外键的迁移。 令人头疼的是,表在迁移时必须全部符合外键所期望的确切顺序。 如果我必须从我的新迁移所依赖的其他程序包中运行迁移以获取外键,则该问题变得更加棘手。 在MySQL中,我可以通过简单地添加SET FOREIGN_KEY_CHECKS = 0;来简化此过程SET FOREIGN_KEY_CHECKS = 0; 到我的迁移文件的顶部。 如何仅在迁移代码的长度内临时在PostgresSQL中执行此操作? 顺便说一句,为此使用Laravel Schema Builder。 回答1 PostgreSQL不支持任何配置选项,但是还有另一种可能性。 postgres=# \d b Table "public.b" ┌────────┬─────────┬───────────┐ │ Column │ Type │ Modifiers │ ╞════════╪═════════╪═══════════╡ │ id │ integer │ │ └────────┴─────────┴───────────┘ Foreign-key constraints: "b_id_fkey" FOREIGN KEY (id) REFERENCES a(id) DEFERRABLE Postgres中的参照完整性是由触发器实现的
  • 如何列出引用SQL Server中给定表的所有外键?(How can I list all foreign keys referencing a given table in SQL Server?)
    问题 我需要在SQL Server数据库中删除一个高度引用的表。 我如何获取要删除表需要删除的所有外键约束的列表? (与在Management Studio的GUI中单击相比,SQL的回答要好。 回答1 不知道为什么没有人建议,但是我使用sp_fkeys查询给定表的外键: EXEC sp_fkeys 'TableName' 您还可以指定架构: EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo' 在未指定架构的情况下,文档规定以下内容: 如果未指定pktable_owner,则适用基础DBMS的默认表可见性规则。 在SQL Server中,如果当前用户拥有具有指定名称的表,则返回该表的列。 如果未指定pktable_owner且当前用户不拥有具有指定pktable_name的表,则该过程将查找数据库所有者拥有的具有指定pktable_name的表。 如果存在,则返回该表的列。 回答2 我会使用SQL Server Management Studio中的数据库图表功能,但是由于您排除了这一点,所以这在SQL Server 2008(没有2005)中对我有用。 要获取引用表和列名称的列表... select t.name as TableWithForeignKey, fk.constraint_column
  • Linux系统下SQL Server数据库操作
    Linux系统SQL Server数据库操作命令 连接数据库 sqlcmd命令创建数据库:查看所有数据库:创建表添加主键约束外部关键字怎么添加唯一约束怎么添加非空约束怎么使用默认约束设置表的属性值自动增加 连接数据库 sqlcmd命令 SQLServer 提供了 sqlcmd 命令,来让我们操作 SQLServer 数据库。接下来我们连接它,并在其中创建一个名为 TestDb 的数据库。 连接 SQLServer :sqlcmd -S localhost -U sa -P '<123123Aa!@>' 如果连接失败原因是服务还未开启,等待几秒重试即可,上述语句各参数解释如下: sqlcmd 是连接数据库必要的语句, localhost 代表从本地连接,sa 是用户名, ‘<123123Aa!@>’ 是密码。 创建数据库: create database TestDb。 查看所有数据库: select name from sys.databases。 操作完毕之后退出: exit 。 创建表 我们先来了解一下,在数据库中创建表的规则: CREATE TABLE 表名 ( 字段名,数据类型, 字段名,数据类型, ..... ) 例如: 添加了数据的表格,示例如下: 首先,创建数据库(在 SQLServer 数据库中是不区分大小写的,不过数据库的名字是区分大小写的),如下: CREATE
  • 不区分大小写的array_unique(case-insensitive array_unique)
    问题 我试图写几行代码以使不区分大小写的数组唯一类型函数。 这是我到目前为止的内容: foreach ($topics as $value) { $lvalue = strtolower($value); $uvalue = strtolower($value); if (in_array($value, $topics) == FALSE || in_array($lvalue, $topics) == FALSE || in_array($uvalue, $topics) == FALSE) { array_push($utopics, $value); } } 麻烦的是if语句。 我认为我的语法有问题,但是我对PHP还是比较陌生,我不确定它是什么。 有什么帮助吗? 回答1 function array_iunique( $array ) { return array_intersect_key( $array, array_unique( array_map( "strtolower", $array ) ) ); } 回答2 您要将lvalue和uvalue lvalue都设置为小写版本。 $uvalue = strtolower($value); 应该 $uvalue = strtoupper($value); 也就是说,这可能会更快一些。 您的函数的性能将呈指数级下降
  • Case-insensitive unique index in Rails/ActiveRecord?
    I need to create a case-insensitive index on a column in rails. I did this via SQL: execute( "CREATE UNIQUE INDEX index_users_on_lower_email_index ON users (lower(email))" ) This works great, but in my schema.rb file I have: add_index "users", [nil], :name => "index_users_on_lower_email_index", :unique => true Notice the "nil". So when I try to clone the database to run a test, I get an obvious error. Am I doing something wrong here? Is there some other convention that I should be using inside rails? Thanks for the help.
  • 如何获得连续的,递增的数字的列,而又不丢失任何数字?(How do I get a column with consecutive, increasing numbers, without having any numbers missing?)
    问题 可能的重复项: 如何在PostgreSQL查询中显示行号? 在PostgreSQL中对带有标识符的列进行重新排序使用带有子选择的更新对PostgreSQL记录进行重新排序 我只是问是否在PostgreSQL中是否存在这样的可能性,如果我有5行并且在一列中有数字1, 2, 3, 4, 5并且如果我删除说postgreSQL重新枚举的第三行,则在那些列中不是主键此列,因此我可以使用1, 2, 3, 4而不是1, 2, 4, 5 ? 回答1 改编自先前的答案。 当应用程序想要变量的制表顺序(请参阅:EAV模型中的记录)时,这种事情经常发生,这也可能是备用键(的一部分)。 priority字段需要保持连续。 [这是制表顺序] 在INSERT上:所有优先级> =新记录的记录的优先级应递增类似地:在DELETE上->递减如果记录的优先级被UPDATE更改,则旧优先级值和新优先级值之间的记录的优先级应上移或下移。 为了避免递归触发调用: 基于触发器的更新会翻转他们触摸的任何记录的flipflag 。 然后他们测试old.flipflag=new.flipflag以检测实际更新。 (不是由触发器引起的) -- Make some data DROP SCHEMA tmp CASCADE; CREATE SCHEMA tmp ; SET search_path=tmp; CREATE
  • case-insensitive array_unique
    I'm trying to write a few lines of code to make a case insensitive array unique type function. Here's what I have so far: foreach ($topics as $value) { $lvalue = strtolower($value); $uvalue = strtolower($value); if (in_array($value, $topics) == FALSE || in_array($lvalue, $topics) == FALSE || in_array($uvalue, $topics) == FALSE) { array_push($utopics, $value); } } The trouble is the if statement. I think there's something wrong with my syntax, but I'm relatively new to PHP and I'm not sure what it is. Any help?
  • PostgreSQL foreign key not existing, issue of inheritance?
    I am struggling with foreign keys in my DB, possibly it has something to do with inheritance? So here's the basic setup: -- table address CREATE TABLE address ( pk_address serial NOT NULL, fk_gadmid_0 integer NOT NULL, -- this table already exists, no problem here street character varying(100), zip character varying(10), city character varying(50), public boolean, CONSTRAINT address_primarykey PRIMARY KEY (pk_address), CONSTRAINT gadmid_0_primarykey FOREIGN KEY (fk_gadmid_0) REFERENCES adm0 (gadmid_0) MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION ) WITH ( OIDS=FALSE ); ALTER TABLE address
  • Rails模型中不区分大小写的搜索(Case-insensitive search in Rails model)
    问题 我的产品型号包含一些物品 Product.first => #<Product id: 10, name: "Blue jeans" > 我现在要从另一个数据集中导入一些产品参数,但是名称的拼写不一致。 例如,在另一个数据集中,“ Blue jeans可以拼写为“ Blue Jeans 。 我想要Product.find_or_create_by_name("Blue Jeans") ,但这将创建一个新产品,几乎与第一个相同。 如果我想查找和比较小写的名字,该怎么办? 性能问题在这里并不是真正重要的:只有100-200种产品,我想将其作为导入数据的迁移来运行。 有任何想法吗? 回答1 您可能需要在这里更加详细 name = "Blue Jeans" model = Product.where('lower(name) = ?', name.downcase).first model ||= Product.create(:name => name) 回答2 这是Rails中的完整设置,仅供我参考。 如果它也对您有帮助,我感到很高兴。 查询: Product.where("lower(name) = ?", name.downcase).first 验证者: validates :name, presence: true, uniqueness: {case_sensitive
  • Split models.py into several files
    I'm trying to split the models.py of my app into several files: My first guess was do this: myproject/ settings.py manage.py urls.py __init__.py app1/ views.py __init__.py models/ __init__.py model1.py model2.py app2/ views.py __init__.py models/ __init__.py model3.py model4.py This doesn't work, then i found this, but in this solution i still have a problem, when i run python manage.py sqlall app1 I got something like: BEGIN; CREATE TABLE "product_product" ( "id" serial NOT NULL PRIMARY KEY, "store_id" integer NOT NULL ) ; -- The following references should be added but depend on non-existent
  • 如何使用T-SQL暂时禁用外键约束?(How can foreign key constraints be temporarily disabled using T-SQL?)
    问题 SQL Server是否支持禁用和启用外键约束? 还是我唯一的选择是先drop然后重新create约束? 回答1 如果要禁用数据库中的所有约束,请运行以下代码: -- disable all constraints EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" 要重新打开它们,请运行:(当然,打印是可选的,它只是列出表) -- enable all constraints exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" 当从一个数据库向另一个数据库填充数据时,我发现它很有用。 这比放弃约束要好得多。 正如您提到的,在删除数据库中的所有数据并重新填充它们时(例如在测试环境中),它很方便。 如果要删除所有数据,则可能会发现此解决方案很有帮助。 另外有时也很方便地禁用所有触发器,您可以在此处查看完整的解决方案。 回答2 http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx -- Disable all table constraints ALTER TABLE
  • INSERT OR REPLACE + foreign key ON DELETE CASCADE working too good
    I am currently trying to create an sqlite database where I can import a table from another sqlite database (can't attach) and add some extra data to each column. Since there is no INSERT OR UPDATE I came up with this: I was thinking about splitting the data into two tables and join them afterwards so I can just dump the whole import into one table replacing everything that changed and manage the extra data separately since that does not change on import. The first table (let's call it base_data) would look like local_id | remote_id | base_data1 | base_data2 | ... ---------+-----------+--------
  • Postgres唯一约束与索引(Postgres unique constraint vs index)
    问题 据我所知,以下定义是等效的: create table foo ( id serial primary key, code integer, label text, constraint foo_uq unique (code, label)); create table foo ( id serial primary key, code integer, label text); create unique index foo_idx on foo using btree (code, label); 但是,Postgres 9.4手册中的注释指出: 向表添加唯一约束的首选方法是ALTER TABLE ... ADD CONSTRAINT 。 使用索引强制实施唯一约束可以被认为是不应直接访问的实现细节。 (编辑:此说明已从Postgres 9.5的手册中删除。) 只是风格上的问题吗? 选择这些变体之一会带来哪些实际后果(例如性能)? 回答1 我对这个基本但重要的问题有些怀疑,因此我决定以身作则。 让我们创建具有两列的测试表母版,即具有唯一约束的con_id和由唯一索引索引的ind_id 。 create table master ( con_id integer unique, ind_id integer ); create unique index master