Thread: ALTERING A TABLE
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/
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-
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
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
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
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
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
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
--snip-- Or is that CAST it on the wasy. Richard
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-
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.
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
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.
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
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.
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