Release Engineering: A draft of an architecture diagram

One of the things that I like to do is create architecture diagrams of complicated systems.

We had Release Engineering and Release Operations in the Portland Mozilla office this week, providing a perfect opportunity to pick everyone’s brains about what the current state of our release infrastructure is like.

Behold: releng flow onepage

And here’s a version that includes some “tree closure reasons” in magenta:

Releng infra with tree closure reason codes

A tree closure is defined as an hg hook that prevents people from committing to a tree (like mozilla-central). It looks up status at to figure out whether or not the tree is closed, and this value is updated manually by “sheriffs” who track tree status.

And the an initial key to the tree closure reasons (the numbers on the magenta blobs), is documented on the Mozilla wiki.

The goal of this document was to take brain dump information from everyone in the meeting, and create a relationship diagram of all the systems that everyone here supports. As you can see, it is pretty complex.

What I took away from creating this was:

  • The cognitive load is very high for trying to diagnose the root cause for several kinds of tree closures.
  • People loved being able to look at how each systems related to the others.
  • No single person really had a model in their head of how everything represented in this diagram was related.

There’s a lot more work to do to link in documentation and create some related diagrams, which I’ll tackle next week. The kinds of questions I’d like to try to answer based on the information that I’ve gathered include:

  • How does my patch get a build created for it?
  • What single points of failure can we mitigate?
  • What kinds of resilience do we need for our typical transient failures?

I really enjoyed identifying sources of tree closure and the kinds of failures that cause it. These are the kinds of problems I love working on solving — complicated, often unpredictable and largely driven by the normal work that people need to do to get their jobs done. There’s rarely a simple solution to things like experimental patches taking down large portions of a build infrastructure, and how we solve, or at least mitigate, these problems is fascinating.

Python Core Summit: notes from my talk today

I gave a short talk today about new coders and contributors to developer documentation today. Here are my notes!

Me: Selena Deckelmann Data Architect, Mozilla Major contributor to PostgreSQL, PyLadies organizer in Portland, OR

Focusing on Documentation, Teaching and Outreach

Two main forks of thought around teaching and outreach: 1. Brand new coders: PyLadies, Software Carpentry and University are the main communities represented 2. New contributors to Python & ecosystem

1. Brand new coders: PyLadies, Software Carpentry and University are the main communities represented

(a) Information architecture of the website

Where do you go if you are a teacher or want to teach a workshop? Totally unclear on Really could use a section on the website for this, microsite.

Version 2 vs 3 is very confusing for new developers. Most workshops default to 2, some workshops now require 3. Maybe mark clearly on all workshops which version. Generally this is a very confusing issue when encountering the site for the first time.

Possible solution: Completely separate “brand new coder” tutorial. Jessica McKellar would like to write this.

(b) Packaging and Installation problems — see earlier long conversation in this meeting about this. Many problems linked to having to compile C code while installing with pip

(c) New coder contribution can come through documenting of issues around install and setup. We could make this easier — maybe direct initial reports to stack overflow, and then float solutions to

2. New contributors to Python & ecosystem — with a focus on things useful for keeping documentation and tutorials up-to-date and relevant

(a) GNOME Outreach Program for WomenPython is participating!

More people from core should participate as mentors! PSF is funding 2-3 students this cycle, Twisted has participated for a while and had a great experience. This program is great because:

  • Supports code and non-code contribution
  • Developer community seems very cohesive, participants seem to join communities and stick around
  • Strong diversity support
  • Participants don’t have to be students
  • Participants are paid for 3 months
  • Participants come from geographically diverse communities
  • To participate, applicants must submit a patch or provide some other pre-defined contribution before their application is even accepted

Jessica McKellar and Lynn Root are mentors for Python itself. See them for more details about this round! Selena is a coordinator and former mentor for Mozilla’s participation and also available to answer questions.

(b) Write the Docs conference is a python-inspired community around documentation.

(c) Openstack – Anne Gentle & her blog. 3-year participant in OpenStack community and great resource for information about building technical documentation community.

(d) Better tooling for contribution could be a great vector for getting new contributors.

  • Wiki is a place for information to go and die (no clear owners, neglected SEO etc) – Maybe separate documentation repos from core code repos for tutorials
  • carefully consider the approval process – put the people who are most dedicated to maintaining the tutorials in charge of maintaining them


Type selection is not relevant to ‘documentation’ errors/fixes. Either remove ‘type’ from the UI or provide relevant types. I recommend removing ‘type’ as a required (or implied required) form field when entering a bug.

The larger issue here is around how we design for contribution of docs:

  • What language do we use in our input systems?
  • What workflow do we expect technical writers to follow to get their contributions included?
  • What is the approval process?

Also see the “tooling for contribution”

Weekly Feminist Work in Tech by Mozillians roundup – Week of March 3, 2014

We have a ton of individual work done by MoFo and MoCo employees related to feminism, feminist activism and the larger technology community. So much is happening, I can barely keep track!

I’ve reached out to a few people I work with to get some highlights and spread the word about interesting projects we’re all working on. If you are a Mozillian and occasionally or regularly work on feminist issues in the tech community, please let me know! My plan is to ping people every Friday morning and post a blog post about what’s happened in the last week.

Without further ado:

Dispatch from me, Selena Deckelmann:

  • I’m presenting at SF Github HQ on Thurs March 13, 7pm as part of the Passion Projects series (Julie Horvath’s project). I’ll be talking about teaching beginners how to code and contribute to open source, specifically through my work with PyLadies. I’m giving a similar talk this afternoon at Portland State University to their chapter of the ACM.
  • Just wrapped up a Git workshop for PyLadiesPDX and am gearing up for a test-run of a “make a Flask blog in 80-lines of code” workshop! Course materials are available here for “intro to git” workshops.
  • Lukas, Liz, me and others (I’m not sure who all else!!) are coordinating a Geekfeminism and feminist hackerspace meetup at PyCon 2014. The details aren’t published yet, so stay tuned!
  • PyLadies PyCon 2014 lunch is happening again!
  • PyLadies will also be holding a Mani-Pedi party just like in 2013. Stay tuned for details!
  • Brownbags for the most recent GNOME Outreach Program for Women contributors are scheduled for next Friday March 14, 10am and 2pm. (thanks Larissa!!) Tune in at One of the GNOME Outreach Program for Women contributors is Jennie Rose Halperin, and another is Sabina Brown.

Dispatch from Liz Henry:

  • I’m doing a lot of work to support Double Union feminist hackerspace, a nonprofit in San Francisco. We are hosting tech and arts workshops, and establishing connections with other hackerspaces in the US and around the world. Lukas is also involved with this effort! We have over 100 members now using the space.
  • For PyCon I would like to host fairly informal sessions in our Feminist Hacker Lounge, on QA, bug triaging, and running/writing WebQA automated tests with pytest and selenium.
  • I’m hoping to have funding for an OPW intern for this upcoming round to work on the back end of a QA community facilitating tool, using Python and various APIs for Mozilla tools like Bugzilla, Moztrap, and the Mozillians profiles.

Dispatch from Lukas Blakk:

  • Just held the Lesbians Who Tech hackathon at the Mozilla SF space and it was an amazing weekend of networking, recruiting for Mozilla, doing a stump speech on the radical/political possibilities of open source, and also just a lot of social fun.
  • I’m nearing the point of Project Kick Off for The Ascend Project which will be a 6 week training course for underrepresented in current tech mainstream (and underemployed/unpaid) persons who will learn how to write automatable tests for MozMill. This first one will take place at the Portland office in Sept/Oct 2014 (Starts on Sept 8th). There’s so much more here, but this is just a sound bite.
  • I’m trying to determine what budget I can get agreement on to put towards women in tech outreach this year.
  • PyCon – yes! Such Feminist, So Hackerspace, Much gathering of geek feminists!

Dispatch from Larissa Shapiro:

  • OPW wrapup and next session – we’re wrapping up the current round, scheduling brownbags for two of the current interns, etc. Funding is nearly secured for the next round and we have like 6 willing mentors. w00t.
  • I’m also providing space for/speaking at an upcoming event in the Mountain View office: last year’s African Techwomen emerging leaders were part of a documentary and the Diaspora African Women’s Network is holding a screening and a planning session for how to support next year’s ELs and other African and African-American bay area women in tech both through this and other projects, March 29. Open to Mozilla folks, let me know if you’re interested.

Anything else that’s come up in the last week, or that you’d like Mozillians to know about? Let me know in the comments!

Printing flashcards on 3×5 index cards

I’m making a few sets of these for a meetup tomorrow night:



In the past, I’d printed out text from a spreadsheet tool and then used a lot of tape and scissors to make the magic happen.

I wanted to step up my game a little and print things directly onto index cards. Printing is a little tricky on most printers because you won’t be able to print double-sided unless you have a very fancy printer. I have a Cannon MG6220 (mostly for printing photos).

Here’s how to set up a word processing app to print out cards (I used Pages):

  1. Configure the page layout to be 3″ x 5″. Here’s the Page Setup dialog box for Pages: Screenshot 2014-02-18 17.18.04
  2. Add your flashcards to the document! Add the cards with the front and back of each flashcard as single, adjacent pages. A sample PDF of what this looks like is here.
  3. To make the printing work, I set up 2 printing presets – one called ‘front flashcards’ and the other called ‘back flashcards’. The ‘front’ is set up to print odd pages only, and ‘back’ is set up to print even pages only and in reverse order.
  4. Print the front side of the cards using your ‘front flashcards’ preset.
  5. Flip the stack of cards over, face down, and put them back into the paper feeder (YMMV with this in the event that your printer is set up differently than mine).
  6. Print the other side of the flashcards using your ‘back flashcards’ preset.

That’s it!

PyLadies Meeting notes from “Negotiating the job market: a panel discussion”

Flora Worley organized a fantastic PyLadies PDX meeting called “Negotiating the job market: a panel discussion“.

The meeting was organized in three parts:

  1. Experiences (good, bad and ugly) from four women who entered the software industry in the last 1-2 years.
  2. Managing expectations and setting boundaries from three people, two recent entrants into the industry, and me.
  3. Negotiating the application/interview/offers process (which we turned into a group discussion, led by one panelist)

We kicked things off by asking people to get into groups of four and talk to each other about why they came and what they were hoping to get out of the meeting.

Some of the comments from the meeting and feedback after included:

  • On what was good prep for interviewing: Attending PyLadies and Python User Group meetups to learn new skills, hear about what modules and techniques people are using. (from Amy Boyle, a local developer)
  • Attending PyLadies helped fill in gaps in knowledge useful as a working programmer, even after having a CS degree.
  • “I love being a Pylady, and if it weren’t for this community, I honestly don’t know that I would have continued learning to code.”

Below are my notes from the first panel, anonymized and edited a bit.

How did you find your first job in the industry and know it was the right place for you?

  • I knew a founder of the company from college
  • Knew someone and they invited me to apply. Wrote a great cover letter and got an interview even though they thought I didn’t have the skills for the exact job I applied for.
  • Got the job by going to talks and staying and talking to the speakers.
  • Decided I was more interested in data than my major! Looked around and found a company that was doing a lot with data.

At what point do you say you “know” a programming language?

  • I shy away from saying I “know” something — seems presumptuous to say that the same way it seems weird to say “I’m a writer.” If you’re getting paid to do a thing, though, then you get to call yourself the “thinger”. Coworker has been asking me for help with python and I know the answers to his questions so…
  • Finding ways to help others with things is a good way to boost your confidence.
  • Once I give a talk about something, I have to say I know how to do it because people will come to me for help. It’s a way to force yourself to cram, find out what you know and really don’t know. On my resume I don’t say “I know” — I say “I have experience with XYZ; I have managed to learn these things to get the job done.” Most technical interviews ask more general questions.. not exact syntax of a language.
  • Interviews seem to be trying to figure out if you can learn whatever it happens to be that they need you for at the job.
  • We have to support many languages. So many languages at the same time can get overwhelming.
  • I don’t say I’m an IOS programmer, but I help people write and improve their IOS code all the time.

