Tuning a SQL query is a mix of data driven discovery and magic art, until now… Aiven SQL query optimizer allows you to speed up your query by simply pasting the SQL and additional metadata information. Check it out at https://go.aiven.io/ft-sql-query-optimizer

Since the beginning of my journey into the data world I’ve been keen on making professionals better at their data job. In the previous years that took the shape of creating materials in several different forms that could help people understand, use, and avoid mistakes on their data tool of choice. But now there’s much more into it: a trusted AI solution to help data professional in their day to day optimization job.

Testing a database performance is a must in every company. Despite everyone’s needs beings slightly different, a good starting point for PostgreSQL® database is using pgbench: a tool shipped with the PostgreSQL installation that allows you to stress test a local or remote database. This blog post showcases how to install (on a Mac) and use pgbench to create load on a remote PostgreSQL database on Aiven.

Some time ago I found an interesting database file suffix I never faced before: the .dbf and saw around that it was first introduced in 1983 with dBASE II. This article showcases how we can automatically generate the PostgreSQL table and fill it with data using Python and dbfread.

Everyone is now talking about AI, and modern databases like PostgreSQL® are increasingly being adopted in companies’ AI journey as sources of data or key pieces of the AI infrastructure. Moreover there’s a new set projects that are solving PostgreSQL problems with AI.

A boolean value should only contain two values, True or False, but is it correct? Usually people assume so, but sometimes miss the fact that there could be the absence of the value all-together. In databases this is absence is usually stored as NULL and this blog showcases how to find them, use them properly and 11 lessons to learn to be a NULL Pro!

Keep in mind, it’s not only booleans that can contain NULL values, it’s all the columns where you don’t define a NOT NULL constraint!

You have a JSON dataset that you want to upload to a PostgreSQL table containing properly formatted rows and columns… How do you do it?

All the main sources like my own blog and others tell you to load the JSON in a dedicated temporary table containing a unique JSON column, then parse it and load into the target table. However there could be another way, avoiding the temp table!

How to load StackOverflow StackExchange data in a PostgreSQL® database!

Need to extract a specific substring out of a text in PostgreSQL®? Read here how!

Last week the good old Gunnar Morling launched an interesting challenge about ordering 1 billion rows in Java. Like my ex colleague and friend Robin Moffat, I’m not at all a Java expert, and while Robin used DuckDB to solve the challenge, I did the same with PostgreSQL and ClickHouse.

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB. This doc showcases how to load JSON data into a column.

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, and provides two different ways to tabulate a JSON to a record:

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, and provides two different ways to tabulate a JSON to a recordset:

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use the jsonb_set function to remove the null values for JSONB columns.

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use the - operator to remove an item from an array.

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, and provides two different ways to remove fields from a JSON document:

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use the json_strip_nulls (jsonb_strip_nulls for JSONB) function to remove the null values.

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use the opearator || to concatenate two JSON objects.

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use the function json_object (jsonb_object for JSONB) to create a JSON object from an array of key/value pairs.

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use the function json_object (jsonb_object for JSONB) to create a JSON object from two arrays of keys and values.

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use the function array_to_json (array_to_jsonb for JSONB) to create a JSON array from an existing array.

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use the function json_build_array (jsonb_build_array for JSONB) to create a JSON array from a list of elements.

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use several functions to extract the keys and related values in tabular format.

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use the function to_json (to_jsonb for JSONB) to convert a table row to a JSON object.

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use the function json_pretty (jsonb_pretty for JSONB) to prettify the output.

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, and provides several different ways to parse arrays from a JSON document:

This series covers how to solve common problems on JSON datasets with PostgreSQL® and it includes (links will appear once the target pages are up):

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, and provides several different ways to extract fields from a JSON document:

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, you can use the function json_typeof (jsonb_typeof for JSONB) to extract the fields type.

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB. This doc showcases how to index a JSONB column with a GIN index.

PostgreSQL® offers two types of data types to handle JSON data, JSON and JSONB, and provides several different ways to check if a value/field from a JSON document:

PostgreSQL® offers two types of data types to handle JSON data:

• JSON stores the JSON as text, performing a validation on the correctness of the JSON syntax
• JSONB optimizes the JSON storage in a custom binary format. Therefore, on top of validating the correctness of the JSON format, time is spent to properly parse and store the content.

I started following the #PGSQLPhriday initiative a couple months back but never had the time to properly sit down and write due to conference traveling. Therefore I was super happy to be at home this week and find out that PGSQL Phriday #003 theme is about the PostgreSQL community!

How to define a database connection in a JSON file that can be imported in PG Admin 4