Re: [GENERAL] ALTER COLUMN, trading a DOMAIN for its base datatype

C GG writes: > ... Is PostgreSQL smart enough to not have to rewrite the table and simply > shed the domain for the underlying datatype? Yes, in recent versions ... don't remember how far back exactly. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-gen

Re: [GENERAL] Alter column from text[] to uuid[]

On Thu, Jun 11, 2015 at 12:57 PM Tom Lane wrote: > alter table t alter u type uuid[] using u::uuid[]; > > The original command worked without a USING because anything-to-text is > considered an allowable assignment coercion; but the other way around > requires an explicit cast. Got it. Thanks! I

Re: [GENERAL] Alter column from text[] to uuid[]

Keith Rarick writes: > I recently did the following: > kr=# alter table t alter u type text[]; > ALTER TABLE > Time: 5.513 ms > Now I'd like to put it back the way it was, but my attempts didn't work: > kr=# alter table t alter u type uuid[]; > ERROR: column "u" cannot be cast automatically to

Re: [GENERAL] Alter column from text[] to uuid[]

On 06/11/2015 11:33 AM, Keith Rarick wrote: I have a table: kr=# create table t (u uuid[]); CREATE TABLE Time: 3.742 ms kr=# insert into t values ('{"0289b709-3cd7-431c-bcbe-f942eb31b4c5","86cc14d6-7293-488e-a85f-384ae6773d28"}'); INSERT 0 1 Time: 1.735 ms I recently did the following: kr=# al

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

Re: [GENERAL] alter column to varchar without view drop/re-creation

Hello Adrian, test=# UPDATE pg_attribute SET atttypmod = -1 WHERE attrelid = 'base_tbl'::regclass AND attname = 'vc_fld'; test=# UPDATE pg_attribute SET atttypmod = -1 WHERE attrelid = 'v_test'::regclass AND attname = 'vc_fld'; *This is exactly what I plan to do*. So, according to the test

Re: [GENERAL] alter column to varchar without view drop/re-creation

On 08/29/2014 02:29 PM, Emi Lu wrote: Hello, On 08/29/2014 03:16 PM, Adrian Klaver wrote: May I know is there a way to "alter column type to varchar" (previous is varchar(***)) without view drop/re-creation? Basically, looking for a way to change column without have to drop/re-create dependent

Re: [GENERAL] alter column to varchar without view drop/re-creation

Hello, On 08/29/2014 03:16 PM, Adrian Klaver wrote: May I know is there a way to "alter column type to varchar" (previous is varchar(***)) without view drop/re-creation? Basically, looki

Re: [GENERAL] alter column to varchar without view drop/re-creation

On 08/29/2014 12:09 PM, Emi Lu wrote: Hello list, May I know is there a way to "alter column type to varchar" (previous is varchar(***)) without view drop/re-creation? Basically, looking for a way to change column without have to drop/re-create dependent views. varchar(***) to varchar and no

Re: [GENERAL] alter column to varchar without view drop/re-creation

Hello list, May I know is there a way to "alter column type to varchar" (previous is varchar(***)) without view drop/re-creation? Basically, looking for a way to change column without have to drop/re-create dependent views.

Re: [GENERAL] Alter column with views depended on it without drop views

On Wed, Mar 19, 2014 at 11:27 PM, Emi Lu wrote: > Is there a way to change a table column from varchar(n) to varchar with > views depended on it. Nope. You cannot update the data type of a table column if it is used by a view. =# create table aa (a varchar(4)); CREATE TABLE =# create view aav as s

Re: [GENERAL] Alter Column from inet to inet[]

Alex - writes: > Hi,I need to change a column type from inet to inet[] but the alter command > always gives me the following errors > ERROR: column "access_ip" cannot be cast to type inet[] > ALTER TABLE users ALTER COLUMN access_ip SET DATA TYPE inet[] USING > access_ip::inet[]; The problem i

Re: [GENERAL] Alter column...using failure under 9.0.4

=?iso-8859-1?Q?Bj=F6rn_H=E4user?= writes: > Am 25.09.2011 um 17:17 schrieb Edson Carlos Ericksson Richter: >> alter table usuario alter column ativo type smallint using (case when ativo >> then 1 else 0 end); >> ERROR: argument of IS FALSE must be type boolean, not type smallint > you could che

Re: [GENERAL] Alter column...using failure under 9.0.4

Hello, you could check for indices or something like that. Björn Am 25.09.2011 um 17:17 schrieb Edson Carlos Ericksson Richter: > Dear list, > > I’ve a strange issue here. I’ve a table that I’m sure the column type is > boolean. I can see the datatype is boolean on PgAdmin. > > Nevertheles

