Listado de la etiqueta: PostgreSQL


PostgreSQL is an open-source object-oriented based database system. It is a powerful database system that supports both relational and non-relational data types. The Boolean data type is a commonly used data type that can accept three types of values: True, False, and NULL. The short form of this data type is bool and one byte is used to store the Boolean data. The True value of the Boolean data can also be denoted by ‘yes’, ‘y’, ‘true’, and 1. The False value of the Boolean data can also be denoted by ‘no’, ‘n’, ‘false’, and 0.

Pre-requisites:

You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:

$ sudo apt-get -y install postgresql postgresql-contrib

$ sudo systemctl start postgresql.service

Run the following command to login to PostgreSQL with root permission:

Use of Boolean data type in PostgreSQL tables:

Before creating any table with the Boolean data type, you have to create a PostgreSQL database. So, run the following command to create a database named ‘testdb’:

# CREATE DATABASE testdb;

The following output will appear after creating the database:

Example-1: Create a table using the Boolean data type

Create a table named ‘technicians’ in the current database with three fields. The first field name is tech_id, the data type is an integer and it is the primary key of the table. The second field name is name and the data type is character. The third field name is available and the data type is Boolean.

# CREATE TABLE technicians (

    tech_id INT NOT NULL PRIMARY KEY,
    name CHARACTER(10) NOT NULL,
    available BOOLEAN NOT NULL

);

The following output will appear if the table is created successfully:

Run the following command to INSERT a record into the technicians table where ‘true’ is used for the Boolean value:

# INSERT INTO technicians VALUES (1, ‘Esquivar Ali’, ‘true’);

The following output will appear after executing the above insert query:

Run the following INSERT command to insert a record into the technicians table where 0 is used for the Boolean value. The 0 is not acceptable for the Boolean value in PostgreSQL. So, an error message will appear.

# INSERT INTO technicians VALUES (2, ‘Kabir Hossain’, 0);

The following output will appear after executing the above insert query. The output shows an error message that indicates that the type of the 0 is Integer, not Boolean.

Run the following INSERT command to insert a record into the technicians table where ‘false’ is used for the Boolean value.

# INSERT INTO technicians VALUES (3, ‘Abir Hasan’, ‘false’);

The following output will appear after executing the above insert query:

Run the following INSERT command to insert a record into the technicians table where ‘t’ is used for the Boolean value:

# INSERT INTO technicians VALUES (5, ‘Rebeka Ali’, ‘t’);

The following output will appear after executing the above insert query:

Example-2: Create a table using Bool data type

Create a table named ‘products’ in the current database with three fields. The first field name is id, the data type is an integer and it is the primary key of the table. The second field name is name and the data type is character. The third field name is physical_product, the data type is BOOL, and the default value of the field is ‘true’.

# CREATE TABLE products (

    id INT NOT NULL PRIMARY KEY,
    name CHARACTER(10) NOT NULL,
    physical_product BOOL NOT NULL DEFAULT ‘true’

);

The following output will appear if the table is created successfully:

Run the following command to insert a record into the products table where ‘f’ is used for the BOOL value:

# INSERT INTO products VALUES (1, ‘Antivirus’, ‘f’)

Run the following INSERT command to insert a record into the products table where no value is provided for the BOOL data. Here, the default value of the field will be inserted.

# INSERT INTO products VALUES (2, ‘Celador’);

The following output will appear after executing the above two insert queries:

Check the content of the tables:

Run the SELECT following select query to retrieve all records from the technicians table:

# SELECT * FROM technicians;

Run the SELECT following select query to retrieve all records from the technicians table where the value of the available field is ‘false’:

# SELECT * FROM technicians WHERE available = ‘false’;

Run the SELECT following select query to retrieve all records from the technicians table where the value of the available field is ‘t’ or ‘true’:

# SELECT * FROM technicians WHERE available = ‘t’ OR available = ‘true’;

The following output will appear after executing the above three ‘select’ queries. The output of the first query shows all records of the table. The output of the second query shows those records of the table where the value of the available field is ‘f’. The output of the third query shows those records of the table where the value of the available field is ‘t’.

Run the following select query to retrieve all records from the products table:

# SELECT * FROM products;

Run the following select query to retrieve all records from the products table where the value of the physical_product field is ‘True’:

# SELECT * FROM products WHERE physical_product = ‘True’;

The following output will appear after executing the above two ‘select’ queries. The output of the first query shows all records of the table. The output of the second query shows those records of the table where the value of the available field is ‘t’.

