pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow do I add/edit 'digit' property into PGAdmin4 ?
7+ messages / 4 participants
[nested] [flat]
* How do I add/edit 'digit' property into PGAdmin4 ?
@ 2019-07-18 15:29 Karen Goh <karenworld@yahoo.com>
2019-07-18 16:12 ` Re: How do I add/edit 'digit' property into PGAdmin4 ? Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-07-18 17:12 ` Re: How do I add/edit 'digit' property into PGAdmin4 ? Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 2 replies; 7+ messages in thread
From: Karen Goh @ 2019-07-18 15:29 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How do I add/edit 'digit' property into PGAdmin4 ?
2019-07-18 15:29 How do I add/edit 'digit' property into PGAdmin4 ? Karen Goh <karenworld@yahoo.com>
@ 2019-07-18 16:12 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-07-19 11:46 ` Re: How do I add/edit 'digit' property into PGAdmin4 ? Karen Goh <karenworld@yahoo.com>
1 sibling, 1 reply; 7+ messages in thread
From: Shreeyansh Dba @ 2019-07-18 16:12 UTC (permalink / raw)
To: Karen Goh <karenworld@yahoo.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com
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.
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How do I add/edit 'digit' property into PGAdmin4 ?
2019-07-18 15:29 How do I add/edit 'digit' property into PGAdmin4 ? Karen Goh <karenworld@yahoo.com>
2019-07-18 16:12 ` Re: How do I add/edit 'digit' property into PGAdmin4 ? Shreeyansh Dba <shreeyansh2014@gmail.com>
@ 2019-07-19 11:46 ` Karen Goh <karenworld@yahoo.com>
2019-07-19 23:40 ` Re: How do I add/edit 'digit' property into PGAdmin4 ? Anthony E. Greene <agreene@pobox.com>
0 siblings, 1 reply; 7+ messages in thread
From: Karen Goh @ 2019-07-19 11:46 UTC (permalink / raw)
To: Shreeyansh Dba <shreeyansh2014@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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...:(
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.
Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How do I add/edit 'digit' property into PGAdmin4 ?
2019-07-18 15:29 How do I add/edit 'digit' property into PGAdmin4 ? Karen Goh <karenworld@yahoo.com>
2019-07-18 16:12 ` Re: How do I add/edit 'digit' property into PGAdmin4 ? Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-07-19 11:46 ` Re: How do I add/edit 'digit' property into PGAdmin4 ? Karen Goh <karenworld@yahoo.com>
@ 2019-07-19 23:40 ` Anthony E. Greene <agreene@pobox.com>
2019-07-20 02:11 ` Re: How do I add/edit 'digit' property into PGAdmin4 ? Karen Goh <karenworld@yahoo.com>
2019-07-20 02:23 ` Re: How do I add/edit 'digit' property into PGAdmin4 ? Karen Goh <karenworld@yahoo.com>
0 siblings, 2 replies; 7+ messages in thread
From: Anthony E. Greene @ 2019-07-19 23:40 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How do I add/edit 'digit' property into PGAdmin4 ?
2019-07-18 15:29 How do I add/edit 'digit' property into PGAdmin4 ? Karen Goh <karenworld@yahoo.com>
2019-07-18 16:12 ` Re: How do I add/edit 'digit' property into PGAdmin4 ? Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-07-19 11:46 ` Re: How do I add/edit 'digit' property into PGAdmin4 ? Karen Goh <karenworld@yahoo.com>
2019-07-19 23:40 ` Re: How do I add/edit 'digit' property into PGAdmin4 ? Anthony E. Greene <agreene@pobox.com>
@ 2019-07-20 02:11 ` Karen Goh <karenworld@yahoo.com>
1 sibling, 0 replies; 7+ messages in thread
From: Karen Goh @ 2019-07-20 02:11 UTC (permalink / raw)
To: Anthony E. Greene <agreene@pobox.com>; pgsql-admin@lists.postgresql.org
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>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How do I add/edit 'digit' property into PGAdmin4 ?
2019-07-18 15:29 How do I add/edit 'digit' property into PGAdmin4 ? Karen Goh <karenworld@yahoo.com>
2019-07-18 16:12 ` Re: How do I add/edit 'digit' property into PGAdmin4 ? Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-07-19 11:46 ` Re: How do I add/edit 'digit' property into PGAdmin4 ? Karen Goh <karenworld@yahoo.com>
2019-07-19 23:40 ` Re: How do I add/edit 'digit' property into PGAdmin4 ? Anthony E. Greene <agreene@pobox.com>
@ 2019-07-20 02:23 ` Karen Goh <karenworld@yahoo.com>
1 sibling, 0 replies; 7+ messages in thread
From: Karen Goh @ 2019-07-20 02:23 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org, "Anthony E. Greene" <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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How do I add/edit 'digit' property into PGAdmin4 ?
2019-07-18 15:29 How do I add/edit 'digit' property into PGAdmin4 ? Karen Goh <karenworld@yahoo.com>
@ 2019-07-18 17:12 ` Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 0 replies; 7+ messages in thread
From: Scott Ribe @ 2019-07-18 17:12 UTC (permalink / raw)
To: Karen Goh <karenworld@yahoo.com>; +Cc: pgsql-admin@lists.postgresql.org
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 type won't work. Perhaps what you need is a text type with a check constraint to limit it to digits.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2019-07-20 02:23 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-07-18 15:29 How do I add/edit 'digit' property into PGAdmin4 ? Karen Goh <karenworld@yahoo.com>
2019-07-18 16:12 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-07-19 11:46 ` Karen Goh <karenworld@yahoo.com>
2019-07-19 23:40 ` Anthony E. Greene <agreene@pobox.com>
2019-07-20 02:11 ` Karen Goh <karenworld@yahoo.com>
2019-07-20 02:23 ` Karen Goh <karenworld@yahoo.com>
2019-07-18 17:12 ` Scott Ribe <scott_ribe@elevated-dev.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox