On Aug 20, 2012, at 7:28 PM, Mitchell Hashimoto wrote:
> Hello,
>
> I have a need to perform an "upsert" query with PostgreSQL. the following SQL
> query achieves this goal:
>
> WITH upsert AS (
> UPDATE metric k SET k.count = k.count + 5
> WHERE event = "foo" AND interval = "D" and date = "whatever"
> RETURNING k.*
> )
> INSERT INTO metric (event, interval, date, count)
> SELECT ("foo", "D", "whatever", 5)
> WHERE NOT EXISTS (
> SELECT 1 FROM upsert
> );
>
> How do I do this sort of thing with SQLAlchemy? It only ever needs to work
> with PostgreSQL so any PG specific things are fine.
What's fascinating is that Postgresql, which in most cases supports way more
than all the other DBs, doesn't yet support MERGE, which is a cleaner bit of
SQL and is also part of the ANSI standard.
SQLAlchemy also hasn't gotten around to supporting MERGE yet, but it *does* do
common table expressions, i.e. the WITH statement.
However, the whole syntax you refer to above is apparently brand new as of 9.1.
Our support for WITH is only involving SELECT,which is all CTEs support in all
of PG up through 9.0 as well as all the other DBs that support CTEs. We don't
yet have an INSERT from SELECT construct built in, which is a major syntactical
change as well as an open question how the API should be organized to support
this (probably accepted by insert().values()). Then particularly, combining
the WITH with the INSERT is a major syntactical change as well. Overall
there are three major syntactical features here and this is many hours of new
development and testing effort.
So at the moment you'd need to use a straight string, unless you want to try
tackling it with a @compiles recipe
(http://docs.sqlalchemy.org/en/rel_0_7/core/compiler.html). The statement
above is extremely PG specific though so there's not much downside to getting
by with a string for now. ticket #2551 is added for this job, it's good to
have it on my radar.
--
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.
