For reference:
http://www.mail-archive.com/[EMAIL PROTECTED]/msg02239.html
I found the above discussion when googling a ProgrammingError i've
been getting with a polymorphic_union:
<quote>
sqlalchemy.exceptions.SQLError: (ProgrammingError) UNION types numeric
and character varying cannot be matched
'SELECT ijoin.type AS ijoin_type, ijoin.id AS ijoin_id, ijoin.size AS
ijoin_size \nFROM (SELECT items.type AS type, skiboots.id AS id,
skiboots.size AS size \nFROM items JOIN skiboots ON items.id =
skiboots.id UNION ALL SELECT anon_c93f.type AS type, anon_c93f.id AS
id, CAST(NULL AS VARCHAR(20)) AS size \nFROM (SELECT items.id AS id,
items.type AS type \nFROM items \nWHERE items.type = %(items_type)s)
AS anon_c93f UNION ALL SELECT items.type AS type, skis.id AS id,
skis.size AS size \nFROM items JOIN skis ON items.id = skis.id) AS
ijoin ORDER BY ijoin.id' {'items_type': 'item'}
</quote>
Here's a minimal test script:
from sqlalchemy import *
db = create_engine('postgres://postgres:[EMAIL PROTECTED]:5432/
testrentals',
encoding='utf-8')
metadata = BoundMetaData(db)
items = Table('items', metadata,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('type', String(20)))
skis = Table('skis', metadata,
Column('id', Integer, primary_key=True),
Column('size', String(20), nullable=False),
ForeignKeyConstraint(['id'], ['items.id']))
skiboots = Table('skiboots', metadata,
Column('id', Integer, primary_key=True),
Column('size', types.Numeric(3,1)),
ForeignKeyConstraint(['id'], ['items.id']))
item_join = polymorphic_union(
{
'ski':items.join(skis),
'skiboot':items.join(skiboots),
'item':items.select(items.c.type=='item'),
}, None, 'ijoin')
class Item(object):pass
class Ski(Item): pass
class SkiBoot(Item): pass
item_mapper = mapper(Item, items,
select_table=item_join,
polymorphic_on=item_join.c.type,
polymorphic_identity='item')
ski_mapper = mapper(Ski, skis,
inherits=item_mapper,
polymorphic_identity='ski')
skiboot_mapper = mapper(SkiBoot, skiboots,
inherits=item_mapper,
polymorphic_identity='skiboot',
inherit_condition = items.c.id==skiboots.c.id)
if __name__ == "__main__":
session = create_session()
print session.query(Item).select()
##############################################
So, skis are working fine but skiboots aren't. If I either comment
out the 'size' column in the skiboots table:
# Column('size', types.Numeric(3,1)),
- or - comment out the 'skiboots' line in the item_join:
# 'skiboot':items.join(skiboots),
...then it runs ok.
Maybe I'm making incorrect use of the Numeric type? Or is this a
bug? I want to use the Numeric type because i'd like to represent
boot sizes as, e.g., 9.5, 10.0, 10.5. For skis the size is a String
because sometimes it's a number and sometimes a nominal size like "s",
"m", "xl", etc. No problem with the string; i'm just wondering if I
can use the Numeric type as sqla currently stands. Do I have to
define a custom type and stash Numerics in string representations?
Thanks!
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
