(Updated – Thanks to the people who commented) Modern Versions of PostgreSQL Suppose you have a table named test1, to which you want to add an auto-incrementing, primary-key id (surrogate) column. The following command should be sufficient in recent versions of PostgreSQL: ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY; Older Versions of PostgreSQL … Read more
Since PostgreSQL 8.2 you have to use: GRANT USAGE, SELECT ON SEQUENCE cities_id_seq TO www; GRANT USAGE – For sequences, this privilege allows the use of the currval and nextval functions. Also as pointed out by @epic_fil in the comments you can grant permissions to all the sequences in the schema with: GRANT USAGE, SELECT … Read more
Try this command: ALTER TABLE your_table ADD COLUMN key_column BIGSERIAL PRIMARY KEY; Try it with the same DB-user as the one you have created the table.
Issue the following command to reseed mytable to start at 1: DBCC CHECKIDENT (mytable, RESEED, 0) Read about it in the Books on Line (BOL, SQL help). Also be careful that you don’t have records higher than the seed you are setting.
If you created the table product with an id column, then the sequence is not simply called product, but rather product_id_seq (that is, ${table}_${column}_seq). This is the ALTER SEQUENCE command you need: ALTER SEQUENCE product_id_seq RESTART WITH 1453 You can see the sequences in your database using the \ds command in psql. If you do … Read more
You can get all of the table data by using this query: SHOW TABLE STATUS FROM `DatabaseName` WHERE `name` LIKE ‘TableName’ ; You can get exactly this information by using this query: SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘DatabaseName’ AND TABLE_NAME = ‘TableName’;
Use this: ALTER TABLE users AUTO_INCREMENT=1001; or if you haven’t already added an id column, also add it ALTER TABLE users ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (id);
You need to use the LAST_INSERT_ID() function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id Eg: INSERT INTO table_name (col1, col2,…) VALUES (‘val1’, ‘val2’…); SELECT LAST_INSERT_ID(); This will get you back the PRIMARY KEY value of the last row that you inserted: The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned … Read more
There is no such thing as “auto_increment” or “identity” columns in Oracle as of Oracle 11g. However, you can model it easily with a sequence and a trigger: Table definition: CREATE TABLE departments ( ID NUMBER(10) NOT NULL, DESCRIPTION VARCHAR2(50) NOT NULL); ALTER TABLE departments ADD ( CONSTRAINT dept_pk PRIMARY KEY (ID)); CREATE SEQUENCE dept_seq … Read more
Yes, SERIAL is the equivalent function. CREATE TABLE foo ( id SERIAL, bar varchar ); INSERT INTO foo (bar) VALUES (‘blah’); INSERT INTO foo (bar) VALUES (‘blah’); SELECT * FROM foo; +———-+ | 1 | blah | +———-+ | 2 | blah | +———-+ SERIAL is just a create table time macro around sequences. You … Read more