Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.getparable.io/llms.txt

Use this file to discover all available pages before exploring further.

​
Basic People Queries

Start with these foundational queries to explore your church database. Each example builds your SQL confidence while providing immediate ministry value.

Query Customization Required

These example queries demonstrate common patterns but may require adjustments to match your specific database schema and field names. Test thoroughly in your environment before use.

​
Query Requirements

​
Schema Prefix

IMPORTANT: All tables in the Planning Center People module live in the planning_center schema. Always prefix table names with planning_center. in every query. ✅ CORRECT: SELECT * FROM planning_center.people_people ❌ INCORRECT: SELECT * FROM people_people

​
Row Level Security (RLS)

Row Level Security automatically filters results for:
  • tenant_organization_id – only data from your organization
  • system_status – only active records by default
Do not add these filters manually—RLS applies them for you and redundant conditions can slow queries or hide data:
  • ❌ WHERE tenant_organization_id = 1
  • ❌ WHERE system_status = 'active'
Trust the built-in RLS policies to keep your results scoped correctly while you focus on ministry-specific filters.

​
Table of Contents

​
Finding People

​
List Active People

-- View active people in your database
SELECT 
    person_id,
    first_name,
    last_name,
    name,
    status,
    membership,
    created_at
FROM planning_center.people_people
WHERE status = 'active'
ORDER BY last_name, first_name
LIMIT 100;

​
Search by Name

-- Find people by name (case-insensitive)
SELECT 
    person_id,
    name,
    nickname,
    status,
    membership,
    birthdate
FROM planning_center.people_people
WHERE status = 'active'
  AND (
    LOWER(first_name) LIKE '%sarah%'
    OR LOWER(last_name) LIKE '%johnson%'
    OR LOWER(nickname) LIKE '%sarah%'
  )
ORDER BY last_name, first_name;

​
Recent Additions

-- People added in the last 30 days
SELECT 
    person_id,
    name,
    status,
    membership,
    created_at,
    CURRENT_DATE - created_at::date as days_since_added
FROM planning_center.people_people
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  AND status = 'active'
ORDER BY created_at DESC;

​
Find by Person ID

-- Look up a specific person
SELECT 
    person_id,
    name,
    first_name,
    last_name,
    nickname,
    birthdate,
    anniversary,
    gender,
    membership,
    status,
    child,
    grade,
    graduation_year
FROM planning_center.people_people
WHERE person_id = 'YOUR_PERSON_ID'
  AND status = 'active';

​
Understanding Demographics

​
Age Distribution

-- Calculate ages and group people
SELECT 
    person_id,
    name,
    birthdate,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) as age,
    CASE 
        WHEN child = true THEN 'Child'
        WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 18 THEN 'Youth'
        WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 30 THEN 'Young Adult'
        WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 50 THEN 'Adult'
        WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 65 THEN 'Middle Age'
        ELSE 'Senior'
    END as age_group
FROM planning_center.people_people
WHERE birthdate IS NOT NULL
  AND status = 'active'
ORDER BY birthdate;

​
Children and Students

-- Find all children and students
SELECT 
    person_id,
    name,
    birthdate,
    grade,
    graduation_year,
    school_type,
    CASE 
        WHEN grade IS NOT NULL THEN 'Grade ' || grade::text
        WHEN graduation_year IS NOT NULL THEN 'Grad Year ' || graduation_year::text
        WHEN child = true THEN 'Child'
        ELSE 'Unknown'
    END as education_status
FROM planning_center.people_people
WHERE (child = true OR grade IS NOT NULL OR graduation_year IS NOT NULL)
  AND status = 'active'
ORDER BY grade, graduation_year, name;

​
Gender Breakdown

-- Analyze gender distribution
SELECT 
    gender,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as percentage
FROM planning_center.people_people
WHERE status = 'active'
GROUP BY gender
ORDER BY count DESC;

​
Upcoming Birthdays

-- Birthdays in the next 30 days
SELECT 
    person_id,
    name,
    birthdate,
    TO_CHAR(birthdate, 'Month DD') as birthday,
    EXTRACT(YEAR FROM AGE(
        DATE(EXTRACT(YEAR FROM CURRENT_DATE) || '-' || 
             EXTRACT(MONTH FROM birthdate) || '-' || 
             EXTRACT(DAY FROM birthdate)),
        birthdate
    )) as turning_age,
    -- Calculate days until birthday
    CASE 
        WHEN DATE_PART('doy', birthdate) >= DATE_PART('doy', CURRENT_DATE)
        THEN DATE_PART('doy', birthdate) - DATE_PART('doy', CURRENT_DATE)
        ELSE 365 - DATE_PART('doy', CURRENT_DATE) + DATE_PART('doy', birthdate)
    END as days_until
FROM planning_center.people_people
WHERE birthdate IS NOT NULL
  AND status = 'active'
  AND (
    -- Birthday in current year
    (EXTRACT(MONTH FROM birthdate) = EXTRACT(MONTH FROM CURRENT_DATE) 
     AND EXTRACT(DAY FROM birthdate) >= EXTRACT(DAY FROM CURRENT_DATE))
    OR
    (EXTRACT(MONTH FROM birthdate) = EXTRACT(MONTH FROM CURRENT_DATE + INTERVAL '1 month'))
  )
ORDER BY 
    EXTRACT(MONTH FROM birthdate),
    EXTRACT(DAY FROM birthdate);

​
Working with Households

​
List All Households

-- View all households with member counts
SELECT 
    household_id,
    name,
    member_count,
    primary_contact_name,
    created_at
FROM planning_center.people_households
ORDER BY name
LIMIT 100;

​
Find Household Members

-- Get all members of households
SELECT 
    h.household_id,
    h.name as household_name,
    h.member_count,
    p.name as member_name,
    p.birthdate,
    CASE 
        WHEN p.child = true THEN 'Child'
        ELSE 'Adult'
    END as member_type,
    hm.pending
FROM planning_center.people_households h
JOIN planning_center.people_household_memberships hm 
    ON h.household_id = hm.household_id
JOIN planning_center.people_people p 
    ON hm.person_id = p.person_id
WHERE p.status = 'active'
  AND hm.pending = false
ORDER BY h.name, p.birthdate;

​
Large Families

-- Find households with 5 or more members
SELECT 
    household_id,
    name,
    member_count,
    primary_contact_name
FROM planning_center.people_households
WHERE member_count >= 5
ORDER BY member_count DESC;

​
Single-Person Households

-- Identify people living alone
SELECT 
    h.household_id,
    h.name as household_name,
    p.name as person_name,
    p.birthdate,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) as age
FROM planning_center.people_households h
JOIN planning_center.people_household_memberships hm 
    ON h.household_id = hm.household_id
JOIN planning_center.people_people p 
    ON hm.person_id = p.person_id
WHERE h.member_count = 1
  AND p.status = 'active'
ORDER BY p.birthdate;

​
Contact Information

​
People with Email Addresses

-- Find primary email addresses
SELECT 
    p.person_id,
    p.name,
    e.address as email,
    e.location as email_type,
    e.is_primary,
    e.blocked
FROM planning_center.people_people p
JOIN planning_center.people_email_relationships er 
    ON p.person_id = er.person_id
JOIN planning_center.people_emails e 
    ON er.email_id = e.email_id
WHERE p.status = 'active'
  AND e.is_primary = true
  AND e.blocked = false
ORDER BY p.last_name, p.first_name
LIMIT 100;

​
People with Phone Numbers

-- Find phone numbers by type
SELECT 
    p.person_id,
    p.name,
    pn.number,
    pn.location as phone_type,
    pn.is_primary,
    pn.carrier_name
FROM planning_center.people_people p
JOIN planning_center.people_phone_number_relationships pnr 
    ON p.person_id = pnr.person_id
JOIN planning_center.people_phone_numbers pn 
    ON pnr.phone_number_id = pn.phone_number_id
WHERE p.status = 'active'
  AND pn.is_primary = true
ORDER BY p.last_name, p.first_name
LIMIT 100;

​
Complete Contact Info

-- Get all contact methods for active people
SELECT 
    p.person_id,
    p.name,
    e.address as email,
    pn.number as phone,
    a.street,
    a.city,
    a.state,
    a.zip
FROM planning_center.people_people p
LEFT JOIN planning_center.people_email_relationships er 
    ON p.person_id = er.person_id
