Why CKAN’s datastore_search_sql gives you a syntax error, and how to fix it

If you’re using the DataStore extension with CKAN, one of the first things you’re likely to try is to execute a SQL query on your data. However, you’ll likely see something like this:

["(ProgrammingError) syntax error at or near \"-\"\nLINE 1: SELECT * FROM 2da8c567-9d09-4b00-b098-c3e036170a86

This is because by default CKAN tends to create resource IDs that PostgreSQL doesn’t like as table names.

To get around this, just put quotes around the resource ID in the URL like so:

/api/action/datastore_search_sql?sql=SELECT%20*%20FROM%20%2241db6160-abc5-4c3a-8064-36cbefd4417b%22
Advertisements
This entry was posted in development. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s