Conclusion:

Different uses of Boolean or BOOL data types in PostgreSQL tables have been shown in this tutorial by using multiple examples to clarify the purpose of using Boolean data types in the table of the PostgreSQL database.



Source link


The Enumerated or ENUM data type is used to select one value from the list of multiple values. The particular value will be selected from the drop-down list for the ENUM data type. The ENUM values are static, unique, and case-sensitive. So, the users have to select any value from the ENUM values. The input value that does not match with any ENUM value can’t be inserted into the ENUM field. This data type takes 4 bytes to store in the table. The ENUM data type is useful for storing those types of data that are not required to change in the future. It helps to insert valid data only. The uses of ENUM data type in PostgreSQL have been shown in this tutorial.

Pre-requisites:

You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:

$ sudo apt-get -y install postgresql postgresql-contrib

$ sudo systemctl start postgresql.service

Run the following command to login to PostgreSQL with root permission:

Uses of ENUM data type:

Before creating any table with the Boolean data type, you have to create a PostgreSQL database. So, run the following command to create a database named ‘testdb’:

# CREATE DATABASE testdb;

The following output will appear after creating the database:

Create and read the ENUM type:

Run the following CREATE command to create an ENUM type named account_status with three values:

# CREATE TYPE account_status AS enum(‘Pending’, ‘Inactive’, ‘Active’);

Run the following SELECT command to print the values of ENUM type that has been created before:

# SELECT UNNEST(enum_range(NULL:: account_status)) AS account_status;

The following output will appear after executing the above commands:

Rename the ENUM Type:

Run the following command to change the name of the ENUM type from ‘account_status’ to ‘status’:

# ALTER TYPE account_status RENAME TO STATUS;

Create a table using the ENUM data type:

Create a table named ‘account’ in the current database with three fields. The first field name is the username that is the primary key of the. The second field name is the name and the data type is VARCHAR (30). The third field name is address and the data type is TEXT. The fourth field name is email and the data type is VARCHAR (50). The fifth field name is a_status and the data type is ENUM that has been created earlier.

# CREATE TABLE account (

   username VARCHAR (20) PRIMARY KEY,
   name   VARCHAR (30),
   address   TEXT,
   email   VARCHAR (50),
   a_status   STATUS );

The following output will appear after executing the above command:

Insert data into the table:

Run the following INSERT query to insert three records into the account table. All values of the ENUM field are valid here:

# INSERT INTO account (username, name, address, email, a_status)

   VALUES
   (‘farhad1278’, ‘Farhad Hossain’, ‘123/7, Dhanmondi Dhaka.’, [email protected], ‘Active’),
   (‘nira8956’, ‘Nira Akter’, ’10/A, Jigatola Dhaka.’, [email protected], ‘Inactive’),
   (‘jafar90’, ‘Jafar  Iqbal’, ‘564, Mirpur Dhaka.’, [email protected], ‘Pending’);

The following output will appear after executing the above query:

Run the following INSERT query to insert a record into the account table but the value given for the ENUM field does not exist in the ENUM type:

# INSERT INTO account (username, name, address, email, a_status)

   VALUES
   (‘rifad76’, ‘Rifad Hasan’, ’89, Gabtoli Dhaka.’, [email protected], ‘Blocked’);

The following output will appear after executing the above query. The error has occurred in the output for giving an ENUM value that does not exist in the ENUM type.

Run the following SELECT command to read all records from the account table:

Run the following SELECT command to read those records of the account table that contain the ‘Active’ or ‘Pending’ value in the ENUM field:

# SELECT * FROM account WHERE a_status=‘Active’ OR a_status=‘Pending’;

The following output will appear after executing the above SELECT queries:

Change the ENUM value:

If any existing value of the ENUM type is changed then the ENUM field value of the table where that ENUM has been used will be changed also.

Run the following ALTER command to change ENUM value ‘Active’ to ‘Online’:

# ALTER TYPE STATUS RENAME VALUE ‘Active’ TO ‘Online’;

Run the following SELECT command to check the records of the account table after changing the ENUM value:

The following output will appear after executing the above commands. There was one record in the table that contains the ENUM value, ‘Active’. The output shows that the ‘Active’ value has been changed to ‘Online’ after changing the ENUM value.

Add new value to an existing ENUM data type:

Run the following ALTER command to add a new item into the ENUM type named status:

# ALTER TYPE STATUS ADD VALUE ‘Blocked’;

