PostgreSQL Cookbook

PostgreSQL lines that do useful things in an evergreen-ils database.

> How many connections are open to my database.

SELECT * FROM pg_stat_activity;

More sql

SELECT id FROM asset.call_number WHERE record NOT IN (SELECT id FROM biblio.record_entry);
# SELECT COUNT(id) FROM asset.call_number WHERE record NOT IN (SELECT id FROM biblio.record_entry);
select acp.id from asset.copy acp left join asset.call_number acn on (acp.call_number = acn.id) left join biblio.record_entry bre on (acn.record = bre.id) where bre.id is null;
select count(acp.id) from asset.copy acp left join asset.call_number acn on (acp.call_number = acn.id) left join biblio.record_entry bre on (acn.record = bre.id) where bre.id is null;
# to add
begin;
insert into biblio.record_entry with new_bib as ( select distinct on (id) * from auditor.biblio_record_entry_history where id not in (select id from biblio.record_entry) and tcn_value not in (select tcn_value from biblio.record_entry) and id in (select record from asset.call_number) order by id,audit_time desc) select id,creator,editor,source,quality,create_date,edit_date,active,deleted,fingerprint,tcn_source,tcn_value,marc,last_xact_id,owner,share_depth from new_bib;

# count missing bibs with holdings
select count(distinct(record)) from asset.call_number WHERE record NOT IN (SELECT id FROM biblio.record_entry);

# count number of distinct holdings without bibs
SELECT COUNT(id) FROM asset.call_number WHERE record NOT IN (SELECT id FROM biblio.record_entry);

## To get a reckoning of the bibless childs and output them to a file..
select distinct(record) from asset.call_number WHERE record NOT IN (SELECT id FROM biblio.record_entry) and deleted=’f’
\o ‘/var/lib/postgresql/missing_bibs.txt’

select count(*) from biblio.record_entry WHERE edit_date < now() – ‘3 months'::INTERVAL AND deleted = FALSE AND id NOT IN (SELECT record FROM asset.call_number);
select count(*) from biblio.record_entry WHERE deleted = FALSE AND id NOT IN (SELECT record FROM asset.call_number);

evergreen=# select count(*) from biblio.record_entry WHERE edit_date < now() – ‘3 months'::INTERVAL AND deleted = FALSE AND id NOT IN (SELECT record FROM asset.call_number);

UPDATE biblio.record_entry SET deleted = TRUE WHERE edit_date < now() – ‘3 months'::INTERVAL AND deleted = FALSE AND id NOT IN (SELECT record FROM asset.call_number);

# dump specific schemae and restore them to a same or different database.

pg_dump -Fc –schema=asset –schema=biblio –schema=authority –schema=metabib evergreen-database-0 > items_n_bibs.sql

pg_restore -d evergreen-database-1 items_n_bibs.sql

Leave a Reply