PostgreSQL Queries

PostgreSQL queries

Find the metadata_field_id for a known text_value:

# select * from metadatavalue where text_value='2015-03-09T17:51:03Z';

item_id or resource_id to handle:

# select handle from item, handle where handle.resource_id = item.item_id AND item.item_id = '22563' and handle.resource_type_id=2;

List all metadata fields and their IDs:

# select * from metadatafieldregistry;

Move metadata values from one field to another:

# update metadatavalue set metadata_field_id=109 where metadata_field_id=66;

Where 109 is the ID of the new field, and 66 is the ID of the old field. IDs come from the metadatafieldregistry (see above). Make sure to do a full Discovery reindex afterwards (index-discovery -b).

Delete blank metadata values:

# delete from metadatavalue where resource_type_id=2 and text_value='';
DELETE 226

Requires a full Discovery reindex afterwards (index-discovery -b).

Get handles for items that are using a given metadata field, ie dc.Species.animal (105):

# select handle from item, handle where handle.resource_id = item.item_id AND item.item_id in (select resource_id from metadatavalue where resource_type_id=2 and metadata_field_id=105);
   handle
-------------
 10568/10298
 10568/16413
 10568/16774
 10568/34487