Why would you need to include number of pages in a report?

• physical collections inventory 
• planning collection shifts, especially when books are not located together and direct measurement (linear feet) is not feasible
• estimating the number of trucks needed to move materials to the Annex or another location
• doing tracers; height of volume and number of pages give an approximation of the size of the item being searched for
• counting the number of exposures needed to scan or copy a book

Where are page number data fields stored?

• in the MARC record, field 300, subfield a (the 300 field and the a subfield are both repeatable in a record). See Marc21 format for bibliographic data for details.
• page numbers are also extracted from the folio_inventory.instance table jsonb array and written to the instance_physical_descriptions derived table. See derivation code for this table.
• NOTE: Some electronic resources have a 300 field subfield a showing page numbers, if the original form of the work was print material

This example below shows 236 pages in the item, which is an electronic resources. If your query is only pulling items from a physical location, you should not get any electronic resources. However, it is good to be aware that the physical description may include

It is impossible to account for every variation of page number entry in your query

Basic code to extract physical description from the instance table

This method does not use the problematic marc__t table. This code extracts the "physical descriptions" array from the instance record; records that do not have a physical descriptions array will not drop out, because a left join lateral function is used. Note that when you choose a specific physical location, the electronic resources will not be included. 

WITH parameters as (

SELECT 

/*Enter the location code for the library between the apostrophes, e.g. 'olin' , 'law,res' /*

(SELECT 
'olin'::VARCHAR as location_code_filter
),

 instance.id AS instance_id,
    instance.jsonb#>>'{hrid}' AS instance_hrid,
    instance.jsonb#>>'{title}' as title,
    location__t.code,
    phys.jsonb#>>'{}' AS field_300,
    CASE
        WHEN substring (phys.jsonb#>>'{}','\d{1,4}') IS NOT NULL
        AND (phys.jsonb#>>'{}' like '%page%' or phys.jsonb#>>'{}' LIKE '%p.%' or phys.jsonb#>>'{}' LIKE '%leaves%' or phys.jsonb#>>'{}' LIKE '% l.%' )

       THEN substring (phys.jsonb#>>'{}','\d{1,4}')::INT
        ELSE NULL
    END AS page_count_estimate,

phys.ordinality

FROM folio_inventory.instance


LEFT JOIN LATERAL jsonb_array_elements (jsonb_extract_path (instance.jsonb,'physicalDescriptions')) WITH ordinality AS phys (jsonb)
ON TRUE

LEFT JOIN folio_inventory.holdings_record__t  on instance.id = holdings_record__t.instance_id  

LEFT JOIN folio_inventory.location__t  on holdings_record__t.permanent_location_id = location__t.id  

WHERE 
CASE WHEN 

   (SELECT location_code_filter FROM parameters IS NULL) 
THEN location__t.code !=‘serv,remo’ 
ELSE location__t.code = (SELECT location_code_filter from parameters)
END

;

Result: