How do I alter the position of a column in a PostgreSQL database table?

by

“Alter column position” in the PostgreSQL Wiki says:

PostgreSQL currently defines column
order based on the attnum column of
the pg_attribute table. The only way
to change column order is either by
recreating the table, or by adding
columns and rotating data until you
reach the desired layout.

That’s pretty weak, but in their defense, in standard SQL, there is no solution for repositioning a column either. Database brands that support changing the ordinal position of a column are defining an extension to SQL syntax.

One other idea occurs to me: you can define a VIEW that specifies the order of columns how you like it, without changing the physical position of the column in the base table.

More Related Contents:

  1. Refactor a PL/pgSQL function to return the output of various SELECT queries
  2. How to check if a table exists in a given schema
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. Optimise PostgreSQL for fast testing
  5. Transpose latest rows per user to columns
  6. How to delete duplicate rows without unique identifier
  7. Best way to check for “empty or null value”
  8. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  9. PostgreSQL parameterized Order By / Limit in table function
  10. How can I send email from PostgreSQL trigger?
  11. SQL Between clause with strings columns
  12. Insert an image in postgresql database
  13. NOT NULL constraint over a set of columns
  14. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  15. Query that ignore the spaces
  16. How to find a table having a specific column in postgresql
  17. Continuing a transaction after primary key violation error
  18. INSERT COMMAND :: ERROR: column “value” does not exist
  19. Use email address as primary key?
  20. Bulk/batch update/upsert in PostgreSQL
  21. SQL query to find record with ID not in another table
  22. How to show row numbers in PostgreSQL query?
  23. Postgres Error: More than one row returned by a subquery used as an expression
  24. Grant privileges for a particular database in PostgreSQL
  25. How can I add a column that doesn’t allow nulls in a Postgresql database?
  26. Upsert with a transaction
  27. PostgreSQL how to create a copy of a database or schema?
  28. Temporal database design, with a twist (live vs draft rows)
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Postgres: convert single row to multiple rows (unpivot)

Leave a Comment