This post examines the work of producing analytics/visualizations and the role software plays. Business Intelligence (BI) software, such as Power BI, Tableau, and SAP Dashboards are a few examples of software with a lot of buzz. The marketing folks in that industry would have us believe their software enables analysts to point and click at data and enable magic. This magic feature provides instant access to interactive dashboards, drill-down functionality, and more. Don't forget, the data will be reliable, accurate, easy to share, easy to secure, and cross-platform (mobile-friendly) too!

Reality check

Data visualization software in 2018 doesn't have the magic feature working yet. I believe it takes at least as much human effort today to produce a great visualization as it did a decade ago. The improvements in the software are not making the process quicker or easier. The reality is that working with data isn't for everyone, and #DataIsHard.

I recently attended a PASS webinar by Scot Reagin who stated:

"90% of users are not self-service capable."

Continue Reading

This post provides an introduction to building a data dictionary directly in our PostgreSQL databases. The steps outlined here are specific to PostgreSQL, though every database platform has these basics components in place.

My 2022 post Postgres Data Dictionary for everyone shows how to use the PgDD extension to make it easy to query data dictionary details using standard SQL sytnax.

What is a Data Dictionary?

Documentation is a good thing. Data dictionaries are one important component of documenting your databases. A data dictionary is a common tool used to provide database-specific documentation to analysts, developers, and other business users. A good data dictionary provides insights into a database's structure, constraints, relationships, and sources of data found inside a system.

Continue Reading

In general, the core PostgreSQL community doesn't latch onto GUI tools. That said, pgAdmin is one of the more popular GUI options for PostgreSQL. I hadn't used pgAdmin for quite a while (the transition from III to 4 was... rough), but a recent feature caught my eye: Geometry viewer.

This feature, available in pgAdmin 4 v3.3, allows you to see your PostGIS data in a separate tab directly in pgAdmin. When you execute a query that includes PostGIS data, pgAdmin overlays your data on the OpenStreetMap background in the geometry viewer.

Awesome!!

Boston GIS introduced the basics of how the Geometry Viewer functions. This post illustrates why I think this feature is so cool.

DBeaver now has a geometry viewer feature too!

Why is geometry viewer awesome?

Spatial (PostGIS) data typically doesn't translate well in most SQL tools. pgAdmin's new geometry viewer provides a dead-simple way to visualize, verify and troubleshoot spatial data in their databases. This feature is targeted for database professionals who support PostGIS databases.

Continue Reading

The database is one of the most critical components of most software projects. Planning for your database project should not be taken lightly. Failure rates in IT projects are unfortunately high; much of that problem is due to a lack of proper planning and design. Most of the database anti-patterns I previously wrote about can be avoided by understanding the system you need to build and designing it appropriately.

This post uses the PiWS project to illustrate the Define and Design approach I use throughout the initial development phases of any new database project. Most projects go through multiple iterations of define and design.

This post is part of the series PostgreSQL: From Idea to Database. This series uses the PiWS project for our example project, to read more about the PiWS, see my introductory post.

Project management, not optional

Anyone who has worked on a project with more than one person to design and build anything has heard this: "I forgot to tell you..." followed by some various change requirements.

Continue Reading

Update: This bug is fixed in the latest supported version's minor releases. Upgrade to fix this 🐛 bug!

I ran into a problem when moving a database from a production PostGIS-enabled PostgreSQL server to a development server for testing. Turns out, what I found is a bug in pg_dump related to the XML data type. The problem encountered is filed under bug #15342. Tom Lane summarized the issue:

"There are two problems here: pg_dump neglects to force a safe value of xmloption for the restore step, plus there doesn't seem to be a safe value for it to force :-(."

The rest of this post explores what the problem is, how to tell if you are affected, and your options if you find yourself in this group.

• Who does this affect?
• Data to reproduce the bug
• Check for problematic XML data
• Workaround for part of the problem
• PostgreSQL and XML, and bug #15342
• What to do?

Continue Reading