---
title: Nullable vs not nullable troubleshooting
meta:
  description: Common issues and solutions for nullable vs not nullable column decisions in ClickHouse and Tinybird.
---

# Nullable vs not nullable troubleshooting

Common issues and solutions for nullable vs not nullable column decisions in ClickHouse and Tinybird.

## When to use nullable

### Optional data

**Use case**: Data that may not be available

**Example**: User profile fields that are optional
```sql
-- Use nullable for optional data
CREATE TABLE users (
  id UInt32,
  name String,
  email Nullable(String),  -- Optional field
  phone Nullable(String)   -- Optional field
)
```

### External data sources

**Use case**: Data from external sources that may be incomplete

**Example**: API responses with missing fields
```sql
-- Handle incomplete external data
CREATE TABLE api_events (
  id UInt32,
  user_id Nullable(UInt32),  -- May be missing
  value Nullable(Float64)    -- May be missing
)
```

## When to use not nullable

### Required fields

**Use case**: Data that should always be present

**Example**: Primary keys and required business fields
```sql
-- Use not nullable for required fields
CREATE TABLE events (
  id UInt32,           -- Required
  timestamp DateTime,   -- Required
  event_type String    -- Required
)
```

### Performance-critical columns

**Use case**: Columns used in filters and joins

**Example**: Frequently queried columns
```sql
-- Use not nullable for performance
CREATE TABLE events (
  user_id UInt32,      -- Not nullable for performance
  date Date,           -- Not nullable for performance
  value Float64
)
```

## Performance considerations

### Nullable performance impact

**Issue**: Nullable columns are slower than non-nullable

**Solution**: Use nullable only when necessary
```sql
-- Avoid nullable for performance-critical columns
-- Instead of: Nullable(UInt32) user_id
-- Use: UInt32 user_id DEFAULT 0
```

### Index considerations

**Issue**: Nullable columns in indexes can be inefficient

**Solution**: Use non-nullable columns for indexes
```sql
-- Use non-nullable for indexes
-- Instead of indexing nullable_column
-- Use a computed non-nullable column
ALTER TABLE events ADD INDEX idx_safe_column
  COALESCE(nullable_column, 'default') TYPE minmax GRANULARITY 1
```

## Migration strategies

### Adding nullable columns

**Issue**: Adding nullable columns to existing tables

**Solution**: Use ALTER TABLE with default values
```sql
-- Add nullable column with default
ALTER TABLE events ADD COLUMN optional_field Nullable(String) DEFAULT NULL
```

### Converting nullable to not nullable

**Issue**: Converting nullable columns to not nullable

**Solution**: Clean data first, then convert
```sql
-- Clean data before conversion
UPDATE events SET nullable_column = 'default' WHERE nullable_column IS NULL
ALTER TABLE events MODIFY COLUMN nullable_column String
```

## Best practices

1. **Use nullable sparingly** - Only for truly optional data
2. **Consider performance** - Avoid nullable for frequently queried columns
3. **Use defaults** - Consider default values instead of nullable
4. **Document decisions** - Keep track of nullable vs not nullable choices
5. **Plan migrations** - Consider impact when changing nullability

## Related documentation

- [Data types](/sql-reference/data-types)
- [Performance optimization](/forward/work-with-data/optimize)