Thread: alter column to inet get error.

alter column to inet get error.

From

Steve Clark

Date:

03 September 2010, 13:21:42

Hello List,

I want to change some columns in a database
that were originally created as char varying to
inet.

When I try I get an error. Is there anyway to work
around this?

See below for table definition.

                 Table "public.kernel_gre"
      Column      |         Type          |   Modifiers
-----------------+-----------------------+---------------
  interface       | character varying(15) | not null
  source_ip       | character varying(16) |
  dest_ip         | character varying(16) |
  physical_ip     | character varying(16) |
  status          | boolean               | default false
  physical_src_ip | character varying(16) |
  tunnel_netmask  | character varying(16) |
  key             | character varying(32) |
  state           | boolean               | default false
  broadcast       | boolean               | default false

alter TABLE kernel_gre ALTER COLUMN  source_ip TYPE inet;
ERROR:  column "source_ip" cannot be cast to type "inet"

current table contents:
  interface | source_ip |  dest_ip  | physical_ip | status | physical_src_ip | tunnel_netmask  | key | state |
broadcast

-----------+-----------+-----------+-------------+--------+-----------------+-----------------+-----+-------+-----------
  gre2      | 10.1.1.2  | 10.1.1.1  | 1.1.1.1     | t      | 1.1.1.2         | 255.255.255.255 |     | f     | f
  gre3      | 10.1.1.4  | 10.1.1.3  | 1.1.1.3     | t      | 1.1.1.4         | 255.255.255.255 |     | f     | f
  gre4      | 10.1.1.6  | 10.1.1.5  | 1.1.1.5     | t      | 1.1.1.6         | 255.255.255.255 |     | f     | f
  gre5      | 10.1.1.8  | 10.1.1.7  | 1.1.1.7     | t      | 1.1.1.8         | 255.255.255.255 |     | f     | f
  gre6      | 10.1.1.10 | 10.1.1.9  | 1.1.1.9     | t      | 1.1.1.10        | 255.255.255.255 |     | f     | f
  gre7      | 10.1.1.12 | 10.1.1.11 | 1.1.1.11    | t      | 1.1.1.12        | 255.255.255.255 |     | f     | f
  gre8      | 10.1.1.14 | 10.1.1.13 | 1.1.1.13    | t      | 1.1.1.14        | 255.255.255.255 |     | f     | f
  gre9      | 10.1.1.16 | 10.1.1.15 | 1.1.1.15    | t      | 1.1.1.16        | 255.255.255.255 |     | f     | f
  gre10     | 10.1.1.18 | 10.1.1.17 | 1.1.1.17    | t      | 1.1.1.18        | 255.255.255.255 |     | f     | f
  gre11     | 10.1.1.20 | 10.1.1.19 | 1.1.1.19    | t      | 1.1.1.20        | 255.255.255.255 |     | f     | f
  gre12     | 10.1.1.22 | 10.1.1.21 | 1.1.1.21    | t      | 1.1.1.22        | 255.255.255.255 |     | f     | f
  gre13     | 10.1.1.24 | 10.1.1.23 | 1.1.1.23    | t      | 1.1.1.24        | 255.255.255.255 |     | f     | f
  gre14     | 10.1.1.26 | 10.1.1.25 | 1.1.1.25    | t      | 1.1.1.26        | 255.255.255.255 |     | f     | f
  gre15     | 10.1.1.28 | 10.1.1.27 | 1.1.1.27    | t      | 1.1.1.28        | 255.255.255.255 |     | f     | f
  gre16     | 10.1.1.30 | 10.1.1.29 | 1.1.1.29    | t      | 1.1.1.30        | 255.255.255.255 |     | f     | f
  gre17     | 10.1.1.32 | 10.1.1.31 | 1.1.1.31    | t      | 1.1.1.32        | 255.255.255.255 |     | f     | f
  gre18     | 10.1.1.34 | 10.1.1.33 | 1.1.1.33    | t      | 1.1.1.34        | 255.255.255.255 |     | f     | f
  gre19     | 10.1.1.36 | 10.1.1.35 | 1.1.1.35    | t      | 1.1.1.36        | 255.255.255.255 |     | f     | f
  gre20     | 10.1.1.38 | 10.1.1.37 | 1.1.1.37    | t      | 1.1.1.38        | 255.255.255.255 |     | f     | f
  gre21     | 10.1.1.40 | 10.1.1.39 | 1.1.1.39    | t      | 1.1.1.40        | 255.255.255.255 |     | f     | f
(20 rows)




Thanks in advance,
--
Stephen Clark
NetWolves
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.clark@netwolves.com
www.netwolves.com

Re: alter column to inet get error.

From

Scott Marlowe

Date:

03 September 2010, 13:31:38

On Fri, Sep 3, 2010 at 7:21 AM, Steve Clark <sclark@netwolves.com> wrote:
> Hello List,
>
> I want to change some columns in a database
> that were originally created as char varying to
> inet.
>
> When I try I get an error. Is there anyway to work
> around this?
>
> See below for table definition.
>
>                Table "public.kernel_gre"
>     Column      |         Type          |   Modifiers
> -----------------+-----------------------+---------------
>  interface       | character varying(15) | not null
>  source_ip       | character varying(16) |
>  dest_ip         | character varying(16) |
>  physical_ip     | character varying(16) |
>  status          | boolean               | default false
>  physical_src_ip | character varying(16) |
>  tunnel_netmask  | character varying(16) |
>  key             | character varying(32) |
>  state           | boolean               | default false
>  broadcast       | boolean               | default false
>
> alter TABLE kernel_gre ALTER COLUMN  source_ip TYPE inet;
> ERROR:  column "source_ip" cannot be cast to type "inet"
>

Try

alter TABLE kernel_gre ALTER COLUMN  source_ip TYPE inet using source_ip::inet
--
To understand recursion, one must first understand recursion.

Re: alter column to inet get error.

From

"A. Kretschmer"

Date:

03 September 2010, 13:38:13

In response to Steve Clark :
> Hello List,
>
> I want to change some columns in a database
> that were originally created as char varying to
> inet.
>
> When I try I get an error. Is there anyway to work
> around this?
>
> See below for table definition.
>
>                 Table "public.kernel_gre"
>      Column      |         Type          |   Modifiers
> -----------------+-----------------------+---------------
>  interface       | character varying(15) | not null
>  source_ip       | character varying(16) |
>  dest_ip         | character varying(16) |
>  physical_ip     | character varying(16) |
>  status          | boolean               | default false
>  physical_src_ip | character varying(16) |
>  tunnel_netmask  | character varying(16) |
>  key             | character varying(32) |
>  state           | boolean               | default false
>  broadcast       | boolean               | default false
>
> alter TABLE kernel_gre ALTER COLUMN  source_ip TYPE inet;
> ERROR:  column "source_ip" cannot be cast to type "inet"

Try this with explicet cast:

test=# create table ip (ip text);
CREATE TABLE
Zeit: 247,763 ms
test=*# copy ip from stdin;
Geben Sie die zu kopierenden Daten ein, gefolgt von einem Zeilenende.
Beenden Sie mit einem Backslash und einem Punkt alleine auf einer Zeile.
>> 127.0.0.1
>> \.
Zeit: 5199,184 ms
test=*# alter table ip alter column ip type inet using ip::inet;
ALTER TABLE
Zeit: 242,569 ms
test=*# \d ip
    Tabelle »public.ip«
 Spalte | Typ  | Attribute
--------+------+-----------
 ip     | inet |

test=*#


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

Re: alter column to inet get error.

From

Scott Marlowe

Date:

03 September 2010, 14:01:12

On Fri, Sep 3, 2010 at 7:21 AM, Steve Clark <sclark@netwolves.com> wrote:
> Hello List,
>
> I want to change some columns in a database
> that were originally created as char varying to
> inet.
>
> When I try I get an error. Is there anyway to work
> around this?
>
> See below for table definition.
>
>                Table "public.kernel_gre"
>     Column      |         Type          |   Modifiers
> -----------------+-----------------------+---------------
>  interface       | character varying(15) | not null
>  source_ip       | character varying(16) |
>  dest_ip         | character varying(16) |
>  physical_ip     | character varying(16) |
>  status          | boolean               | default false
>  physical_src_ip | character varying(16) |
>  tunnel_netmask  | character varying(16) |
>  key             | character varying(32) |
>  state           | boolean               | default false
>  broadcast       | boolean               | default false
>
> alter TABLE kernel_gre ALTER COLUMN  source_ip TYPE inet;
> ERROR:  column "source_ip" cannot be cast to type "inet"
>

Try

alter TABLE kernel_gre ALTER COLUMN  source_ip TYPE inet using source_ip::inet
--
To understand recursion, one must first understand recursion.

Re: alter column to inet get error.

From

Steve Clark

Date:

03 September 2010, 14:37:41

On 09/03/2010 09:38 AM, A. Kretschmer wrote:
> In response to Steve Clark :
>> Hello List,
>>
>> I want to change some columns in a database
>> that were originally created as char varying to
>> inet.
>>
>> When I try I get an error. Is there anyway to work
>> around this?
>>
>> See below for table definition.
>>
>>                  Table "public.kernel_gre"
>>       Column      |         Type          |   Modifiers
>> -----------------+-----------------------+---------------
>>   interface       | character varying(15) | not null
>>   source_ip       | character varying(16) |
>>   dest_ip         | character varying(16) |
>>   physical_ip     | character varying(16) |
>>   status          | boolean               | default false
>>   physical_src_ip | character varying(16) |
>>   tunnel_netmask  | character varying(16) |
>>   key             | character varying(32) |
>>   state           | boolean               | default false
>>   broadcast       | boolean               | default false
>>
>> alter TABLE kernel_gre ALTER COLUMN  source_ip TYPE inet;
>> ERROR:  column "source_ip" cannot be cast to type "inet"
>
> Try this with explicet cast:
>
> test=# create table ip (ip text);
> CREATE TABLE
> Zeit: 247,763 ms
> test=*# copy ip from stdin;
> Geben Sie die zu kopierenden Daten ein, gefolgt von einem Zeilenende.
> Beenden Sie mit einem Backslash und einem Punkt alleine auf einer Zeile.
>>> 127.0.0.1
>>> \.
> Zeit: 5199,184 ms
> test=*# alter table ip alter column ip type inet using ip::inet;
> ALTER TABLE
> Zeit: 242,569 ms
> test=*# \d ip
>      Tabelle »public.ip«
>   Spalte | Typ  | Attribute
> --------+------+-----------
>   ip     | inet |
>
> test=*#
>
>
> Regards, Andreas

Thanks guys, that seems to do the trick. Postgresql ROCKS!!!


--
Stephen Clark
NetWolves
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.clark@netwolves.com
www.netwolves.com

Re: alter column to inet get error.

From

"A. Kretschmer"

Date:

03 September 2010, 16:01:25

In response to Steve Clark :
> >Try this with explicet cast:
>
> Thanks guys, that seems to do the trick. Postgresql ROCKS!!!

Yeah, definitively!

You are welcome, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99