Set column

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

A frozen set column can only insert or update the entire set, and elements cannot be prepended or appended to the set.

Prerequisites

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)

 id                                   | lastname | teams
--------------------------------------+----------+-------
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |      VOS |  null

(1 rows)

Set column

Prerequisites

Procedure

Was this helpful?

Give Feedback