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

Question

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)

I would have try to apply this `concat()` in an `_init_`method. Anyway, the question is interesting, but I would strongly recommend you do NOT use this kind of method for IDs. An auto calculated field will be much more efficient, and you can add a calculated field if there is a need of it anyway. — Christophe, Sep 06 '21 at 14:00
@Christophe do you mean that it is better idea to have: `source` and `source_id` as composite PK and `id` as autoincrement, not null, unique, Integer? — Karol Zlot, Sep 06 '21 at 14:09
I would not even create a composite PK. You could create an index, but for the PK I really prefer to have an autoincrement `id`. In my environment, we use aggregations for ids, to have an 'explicit link with the source', and it's a mess — Christophe, Sep 06 '21 at 16:06

Stefanov.sm · Accepted Answer · 2021-09-06T14:54:58.373

2

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)

edited Sep 06 '21 at 14:54

answered Sep 06 '21 at 14:31

Stefanov.sm

11,215
2
21
21

1

Yes, `id` is redundant, but it allows me to make ForeignKeys and API calls easier. Although I will try to switch to composite PK / composite FK and get rid of `id` as you say, it seems to be the right way of doing things. – Karol Zlot Sep 06 '21 at 15:26
After trying composite PK and seeing its flaws I decided to go with single autoincrement `id` PK which was suggested by @Christophe – Karol Zlot Oct 06 '21 at 23:34

score 0 · Answer 2 · answered Sep 06 '21 at 14:00

0

What about

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

directly in your SQL statement?

answered Sep 06 '21 at 14:00

I don't know how to do this in SQLAlchemy – Karol Zlot Sep 06 '21 at 14:09

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

2 Answers2