Re: Mysql -> Postgresql pitfalls

On Mon, 4 Aug 2003, Chad N. Tindel wrote:

> > > > FWIW, in 7.5 we're likely to implement SQL3 IDENTITY columns ... basically
> > > > just a SERIAL column where you don't have the option of inserting your own
> > > > value, you have to take what it gives you.
> > >
> > > Interesting... how do you import data from a dump with such columsn?
> >
> > Easy, after you import the last row, you
> >
> > select setval('seqname',lastvalue);
> >
> > on the sequence. Like I said above, it's mostly just a different way of
> > doing things in Postgresql, and often those different ways are less
> > obvious, and quite often, being less obvious is actually safer even if
> > it is a littler harder to learn up front.
>
> But you just said that I can't actually include the id column in an insert
> query. So how would I import data from a dump and ensure that the id columns
> are what I expect them to be?

I did not say that, I was talking about serial types.

What the guy ahead of ME said was that they were looking at building the
SQL3 IDENTITY columns, which are a serial you can't set the value of in an
insert. With those, the import would happen "behind it's back" so to
speak in the \copy command. I.e. you wouldn't use inserts to load your
data, you'd use a bulk copy, which bypassess all the serial / IDENTITY
stuff. Basically, with the IDENTITY type, if you try to insert a value,
it just ignores it and inserts the next sequence. So, users would have no
way of setting the id being inserted. There would still be, I'm sure, a
method for setting the sequence number, it just might be limited to
superusers / IDENTITY owners.

So, I think we were getting Postgresql's CURRENT serial implementation
confused with a possible future implementation of IDENTITY type.