FAQ Database Discussion Community


Alter column bytea to text in various versions of Postgres with plpgsql

postgresql,plpgsql,alter,version-numbering
I have problem how to convert column using plpgsql (bytea -> text). I wrote function which works on some databases and doesn't for others. I don't know how to fix it. Using databases from 8.0 - 9.3; this error is for 8.1.19. I received: ERROR: column "the_column" cannot be cast...

add new column to existing Composite Unique Key

mysql,unique-constraint,composite-key,alter,unique-index
I have table named "room" , and it has the following composite unique key room_acId_levelId_year_name_unique formed from FOUR columns : acId levelId year name And I want add column isLevel to that key without drop and recreate it The new key should like this: room_acId_levelId_year_name_isLevel_unique with the following columns :...

Add index to table if it does not exists

mysql,indexing,alter
I want to add index to table by using ALTER syntax and I also want to check if the index already exists in the table, and if it does not exists, then add the index. ALTER TABLE tableName ADD INDEX IX_Table_XYZ (column1); Is there any way to do this?...

Trying to better understand MySQL ALTER syntax

mysql,alter
I'm learning MySQL by reading the book "Head First SQL" but I've hit a brick wall. I can't seem to wrap my head around the code below. Specifically, I have a couple questions. When I try running the entire code as it is on the console (MySQL on Wamp) I...

Rename two columns in netezza using single query

table,rename,multiple-columns,netezza,alter
I am new to netezza. I need to know how can we rename two columns with a single query. I've tried , ALTER TABLE tabemp RENAME COLUMN salary to empsalary and name to empname; ALTER TABLE tabemp RENAME COLUMN salary to empsalary , name to empname; But none of these...

ALTER TABLE ADD AFTER works in SQL query but not in PHP

php,sql,mysqli,alter
When I run SQL query in phpMyAdmin directly it works; however, running the same query from PHP file does not affect the database table and no errors are thrown also. ALTER TABLE tender_230115 ADD avt DECIMAL(10,2) NOT NULL AFTER vpm PHP Version 5.3.28. Database connection is indeed established. When I...

Alter a file using information from another file

perl,file,format,alter,phylogeny
I want to alter the names in a phylip file using information from another file. The phylip is just one continuous string of information, and the names I want to alter (e.g. aaaaaaabyd) are embedded in it. Like so...

alter column (using + where)

postgresql,alter
I work with postgresql 9.3, I use this query to convert timestamp column to UTC. ALTER TABLE table ALTER timestamp TYPE timestamp USING timestamp AT TIME ZONE 'UTC' ; I need to add WHERE cluose to the query : ALTER TABLE table ALTER timestamp TYPE timestamp where timestamp < '2015-01-06...

Programmatically set column nullable

sql-server,tsql,alter
I would like to remove not null constraint on a column, whatever its type is (nvarchar, bingint, smallint). I am pretty sure it can be achieved using sp_executesql and building the ALTER TABLE xxx ALTER COLUMN cccc type NULL statement (by getting column type information from INFORMATION_SCHEMA.COLUMNS). Is there any...

Adding new enum column to an existing table

mysql,enums,alter
I'm trying to add a gender column to my table with this query: ALTER TABLE QRCodeUser ADD gender CHAR(1) enum('M','F') NOT NULL; I get this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax...

How to move mySql table column to first position [closed]

mysql,phpmyadmin,alter
How to move mySql table column to first position using Alter query with phpMyAdmin?

Mysql changing column from 'allow null' to 'not null' and setting value

mysql,foreign-keys,default-value,alter
CREATE TABLE `product` ( `product_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `category_id` int(10) unsigned DEFAULT NULL, CONSTRAINT `product_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`), ) ENGINE=InnoDB; CREATE TABLE `category` ( `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT ) ENGINE=InnoDB; INSERT INTO `category` VALUES (1), (2); INSERT INTO `product` (`product_id`, `category_id`) VALUES (1,...

How to use TAdoQuery to ALTER IB stored procedure

delphi,stored-procedures,ado,alter,interbase
We test migration of a D5 project (using IB 2009 Server and IBProvider) to XE6, keeping the same IB server an ADO provider. The project heavily uses stored procedures (SPs). At database creation (programmatically, with IB API), after having created (with TAdoQuery) the tables, exceptions and declaring (also with TAdoQuery)...