![]() In such a case we can run the following query: select employee_full_name We use the ANY() function. In case there are multiple employees having same phone number (which should not be the case ideally), the results will print multiple employee names. Suppose we need to find out the employee name who holds a particular phone number. This requires us to query across every record of the table and checking if "any" contact number matches the given phone number. ![]() Searching for a specific data in the record of arrays Generally if we try to access the element at an index of an array that is beyond it's size, we get some index out of range/bound exception in most languages. However if we run this query, we will get a null value returned since there are only 2 contact numbers for this employee. Where employee_full_name = 'Sabyasachi Mukherjee' If we run a query to get the third contact number of Sabyasachi, the query would look like: select employee_full_name, contact_number In the below data set, what if we tried to access the third contact number of Sabyasachi Mukherjee, which actually doesn't exist? What if we tried to access an index from the array that doesn't exist? This will look for all the matching records in the table whose 2nd contact number (2nd array element) matches to the given number. We can check for the employee information whose second contact number is "+91-0987654321" by running the following query: select employee_full_name We can also do many more operations on this array data type, just like we can do on other data type columns. If we want to query for the primary contact number of every employee in the organization, we can run the following query: select employee_full_name, contact_number We just assumed that the first phone number of every employee is his/her primary contact number, so the subscript used would be "1". We can access array elements using the subscript within square brackets. If we check the contents of the employee_info table now, we will see: INSERT INTO employee_info (employee_full_name, contact_number) We add these three employees using the following query: INSERT INTO employee_info (employee_full_name, contact_number) We have three employees have multiple contact numbers. Once the table is created, we can store some data into it. We can run the following query to create the table: CREATE TABLE employee_info ( Let us create an employee table with the contact_number column as an ARRAY data type. We will use the array data type to accommodate the column containing the contact number to store multiple numbers. Let us assume that the first contact number is the primary and the remaining are all alternate contact numbers. We need to store these contact numbers against a single column in the database. Suppose an employee can add multiple contact numbers to their record. ![]() We will also cover these custom types later in the article. What if we want to have similar restrictions for the columns of multiple tables? Instead of repeatedly writing the redundant restriction logic in every DDL script, we can make use of custom data type here. PostgreSQL also supports different forms of checks and restrictions on a column in DDL scripts. How do we store them all in one column instead of having different columns for all the numbers? We can use of ARRAY datatype in PostgreSQL, which we will cover in-depth shortly. Suppose we are storing the data of employees in an organization and it's obvious that most of the employees will have multiple contact numbers alternate contact numbers. using these supported datatypes, but what if we need to store multiple data elements in a single column? We can easily store data like numbers, characters, date, time, etc. Just like every other database, PostgreSQL has its own set of basic datatypes, like Boolean, Varchar, Text, Date, Time, etc.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |