JSON is a data interchange format that is designed to be lightweight and easy to work with. It’s quite popular in web applications, and it can be considerably more flexible than a traditional relational data model. PostgreSQL 9.3 and later versions support JSON, so you can store JSON data and use native Postgres functions to operate on it. This includes decomposing, transforming, or even creating JSON data from regular relational data.
PostgreSQL provides an activity-tracking module called thestatistics collector, which tracks table access and other internal events. If your database is experiencing long wait times, you may be able to use this tool and some simple SQL to find and fix the problem.Tracking Postgres Activity With the Statistics Collector ToolThe statistics collector is designed to keep records about internal activity in a Postgres database manager. It can:count table and index access in both disk-block and individual-row terms.
Geographical applications are everywhere: GPS and sat nav systems, maps, get-a-taxi apps, real estate portals, etc. Behind each of them is a spatial database storing geographical data, and supporting spatial queries. In this article, we will introduce PostGIS, the main open-source spatial database manager.PostGIS is a spatial database extension for the PostgreSQL relational database. It adds support for geographic objects, allowing location queries to be run in SQL.PostGIS adds two main data types to PostgreSQL: geography and geometry. Both allow the storage of points in a table, as well as other more complex shapes like lines (a line is defined by two points), multipoint lines (defined by N points), polygons (defined by a closed multipoint line), and points with a specific altitude (defined by a third coordinate). This extender also offers a set of spatial functions for distance calculation, area calculation, intersection, and inclusion, among many others. All these new data types and functions can be used in combination with regular relational data in SQL, increasing the power of queries.
Some years ago, when PostgreSQL version 8.3 was released, a new extension calledtablefuncwas introduced. This extension provides a really interesting set of functions. One of them is thecrosstabfunction, which is used for pivot table creation. That’s what we’ll cover in this article.The simplest way to explain how this function works is using an example with a pivot table. First, we will explain our initial point from a practical perspective, then we’ll define the pivot table we want.
It’s common knowledge that the best way to learn something is to practice it in a real-life scenario. Obviously, the same applies to database modeling. Therefore, in this article I decided to teach you how to create a simple database structure, taking a textbook example of a hotel room reservation system. I will show you how to get started and give you some ideas for extending the model.Database Modeling: Discover, Discover, Discover