Given table [foo smallint] [bar varchar(10)]
var metadata = ... // use pg-metadata module
var validate = require('pg-validate')
// Validate for PostgreSQL
var errors = validate.pg.object({
foo: 1280000,
bar: 'aaaaaa'
}, metadata)
// Validate for Redshift
var errors = validate.redshift.object({
foo: 1280000,
bar: 'aaaaaa'
}, metadata)
// Or the verbose way
var errors = validate.object({
foo: 1280000,
bar: 'aaaaaa'
}, metadata, { platform: validate.REDSHIFT })
smallint
orint2
integer
orint4
bigint
orint8
serial
bigserial
numeric
ordecimal
numeric(p, s)
, wherep
is the maximum precision in decimal digits ands
is the scale (number of fractional digits).numeric(p)
selects a scale of0
.float4
orreal
float8
ordouble_precision
orfloat
- PostgreSQL only:
float(p)
, wherep
is the minimum acceptable precision in binary digits.float(1)
tofloat(23)
selectsreal
,float(24)
tofloat(53)
selectsdouble_precision
. Note thatp
is not used to validate values, unlikenumeric(p)
.
boolean
varchar
orchar
orbpchar
text
: strings of any length on PostgreSQL, an alias ofvarchar
on Redshift
timestamp[ (p) ]
ortimestamptz [ (p) ]
date
time [ (p) ]
ortimetz [ (p) ]
interval [ fields ] [ (p) ]
For the purposes of validation, there's no difference between time
and timetz
, or timestamp
and timestamptz
. This is because PostgreSQL silently ignores time zones for types without a time zone. And though Redshift stores dates in UTC, it does accept input values with a time zone - with the exception of full time zone names like "America/New_York".
Not currently supported:
- Julian Day ("J2451187"), BC/AC dates, or ambiguous dates in a DateStyle mode
- Time values with a precision greater than 3
- Dates outside the range of
moment.js
andDate
- Custom configured timezone names or abbreviations. In the future, the list of supported timezone values could be dynamically generated, via
SELECT row_to_json(pg_timezone_abbrevs) FROM pg_timezone_abbrevs
and the same forpg_timezone_names
. For those timezones that are DST sensitive (is_dst: true
), we should validate that values have a date. - POSIX-style time zone specifications
Requires a PostgreSQL or Redshift database. Create a dummy user and database (nothing is written by the tests) and specify the credentials in the PG_VALIDATE
environment variable:
PG_VALIDATE=postgres://user:password@localhost:5432/database
npm test