LEFT JOIN planning_center.people_emails e 
    ON er.email_id = e.email_id AND e.is_primary = true
LEFT JOIN planning_center.people_phone_number_relationships pnr 
    ON p.person_id = pnr.person_id
LEFT JOIN planning_center.people_phone_numbers pn 
    ON pnr.phone_number_id = pn.phone_number_id AND pn.is_primary = true
LEFT JOIN planning_center.people_addresses a 
    ON p.person_id = a.person_id AND a.is_primary = true
WHERE p.status = 'active'
ORDER BY p.last_name, p.first_name
LIMIT 50;

​
People Missing Contact Info

-- Find people without email or phone
SELECT 
    p.person_id,
    p.name,
    p.created_at,
    CASE 
        WHEN e.email_id IS NULL AND pn.phone_number_id IS NULL THEN 'No Contact Info'
        WHEN e.email_id IS NULL THEN 'No Email'
        WHEN pn.phone_number_id IS NULL THEN 'No Phone'
        ELSE 'Has Contact Info'
    END as contact_status
FROM planning_center.people_people p
LEFT JOIN planning_center.people_email_relationships er 
    ON p.person_id = er.person_id
LEFT JOIN planning_center.people_emails e 
    ON er.email_id = e.email_id
LEFT JOIN planning_center.people_phone_number_relationships pnr 
    ON p.person_id = pnr.person_id
LEFT JOIN planning_center.people_phone_numbers pn 
    ON pnr.phone_number_id = pn.phone_number_id
WHERE p.status = 'active'
  AND p.child = false  -- Adults only
  AND (e.email_id IS NULL OR pn.phone_number_id IS NULL)
ORDER BY p.created_at DESC;

​
Membership and Status

​
Membership Levels

-- Count people by membership level
SELECT 
    membership,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as percentage
FROM planning_center.people_people
WHERE status = 'active'
GROUP BY membership
ORDER BY count DESC;

​
New Members

-- People who became members recently
SELECT 
    person_id,
    name,
    membership,
    created_at,
    updated_at
FROM planning_center.people_people
WHERE status = 'active'
  AND membership = 'Member'
  AND updated_at >= CURRENT_DATE - INTERVAL '90 days'
ORDER BY updated_at DESC;

​
Permission Levels

-- Find people with administrative permissions
SELECT 
    person_id,
    name,
    site_administrator,
    people_permissions,
    can_create_forms,
    can_email_lists
FROM planning_center.people_people
WHERE status = 'active'
  AND (
    site_administrator = true
    OR people_permissions IS NOT NULL
    OR can_create_forms = true
    OR can_email_lists = true
  )
ORDER BY name;

​
Inactive People

-- Recently inactivated people (for follow-up)
SELECT 
    p.person_id,
    p.name,
    p.inactivated_at,
    p.membership,
    ir.name as inactive_reason
FROM planning_center.people_people p
LEFT JOIN planning_center.people_inactive_reasons ir 
    ON p.inactive_reason_id = ir.inactive_reason_id
WHERE p.status = 'inactive'
  AND p.inactivated_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY p.inactivated_at DESC;

​
Date-Based Queries

​
Anniversary This Month

-- Wedding anniversaries this month
SELECT 
    person_id,
    name,
    anniversary,
    TO_CHAR(anniversary, 'Month DD') as anniversary_date,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, anniversary)) as years_married
FROM planning_center.people_people
WHERE anniversary IS NOT NULL
  AND status = 'active'
  AND EXTRACT(MONTH FROM anniversary) = EXTRACT(MONTH FROM CURRENT_DATE)
ORDER BY EXTRACT(DAY FROM anniversary);

​
People Added by Month

-- Track growth over the last year
SELECT 
    TO_CHAR(created_at, 'YYYY-MM') as month,
    COUNT(*) as people_added,
    COUNT(*) FILTER (WHERE child = true) as children_added,
    COUNT(*) FILTER (WHERE child = false OR child IS NULL) as adults_added
FROM planning_center.people_people
WHERE created_at >= CURRENT_DATE - INTERVAL '12 months'
  AND status = 'active'
GROUP BY TO_CHAR(created_at, 'YYYY-MM')
ORDER BY month DESC;

​
Long-Time Members

