On Thursday 27 September 2012 15:49:53 Craig James wrote:
> On Thu, Sep 27, 2012 at 2:23 AM, Gary Stainburn
>
> wrote:
> > Hi folks.
> >
> > I'm planning on extending a field in one of my main tables using:
> >
> > alter table stock alter column type varchar(255);
>
> Why not just do
>
>alter
On Thu, Sep 27, 2012 at 2:23 AM, Gary Stainburn
wrote:
> Hi folks.
>
> I'm planning on extending a field in one of my main tables using:
>
> alter table stock alter column type varchar(255);
Why not just do
alter table stock alter column type text;
That is, do you really need a limit? The
Hi folks.
I'm planning on extending a field in one of my main tables using:
alter table stock alter column type varchar(255);
to extend it from the current varchar(80). My concern is that this table and
this field is used in a number of views, and views of views.
Are then any gotchas that I n
Thanks for your answer!
In this spacial case your suggestion is a solution, but I am searching
for an opportunity to make every change (e.g. alter table) without
effecting availability of the database and the modified table when using
wal shipping (maybe something like concurrently when creating an
Thanks for your answer!
In this spacial case your suggestion is a solution, but I am searching
for an opportunity to make every change (e.g. alter table) without
effecting availability of the database and the modified table when using
wal shipping (maybe something like concurrently when creating an
Andreas Berger <4postg...@gmail.com> writes:
> i'm searching for a solution for changing the type of a column, e.g. from
> varchar(128) to varchar(512), without an ACCESS EXCLUSIVE lock.
That specific case (increasing the max length of a varchar column) could
be handled by hacking the pg_attribute
Hi,
i'm searching for a solution for changing the type of a column, e.g. from
varchar(128) to varchar(512), without an ACCESS EXCLUSIVE lock. The issue is
that the relation is nearly 100 million rows big and in our environment
every second of downtime is very bad.
In my current setup a slave datab
Hi,
i'm searching for a solution for changing the type of a column, e.g. from
varchar(128) to varchar(512), without an ACCESS EXCLUSIVE lock. The issue is
that the relation is nearly 100 million rows big and in our environment
every second of downtime is very bad.
In my current setup a slave datab
s to upgrade us to 8.3.10. Thanks both of you for all your
help.
Sam
-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com]
Sent: Thursday, 22 April 2010 4:15 PM
To: Samuel Stearns
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] ALTER TABLE ADD COLUMN Hangs
On Wed,
On Wed, Apr 21, 2010 at 8:17 PM, Samuel Stearns
wrote:
> Howdy,
>
> I’m running in an 8.3.3 environment. What could cause, or how can I
> diagnose, why an ‘ALTER TABLE ADD COLUMN’ statement runs for hours
> eventually locking users out and requiring to be killed in the end?
And not that it matt
On Wed, Apr 21, 2010 at 8:17 PM, Samuel Stearns
wrote:
> Howdy,
>
> I’m running in an 8.3.3 environment. What could cause, or how can I
> diagnose, why an ‘ALTER TABLE ADD COLUMN’ statement runs for hours
> eventually locking users out and requiring to be killed in the end?
By any chance you go
On Thu, Apr 22, 2010 at 11:47:34AM +0930, Samuel Stearns wrote:
> Howdy,
>
> I'm running in an 8.3.3 environment. What could cause, or how can I
> diagnose, why an 'ALTER TABLE ADD COLUMN' statement runs for hours
> eventually locking users out and requiring to be killed in the end?
>
ALTER
Howdy,
I'm running in an 8.3.3 environment. What could cause, or how can I diagnose,
why an 'ALTER TABLE ADD COLUMN' statement runs for hours eventually
locking users out and requiring to be killed in the end?
Thanks,
Sam
On Tue, 28 Apr 2009 15:40:22 +0530 Dhaval Rami wrote:
> No other process/query is accessing this table until rotation is done.
Are you sure? Not even VACUUM?
Look into the pg_locks system table to find out, if another connection
is accessing the table.
Kind regards
--
Hi,I am working on a data warehousing project, our system is handling
huge amount of live data.
We get data in form of events.
Frequency of events goes up to 1000 events/second. average frequency is 200
events/second.
We are using libpg to connect pg-server and insert each event into database
t
"Erik Aronesty" <[EMAIL PROTECTED]> writes:
> alter table x alter a set default 999;
> still doesn't help.
Doesn't help what?
If you mean that the sequence object is still there, you can drop that too.
regression=# create table x(a serial not null);
NOTICE: CREATE TABLE will create implicit se
CREATE TABLE x (
a serial NOT NULL
);
alter table x alter a drop default;
alter table x alter a set default 999;
still doesn't help.
i supposed i can dump the whole table and rebuild it but that's
going to be a bit slow.
---(end of broadcast)-
r 08, 2007 11:14 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] alter table serial->int
> "Erik Aronesty" <[EMAIL PROTECTED]> writes:
> > for some odd reason when i try to change a table from serial to just
> > plain "int with a default" postgres seem
Erik Aronesty wrote:
> -- Simpler example for you
>
> CREATE TABLE x (
>id serial NOT NULL
> );
>
> ALTER TABLE x alter id type int;
>
> -- Under 8.1.10, a call to pg_dump will reveal the change has not happened
Do an ALTER TABLE DROP DEFAULT instead. SERIAL is a macro for "int with
a defa
> "Erik Aronesty" <[EMAIL PROTECTED]> writes:
> > for some odd reason when i try to change a table from serial to just
> > plain "int with a default" postgres seems to ignore me.
>
> What PG version?
(PostgreSQL) 8.1.10
> The fact that you even tried that suggests that you don't understand
> very
-- Simpler example for you
CREATE TABLE x (
id serial NOT NULL
);
ALTER TABLE x alter id type int;
-- Under 8.1.10, a call to pg_dump will reveal the change has not happened
On Nov 8, 2007 11:13 AM, Erik Aronesty <[EMAIL PROTECTED]> wrote:
> n Nov 7, 2007 12:11 AM, Tom Lane <[EMAIL PROTECTE
"Erik Aronesty" <[EMAIL PROTECTED]> writes:
> for some odd reason when i try to change a table fromserial to just
> plain "int with a default" postgres seems to ignore me.
What PG version?
I'd expect this to work somewhat sanely in 8.2, but in earlier versions
fooling with the default expression
for some odd reason when i try to change a table fromserial to just
plain "int with a default" postgres seems to ignore me.
when i dump the schema i get ...
CREATE TABLE custom (
id serial NOT NULL,
name text,
email text,
);
then i run...
alter table custom alter id type int;
alter
Hi,
Anybody knows how to add column with reference to BEFORE or AFTER any
given column? Let say here's my table structure:
Column | Type| Modifiers
--+---+---
surname | character varying |
lastname | character varying |
address | character
=?ISO-8859-1?Q?Jos=E9_Roberto_Motta_Garcia?= <[EMAIL PROTECTED]> writes:
> ALTER TABLE synop
> ADD CONSTRAINT estacoes_fk
> FOREIGN KEY (id_estacao)
> REFERENCES estacoes
> Then I check the DDL and it is like this:
>
> CONSTRAINT estacoes_fk FOREIGN KEY (id_estacao, id_estacao) REFERENCES
> e
Hi all,
I do it in table synop:
ALTER TABLE synop
ADD CONSTRAINT estacoes_fk
FOREIGN KEY (id_estacao)
REFERENCES estacoes
Then I check the DDL and it is like this:
CONSTRAINT estacoes_fk FOREIGN KEY (id_estacao, id_estacao) REFERENCES
estacoes (id_estacao, id_estacao)
- Why id_estac
After I sent my request I found the notes related to ALTER TABLE on page
1,331 of the 8.1 manual, so that helped to explain it. Your reply to my
post confirmed what I thought I was reading.
Thanks for the reply,
Keaton
On Tue, 2007-05-29 at 17:16 -0400, Tom Lane wrote:
> Keaton Adams <[EMAIL
Keaton Adams <[EMAIL PROTECTED]> writes:
> In this example the created and msg_datetime columns are currently
> defined as TIMESTAMP WITHOUT TIME ZONE and no default. If the alter
> table for these changes is not in-place then I would want to run it as a
> single alter with all of the changes, suc
When I issue an alter table . alter column command, the table
contents are copied from the old structure to the new, correct? So I
need 2x the table space available so the old and new tables can exist at
the same time? PostgreSQL doesn't use an in-place alter-table for these
types of changes,
I have had to bump the stats on a partitioned table in order to get the
planner to use an index over a seqscan. This has worked well in making
the system perform where it needs to as it reduced one query's execution
from > 45 seconds to < 1 second.
The one problem I have run into is that when
I have a table called type:
type
---
type_id integer
tvalue smallint
...
..
..
tvalue was a second key on this table which now turns out to be unnecessary.
Before I can remove it I need to change the columns which reference it. I
wanted to do this with
alter table alter column using
But
Not that happiest of news, but very helpful to actually know it.
Thanks
On Aug 1, 2005, at 1:12 PM, Jaime Casanova wrote:
Secondly does anyone know a way to insert a column instead of
appending a column to a table. That way I could just avoid the
entire mess.
http://archives.pos
Thanks for the suggestion. Dump/Reload the DB I am really not
going to be able to do. Also I am going to be doing this to several
copies of similar databases so vi is out, but that is nothing that a
nice script can't overcome. I think that the down time would just be
to much. Also b
>
> Secondly does anyone know a way to insert a column instead of
> appending a column to a table. That way I could just avoid the
> entire mess.
>
http://archives.postgresql.org/pgsql-hackers/2004-09/msg00102.php
--
Regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
Robert,
If you have the luxury of taking the production db offline for a few minutes
or if you're just making changes in a test environment, this process works:
given:
create table test1 (
id integer,
txt1text,
txt2text
);
execute at command line:
pg_dump [dbname]> bac
Perhaps I am just just a bit anal on this but some columns I
really like to have as the last columns of a table. (usually
last_modby, last_modtime, type fields) Thus when I need to add a
column to a table I am not happy just adding the column. Instead I
go a little insane and rename
Hi
I am quite new to PostgreSQL.
Could someone please help or gide me in the right direction.
My problem is that I have a table with about 15 billion entries. I need
to auto increment the table for the start and then every entry after
that.
I have create the field "num" that has the auto
Hi
I am quite new to PostgreSQL.
Could someone please help or gide me in the right direction.
My problem is that I have a table with about 15 billion entries. I need
to auto increment the table for the start and then every entry after
that.
I have create the field "num" that has the auto i
Rajesh Kumar Mallah wrote:
Greetings!
It takes ages to drop a constraint from one of my tables
[ table details at the end ] I cannot insert into it also.
I know pg_dump is not running and no other query is accessing the table.
Can anyone help me debugging this problem? Can anyone explain the
foll
Thanks very much,
Unfortunately i restarted the postmaster
as we had to move forward. Since then i have not faced this problem.
I shall post the results next time i face this kind of problem.
Regds
mallah.
> Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes:
>> It takes ages to drop a constraint
Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes:
> It takes ages to drop a constraint from one of my tables
> [ table details at the end ] I cannot insert into it also.
DROP CONSTRAINT in itself isn't going to take any significant amount of
time. The only plausible explanation is that some other s
Greetings!
It takes ages to drop a constraint from one of my tables
[ table details at the end ] I cannot insert into it also.
I know pg_dump is not running and no other query is accessing the
table.
Can anyone help me debugging this problem? Can anyone explain the
following entires in pg_
* Martins Zarins <[EMAIL PROTECTED]> [05.06.2003 10:18]:
> Hello!
> Can someone explain me, why last ALTER fails?
>
> work=# \d rdu
> Table "rdu"
>Column|Type | Modifiers
>
>
> work=# ALTER TABLE rdu ADD CONSTRAINT rdu_finish_time CHECK ((start_time <=
> finish_time) OR finish_time IS NULL);
> ERROR: AlterTableAddConstraint: rejected due to CHECK constraint
> rdu_finish_time
I can say that the field, finish_time have the values which conflict with
the check constrai
Hello!
Can someone explain me, why last ALTER fails?
work=# \d rdu
Table "rdu"
Column|Type | Modifiers
-+-+-
Hi all,
I've experienced very slow performance to add foreign key constraints using
ALTER TABLE ADD CONSTRAINT FOREIGN KEY ...
After using COPY ... FROM to load the base tables, I started to build the
referential integrity between tables.
I have 3 tables: T1 (6 million records), T2 (1.5 million
Try this:
1) Add a new column of type DECIMAL(5,2)
2) update the table setting the new column with the value in the old
column.
3) rename the old column to something not used
4) rename the new colum to the name of the old column
5) wait for a later version where you can drop column, or just leave
As we transition our database, I'm finding the need to change columns
types. I'm also finding this to be a major PITA.
I have a column that's a something like:
create table stuff
(
...
rate varchar(7),
...
);
However that doesn't allow math-like functions (e.g. avg), so it's gotta
be chang
This might be helpful..
--- Begin Message ---
> Can someone clue me in as to proper syntax for adding a not null
> check constraint? I've fumbled around a bit without much luck
> and I don't see this covered in the alter table section of the
> interactive docs.
>
> My pseudo code is:
> ALTER T
David Stanaway <[EMAIL PROTECTED]> wrote:
> In this case the row is ID (Case sensitive) so whenever
> you refer to it you must refer to it as "ID"
> ...
>
> > proba=# alter table tabla1 drop constraint ID not null;
>
> Here you refer to it as ID which is case folded to id and
> does not refer
On Tue, 2002-05-14 at 11:24, MG wrote:
>
> On 03-May-2002 Nick Fankhauser wrote:
> > alter table drop constraint ID unique;
> > alter table drop constraint ID not null;
> >
> > alter table add constraint ID references table2 ;
> >
>
> Hi!
>
> It not works. :(
> I try it:
> proba=# create t
On 03-May-2002 Nick Fankhauser wrote:
> alter table drop constraint ID unique;
> alter table drop constraint ID not null;
>
> alter table add constraint ID references table2 ;
>
Hi!
It not works. :(
I try it:
proba=# create table "tabla1" ("ID" int8 NOT NULL UNIQUE, "nev" varchar(20) );
NO
On 03-May-2002 Nick Fankhauser wrote:
> alter table drop constraint ID unique;
> alter table drop constraint ID not null;
>
> alter table add constraint ID references table2 ;
>
Hi!
It not works... :(
How can I change the properties of a field?
Bye!
-
Linux RedHat 7.1
/www.ontko.com/
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of MG
> Sent: Thursday, May 02, 2002 3:28 PM
> To: PostgreSQL List
> Subject: [ADMIN] ALTER TABLE for field modify...
>
>
>
> Hi!
>
> How can I modify a
Hi!
How can I modify a field of table with ALTER TABLE? I want to modify the
constraint. The TABLE is now:
"ID"int8NOT NULL, UNIQUE
But I want this:
"ID" int8 REFERENCE "table2"
THanx!
Bye!
-
Linux RedHat 7.1
-
---(end of
On Thu, 2 May 2002, Mike Baker wrote:
>
> > > Hi.
> > >
> > > I am in the process of doing some data migration.
> > I
> > > have had no trouble moving my data, but am having
> > > problems creating a foreign key.
> > >
> > > When I run the following command, psql hangs
> > (well,
> > > not totall
> > Hi.
> >
> > I am in the process of doing some data migration.
> I
> > have had no trouble moving my data, but am having
> > problems creating a foreign key.
> >
> > When I run the following command, psql hangs
> (well,
> > not totally sure if it is hung).
>
> It probably isn't hung, but it'
On Mon, 29 Apr 2002, Mike Baker wrote:
> Hi.
>
> I am in the process of doing some data migration. I
> have had no trouble moving my data, but am having
> problems creating a foreign key.
>
> When I run the following command, psql hangs (well,
> not totally sure if it is hung).
It probably isn'
Hi.
I am in the process of doing some data migration. I
have had no trouble moving my data, but am having
problems creating a foreign key.
When I run the following command, psql hangs (well,
not totally sure if it is hung).
alter table t_release_component
add constraint t_release_component_
Brian McCane <[EMAIL PROTECTED]> writes:
> Okay, so if I create a function as 'iscachable', it assumes that the value
> will never change, calls the function and places the value in as the
> default? This doesn't make sense to me since the 'iscacheable' only works
> inside of a transaction block.
Brian McCane <[EMAIL PROTECTED]> writes:
> This doesn't make sense to me since the 'iscacheable' only works
> inside of a transaction block.
Nope, wrong definition.
This has been refined for 7.3, but in existing releases "iscachable"
really means "result never changes, *ever*".
Okay, so if I create a function as 'iscachable', it assumes that the value
will never change, calls the function and places the value in as the
default? This doesn't make sense to me since the 'iscacheable' only works
inside of a transaction block. If I call my function a hundred times, I
get a
Brian McCane <[EMAIL PROTECTED]> writes:
> Been there, done that :). It put the current epoch value in as the
> default value for updated.
That would suggest that you marked since_epoch() as cachable. Wrong
thing to do, if you want it evaluated again every time the default
is used.
Been there, done that :). It put the current epoch value in as the
default value for updated.
- brian
On Fri, 12 Apr 2002, Tom Lane wrote:
>
> Brian McCane <[EMAIL PROTECTED]> writes:
> > alter table foo alter column updated set default 'since_epoch()' ;
> > this gets the error:
> > ERROR: p
Brian McCane <[EMAIL PROTECTED]> writes:
> alter table foo alter column updated set default 'since_epoch()' ;
> this gets the error:
> ERROR: pg_atoi: error in "since_epoch()": can't parse "since_epoch()"
Try it without the quotes.
regards, tom lane
Okay, I can't wait until morning for an answer, so I made one up ;-). I
already have a function on this particular table which does processing
'BEFORE INSERT'. As a temporary hack (or maybe permanent depending on
anyones response), I have added code to see if 'updated' IS NULL and
setting it to
I have a field called 'updated' of type 'integer' in a table. I have
created a function called 'since_epoch' which is declared with iscacheable
and returns the value of 'extract(epoch from now())'. I want to make
'updated' have a default of 'since_epoch()'. I cannot get it to work with
the fo
Hi all,
I am new to PostgreSQL
how can i give the following satement in PostgreSQL
ALTER TABLE tblDayTransaction WITH NOCHECK ADD
UNIQUE NON
(
empid,
ppid,
transdate
)
regards,
sreedhar
---(end of broadcast)---
TIP 1: subscribe and
In article <9rc24d$170k$[EMAIL PROTECTED]>, "Jeff Boes" <[EMAIL PROTECTED]>
wrote:
> Does anyone have a master list of ALTER TABLE workarounds? That is,
> ways to manipulate the various pg_* tables to emulate 'missing' ALTER
> TABLE commands like
>
> ALTER TABLE DROP CONSTRAINT
>
> ALTER TABLE
Does anyone have a master list of ALTER TABLE workarounds? That is, ways
to manipulate the various pg_* tables to emulate 'missing' ALTER TABLE
commands like
ALTER TABLE DROP CONSTRAINT
ALTER TABLE DROP COLUMN
etc.
--
Jeff Boes vox 616.226.9550
Da
On Tue, 16 Oct 2001, Tariq Muhammad wrote:
> I am trying to set value of an attribute to not null but it does not work
> I am using the following statment:
>
> UPDATE contact
> SET attnotnull = TRUE
> WHERE attname = '_rserv_ts' ;
>
> The error I get is as under :
>
> ERROR: Attribute 'attn
Hello
I am trying to set value of an attribute to not null but it does not work
I am using the following statment:
UPDATE contact
SET attnotnull = TRUE
WHERE attname = '_rserv_ts' ;
The error I get is as under :
ERROR: Attribute 'attname' not found
I am running postgressql version is 7.1
72 matches
Mail list logo
