Crunchy Data joins Snowflake. Read the announcement
Kat Batuigas
Kat Batuigas
Even if you're relatively new to SQL , chances are that you know the INSERT command is used to add new data to a table. For those of you newer to Postgres such as myself, you may not be familiar with a couple of neat tricks you could do with inserts. This post is a refresher on INSERT and also introduces the RETURNING and ON CONFLICT clauses if you haven't used them yet, commonly known as upsert. Let's start with the basics, we usually see INSERT in two forms. The first explicitly provides the...
Read MoreSteve Pousty
Steve Pousty
Today’s blog post is going to be a nice little adventure of learning how to use composite primary keys in a PostgreSQL many-to-many relationship table while building a Django application. Along the way we will talk about some basics of Django and some workarounds you need to use. Let’s dig in and get started. Here on the developer relations team at Crunchy Data , we have started building a demo application that manages Dungeon and Dragons (D&D) players, characters, dungeon masters, and c...
Read MoreYorvi Arias
Yorvi Arias
An important part of performing a migration from Oracle to PostgreSQL is selecting the right tool for helping with the conversion between systems. When people ask me how they can get the migration process started, I like to recommend a powerful open source utility called " ora2pg ". As the name suggests, ora2pg is a tool that migrates Oracle (or MySQL) databases to PostgreSQL by generating compatible SQL files As the documentation states, ora2pg “connects your Oracle database, scans it automatic...
Read MoreDavid Youatt
David Youatt
Creation and clean up of WAL files in the primary's folder ( prior to PG10) is a normal part of PostgreSQL operation. The WAL files on the primary are used to ensure data consistency during crash recovery. Use of write-ahead logs (also called redo logs or transaction logs in other products) is common for data stores that must provide durability and consistency of data when writing to storage. The same technique is used in modern journaling and log-structured filesystems. As the DB is operatin...
Read MoreCraig Kerstiens
Craig Kerstiens
Most queries against a database are short lived. Whether you're inserting a new record or querying for a list of upcoming tasks for a user, you're not typically aggregating millions of records or sending back thousands of rows to the end user. A typical short lived query in Postgres can easily be accomplished in a few milliseconds or less. For the typical application, this means a well tuned production Postgres database is capable of easily running thousands or up to hundreds of thousands of q...
Read MoreSteve Pousty
Steve Pousty
In today's blog post I am going to show you how to easily spin up PostGIS in your Kubernetes or OpenShift cluster using the PostgreSQL Operator . You may have already started to investigate how to take advantage of all the Kubernetes goodness, especially combining it with your favorite spatial database, PostGIS. Well have no fear, after a few introductory steps I will show you how one command can bring up PostGIS and then with another command, make it a replicated cluster. This replicated...
Read MorePaul Ramsey
Paul Ramsey
In our last installment , we covered the use of a constraint trigger to enforce data quality by looking at geometry spatial relationships. For this installment, we'll start with basic relationships and then look at more complex use cases: deferred constraints, and full table-level data structures. Linear Network Constraints Let's start with a simple road network. We've added a couple simple check constraints in the table definition: • we want our road segments to have a non-zero length; and, •...
Read MorePaul Ramsey
Paul Ramsey
If constraints in general have caught your interest, our interactive learning portal has a whole section on the use of non-spatial constraints , even a video walkthrough! In our last installment , we covered the use of CHECK constraints to enforce data quality at an object level. However, spatial data quality usually involves higher order relationships between geometries. Just as a strong non-spatial model will enforce foreign key relationships, spatial constraints can be used to enforce s...
Read MoreCraig Kerstiens
Craig Kerstiens
At a company where most all people have some Postgres expertise you can easily learn something new from your coworkers every day about Postgres. In my first week I saw a question in our internal slack that I could guess an answer to, but it wasn't definitive. It was "Why have composite types? Why would you use them?". I threw in an answer a few others did as well, but collectively we didn't have anything definitive but all these seemed like valid cases. Composite types ) are a custom type that i...
Read MoreKat Batuigas
Kat Batuigas
This post is the second in a two-part series -- read the first here: Going Back to Basics with PostgreSQL Data Types . In my last post, I shared some interesting (and at times surprising) things that I learned while digging into data types in PostgreSQL. Data types like numeric, integer, date, and char/varchar exist in every other relational database system since the need to work with such data is pretty much a given. The implementation may vary somewhat between systems, but generally there are...
Read More