Default Rules

These 15 rules run automatically when you execute pgvet check. Each rule detects a specific SQL anti-pattern.

Quick Reference

Rule	Severity	Category	Description
select-star	warning	Best Practice	`SELECT *` is fragile — list columns explicitly
limit-without-order	warning	Correctness	`LIMIT` without `ORDER BY` is non-deterministic
not-in-subquery	error	Correctness	`NOT IN (SELECT ...)` broken with NULLs
for-update-no-skip	warning	Performance	`FOR UPDATE` without `SKIP LOCKED`/`NOWAIT`
distinct-on-order	warning	Correctness	`DISTINCT ON` needs matching `ORDER BY`
null-comparison	error	Correctness	`= NULL` or `<> NULL` always yields NULL
update-without-where	warning	Safety	`UPDATE` without `WHERE` updates all rows
delete-without-where	warning	Safety	`DELETE` without `WHERE` deletes all rows
insert-without-columns	warning	Best Practice	`INSERT` without column list is fragile
ban-char-type	warning	Best Practice	`char(n)` pads with spaces
timestamp-without-timezone	warning	Best Practice	Use `timestamptz` to preserve timezone
order-by-ordinal	warning	Best Practice	`ORDER BY 1` is fragile
group-by-ordinal	warning	Best Practice	`GROUP BY 1` is fragile
like-starts-with-wildcard	warning	Performance	`LIKE '%...'` prevents index usage
offset-without-limit	warning	Correctness	`OFFSET` without `LIMIT` likely a mistake

Correctness Rules

not-in-subquery

Rule Details

Severity: error
Name: not-in-subqueryNOT IN (SELECT ...) is broken when the subquery can return NULLs. PostgreSQL’s NULL semantics mean that if any subquery value is NULL, the entire expression yields NULL (not TRUE or FALSE), causing rows to be incorrectly excluded.Solution: Use NOT EXISTS instead.

Problematic Examples

-- WRONG: Broken if banned.user_id contains any NULLs
SELECT * FROM users 
WHERE id NOT IN (SELECT user_id FROM banned)

-- WRONG: Even with other columns
SELECT * FROM orders
WHERE user_id NOT IN (SELECT user_id FROM refunds)

Correct Examples

-- CORRECT: Use NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM banned b WHERE b.user_id = u.id
)

-- CORRECT: IN with subquery is fine
SELECT * FROM users
WHERE id IN (SELECT user_id FROM active)

-- CORRECT: NOT IN with literal list is fine
SELECT * FROM users
WHERE status NOT IN ('banned', 'deleted')

null-comparison

Rule Details

Severity: error
Name: null-comparisonUsing = NULL or <> NULL always yields NULL (not TRUE or FALSE) in SQL’s three-valued logic. These comparisons will never match any rows.Solution: Use IS NULL or IS NOT NULL.

Problematic Examples

-- WRONG: Never matches any rows
SELECT * FROM users WHERE id = NULL

-- WRONG: Never matches any rows
SELECT * FROM users WHERE id <> NULL

-- WRONG: Even in reverse order
SELECT * FROM users WHERE NULL = id

Correct Examples

-- CORRECT: Use IS NULL
SELECT * FROM users WHERE id IS NULL

-- CORRECT: Use IS NOT NULL
SELECT * FROM users WHERE id IS NOT NULL

-- CORRECT: Normal comparisons are fine
SELECT * FROM users WHERE id = 1

limit-without-order

Rule Details

Severity: warning
Name: limit-without-orderUsing LIMIT without ORDER BY produces non-deterministic results — PostgreSQL may return different rows on each execution.Exception: LIMIT 1 is exempted as it’s commonly used for existence checks.

Problematic Examples

-- WRONG: Which 10 users? Results vary between runs
SELECT id FROM users LIMIT 10

Correct Examples

-- CORRECT: Deterministic ordering
SELECT id FROM users ORDER BY id LIMIT 10

-- CORRECT: LIMIT 1 for existence check is exempted
SELECT id FROM users WHERE email = '[email protected]' LIMIT 1

-- CORRECT: No LIMIT at all
SELECT id FROM users

distinct-on-order

Rule Details

Severity: warning
Name: distinct-on-orderDISTINCT ON without a matching leading ORDER BY produces non-deterministic results. PostgreSQL returns the “first” row for each distinct group, but without ordering, which row is “first” is arbitrary.

Problematic Examples

-- WRONG: Which employee per dept? Non-deterministic
SELECT DISTINCT ON (dept_id) dept_id, name FROM employees

-- WRONG: ORDER BY doesn't match DISTINCT ON columns
SELECT DISTINCT ON (dept_id) dept_id, name 
FROM employees 
ORDER BY name

Correct Examples

-- CORRECT: ORDER BY matches DISTINCT ON
SELECT DISTINCT ON (dept_id) dept_id, name 
FROM employees 
ORDER BY dept_id, salary DESC

