phpmyadmin
Perhaps you could just select the phpMyAdmin Operations tab:
- In phpMyAdmin, click on the table you want to reset or change the AUTO_INCREMENT value
- Click on the Operations Tab
- In the Table Options box find the auto_increment field.
- Enter the new auto_increment starting value
- Click on the Go button for the Table Options box.
Since this one of the most frequently asked questions for phpmyadmin, you can learn more about this in this blog : http://trebleclick.blogspot.com/2009/01/mysql-set-auto-increment-in-phpmyadmin.html
Supplemental Info
For an empty table, another way to reset the auto_increment attribute is to run
TRUNCATE TABLE mydb.tablename;
Don't run this if you have data in it. If you want to hose the data, then be my guest.
In phpmyadmin, just click the SQL
tab, enter the command, and run it.
For a nonempty table, you may want to adjust the auto_increment attribute to the highest existing id in use in case higher entries were deleted.
First, optimize the table
OPTIMIZE TABLE mydb.mytable;
Next, locate the highest value for the auto_increment column (say it is id
)
SELECT MAX(id) maxid FROM mydb.mytable;
Suppose the answer returns 27. Goto the Operations tab and enter 28.
Best Answer
PostgreSQL does not have "auto-increment" fields in the sense of MySQL's
AUTO_INCREMENT
, but I'm guessing you meanSERIAL
.If so, yes, what you describe is possible, but please, please don't do this.
A
SERIAL
is just shorthand for aCREATE SEQUENCE
and a default value. e.g.is actually shorthand for:
You can use this knowledge to cast the
nextval
return to a character type, e.g.but again, I beg of you, do not do this. It's horrible. It's wrong. You will regret it. Whatever you are trying to do, there is a better way to do it than this.
I have intentionally not shown how to generate a formatted field like
PAT000001
, but you can use any expression in aDEFAULT
, not just aCAST
. So look at theto_char
function or theformat
function for how to do this if you insist.The correct way to do what you want to do is not to do it. Get the application to display codes like
PAT000001
when it sees a primary key value forpatient_id
like1
. The user never needs to know that you're just storing integers.If you want other prefixes like
PAT
,DOC
, etc, use a composite primary key, e.g.(possibly with an
enum
type, anIN
list, or whatever for validation).Then in the application turn
PAT00001
into('PAT', 1)
for queries, and reverse it for display to the user.