How to add a new Column in a table after the 2nd or 3rd column in the Table using postgres?

by

No, there’s no direct way to do that. And there’s a reason for it – every query should list all the fields it needs in whatever order (and format etc) it needs them, thus making the order of the columns in one table insignificant.

If you really need to do that I can think of one workaround:

  • dump and save the description of the table in question (using pg_dump --schema-only --table=<schema.table> ...)
  • add the column you want where you want it in the saved definition
  • rename the table in the saved definition so not to clash with the name of the old table when you attempt to create it
  • create the new table using this definition
  • populate the new table with the data from the old table using ‘INSERT INTO <new_table> SELECT field1, field2, <default_for_new_field>, field3,… FROM <old_table>‘;
  • rename the old table
  • rename the new table to the original name
  • eventually drop the old, renamed table after you make sure everything’s alright

More Related Contents:

  1. How does the search_path influence identifier resolution and the “current schema”
  2. How do you find the row count for all your tables in Postgres
  3. How do you use variables in a simple PostgreSQL script?
  4. How to make “case-insensitive” query in Postgresql?
  5. Rownum in postgresql
  6. How to switch databases in psql?
  7. Postgresql: Scripting psql execution with password
  8. dynamic sql query in postgres
  9. Cannot create a database table named ‘user’ in PostgreSQL
  10. Is there a timeout for idle PostgreSQL connections?
  11. How to make connection to Postgres via Node.js
  12. Buffers (Circle) in PostGIS
  13. docker postgres pgadmin local connection
  14. Find difference between timestamps in seconds in PostgreSQL
  15. Unable to connect PostgreSQL to remote database using pgAdmin
  16. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  17. How can I change a PostgreSQL user password?
  18. Docker wait for postgresql to be running
  19. I forgot the password I entered during PostgreSQL installation
  20. SQL query column does not exist error
  21. Switch role after connecting to database
  22. Postgresql enforce unique two-way combination of columns
  23. When do Postgres column or table names need quotes and when don’t they?
  24. How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
  25. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  26. how to store PostgreSQL jsonb using SpringBoot + JPA?
  27. Displaying image in Ireports using PostgreSql
  28. Orbeon Forms Postgres DB connection
  29. Postgres default sort by id – worldship
  30. Best way to install hstore on multiple schemas in a Postgres database?

Leave a Comment