#autoincrement

http://stackoverflow.com/questions/4567574/pylons-sqlite-and-autoincrementing-fields

How to add autoincrement pk to table in pgAdmin?

To add autoincrement value to table you can use this:

ALTER TABLE public.”Uzytkownicy” ADD COLUMN id BIGSERIAL PRIMARY KEY;

If you have values that are on the table the unique value of id will be added automaticly.

We use some sequences for generating unique identifiers in tables. It is used different techniques for generating UIDs. In Oracle or PgSQL we create a sequence and use next value of it every time when we insert into the particular table. In mysql there is an attribute for the column to be a autoincrement field. 

Sometimes we need to take its last value. The first idea will be using LAST_INSERT_ID() function in MySQL. But it will not work for your every scenario. It is useful when you have just inserted a row. 

What about to see AI value for any table in any schema when you want? Googling did not give an adequate result for me. So I used different approach. As you know mysql keeps all information about db in schema named information_schema . 

You can fetch any data related to table structures using informaton_schema. The table called “TABLES” keeps list of tables and attributes for all tables in database. 

mysql> show columns from tables; +-----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint(21) unsigned | YES | | NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) unsigned | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_FREE | bigint(21) unsigned | YES | | NULL | | | AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar(32) | YES | | NULL | | | CHECKSUM | bigint(21) unsigned | YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | | NULL | | | TABLE_COMMENT | varchar(2048) | NO | | | | +-----------------+---------------------+------+-----+---------+-------+ 21 rows in set (0.00 sec)

The column AUTO_INCREMENT is what we need. The following query will be useful for our purpose:

SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA='db_name' AND TABLE_NAME='table_name'

To make it more user-friendly I have created a function that returns that value for any table:

CREATE FUNCTION `current_ai_value`(p_schema_name VARCHAR(255), p_table_name VARCHAR(255)) RETURNS int(11) BEGIN DECLARE current_value INT; select AUTO_INCREMENT INTO current_value FROM information_schema.TABLES WHERE TABLE_NAME=p_table_name and table_schema=p_schema_name; RETURN current_value; END

That's it: