Thread: ALTERING A TABLE

ALTERING A TABLE

From
Peter Landis
Date:
Hi-
    I'm a newbie at postgresql and ran into a
situation on trying to alter an element in a table.  I
have a table called company where a field is called
address.  The address field is set to char() 20 and I
want to alter the value to have an address field of
100.  What would be the command to alter the table to
change this field without affecting the data?  If
anyone could help I would greatly appreciate it.

Thanks,
Mr. Newbie


__________________________________________________
Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.
http://im.yahoo.com/

Re: ALTERING A TABLE

From
Ron Peterson
Date:
Peter Landis wrote:
>
> Hi-
>     I'm a newbie at postgresql and ran into a
> situation on trying to alter an element in a table.  I
> have a table called company where a field is called
> address.  The address field is set to char() 20 and I
> want to alter the value to have an address field of
> 100.  What would be the command to alter the table to
> change this field without affecting the data?  If
> anyone could help I would greatly appreciate it.

You can't use ALTER TABLE to change a field's data description.  You'll
have to make a new table.  Then use SELECT INTO to move your data.  Then
DROP TABLE oldtable.  Then ALTER TABLE tablename RENAME TO newname.

-Ron-

Re: ALTERING A TABLE

From
Steve Wampler
Date:
Ron Peterson wrote:
>
>
> You can't use ALTER TABLE to change a field's data description.  You'll
> have to make a new table.  Then use SELECT INTO to move your data.  Then
> DROP TABLE oldtable.  Then ALTER TABLE tablename RENAME TO newname.

Would this really work?  According to the docs, SELECT INTO creates a
new table (which must not yet exist).  So this new table
would have the same field data descriptions as the original, right?

Is the documentation wrong?
--
Steve Wampler-  SOLIS Project, National Solar Observatory
swampler@noao.edu

Re: ALTERING A TABLE

From
Ed Loehr
Date:
Steve Wampler wrote:
>
> Ron Peterson wrote:
> >
> >
> > You can't use ALTER TABLE to change a field's data description.  You'll
> > have to make a new table.  Then use SELECT INTO to move your data.  Then
> > DROP TABLE oldtable.  Then ALTER TABLE tablename RENAME TO newname.
>
> Would this really work?  According to the docs, SELECT INTO creates a
> new table (which must not yet exist).  So this new table
> would have the same field data descriptions as the original, right?
>
> Is the documentation wrong?

No, the doc is at least right that a new table is created.  Not sure what
it does if the table already exists.

I do this task by the following sequence (psuedo-sql here):

    select into temp_mytable * from mytable;
    drop mytable;
    create table mytable (...new defn...);
    insert into mytable (...)
        select ... from temp_mytable

And that works pretty well.  Don't forget you'll have to drop/reload all
dependent functions/triggers.  And if you're using a SERIAL column, don't
mistakenly nuke your sequence object (mytable_id_seq) if you're using one
as a primary key generator, otherwise you'll reset the sequence and get
massive confusion.

Regards,
Ed Loehr

Re: ALTERING A TABLE

From
Mike Mascari
Date:

Steve Wampler wrote:
>
> Ron Peterson wrote:
> >
> >
> > You can't use ALTER TABLE to change a field's data description.  You'll
> > have to make a new table.  Then use SELECT INTO to move your data.  Then
> > DROP TABLE oldtable.  Then ALTER TABLE tablename RENAME TO newname.
>
> Would this really work?  According to the docs, SELECT INTO creates a
> new table (which must not yet exist).  So this new table
> would have the same field data descriptions as the original, right?
>
> Is the documentation wrong?

You are correct. I use INSERT INTO <tablename> SELECT ...

Hope that helps,

Mike Mascari

Re: ALTERING A TABLE

From
"Ross J. Reedstrom"
Date:
On Thu, Jun 01, 2000 at 11:00:09AM -0700, Steve Wampler wrote:
> Ron Peterson wrote:
> >
> >
> > You can't use ALTER TABLE to change a field's data description.  You'll
> > have to make a new table.  Then use SELECT INTO to move your data.  Then
> > DROP TABLE oldtable.  Then ALTER TABLE tablename RENAME TO newname.
>
> Would this really work?  According to the docs, SELECT INTO creates a
> new table (which must not yet exist).  So this new table
> would have the same field data descriptions as the original, right?
>
> Is the documentation wrong?

Document's right, Ron mis-spoke (mis-typed?) That should be "INSERT INTO
... SELECT ... FROM"

The tricky part is doing the select in such a way that the data gets
converted on the way into the new table. This shouldn't be a problem
for text to text, but if you where changing the type of a column, you'd
need to cast it on the way.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

index problem

From
Marcin Inkielman
Date:
hi!


I created an index using pgaccess rescently. the name of the index was
long:

"oceny_stud_numer_albumu_protokoloceny_stud"

now i am unable to vacuum my database. i obtain something like this when
i try:

NOTICE:  Pages 310: Changed 0, reaped 2, Empty 0, New 0; Tup 48611: Vac 3,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 48, MaxLen
48; Re-using: Free/Avail. Space 3096/116; EndEmpty/Avail. Pages 0/1. CPU
0.02s/0.01u sec.
pqReadData() -- backend closed the channel unexpectedly.
    This probably means the backend terminated abnormally
    before or while processing the request.
connection to server was lost
vacuumdb: vacuum failed

pg_dump works, so i am able to backup my db and to restore it under
another name.

i tryed to delete my index:

nat=# drop index oceny_stud_numer_albumu_protokoloceny_stud;
NOTICE:  identifier "oceny_stud_numer_albumu_protokoloceny_stud" will be
truncated to "oceny_stud_numer_albumu_protoko"
ERROR:  index "oceny_stud_numer_albumu_protoko" nonexistent

then i did:

nat=# select * from pg_class where
relname='oceny_stud_numer_albumu_protoko';
 relname | reltype | relowner | relam | relpages | reltuples |
rellongrelid | relhasindex | relisshared | relkind | relnatts | relchecks
| reltriggers | relukeys | relfkeys | relrefs | relhaspkey | relhasrules |
relacl

---------+---------+----------+-------+----------+-----------+--------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+-------------+--------
(0 rows)

however:

nat=# select * from pg_class where
relname~'oceny_stud_numer_albumu_protoko';
               relname               | reltype | relowner | relam |
relpages | reltuples | rellongrelid | relhasindex | relisshared | relkind
| relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs |
relhaspkey | relhasrules | relacl

-------------------------------------+---------+----------+-------+----------+-----------+--------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+-------------+--------
 oceny_stud_numer_albumu_protokol_id | 6580575 |       32 |   403 |
122 |     48611 |            0 | f           | f           | i       |
2 |         0 |           0 |        0 |        0 |       0 | f          |
f           | (1 row)


then i tryed:

nat=# drop index oceny_stud_numer_albumu_protokol_id;
NOTICE:  identifier "oceny_stud_numer_albumu_protokol_id" will be
truncated to "oceny_stud_numer_albumu_protoko"
ERROR:  index "oceny_stud_numer_albumu_protoko" nonexistent


my question is:
~~~~~~~~~~~~~~~
how may i delete this index in my original database???


thanks for any help..

marcin inkielman


Re: index problem

From
Tom Lane
Date:
Marcin Inkielman <marn@wsisiz.edu.pl> writes:
> I created an index using pgaccess rescently. the name of the index was
> long:
> "oceny_stud_numer_albumu_protokoloceny_stud"
> now i am unable to vacuum my database.

Oh dear :-( ... it seems that when you quote an identifier, the system
forgets to make sure that it's truncated to no more than 31 characters.
You've got a corrupted pg_class entry now for that index.

> my question is:
> ~~~~~~~~~~~~~~~
> how may i delete this index in my original database???

Dropping the table that the index is on should work.  Hopefully
restoring just the one table is better than restoring your whole DB.

In the meantime, this is a high-priority bug fix...

            regards, tom lane

Re: ALTERING A TABLE

From
Richard Smith
Date:
--snip--

Or is that CAST it on the wasy.

Richard

Re: ALTERING A TABLE

From
Ron Peterson
Date:
Steve Wampler wrote:
>
> Ron Peterson wrote:
> >
> >
> > You can't use ALTER TABLE to change a field's data description.  You'll
> > have to make a new table.  Then use SELECT INTO to move your data.  Then
> > DROP TABLE oldtable.  Then ALTER TABLE tablename RENAME TO newname.
>
> Would this really work?  According to the docs, SELECT INTO creates a
> new table (which must not yet exist).  So this new table
> would have the same field data descriptions as the original, right?
>
> Is the documentation wrong?

Umm, no - I'm stupid.  Perhaps use COPY instead.  E.G. - COPY tablename
TO '/var/temp.data';  COPY newtable FROM '/var/temp.data';

Sorry.  I'll remember to try it myself first nextime.

-Ron-

Re: index problem

From
Lincoln Yeoh
Date:
At 06:13 PM 01-06-2000 -0400, Tom Lane wrote:
>Marcin Inkielman <marn@wsisiz.edu.pl> writes:
>> I created an index using pgaccess rescently. the name of the index was
>> long:
>> "oceny_stud_numer_albumu_protokoloceny_stud"
>> now i am unable to vacuum my database.
>
>Oh dear :-( ... it seems that when you quote an identifier, the system
>forgets to make sure that it's truncated to no more than 31 characters.
>You've got a corrupted pg_class entry now for that index.
>
>> my question is:
>> ~~~~~~~~~~~~~~~
>> how may i delete this index in my original database???
>
>Dropping the table that the index is on should work.  Hopefully
>restoring just the one table is better than restoring your whole DB.

If dropping the index is not possible, how about the good ol filesystem rm?

I used to use that to deal with drop table; rollback;

The table was there but not quite there ;). So I had to rm it.

How about hexediting the relevant files. I did that once to fix an
application which stored its code in an Oracle DB. The database wouldn't
allow us to access the vendor's code even though we knew where the bug was,
so I just stopped the database engine, backed up the 200MB database file,
hexedited it and fixed it :).

That's the sort of thing you sometimes have to do with closed source apps...

Cheerio,
Link.


Re: index problem

From
Marcin Inkielman
Date:
On Mon, 5 Jun 2000, Lincoln Yeoh wrote:

> Date: Mon, 05 Jun 2000 15:15:42 +0800
> From: Lincoln Yeoh <lylyeoh@mecomb.com>
> To: Tom Lane <tgl@sss.pgh.pa.us>, Marcin Inkielman <marn@wsisiz.edu.pl>
> Cc: postgres-general <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] index problem
>
> At 06:13 PM 01-06-2000 -0400, Tom Lane wrote:
> >Marcin Inkielman <marn@wsisiz.edu.pl> writes:
> >> I created an index using pgaccess rescently. the name of the index was
> >> long:
> >> "oceny_stud_numer_albumu_protokoloceny_stud"
> >> now i am unable to vacuum my database.
> >
> >Oh dear :-( ... it seems that when you quote an identifier, the system
> >forgets to make sure that it's truncated to no more than 31 characters.
> >You've got a corrupted pg_class entry now for that index.
> >
> >> my question is:
> >> ~~~~~~~~~~~~~~~
> >> how may i delete this index in my original database???
> >
> >Dropping the table that the index is on should work.  Hopefully
> >restoring just the one table is better than restoring your whole DB.
>
> If dropping the index is not possible, how about the good ol filesystem rm?
>
> I used to use that to deal with drop table; rollback;
>
> The table was there but not quite there ;). So I had to rm it.
>
> How about hexediting the relevant files. I did that once to fix an
> application which stored its code in an Oracle DB. The database wouldn't
> allow us to access the vendor's code even though we knew where the bug was,
> so I just stopped the database engine, backed up the 200MB database file,
> hexedited it and fixed it :).
>
> That's the sort of thing you sometimes have to do with closed source apps...
>
> Cheerio,
> Link.
>

I generaly avoid  to do things like this... i am not postgres code expert
and i think i risk to really corrupt my DB.

I solved my problem simplier:

drop index oceny_stud_numer_albumu_protokol_id;

and

drop index oceny_stud_numer_albumu_protokoloceny_stud;

failed....


so I used:

drop index "oceny_stud_numer_albumu_protokoloceny_stud";
and it worked for me 8-)))


--
mi


Re: index problem

From
Lincoln Yeoh
Date:
At 10:53 AM 05-06-2000 +0200, Marcin Inkielman wrote:
>On Mon, 5 Jun 2000, Lincoln Yeoh wrote:

>I solved my problem simplier:
>
>drop index oceny_stud_numer_albumu_protokol_id;
>
>and
>
>drop index oceny_stud_numer_albumu_protokoloceny_stud;
>
>failed....
>
>
>so I used:
>
>drop index "oceny_stud_numer_albumu_protokoloceny_stud";
>and it worked for me 8-)))

I wonder why it worked tho. How does Postgresql treat stuff between double
quotes, especially regard to string length limits?

Yes I know it's in the source code somewhere, but it's also good to know
the official/intended behaviour vs the actual behaviour.

Cheerio,

Link.


Re: index problem

From
Tom Lane
Date:
Lincoln Yeoh <lylyeoh@mecomb.com> writes:
> At 10:53 AM 05-06-2000 +0200, Marcin Inkielman wrote:
>> drop index oceny_stud_numer_albumu_protokoloceny_stud;
>> failed....
>> so I used:
>> drop index "oceny_stud_numer_albumu_protokoloceny_stud";
>> and it worked for me 8-)))

> I wonder why it worked tho. How does Postgresql treat stuff between double
> quotes, especially regard to string length limits?

Stuff between double quotes *should* be subject to the same
NAMEDATALEN-1 restriction as unquoted names.  Embarrassingly, 7.0's
lexer didn't enforce such a limit (it's fixed in 7.0.1 and later)
which meant that you could overrun the space allocated for names
in pg_class and other system tables, if you quoted the name.

Marcin's original create index command evidently managed to create
a pg_class entry with 32 non-null characters in the name field,
where it should have been only 31 and a null.  He couldn't delete
that entry with a drop index command using an unquoted name, because
the lexer would (correctly) truncate such a name to 31 chars.  But
evidently it worked to match against a quoted-and-not-truncated
name.  I'm pretty surprised that he didn't see coredumps instead.

If you want to trace through the code to discover exactly how it
managed to avoid crashing, go for it --- but it doesn't seem like
an especially pressing question from here.  To my mind the bug is
just that the lexer created an invalid internal name string to
begin with.  Internally, no name should ever exceed NAMEDATALEN-1.

            regards, tom lane

Re: index problem

From
Lincoln Yeoh
Date:
At 03:00 AM 07-06-2000 -0400, Tom Lane wrote:
>Lincoln Yeoh <lylyeoh@mecomb.com> writes:
>> At 10:53 AM 05-06-2000 +0200, Marcin Inkielman wrote:
>>> drop index oceny_stud_numer_albumu_protokoloceny_stud;
>>> failed....
>>> so I used:
>>> drop index "oceny_stud_numer_albumu_protokoloceny_stud";
>>> and it worked for me 8-)))
>
>> I wonder why it worked tho. How does Postgresql treat stuff between double
>> quotes, especially regard to string length limits?
>
>Stuff between double quotes *should* be subject to the same
>NAMEDATALEN-1 restriction as unquoted names.  Embarrassingly, 7.0's
>lexer didn't enforce such a limit (it's fixed in 7.0.1 and later)
>which meant that you could overrun the space allocated for names
>in pg_class and other system tables, if you quoted the name.
>
>evidently it worked to match against a quoted-and-not-truncated
>name.  I'm pretty surprised that he didn't see coredumps instead.
>
>If you want to trace through the code to discover exactly how it
>managed to avoid crashing, go for it --- but it doesn't seem like
>an especially pressing question from here.  To my mind the bug is

Well for some reason things like these tend to set alarm bells off in my
head with a blinking "security" sign :). But of course in most
environments, untrusted users don't get to define their own names (I recall
someone talking about a million table thing, hopefully those tables are
given internally defined names).

Cheerio,

Link.


Re: index problem

From
Tom Lane
Date:
Lincoln Yeoh <lylyeoh@mecomb.com> writes:
> At 03:00 AM 07-06-2000 -0400, Tom Lane wrote:
>> If you want to trace through the code to discover exactly how it
>> managed to avoid crashing, go for it --- but it doesn't seem like
>> an especially pressing question from here.  To my mind the bug is

> Well for some reason things like these tend to set alarm bells off in my
> head with a blinking "security" sign :).

Well, yeah, which is why I made Marc redo the already-built 7.0.1
tarballs in order to squeeze in the fix.  (That last-minute fire drill
might be the reason why 7.0.1 was messed up :-(.)  But as long as the
lexer does what it's supposed to do, there's no security issue.

            regards, tom lane