How to create table fields using char(N) instead of varchar using sqlmodel in python/postgresql

Question

Defining the class: from sqlmodel import Field, SQLModel, create_engine, Session, select, Column,String

class person(SQLModel, table=True):

id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(max_length=100)
nickname: str = Field(max_length=30)

sqlmodel creates the table person:

CREATE TABLE person( id SERIAL NOT NULL, name varchar NOT NULL, nickname varchar NOT NULL, PRIMARY KEY(id) );

If using sqlalchemy :

Base = declarative_base() class person(Base): tablename = 'person'

id = Column(Uuid, primary_key=True, default=uuid4)
nickname = Column(String(32))
name = Column(String(100))

fields are created varchar(n)

How i fixed it for char(n) ?

Answer 1

SQLALchemy's CHAR datatype will create a char(n) column in PostgreSQL. This script

import sqlalchemy as sa

engine = sa.create_engine('postgresql+psycopg2:///test', echo=True)

metadata = sa.MetaData()
tbl = sa.Table('t78331672', metadata, sa.Column('col', sa.CHAR(4)))

metadata.drop_all(engine)
metadata.create_all(engine)

with engine.connect() as conn:
    conn.execute(tbl.insert().values(col='a'))
    rows = conn.execute(sa.select(tbl))
    for row in rows.mappings():
        print(row)

will emit this DDL:

CREATE TABLE t78331672 (
        col CHAR(4)
)

We can confirm the result with psql:

psql test -c '\d t78331672'
                Table "public.t78331672"
 Column │     Type     │ Collation │ Nullable │ Default 
════════╪══════════════╪═══════════╪══════════╪═════════
 col    │ character(4) │           │          │ 

The final SELECT query produces this output (note the padding):

{'col': 'a   '}

However, as noted in the comments [1], [2], CHAR(n) should not be used in modern PostgreSQL.

Answer 2

char(n) isn’t supported. You could technically use raw SQL but ideally you would just not use char(n). Just update the column type instead.