check if sequence exists before creation for postgresql

[zzzeek]

Originally reported by: Anonymous

---

There is a problem when using sequences with PostgreSql, if the sequence already exists and we try to create a table (even with checkfirst=true) an error raises telling that the sequence already exists.

Index: lib/sqlalchemy/databases/postgres.py
===================================================================
--- lib/sqlalchemy/databases/postgres.py    (revision 1802)
+++ lib/sqlalchemy/databases/postgres.py    (working copy)
@@ -348,9 +348,14 @@

     def visit_sequence(self, sequence):
         if not sequence.optional:
-            self.append("CREATE SEQUENCE %s" % sequence.name)
-            self.execute()
-            
+            r = self.connection.execute(**SELECT relname FROM pg_class WHERE relkind = 'S'
+    AND relnamespace IN ( SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%%'
+    AND nspname != 'information_schema'
+    AND relname = %(seqname)s);**, {'seqname': sequence.name})
+            if r.rowcount == 0:
+                self.append("CREATE SEQUENCE %s" % sequence.name)
+                self.execute()
+
 class PGSchemaDropper(ansisql.ANSISchemaDropper):
     def visit_sequence(self, sequence):
         if not sequence.optional:

---

• Bitbucket: https://bitbucket.org/zzzeek/sqlalchemy/issue/277

[zzzeek]

Original comment by Michael Bayer (Bitbucket: zzzeek, GitHub: zzzeek):

---

verified for PG, fix for Oracle is in place but not tested in changeset:1803. im a little uncomfortable that sequences have a hardcoded existence check but tables do not (would almost prefer both to just have a hardcoded existence check....)

[zzzeek]

Original comment by Anonymous:

---

patch to conditionally create sequences for postgresql