postgres value too long for type character varying(100)

Question

I created a flask website with Postgres database. I created a class like this.

class Book(db.Model):
    __tablename__ = "books"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), unique=False)
    summary = db.Column(db.String(100), unique=False)
    image = db.Column(db.String(100), unique=False)

However, when I tried to "add" a book with a summary field. There is a error like this:

sqlalchemy.exc.DataError: (psycopg2.errors.StringDataRightTruncation) value too long for type character varying(100)

[SQL: INSERT INTO books (id, title, summary, image) VALUES (%(id)s, %(title)s, %(summary)s, %(image)s)]
[parameters: {'id': 5, 'title': 'Crying in H Mart: A Memoir', 'summary': "In this exquisite story of family, food, grief, and endurance, Michelle Zauner proves herself far more than a dazzling singer, songwriter, and guitar ... (218 characters truncated) ... treasured months spent in her grandmother's tiny apartment in Seoul, where she and her mother would bond, late at night, over heaping plates of food.", 'image': 'https://m.media-amazon.com/images/I/51gNCTAbLJS.jpg'}]
(Background on this error at: https://sqlalche.me/e/14/9h9h)
192.168.0.15 - - [15/May/2022 10:09:33] "POST /admin HTTP/1.1" 500 -

I tried summary = db.Column(db.String(10000), unique=False)

and summary = db.Column(db.Text, unique=False) and summary = db.Column(db.String, unique=False)

But none of them work....

Answer 1

Presumably your table already exists from a previous run of the program. SQLAlchemy create_all will not recreate nor change an existing table.

The usual way to issue CREATE is to use create_all() on the MetaData object. This method will issue queries that first check for the existence of each individual table, and if not found will issue the CREATE statements:

So your changes to the schema in the code have no effect if the table already exists.

While experimenting you can simply drop_all to clear the schema and then create_all. All tables will be recreated.

If you want to preserve the data you'd write a migration to alter the existing table.

---

In general, don't put artificial limits on columns. It doesn't save any space. In Postgres, use text.