Maps: Key-Value Pairs in a Column

A map is a collection of key-value pairs inside one column. Each key has one value, like a word and its meaning in a small dictionary. Maps are good for settings, where each row can have its own options.

Write two types inside < >: first the type of the keys, then the type of the values:

CREATE TABLE settings (
  username text PRIMARY KEY,
  prefs map<text, text>
);

Write a map

Use curly braces again, with key: value pairs:

INSERT INTO settings (username, prefs)
VALUES ('lee', {
  'theme': 'dark',
  'notifications': 'on'
});

SELECT * FROM settings;

Output:

 username | prefs
----------+------------------------------------------
      lee | {'notifications': 'on', 'theme': 'dark'}

(1 rows)

Like a set, a map is stored sorted. Here it is sorted by key, so 'notifications' comes before 'theme'.

Set one entry

Put the key in square brackets after the column name:

UPDATE settings
SET prefs['language'] = 'de'
WHERE username = 'lee';

UPDATE settings
SET prefs['theme'] = 'light'
WHERE username = 'lee';

SELECT * FROM settings;

Output:

 username | prefs
----------+-------------------------------------------------------------
      lee | {'language': 'de', 'notifications': 'on', 'theme': 'light'}

(1 rows)

The key 'language' was new, so it was added. The key 'theme' already existed, so its value changed. The other entries stayed the same. This is the upsert rule again, this time inside a map.

Read one entry

SELECT prefs['theme'] FROM settings
WHERE username = 'lee';

Output:

 prefs['theme']
----------------
          light

(1 rows)

Delete one entry

DELETE prefs['notifications'] FROM settings
WHERE username = 'lee';

SELECT * FROM settings;

Output:

 username | prefs
----------+--------------------------------------
      lee | {'language': 'de', 'theme': 'light'}

(1 rows)

Don't put everything in a map

Use maps for small key-value data that belongs to one row, like preferences. Don't move your real columns into a map, like prefs['email'] or prefs['age']. Real columns have their own types: age can be an int, but in a map<text, text> every value is text. Like all collections, keep maps small.

In the exercise, Kai wants the app in French. Set one entry in kai's map.