CODEWITHSUNDEEP
pythonpostgresqldatabase-designsqlalchemycalculated-columns
Karol Zlot
Karol Zlot

Reputation: 4065

Computed text column as concat (sum of strings) of two other columns

I want to have a table in which I have unique pairs of source and source_id. But I also need single column unique id to make API more simple.

I am trying to have id column as concat of two other columns:

from sqlalchemy import Computed, Column, Integer, Text
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Product(Base):
    __tablename__ = "product"

    id = Column(Text, Computed("source || source_id"), primary_key=True)
    source = Column(Text, nullable=False)
    source_id = Column(Integer, nullable=False)
    name = Column(Text, nullable=True)

I first tried to use contcat() but it gave me error: generation expression is not immutable.

I learned here that it can't work and I should switch to ||. But || gives me the same error. How to solve it?

Below is error log which contains generated SQL.

...
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidObjectDefinition) generation expression is not immutable

[SQL:
CREATE TABLE product (
        id TEXT GENERATED ALWAYS AS (source || source_id) STORED NOT NULL,
        source TEXT NOT NULL,
        source_id INTEGER NOT NULL,
        name TEXT,
        CONSTRAINT pk_product PRIMARY KEY (id)
)

]
(Background on this error at: https://sqlalche.me/e/14/f405)

Upvotes: 0

Views: 827

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13049

Explicitly cast source_id as text and it works.

CREATE TABLE product
(
  id TEXT GENERATED ALWAYS AS (source || source_id::text) STORED NOT NULL,
  source TEXT NOT NULL,
  source_id INTEGER NOT NULL,
  name TEXT,
  CONSTRAINT pk_product PRIMARY KEY (id)
);

in Python:

    id = Column(Text, Computed("source || source_id::text"), primary_key=True)

BTW isn't id redundant? What about CONSTRAINT pk_product PRIMARY KEY (source, source_id), i.e.

    source = Column(Text, nullable=False, primary_key=True)
    source_id = Column(Integer, nullable=False, primary_key=True)

Upvotes: 2

user12965285
user12965285

Reputation:

What about

CREATE TABLE product(
    CONCAT (source, source_id) AS "TEXT GENERATED ALWAYS",
    ...
)

directly in your SQL statement?

Upvotes: 0

Related Questions