Set column

Write multiple values into a set column.

Write multiple values into a set column. If a table specifies a set to hold data, then use INSERT or UPDATE to enter data.

Procedure

  • Insert data into the set, enclosing values in curly brackets.
    Set values must be unique, because no order is defined in a set internally.
    INSERT INTO cycling.cyclist_career_teams (
      id, lastname, teams
     ) VALUES (
      5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 
      'VOS', 
      {
        'Rabobank-Liv Woman Cycling Team',
        'Rabobank-Liv Giant',
        'Rabobank Women Team',
        'Nederland bloeit'
      }
    );
  • Add an element to a set using the UPDATE statement and the addition (+) operator.
    UPDATE cycling.cyclist_career_teams
    SET teams = teams + {'Team DSB - Ballast Nedam'} 
    WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
  • Remove an element from a set using the subtraction (-) operator.
    UPDATE cycling.cyclist_career_teams
    SET teams = teams - {'DSB Bank Nederland bloeit'} 
    WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
  • Remove all elements from a set by using the UPDATE or DELETE statement.

    A set, list, or map needs to have at least one element because an empty set, list, or map is stored as a null set.

    UPDATE cycling.cyclist_career_teams
    SET teams = {} 
    WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
    
    DELETE teams
    FROM cycling.cyclist_career_teams
    WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

    A query for the teams returns null.

    SELECT id, lastname, teams 
    FROM cycling.cyclist_career_teams 
    WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
     id                                   | lastname | teams
    --------------------------------------+----------+-------
     5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |      VOS |  null
    
    (1 rows)