Introduction to PostgreSQL where in array
PostgreSQL provides different types of data types. Array is a one type of data type which one provided by PostgreSQL (Array is multidimensional with variable length). Array used where clause to select specific column from database table, array is user defined data type or built in data type. PostgreSQL provides a facility to define columns as an array with any valid data type, array should be integer [] type, character [] type or user-defined data type. Sometimes we need to create our own data type. At that time, PostgreSQL created an equivalent array data type in the backend. We can perform different operations using where in the array.
Syntax:
select column name 1, column name 2,…………. columnN from table name where condition;
Explanation:
- In the above syntax we use select and where clause, where column name 1, column name 2 are column names that we want to show, where table name is specified table name and we apply condition using where clause.
How to use where in array in PostgreSQL?
- We must install PostgreSQL in our system.
- We require basic knowledge about PostgreSQL.
- We must require a database table to perform where in the array.
- We must need basic knowledge about the array that means how it is used.
- We can perform different operations on users with the help of psql and pgAdmin.
1. Operation of where in array
Before implementation of where clause in array we need a table to implement where in array.
So let’s see how we can create tables using array data type.
Syntax:
create table table name(column name 1 data type [], column name 2 data type [],…………………… column name N data type []);
Explanation:
- In the above syntax we use create table statement, where table name is used for specified table name and column name 1, column name 2 is defined with different array data types.
Example:
Code:
create table emp(emp_id serial PRIMARY KEY, emp_name varchar (100),emp_dept varchar [], emp_phones text []);
Explanation:
- In the above example where create table is statement, emp is a table name and we created an emp table with four columns such as emp_id, emp_name, emp_dept and emp_phones with different array data types.
Now we insert records into the database table by using the following insert into statement.
Code:
Insert into emp (emp_name, emp_dept, emp_phones) Values
('Jacson', '{"comp"}' , '{"(204)-123-3452"}'),
('Paul', '{"mech"}', '{"(222)-654-0979","(205)-756-13345"}'),
('sam', '{"Account"}','{"(204)-123-3452"}'),
('John', '{"Account", "Purchase"}','{"(204)-123-3452"}');
select * from emp;
Explanation:
- In the above example, suppose the user needs to insert records into the table. At that time we use the above statement to insert.
- In above example the employee Paul has a two contact number, employee John works in two departments such as Account and Purchase.
- Illustrate the end result of the above declaration by using the following snapshot.
Output:
2. Display operation using where in array
Suppose the user needs to display a single phone number and the number must be first from the table at that time we use the following statement.
Example:
Code:
select emp_name, emp_dept,emp_phones [ 1 ] from
emp;
Explanation:
- In the above example we show the employee name, employee department and employee phones.
- See here we only display the first phone number of the employee.
- Illustrate the end result of the above declaration by using the following snapshot.
Output:
Suppose users need to display the second number of employees at that time we use the following statement.
Example:
Code:
select emp_name,emp_dept, emp_phones [ 2 ] from emp;
Explanation:
- In the above example we show the employee’s second number using a select statement.
- Illustrate the end result of the above declaration by using the following snapshot.
Output:
Now let’s see how we can use where clauses in an array. In the above examples, we see how we can implement arrays with different operations.
Suppose the user needs to see a specific phone number from the database table at that time we use the following syntax.
Syntax:
select column name 1, column name 2,………………… column nameN from table name where condition ;
Explanation:
- In the above syntax we use select and where clause, where column name is used to display specified column name from table, table name means specified table name and apply condition using where clause.
Example:
Code:
select emp_name from emp where emp_phones [2] = '(205)-756-13345';
Explanation:
- In this example, suppose the user needs to show the second number of employees from the database table at that time we use the above statement.
- The only one employee has a second number.
- Illustrate the end result of the above declaration by using the following snapshot.
Output:
In a similar way we implemented a department column.
Example:
Code:
select emp_name from emp where emp_dept [1] = 'Account';
Explanation:
- In the above example we show those employee works in the Account department using where clause here we use condition emp_dept = 1 that indicates first array value in emp_dept column.
- Illustrate the end result of the above declaration by using the following snapshot.
Output:
3. Update array operation
In this operation we update array values by using an update statement.
Syntax:
update table name set value where condition;
Explanation:
- In the above syntax we use an update statement, where table name means specified table name, value means updated values and apply condition using where clause.
Example:
Code:
update emp set emp_phones= '{"(202)-5555-444","(105)-777-5555"}'
where emp_name='sam';
select * from emp;
Explanation:
- In above example suppose user want to update phone number of sam employee at that time we use above statement see here we update all phone numbers.
- Illustrate the end result of the above declaration by using the following snapshot.
Output:
Now let’s see how we can update the first array value.
Example:
Code:
update emp set emp_phones [1]= '{"(202)-5555-444"}'
where emp_name = 'Jacson';
select * from emp;
Explanation:
- In the above example we update the first phone number of Jacson employee.
- Illustrate the end result of the above declaration by using the following snapshot.
Output:
Similarly we can update the second value of array.
Conclusion
From the above article we saw the basic syntax of where in array. We also saw how we can implement them in PostgreSQL with different examples of each operation. From this article we saw how we can handle where in array in PostgreSQL.
Recommended Articles
This is a guide to PostgreSQL where in array. Here we discuss the introduction and how to use where in array in PostgreSQL? You may also have a look at the following articles to learn more –
