Reorder Columns in Postgres

Sometimes you need to reorder columns in your database to align with other data models, to show relevant data first, to satisfy your O.C.D (✋ that’s me). If you’re using PostgreSQL for your relational database (really any SQL database) and you already have data in your database, here are steps you can take to reorder columns in a given table.

Step 1. Convert table to a temp table

Because we want to maintain our table names and preserve data we will first convert our table to a temporary table, this will allow us to create a new table with our existing table name.

1

ALTER TABLE <table name> RENAME TO <temp table name>;

Example Use:

1

ALTER TABLE users RENAME TO temp_users;

Step 2. Create a “new” Table

We need to create a “new” table with the correct column ordering, etc. this is our opportunity to create the table with the structure we want.

1
2
3
4
5
6
7
8

CREATE TABLE users
(
  first_name text,
  last_name text
  created_at timestamp,
  updated_at timestamp
  ...
)

Step 3. Copy data from temp table into “new” table

With our “new” table with the correctly ordered columns we need to copy data from the temp table (our original table).

1

INSERT INTO users (first_name, last_name, created_at, updated_at, ...) SELECT first_name, last_name, created_at, updated_at FROM temp_users;