By contrast, if the spreadsheet data is stored in the Data Store, one would be able to access individual spreadsheet rows via a simple web API, as well as being able to make queries over the spreadsheet contents.

Note The Data Store (like CKAN) requires Postgre SQL 9.2 or later. At the time of writing, the only version that is not supported by CKAN that has not been made ‘end-of-life’ by the Postgre SQL community is 9.1. Since changing the encoding of Postgre SQL may mean deleting existing databases, it is suggested that this is fixed before continuing with the datastore setup.

If you are able to use the with the id of an existing CKAN dataset.

A table named after the resource id should have been created on your Data Store database.

Once the Data Store database and the users are created, the permissions on the Data Store and CKAN database have to be set.

CKAN provides a paster command to help you correctly set these permissions.

The Data Store is generally used alongside the Data Pusher, which will automatically upload data to the Data Store from suitable files, whether uploaded to CKAN’s File Store or externally linked. In contrast to the File Store which provides ‘blob’ storage of whole files with no way to access or query parts of that file, the Data Store is like a database in which individual data elements are accessible and queryable.

To illustrate this distinction, consider storing a spreadsheet file like a CSV or Excel document.

You can also add a new column to an existing table even if the Data Store resource already contains some data.

Triggers may be added to enforce validation, clean data as it is loaded or even record record histories.

Triggers are PL/pg SQL functions that must be created by a sysadmin.

You will notice that we tried to keep the layer between the underlying Postgre SQL database and the API as thin as possible to allow you to use the features you would expect from a powerful database management system.

A Data Store resource can not be created on its own.