How to Load a Statewide Property File Into PostgreSQL

A statewide property export is a good fit for PostgreSQL. It is big enough that spreadsheets give up, small enough that a single database server handles it without special tuning, and it changes on a monthly cycle, which suits a table that keeps one copy per month. Once it is loaded, joining it to your own data, filtering by mailing address or comparing this month with last month are ordinary SQL queries.

This guide walks through a load that you can repeat every month without surprises: a text-only staging table, a typed table with sensible column types, the indexes that pay for themselves, and a partitioned layout for monthly snapshots. It covers all 46 columns in every Bulk Property Records export, and the field dictionary explains what each one means.

The shape of the load

Loading straight into a typed table looks simpler but fails badly: one unparseable value anywhere in a few million rows aborts the whole COPY, and you learn about problems one at a time. A two-step load is more forgiving:

Stage. COPY every part of the export into a table where every column is text. Nothing can fail to parse, so the load either completes or stops on a genuinely malformed file.
Profile. Look for values that will not convert, and for duplicate keys, while the data is still text.
Convert. INSERT … SELECT into a typed table for the month, with blanks turned into NULL.
Attach and index. Add the month to the partitioned table and build indexes once, after the data is in.

Larger exports arrive as several CSV parts of up to 50,000 records each, all with the same header row. The staging step is where you combine them; nothing else in the process needs to know how many parts there were.

Step 1: Create the staging table

One text column per export column, named exactly as in the CSV header and in the same order. The header names contain spaces and punctuation, so they are double-quoted, for example "Owner Mailing Zip". The typed table in step 4 switches to short lowercase names. The table is UNLOGGED, which skips the write-ahead log and makes bulk loads noticeably faster. The trade-off is that an unlogged table is emptied after a crash and is not copied to replicas. For a staging table you can reload from the original files, that does not matter.

staging tablesql

CREATE SCHEMA IF NOT EXISTS propdata;

-- UNLOGGED: no write-ahead log, so loads are faster. The table is emptied
-- after a crash, which is fine for a staging table you can reload.
-- Column names are the CSV header names, quoted, in header order.
CREATE UNLOGGED TABLE propdata.staging (
  "Property ID"                text,
  "Address"                    text,
  "City"                       text,
  "State"                      text,
  "Zip"                        text,
  "County"                     text,
  "APN"                        text,
  "Property Type"              text,
  "Bedrooms"                   text,
  "Bathrooms"                  text,
  "Living Square Feet"         text,
  "Lot (Square Feet)"          text,
  "Year Built"                 text,
  "Units Count"                text,
  "Owner 1 First Name"         text,
  "Owner 1 Last Name"          text,
  "Owner 2 First Name"         text,
  "Owner 2 Last Name"          text,
  "Owner Mailing Address"      text,
  "Owner Mailing City"         text,
  "Owner Mailing State"        text,
  "Owner Mailing Zip"          text,
  "Owner Type"                 text,
  "Owner Occupied"             text,
  "Ownership Length (Months)"  text,
  "Vacant?"                    text,
  "Estimated Value"            text,
  "Estimated Equity"           text,
  "Equity Percent"             text,
  "Mortgage Balance"           text,
  "Last Sale Date"             text,
  "Last Sale Price"            text,
  "Preforeclosure"             text,
  "Corporate Owned"            text,
  "Free And Clear"             text,
  "Motivation Score"           text,
  "Years Owned"                text,
  "DTV"                        text,
  "Open Lien Count"            text,
  "Lien 1 Lender"              text,
  "Lien 1 Rate"                text,
  "Foreclosure Status"         text,
  "Foreclosure Auction Date"   text,
  "Tax Delinquent Year"        text,
  "Owner Property Count"       text,
  "Data Version"               text
);

COPY maps CSV columns to table columns by position, not by name. Before the first load, and any time you start using a new export, compare the header with the table definition:

check the headershell

# Compare the file's header with the staging table's columns before loading.
head -n 1 exports/2026-10/part_001.csv | tr ',' '\n' > header.txt
psql -At -c "SELECT column_name FROM information_schema.columns
             WHERE table_schema = 'propdata' AND table_name = 'staging'
             ORDER BY ordinal_position" > table.txt
diff header.txt table.txt && echo "columns match"

If diff prints anything, adjust the staging table (or pass an explicit column list to COPY) before loading. A shifted column would load without error and quietly put mailing ZIPs into the city column.

Step 2: COPY the parts in

There are two forms. Server-side COPY … FROM 'file' reads a file on the database server and needs superuser or the pg_read_server_files role. Client-side \copy, a psql command, reads the file on your machine and streams it to the server. On a managed database (RDS, Cloud SQL, Supabase and similar) \copy is usually the only option, and it is fast enough for this size of data.

one partsql

-- From psql on your own machine. \copy reads the file client-side, so it
-- works against a managed database where you can't put files on the server.
\copy propdata.staging FROM 'part_001.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')

For a whole export directory, loop over the parts and stop at the first error:

load_parts.shshell

#!/usr/bin/env bash
# Load every CSV part of an export into the staging table, then count rows.
set -euo pipefail
export PGDATABASE=property

psql -v ON_ERROR_STOP=1 -c "TRUNCATE propdata.staging"

for f in exports/2026-10/*.csv; do
  echo "loading $f"
  psql -v ON_ERROR_STOP=1 -c "\\copy propdata.staging FROM '$f' WITH (FORMAT csv, HEADER true)"
done

psql -c "SELECT count(*) FROM propdata.staging"

The final count should equal the number of records in the export. If you kept the line counts from wc -l, remember each part has its own header line, so subtract one per file.

Step 3: Profile before converting

Three small helper functions turn text into numbers, booleans and dates, returning NULL for blanks and for anything that does not parse. That makes the conversion step robust, but it also means a systematic problem, such as a currency symbol in a value column, would silently become a column of NULLs. So profile first.

conversion helperssql

-- Blank or unparseable text becomes NULL instead of failing the whole insert.
CREATE OR REPLACE FUNCTION propdata.to_num(v text) RETURNS numeric
LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE WHEN btrim(v) ~ '^-?[0-9]+(\.[0-9]+)?$' THEN btrim(v)::numeric END
$$;

CREATE OR REPLACE FUNCTION propdata.to_bool(v text) RETURNS boolean
LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE lower(btrim(v))
    WHEN 'true' THEN true  WHEN 't' THEN true  WHEN 'yes' THEN true
    WHEN 'y'    THEN true  WHEN '1' THEN true
    WHEN 'false' THEN false WHEN 'f' THEN false WHEN 'no' THEN false
    WHEN 'n'     THEN false WHEN '0' THEN false
  END
$$;

CREATE OR REPLACE FUNCTION propdata.to_date(v text) RETURNS date
LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE WHEN btrim(v) ~ '^\d{4}-\d{2}-\d{2}$' THEN btrim(v)::date END
$$;

Exports write yes/no columns as true and false; the flag helper also accepts other common spellings, in case you load files from elsewhere. Check what your file actually contains with SELECT "Owner Occupied", count(*) FROM propdata.staging GROUP BY 1. A blank flag stays NULL, which means “not supplied”, not “no”.

profile the staging tablesql

-- Values that are present but will not parse. Expect zero rows per column;
-- anything else is worth a look before you convert.
SELECT 'Estimated Value' AS col, "Estimated Value" AS raw, count(*)
FROM propdata.staging
WHERE nullif(btrim("Estimated Value"), '') IS NOT NULL
  AND propdata.to_num("Estimated Value") IS NULL
GROUP BY 1, 2
UNION ALL
SELECT 'Last Sale Date', "Last Sale Date", count(*)
FROM propdata.staging
WHERE nullif(btrim("Last Sale Date"), '') IS NOT NULL
  AND propdata.to_date("Last Sale Date") IS NULL
GROUP BY 1, 2
UNION ALL
SELECT 'Owner Occupied', "Owner Occupied", count(*)
FROM propdata.staging
WHERE nullif(btrim("Owner Occupied"), '') IS NOT NULL
  AND propdata.to_bool("Owner Occupied") IS NULL
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

-- Is Property ID unique? Should return nothing.
SELECT "Property ID", count(*)
FROM propdata.staging
GROUP BY 1
HAVING count(*) > 1
LIMIT 20;

-- Is State + County + APN unique? Look at anything this returns.
SELECT "State", "County", "APN", count(*)
FROM propdata.staging
WHERE nullif(btrim("APN"), '') IS NOT NULL
GROUP BY 1, 2, 3
HAVING count(*) > 1
LIMIT 20;

Extend the first query to every numeric and date column you care about. For a statewide file it takes a few sequential scans, which is cheap compared with discovering a problem after the indexes are built.

The duplicate checks matter because Property ID is the primary key of the typed table, and State + County + APN is how the county identifies a parcel. If either returns rows, look at them before deciding what to do. A typical cause is a parcel exported twice because two overlapping areas were combined, in which case keep one copy.

Step 4: The typed, partitioned table

The main table holds every month you keep, partitioned by snapshot_month, which comes from the file’s Data Version column (for example 2026-10). Column names are short lowercase versions of the headers, so queries don’t need quotes. Each month is its own physical table, which makes adding and removing a month a metadata operation rather than a large DELETE.

partitioned tablesql

CREATE TABLE propdata.properties (
  snapshot_month       date         NOT NULL,  -- first day of the Data Version month
  property_id          text NOT NULL,
  address              text,
  city                 text,
  state                char(2),
  zip                  text,
  county               text,
  apn                  text,
  property_type        text,
  beds                 smallint,
  baths                numeric(4,1),
  sqft                 integer,
  lot_sqft             bigint,
  year_built           smallint,
  units                integer,
  owner1_first         text,
  owner1_last          text,
  owner2_first         text,
  owner2_last          text,
  mail_street          text,
  mail_city            text,
  mail_state           char(2),
  mail_zip             text,
  owner_type           text,
  owner_occupied       boolean,
  ownership_months     integer,
  vacant               boolean,
  est_value            bigint,
  equity               bigint,
  equity_pct           numeric(7,2),
  mortgage_balance     bigint,
  last_sale_date       date,
  last_sale_price      bigint,
  preforeclosure       boolean,
  corporate_owned      boolean,
  free_and_clear       boolean,
  motivation_score     smallint,
  years_owned          numeric(5,1),
  dtv                  numeric(7,2),
  open_lien_count      smallint,
  lien1_lender         text,
  lien1_rate           numeric(6,3),
  foreclosure_status   text,
  auction_date         date,
  tax_delinquent_year  smallint,
  owner_property_count integer,
  data_version         char(7),
  PRIMARY KEY (snapshot_month, property_id)
) PARTITION BY LIST (snapshot_month);

Why these types

Columns	Type	Reason
property_id, apn, zip, mail_zip	text	Identifiers, not numbers. Leading zeros and dashes all survive.
state, mail_state	char(2)	Always two letters, upper-cased on the way in.
est_value, equity, …	bigint	Whole dollars. bigint avoids overflow on large commercial values and sums.
equity_pct, dtv	numeric(7,2)	0–100 scale, but dtv can exceed 100 and equity_pct can go negative.
last_sale_date, auction_date	date	Delivered as YYYY-MM-DD, so they convert directly; blanks become NULL.
Yes/no columns	boolean	Converted once, so queries say WHERE free_and_clear instead of comparing strings.

If you only ever keep one month, drop the PARTITION BY clause and the snapshot_month column; everything else in this guide still applies.

Step 5: Convert one month and attach it

Build the month as a standalone table, add a CHECK constraint that matches the partition bound, then attach it. With the constraint in place, PostgreSQL can attach the partition without scanning it to verify every row belongs there.

convert and attach Octobersql

BEGIN;

CREATE TABLE propdata.properties_2026_10
  (LIKE propdata.properties INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

INSERT INTO propdata.properties_2026_10
SELECT
  (btrim("Data Version") || '-01')::date,
  btrim("Property ID"),
  nullif(btrim("Address"), ''),
  nullif(btrim("City"), ''),
  nullif(upper(btrim("State")), ''),
  nullif(btrim("Zip"), ''),
  nullif(btrim("County"), ''),
  nullif(btrim("APN"), ''),
  nullif(btrim("Property Type"), ''),
  propdata.to_num("Bedrooms")::smallint,
  propdata.to_num("Bathrooms"),
  propdata.to_num("Living Square Feet")::integer,
  propdata.to_num("Lot (Square Feet)")::bigint,
  propdata.to_num("Year Built")::smallint,
  propdata.to_num("Units Count")::integer,
  nullif(btrim("Owner 1 First Name"), ''),
  nullif(btrim("Owner 1 Last Name"), ''),
  nullif(btrim("Owner 2 First Name"), ''),
  nullif(btrim("Owner 2 Last Name"), ''),
  nullif(btrim("Owner Mailing Address"), ''),
  nullif(btrim("Owner Mailing City"), ''),
  nullif(upper(btrim("Owner Mailing State")), ''),
  nullif(btrim("Owner Mailing Zip"), ''),
  nullif(btrim("Owner Type"), ''),
  propdata.to_bool("Owner Occupied"),
  propdata.to_num("Ownership Length (Months)")::integer,
  propdata.to_bool("Vacant?"),
  propdata.to_num("Estimated Value")::bigint,
  propdata.to_num("Estimated Equity")::bigint,
  propdata.to_num("Equity Percent"),
  propdata.to_num("Mortgage Balance")::bigint,
  propdata.to_date("Last Sale Date"),
  propdata.to_num("Last Sale Price")::bigint,
  propdata.to_bool("Preforeclosure"),
  propdata.to_bool("Corporate Owned"),
  propdata.to_bool("Free And Clear"),
  propdata.to_num("Motivation Score")::smallint,
  propdata.to_num("Years Owned"),
  propdata.to_num("DTV"),
  propdata.to_num("Open Lien Count")::smallint,
  nullif(btrim("Lien 1 Lender"), ''),
  propdata.to_num("Lien 1 Rate"),
  nullif(btrim("Foreclosure Status"), ''),
  propdata.to_date("Foreclosure Auction Date"),
  propdata.to_num("Tax Delinquent Year")::smallint,
  propdata.to_num("Owner Property Count")::integer,
  nullif(btrim("Data Version"), '')
FROM propdata.staging
WHERE nullif(btrim("Property ID"), '') IS NOT NULL;

-- Lets ATTACH skip its validation scan of the whole partition. It also fails
-- if any row's Data Version is not 2026-10, which catches mixed-up files.
ALTER TABLE propdata.properties_2026_10
  ADD CONSTRAINT properties_2026_10_month CHECK (snapshot_month = DATE '2026-10-01');

ALTER TABLE propdata.properties
  ATTACH PARTITION propdata.properties_2026_10 FOR VALUES IN ('2026-10-01');

COMMIT;

Running it in one transaction means a failure leaves nothing half-built. The snapshot_month value comes from Data Version, the month the data is from, not the date you loaded it. If you load late or reload an old file, the month still describes the data, and if a part from another month slipped into the directory, the CHECK constraint fails and the whole transaction rolls back.

Step 6: The indexes worth adding

Create indexes after the data is loaded, not before: building an index once over sorted data is much faster than maintaining it row by row during a bulk insert. Indexes created on the partitioned parent are created on every partition, including ones you attach later.

indexessql

-- Give index builds more memory for this session only.
SET maintenance_work_mem = '1GB';

-- 1. Parcel lookups and joins. The primary key already covers
--    (snapshot_month, property_id); this one serves parcel lookups by the
--    county's identifier, such as "every version of this parcel".
CREATE INDEX ON propdata.properties (state, county, apn);

-- 2. Area filters by the owner's mailing ZIP.
CREATE INDEX ON propdata.properties (mail_zip);

-- 3. Owner mailing address, normalised the same way your queries will
--    normalise it. Used for "everything mailed to this address".
CREATE INDEX ON propdata.properties (
  (upper(regexp_replace(mail_street, '[^A-Za-z0-9 ]', '', 'g'))),
  mail_zip
);

-- 4. Optional: small partial indexes for the segments you filter on most.
CREATE INDEX ON propdata.properties (state, county) WHERE NOT owner_occupied;
CREATE INDEX ON propdata.properties (state, county) WHERE free_and_clear;

ANALYZE propdata.properties;

Index	Serves	Skip it if
`state, county, apn`	Joins to your own parcel lists, lookups of one parcel across months.	Never. It is how the county identifies a parcel.
`mail_zip`	Filters and counts by the owner's mailing ZIP, such as out-of-state owners in a target area.	You never filter by ZIP.
`mail_street + ZIP, normalised`	Grouping or looking up every property mailed to one address: portfolio owners, property managers.	You never work by owner.
`partial indexes`	Fast counts and pulls of one segment per county, such as owners who don't live at the property.	The segment covers a large share of rows; a scan is then just as fast.

An expression index is only used when the query contains exactly the same expression. Decide on one normalisation for mailing addresses, put it in the index, and use it everywhere. Wrapping it in a SQL function marked IMMUTABLE keeps queries and index in step.

queries that use (and miss) the indexessql

-- Uses index 2.
SELECT count(*) FROM propdata.properties
WHERE snapshot_month = DATE '2026-10-01'
  AND mail_zip = '30309';

-- Uses index 3: same normalisation on both sides.
SELECT address, owner1_first, owner1_last, est_value
FROM propdata.properties
WHERE upper(regexp_replace(mail_street, '[^A-Za-z0-9 ]', '', 'g')) = '100 PEACHTREE ST NW'
  AND mail_zip = '30303';

-- Does NOT use index 3: the expression differs (no regexp_replace()).
SELECT count(*) FROM propdata.properties
WHERE upper(mail_street) = '100 PEACHTREE ST NW';

Run EXPLAIN on your common queries after building indexes. If a query you expected to use an index shows a sequential scan, the expression usually differs in some small way, or the filter matches so many rows that a scan is genuinely cheaper.

What about the property’s own ZIP?

The export has both: Zip is where the property is, Owner Mailing Zip is where the owner receives mail. To filter by where the property is, use state and county, which index 1 already covers, or add CREATE INDEX ON propdata.properties (zip) if you work ZIP by ZIP.

Step 7: Keep monthly snapshots

Each month repeats steps 2 to 5 with a new partition name and date:

TRUNCATE propdata.staging and load the new parts.
Profile, paying attention to any column whose blank count changed sharply from last month.
Create properties_2026_11, convert, add the CHECK, attach.
Run ANALYZE propdata.properties so the planner knows about the new month.

A view gives reports a stable name for the latest month, and old months can be retired cleanly:

current view and retentionsql

-- Always the newest month, so reports never hardcode a date.
CREATE OR REPLACE VIEW propdata.current_properties AS
SELECT * FROM propdata.properties
WHERE snapshot_month = (SELECT max(snapshot_month) FROM propdata.properties);

-- Retire a month you no longer need: detach, archive, then drop.
ALTER TABLE propdata.properties DETACH PARTITION propdata.properties_2026_04;
-- pg_dump -t propdata.properties_2026_04 -Fc -f properties_2026_04.dump
DROP TABLE propdata.properties_2026_04;

Because every month shares a key, comparisons across months are plain joins. The query below finds parcels whose estimated equity percentage moved sharply between two refreshes:

compare two monthssql

-- Parcels whose estimated equity moved by 20 points or more between two months.
SELECT n.state, n.county, n.apn, n.address,
       o.equity_pct AS sep_pct, n.equity_pct AS oct_pct
FROM propdata.properties n
JOIN propdata.properties o
  ON o.state = n.state AND o.county = n.county AND o.apn = n.apn
WHERE n.snapshot_month = DATE '2026-10-01'
  AND o.snapshot_month = DATE '2026-09-01'
  AND abs(n.equity_pct - o.equity_pct) >= 20;

For a full treatment of what changes from month to month and how to separate real changes from formatting noise, see how often property records change.

How much disk to plan for

Storage depends on the state, on how many indexes you add and on how many months you keep, so measure rather than guess. After the first month is loaded and indexed, this query shows the real size per partition; multiply by the number of months you plan to retain and leave headroom for the staging table and index builds.

partition sizessql

-- Size of each monthly partition, including its indexes.
SELECT c.relname, pg_size_pretty(pg_total_relation_size(c.oid)) AS total
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'propdata.properties'::regclass
ORDER BY c.relname;

Record totals for each state are listed on the data by state page, which is a useful starting point when you are deciding between one big server and several smaller ones.

Load speed: what helps

Load into the unlogged staging table, index last. These two choices account for most of the difference between a fast and a slow load.
Raise maintenance_work_mem for the session that builds indexes, within what the server can spare.
Run \copy close to the database. Over a slow link the network, not PostgreSQL, sets the pace. Copying the files to a machine in the same region first often helps.
Load parts in parallel only if the server has spare cores and disk bandwidth. Two or three concurrent \copy sessions into the staging table can help; many more usually do not.

Analysts rarely need write access. Give them a read-only role and let each person log in with their own credentials:

read-only accesssql

CREATE ROLE analyst_ro NOLOGIN;
GRANT USAGE ON SCHEMA propdata TO analyst_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA propdata TO analyst_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA propdata GRANT SELECT ON TABLES TO analyst_ro;

-- Then give each person their own login role that inherits it.
CREATE ROLE dana LOGIN PASSWORD '...' IN ROLE analyst_ro;

Keep the database credentials out of shared notebooks and scripts; read them from environment variables or a secrets manager instead.

Checklist

Step	Done when
Header matches staging table	diff prints nothing
All parts loaded	Staging row count equals the export's record count
Profiled	No unexpected unparseable values; key duplicates explained
Converted and attached	New partition visible under propdata.properties
Indexed and analysed	EXPLAIN shows index use for your common queries
Old months handled	Detached and archived, or kept on purpose