Hi, In my table I have a primary k

[aneev]

Hi,
In my table I have a primary key column proposaid which will take values like
x6a1
x6a2
.
.
.
x6a10
.
.

In order to insert a unique proposalid (kind of auto increment but with varchar type), I was doing
select max(proposalid) from proposalinfo;

But now I found that once proposalid reaches x6a10, the maximum proposalid I get everytime is x6a9. So, my logic fails. Could anyone tell me how to get the last inserted row so that I can get the last proposalid being inserted.
or would any other logic works better?

 

[rycamor]

Using a function like MAX on a varchar type implies all sorts of future problems. Why don't you just use a real autoincrement field as your primary key, and create another unique key with your naming convention above, if it is important for human-readable reasons?

But anyway, on to the full explanation: in your above query, PostgreSQL is doing its best to cast the varchar elements to numeric form in order to make the comparison. Unfortunately, when you go from x6a9, to x6a10, all it really sees is 'x6a1', for comparison purposes, because the '0' is moved into the next column.

If you want to continue using your system, you need to 'zerofill' the numbers, estimating the maximum size you need to support, like so:

x001a00001
x001a00002
x001a00003
.
.
.
x002a00001
x002a00002
.
.
.
x002b00001
x002b00002
.
.
.
x002c00001
x002c00002

etc... But you have to "fix" your columns. For example, if the second alphabetic column spills past the letters of one alphabet, you cannot continue with

x002aa00001
.
.
.
x002ab00001
x002ab00002

This would ruin your ability to handle a MAX comparison, because the numbers in that column are now moved over by one character.

I also think you will find that running MAX on this kind of naming scheme will perform badly if your table becomes large.

-------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[aneev]

I understand it's a problem. But our requirement is that the proposalid should be a unique value which is autoincrementing but with a prefix x6a (name x6a has some significance). I am trying to find a best logic which would perform well without any hassels. If you think fo any good alternative, please let me know. Meanwhile I will also be researching on this. I am using php in combination with postgresql to design our webpage on linux OS with apache as web server.