Re: [GENERAL] alter column type

On Fri, Jun 5, 2015 at 4:00 PM, John R Pierce wrote: > Actually, not too late. My first ALTER is still running, and I still have > four more to go. Sigh. I had thought of this but wasn't sure how it might > compare. Thanks for the data point :) > > > > if all 5 alters' were to the same table,

Re: [GENERAL] alter column type

On 6/5/2015 11:46 AM, Casey Deccio wrote: On Fri, Jun 5, 2015 at 2:36 PM, Michael Nolan > wrote: Probably too late for this time, but in the past when I've needed to redefine the type for a column, I've made a dump, edited the dump file to change the type an

Re: [GENERAL] alter column type

Casey Deccio writes: > Being unfamiliar with the internals, what's the risk here? If postgres > thinks something is a bigint, but previously stored it as an int, does that > mean it will try to extract data beyond the boundary of some of the (old) > 32-bit values and potentially throw off offsets

Re: [GENERAL] alter column type

> In the above case PG will simply do a dictionary update of meta > tables. So all new rows will reflect col-T and as and when the old I will clarify it bit further: All new rows will have space allocated for col-T and no space allocated for col-S, while existing dormant rows are left unmodified

Re: [GENERAL] alter column type

> On 6/5/2015 11:37 AM, Ravi Krishna wrote: >> >> Why is PG even re-writing all rows when the data type is being changed >> from smaller (int) to larger (bigint) type, which automatically means >> existing data is safe. Like, changing from varchar(30) to varchar(50) >> should involve no rewrite of

Re: [GENERAL] alter column type

On Fri, Jun 5, 2015 at 2:23 PM, Melvin Davidson wrote: > CAUTION: This is very dangerous and may cause corruption. > *** DO THIS IN A TEST DATABASE FIRST *** > > --1. Get the oid for int8 (bigint) > SELECT t.oid > FROM pg_type t > WHERE typname = 'int8'; > > --2. Get the oid for your t

Re: [GENERAL] alter column type

On 6/5/2015 11:37 AM, Ravi Krishna wrote: Why is PG even re-writing all rows when the data type is being changed from smaller (int) to larger (bigint) type, which automatically means existing data is safe. Like, changing from varchar(30) to varchar(50) should involve no rewrite of existing rows.

Re: [GENERAL] alter column type

On Fri, Jun 5, 2015 at 2:36 PM, Michael Nolan wrote: > > Probably too late for this time, but in the past when I've needed to > redefine the type for a column, I've made a dump, edited the dump file to > change the type and then renamed the table and reloaded it. That's usually > several orders

Re: [GENERAL] alter column type

On Fri, Jun 5, 2015 at 12:23 PM, Casey Deccio wrote: > I have a database in which one table references the primary key of > another. The type of the primary key was initially int, but I changed it > to bigint. However, I forgot to update the type of a column that > references it. So, I've init

Re: [GENERAL] alter column type

Why is PG even re-writing all rows when the data type is being changed from smaller (int) to larger (bigint) type, which automatically means existing data is safe. Like, changing from varchar(30) to varchar(50) should involve no rewrite of existing rows. -- Sent via pgsql-general mailing list (p

Re: [GENERAL] alter column type

CAUTION: This is very dangerous and may cause corruption. *** DO THIS IN A TEST DATABASE FIRST *** --1. Get the oid for int8 (bigint) SELECT t.oid FROM pg_type t WHERE typname = 'int8'; --2. Get the oid for your table SELECT c.oid, c.relname as table, a.attname , a

[GENERAL] alter column type

I have a database in which one table references the primary key of another. The type of the primary key was initially int, but I changed it to bigint. However, I forgot to update the type of a column that references it. So, I've initiated "ALTER TABLE foo ALTER COLUMN bar TYPE bigint", where foo

Re: [GENERAL] ALTER column TYPE varying question

"Paolo Negri" <[EMAIL PROTECTED]> writes: > I need to increase the length of a string field using version 8.1 8.1.what? > After executing > ALTER TABLE mytable ALTER COLUMN mycolumn TYPE varchar(120) > I can see the column definition correctly changes and I can insert > rows with longer data in m

[GENERAL] ALTER column TYPE varying question

I need to increase the length of a string field using version 8.1 I was thinking to use ALTER TABLE since now altering a column type should be supported by pg. The column is currently varying(60) and I want to have it varying(120) After executing ALTER TABLE mytable ALTER COLUMN mycolumn TYPE va