Question 377321 in PostgreSQL Question posted in
The official documentation can be found here.

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

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) ?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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:

    {'col': 'a   '}

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search