What was your interview like?

  • Shared projects I made. We just went through my github repo. Tell me about this project, what did you do, what did you use.
  • Was interviewed over Skype
  • Interviewed with 4 people in a marathon. None of those interviews were super technical.
  • The interviews seemed to be gauging whether you would be able to just talk about whatever comes up.
  • They asked me about a lot of command line skills and brought lots of people in from different parts of the company.
  • The interview had me sit down with a program — there’s a bug in this program solve it. What I learned is what matters is the process of how you work on bugs — and being able to communicate that while you’re doing it, not that you actually fix it.
  • Most valued skill is resilience rather than a technical background. You can learn, compensate, fill in gaps. Need willingness to learn, capacity to be frustrated/despair and just move on. People don’t want to hear you freaking out, just want you to do it. Even if it takes you a long time.

Were there classes or resources that prepared you for interviewing?

  • Go out and make something for yourself. Nothing better than finding a thing you want to make, and then making it. Going back to old code, really fulfilling to see progress and wanting to make old code better. You have something to share!
  • Coming to meetings like PyLadies and the Python User Groups. I took some CS classes, but there was lots that I missed. You don’t realize how much you don’t know! Get to meetups to hear about what others are using, what is current in the field. Having a sense of with what the latest stuff is, what are good blogs, best practices, helps out. (seconded by another panelist)
  • Very helpful to go to PyLadies and talk with people around my same experience level. In conversation, people would explain stuff to you in a way that made sense, not just a bunch of jargon.
  • Learning that ecosystem and what tools people use is huge. Helps you figure out what a job entails and what do you need to know to build it.
  • For most of the stuff you’ll build, you’re going to use other libraries. It helps having experience mushing things together. Iterate on it. Every little thing you learn, you’ll find ways of improving stuff.

What if your coding style is really different than other people’s? How do you handle that?

  • Get good at giving constructive feedback
  • “days of spaghetti code is behind us” at the mercy of other people’s crappy code — tech companies deal with this a lot less.
  • Don’t be afraid to say “no idea what this function ” learned a lot about better code by reviewing a lot of code

What kinds of positions can you get? And where do you want to go?

  • Really overwhelming to learn everything I needed to know to support product. It’s an all guys team, learned to love them all, took on a mother role.
  • Teaching stuff all guy team can be intimidating, but most of the guys I work with are college educated CS guys, me having no CS background was intimidating until I realized I knew and could learn this stuff as quickly as they could. No CS didn’t matter.
  • I’m doing tech support, wish I could do more coding. It’s a mixed bag doig support, you learn a lot about system. Found that management wanted everyone to level up in tech support and not go to other parts of the company. I’ve had to come to terms with managers trying to keep me in that role. Need to see how long they expect you to stay in that role before you can move on — ask up front.
  • Had similar experience — resistance to moving from tech support into other roles. Most people in support are looking to move into another role. Make sure you’re on the same page with manager rather than be surprised by it later.
  • On working with guys: my communication style was not really effective when I started. Sometimes had customers that said “can I talk to tech support?” “can I talk to an engineer?”. Had to learn how to be kind of cocky — “This is how this works.” If I was wrong, I was wrong, and had to fix it but that was much more effective at communicating with customers and coworkers.
  • Had to learn that nothing wrong with asking for help. Saves a lot of time.
  • Everything takes me longer than I think it will. Triple your estimates!
  • I needed to learn its ok to ask questions, ask questions confidently rather than despair of never being a programmer. We all tell ourselves crap like: “i’m just generally stupid and can’t learn anything” or “can’t learn javascript in a day, so clearly i’m an idiot”. But turning that around is important for yourself.
  • Be dilligent in the way you ask questions: 1) first google it, stack overflow, check reference book; 2) write out the question fully (rubber ducking) — articulate the problem fully and you might solve it for yourself — what did i expect, what happened, what else can i expect….
  • I’ve had people say to me: “I don’t know why pyladies needs to exist.” I said: “I think its nice its a safe place where that you can articulate your questions without fear.” I don’t know what to do about it other than say that.
  • Men might be made clear to you that they believe we are post-sexist, post-racist. If you have too many of those people around, find a new job.
  • “you need to be careful about where you are” when expressing opinions about feminism.
  • Be open and honest, and they tend to understand.
  • Sometimes it may be the case you have to figure out if an opinion is being expressed because of privilege or malice. If privilege, it can be worked on.
  • Try to internalizing confidence. Tell yourself: “I know how to program” more. Once I really believed that, my programming got better. Because I know I will solve the problem I’m working on!

Do you find that as a woman you communicate differently and you are interpreted differently?

  • Here’s an example: when someone says: “Can you pass the salt?” One person might respond by thinking, “I understand you’re being polite and phrasing that request as a question rather than a command. Sure, here’s the salt!” Or another person might respond with, “I could pass the salt to you. Is that actually what you want me to do?” Pretty different communication styles.
  • Try to be flexible and ride out conflict.
  • I question myself a lot — did i interrupt too many times, was i too aggressive. I worry I am too aggressive.
  • Be yourself. Take time to figure out if you are happy or not.
  • Try to compromise and avoid “truth bombs” where you “explain how the world is”. Take conversations case by case and try not to take it personally.
  • If it gets to be too much, get out of there!

Everyday Postgres: Describing an “ideal” Postgres Operational Environment

I spent some time thinking about what things in the Postgres environment (and specifically for make me happy, and which things bother me so much that I feel like something is pretty wrong until they are fixed or monitored.

Here’s what I came up with:

I’m planning to go through each of these items and talk about how we address them in the Web Engineering team, and that will include implementing some new things over the next couple of quarters that we haven’t had in the past.

One thing that didn’t surprise me about this list was how much documentation is needed to keep environments running smoothly. By smoothly, I mean that other people on the team can jump in and fix things, not just a single domain expert.

Sometimes docs come in the form of scripts or code. However, some prose and explanation of the thinking behind the way things works is often also necessary. I frequently underestimate how much domain knowledge I have that I really aught to be sharing for the sake of my team.

Everyday Postgres: Specifying all your INSERT columns

Postgres has so many convenient features, including the ability to not provide a list of columns to an INSERT.

For example:

CREATE TABLE temp_product_versions ( LIKE product_versions );
INSERT INTO temp_product_versions ( SELECT * from product_versions ); 

That’s pretty badass.

However, you may encounter trouble in paradise later if you use this kind of shortcut in production code.

See if you can spot the error in this code sample below.

Here’s the error message:

ERROR:  column "is_rapid_beta" is of type boolean but expression is of type citext
LINE 10:     repository
HINT:  You will need to rewrite or cast the expression.
QUERY:  INSERT INTO releases_recent
SELECT 'MetroFirefox',
FROM releases_recent
    JOIN products
        ON products.product_name = 'MetroFirefox'
WHERE releases_recent.product_name = 'Firefox'
    AND major_version_sort(releases_recent.version)
        >= major_version_sort(products.rapid_release_version)
CONTEXT:  PL/pgSQL function update_product_versions(integer) line 102 at SQL statement

And here’s the code (long!)

I’m sure quite a few of you found the problem right away. For the rest of us…

Here’s the error message you get if you specify the columns for the INSERT:

ERROR:  INSERT has more target columns than expressions
LINE 10:     repository
QUERY:  INSERT INTO releases_recent (
SELECT 'MetroFirefox',
FROM releases_recent
    JOIN products
        ON products.product_name = 'MetroFirefox'
WHERE releases_recent.product_name = 'Firefox'
    AND major_version_sort(releases_recent.version)
        >= major_version_sort(products.rapid_release_version)
CONTEXT:  PL/pgSQL function update_product_versions(integer) line 112 at SQL statement

Now, it should be completely obvious. There’s a missing comma after build_id.

Implicit columns for INSERT are a convenient feature when you’re getting work done quickly, they are definitely not a best practice when writing production code. If you know of a linting tool for plpgsql that calls this kind of thing out, I’d love to hear about it and use it.

My nerd story: it ran in the family, but wasn’t inevitable

This is about how I came to identify as a hacker. It was inspired by Crystal Beasley’s post. This is unfortunately also related to recent sexist comments from a Silicon Valley VC about the lack of women hackers. I won’t bother to hate-link, as others have covered his statements fully elsewhere.

I’ve written and talked about my path into the tech industry before, and my thoughts about how to get more women involved. But I didn’t really ever start the story where it probably should have been started: in my grandfather’s back yard.

grandpa-our wedding

I spent the first few years of my life in Libby, MT. Home of the Libby Dam, an asbestos mine and loggers. My grandfather, Bob, was a TV repairman in this very remote part of Montana. He was also a bit of a packrat.

I can still picture the backyard in my mind — a warren of pathways through busted up TVs, circuit boards, radios, transistors, metal scrap, wood and hundreds of discarded appliances that Grandpa would find broken and eventually would fix.

His garage was similarly cramped — filled with baby jars, coffee cans and bizarre containers of electronic stuff, carefully sorted. Grandpa was a Ham, so was my uncle and Grandma. I don’t remember exactly when it happened, but at some point my uncle taught me Morse code. I can remember writing notes full of dots and dashes and being incredibly excited to learn a code and to have the ability to write secret messages.

I remember soldering irons and magnifying lens attachments to glasses. We had welding equipment and so many tools. And tons of repaired gadgets, rescued from people who thought they were dead for good.

Later, we had a 286 and then a 386 in the house. KayPro, I think, was the model. I’d take off the case and peer at the dust bunnies and giant motherboard of that computer. I had no idea what the parts were back then, but it looked interesting, a lot like the TV boards I’d seen in piles when I was little.

I never really experimented with software or hardware and computers as a kid. I was an avid user of software. Which, is something of a prelude to my first 10 years of my professional life as a sysadmin. I’m a programmer now, but troubleshooting and dissecting other people’s software problems still feels the most natural.

Every floppy disk we had was explored. I played Dig Dug and Mad Libs on an Apple IIe like a champ. I mastered PrintShop and 8-in-1, the Office-equivalent suite we had at the time. And if something went wrong with our daisy wheel printer, I was on it – troubleshooting paper jams, ribbon outages and stuck keys.

My stepdad was a welder, and he tried to get me interested in mechanical things (learning how to change the oil in my car was a point of argument in our family). But, to be honest, I really wasn’t that into it beyond fixing paper jams so that I could get a huge banner printed out.

I was good at pretty much all subjects in school apart from spelling and gym class. I LOVED to read — spending all my spare time at the local library for many years, and then consuming books (sometimes two a day) in high school. My focus was: get excellent grades, get a scholarship, get out of Montana.

And there were obstacles. We moved around pretty often, and my new schools didn’t always believe that I’d taken certain classes, or that grades I’d gotten were legit. I had school counselors tell me that I shouldn’t take math unless I planned “to become a mathematician.” I was required to double up on math classes to “make up” algebra and pre-algebra I’d taken one and two years earlier. I gave and got a lot of shit from older kids in classes because I was immature and a smartass. I got beat up on buses, again because I was a smartass and had a limited sense of self-preservation.

The two kids I knew who were into computers in high school were really, really nerdy. I was awkward, in Orchestra, and didn’t wear the kind of cool girl clothes you need to make it socially. I wasn’t exactly at the bottom of the social heap, but I was pretty close for most of high school. And avoiding those kids who hung out after school in the computer lab was something I knew to do to save myself social torture.

Every time I hear people say that all we need to do is offer computer science classes to get more girls coding, I remember myself at that age, and exactly what I knew would happen to me socially if I openly showed an interest in computers.

I lucked out my first year in college. I met a guy in my dorm who introduced me to HTML and the web in 1994. He spent hours telling me story after story about kids hacking hotel software and stealing long distance. He introduced me to Phrack and 2600. He and his friends helped me build my first computer. I remember friends saying stuff to me like, “You really did that?” at the time. Putting things together seemed perfectly natural and fun, given my childhood spent around family doing exactly the same thing.

It took two more years before I decided that I wanted to learn how to program, and that I maybe wanted to get a job doing computer-related work after college. What I do now has everything to do with those first few months in 1994 when I just couldn’t tear myself away from the early web, or the friends I made who all did the same thing.

Jen posted an awesome chart of her nerd story. I made one sorta like it, but couldn’t manage to make it as terse.

Whether it was to try and pursue a music performance career after my violin teacher encouraged me, starting out as a chemistry major after a favorite science teacher in high school told me I should, or moving into computer science after several years of loving, mischievous fun with a little band of hackers; what made the difference in each of these major life decisions was mentorship and guidance from people I cared about.

Maybe that’s a no-brainer to people reading this. I say it because sometimes people think that we come to our decisions about what we do with our lives in a vacuum. That destiny or natural affinity are mostly at work. I’m one anecdata point against destiny being at work, and I have heard lots of stories like mine. Especially from new PyLadies.

Not everyone is like me, but I think plenty of people are. And those people who are like me — who could have picked one of many careers, who like computers but weren’t in love or obsessed with them at a young age — could really use role models, fun projects and social environments that are low-risk in middle school, high school and college. And as adults.

Making that happen isn’t easy, but it’s worth sharing our enthusiasm and creating spaces safe for all kinds of people to explore geeky stuff.

Thanks to an early childhood enjoyment of electronics and the thoughtfulness of a few young men in 1994, I became the open source hacker I am today.

Everyday Postgres: INSERT with SELECT

This is a continuation of a series of posts about how I use Postgres everyday.

One of the most pleasant aspects of working with Postgres is coming across features that save me lots of typing. Whenever I see repetitive SQL queries, I now tend to assume there is a feature available that will help me out.

One such feature is INSERT using a SELECT, and beyond that, using the output of a SELECT statement in place of VALUES.

Take for example:

INSERT into foo_bar (foo_id, bar_id) VALUES ((select id from foo where name = 'selena'), (select id from bar where type = 'name'));    
INSERT into foo_bar (foo_id, bar_id) VALUES ((select id from foo where name = 'funny'), (select id from bar where type = 'name'));
INSERT into foo_bar (foo_id, bar_id) VALUES ((select id from foo where name = 'chip'), (select id from bar where type = 'name'));

I think a lot of people know that this is possible. There are a few problems with it – like if the result of the WHERE clause isn’t unique in both cases, you’d get an error. In this case, id in both tables were surrogate keys, with both name and type being unique.

What some people don’t realize is that you can SELECT, and then directly insert that into a table:

INSERT into foo_bar (foo_id, bar_id) ( 
    WHERE type = 'name' AND name IN ('selena', 'funny', 'chip') 

If the values you wanted to take from the table bar were not all the same, the query would be considerably more complex. Given that I only am interested in a single value from bar, and I want it joined with a series of explicitly selected values from foo, this version of the query saves me a lot of typing.

The bigger picture, however, was pointed out in the comments by Marko:

VALUES is just a special type of SELECT and that INSERT writes the
result of an arbitrary SELECT statement into the table. Consider:

SELECT 1; vs. VALUES (1);



The reason VALUES is often used with INSERT is that many RDMBSs don’t
support SELECT without a FROM clause, so using VALUES is more
convenient. It’s also handy if you have a list of data you want to
SELECT, e.g. VALUES (..), (..), (..);

I may have referenced this feature a few times when breaking down functions used for reports in Socorro. It’s super convenient and saves quite a bit of typing! You can put any valid SQL query in there, including CTEs. The documentation for INSERT provides a few more examples.