PostgreSQL is Powerful, Confuses Newbies
Looking at the list of PostgreSQL blog entries, I saw this one,
Add auto increment column in PostgreSQL, and realized why my database of choice isn’t so popular… it confuses people. Back in the old days, add an auto-numering field to PostgreSQL as a Primary Key was hard, you had to create the sequence and set the default. This was a common operation, since a numeric Primary Key is often easier than looking for a natural key, and even when we have a natural key, it’s usually easier to index an integer than a string if we are going to use it as a foreign key somewhere. The SERIAL “type” in PostgreSQL is the same as auto_increment, but it’s implemented as TYPE integer, attached SEQUENCE, and default value.
Example:
CREATE TABLE names_table (name_id SERIAL PRIMARY KEY, name varchar);
This gives you an PRIMARY KEY name_id, that will be auto incremented, and a string column name. I use these all the time, and the PRIMARY KEY shortcut is an easy way to make a single column the primary key. You can always use a primary key directive for composite keys in mapping tables and similar constructs.
I find it easy, because I know what a SERIAL is, but given that EVERY newcomer to PostgreSQL is looking for this feature, wouldn’t it make sense to highlight it in the documentation? Perhaps in the examples given? In addition, since auto increment is so common, why not support it? SERIAL is syntactic sugar anyway, why not have it accept the MS SQL/MySQL terminology, you can throw off a notice so people learn the preferred one, but why push off testers?
I have a question, why if I delete a row, then auto_increment (serial) not re-number,
example :
no | name
_________
1 | jack
2 | smith
3 | less
then I delete row number 2, why column no became like this :
no | name
_________
1 | jack
3 | less
thanks
Because removing row 2 doesn’t create a reason to eliminate row 3. The only rule is that the numbers are unique.
If you had 15 Billion lines in a table, and you removed one of them, you want the database to renumber 15 billion lines?
If you want to know the number of rows, do, SELECT count(*) FROM tablename; and you’ll get the count. If you want the last inserted one that isn’t deleted, you do SELECT max(no) FROM tablename, and those results may not be the same.