5

I have quite a few bytea fields in a PostgreSQL database.

(i) Is there a way to impose a size cap on such fields (so that an error is thrown if this size is attempted to be exceeded) by using a database feature?

(ii) Alternatively, is there a good way to do this in SQLAlchemy?

Soferio
  • 483
  • 6
  • 14

1 Answers1

5

You can use a domain (on PostgreSQL size).

CREATE DOMAIN my_bytea_8 AS bytea CHECK(length(value) <= 8);

postgres=# SELECT 'NAZDARBAZAR'::my_bytea_8;
ERROR:  23514: value for domain my_bytea_8 violates check constraint "my_bytea_8_check"
SCHEMA NAME:  public
DATATYPE NAME:  my_bytea_8
CONSTRAINT NAME:  my_bytea_8_check
LOCATION:  ExecEvalCoerceToDomain, execQual.c:4042
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • 3
    Or just throw a CHECK on the relevant column. – mu is too short Feb 21 '16 at 06:10
  • For those interested, I found a range of links which explain this: bytea length function (http://www.postgresql.org/docs/8.2/static/functions-binarystring.html); creating a domain (http://www.postgresql.org/docs/9.1/static/sql-createdomain.html); and in SQL Alchemy constraints (http://docs.sqlalchemy.org/en/latest/core/constraints.html); but there are issues in creating a domain in SQLAlchemy (http://stackoverflow.com/questions/18662846/how-to-represent-a-custom-postgresql-domain-in-sqlalchemy); there may be another way to do this in SQLAlchemy. – Soferio Feb 22 '16 at 22:09