Row Counts of Tables in a SQL Schema & Database - PostgreSQL and YugabyteDB

Row Counts of Tables in a SQL Schema & Database - PostgreSQL and YugabyteDB

See how to get total row counts of data in tables across various dimensions (per-table, per-schema, and in a given database)–a useful SQL technique!

Getting total row counts of data in tables across various dimensions (per-table, per-schema, and in a given database) is a useful technique to have in one’s tool belt of SQL tricks. While there are a number of use cases for this, my scenario was to get the per-table row counts of all tables in PostgreSQL and YugabyteDB as a first sanity check after migrating an application with the pre-existing data from PostgreSQL to YugabyteDB.

This blog post outlines how to get the following row counts of tables in a database:

  • Row counts broken down per table in the schema
  • Aggregate row counts per schema of the database
  • Aggregate row count across all tables in the database

We will create an example database, import two popular SQL datasets – Northwind and SportsDB, and run through the above scenarios on these example databases.

The examples in this blog post, which are essentially dynamic SQL queries on the system catalog tables, must be done with superuser privileges. Also, note that the programmatic generation of SQL queries using catalog tables needs to handle exotic names properly. An instance of a table and a column with an exotic name is shown below.

create table "Some Exotically Named Table"(
  k bigserial primary key,
  "Some Exotically Named Column" text
);

While this post does not explicitly discuss the challenges posed by the example above, the SQL functions below handle these cases correctly and do so by incorporating some of the important and well-known techniques necessary to prevent SQL injection attacks.

Create an example database

In order to create a test setup, I simply installed YugabyteDB on my laptop, created a database example, and loaded the Northwind dataset – all of which only took a few minutes to do. For the purpose of simplicity, we’re going to use the default yugabyte user for the operations below. However, creating a dedicated user for each of these datasets with the appropriate privileges is the recommended best practice.

  • Create an example database and connect to it.
yugabyte=## CREATE DATABASE example;
yugabyte=## \c example
  • Create the Northwind tables and import the dataset into the northwind schema.
example=## CREATE SCHEMA northwind;
example=## SET SCHEMA 'northwind';
example=## \i northwind_ddl.sql
example=## \i northwind_data.sql
  • You can verify that the tables have been created by running the following command.
example=## \d
                   List of relations
  Schema   |          Name          | Type  |  Owner
-----------+------------------------+-------+----------
 northwind | categories             | table | yugabyte
 northwind | customer_customer_demo | table | yugabyte
 northwind | customer_demographics  | table | yugabyte
 northwind | customers              | table | yugabyte
 northwind | employee_territories   | table | yugabyte
 northwind | employees              | table | yugabyte
 northwind | order_details          | table | yugabyte
 northwind | orders                 | table | yugabyte
 northwind | products               | table | yugabyte
 northwind | region                 | table | yugabyte
 northwind | shippers               | table | yugabyte
 northwind | suppliers              | table | yugabyte
 northwind | territories            | table | yugabyte
 northwind | us_states              | table | yugabyte
(14 rows)
example=## CREATE SCHEMA sportsdb;
example=## SET SCHEMA 'sportsdb';
example=## \i sportsdb_tables.sql
example=## \i sportsdb_indexes.sql
example=## \i sportsdb_inserts.sql
example=## \i sportsdb_constraints.sql
example=## \i sportsdb_fks.sql

databases distributed sql how to postgresql sql

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Distributed SQL: An Evolution of the Database

The next step in the evolution of database architecture is distributed SQL. Take a look at some of the characteristics here.As organizations transition to the cloud, they eventually find that the legacy relational databases that are behind some of their most critical applications simply do not take advantage of the promise of the cloud and are difficult to scale.

Distributed SQL Tips and Tricks for PostgreSQL and Oracle DBAs – Aug 2020

In this article, take a look at distributed SQL tips for PostgreSQL and Oracle DBAs. Welcome to this week’s tips and tricks blog where we explore both beginner and advanced YugabyteDB topics for PostgreSQL and Oracle DBAs. First things first, for those of you who might be new to either distributed SQL or YugabyteDB.

Distributed SQL Change Management With Liquibase and YugabyteDB on GKE

In this article, look at database SQL change management with Liquibase and Yugabyte on GKE. Liquibase is an open source and extensible change management project that supports a variety of databases including Snowflake, MySQL, and PostgreSQL via JDBC.

Getting Started With SQLPad and Distributed SQL on Google Kubernetes Engine

See how to install a 3 node YugabyteDB cluster on Google Kubernetes Engine, build the sample Northwind database, build and configure SQLPad, and more! In this blog post we’ll show you how to: Install a 3 node YugabyteDB cluster on Google Kubernetes Engine, Build the sample Northwind database, Build and configure SQLPad, Start the required SQLPad processes, Launch the SQLPad UI and issue a test query to validate the deployment.