assure row and column types

[burner]

One issue I faced a few times is that the result of the query changes when tables used to compute the results changed.

It would be awesome to catch those issues before the application layer fails to parse the data.

Just a brainstorm

SELECT 
  A.id        -- type: bigint
  B.name -- type: text
  B.date   -- type: date
FROM A
INNER JOIN B ON A.id = B.id

should B.date be changed to DateTime regresql should tell me that the expected type of B.date is not date but DateTime.

edit:
This is even more useful when returning json

SELECT json_build_object
 (  'id' ,A.id        -- type: bigint
 , 'name', B.name -- type: text
 , 'date', B.date   -- type: date
) AS rslt
FROM A
INNER JOIN B ON A.id = B.id

[radim]

@burner it actually already works this way. regresql compares the actual reference data values. On this example I converted column from timestamptz to date

✗ album-by-artist_list-albums-by-artist.2.json (0.00s)

  COMPARISON SUMMARY:
  ├─ Expected: 2 rows
  ├─ Actual:   2 rows
  ├─ Matching: 0 rows
  └─ Modified: 2 rows

  MODIFIED ROWS (showing 2 of 2):
  Row #1:
    Expected: {album: "For Those About To Rock We Salute You", created_at: "2025-11-17T16:11:58.760899+01:00", duration: "00:09:09.381"}
    Actual:   {album: "For Those About To Rock We Salute You", created_at: 2025-11-17 00:00:00 +0000 UTC, duration: "00:09:09.381"}
  Row #2:
    Expected: {album: "Let There Be Rock", created_at: "2025-11-17T16:11:58.760899+01:00", duration: null}
    Actual:   {album: "Let There Be Rock", created_at: 2025-11-17 00:00:00 +0000 UTC, duration: null}

and now back from date to timestamptz

  COMPARISON SUMMARY:
  ├─ Expected: 2 rows
  ├─ Actual:   2 rows
  ├─ Matching: 0 rows
  └─ Modified: 2 rows

  MODIFIED ROWS (showing 2 of 2):
  Row #1:
    Expected: {album: "For Those About To Rock We Salute You", created_at: "2025-11-17T00:00:00Z", duration: "00:09:09.381"}
    Actual:   {album: "For Those About To Rock We Salute You", created_at: 2025-11-17 00:00:00 +0100 CET, duration: "00:09:09.381"}
  Row #2:
    Expected: {album: "Let There Be Rock", created_at: "2025-11-17T00:00:00Z", duration: null}
    Actual:   {album: "Let There Be Rock", created_at: 2025-11-17 00:00:00 +0100 CET, duration: null}

Does this work as you would expect?

[burner]

Cool, but I was more hoping for a message like

column: 'THE_COLUMN_NAME' has type 'ACTUAL TYPE', but type 'EXPECTED_TYPE' was expected.

I'm not sure if regresql is the right place for such a check!?

[radim]

@burner right now regreSQL is not testing schema. pgTap might be actually better for this

BEGIN;
SELECT plan(3);

-- syntax: col_type_is( schema, table, column, type, description )

SELECT col_type_is(
    'public', 'users', 'email', 'character varying', 
    'The email column should be a varchar'
);

SELECT col_type_is(
    'public', 'users', 'created_at', 'timestamp with time zone',
    'The created_at column should be a timestamptz'
);

SELECT col_type_is(
    'public', 'users', 'age', 'integer',
    'The age column should be an integer'
);

SELECT * FROM finish();
ROLLBACK;

I will actually follow up on blog in next 14 days with introduction article to pgTap testing.

[burner]

awesome