Thread: auto-increment field : in a simple way

CREATE TABLE tablename (   colname SERIAL
);

For more granular control over the size of field, I need to do the following---

CREATE SEQUENCE user_id_seq;

CREATE TABLE user (

    user_id smallint NOT NULL DEFAULT nextval('user_id_seq')

);
ALTER SEQUENCE user_id_seq OWNED BY user.user_id;



I am not pinpointing MySQL v/s Postgres; but since I am accustomed to using simply "AUTOINCREMENT" in MySQL,
I find this a bit cumbersome procedure.

1] Is there any simpler way of doing this?

2] Whether the development team has this point in their TO DO list for future release of postgres?

Thanks,

Vineet

On Thu, Oct 11, 2012 at 1:04 AM, Vineet Deodhar
<vineet.deodhar@gmail.com> wrote:
> I wish to know regarding auto-increment field.
> I learn that the required table definition would be something like --
>
> CREATE TABLE tablename (
>     colname SERIAL
> );
>
> For more granular control over the size of field, I need to do the
> following---
>
> CREATE SEQUENCE user_id_seq;
>
> CREATE TABLE user (
>
>     user_id smallint NOT NULL DEFAULT nextval('user_id_seq')
>
> );
> ALTER SEQUENCE user_id_seq OWNED BY user.user_id;
>
>
>
> I am not pinpointing MySQL v/s Postgres; but since I am accustomed to using
> simply "AUTOINCREMENT" in MySQL,
> I find this a bit cumbersome procedure.
>
> 1] Is there any simpler way of doing this?
>
> 2] Whether the development team has this point in their TO DO list for
> future release of postgres?

Can't you just add this to your create table:


CREATE TABLE tablename (
   colname SERIAL
, check (colname>0 and colname < 32768));
);

Also I can't imagine this problem being common enough to justify much
work to provide a "smallserial" type etc.  What about when we want a
sequence with a different increment by, start, min/max, cycle, or
cache value? Any idea for making things a bit different here should
probably address all of those possibilites.

On 10/11/2012 03:04 PM, Vineet Deodhar wrote:

>      user_id smallint NOT  NULL  DEFAULT  nextval('user_id_seq')

I'm kind of puzzled about why you'd want to use a serial on a field that
can contain at most 65,536 entries anyway. If you're only going to have
a max of 65,536 entries then the space saving over INTEGER is at most
65536*2 = 131072 bytes or 128kb for the table.

Planning on creating many thousands of these tables? If you're not, then
it isn't worth caring. If you are, then you're automating it so you
won't mind doing it the longhand way.

If you're creating few enough tables that you care about the syntax of
defining an unusually small data type for a generated primary key,
you're creating few enough that the space doesn't actually matter.

--
Craig Ringer

On 10/11/2012 05:11 PM, Vineet Deodhar wrote:
> On Thu, Oct 11, 2012 at 12:56 PM, Scott Marlowe <scott.marlowe@gmail.com
> <mailto:scott.marlowe@gmail.com>> wrote:
>
>
>     Can't you just add this to your create table:
>
>
>     CREATE TABLE tablename (
>         colname SERIAL
>     , check (colname>0 and colname < 32768));
>     );
>
>
>
> With this constraint, whether the storage space requirement would reduce?
> OR
> Is it just for validation of data?

It's purely validation and has no effect on storage size.

--
Craig Ringer