Hatrack River Forum: Made Mistake with SQL table, need help

This is topic Made Mistake with SQL table, need help in forum Books, Films, Food and Culture at Hatrack River Forum.

To visit this topic, use this URL:
http://www.hatrack.com/ubb/main/ultimatebb.php?ubb=get_topic;f=2;t=053788

Posted by Blayne Bradley (Member # 8565) on :

Basically I have a table of user accounts, my problem is I forgot to put a column with ID numbers at the front, without having to redo everything is there an easy painless way of inserting a column at the front?

Posted by fugu13 (Member # 2859) on :

First, columns in SQL databases have no necessary order. They're usually implemented to appear in a consistent order, but that's for the user's convenience. There is no requirement.

So, you don't need to add a column at the 'front', just add a column.

Is there any data in the table, yet? That will affect the exact procedure you'll want to use. As will the RDBMS you are using. But basically, you're going to want to use the ADD COLUMN variant of ALTER TABLE.

Posted by Blayne Bradley (Member # 8565) on :

I'm using postgre, but eh, I wanted ID's at the front. Right now my table looks like this:

code:

    username    |            email             | password  | public_name |  location  |      homepage      |  interests  | icq_num | msn_messenger | occupation 
----------------+------------------------------+-----------+-------------+------------+--------------------+-------------+---------+---------------+------------
 Raenir         | raenir.tir@gmail.com         | eve       |             |            |                    |             |         |               | 
 Drizzt         | drizzt.tir@gmail.com         | 2         |             |            |                    |             |         |               | 
 Nebuchadrezzar | Nebuchadrezzar.tir@gmail.com | 3         |             |            |                    |             |         |               | 
 God            | God.tir@gmail.com            | 4         |             |            |                    |             |         |               | 
 Petey          | Petey.tir@gmail.com          | 5         |             |            |                    |             |         |               | 
 Blayne         | Blayne.tir@gmail.com         | 6         |             |            |                    |             |         |               | 
 Blayne1        | Blayne1.tir@gmail.com        | 0         |             |            |                    |             |         |               | 
 Ryuujin        | ryuujin.dragongod@gmail.com  | dragongod |             |            |                    |             |         |               | 
 Artemis        | artemis@fowl.com             | dragon    | Fowl        |            |                    |             |         |               | 
 Ashkore        | ashkore@stormrune.com        | 123456    | Ashkore     | Tagnik'Zur | www.tagnik-zur.com | Sword Saint | Weiqi   | n/a           | n/a
 Danomite       | danomite2k4@gmail.com        | 123456    | Daniel      | Quebec     | novasearch         | Student     | Games   |               | 
(11 rows)

Here she blows.

Posted by TomDavidson (Member # 124) on :

Couldn't you just specify the order of the columns in your SELECT statement?

Posted by Blayne Bradley (Member # 8565) on :

I like my tables aesthetically pleasing with minimal effort.

Posted by Bokonon (Member # 480) on :

Yes, yes he could.

EDIT: At this point, Blayne, you may just want to settle on "functional capable".

-Bok

Posted by scifibum (Member # 7625) on :

If you just HAVE to make this table over again with the Id column at the front, I think you should probably try the "Create table as" statement. In the example below I've assumed you want ID numbers generated automatically and you have already created a sequence called "serial" to generate the values:

create table new_tablename AS
select nextval('serial') AS ID, orig_table.*
from orig_table;
alter table new_tablename alter ID SET DEFAULT nextval('serial');

I don't think this will inherit any of the constraints on the original table so you'd have to add those in addition to running SQL like the above.

Posted by Blayne Bradley (Member # 8565) on :

so far my sequence is generated in my perl script.

Posted by Dagonee (Member # 5818) on :

quote:
so far my sequence is generated in my perl script.

*shudder* To do this, you either have to lock the table across db calls - with all the complications that entails - or will have duplicate sequences eventually. Assuming you add proper constraints, the duplicates won't make their way into your table. But you're still asking for trouble.

Postgres has a feature to make assigning sequences to PK fields effortless. Use the DB, young padawan. Don't fight it.

Posted by scifibum (Member # 7625) on :

I tend to write unnecessary code too.

Posted by HollowEarth (Member # 2586) on :

Uh with only eleven rows, just dump the table and recreate and insert the rows? You could do this by hand even.