Run the following SELECT query that will print the list of ENUM types after adding the new value:

# SELECT UNNEST(enum_range(NULL:: STATUS)) AS account_status;

The following output will appear after executing the above query:

A new value can be inserted before or after the particular value of an existing ENUM type. Run the first ALTER command to add the new value, ‘Blocked’ before the value ‘Inactive’. Run the second ALTER command to add the new value, ‘Blocked’ after the value ‘Inactive’.

# ALTER TYPE STATUS ADD VALUE ‘ Blocked’ BEFORE ‘Inactive’;

# ALTER TYPE STATUS ADD VALUE ‘ Blocked’ AFTER ‘Inactive’;

Delete ENUM data type:

You have to delete the table where the ENUM type is used before removing the ENUM type.  Run the following command to remove the table:

Run the following command to remove the ENUM type after removing the table:

Conclusion:

The ways to create, update, and delete ENUM data types in PostgreSQL and the uses of ENUM data types in the PostgreSQL table have been shown in this tutorial that will help the new PostgreSQL users to know the purpose of using ENUM data types properly.



Source link


The binary data type is another useful data type of PostgreSQL to store binary string data. The sequence of bytes or octets is stored in the binary string. The zero-value octet and the non-printable octets can be stored in the field of the binary data type. The raw bytes are stored by the binary strings. The input value of the binary string can be taken by the ‘hex’ or ‘escape’ format and the format of the output depends on the configuration parameter, bytea_output. The default output format is ‘hex’. The BLOB or BINARY LARGE OBJECT is defined by the SQL standard as the binary string type. Different formats and the uses of binary data types in PostgreSQL have been shown in this tutorial.

Pre-requisites:

You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:

1
2
3

$ sudo apt-get -y install postgresql postgresql-contrib

$ sudo systemctl start postgresql.service

Run the following command to login to PostgreSQL with root permission:

Bytea Hex Format:

The binary data is encoded as two hexadecimal digits per byte in hex format. The binary string is preceded by the sequence, x. The hexadecimal digits can be either uppercase or lower case. This format is supported by a wide range of external applications.

Example:

1

# SELECT ExABC0110′ AS hex_format;

Bytea Escape Format:

The escape format is the traditional PostgreSQL format. A sequence of ASCII characters is used to represent the binary data in escape format. The binary string is converted into a three-digit octal value preceded by two backslashes.

Bytea Idéntico Escaped Octets:

Parte Value Description Escaped Input Example Output
0 Zero octet E’00′ SELECT E’00′::bytea; x00
45 Hyphen ‘-‘ or E’55’ SELECT E’-‘::bytea; x2d
110 ‘n’ ‘n’ or E’156′ SELECT E’n’::bytea; x6e
0 to 31 and 127 to 255 Non-printable octets E’xxx'(octal value) SELECT E’01′::bytea; x01

Bytea output Escaped Octets:

Parte Value Description Escaped Output Example Output
45 Hyphen SELECT E’55′::bytea;
32 to 126 Printable octets Any printable character SELECT E’156′::bytea; n
0 to 31 and 127 to 255 Non-printable octets xxx(octal value) SELECT E’01′::bytea; 01

Use of Binary data type in PostgreSQL:

Before creating any table with the Boolean data type, you have to create a PostgreSQL database. So, run the following command to create a database named ‘testdb’:

1

# CREATE DATABASE testdb;

The following output will appear after creating the database:

Example-1: Create a table with a binary data type to store octal value

Create a table named ‘tbl_binary_1’ in the current database with two fields. The first field name is id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The second field name is binary_data and the data type is BYTEA.

1
2
3
4

# CREATE TABLE tbl_binary_1 (

   Id SERIAL PRIMARY KEY,
   binary_data BYTEA);

The following output will appear after executing the above query:

Run the following INSERT query that will insert two octal values into the tbl_binary_1 table:

1
2
3
4
5

# INSERT INTO tbl_binary_1 (binary_data)

   VALUES
   (E055′),
   (E156′);

The following output will appear after executing the above query:

Run the following SELECT query that will read all records from the tbl_binary_1 table:

1

# SELECT * FROM tbl_binary_1;

The following output will appear after executing the above query. The output shows the hexadecimal value of the octal value.

Example-2: Create a table with a binary data type to store image data

Create a table named ‘tbl_binary_2’ in the current database with three fields. The first field name is id which is the primary key of the table and the value of this field will be incremented automatically when a new record will be inserted. The second field name is image_name and the data type is VARCHAR (20). The image name will be stored in this field. The third field name is image_data and the data type of this field is BYTEA. The image data will be stored in this field.

