SQLAlchemy fails when adding literal_column

Question

I'm using Flask-SQLAlchemy and Postgres, and I have 2 classes FacebookPost and TwitterPost which I want to query 2 columns from, add a 3rd column post_type to denote which class they are from, union the results, and then sort them.

When I construct the two subqueries (without the post_type column) and union them like so

t1 = FacebookPost.__table__
fbq = db.session.query(t1.c.provider_post_id, t1.c.created_at).\
    join(FacebookPage).join((Artist,FacebookPage.artists)).\
    filter(Artist.id == 1).order_by(FacebookPost.created_at.desc()).\
    limit(5)
t2 = TwitterPost.__table__
twq = db.session.query(t2.c.provider_post_id, t2.c.created_at)\
    .join(TwitterUser).join((Artist,TwitterUser.artists)).\
    filter(Artist.id == 1).order_by(TwitterPost.created_at.desc()).\
    limit(5)
u = union(fbq.subquery().select(), twq.subquery().select())
db.session.query('provider_post_id', 'created_at').select_from(u).\
    order_by("created_at desc").all()

This works compeltely fine and as you'd expect. As soon as I update my two queries to include the post_type like so

fbq = db.session.query(t1.c.provider_post_id, t1.c.created_at,\
    literal("facebook_post", type_=db.String).label("post_type")).\
    join(FacebookPage).join((Artist,FacebookPage.artists)).\
    filter(Artist.id == 1).order_by(FacebookPost.created_at.desc()).\
    limit(5)
twq = db.session.query(t2.c.provider_post_id, t2.c.created_at,\
    literal("twitter_post", type_=db.String).label("post_type")).\
    join(TwitterUser).join((Artist,TwitterUser.artists)).\
    filter(Artist.id == 1).order_by(TwitterPost.created_at.desc()).\
    limit(5)

the queries indiviually work, i.e.

fbq.all()

gives a list of results which each have an attribute post_type that is "facebook_post". The same works for twq.all(). But as soon as I call

u = union(fbq.subquery().select(), twq.subquery().select())
db.session.query('provider_post_id', 'created_at', 'post_type').select_from(u).\
    order_by("created_at desc").all()

I get the following error:

InternalError: (InternalError) failed to find conversion function from unknown to text

I've tried using literal_column instead of literal to no avail. It seems like the issue is with adding this, since it works before adding the literal, but since they work individually, I'm really at a loss as to why this error is being raised. Any help would be greatly appreciated.

This might be a postgres specific problem [See This](http://stackoverflow.com/questions/18073901/failed-to-find-conversion-function-from-unknown-to-text) — RedBaron, Feb 12 '14 at 05:13
@RedBaron thanks! that did it. will post full solution below. — erik, Feb 12 '14 at 06:30

score 4 · Answer 1 · edited May 23 '17 at 10:25

Thanks to @RedBaron for his suggestion that this might be a postgres specific problem, I was able to get this to work. I had to cast the literal into a db.String:

fbq = db.session.query(t1.c.provider_post_id, t1.c.created_at,\
    cast(literal("facebook_post"), db.String).label("post_type")).\
    join(FacebookPage).join((Artist,FacebookPage.artists)).\
     filter(Artist.id == 1).order_by(FacebookPost.created_at.desc()).\
    limit(5)
twq = db.session.query(t2.c.provider_post_id, t2.c.created_at,\
    cast(literal("twitter_post"), db.String).label("post_type")).\
    join(TwitterUser).join((Artist,TwitterUser.artists)).\
    filter(Artist.id == 1).order_by(TwitterPost.created_at.desc()).\
    limit(5)
u = union(fbq.subquery().select(), twq.subquery().select())
db.session.query('provider_post_id', 'created_at', 'post_type').select_from(u).\
order_by("created_at desc").all()

works!

SQLAlchemy fails when adding literal_column

1 Answers1