Course list

Category: PostgreSQL

  • Reminder: My “Intro to SQL” course is this Sunday!

    This is just a reminder that on Sunday, I’ll be teaching a live, 4-hour introductory course on databases and SQL. If you haven’t ever worked with databases before, then this will give you the push that you need to understand how they work, and how to work effectively with them. I’ve been using SQL for…

    Read more

  • Join the data revolution with my “Intro to SQL” course!

    Have you heard? Data is “the new oil” — meaning, data is the most valuable and important thing in the modern world. Which means that if you can store, retrieve, and organize your data, then you (and your company) are positioned for greater success. This usually means working with a database — and frequently, a…

    Read more

  • Yes, you can master regular expressions!

    Announcing: My new book, “Practice Makes Regexp,” with 50 exercises meant to help you learn and master regular expressions. With explanations and code in Python, Ruby, JavaScript, and PostgreSQL. I spend most of my time nowadays going to high-tech companies and training programmers in new languages and techniques. Actually, many of the things I teach…

    Read more

  • Using regexps in PostgreSQL

    After months of writing, editing, and procrastinating, my new ebook, “Practice Makes Regexp” is almost ready.  The book (similar to my earlier ebook, “Practice Makes Python“) contains 50 exercises to improve your fluency with regular expressions (“regexps”), with solutions in Python, Ruby, JavaScript, and PostgreSQL. When I tell people this, they often say, “PostgreSQL?  Really?!?” …

    Read more

  • In PostgreSQL, as in life, don’t wait too long to commit

    I recently helped a client with serious PostgreSQL problems. Their database is part of an application that configures and monitors large networks; each individual network node reports its status to the database every five minutes. The client was having problems with one table in particular, containing about 25,000 rows — quite small, by modern database…

    Read more

  • Remember: Don’t mix = and NULL

    I was recently performing an analysis on the database for my dissertation software, and wanted to look at one particular set of rows.  The table I was looking at is called Nodes, and because each node (represented by a row in the table) can optionally belong to a group, the group_id column allows for NULL…

    Read more

  • Turning a PostgreSQL array to rows

    Bottom line: To turn a PostgreSQL array into rows, wrap the column in the UNNEST function: SELECT UNNEST(stuff) FROM foo. Each element of the array becomes its own row, which you can then join, filter, or feed into INTERSECT and UNION. Wrap the whole query in ARRAY(…) to pack the rows back into an array.…

    Read more

  • Turning PostgreSQL rows into arrays

    The short version: To turn a set of PostgreSQL rows into an array, wrap a subselect in the ARRAY function: SELECT ARRAY(SELECT description FROM Appointments). It returns one row with one array, holding one element per input row. Wrap that in array_to_string(…, ‘, ‘) to get a comma-separated string, and use AS to give the…

    Read more

  • Looking in PostgreSQL arrays with ANY

    So far, this series has looked at how to create PostgreSQL arrays, how to retrieve data from them, and how to get the length of an array’s outer and inner dimensions. But one of the most common actions that we’ll want to do with an array is look inside to see if a particular value…

    Read more

  • PostgreSQL array indexes and length

    In my last blog post, I introduced the idea of a PostgreSQL array, and showed how we can insert data into a table using either the curly-brace {} syntax, or the ARRAY construction syntax.  In this post, I want to talk about PostgreSQL indexes and length — what happens when we retrieve from indexes that exist (and…

    Read more