Listado de la etiqueta: Type


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


By the end of this tutorial, you will understand how to use the astype() function in Pandas. This function allows you to cast an object to a specific data type.

Let us go exploring.

Function Syntax

The function syntax is as illustrated below:

DataFrame.astype(dtype, copy=True, errors=‘raise’)

The function parameters are as shown:

  1. dtype – specifies the target data type to which the Pandas object is cast. You can also provide a dictionary with the data type of each target column.
  2. copy ­– specifies if the operation is performed in-place, i.e., affects the innovador DataFrame or creating a copy.
  3. errors – sets the errors to either ‘raise’ or ‘ignore.’

Return Value

The function returns a DataFrame with the specified object converted to the target data type.

Example

Take a look at the example code shown below:

# import pandas
import pandas as pd
df = pd.DataFrame({
    ‘col1’: [10,20,30,40,50],
    ‘col2’: [60,70,80,90,100],
    ‘col3’: [110,120,130,140,150]},
    index=[1,2,3,4,5]
)
df

Convert Int to Float

To convert the ‘col1’ to floating-point values, we can do:

df.col1.astype(‘float64’, copy=True)

The code above should convert ‘col1’ to floats as shown in the output below:

Convert to Multiple Types

We can also convert multiple columns to different data types. For example, we convert ‘col1’ to float64 and ‘col2’ to string in the code below.

print(f«before: {df.dtypes}n«)
df = df.astype({
    ‘col1’: ‘float64’,
    ‘col2’: ‘string’
})
print(f«after: {df.dtypes}»)

In the code above, we pass the column and the target data type as a dictionary.

The resulting types are as shown:

Convert DataFrame to String

To convert the entire DataFrame to string type, we can do the following:

The above should cast the entire DataFrame into string types.

Conclusion

In this article, we covered how to convert a Pandas column from one data type to another. We also covered how to convert an entire DataFrame into string type.

Happy coding!!



Source link


For this one, we will explore how to get the data type of a specific column in a Pandas DataFrame.

Sample

Let us start by creating a sample DataFrame:

# import pandas
import pandas as pd
df = pd.DataFrame({
    ‘salary’: [120000, 100000, 90000, 110000, 120000, 100000, 56000],
    ‘department’: [‘game developer’, ‘database developer’, ‘front-end developer’, ‘full-stack developer’, ‘database developer’, ‘security researcher’, ‘cloud-engineer’],
    ‘rating’: [4.3, 4.4, 4.3, 3.3, 4.3, 5.0, 4.4]},
    index=[‘Alice’, ‘Michael’, ‘Joshua’, ‘Patricia’, ‘Peter’, ‘Jeff’, ‘Ruth’])
print(df)

The above should create a DataFrame with sample data as shown:

Pandas dtype Attribute

The most straightforward way to get the column’s data type in Pandas is to use the dtypes attribute.

The syntax is as shown:

The attribute returns each column and its corresponding data type.

An example is as shown:

The above should return the columns and their data types as shown:

salary          int64
department     object
rating        float64

If you want to get the data type of a specific column, you can pass the column name as an index as shown:

This should return the data type of the salary column as shown:

Pandas Column Info

Pandas also provide us with the info() method. It allows us to get detailed information about the columns within a Pandas DataFrame.

The syntax is as shown:

DataFrame.info(verbose=None, buf=None, max_cols=None, memory_usage=None, show_counts=None, null_counts=None)

It allows you to fetch the name of the columns, data type, number of non-null elements, etc.

An example is as shown:

This should return:

The above shows detailed information about the columns in the DataFrame, including the data type.

Conclusion

This tutorial covers two methods you can use to fetch the data type of a column in a Pandas DataFrame.

Thanks for reading!!



Source link