alter-table

ALTER table – adding AUTOINCREMENT in MySQL

CREATE TABLE ALLITEMS( itemid INT(10)UNSIGNED, itemname VARCHAR(50) ); ALTER TABLE ALLITEMS CHANGE itemid itemid INT(10)AUTO_INCREMENT PRIMARY KEY; DESC ALLITEMS; INSERT INTO ALLITEMS(itemname) VALUES (‘Apple’), (‘Orange’), (‘Banana’); SELECT * FROM ALLITEMS; I was confused with CHANGE and MODIFY keywords before too: ALTER TABLE ALLITEMS CHANGE itemid itemid INT(10)AUTO_INCREMENT PRIMARY KEY; ALTER TABLE ALLITEMS MODIFY itemid INT(5); … Read more

Create an index on a huge MySQL production table without table locking

[2017] Update: MySQL 5.6 has support for online index updates https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-index-syntax-notes In MySQL 5.6 and higher, the table remains available for read and write operations while the index is being created or dropped. The CREATE INDEX or DROP INDEX statement only finishes after all transactions that are accessing the table are completed, so that the … Read more

Error when setting n_distinct using a plpgsql variable

This is by design. The manual explains in the chapter Variable Substitution: Variable substitution currently works only in SELECT, INSERT, UPDATE, and DELETE commands, because the main SQL engine allows query parameters only in these commands. To use a non-constant name or value in other statement types (generically called utility statements), you must construct the … Read more

Check if a temporary table exists and delete if it exists before creating a temporary table

I cannot reproduce the error. Perhaps I’m not understanding the problem. The following works fine for me in SQL Server 2005, with the extra “foo” column appearing in the second select result: IF OBJECT_ID(‘tempdb..#Results’) IS NOT NULL DROP TABLE #Results GO CREATE TABLE #Results ( Company CHAR(3), StepId TINYINT, FieldId TINYINT ) GO select company, … Read more

How do I alter the position of a column in a PostgreSQL database table?

“Alter column position” in the PostgreSQL Wiki says: PostgreSQL currently defines column order based on the attnum column of the pg_attribute table. The only way to change column order is either by recreating the table, or by adding columns and rotating data until you reach the desired layout. That’s pretty weak, but in their defense, … Read more