Column definition and database having different string length fields

[Dave]

Hello,

I have an application that is throwing exceptions on insert because one of the columns was defined too short.

sqlalchemy.exc.DataError: (psycopg2.DataError) value too long for type character varying(50)

The model is defined as:

class Device(Base):
    processor = Column(String(50))
    ...

Temporarily, I was thinking on increasing the length by running the statement: 

ALTER TABLE devices ALTER COLUMN processor TYPE character varying(128);

Are there any foreseeable issues with the column definition and the actual database having different length fields?

The application consists of:

- Pyramid 1.5.
- Postgres 9.3.4.
- SQLAlchemy 1.0.6
- Python 3.4.3

Any thoughts or help would be much appreciated.

Kind Regards,
David Anderson

[mike bayer]

Hi there -

the length that's in your String() type is not used by SQLAlchemy except
when it first renders DDL for CREATE TABLE, so to that extent it is safe
for the database column itself to be of a different size. This
wouldn't account for something like a client-side validation rule that
makes use of this value, but it sounds like you don't have anything like
that in place.

you of course would want to update your source code with the correct
number so that it documents the actual size but there's no urgency to
having this deployed on production.

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

Just to expand on Mike's answer... I've run into this often on a handful of columns.

The solution I've settled on is to set a variable:

   COLUMN_SIZE_SERVERSIDE = 1

and then use that as the length on the problematic column sizes while I work out the issues on what the length should be, and how to best limit it (which can take a week or more).

when it comes time to implement a fix, or if anyone else is touching that code, it is incredibly obvious which columns don't match up in size.