Thread: How do I add/edit 'digit' property into PGAdmin4 ?
Hi all, I need help to change an existing column in my table from text to 'digit' type. However, I can't find any numeric or integer property type. The digit type should contains numbers only but it is not for calculation. It is a zipcode that has 6 digit numbers. Please let me know how do I alter the text type to digit type. If alteration is not possible, how do I add in the propertyinto PGAdmin4 which is running on windows 10. Thank you.
Hi Karen,
Use below query to change column type text to integer/numeric.
alter table <table_name> alter column <column_name> type int using (column_name::int);
alter table <table_name> alter column <column_name> type numeric using (column_name::numeric);
Hope this will help you.
Use below query to change column type text to integer/numeric.
alter table <table_name> alter column <column_name> type int using (column_name::int);
alter table <table_name> alter column <column_name> type numeric using (column_name::numeric);
Hope this will help you.
On Thu, Jul 18, 2019 at 8:59 PM Karen Goh <karenworld@yahoo.com> wrote:
Hi all,
I need help to change an existing column in my table from text to 'digit' type.
However, I can't find any numeric or integer property type.
The digit type should contains numbers only but it is not for calculation. It is a zipcode that has 6 digit numbers.
Please let me know how do I alter the text type to digit type. If alteration is not possible, how do I add in the property into PGAdmin4 which is running on windows 10.
Thank you.
On Jul 18, 2019, at 9:29 AM, Karen Goh <karenworld@yahoo.com> wrote: > > I need help to change an existing column in my table from text to 'digit' type. > > However, I can't find any numeric or integer property type. > > The digit type should contains numbers only but it is not for calculation. It is a zipcode that has 6 digit numbers. There is no digit type. I assume your 6-digit numbers can have leading 0s which need to be preserved, so an integer typewon't work. Perhaps what you need is a text type with a check constraint to limit it to digits.
Hi Shreeyansh,
Due to many mails in my inbox, I got to see your email only now.
I'd like to seek your help in terms of how to edit the data in this column that I want to change to numeric.
At one go?
So, in this column that I used the wrong property type - and now it contains CountryName + the 6 digit numbers.
Is there any way I can alter the data in that column at one go and remove the CountryName altogether?
I tried using pgAdmin4 but it is not working...:(
Due to many mails in my inbox, I got to see your email only now.
I'd like to seek your help in terms of how to edit the data in this column that I want to change to numeric.
At one go?
So, in this column that I used the wrong property type - and now it contains CountryName + the 6 digit numbers.
Is there any way I can alter the data in that column at one go and remove the CountryName altogether?
I tried using pgAdmin4 but it is not working...:(
On Friday, July 19, 2019, 12:14:04 AM GMT+8, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Hi Karen,
Use below query to change column type text to integer/numeric.
alter table <table_name> alter column <column_name> type int using (column_name::int);
alter table <table_name> alter column <column_name> type numeric using (column_name::numeric);
Hope this will help you.
Use below query to change column type text to integer/numeric.
alter table <table_name> alter column <column_name> type int using (column_name::int);
alter table <table_name> alter column <column_name> type numeric using (column_name::numeric);
Hope this will help you.
On Thu, Jul 18, 2019 at 8:59 PM Karen Goh <karenworld@yahoo.com> wrote:
Hi all,
I need help to change an existing column in my table from text to 'digit' type.
However, I can't find any numeric or integer property type.
The digit type should contains numbers only but it is not for calculation. It is a zipcode that has 6 digit numbers.
Please let me know how do I alter the text type to digit type. If alteration is not possible, how do I add in the property into PGAdmin4 which is running on windows 10.
Thank you.
On 19-Jul-2019/11:46 +0000, Karen Goh <karenworld@yahoo.com> wrote:
> Hi Shreeyansh,
>
>Due to many mails in my inbox, I got to see your email only now.
>
>I'd like to seek your help in terms of how to edit the data in this column that I want to change to numeric.
>
>At one go?
>
>So, in this column that I used the wrong property type - and now it contains CountryName + the 6 digit numbers.
>
>Is there any way I can alter the data in that column at one go and remove the CountryName altogether?
If the postal code is the last six characters, then this will do what you
want:
UPDATE mytable SET mycolumn=RIGHT(mycolumn,6) WHERE mycolumn ~ '[0-9]{6}$'
TG
--
Anthony E. Greene <mailto:agreene@pobox.com>
Sent from Yahoo Mail for iPhone
On Saturday, July 20, 2019, 7:40 AM, Anthony E. Greene <agreene@pobox.com> wrote:
On 19-Jul-2019/11:46 +0000, Karen Goh <karenworld@yahoo.com> wrote:
> Hi Shreeyansh,
>
>Due to many mails in my inbox, I got to see your email only now.
>
>I'd like to seek your help in terms of how to edit the data in this column that I want to change to numeric.
>
>At one go?
>
>So, in this column that I used the wrong property type - and now it contains CountryName + the 6 digit numbers.
>
>Is there any way I can alter the data in that column at one go and remove the CountryName altogether?
If the postal code is the last six characters, then this will do what you
want:
UPDATE mytable SET mycolumn=RIGHT(mycolumn,6) WHERE mycolumn ~ '[0-9]{6}$'
Hi Anthony, I tried what you said but very strange I received an error that said my table name is not exists. When I did a data query of all rows, all the data appeared.
TG
--
Anthony E. Greene <mailto:agreene@pobox.com>
On Saturday, July 20, 2019, 7:41:10 AM GMT+8, Anthony E. Greene <agreene@pobox.com> wrote:
On 19-Jul-2019/11:46 +0000, Karen Goh <karenworld@yahoo.com> wrote:
> Hi Shreeyansh,
>
>Due to many mails in my inbox, I got to see your email only now.
>
>I'd like to seek your help in terms of how to edit the data in this column that I want to change to numeric.
>
>At one go?
>
>So, in this column that I used the wrong property type - and now it contains CountryName + the 6 digit numbers.
>
>Is there any way I can alter the data in that column at one go and remove the CountryName altogether?
If the postal code is the last six characters, then this will do what you
want:
UPDATE mytable SET mycolumn=RIGHT(mycolumn,6) WHERE mycolumn ~ '[0-9]{6}$'
TG
Hello Anthony,
It works! Please ignore my last email.
Thank you so much. Hope you have a wonderful day!
--
Anthony E. Greene <mailto:agreene@pobox.com
>