title: PostgreSQL: is it possible to ALTER TABLE and INSERT to it within a single transaction?
body: <p>My use case is the following: I'm working on periodical data imports to a PostgreSQL database where some external data has to be imported at regular intervals.</p>
  
  <p>The caveat is the structure of the data might change from one import to the other, therefore I'm truncating + dropping all columns on every import before inserting the new data.</p>
  
  <p>I would like to wrap the entire operation within a single transaction, so in case something goes wrong, the transaction would be rolled back and the old data would still be accessible (lesser of two evils kind-of-thing).</p>
  
  <p>As an example, here is what a data import statement might look like:</p>
  
  <pre><code>BEGIN
  
  ALTER TABLE "external_data" DROP "date"
  ALTER TABLE "external_data" DROP "column1"
  ALTER TABLE "external_data" DROP "column2"
  
  ALTER TABLE "external_data" ADD "date" date DEFAULT NULL
  ALTER TABLE "external_data" ADD "column1" text DEFAULT NULL
  ALTER TABLE "external_data" ADD "column2" text DEFAULT NULL
  ALTER TABLE "external_data" ADD "column3" text DEFAULT NULL
  
  INSERT INTO "external_data" ("date","column1","column2","column3") VALUES ('20170523','Berlin','Chrome','1'),('20170524','Berlin','Chrome','2')
  
  COMMIT
  </code></pre>
  
  <p>This is currently not working. The INSERT statement gets stuck because, when it's called, the table is still locked from the ALTER TABLE statement that preceded it.</p>
  
  <p>Is there any way to achieve this within Postgres transactions or should I give up and go for some other solution application-side?</p>