Query for postgres database to fetch record from query parameter stored inside an array within an attribute of the table - Postgresql

bluestacks454
February 22, 2023
197 views
0 votes
2 Answers

I have a table in a postgres database whose columns are shown below.

CREATE TABLE Student(
  name VARCHAR,  
  course_id SMALLINT[]
);

I am trying to write a SQL query that fetches the name of the Student that has course_id 1 in its vector.

So if an entry has

INSERT INTO Student ( name, course_id ) VALUES ( 'john', ARRAY [1,2,3] );

The query will return ‘john’ since ‘john’ has a course_id 1

Tags: postgresql

Answers

use postgres contains:

select * from Student where course_id @> array[1]

CREATE TABLE Student(
  name VARCHAR,  
  course_id SMALLINT[]
);

INSERT INTO Student ( name, course_id ) VALUES ( 'john', ARRAY [1,2,3] );
INSERT INTO Student ( name, course_id ) VALUES ( 'john2', ARRAY [2,3] );
INSERT INTO Student ( name, course_id ) VALUES ( 'john3', ARRAY [1] );

select * from Student where course_id @> array[1]

name	course_id
john	{1,2,3}
john3	{1}

SELECT 2

fiddle

- a_horse_with_no_name
- February 22, 2023 at 3:00 pm
- 0 votes
0
You can use the ANY() operator:
```
select * 
from student 
where 1 = any(course_id);
```
Login or Signup to reply.