Re: [GENERAL] alter column appears to work, but doesn't?

Phghght. Sorry, no, that didn't do it, I was typing too fast and skipped updating the attributes table. That was definitely not the case w/ my original database. Wasn't working. The table definition reported the update I made. Insert did not work. Dropping rules, restarting database, and recr

Re: [GENERAL] alter column appears to work, but doesn't?

On Monday, September 05, 2011 1:48:58 pm Ron Peterson wrote: > 2011-09-05_16:14:00-0400 Tom Lane : > > Ron Peterson writes: > > > I just dropped my logging rules, stopped the database and restarted it, > > > put my rules back in place, and now it works. Not sure why. Cached > > > query plan? > >

Re: [GENERAL] alter column appears to work, but doesn't?

2011-09-05_16:14:00-0400 Tom Lane : > Ron Peterson writes: > > I just dropped my logging rules, stopped the database and restarted it, > > put my rules back in place, and now it works. Not sure why. Cached > > query plan? > > Maybe. We'd need a reproducible test case to do more than speculate

Re: [GENERAL] alter column appears to work, but doesn't?

Ron Peterson writes: > I just dropped my logging rules, stopped the database and restarted it, > put my rules back in place, and now it works. Not sure why. Cached > query plan? Maybe. We'd need a reproducible test case to do more than speculate though. regards, tom la

Re: [GENERAL] alter column appears to work, but doesn't?

2011-09-05_15:03:00-0400 Tom Lane : > Ron Peterson writes: > > I just updated a table to have a larger column size as follows. > > > alter table attributes_log alter column attribute_name type varchar(48); > > How come this refers to "attributes_log" while your failing command is > an insert int

Re: [GENERAL] alter column appears to work, but doesn't?

Ron Peterson writes: > I just updated a table to have a larger column size as follows. > alter table attributes_log alter column attribute_name type varchar(48); How come this refers to "attributes_log" while your failing command is an insert into "attributes"? regards,

Re: [GENERAL] alter column to inet get error.

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 A32

Re: [GENERAL] alter column to inet get error.

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.

Re: [GENERAL] alter column to inet get error.

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

Re: [GENERAL] alter column to inet get error.

On Fri, Sep 3, 2010 at 7:21 AM, Steve Clark 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. > >                Ta

Re: [GENERAL] Alter column position

On Sat, May 22, 2010 at 11:01 AM, Patrick Rutkowski wrote: > Though I do see how the performance bumps could be useful, I would still bet > that the huge majority of users who utters the words "damn, I wish I could > re-order columns" are just talking about the visual order in psql(1) or > PgAd

Re: [GENERAL] Alter column position

Hmm, interesting. Though I do see how the performance bumps could be useful, I would still bet that the huge majority of users who utters the words "damn, I wish I could re-order columns" are just talking about the visual order in psql(1) or PgAdminIII. On May 22, 2010, at 10:49 AM, David Fett

Re: [GENERAL] Alter column position

On Sat, May 22, 2010 at 09:34:50AM -0400, Patrick Rutkowski wrote: > I'm curious, is there any latest word on this? > > (Note that I've read fully the link > http://wiki.postgresql.org/wiki/Alter_column_position as well as all > links stemming from it). No one's working on it, to my knowledge. W

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

Re: [GENERAL] alter column datatype with cast

On Thu, Dec 29, 2005 at 09:46:10PM +0100, Klein Balzs wrote: > I had to change the datatype of a column from text to integer. The column > contained integers (obviously stored as text). > > When I tried to change the datatype of the column I got an error message > saying that the column can not be

Re: [GENERAL] ALTER COLUMN

Peter Eisentraut <[EMAIL PROTECTED]> writes: > Fran Fabrizio writes: >> What's the best way to alter a column definition after the fact (i.e. >> int8-->int4). Is the answer dump, drop table, make new table with new >> definition and same name, import data? > Yes. Dump and reload can be avoided

RE: [GENERAL] Alter column length

I've used that method without any problems. I had to experiment a bit by creating test tables with different length columns to see what to set atttypmod to, because I didn't really know what the value meant! Seemed to work ok though... Tamsin > > - > update pg_attrib

Re: [GENERAL] Alter column length

"Dan Wilson" <[EMAIL PROTECTED]> writes: > I want to alter the length of a column without dumping an re-creating the > table. What kind of column? Offhand I think that hacking atttypmod would be safe for varchar(n) but not char(n). BTW, you might need to start a new backend session to see the e