DEV Community: Chiazam Ochiegbu

Communicating with PostgreSQL Using LibPQ

Chiazam Ochiegbu — Thu, 26 Oct 2023 01:30:33 +0000

The libpq library is a PostgreSQL client library to communicate with the PostgreSQL server. The purpose of this chapter is to write the C program to connect to the PostgreSQL server and execute queries in the C programming language. In this chapter, we will explore communication with PostgreSQL using libpq, which includes learning about all the library functions and their utilization with sufficient C code examples. We will also discuss the blocking and nonblocking behavior of libpq functions.

Connecting and disconnecting to PostgreSQL

The libpq library provides multiple functions and mechanisms to connect to the PostgreSQL server. We will discuss each and every function that is involved in establishing a connection to the backend. An application can have multiple connections with the PostgreSQL database, but needs to maintain the PGconn pointer for each and every connection. The PGconn pointer is a connection object and a function used to establish a connection. It returns the pointer that the application needs to store and use for subsequent functions to query the database. This object must be closed when the connection to the server is no longer needed because the code must release resources that the PostgreSQL runtime allocates.

Using PQconnectdb

The PQconnectdb function is the most basic function to connect to the PostgreSQL backend. This function takes only one parameter: the conninfo string. The syntax for PQconnectdb is as follows:

PGconn *PQconnectdb (const char *conninfo);

The conninfo string is a space-delimited string that contains the keyword value pairs, for
example,keyword = 'foo'.Hereisthelistofallconninfokeywords:

hostaddr: This is the IP address of the PostgreSQL server
host: This is the hostname of the PostgreSQL server; it can be the name of the path of the Unix domain socket. The default value is the /tmp directory

Note

If the operating system does not support Unix domain sockets, the default value is localhost.

port: This is the port number of the PostgreSQL server; the - default port is 5432 dbname: This is the name of the database we need to connect to; the default value for this is the username
user: This is the username to connect as; the default is the - operating system user password: This is the password used during authentication
connect_timeout: The connect_timeout keyword shows how long the PQconnectdb function waits before giving up

Here are some more options that are not commonly used:

- client_encoding
- options
- application_name
- fallback_application_name
- keepalives
- keepalives_idle
- keepalives_interval
- keepalives_count
- tty
- sslmode
- disable
- allow
- verify-ca
- verify-full
- requiressl
- sslcert
- sslkey
- sslrootcert
- sslcrl
- requirepeer
- krbsrvname
- gsslib
- service

The details of these values can be found at http://www.postgresql.org/docs/9.4/static/libpq- connect.html#LIBPQ-PARAMKEYWORDS.

Note

If you see the following kind of error, then it is due to an error in the conninfo string: missing "=" after "port" in connection info string.

We'll explore other connection methods in the next article.

Dealing with Large Objects in Postgresql

Chiazam Ochiegbu — Thu, 26 Oct 2023 00:52:58 +0000

Databases provide data types as suitable containers to store values accordingly. You use the int data type to store numeric values and char and varchar data types for string values. Each data type has its own limitations with respect to the size and type of data it can store. A database solution model will be based on real-life problems, therefore these are not the only types of data that you will always confront. We do not live in the ice age anymore; we have to store large-sized images, audio, and video files and varchar is certainly not the answer to this. Objects that require huge storage sizes and can’t be entertained with simple available data types are usually referred to as Large Objects (LOs) or Binary Large Objects (BLOBs). To handle these LOs, you need a LO storage mechanism that can store them easily and access them efficiently.

Let's discuss the following

Why large objects?
PostgreSQL large objects
Large objects in action
Manipulating large objects through the libpq client interface library

Why large objects?

We will see in a more comparative mode why and where we need large objects.
You can categorize large objects in general, based on the structure or type of data. The types you usually come across are as follows:

Simple
Complex
Semi-structured
Unstructured

Among the first two, simple-structured data is related to data that can be organized in simple tables and data types and complex-structured data is the one that deals with requirements such as that of user-defined data types.
In the age of the Internet, the types mentioned previously are not the only forms of data that you have to handle; you have XML and JSON as well. It’s not interpreted by a relational database in a general way. This type of data can be categorized as semi- structured. Again, referring to storage of images, audio, and videos that are used massively today and can’t be stored in the same way as the first three types of data because they can’t be broken down into smaller logical structures for interpretation by standard means. It is hence unstructured data and needs a different mechanism to handle them.
PostgreSQL answers your problems with the feature of large objects that store objects of considerably huge size, and it’s been there since the release of the PostgreSQL. Good things happened and over the time it’s got even better.

PostgreSQL large objects

Interestingly, PostgreSQL provides two ways to store large objects with respect to each requirement you have to meet. They are as follows:

Implementation of the BYTEA data type
Implementation of large object storage

Though our area of interest here has been large objects, yet we will skim through some characteristics of BYTEA. It is similar to VARCHAR and text character strings, yet it has a few distinctive features as well. It can store raw or unstructured data, but character strings do not. It also allows storing of null values. VARCHAR does not permit storing zero octets, other octets, or sequences of octet values that are not valid as per database character set encoding. While using BYTEA, you can manipulate actual raw bytes, but in the case of character strings, processing is dependent on locale setting.

BYTEA when compared with large object storage comes with a big difference of storage size; each BYTEA entry permits storage of 1 GB whereas large objects allow up to 4 TB. The large object feature provides functions that help you manipulate external data in a much easier way that could be quite complex when doing the same for BYTEA.

The preceding discussion was a small comparison and analysis to show you the available choices in PostgreSQL to store binary data using BYTEA or large object storage. A requirement is the best judge to opt any of these.

Implementing large objects

Things are well remembered when they are listed and this is how we will remember PostgreSQL large objects implementation in our memory:

Large objects, unlike BYTEA, are not a data type but an entry in a system table.
All large objects are stored in the pg_largeobject system table.
Each large object also has a corresponding entry in the pg_largeobject_metadata system table.
Large objects are broken up into chunks of default size and further stored as rows in the database.
These chunks in rows are B-tree indexed; hence, this ensures fast searches during read/write operations.
From PostgreSQL 9.3 onwards, the maximum size of a large object in a table can be 4 TB.
Large objects are not stored in user tables; rather, a value of the Object Identifier (OID) type is stored. You will use this OID value to access the large object. So, when you have to access a large object, you will reference the OID value that points to a large object present on the pg_largeobject system table.
PostgreSQL provides the read/write Application Program Interface (API) that offers client- and server-side functions. Using this API, you can perform operations such as create, modifying, and delete on large objects. OIDs are used in this function as a reference to access large objects, for example, to transfer the contents of any file to the database or to extract an object from the database into a file.
From PostgreSQL 9.0 onwards, large objects now have an associated owner and a set of access permissions. Retrieving data using these functions gives you the same binary data you added. Examples of the functions are lo_create(), lo_unlink(), lo_import(), and lo_export().
PostgreSQL provides the ALTER LARGE TABLE feature to change the definition of a large object. Remember that its only functionality is to assign a new owner.

Note
Functions for large objects must be called in a transaction block, so when auto-commit is off, make sure that you issue the BEGIN command explicitly.

List Partition in Postgresql

Chiazam Ochiegbu — Sun, 15 Oct 2023 08:12:33 +0000

List partition is very much similar to range partition. The table is partitioned by explicitly listing which key values appear in each partition. In list partition, each partition is defined and designated based on a column value in one set of value lists, instead of one set of adjoining ranges of values. This will be done by defining each partition by means of the values IN (value_list) syntax, where value_list is a comma-separated list of values.
In the preceding article, we have successfully created the range partition. Now for the purpose of list partition, we have to do the same task again. We will create a master table that will have a sales record along with the city information. The list partition will use the city column as a base to create the child partitions.
Let’s create a master table first in the following manner:

CREATE TABLE sales_record_listpart
  (
  id NUMERIC primary key,
  sales_date date,
  sales_amount NUMERIC,
  city text
);

Let’s create the child tables, but this time on the basis of the city list.

Create the sales_record_list1 table in the following manner:

 CREATE TABLE sales_record_list1
  (
  PRIMARY KEY (id, city),
  CHECK (city IN ('new york', 'sydney'))
  )
  INHERITS (sales_record_listpart);

Now, create the sales_record_list2 table in the following manner:

CREATE TABLE sales_record_list2
  (
  PRIMARY KEY (id, city),
  CHECK (city IN ('Islamabad', 'Boston', 'London'))
  )
  INHERITS (sales_record_listpart);

Let’s create the index for the sales_record_list1 table:

CREATE INDEX list1_index ON sales_record_list1(city);
Let’s create the index for the sales_record_list2 table:
warehouse_db=# CREATE INDEX list2_index ON sales_record_list2 (city);

