Re: [SQLObject] two postgres questions

[Edmund L. <el...@in...>]

Bud P.Bruegger wrote:

> <quote src=http://www.phpbuilder.com/columns/tim20000705.php3:> 
> At this point, I ran into another small issue with Postgres - its
> "serial" data type (the equivalent of MySQL's auto_increment) creates
> a "sequence" which does not get dropped when its parent table is
> dropped. So if you try to re-create the table, you'll get a name
> conflict for this sequence. A lot of new users would be confused by
> this, so Postgres loses a couple points for that.

This depends on which version of PostgreSQL you are using. The latest 
versions drop this automatically.


> Also, MySQL is
> "smart" enough to increment its auto_increment value when you import
> data, whereas Postgres' sequence does not get reset when you import
> data, causing all new inserts to fail.  

This depends on how you import the data. Are you using the copy command? 
If so, then rather than importing every column of a table that has an 
autosequence, you should just import the columns you really want. Leave 
the autoincrementing column out of the import statement, and it will be 
autoincremented as you expect.


> I was thinking of having IDs that are unique across tables--something
> useful when thinking of implementing inheritance etc.  So I tried to
> create a global sequence and have the id columns of several tables use
> it for their default values (DEFAULT nextval('global_oid_seq')).  But
> that seems to fail.  Can there be only a single column (of a single
> table) associated with a sequence???

You should be using triggers for this. I do this now and then, no problems.

...Edmund.