2

I have a table with lots of columns, and I'd like to add two more (date and time) to the front of the existing table.

There is no data in the table right now, but I'm wondering what the best way is get the table in the format I need it.

I could just drop the table and create a new one with the correct configuration, but I'm wondering if there is a better way?

johncorser
  • 9,262
  • 17
  • 57
  • 102
  • Why does it have to be to the front of the existing table? When you use a SELECT statement to pull the information you specify the order of fields then. – Linger Jul 29 '14 at 15:32
  • Two reasons: 1. I would like the select * behavior to show the most relevant columns first, and 2 I will be writing to the db with a script that organizes the information this way in a csv, and adjusting the script seems like a little more work than just adding a couple columns in front of the table. – johncorser Jul 29 '14 at 15:34
  • http://stackoverflow.com/questions/285733/how-do-i-alter-the-position-of-a-column-in-a-postgresql-database and http://dba.stackexchange.com/questions/3276/how-can-i-specify-the-position-for-a-new-column-in-postgresql – chresse Jul 29 '14 at 15:34

2 Answers2

1

This is currently not possible. You have to drop and recreate the table.
Theoretically you could add the column, drop and re-add all other columns, but that's hardly practical.

It's an ongoing discussion and an open TODO-item of the Postgres project to allow reordering of columns. But a lot of dependencies and related considerations make that hard.

Quoting the Postgres project's ToDo List:

Allow column display reordering by recording a display, storage, and permanent id for every column?

Contrary to what some believe, the order of columns in a table is not irrelevant, for multiple reasons.

  • The default order is used for statements like INSERT without column definition lists.
    Or SELECT *, which returns columns in the predefined order.
  • The composite type of the table uses the same order of columns.
  • The order of columns is relevant for storage optimization (padding and alignment matter). More:

People may be confusing this with the order of rows, which in undefined in a table.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Is there at least a way to get the schema for the table? like name, varchar(100) for each row so that I can reference that when rebuilding my giant table? – johncorser Jul 29 '14 at 15:42
  • @johncorser: There are many ways. `\d tbl` in psql. Or use the reverse engineered CREATE scripts provided by pgAdmin ... – Erwin Brandstetter Jul 29 '14 at 15:45
  • 2
    `SELECT *` is a bad practice which is not recommended. Same goes to insert without a list, or not using `order by` just because the incidental order whitout it suits your needs. An application should not fail because the order of columns in a table changed. – Tulains Córdova Jul 29 '14 at 16:04
  • `SELECT *` and `INSERT` without target list are bad practice in certain contexts. Doesn't make the order of columns irrelevant, though. That's just a false claim. – Erwin Brandstetter Jul 29 '14 at 16:15
  • I still don't get it why the order of columns is relevant if there is at least one column that has a variable length. Because then the "following" rows in a database block won't be aligned properly. Only the row that is physically located at the beginning of a block will benefit from the column order - until one of the columns gets longer (and stays below the TOAST limit) –  Jul 29 '14 at 17:06
  • @a_horse_with_no_name: If the [example in the linked answer](http://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql/7431468#7431468) doesn't do the trick for you, I suggest you ask a question, so we can address this properly. – Erwin Brandstetter Jul 29 '14 at 17:34
  • The example in the linked question only uses fixed length data types. I can somehow understand the alignment advantage there, but not if a row contains variable length columns. The alignment can't be working then. –  Jul 29 '14 at 19:19
  • @a_horse_with_no_name: If you have a column that requires alignment (like `int`) after a column that doesn't (like `text`) or has different alignment needs (like `int2`), Postgres adds **padding** up to the next multiple of `n` bytes (if needed), to the next possible start address, thereby wasting space. More related answers [here](http://dba.stackexchange.com/questions/42290/configuring-postgresql-for-read-performance/43142#43142) and [here](http://stackoverflow.com/questions/13570613/making-sense-of-postgres-row-sizes/13570853#13570853). This would make a very nice question ... – Erwin Brandstetter Jul 30 '14 at 01:13
0
  • In relational databases the order of columns in a table is irrelevant
  • Create a view that shows you the columns in the order you want
  • If you still want to, drop the table and recreate it
Tulains Córdova
  • 2,559
  • 2
  • 20
  • 33
  • @ErwinBrandstetter Not "order in tables" but "column order in tables" – Tulains Córdova Jul 29 '14 at 15:53
  • Well, I have to disagree. Column order is well-defined and matters in multiple ways. I wrote more in my answer. It's irrelevant in set theory or relational algebra, but not in the Postgres implementation (or any other RDBMS implementation). Not even in standard SQL. – Erwin Brandstetter Jul 29 '14 at 15:54