A place to record tips for working with CollectionSpace data directly in the PostgreSql database
Table of Contents
Client-identifying info has been removed from SQL examples and replaced with the following patterns:
{INSTANCEDOMAIN} replaces values like core.collectionspace.org or collectionspace.clientdomain.org in refname values or elsewhere
CAST(E'urn:cspace:{INSTANCEDOMAIN}:vocabularies:name(publishto):item:name(all)\'All\'' AS text) AS item-
Common patterns/snippets - excluding deleted records, how to find data for a given record from the application UI, etc
-
Functions - reusable bits
-
Data projects - queries to support actual work
-
Nuxeo database documentation: Internal VCS Model — VCS tables
-
Some example JasperReports have embedded PGSQL queries to crib from: