🔢 Data Types¶

This page explains what Meerschaum data types (dtypes) are, when to use each one, and how dtype coercion behaves during syncs. For the dtype table in context with the rest of the metadata, see parameters.dtypes.

What dtypes are¶

A pipe's dtypes map each column to a storage type. They live under the dtypes key of the parameters dictionary and are surfaced through the convenience attribute Pipe.dtypes.

import meerschaum as mrsm

pipe = mrsm.Pipe(
    'demo', 'dtypes',
    instance='sql:memory',
    columns={'datetime': 'ts', 'id': 'station'},
    dtypes={
        'ts': 'datetime',
        'station': 'int',
        'reading': 'numeric',
    },
)

print(pipe.dtypes)
# {'ts': 'datetime', 'station': 'int', 'reading': 'numeric'}

If you don't set dtypes explicitly, they are inferred from the first sync and persisted into parameters['dtypes']. Explicitly setting them up front avoids surprises (e.g. an integer column that later receives a float).

Supported dtypes¶

The base Meerschaum dtypes and the SQL types they map to are below. The authoritative mapping lives in meerschaum/utils/dtypes/sql.py (get_db_type_from_pd_type and get_pd_type_from_db_type).

numeric vs float vs int¶

• int — exact whole numbers. Use for counts and integer keys.
• float — IEEE-754 double. Fast and compact, but subject to binary rounding error. Use for measurements where exactness isn't required.
• numeric — arbitrary-precision Decimal. Use when exactness matters (currency, precise identifiers) or when a column mixes integers and decimals. Optionally pin precision and scale with numeric[precision,scale].

By default, a column that starts as int and later receives float values is promoted to numeric (so no data is lost). This is governed by the mixed_numerics parameter:

pipe = mrsm.Pipe(
    'demo', 'numerics',
    instance='sql:memory',
    columns={'datetime': 'ts'},
    dtypes={'val': 'int'},
    parameters={'mixed_numerics': False},  # keep 'val' as int; do not promote to numeric
)

With mixed_numerics=False, the int→float promotion is suppressed, preventing a schema change on the target table (similar to static).

Precision (datetime units)¶

The precision parameter sets the granularity of the datetime axis and the value captured by autotime. Supported units (and aliases):

By default the dtype determines the precision — e.g. the default datetime64[us, UTC] is microsecond precision. Set it explicitly under parameters['precision']:

pipe = mrsm.Pipe(
    'demo', 'precision',
    instance='sql:memory',
    columns={'datetime': 'ts'},
    parameters={'precision': 'second'},
)

See parameters.precision for rounding intervals and round_to.

Geometry (PostGIS)¶

Spatial columns use the geometry dtype, optionally qualified with a geometry type and/or SRID: geometry[srid], geometry[type,srid], or geography. When no SRID is given, the default is EPSG:4326 (WGS 84).

import meerschaum as mrsm

pipe = mrsm.Pipe(
    'demo', 'spatial',
    instance='sql:main',  # a PostGIS-enabled PostgreSQL connector
    columns={'datetime': 'ts', 'id': 'station'},
    dtypes={'location': 'geometry[POINT, 4326]'},
)
pipe.sync([{'ts': '2025-01-01', 'station': 1, 'location': 'POINT (-82.4 34.85)'}])

Values may be shapely objects, WKT strings, or WKB (hex / GPKG) bytes — they are coerced automatically.

Coercion and enforcement¶

During syncing, incoming data is cast to the pipe's registered dtypes by Pipe.enforce_dtypes() before being written. This is controlled by the enforce parameter (default True):

• enforce=True (default) — every incoming DataFrame is coerced to match parameters['dtypes'].
• enforce=False — coercion is skipped for performance. Only use this when your source already produces clean, correctly-typed data.

When a sync introduces a new column or a value that conflicts with the inferred dtype, Meerschaum auto-alters the target table — adding the column or widening the type (e.g. int → numeric, or a numeric column → string if text arrives). This automatic schema evolution is disabled by static=True, in which case new columns and type changes are rejected instead.

Inspecting and changing dtypes¶

Read the current dtypes with Pipe.dtypes (or pipe.parameters['dtypes']). To change a dtype on an existing pipe, edit the parameter and persist:

import meerschaum as mrsm

pipe = mrsm.Pipe('demo', 'dtypes', instance='sql:main')
pipe.dtypes['reading'] = 'numeric'
pipe.edit()           # persist the change
# or: pipe.update_parameters({'dtypes': {'reading': 'numeric'}}, persist=True)

Common dtype issues¶

• A column became numeric (or string) unexpectedly. A later sync sent a float into an int column (promoted to numeric), or text into a numeric column (promoted to string). Set the dtype explicitly up front, or use mixed_numerics=False / static=True to lock the schema.
• Unknown Pandas data type '...'. Falling back to 'TEXT'. The dtype string isn't recognized. Use a base Meerschaum dtype or a valid Pandas dtype string.
• Timezone offsets disappeared / shifted. The datetime dtype is tz-aware and coerces everything to UTC. For tz-naive storage, use datetime64[us] (no timezone).
• Geometry stored as a base64 text blob. The instance isn't PostGIS-enabled — see Geometry.
• Schema won't change when I expect it to. static=True blocks new columns and type changes; remove it (or set enforce=True) to allow schema evolution.

See the full API reference for the underlying dtype utilities.