posted
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?
IP: Logged |
posted
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.
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
Blayne Bradley
unregistered
posted
I'm using postgre, but eh, I wanted ID's at the front. Right now my table looks like this:
posted
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.
Posts: 4287 | Registered: Mar 2005
| IP: Logged |
Blayne Bradley
unregistered
posted
so far my sequence is generated in my perl script.
IP: Logged |
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.
Posts: 26071 | Registered: Oct 2003
| IP: Logged |
posted
Uh with only eleven rows, just dump the table and recreate and insert the rows? You could do this by hand even.
Posts: 1621 | Registered: Oct 2001
| IP: Logged |
Printer-friendly view of this topic
