Question 89659 in PostgreSQL Question posted in
The official documentation can be found here.

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

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:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    You can use the ANY() operator:

    select * 
from student 
where 1 = any(course_id);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search