1
2
3
4
5

# CREATE TABLE tbl_binary_2 (

  Id SERIAL PRIMARY KEY,
  image_name VARCHAR(20),
  image_data BYTEA);

The following output will appear after executing the above query.

Insert an image in the table using PHP:

Create a PHP file named insert_image.php with the following code that will read the content of an image file. Then, store the image in the PostgreSQL table after converting it into binary data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

<?php

//Display error setting

ini_set(‘display_errors’, 1);

error_reporting(E_ALL);

$host = «localhost»;

$user = «postgres»;

$pass = «12345»;

$db = «testdb»;

 

//Create database connection object

$db_connection = pg_connect(«host=$host dbname=$db user=$user password=$pass«)

    or die («Could not connect to servern«);

 

$filename = «flower.png»;

$image = fopen($filename, ‘r’) or die(«Unable to open the file.»);

$data = fread($image, filesize($filename));

$cdata = pg_escape_bytea($data);

fclose($image);

 

//Insert the image data

$query = «INSERT INTO tbl_binary_2(image_name, image_data) Values(‘$filename‘, ‘$cdata‘)»;

$result = pg_query($db_connection, $query);

if($result) echo «Image data is inserted successfully.»;

pg_close($db_connection);

?>

The following output will appear after executing the above script from the recinto server and the image file existing in the current location:

Read the image data from the table using PHP:

Create a PHP file named get_image.php with the following code that will read the binary data of an image file. Create the image from the binary data and display the image in the browser.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

<?php

//Display error setting

ini_set(‘display_errors’, 1);

error_reporting(E_ALL);

$host = «localhost»;

$user = «postgres»;

$pass = «12345»;

$db = «testdb»;

 

//Create database connection object

$db_connection = pg_connect(«host=$host dbname=$db user=$user password=$pass«)

    or die («Could not connect to servern«);

 

//Read the image data from the table

$query = «SELECT image_data FROM tbl_binary_2 WHERE id=1»;

$result = pg_query($db_connection, $query) or die (pg_last_error($db_connection));

$data = pg_fetch_result($result, ‘image_data’);

$cimage = pg_unescape_bytea($data);

 

//Create an image file with the image data retrieved from the table

$filename = «myfile.jpg»;

$image = fopen($filename, ‘wb’) or die(«Unable to open image.»);

fwrite($image, $cimage) or die(«Unable to write data.»);

fclose($image);

pg_close($db_connection);

 

//Display the image in the browser

echo «<img src=»https://linuxhint.com/postgresql-binary-data-type/».$filename.«» height=200 width=300 />»;

?>

The generated image from the image data will appear after executing the above script from the recinto server.

Conclusion:

The purpose of using binary data types and different uses of binary data in PostgreSQL has been shown in this tutorial that will help the new PostgreSQL user to work with the binary data type.



Source link


The full form of XML is eXtensible Markup Language. Each element or tag of the XML is defined by the user. The information can be transmitted easily from one location to another by using XML data. The XML data are stored in a hierarchical format. PostgreSQL database supports XML data type to store XML data. The way to use XML data type in the PostgreSQL table has been shown in this tutorial.

Pre-requisites:

You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:

$ sudo aptGET y install postgresql postgresqlcontrib

$ sudo systemctl START postgresql.service

Run the following command to login to PostgreSQL with root permission:

Use of PostgreSQL data type:

Before creating any table with the Boolean data type, you have to create a PostgreSQL database. So, run the following command to create a database named ‘testdb’.

# CREATE DATABASE testdb;

The following output will appear after creating the database:

A. XML data with one child node

Run the following CREATE query to create a table named xmldoc1 with a field of XML data type:

# CREATE TABLE xmldoc1 (xmldata XML);

Run the following INSERT query to add XML data with one child node in the xmldata field of XML data type:

The following output will appear after executing the above statements:

Run the following SELECT query to read all records from the xmldoc1 table:

The following output will appear after executing the above statement:

B. XML data with different child nodes

Run the following CREATE query to create a table named xmldoc2 with two fields. The first field name is id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The second field name is xmldata and the data type is XML.

# CREATE TABLE xmldoc2 (
id SERIAL PRIMARY KEY,
      xmldata XML );

The following output will appear if the table is created successfully:

Run the following INSERT query to insert XML data of different child node. Here, the XML data with four child nodes will be inserted in the xmldata field.

