Skip to main content

postgresql altering column type

ever had to change the column type and run into trouble?

well here is what I found as far:

* postgresql 7.4 ( and lower I think ) - cannot be done with the ALTER TABLE command, and then you have to do something like this:

BEGIN;
ALTER TABLE <table name> ADD COLUMN <new column> <new data type>;
UPDATE table SET <new column> = CAST(<old column> AS <new data type>);
ALTER TABLE <table> DROP COLUMN <old column>;
COMMIT;
VACUUM FULL <table>;

VACUUM FULL tab to reclaim the disk space used by the expired rows
( for more detailed information go here)

* postgresql 8.0 ( and higher ) - has implemented the ALTER TABLE ALTER COLUMN command and you have to do something like this:

ALTER TABLE <table name> ALTER COLUMN <column name> TYPE <new type> USING <column name>::<new type>;

( for more detailed information go here)
Labels:

Comments

Post a Comment

Popular posts from this blog

PowerShell script to add work email to the list fields for the author

The Author (Created By) field is actually an lookup field to the hidden User Information list. If you need to add the work email of the Author (Created By field) user to a specific list it can be done by adding a dependent lookup field for the Author field: $site = get - spsite "site url here"; $list = $site . RootWeb . Lists [ "list name" ]; $createdByEmailGuid = $web . Fields . AddDependentLookup ( "Work e-mail" , [ Microsoft . SharePoint . SPBuiltInFieldId ]:: Author ); $createdByEmail = $web . Fields . GetFieldByInternalName ( $createdByEmailGuid ); $createdByEmail . Description = "Work e-mail address of the Author."; $createdByEmail . LookupField = $web . Fields [[ Microsoft . SharePoint . SPBuiltInFieldId ]:: EMail ]. InternalName ; $createdByEmail . Update (); $web . Update (); $list . Fields . Add ( $createdByEmail ); $list . Update (); If additional fields are needed the same method can be applied. Here is a li
Post a Comment
Read more

Emmet in Visual Studio Code | cheat sheet link

Support for   Emmet   snippets and expansion is built right into Visual Studio Code,   no extension required .   Emmet 2.0   has support for the majority of the   Emmet Actions   including expanding   Emmet abbreviations and snippets .   Emmet is a web-developer’s toolkit that can greatly improve your HTML & CSS workflow   Links: Emmet in Visual Studio Code   Cheat Sheet (emmet.io)  
Post a Comment
Read more

SharePoint 2010 - Enable and configure unique Document IDs

There is a great article on how to Enable and configure unique Document IDs on Microsoft.com. In short you need to: Activate the Document ID site collection feature Enable and configure the use of Document IDs in a site collection But what it doesn't mention is that behind the scenes there are two time jobs that need to run to activate this feature: one to add the Document ID column and one to generate unique ids for the existing documents. But, these jobs are set to run once a day. So if you need your unique Document IDs to be activate right away, you need to go to Central Administration and run these two timer jobs for your respective web application: Document ID enable/disable job  Document ID assignment job 
Post a Comment
Read more