From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: tid_le comparison for tuple id (ctid) values?
Date: Mon, 21 Nov 2005 16:00:16 -0500
Message-ID: <20051121160016.282c66c2.gry@ll.mit.edu> (raw)
[PostgreSQL 8.1.0 on i686-pc-linux-gnu]
I would like to suggest that there be a less-than (or greater-than)
operator for the 'tid' type.
I used to use oid's for finding and distinguishing duplicate data.
Now that oid's are not included by default (and I do not quarrel with
that change), I thought I could use ctid's instead.
Suppose I have a table steps:
create table steps(x text, y text, z text)
but I want there to be a primary key(x,y). If I try to do:
create table temp_steps(x text, y text, z text, primary key(x,y))
insert into temp_steps select * from steps;
drop table steps; alter table temp_steps rename to steps;
I get an error that "duplicate key violates unique constraint". Some of the rows in steps differ only in value of z. OK, I'll just fix the data...
I thought I could force values of x to be distinct with:
(I've done this several times in the past with oid's)
update steps set x=x||'X' from steps s where steps.key1=s.key1 and steps.key2=s.key2 and step.ctid<s.ctid;
But this fails because there is no less-than operator (or function) on type "tid". I tried casting the ctid to string but that fails too. Using "not s.ctid=step.ctid" doesn't get me what I need(BTW, there's no != operator for tid either).
I don't actually care which row of a pair gets changed. I just need a way to choose *one* in the update. [sometimes I do a delete with similar where clause]
If there was some asymmetrical comparison defined on ctids, as there had been on oids, this would all work fine. A cast to some type that has less-than would also work fine. Any suggestions?
-- George
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gry@ll.mit.edu
Subject: Re: tid_le comparison for tuple id (ctid) values?
In-Reply-To: <20051121160016.282c66c2.gry@ll.mit.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox