Crunchy Data joins Snowflake. Read the announcement
Craig Kerstiens
Craig Kerstiens
For developers who have been around SQL long enough, they know that there is often more than one way to get the same result. Today I wanted to look at a few different ways to aggregate and show a total with paid and unpaid status. First, we will use a common table expression ( CTE ), which is a nice method for organizing subqueries. Second, we use statements within aggregation context to filter out the values we want. Lastly, we use to clean up the syntax, but effectively do the same as th...
Read MoreCraig Kerstiens
Craig Kerstiens
Postgres is a great database with a ton of features including really rich indexing . Postgres itself maintains all sorts of data under the covers about things like cache hits and misses, when indexes are and aren't used , and more. If you're staring at a complex explain plan you may think some well targeted indexes may help, but how can you be sure? Enter HypoPG , a Postgres extension for adding hypothetical indexes to Postgres so you can do index planning. HypoPG supports hypothetical inde...
Read MoreElizabeth Christensen
Elizabeth Christensen
Custom data types is one of those many features that makes PostgreSQL flexible for development of a huge variety of business and application use cases. Data types will help you primarily for data integrity, ensuring your data is stored in the database as you want it to be. A common surprise gift of using strict data types is that you can end up reducing your long term data maintenance. There’s two main ways to customize data types in Postgres: • Create s, which are value constraints added to bu...
Read MoreChristopher Winslett
Christopher Winslett
JSON is everywhere, even in your SQL database. Since Postgres added JSON support 10 years ago, we've seen widespread adoption. When JSON support was first released in 2012 the implementation was a fairly flat representation of the JSON data type. It had limited querying and indexing capabilities. Craig , who was active in early JSON discussions with Postgres contributors, admits "Postgres cheated" with these early implementations. As JSONB has been introduced and adopted, I think you can see...
Read MorePaul Ramsey
Paul Ramsey
One of my favourite (in an ironic sense) data formats is the "CSV in the CSV", a CSV file in which one or more of the column is itself structured as CSV. Putting CSV-formatted columns in your CSV file is a low tech approach to shipping a multi-table relational data structure in a single file. The file can be read by anything that can read CSV (which is everything?) and ships around the related data in a very readable form. But how can we interact with that extra data? If you want to try this blo...
Read MorePaul Ramsey
Paul Ramsey
Back when I first learned SQL, calculating percentages over a set of individual contributions was an ungainly business: • First calculate the denominator of the percentage, • Then join that denominator back to the original table to calculate the percentage. First calculate the denominator of the percentage, Then join that denominator back to the original table to calculate the percentage. This requires two passes of the table: once for the denominator and once for the percentage. For BI queries...
Read MorePaul Ramsey
Paul Ramsey
Last week Craig Kerstiens published a great introduction to Postgres terminology , covering some of the basics you might run into when just getting started. The funny thing about jargon is how quickly we get used to it, and forget we are even using it. It becomes part of the secret handshake, the way we signal to other members of our tribe that we're part of the group. When I first started going to Postgres conferences and listening to talks by Postgres core developers I suddenly found myself...
Read MoreElizabeth Christensen
Elizabeth Christensen
So you’re new to Postgres and you want to test it out and see how it works. Or maybe you’re building a new app and you need to seed your database. How do you get data in your Postgres database? There’s a lot out there on the topic of data migration for Postgres and some of it can be complicated and overwhelming. I’m going to give you some quick and simple ways to get test data IN your database. If you need to load data from csv files, the psql command in Postgres is a utility that can come...
Read MoreCraig Kerstiens
Craig Kerstiens
In my career I've worked on teams that ran and managed over a million databases, and I've helped shard and scale database for customers at 100s of terabytes. Postgres is an incredibly flexible database , and because of it's flexibility you have a lot of options for architecting your data design for your application needs. In talking with current customers and future customers how you design and architect your database is a common topic. We're going to dig into some of the flexibility on archi...
Read MoreJean-Paul Argudo
Jean-Paul Argudo
With PostgreSQL 15 comes a new SQL Command called MERGE . has been in the SQL standard for quite a while, but it just made its way into the PostgreSQL codebase. Prior to , if you wanted to refresh a target table from a source table , prior to Postgres 15, you could use the "upsert" method with the clause . Now, can be used instead! Some situations where makes a lot of sense are: • data loading from external sources, thru foreign data wrappers • staged and batched process jobs data l...
Read More