# INSERT INTO xmldoc2 (xmldata)

        VALUES (‘<?xml version=»1.0″?>

        <Email>

                <to>[email protected]</to>

                <from>[email protected]</from>

                <subject>Site is down</subject>

                <message>My site is not working.</message>

        </Email>’);

The following output will appear if the INSERT query is executed successfully:

Run the following SELECT query to read the value of each node of the XML document in each field separately.:

# SELECT unnest(xpath(‘//to/text()’, xmldata::XML)) AS TO,

        unnest(xpath(‘//from/text()’, xmldata::XML)) AS FROM,

        unnest(xpath(‘//subject/text()’, xmldata::XML)) AS Subject,

        unnest(xpath(‘//message/text()’, xmldata::XML)) AS Message

FROM xmldoc2;

C. Convert XML data into a table

Run the following CREATE query to create a table named booklist that will convert the XML data into a table:

# CREATE TABLE booklist AS SELECT xml

   $$<books>

      <book id=«01»>

         <title>XML Developers Guide</title>

         <author>Gambardella AND Matthew</author>

         <price>44.95</price>

      </book>

      <book id=«02»>

      <title>Midnight Rain</title>

         <author>Ralls AND Kim</author>

         <price>5.95</price>

      </book>

      <book id=«03»>

         <title>Maeve Ascendant</title>

         <author>Corets AND Eva</author>

         <price>5.95</price>

      </book>

</books>$$ AS books;

The following output will appear if the XML data is converted into a table properly:

The content of the XML nodes can be retrieved by defining the path of the node properly. The xmltable.* is one of the ways to read the node and attribute values from the table that was converted from the XML data. Run the following SELECT query to read the value of the id attribute of the book node and the values of the title, author, and price nodes. Here, the ‘@’ symbol has used to read the attribute value.

# SELECT xmltable.*  FROM booklist,

   XMLTABLE (‘/books/book’ PASSING books

      COLUMNS

         id CHAR(2) PATH ‘@id’ NOT NULL,

         title TEXT PATH ‘title’ NOT NULL,

         author TEXT PATH ‘author’ NOT NULL,

         price FLOAT PATH ‘price’ NOT NULL );

The following output will appear after executing the above query:

The values of the XML nodes and attributes can be retrieved from the table by defining the field names in the SELECT query based on the XML nodes. Run the following SELECT query to read the value of the id attribute of the book node and the values of the title, author, and price nodes. Here, the ‘@’ symbol has been used to read the attribute value like the previous SELECT query.

# SELECT Id, Title, Author, Price FROM booklist,

   XMLTABLE (‘/books/book’ PASSING books

      COLUMNS

         Id INT PATH ‘@id’ NOT NULL,

         Title VARCHAR (50) PATH ‘title’ NOT NULL,

         Author VARCHAR (30) PATH ‘author’ NOT NULL,

         Price FLOAT PATH ‘price’ NOT NULL);

The following output will appear after executing the above query:

The aggregate function can be applied to the node of the XML by using the SELECT query. Run the following SELECT query to count the total number of id attribute and total price of all books by using the COUNT() function and SUM() function. According to the XML data, the total number of id attributes is 3 and the sum of all price nodes is 56.85.

# SELECT COUNT(id) AS Total_Books, SUM(Price) AS Total_Price FROM booklist,

   XMLTABLE (‘/books/book’ PASSING books

      COLUMNS

         id INT PATH ‘@id’ NOT NULL,

         tittle VARCHAR (50) PATH ‘title’ NOT NULL,

         author VARCHAR (30) PATH ‘author’ NOT NULL,

         price FLOAT PATH ‘price’ NOT NULL);

The following output will appear after executing the above query:

Conclusion:

The way of using XML data type in PostgreSQL tables and reading the XML data from the table in different ways have been shown in this tutorial for helping the new PostgreSQL users to know the uses of this data type properly.



Source link


Date/Time data types are used in PostgreSQL to store date and time data. The Julian date is used in PostgreSQL for different types of date and time calculations. The most commonly used January to December calendar use Julian date. Five different data types are used in PostgreSQL to store date and time data. These are date, time, timestamp, time with time zone, and interval. The size and purpose of these data types have been shown below.

date data type:

It is used to store calendar date values in the date field of the database table. It takes 4 bytes to store the data. Different date input formats and date output formats are supported by the PostgreSQL database table which is explained below.

Date Input Formats:

Some commonly used date input formats are given below:

Format Description
Wednesday May 18, 2022 It uses the full weekday name, the full name of the month, the day of the month, and the year.
May 18, 2022 It uses the full name of the month, the day of the month, and the year.
2022-05-18 It uses standard ISO-8601 format that contains the numeric year, month, and day.
5/18/2022 It uses Non-European (U.S.) format that contains numeric month, day, and year.
18/5/2022 It uses a European format that contains numeric days, month, and year

Date Output Format:

The date output formats that are used in PostgreSQL are given below:

Format Description
PostgreSQL It uses the flamante PostgreSQL format.
Example: Wed 18 May 11:30:00 2022 PST
SQL It uses the traditional SQL format.
Example: 05/18/2022 11:30:00.00 PST
ISO It uses ISO-8601 standard format.
Example: 2022-05-18 11:30:00-07
German It uses the Regional Germany format.
Example: 18.05.2022 09:30:15.00 PST

time data type:

Time and time with time zone values take 4 bytes to store the data in the table of the PostgreSQL. Some valid time formats are given below:

Format Description
06:30 It uses ISO-8601 standard format.
07:20 AM It is similar to 07:20.
07:20 PM It is similar to 19:20.
15:15 It is similar to 03:15 PM.

Some valid time zone formats are given below:

Format Description
03:45:20-5 It uses ISO-8601 standard format that prints 7 hours behind GMT.
03:45:20-05:30 It uses ISO-8601 standard format that prints 7 hours and 30 minutes behind GMT.
03:45:20-0530 It uses ISO-8601 standard format that prints 7 hours and 30 minutes behind GMT.

timestamp data type:

Timestamp with time zone value takes 8 bytes to store the data in the table of the PostgreSQL. Some valid timestamp formats are given below:

Format Description
2022-05-25 12:25-7 It uses ISO-8601 date format with the minutes and PST time zone.
25/05/2022 12:30:20.100 It uses the European date format with microseconds.
05/25/2022 21:30 It uses the US date format with the minutes in 24-hour time.
25.05.2022 20:45:125 PM It uses the German regional date format with the seconds, and PM.

Pre-requisites:

You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:

$ sudo apt-get -y install postgresql postgresql-contrib
$ sudo systemctl start postgresql.service

Run the following command to login to PostgreSQL with root pemission:

PostgreSQL of date and time examples:

Run the following SELECT statement to read the particular date:

# SELECT date(‘2022-05-25’);

Run the following SELECT statement to read the current date and time:

Run the following SELECT statement to read the current date:

Run the following SELECT statement to read the current time:

The following output will appear after executing the above command:

Create a table using data and time data types:

Before creating any table with the Boolean data type, you have to create a PostgreSQL database. So, run the following command to create a database named ‘testdb’:

# CREATE DATABASE testdb;

The following output will appear after creating the database:

Create a table named ‘orders’ in the current database with five fields. The first field name is invoice_id, the data type is INT and it is the primary key of the table. The second field name is order_date and the data type is DATE. The third field name is order_time and the data type is TIME. The fourth field name is customer_id and the data type is VARCHAR(6). The fifth field name is entry_time and the data type is TIMESTAMP.

CREATE TABLE orders (
Invoice_id INT PRIMARY KEY,
order_date DATE,
order_time TIME,
customer_id VARCHAR (6),
entry_time TIMESTAMP);

The following output will appear if the table is created successfully:

Run the following insert query that will insert three records into the orders table. The order_date value has been given in ISO-8601 format. The order_time value has been given in ISO-8601 format. The current_timestamp value has been used in the entry_time field that will insert the timestamp value in ISO-8601 date format with the microseconds.

INSERT INTO orders
VALUES
(11785645, ‘2022-05-18’, ’10:30 AM’, ‘785634’, current_timestamp),
(11895634, ‘2022-05-17’, ’11:33 AM’, ‘856345’, current_timestamp),
(11128976, ‘2022-05-10’, ’08:30 PM’, ‘906745’, current_timestamp);

The following output will appear after executing the above query:

Run the following select query to read all records from the orders table:

The following output will appear after executing the above query:

Conclusions:

The date and time data types are required to store date and time data in the PostgreSQL tables properly. The PostgreSQL database supports different formats of date, time, and timestamp data types to store date and time data in the tables. The uses of these date and time data types have been shown in this tutorial by creating a table that will help the new PostgreSQL users to create the table by using these data types.



Source link


The character data types are used in the PostgreSQL database to store the different lengths of string data in the database tables. Three types of character data types are supported by PostgreSQL data types: char, varchar, and text. These data types are used for storing different types of string data. The purposes of using these data types have been explained below.

CHAR(n) or CHARACTER(n):

This data type is used to store the fixed length of string data. The length value will be provided at the time of table creation. The field of the table that contains char data will fill up the unused space with space characters. So, this data type is better to use for storing data of fixed length such as id of fixed length, phone number of a particular format, etc. The error will appear if the user tries to store string data larger than the length of the field of char data.

VARCHAR(n) or CHARACTER VARYING(n):

This data type is used to store the variable length of string data. The length will be provided at the time of table creation like the char data. The unused space of the field that contains this data type will remain unused and takes the space occupied by the string data only. The error will appear if the user tries to store string data larger than the length of the field of varchar data.

TEXT or VARCHAR:

This data type is used to store string data of unlimited length. So, the length value is not defined at the time of table creation. This is not SQL standard data type and it is removed from some relational database management systems (RDBMS) such as Oracle, MS SQL Server, etc.

Pre-requisites:

You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:

$ sudo apt-get -y install postgresql postgresql-contrib
$ sudo systemctl start postgresql.service

Run the following command to login to PostgreSQL with root permission:

Use of character data types in PostgreSQL:

Before creating any table with the Boolean data type you have to create a PostgreSQL database. So, run the following command to create a database named ‘testdb’:

# CREATE DATABASE testdb;

The following output will appear after creating the database:

Create a table:

Run the following SQL statement to create a table named clients in a PostgreSQL database by using different types of character data types. Here, the first field name is id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The name of the second field is name and the data type of this field is varchar which will be able to store a maximum of 15 characters. The name of the third field is address and the data type of this field is text. The name of the fourth field is email and the data type of this field is character varying that will be able to store maximum of 30 characters. The fifth field is phone and the data type is char which will store the string data of 14 characters.

# CREATE TABLE clients (
    id serial PRIMARY KEY,
    name varchar (15) NOT NULL,
    address text NOT NULL,
    email character varying (30),
    phone char (14) NOT NULL );

The following output will appear if the table is created successfully:

Insert data into the table:

Run the following insert query with the valid data to insert a record into the clients table:

# INSERT INTO clients (name, address, email, phone)
   VALUES (‘AR Company’, ’12/A, Dhanmondi, Dhaka.’, [email protected], ‘+8801826783423’);

The following output will appear if the insert query is executed successfully:

Run the following insert query to insert a record into the clients table. Here the value of the name field exceeds the size of the field. So, an error message will appear.

# INSERT INTO clients (name, address, email, phone)
   VALUES (‘RFL Company Ltd.’, ’23, Motijeel, Dhaka.’, [email protected], ‘+8801927891278’);

The following output will appear after executing the above insert query. Here, the name field can store a maximum of 15 characters but 16 characters are given in the query.

Run the following insert query to insert a record into the clients table. Here the value of the phone field exceeds the size of the field. So, an error message will appear.

# INSERT INTO clients (name, address, email, phone)
   VALUES (‘Vision’, ‘190/1, Mirpur, Dhaka.’, [email protected], ‘+880152678342312’);

The following output will appear after executing the above insert query. Here, the phone field can store 14 characters but 16 characters are given in the query.

Read data from the table:

Only one insert query has been executed successfully after executing the above three insert queries. Now, run the following select query to retrieve all records from the clients table:

The following output will appear after executing the above query:

Conclusion:

The character data type is a very useful data type of the PostgreSQL table. Some fields of the table require to store fixed-length of string data and some fields of the table require the variable length of data. The uses of three different data types of PostgreSQL tables have been explained in this tutorial by creating a table. The new PostgreSQL users will get proper knowledge about the character data types and will be able to use them properly for creating tables after reading this tutorial.



Source link


The sequential series of data is called range data. PostgreSQL database supports a range of data types which is a unique feature of this database. The range and multi-range data types are introduced in the PostgreSQL version 14. This data type stores the lower and the upper values. The integer or the timestamp value can be stored in the field of this data type. When it is required to store many values in a single range value then it is better to use range data type. The built-in range of data types that are supported by PostgreSQL has been mentioned below.

Range Types Purpose
int4range It defines the range of integers.
int4multirange It defines the multi-range integers.
int8range It defines the range of big integers.
int8multirange It defines the multi-range big integers.
numrange It defines the range of numbers.
nummultirange It defines the multi-range of numbers.
tsrange It defines the range of timestamp without the time zone.
tsmultirange It defines the multi-range of timestamp without the time zone.
tstzrange It defines the range of timestamp with the time zone.
tstzmultirange It defines the multi-range of timestamp with the time zone.
daterange It defines the range of date.
datemultirange It defines the multi-range of date.

Pre-requisites:

You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:

$ sudo apt-get -y install postgresql postgresql-contrib
$ sudo systemctl start postgresql.service

Run the following command to login to PostgreSQL with root permission:

PostgreSQL range data type examples:

Before creating any table with the Boolean data type you have to create a PostgreSQL database. So, run the following command to create a database named ‘testdb’:

# CREATE DATABASE testdb;

The following output will appear after creating the database:

A. Use of integer range data type
Create a table named ‘tbl_intRange’ in the current database with two fields. Here, the first field name is id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The second field name is int_range and the data type is INT4RANGE.

# CREATE TABLE tbl_intRange (
    id SERIAL PRIMARY KEY,
    int_range INT4RANGE );

The following output will appear if the table is created successfully.

Run the following INSERT query to insert three records into the tbl_intRange table:

# INSERT INTO tbl_intRange (int_range)
    VALUES (‘[1, 10)’::int4range),
              (‘[1, 20)’::int4range),
              (‘[1, 30)’::int4range);

The following output will appear after executing the above query:

Run the following SELECT query to read all records from the tbl_intRange:

# SELECT * FROM tbl_intRange;

Run the following SELECT query to read those records from the tbl_intRange WHERE int_range is greater than 12:

# SELECT * FROM tbl_intRange WHERE int_range @>12;

The following output will appear after executing the above SELECT queries:

B. Use of numeric range data type
Create a table named ‘tbl_numRange’ in the current database with two fields. Here, the first field name is id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The second field name is num_range and the data type is NUMRANGE.

# CREATE TABLE tbl_numRange (
    id SERIAL PRIMARY KEY,
    num_range NUMRANGE );

Run the following INSERT query to insert three records into the tbl_numRange table:

# INSERT INTO tbl_numRange (num_range)
    VALUES (numrange(20, 40)),
                  (numrange(100, 500));

The following output will appear after executing the above queries:

Run the following SELECT query that will read all records from the tbl_numRange:

# SELECT * FROM tbl_numRange;

The following output will appear after executing the above SELECT query:

C. Use of timestamp range data type
Create a table named ‘tbl_timeRange’ in the current database with two fields. Here, the first field name is id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The second field name is timeRange and the data type is TSRANGE.

# CREATE TABLE tbl_timeRange (
    id SERIAL PRIMARY KEY,
    timeRange TSRANGE);

Run the following INSERT query to insert three records into the tbl_timeRange table:

# INSERT INTO tbl_timeRange ( timeRange)
    VALUES (‘[2022-05-20 10:20:30, 2022-05-21 10:20:15)’),
                 (‘[2022-09-13 9:30:15, 2022-09-14 11:10:20)’);

The following output will appear after executing the above queries:

Run the following SELECT query that will read all records from the tbl_timeRange:

# SELECT * FROM tbl_timeRange;

The following output will appear after executing the above SELECT query:

D. Use of date range data type
Create a table named ‘tbl_dateRange’ in the current database with two fields. Here, the first field name is id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The second field name is date_range and the data type is DATERANGE.

# CREATE TABLE tbl_dateRange (
    id SERIAL PRIMARY KEY,
    date_range DATERANGE);

Run the following INSERT query to insert three records into the tbl_dateRange table:

# INSERT INTO tbl_dateRange ( date_range)
VALUES (‘[2022-05-20, 2022-05-21)’),
                  (‘[2022-07-10, 2022-07-15)’),
                  (‘[2022-12-01, 2022-12-16)’);

The following output will appear after executing the above queries:

Run the following SELECT query that will read all records from the tbl_dateRange:

# SELECT * FROM tbl_dateRange;

Run the following SELECT query that will read those records from the tbl_dateRange where the date value of the date_range field is greater than ‘2002-07-13’.

# SELECT *FROM tbl_dateRange WHERE date_range @>‘2022-07-13’::date;

The following output will appear after executing the above queries:

Conclusion:

Different uses of range data types of PostgreSQL have been shown in this tutorial by using multiple tables. The new PostgreSQL users will be able to use the range of data types in their tables after reading this tutorial.



Source link