Now, create the trigger function in the following manner:

CREATE OR REPLACE FUNCTION sales_record_list_insert()
RETURNS TRIGGER AS $$
BEGIN
  IF (NEW.city IN ('new york', 'sydney')) THEN
    INSERT INTO sales_record_list1 VALUES (NEW.*);
  ELSEIF (NEW.city IN ('Islamabad', 'Boston', 'London')) THEN
    INSERT INTO sales_record_list2 VALUES (NEW.*);
  ELSE
    RAISE EXCEPTION 'CITY not present in this lists';
  END IF;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

In the end, we need to create the supporting trigger in the following manner:

CREATE TRIGGER sales_day_trigger
  BEFORE INSERT ON sales_record_listpart
  FOR EACH ROW
  EXECUTE PROCEDURE sales_record_list_insert();

You can verify that the partition is linked with the master table using the following command:

Column	Type	Modifiers	Storage
id	numeric	not null	main
sales_date	date
sales_amount	numeric
city	text

Indexes:

    "sales_record_listpart_pkey" PRIMARY KEY, btree (id)

Triggers:

```Sales_day_trigger BEFORE INSERT ON sales_record_listpart FOR EACH ROW

EXECUTE PROCEDURE sales_record_list_insert()



**Child tables:** `sales_record_list1, sales_record_list2`
**Has OIDs:** `no`


The preceding output is a partial output of the \d+ command
Now, let’s do some quick inserts and verify that our list partition is also working how we expect it do so:

INSERT INTO sales_record_listpart
(id, sales_date, sales_amount, city)
VALUES
(1,'15-APR-2008',1200,'sydney');

INSERT INTO sales_record_listpart
(id, sales_date, sales_amount, city)
VALUES (2,'15-APR-2008',1500,'Boston');

INSERT INTO sales_record_listpart
(id, sales_date, sales_amount, city)
VALUES (3,'16-APR-2008',1800,'Islamabad');

INSERT INTO sales_record_listpart
(id, sales_date, sales_amount, city)
VALUES
(4,'20-APR-2008',1300,'new york');




>> When you perform the preceding INSERT statements, you will observe that the INSERT query returns the `INSERT 0 0 message;` this is because the record is inserted in the child tables instead of the master tables.

Perform `SELECT` on `select_record_list1` to verify that the record is inserted as expected in the following manner:

SELECT * FROM sales_record_list1;





 id | sales_date | sales_amount |   city
----+------------+--------------+----------
  1 | 2008-04-15 |         1200 |  sydney
  4 | 2008-04-20 |         1300 | new york
(2 rows)


Perform `SELECT` on `select_record_list2` to verify that the record is inserted as expected in the following manner:

SELECT * FROM sales_record_list2;




 id | sales_date | sales_amount |   city
----+------------+--------------+-----------
  2 | 2008-04-15 |         1500 | Boston
  3 | 2008-04-16 |         1800 | Islamabad
(2 rows)

Partitioning in PostgreSQL

Chiazam Ochiegbu — Fri, 13 Oct 2023 05:11:18 +0000

What is Partitioning?

The process of dividing the tables into smaller manageable parts is called partitioning, and these smaller manageable parts are called partitions.
In the process of partitioning, we divide one logical big table into multiple physical smaller parts.

Why do we partition?

Before actually creating the partitions, let’s understand why we need partitions. The first and most demanding reason to use partitions in a database is to increase the performance of the database. This is achieved by partition-wise joins; if a user’s queries perform a lot of full-table scans, partitioning will help vastly, because partitions will limit the scope of this search. The second important reason to partition is ease of managing large tables.
Partitioning always helps manage large objects. Although it is applicable to objects of any size, the advantages are more apparent in large tables. When a user recreates an index on a nonpartitioned table, the only option is to build the entire index in one statement. On the other hand, if the table is partitioned, the user can rebuild partitions of the local indexes one at a time.

Creating a table partition

PostgreSQL supports table partitioning through table inheritance, which means every partition will be created as a child table of a single parent table. Partitioning is performed in such a way that every child table inherits a single parent table. The parent table will be empty; it exists just to describe the whole dataset. Currently in PostgreSQL, partitioning can be implemented in range partitioning or list partitioning.

Steps to create partition in Postgrsql

Createthemastertable 2.Create multiple child tables without having an overlapped table constraint
Create indexes
Create a trigger function to insert data into childtables
Enable the constraint exclusion

We will focus on creating a range partition in this article.

Range partition

The range partition is the partition in which we partition a table into ranges defined by a single column or multiple columns. When defining the ranges, the user will have to take care that ranges should be connected and not overlap with each other; moreover, ranges must be defined using the < value operator. For instance, one can partition by date ranges or ranges of identifiers for specific business objects.

While working with Range Partitioning, it is important to note the following:

In PostgreSQL, range partitioning is a method of dividing a table into multiple partitions based on a specified range of values in one or more columns.
Each partition holds a subset of the data based on the range values, making it an efficient way to manage and query large datasets.
Range partitioning is particularly useful when your data can be logically divided into non-overlapping ranges, such as date ranges or numerical ranges.

Creating the master table

So, let’s start by creating a simple master table. This table will contain data on sales stored on a daily basis. This will be done in the following manner:

CREATE TABLE sales_record
  (
  id NUMERIC PRIMARY KEY,
  sales_amount NUMERIC,
  sales_date DATE NOT NULL DEFAULT CURRENT_DATE
  );

This is the parent table; all the records that a user inserts in this table will move to the child table based on the criteria of sales_date that we are going to create in the following section.

Creating a range partition table

To implement the partition, we will create child tables. All child tables will inherit the master table. We will add the CHECK constraint for dates because we want to make sure that we have only the correct data on each partition. Partitions starts from the date 2014-01-01 and end on the date 2014-12-31. Each partition will have two months data.

CREATE TABLE sales_record_m1_to_m2
  (
  PRIMARY KEY (id, sales_date),
  CHECK (sales_date >= DATE '2014-01-01'
  AND sales_date < DATE '2014-03-01')
  )
  INHERITS (sales_record);

This child table will contain data of January and February.
Now, create the sales_record_m3_to_m4 child table as follows:

CREATE TABLE sales_record_m3_to_m4
  (
  PRIMARY KEY (id, sales_date),
  CHECK (sales_date >= DATE '2014-03-01'
  AND sales_date < DATE '2014-05-01')
  )
  INHERITS (sales_record);

This child table will contain data of March and April.

Create the sales_record_m5_to_m6 child table as follows:

CREATE TABLE sales_record_m5_to_m6
  (
  PRIMARY KEY (id, sales_date),
  CHECK (sales_date >= DATE '2014-05-01'
  AND sales_date < DATE '2014-07-01')
  )
  INHERITS (sales_record);

This child table will contain data of May and June.

Create the sales_record_m7_to_m8 child table as follows:

CREATE TABLE sales_record_m7_to_m8
  (
  PRIMARY KEY (id, sales_date),
  CHECK (sales_date >= DATE '2014-07-01'
  AND sales_date < DATE '2014-09-01')
  )
  INHERITS (sales_record);

This child table will contain data of July and August.

CREATE TABLE sales_record_m9_to_m10
  (
  PRIMARY KEY (id, sales_date),
  CHECK (sales_date >= DATE '2014-09-01'
  AND sales_date < DATE '2014-11-01')
  )
  INHERITS (sales_record);

This child table will contain data of September and October.

Now, create the sales_record_m11_to_m12 child table as follows:

CREATE TABLE sales_record_m11_to_m12
  (
  PRIMARY KEY (id, sales_date),
  CHECK (sales_date >= DATE '2014-11-01'
  AND sales_date < DATE '2015-01-01')
  )
  INHERITS (sales_record);

This child table will contain data of November and December.

You can verify that the tables are linked and the partition is successfully created using the following query:

DB_name=# \d+ sales_record

In the next article, we'll dive into operations we can perform in a partitioned table.

Types of Indexes in Postgresql

Chiazam Ochiegbu — Wed, 13 Sep 2023 22:47:40 +0000

A PostgreSQL database supports the following index types:

single-column index
multicolumn index
partial index
unique index
expression index
implicit index and
concurrent index.

The single-column index

The single-column index is utilized when a table represents mostly a single category of data, or queries span around only a single category in the table. Normally, in a database design, tables represent a single category of data, so generally a single-column (category) index is utilized.
Its syntax is as follows:

CREATE INDEX index_name ON table_name (column);

Let's consider the following example:

warehouse_db=# SELECT COUNT(*) FROM item WHERE item_id = 100;

In this example, the rows are required where item_id is 100. If there is no index defined, then the whole table will be scanned to find the rows where item_id is 100, which is an expensive operation. If you look closely, only a single column is utilized in the WHERE clause, thereby creating an index on a single column, which is item_id in the case of the preceding query. This optimizes that query.
Now, consider the following example:

warehouse_db=# CREATE INDEX item_index ON item (item_id);

The result can be seen using the following statement:

warehouse_db=# \d item;

            Table "item"

Column	Type	Modifiers
item_id	integer
item_name	text
item_price	numeric
item_data	text

Indexes:

"item_index" btree (item_id)

Now, we have created a B-tree index, item_index, on a table item’s item_id column, so now we try the same SELECT query again and see how much time it takes after index creation.
Creating an index on a table reduces the SELECT query time drastically, as shown in the following output:

warehouse_db=# SELECT COUNT(*) FROM item WHERE item_id = 100;

There is an obvious timing difference with and without the index. The same query without the index took significantly longer time to execute.

Understanding Indexes in Postgresql

Chiazam Ochiegbu — Fri, 08 Sep 2023 21:22:31 +0000

What is an index?

An index is a structure utilized for quick data retrieval operations. In the database world, an index is associated with a table and used to efficiently locate data without having to investigate every row in a database table. If a table does not have an index, then a full table scan is needed to find a record, which is very costly in terms of disk I/O and CPU utilization.
A full table scan is the process of sequentially reading every record from disk, checking against search criteria, and building a result set.

It is good to note that an index amends the performance of database operations at the cost of extra replicas of data. As an index stores the extra copy of data for more speedy access, an index is the routine way to amend the performance of the database.
In a nutshell, an index is a quick access path to a single row of a table in the database. A database index is similar to a book index where any specific information can be located by looking at the index page to avoid the full search of the book, which is an exhaustive operation. Similarly, a database index is created to minimize table traversal and maximize performance.
An index can be created utilizing a single column or multiple columns of a table. Once the index is created, then there is no further intervention needed; the index will be automatically updated on each DML operation on the table. After creating the index, it is the planner’s decision to utilize the index in lieu of sequential scans based on cost.

How to create an index

The CREATE INDEX command is used to create an index the basic syntax of creating an index on a table is as follows:

CREATE INDEX index_name ON table_name (column_name);

Here is an example of creating an index on the item_id column of the item table:

warehouse_db=# CREATE INDEX item_idx ON item (item_id);

The result can be seen using the following statement:

warehouse_db=# \di item_idx;
               List of relations
 Schema |   Name   | Type  | Owner    | Table
--------+----------+-------+----------+-------
 public | item_idx | index | postgres | item
(1 row)

The index name is optional; if the index name is not specified, PostgreSQL generates the index name using the table name and the column name. In the preceding example, we have specified the index name. In the following example, PostgreSQL generates the index name using the table name and column name, which is item_item_id_idx:

warehouse_db=# CREATE INDEX ON item (item_id);

CREATE INDEX

The result can be seen using the following statement:

warehouse_db=# \di item_item_id_idx;
                 List of relations
 Schema |       Name       | Type  | Owner    | Table
--------+------------------+-------+----------+-------
 public | item_item_id_idx | index | postgres | item
(1 row)

Creating an index on a large table can take a long time; for example, in the preceding example, the query has to scan all the records and generate the index data. On a larger table, this process can take time.

Privileges in PostgreSQL

Chiazam Ochiegbu — Thu, 07 Sep 2023 07:43:20 +0000

What are privileges in Postgresql?

Privileges refer to the permissions and rights granted to database objects, such as tables, views, schemas, and functions, allowing users and roles to perform various actions on these objects
In PostgreSQL, multiple privileges are present for every object that is created. By default, the owner (or a superuser) of an object has all the privileges on it. In PostgreSQL, the following types of privileges are present:

SELECT
INSERT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER
CREATE
CONNECT
TEMPORARY
EXECUTE
USAGE

There are different privileges associated with different objects. For instance, the EXECUTE privilege is associated with procedure. The GRANT command is used to grant any privilege to any user. It allows you to control what actions users can perform on the object. The syntax for granting privileges is as follows:

GRANT privilege_name(s) ON object_name TO user_name_or_role;

To grant the EXECUTE privilege on a procedure named "my_function" to a user "my_user," you would use:

GRANT EXECUTE ON FUNCTION my_function TO my_user;

Similarly, to take back privileges, the REVOKE command is used. The REVOKE command is used to revoke previously granted privileges from a user or role. It allows you to take back specific privileges that were previously assigned. The syntax for revoking privileges is shown below:

REVOKE privilege_name(s) ON object_name FROM user_name_or_role;

To revoke the previously granted EXECUTE privilege on the "my_function" procedure from the "my_user," you would use:

REVOKE EXECUTE ON FUNCTION my_function FROM my_user;

It is important to note that managing privileges in PostgreSQL is crucial for access control in your data and ensuring the security and integrity of your database is intact.

Exclusion Constraints in Postgresql

Chiazam Ochiegbu — Mon, 21 Aug 2023 17:59:09 +0000

Exclusion constraints

An exclusion constraint is used when comparing two rows on nominative columns or expressions using the nominative operators. The result of the comparison will be false or null.

Consider the following example in which the conflicting tuple is given the AND operation together:

warehouse_db=# CREATE TABLE movies
  (
  Title TEXT,
  Copies INTEGER
  );

Using the ALTER TABLE command,we get the following:

warehouse_db=# ALTER TABLE movies
  ADD EXCLUDE (title WITH=, copies WITH=);

We will create an exclusion constraint above the ALTER TABLE command. The conditions for a conflicting tuple are AND together. Now, in order for two records to conflict, we’ll use the following:

record1.title = record2.title AND record1.copies = record2.copies.

Next article, we'll consider primary and secondary key constraints.

not-null constraint in Postgresql

Chiazam Ochiegbu — Mon, 21 Aug 2023 17:50:45 +0000

A not-null constraint makes sure that a column must have some values and a value is not left as null. Drop the previously created tools table and create the tools table again using this constraint using the following example:

warehouse_db=# CREATE TABLE tools
  (
  tool_id INTEGER NOT NULL,
  tool_name TEXT,
  tool_class NUMERIC
  );

The preceding query will create a table with a not-null constraint on the tool_id column. We can apply the not-null constraint on as many columns as we can. Consider the following example:

warehouse_db=# CREATE TABLE tools
  (
  tool_id INTEGER NOT NULL,
  tool_name TEXT NOT NULL,
  tool_class NUMERIC
  );

The preceding query will create the tools table with not-null constraints on tool_id and tool_name.

In the next article, we'll discuss Exclusion constraints in postgresql.

Constraints in Postgresql

Chiazam Ochiegbu — Mon, 21 Aug 2023 17:46:42 +0000

PostgreSQL offers support for constraints and has coverage of multiple-level constraints. Constraints are used to enforce rules on data insertion in tables. Only data that complies with the constraint rules is allowed to be added to the table.

The constraints present in PostgreSQL are:

Unique constraints
Not-null constraints
Exclusion constrains
Primary key constraints
Foreign key constraints
Check constraints

We will explain all of these constraints one by one with supportive examples. Let’s start with the unique constraints.

Unique constraints

A unique constraint is a constraint that at the time of an insertion operation makes sure that data present in a column (or a group of columns) is unique with regard to all rows already present in the table. Let’s create a few tables using unique constraints in the following manner:

warehouse_db=# CREATE TABLE tools
  (
  tool_id INTEGER UNIQUE,
  tool_name TEXT,
  tool_class NUMERIC
  );

Alternatively, the same constraint can be declared at the end of all columns. For instance, this can look like the following:

warehouse_db=# CREATE TABLE tools
  (
  tool_id INTEGER,
  tool_name TEXT,
  tool_class NUMERIC,
  UNIQUE (tool_id)
);

When defining the unique constraints for a group of columns, all columns must be listed separately using commas. Consider the following example:

warehouse_db=# CREATE TABLE cards
  (
  card_id INTEGER,
  owner_number INTEGER,
  owner_name TEXT,
  UNIQUE (card_id, owner_number)
  );

The preceding query will create the cards table with a unique constraint implemented on the card_id and owner_number columns. Note that the unique constraint is not applicable on null values. This means that in the cards table, two records can have the same record if they have card_id and owner_number as null.

not-null constraint in Postgresql

warehouse_db=# CREATE TABLE tools
  (
  tool_id INTEGER NOT NULL,
  tool_name TEXT,
  tool_class NUMERIC
  );

The preceding query will create a table with a not-null constraint on the tool_id column. We can apply the not-null constraint on as many columns as we can. Consider the following example:

warehouse_db=# CREATE TABLE tools
  (
  tool_id INTEGER NOT NULL,
  tool_name TEXT NOT NULL,
  tool_class NUMERIC
  );

The preceding query will create the tools table with not-null constraints on tool_id and tool_name.

Exclusion Constraints in Postgresql

An exclusion constraint is used when comparing two rows on nominative columns or expressions using the nominative operators. The result of the comparison will be false or null.

Consider the following example in which the conflicting tuple is given the AND operation together:

warehouse_db=# CREATE TABLE movies
  (
  Title TEXT,
  Copies INTEGER
  );

Using the ALTER TABLE command,we get the following:

warehouse_db=# ALTER TABLE movies
  ADD EXCLUDE (title WITH=, copies WITH=);

record1.title = record2.title AND record1.copies = record2.copies.

Next article, we'll consider primary and secondary key constraints.

Short and Long Queries in PQSL

Chiazam Ochiegbu — Mon, 21 Aug 2023 17:30:47 +0000

Let us identify whether a query is a short query or a long query. You will learn how to identify short queries.

What is a short query? First, it has nothing to do with the length of the SQL query.

A query is short when the number of rows needed to compute its output is small, no matter how large the involved tables are. Short queries may read every row from small tables but read only a small percentage of rows from large tables.

Example:
Let's consider a scenario where you have two tables: a small table named small_table and a large table named large_table. The goal is to identify whether a query is a short query based on the criteria you provided.

CREATE TABLE small_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE large_table (
    id SERIAL PRIMARY KEY,
    data VARCHAR(100)
);

Here's an example of a query that reads from both tables and follows the criteria for a short query:

-- Short query example
SELECT *
FROM small_table
UNION ALL
SELECT *
FROM large_table
WHERE id <= 100;

In this example, the query is considered "short" because even though it involves both tables, it reads every row from the small_table and only a small percentage of rows (those with id values less than or equal to 100) from the large_table.

SELECT *
FROM large_table
WHERE data LIKE '%something%';

In this case, the query involves only the large_table, but it reads potentially a large number of rows based on the LIKE condition, so it would not meet the criteria for a "short query."

Unique Indexes and Constraints in Postgresql

Chiazam Ochiegbu — Mon, 21 Aug 2023 17:06:20 +0000

What is an Index in `PSQL`?

An index is unique if for each indexed value there is exactly one matching row in the table.

There are several different ways to create a unique index. First, PostgreSQL automatically creates a unique index to support any primary key or unique constraint on a table.

What is the difference between a primary key and a unique constraint? A common misconception among SQL developers is that a primary key has to be an incrementing numeric value and that each table “has” to have a primary key. Although it often helps to have a numeric incremental primary key (called a surrogate key), a primary key does not have to be numeric, and moreover, it does not have to be a single-attribute constraint.

It is possible to define a primary key as a combination of several attributes; it just has to satisfy two conditions: the combination must be UNIQUE and NOT NULL for all of the participating attributes.
In contrast, unique constraints in PostgreSQL allow for NULL values.

A table can have a single primary key (though a primary key is not required) and multiple unique constraints. Any non-null unique constraint can be chosen to be
a primary key for a table; thus, there is no programmatic way to determine the best candidate for a table’s primary key

DEV Community: Chiazam Ochiegbu

Communicating with PostgreSQL Using LibPQ

Connecting and disconnecting to PostgreSQL

Using PQconnectdb

Note

Note

Dealing with Large Objects in Postgresql

Why large objects?

PostgreSQL large objects

Implementing large objects

List Partition in Postgresql

Partitioning in PostgreSQL

What is Partitioning?

Why do we partition?

Creating a table partition

Steps to create partition in Postgrsql

Range partition

Creating the master table

Creating a range partition table

Types of Indexes in Postgresql

The single-column index

Indexes:

"item_index" btree (item_id)

Understanding Indexes in Postgresql

What is an index?

How to create an index

CREATE INDEX

Privileges in PostgreSQL

What are privileges in Postgresql?

Exclusion Constraints in Postgresql

Exclusion constraints

not-null constraint in Postgresql

Constraints in Postgresql

Unique constraints

not-null constraint in Postgresql

Exclusion Constraints in Postgresql

Short and Long Queries in PQSL

Unique Indexes and Constraints in Postgresql

What is an Index in PSQL?

What is an Index in `PSQL`?