• Home

  • Custom Ecommerce
  • Application Development
  • Database Consulting
  • Cloud Hosting
  • Systems Integration
  • Legacy Business Systems
  • Security & Compliance
  • GIS

  • Expertise

  • About Us
  • Our Team
  • Clients
  • Blog
  • Careers

  • VisionPort

  • Contact
  • Our Blog

    Ongoing observations by End Point Dev people

    Speedier column additions in PostgreSQL

    Jeff Boes

    By Jeff Boes
    July 17, 2013

    Say you want to add a column to a large table, e.g.,

    ALTER TABLE transactions ADD COLUMN email_sent BOOLEAN DEFAULT FALSE;
    

    You really do want new rows to start out with the column “false” (if no value is supplied when the row is created). However, you also want all existing rows to be “true”, so innocently:

    UPDATE transactions SET email_sent = TRUE;
    

    This is a great time for a coffee break, or a trip to the post office, or maybe (if you’re a telecommuter like me), a stroll out to weed the garden. Unfortunately for all those side activities, you really didn’t have to take the long way.

    BEGIN;
    ALTER TABLE transactions ADD COLUMN email_sent BOOLEAN DEFAULT TRUE;
    ALTER TABLE transactions ALTER TABLE email_sent SET DEFAULT FALSE;
    COMMIT;
    

    This is a lot faster; create all the columns with the appropriate value, then set the default for new rows, and all inside a transaction so you know it gets done atomically.

    postgres sql


    Comments