Postgres: Functions, Operators, Conversion

Today I got through the functions, operators, and conversion sections of the postgres docs. There are lots of different functions in postgres and there is a lot of overloading on operators and functions, so this was just another overview of the possibilities. A thorough understanding of these concepts will require a lot more experience. So now, if someone asked me to name all of the functions that are associated with the bytea data type, I would still be fairly lost. BUT, my goal in doing this, is to get a general idea of the scope of problems that postgres is capable of solving. So if I arrive at some implementation problem where I need to make a scheduling and pricing system for a product, I know that postgres is ABLE to implement a money data type AND it can handle datetimes, including intervals and timezones. Postgres may not turn out to be the optimal solution for a given problem, but at least it’s another option now.


Postgres: Data types

Today I (mostly) trudged through the documents on postgres data types. As with some of the other docs, I have some familiarity with most of the concepts, and reading through the docs is more about completeness and making sure I haven’t missed anything. I got a better understanding of the differences between all the different number types, and from my experience, the important ones are simply integer, numeric, and serial. I’ve come across some of the more complicated ones such as uuid and point before, and I learned some new ones such as money and network addresses.

One of my big discoveries was the fact that you can specify a timestamp with a time zone. The time zone is something I’ve run into issues with before, and which likely could’ve been solved if I knew about these, but better late than never! I’m still a bit curious about the datetime identifier ‘allballs’, which is equivalent to 00:00:00:000 UTC.

I will likely be coming back to the textsearch types and the jsonb types. I have some experience implementing some text searches using ElasticSearch, but some of these docs are hinting that I may be able to do some of the same functionality in Postgres, which would be BIG deal for me. Further down in the docs I see a whole section on text searches, so that’s something to look forward to.

Postgres: Digging into queries

I got through some more postgres documentation today. I think reading docs can only hold my concentration for 1-2 hours max. Today I learned some formal definitions of some postgres functionality that I’m familiar with, such as joins, groupings, and the select list. Some new concepts for me were the GROUPING SETS, the ROLLUP, and the CUBE. These are essentially shortcuts to do complex groupings. Grouping sets allows you to do display multiple groupings within the same result set, a rollup lets you iteratively remove a column from a grouping set, and a cube takes the power set groupings from a group set. Off the top of my head, I can’t think of anywhere that these would’ve been useful for me in the past, but at least I know they exist.

The WITH clause, including the RECURSIVE option, are terms I’ve seen before, but have always been a little intimidated by, since they aren’t as intuitive as the simpler statements. I got a brief overview of them, and it seems like the WITH is something I could make use of (not so much the RECURSIVE), but I will definitely need some hands-on practice before feeling confident with it.

Postgres: Moving beyond the basics

I’ve continued reading through the postgres docs. The first little while was slow since it was mostly review and a lot of syntax and semantics. Right now I’m looking for a broad overview so I can be familiar with the functionality and workings of postgres at a mid-to-high level, so I’m not too focused on syntax specifics. I think that will come with more use. Today I got into some of the deeper concepts, including inheritance, partitioning, and schemas. I’ve heard about these and have a general idea of what they do, but reading these gives a bit more detail on HOW they work. I still would prefer to have a documentation that includes some exercises at the end of each document. I may look into some online courses to this end. In the meantime, I found a set of simple exercises to give me some hands-on practice:

I’ve also found a few ideas for how I can improve existing postgres databases that I’m currently working with. Some of the improvements might include stronger constraints, adding schemas for users, and adding permissions for certain queries (e.g. read-only data).

Postgres Intro

Today I started reading through the postgres docs. I just got through the Preface and the intro tutorial. It was mostly review, but I wanted to get through it anyways just to make sure there weren’t any important or basic concepts I’ve missed. So far there are no real surprises, and it was nice a refresher on some familiar terms, but I think I’d like to find some more practice on certain concepts, such as inheritance and window functions. I’m mainly used to using a relational database for simple queries (Update, Select, Delete) and the occasional join, and then I let the procedural logic in another application layer handle the logic that might be handled by some of these techniques. But I’d like to learn them anyways, just to have some more tools, in case I ever need them. I’m also going to try and find some simple postgres challenges, similar to programming katas, like I’ve tried on Codewars.


June Goal: PostgreSQL

At the end of June I am going to be heading to Boston for the postgresVision conference. It will be my first tech conference, and while I have a fairly good foundation in postgres, I’d still like to learn a bit more before going there. So for the month of June, I am going to be focusing on becoming an expert in postgres!

My main resource will be the official documentation found here, but I am also going to try and apply as much as possible to my postgres implementation in the quizipedia application (and of course at work). So I’ll be posting some summaries of what I learn throughout the month. After the conference, my tentative plan is to switch back to some of the larger scale goals in quizipedia, but this month will be a well-earned context-switch.

Quizipedia: Ready for alpha!

I made a few stylistic and programmatic changes to the application today. Mainly I added the first letter at the start of the blanks after immediately realizing that it was way too hard. Behind the scenes I also reworked some of the DAL code to be reusable.

I thought about some admin-related stuff, e.g. buying a domain name, or upgrading Heroku’s postGres add-on to a paid version. But I think I’d just like to get it out there and have people use it before spending any money on it.

Over the next month I’m going to gather some feedback on it, keep track of all the changes I’d like to make, and make a few updates once or twice a week. But for now, here is the link:

Here are my goals for the month:

  • Improve the correct/incorrect feedback to users
  • Add unit tests and integration tests
  • Setting up some “suggested” tests on the main page
  • Better handling of words that have punctuation
  • Improve the relevant word algorithm
    • Adding better regex
    • Lowering the score so that the same word doesn’t show up multiple times


Long term goals:

  • Importing a dictionary/thesaurus and working in multiple choice
  • Finding a way to read a pdf or other file formats
  • Adding in HTML parsing so the user only has to provide a link
  • Coming up with a way of giving the user hints


Quizipedia: Styling and functionality

After finishing all the connections from the views down to the database yesterday, I still need to add all the functionality of a standard web application. This includes some nice, non-invasive styling, a consistent look and feel, and some client-side AND server-side form validation. There is still lots to do on the app, but I think I’ll be ready to start showing it off to some select people this week. Right now I am just using the completely free heroku account, including the free postgres add-on. So I will likely be upgrading to some paid versions, and may even consider buying a domain name, just so I’m really going all the way with this.

I will not be heavily focusing on the app until July, so I am very glad I was able to get this far. I was hoping to get to a point where the main functionality is all there, and I can have a checklist of smaller, simpler tasks that are primarily focused on improved user interface and best coding practices. I believe I am just about there – I think one more evening of devoted work should do it. Then I’d like to break my todo-list for the app down into predictable hour-long tasks, and maybe do 2-3 every week.

These tasks will mainly include:

  • Learning and adding CSS animation
  • Automated Database scripts to remove stale data
  • Providing hints to users (maybe the first letter mininum)
  • Error logging
  •  Unit and integration tests

Quizipedia – Client-Side code

Today I was able to add the client-side views and connections to the quizipedia app. There are two simple views:

  • The game creation form: Enter a game name and the text to be parsed into a quiz.
  • The game itself: This is where the blanked out game and interaction takes place

As with the server-side code, I had written quite a bit of this code previously in another repo, so it was matter of getting it organized and hooked up with the rest of the new system. So it only took me one session to get all that done. And at this point, I’ve pretty much used up all my previously-written code, so all the next stuff is going to be new for me, and will likely take longer.

The client-side functionality is written in JavaScript, so this gives me a chance to see JavaScript in action on both the client-side and the server-side. This gets rid of any mental context-switching, which can occasionally cause a bit of lag.

So the main connections are made, and it is working well both locally and on production! So quite a satisfying day, but still lots of work to do. Over the next day or two, I’m going to get as much of the following done as I can:

  • Add in logical error-handling and logging
  • Style the client-side so it looks nice
  • Providing more feedback on correct/incorrect answers to the user
  • Add tests for the handlers
  • General code cleanup: comments, proper conventions, consistency, etc.
  • DB maintenance – maybe automate some scripts to clean up the db every so often

Once I finish a few of those tasks, I’m going to be re-focusing my goals for the month of June, which I will discuss in a later post.

Quizipedia Server-side code

I finished up most of the server-side code for my quizipedia web application today. It was fairly easy to write, since I had a few repos of sandbox code that had at least 75% of the main functionality, so it was just a matter of cleaning it up and putting it all together.

Here’s what I’ve accomplished in the past two sessions of working on it:

  • Set up a Heroku project using the node-related tutorial
    • Resource
    • Heroku makes it very easy to actually deploy something and allows you to have an easy testing environment, using a local testing database
  • Set up a postgres database (local and prod)
    • Resource
    • To add a PG db I just had to “provision” the Heroku PostGres add-on, which is straightforward and included in the node tutorial
    • My main issue was that I was trying to connect my local environment to the production database that Heroku sets up for you. It took me too long to realize that I was supposed to be using a LOCAL db, which I quickly set up
    • I think it will be important to keep a change log of any db updates that I do. When making changes locally, I can just wipe the db and re-run the script I’ve saved. But this isn’t going to work on prod, where the existence of the data depends on me NOT destroying the tables. I have some experience using a migrations package, which allows you to create a new ‘migration’ file anytime you need to make a change, for example adding a table column. This will be essential for keeping track of all my changes. I’m not going to add it in right now, as my focus is to get something out and working.
  • Added the main handlers. This is a RESTful application, so the primary handlers right now include:
    • POST /game: This is where a user would post some text to be formatted into a quiz. Eventually I would like to have them just send a link to a webpage with the data, do the parsing, and create the quiz that way.
    • GET /game/:gameId: This is going to fetch the game information by the id generated in the saving functionality. It will return the blanked out game. Further parsing will be done on the client-side
    • POST /guess: This is is a call to allow the user to submit a guess for one of the blanks. Their guess will be verified against the true answer. If it is correct, it will be saved accordingly.
  • Connected the handlers to the DB via a function library and a data layer.
    • The data layer is essentially an active record setup, where the models reflect the columns in the database. I have three tables, so I have 3 dal classes.
    • The library is the business logic layer, where the relevant word algorithm gets used, and the objects get converted to the appropriate formats
    • Building the game is a large job, so I created a completely separate ‘gameBuilder’ helper class which is called by the main ‘game’ library class.


So I’m happy with this initial progress. Again, a lot of it was just organizing some existing code in some unorganized repos into something workable and organized.

My next big step is going to be to add the client-side handling. This will mainly involve submitting a new game text, presenting the constructed game, and filling out correct answers as needed.