Type Warnings

​

Overview

• ban-char-type: Flags char(n) / character(n) types
• timestamp-without-timezone: Flags timestamp without time zone

​

ban-char-type

​

When This Rule Triggers

• char(n) or character(n) is used in CREATE TABLE
• char(n) or character(n) is used in ALTER TABLE ADD COLUMN
• ::char(n) or ::character(n) is used in a CAST

• varchar(n) or character varying(n) is used
• text is used
• Other string types are used

​

Why It Matters

​

Space Padding Behavior

CREATE TABLE test (code char(10));

INSERT INTO test VALUES ('ABC');

-- The value is stored as 'ABC       ' (7 spaces added!)
SELECT code, length(code) FROM test;
-- Result: 'ABC       ', 10

-- Comparisons can be surprising
SELECT * FROM test WHERE code = 'ABC';  -- Matches! (trailing spaces ignored in comparison)
SELECT * FROM test WHERE code LIKE 'ABC';  -- Doesn't match! (LIKE is sensitive to spaces)

​

Problems This Causes

1. Storage waste: Always uses the full n bytes, even for short values
2. Surprising behavior: Space padding confuses application code
3. Comparison inconsistencies: = ignores trailing spaces, but LIKE doesn’t
4. Export/import issues: Trailing spaces may be preserved or stripped depending on the tool
5. No performance benefit: Modern PostgreSQL treats varchar and char identically

​

Better Alternatives

• Use text: Variable-length, no padding, no arbitrary limits
• Use varchar(n): Variable-length with optional limit, no padding
• Use check constraints: For validation, use CHECK (length(col) <= n) instead

​

Examples

CREATE TABLE t (code char(10))

CREATE TABLE t (code character(10))

SELECT x::char(5) FROM t

CREATE TABLE t (name varchar(100))

CREATE TABLE t (name text)

SELECT x::text FROM t

​

timestamp-without-timezone

​

When This Rule Triggers

• timestamp (without time zone) is used in CREATE TABLE
• timestamp without time zone is explicitly used
• ::timestamp (without time zone) is used in a CAST

• timestamptz or timestamp with time zone is used

​

Why It Matters

​

Lost Timezone Context

CREATE TABLE events (created_at timestamp);

INSERT INTO events VALUES ('2024-01-15 14:00:00');

-- Is this 2PM UTC? 2PM EST? 2PM local? You can't tell!

1. Ambiguity: You don’t know what timezone the timestamp represents
2. DST issues: Daylight saving time transitions create duplicate or missing hours
3. Global applications: Users in different timezones see different local times with no context
4. Comparison issues: Comparing timestamps from different timezones is meaningless

​

Real-World Example

-- User in New York (EST) creates an event at 2PM local
INSERT INTO events VALUES ('2024-01-15 14:00:00');

-- User in London (GMT) creates an event at 2PM local
INSERT INTO events VALUES ('2024-01-15 14:00:00');

-- These look identical but represent times 5 hours apart!

​

Better Alternative: timestamptz

CREATE TABLE events (created_at timestamptz);

INSERT INTO events VALUES ('2024-01-15 14:00:00-05:00');  -- EST
INSERT INTO events VALUES ('2024-01-15 14:00:00+00:00');  -- UTC

-- These are correctly stored as different times
-- PostgreSQL converts them to UTC internally
-- They display in your session's timezone

​

Benefits of timestamptz

1. Unambiguous: Timezone is part of the value
2. Correct comparisons: Times are stored in UTC, compared accurately
3. Automatic conversion: PostgreSQL converts to/from your session timezone
4. DST-safe: Handles daylight saving time correctly
5. Global-ready: Works correctly for users worldwide

​

Common Misconceptions

• Reality: timestamptz stores UTC internally, displays in session timezone

• Reality: Performance difference is negligible; correctness matters more

• Reality: Even local users experience DST; plus your app may expand globally

​

Examples

CREATE TABLE t (created_at timestamp)

CREATE TABLE t (created_at timestamp without time zone)

SELECT now()::timestamp

CREATE TABLE t (created_at timestamptz)

CREATE TABLE t (created_at timestamp with time zone)

SELECT now()::timestamptz

​

Implementation Details

​

ban-char-type

1. Checking CREATE TABLE and ALTER TABLE ADD COLUMN for ColumnDef nodes
2. Checking CAST expressions for TypeCast nodes
3. Inspecting TypeName to see if it’s bpchar (internal name for char)
4. Reporting a warning at the column or cast location

​

timestamp-without-timezone

1. Checking CREATE TABLE and ALTER TABLE ADD COLUMN for ColumnDef nodes
2. Checking CAST expressions for TypeCast nodes
3. Inspecting TypeName to see if it’s timestamp (without timezone)
4. Reporting a warning at the column or cast location

​

How to Disable

rules:
  ban-char-type:
    enabled: false
  timestamp-without-timezone:
    enabled: false

-- pgvet: ignore ban-char-type
CREATE TABLE legacy_codes (code char(10));

-- pgvet: ignore timestamp-without-timezone
CREATE TABLE local_events (event_time timestamp);