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

How to create a custom Workflow Task / SharePoint Server Workflow Task content type using Visual Studio?

If you are designing an Workflow and you want to create custom  Workflow Task or SharePoint Server Workflow Task  using Visual Studio interface then you'll need to make these two content types visible. To make them visible I'm using the following  PowerShell   script: $site = Get - SPSite "http://site/url/here" $web = $site . RootWeb # Workflow Task $contentType = $web . ContentTypes [ "Workflow Task" ] $contentType . Group = "Custom Content Types" $contentType . Hidden = $FALSE $contentType . Update () # SharePoint Server Workflow Task $contentType = $web . ContentTypes [ "SharePoint Server Workflow Task" ] $contentType . Group = "Custom Content Types" $contentType . Hidden = $FALSE $contentType . Update () $site . Dispose () Then when you can add a new Content Type from Visual Studio and you will see the two Content Type in the list. To revert these changes run this PowerShell script: ...
Post a Comment
Read more

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

SharePoint Designer 2010 Reusable Workflows with Lookup Values

When you create a Reusable Workflow in SharePoint Designer 2010, that uses lookup values, and you try to deploy it in another environment, the lookup values will get broken. This is happening because the list you are reading the lookup values from has another Guid. Here is what I came up with to make this deployment easier: Deploy the reusable workflow in the new environment. Open the site you have deployed the workflow solution in SharePoint Designer Go to the related list (List and Libraries > Your Related List Name) and copy the new List ID value. Go to All Files > Workflows >   Your Workflow Name >  Your Workflow Name. xoml and open it in SharePoint Designer as XML ( right click on it > Open with >  SharePoint Designer (Open as XML) ) Find an  and you will find the old list guid in the ListId  attribute  Replace the old guid value with the new one Save it and close the window. The workflow should be pointi...
Post a Comment
Read more