-- CORRECT: Plain DISTINCT is fine
SELECT DISTINCT status FROM orders

offset-without-limit

Rule Details

Severity: warning
Name: offset-without-limitOFFSET without LIMIT skips rows but then returns all remaining rows. This is rarely intentional and usually indicates a pagination bug.

Problematic Examples

-- WRONG: Returns all rows after row 10 (likely unintended)
SELECT * FROM users OFFSET 10

Correct Examples

-- CORRECT: Proper pagination
SELECT * FROM users LIMIT 10 OFFSET 5

-- CORRECT: LIMIT without OFFSET
SELECT * FROM users LIMIT 10

-- CORRECT: No pagination clauses
SELECT * FROM users

Safety Rules

update-without-where

Rule Details

Severity: warning
Name: update-without-whereUPDATE without a WHERE clause updates every row in the table. This is rarely intentional in application code.

Problematic Examples

-- WRONG: Updates every single user
UPDATE users SET active = false

Correct Examples

-- CORRECT: Updates specific user
UPDATE users SET active = false WHERE id = 1

-- CORRECT: Updates with JOIN condition
UPDATE orders SET status = 'shipped' 
FROM shipments 
WHERE orders.id = shipments.order_id

delete-without-where

Rule Details

Severity: warning
Name: delete-without-whereDELETE without a WHERE clause deletes every row in the table. Use TRUNCATE if this is intentional.

Problematic Examples

-- WRONG: Deletes all users
DELETE FROM users

Correct Examples

-- CORRECT: Deletes specific user
DELETE FROM users WHERE id = 1

-- CORRECT: Delete with USING join
DELETE FROM orders 
USING expired 
WHERE orders.id = expired.id

Performance Rules

for-update-no-skip

Rule Details

Severity: warning
Name: for-update-no-skipFOR UPDATE without SKIP LOCKED or NOWAIT can cause lock contention. If another transaction holds a lock, your query will wait indefinitely.Note: Only flags exclusive lock modes (FOR UPDATE and FOR NO KEY UPDATE). FOR SHARE is exempted.

Problematic Examples

-- WRONG: Will wait indefinitely if rows are locked
SELECT id FROM items ORDER BY id FOR UPDATE

Correct Examples

-- CORRECT: Skip locked rows (queue processing)
SELECT id FROM items ORDER BY id FOR UPDATE SKIP LOCKED

-- CORRECT: Fail fast if locked
SELECT id FROM items ORDER BY id FOR UPDATE NOWAIT

-- CORRECT: FOR SHARE doesn't require SKIP LOCKED
SELECT id FROM items WHERE id = 1 FOR SHARE

like-starts-with-wildcard

Rule Details

Severity: warning
Name: like-starts-with-wildcardLIKE or ILIKE patterns starting with % cannot use B-tree indexes, forcing a full table scan.Solution: Use PostgreSQL’s full-text search or trigram indexes for substring searches.

Problematic Examples

-- WRONG: Full table scan
SELECT * FROM users WHERE name LIKE '%test'

-- WRONG: Both leading and trailing wildcard
SELECT * FROM users WHERE name LIKE '%test%'

-- WRONG: ILIKE has same issue
SELECT * FROM users WHERE name ILIKE '%test'

Correct Examples

-- CORRECT: Trailing wildcard can use index
SELECT * FROM users WHERE name LIKE 'test%'

-- CORRECT: Exact match
SELECT * FROM users WHERE name LIKE 'test'

-- CORRECT: Equality comparison
SELECT * FROM users WHERE name = 'test'

Best Practice Rules

select-star

Rule Details

Severity: warning
Name: select-starSELECT * in the outermost query is fragile — if table columns change, your application code may break. Explicitly list the columns you need.Note: SELECT * inside CTEs is allowed.

Problematic Examples

-- WRONG: Fragile to schema changes
SELECT * FROM users WHERE id = 1

-- WRONG: Even with CTE, outer SELECT * is flagged
WITH f AS (SELECT id FROM users) 
SELECT * FROM f

-- WRONG: Both sides of UNION flagged
SELECT * FROM users UNION ALL SELECT * FROM admins

Correct Examples

-- CORRECT: Explicit columns
SELECT id, name FROM users

-- CORRECT: SELECT * inside CTE is allowed
WITH f AS (SELECT * FROM users) 
SELECT id, name FROM f

insert-without-columns

Rule Details

Severity: warning
Name: insert-without-columnsINSERT without an explicit column list depends on column order, which may change if columns are added or reordered.

Problematic Examples

-- WRONG: Depends on column order
INSERT INTO users VALUES (1, 'alice')

-- WRONG: INSERT SELECT without columns
INSERT INTO users SELECT * FROM temp

Correct Examples

-- CORRECT: Explicit columns
INSERT INTO users (id, name) VALUES (1, 'alice')

-- CORRECT: INSERT SELECT with columns
INSERT INTO users (id, name) SELECT id, name FROM temp

-- CORRECT: DEFAULT VALUES is allowed
INSERT INTO users DEFAULT VALUES

ban-char-type

Rule Details

Severity: warning
Name: ban-char-typechar(n) (or character(n)) pads values with spaces to the specified length. This behavior is surprising and rarely desired. Use text or varchar instead.

Problematic Examples

-- WRONG: Will pad with spaces
CREATE TABLE t (code char(10))

-- WRONG: character(n) is the same as char(n)
CREATE TABLE t (code character(10))

-- WRONG: CAST to char also flagged
SELECT x::char(5) FROM t

Correct Examples

-- CORRECT: Use varchar
CREATE TABLE t (name varchar(100))

-- CORRECT: Use text (preferred)
CREATE TABLE t (name text)

-- CORRECT: CAST to text
SELECT x::text FROM t

timestamp-without-timezone

Rule Details

Severity: warning
Name: timestamp-without-timezonetimestamp without time zone loses timezone context. When users in different timezones interact with your data, this causes ambiguity and bugs.Solution: Use timestamptz (or timestamp with time zone).

Problematic Examples

-- WRONG: Loses timezone context
CREATE TABLE t (created_at timestamp)

-- WRONG: Explicit "without time zone"
CREATE TABLE t (created_at timestamp without time zone)

-- WRONG: CAST to timestamp
SELECT now()::timestamp

Correct Examples

-- CORRECT: Use timestamptz
CREATE TABLE t (created_at timestamptz)

-- CORRECT: Explicit "with time zone"
CREATE TABLE t (created_at timestamp with time zone)

-- CORRECT: CAST to timestamptz
SELECT now()::timestamptz

order-by-ordinal

Rule Details

Severity: warning
Name: order-by-ordinalORDER BY using ordinal positions (e.g., ORDER BY 1) is fragile — if the SELECT list changes, the ordering silently breaks.

Problematic Examples

-- WRONG: Fragile positional reference
SELECT id, name FROM users ORDER BY 1

-- WRONG: Multiple ordinals
SELECT id, name FROM users ORDER BY 1, 2

Correct Examples

-- CORRECT: Use column name
SELECT id, name FROM users ORDER BY id

-- CORRECT: No ORDER BY
SELECT id FROM users

group-by-ordinal

Rule Details

Severity: warning
Name: group-by-ordinalGROUP BY using ordinal positions (e.g., GROUP BY 1) is fragile — if the SELECT list changes, the grouping silently breaks.

Problematic Examples

-- WRONG: Fragile positional reference
SELECT status, count(*) FROM users GROUP BY 1

-- WRONG: Multiple ordinals
SELECT a, b, count(*) FROM t GROUP BY 1, 2

Correct Examples

-- CORRECT: Use column name
SELECT status, count(*) FROM users GROUP BY status

-- CORRECT: No GROUP BY
SELECT id FROM users

Disabling Specific Rules

If a rule doesn’t fit your use case, disable it:

pgvet check --disable select-star
pgvet check --disable select-star,limit-without-order

Next Steps

Opt-In Rules

Learn about rules that require explicit enablement

Get Started

Usage

Rules

Advanced

Default Rules

Quick Reference

Correctness Rules

not-in-subquery

null-comparison

limit-without-order

distinct-on-order

offset-without-limit

Safety Rules

update-without-where

delete-without-where

Performance Rules

for-update-no-skip

like-starts-with-wildcard

Best Practice Rules

select-star

insert-without-columns

ban-char-type

timestamp-without-timezone

order-by-ordinal

group-by-ordinal

Disabling Specific Rules

Next Steps

Opt-In Rules

Build docs developers (and LLMs) love

Get Started

Usage

Rules

Advanced

​Quick Reference

​Correctness Rules

​not-in-subquery

​null-comparison

​limit-without-order

​distinct-on-order

​offset-without-limit

​Safety Rules

​update-without-where

​delete-without-where

​Performance Rules

​for-update-no-skip

​like-starts-with-wildcard

​Best Practice Rules

​select-star

​insert-without-columns

​ban-char-type

​timestamp-without-timezone

​order-by-ordinal

​group-by-ordinal

​Disabling Specific Rules

​Next Steps

Opt-In Rules

Build docs developers (and LLMs) love

Quick Reference

Correctness Rules

not-in-subquery

null-comparison

limit-without-order

distinct-on-order

offset-without-limit

Safety Rules

update-without-where

delete-without-where

Performance Rules

for-update-no-skip

like-starts-with-wildcard

Best Practice Rules

select-star

insert-without-columns

ban-char-type

timestamp-without-timezone

order-by-ordinal

group-by-ordinal

Disabling Specific Rules

Next Steps