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:
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.
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.
yugabyte=## CREATE DATABASE example; yugabyte=## \c example
example=## CREATE SCHEMA northwind; example=## SET SCHEMA 'northwind'; example=## \i northwind_ddl.sql example=## \i northwind_data.sql
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
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.
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.
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.
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.
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.