Skip site navigation (1) Skip section navigation (2)

Re: Mysql -> Postgresql pitfalls

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: "Chad N(dot) Tindel" <chad(at)tindel(dot)net>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, <pgsql-docs(at)postgresql(dot)org>
Subject: Re: Mysql -> Postgresql pitfalls
Date: 2003-08-04 19:26:50
Message-ID: Pine.LNX.4.33.0308041323060.10821-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-docs
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.


In response to

Responses

pgsql-docs by date

Next:From: Chad N. TindelDate: 2003-08-04 19:29:48
Subject: Re: Mysql -> Postgresql pitfalls
Previous:From: scott.marloweDate: 2003-08-04 19:06:28
Subject: Re: Mysql -> Postgresql pitfalls

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group