-- People who have been in database 5+ years
SELECT 
    person_id,
    name,
    membership,
    created_at,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, created_at)) as years_in_database
FROM planning_center.people_people
WHERE status = 'active'
  AND created_at <= CURRENT_DATE - INTERVAL '5 years'
ORDER BY created_at
LIMIT 100;

​
Basic Statistics

​
Overall Demographics Summary

-- High-level congregation overview
SELECT 
    COUNT(*) as total_people,
    COUNT(*) FILTER (WHERE status = 'active') as active_people,
    COUNT(*) FILTER (WHERE membership = 'Member') as members,
    COUNT(*) FILTER (WHERE child = true) as children,
    COUNT(*) FILTER (WHERE graduation_year IS NOT NULL) as students,
    COUNT(*) FILTER (WHERE gender = 'Male') as males,
    COUNT(*) FILTER (WHERE gender = 'Female') as females,
    ROUND(AVG(EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate))) FILTER (WHERE birthdate IS NOT NULL), 1) as avg_age
FROM planning_center.people_people;

​
Age Group Distribution

-- Breakdown by age categories
WITH age_groups AS (
    SELECT 
        CASE 
            WHEN birthdate IS NULL THEN 'Unknown'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 1 THEN 'Infant'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 5 THEN 'Preschool'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 12 THEN 'Elementary'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 18 THEN 'Youth'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 30 THEN 'Young Adult'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 50 THEN 'Adult'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) < 65 THEN 'Middle Age'
            ELSE 'Senior'
        END as age_group
    FROM planning_center.people_people
    WHERE status = 'active'
)
SELECT 
    age_group,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as percentage
FROM age_groups
GROUP BY age_group
ORDER BY 
    CASE age_group
        WHEN 'Infant' THEN 1
        WHEN 'Preschool' THEN 2
        WHEN 'Elementary' THEN 3
        WHEN 'Youth' THEN 4
        WHEN 'Young Adult' THEN 5
        WHEN 'Adult' THEN 6
        WHEN 'Middle Age' THEN 7
        WHEN 'Senior' THEN 8
        ELSE 9
    END;

​
Household Statistics

-- Household composition analysis
SELECT 
    'Total Households' as metric,
    COUNT(*)::text as value
FROM planning_center.people_households
UNION ALL
SELECT 
    'Average Household Size',
    ROUND(AVG(member_count), 2)::text
FROM planning_center.people_households
UNION ALL
SELECT 
    'Single-Person Households',
    COUNT(*)::text
FROM planning_center.people_households
WHERE member_count = 1
UNION ALL
SELECT 
    'Families with Children',
    COUNT(DISTINCT h.household_id)::text
FROM planning_center.people_households h
JOIN planning_center.people_household_memberships hm ON h.household_id = hm.household_id
JOIN planning_center.people_people p ON hm.person_id = p.person_id
WHERE p.child = true
ORDER BY metric;

​
Tips for Writing Queries

​
1. Always Filter by Status

WHERE status = 'active'  -- Unless you specifically need inactive people

​
2. Handle NULL Values

-- Use COALESCE to provide defaults
COALESCE(nickname, first_name) as preferred_name

-- Check for NULL before calculations
WHERE birthdate IS NOT NULL

​
3. Case-Insensitive Searches

WHERE LOWER(name) LIKE '%smith%'  -- Finds Smith, SMITH, smith, etc.

​
4. Date Calculations

-- Age from birthdate
EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) as age

-- Days since date
CURRENT_DATE - created_at::date as days_since

​
5. Use Proper Joins

-- LEFT JOIN when data might not exist
LEFT JOIN planning_center.people_emails e ON ...

-- INNER JOIN when data must exist
JOIN planning_center.people_household_memberships hm ON ...

​
Common Issues & Solutions

​
Issue: Duplicate people in results

Solution: Check your joins and consider using DISTINCT.

​
Issue: Missing people who should appear

Solution: Verify status = ‘active’ and check relationship joins.

​
Issue: Age calculations returning NULL

Solution: Ensure birthdate IS NOT NULL before calculating.

​
Issue: Contact info not showing

Solution: Use LEFT JOIN for optional data like emails and phones.

​
Next Steps

Ready for more complex queries? Continue with: