select version()
"PostgreSQL 9.0.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit"

Hi,

should a bigserial sequence know that I have made a separate 'insert'
on a table ?

my experience says that it does not...and this seems wrong to me.

On Sun, 27 Feb 2011 07:39:55 -0800, Dog is love wrote:

Don't use "serial" data types, use sequence in combination with a
trigger. That way, you will know what happens, when.




--
http://mgogala.byethost5.com

On 2/27/2011 2:14 PM, Mladen Gogala wrote:
Ha! That seems like a GREAT idea! You'd only have to
deal with INSERT triggers, right?

Aside from having to write the trigger function (trivial?),
are there any other downsides to this approach?

--don

D Yuniskis <not.going.to.be (AT) seen (DOT) com> wrote:
I don't see any advantage at all to using a trigger rather than using a
DEFAULT in the column definition.

(And using a DEFAULT in the column definition gives essentially the
same behaviour as SERIAL would in the first place; if you think of it
that way, SERIAL shouldn't give any surprises. The only real difference
is that if you use SERIAL the sequence will be dropped automatically
when you drop the table.)

-M-

On Mon, 28 Feb 2011 07:02:14 -0700, D Yuniskis wrote:

Unless your application will attempt to modify the PK, which would
definitely be the worst possible application design.



--
http://mgogala.byethost5.com

On Mon, 28 Feb 2011 18:05:10 +0000, Matthew Woodcraft wrote:

So, what happens in case you try with an explicit insert?



--
http://mgogala.byethost5.com

In article <pan.2011.02.28.21.19.22 (AT) email (DOT) here.invalid>,
Mladen Gogala <no (AT) email (DOT) here.invalid> wrote:
It overrides the default (like for any other default), and it doesn't
affect the sequence.

-M-

"Excuse me! Step aside! Make way! Newbie coming through...."

<frown>

On 2/28/2011 3:13 PM, Matthew Woodcraft wrote:
Sorry, you guys are getting ahead of me, here... :-(

[Disclaimer: I don't do this for a living]

I typically use serials/bigserials as a "cheap" (in terms
of storage space and effort required to *create*) primary
key in a table. Often called "ID" (yes, uppercase :< ).
This just gives me a "durable" handle against which to
link other tables when the table itself has nothing truly
"unique" by which to identify its rows.

So, for example, I have a table called paper_sizes which
enumerates the dimensions of "sheets of paper" in use
around the world. At it's minimum, it might contain
columns: ID, width, height. A typical row might be:
238 17 in 11 in

Another table -- paper_names -- lets me bind common *names*
for "paper sizes" to those actual sizes. It's columns
might include: ID, name, paper_size_ID (the latter obviously
tying to the ID column in the paper_size table). Some typical
rows might be:
997 ledger 238
1042 ANSI B 238
2734 tabloid 238

In my case, I want to be able to create *new* entries in
each table without bothering with coming up with a unique
hash to identify *this* new row/record. When creating a
new record, I *really* don't care what the ID field
contains -- as long as it will be *unique* within that
table *and* "durable" (i.e., OIDs seem like they would
be a problem waiting to happen if used as such).

If I *update* a record, I probably want to preserve the
existing "ID" field. E.g., if I had mistyped "16 in"
instead of "17 in" for the width of the paper_size entry.

Note that I might want to *add* a new record even though
it *appears* identical to an existing record -- at the
present time. So, I could potentially end up with a
second paper_size record that differs from the one
illustrated above *only* by the issuance of a new/unique
"ID" field. For example:
238 17 in 11 in
947 17 in 11 in

This might, for example, be because I am adding another
column to the paper_size table (explicitly or indirectly
by way of a second table) and the contents of that "other
column" might (or might NOT!) differentiate between these
two. Or, because I haven't yet got around to "correcting"
the actual height/width columns of one (or both) of these
apparently identical paper_size records.

In light of this type of usage, could I impose upon you
to please explain the consequences of each of these
"serial/sequence" approaches?

Thanks!
--don

On Mon, 28 Feb 2011 22:13:29 +0000, Matthew Woodcraft wrote:

So, when the sequence reaches the given number, you will have a primary
key violation? That sounds like a plan.



--
http://mgogala.byethost5.com

Mladen Gogala <no (AT) email (DOT) here.invalid> wrote:
If what you want is to forbid people setting explicit values, you can
do that with per-column permissions.

(But before Postgresql 8.4 using a trigger was the best way, yes.)

-M-