Horton

Thread

  1. Autoincrement

    Wari Wahab <wari@technology.com> — 1998-07-17T06:59:47Z

    Hi, is there a way to do a field that can automatically increment itself,
like in paradox or dbase?

________________________________________
Regards
Wari Wahab

  2. Re: [INTERFACES] Autoincrement

    Antonio Garcia Mari <agarcia@at4.net> — 1998-07-17T09:47:04Z

    > Hi, is there a way to do a field that can automatically increment itself,
> like in paradox or dbase?
> 

this is a hack, but it works...

CREATE SEQUENCE key_s INCREMENT 1 START 1;
CREATE TABLE cliente (
	key 	int4 NOT NULL DEFAULT nextval('key_s') PRIMARY KEY,
	name 	varchar(100) UNIQUE NOT NULL,
	username	varchar(8) NOT NULL
	);
Antonio Garcia Mari
Mallorca (Spain)

  3. Re: [INTERFACES] Autoincrement

    Federico Passaro <fede@link.it> — 1998-07-17T11:07:08Z

    Antonio Garcia Mari wrote:

> > Hi, is there a way to do a field that can automatically increment itself,
> > like in paradox or dbase?
> >
>
> this is a hack, but it works...
>
> CREATE SEQUENCE key_s INCREMENT 1 START 1;
> CREATE TABLE cliente (
>         key     int4 NOT NULL DEFAULT nextval('key_s') PRIMARY KEY,
>         name    varchar(100) UNIQUE NOT NULL,
>         username        varchar(8) NOT NULL
>         );
> Antonio Garcia Mari
> Mallorca (Spain)

  You are right, but it's better to put the autoincrementing field as the last
one like in:

CREATE TABLE cliente (
        name    varchar(100) UNIQUE NOT NULL,
        username        varchar(8) NOT NULL ,
        key     int4 NOT NULL DEFAULT nextval('key_s') PRIMARY KEY,
        );

This way you can use the sintax

insert into cliente values ('JACK', 'postgres');

in place of

insert into cliente (name, username) values ('JACK', 'postgres');

A more robust solution is to use a trigger. Look at the files
<PostGreSQL source dir>/contrib/spi/autoinc.*

federico

  4. Re: [INTERFACES] Autoincrement

    James Oden <joden@lee.k12.nc.us> — 1998-07-17T12:27:45Z

    > Hi, is there a way to do a field that can automatically increment itself,
> like in paradox or dbase?
>
>

Read the man page on create_sequence.  This will tell you how its done in
Postgres...james

  5. Re: Autoincrement

    Herouth Maoz <herouth@oumail.openu.ac.il> — 1998-07-19T10:40:00Z

    (redirected to the SQL list because it really has nothing to do with
interfaces):

At 14:07 +0300 on 17/7/98, Federico Passaro wrote:


>   You are right, but it's better to put the autoincrementing field as
>   the last one like in:
>
> CREATE TABLE cliente (
>         name    varchar(100) UNIQUE NOT NULL,
>         username        varchar(8) NOT NULL ,
>         key     int4 NOT NULL DEFAULT nextval('key_s') PRIMARY KEY,
>         );
>
> This way you can use the sintax
>
> insert into cliente values ('JACK', 'postgres');
>
> in place of
>
> insert into cliente (name, username) values ('JACK', 'postgres');

No! The syntax may look attractive to you, because you have to write less,
but you will pay for it in performance! I definitely would not put that
field last. This is because in the current version of Postgres, any fields
following the first VARCHAR have a performance penalty. So, in order to
avoid this, you should place all fixed-sized fields at the beginning, and
then all the variable-length fields.

In any case it is always recommeded to explicitly specify the names of all
the fields in an insert operation, rather than rely on your memory of the
correct order.

> A more robust solution is to use a trigger. Look at the files
> <PostGreSQL source dir>/contrib/spi/autoinc.*

I am not sure a solution which depends on writing code in C and having
postgres superuser privileges can be considered "more robust". I'd
recommend the use of sequences in any case.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

  6. Creating table with unique key.

    Colin Dick <cdick@mail.ocis.net> — 1998-08-05T21:49:13Z

    > > CREATE TABLE cliente (
> >         name    varchar(100) UNIQUE NOT NULL,
> >         username        varchar(8) NOT NULL ,
> >         key     int4 NOT NULL DEFAULT nextval('key_s') PRIMARY KEY,
> >         );

Hi this is from a previous post regarding auto-incrementing.  I have that
part working, now I am trying to create a table with a field which will
not be null and will be unique to the rest of table.  Is this possible and
what is the correct syntax.  I have tried and receeved the following:

CREATE TABLE cust (FileNumber int4 unique not null, CompName varchar(50),
FirstName varchar(50), LastName varchar(50), BirthDate varchar(8), Suite
varchar(5), Address varchar(50), City varchar(20), Province varchar(20),
PostalCode varchar(7), Email varchar(50), HomePhone varchar(14), WorkPhone
varchar(14), FaxPhone varchar(14));
WARN:parser: parse error at or near "unique"

Can someone point me in the right direction.  Thanks.

--
Colin Dick
On Call Internet Services
cdick@mail.ocis.net

  7. Re: [SQL] Creating table with unique key.

    Eric J McKeown <ericm@palaver.net> — 1998-08-05T22:46:20Z

    On Wed, 5 Aug 1998, Colin Dick wrote:

> Date: Wed, 5 Aug 1998 14:49:13 -0700 (PDT)
> From: Colin Dick <cdick@mail.ocis.net>
> To: pgsql-sql@postgreSQL.org
> Subject: [SQL] Creating table with unique key.
> 
> > > CREATE TABLE cliente (
> > >         name    varchar(100) UNIQUE NOT NULL,
> > >         username        varchar(8) NOT NULL ,
> > >         key     int4 NOT NULL DEFAULT nextval('key_s') PRIMARY KEY,
> > >         );
> 
> Hi this is from a previous post regarding auto-incrementing.  I have that
> part working, now I am trying to create a table with a field which will
> not be null and will be unique to the rest of table.  Is this possible and
> what is the correct syntax.  I have tried and receeved the following:
> 
> CREATE TABLE cust (FileNumber int4 unique not null, CompName varchar(50),
> FirstName varchar(50), LastName varchar(50), BirthDate varchar(8), Suite
> varchar(5), Address varchar(50), City varchar(20), Province varchar(20),
> PostalCode varchar(7), Email varchar(50), HomePhone varchar(14), WorkPhone
> varchar(14), FaxPhone varchar(14));
> WARN:parser: parse error at or near "unique"

CREATE TABLE cust (FileNumber int4 PRIMARY KEY NOT NULL, ...

I think that should fix it.  You could also create a unique index on
individual fields in order to enforce their uniqueness.

CREATE [UNIQUE] INDEX <indexname> on <class_name> (fieldname)

Do a \h on create index for more info...

eric

> 
> Can someone point me in the right direction.  Thanks.
> 
> --
> Colin Dick
> On Call Internet Services
> cdick@mail.ocis.net
> 
> 
> 

_______________________
Eric McKeown
ericm@palaver.net
http://www.palaver.net

  8. Re: [SQL] Creating table with unique key.

    jose' soares <sferac@bo.nettuno.it> — 1998-08-06T14:09:20Z

    Hello Colin,

mercoledì, 5 agosto 98, you wrote:

>> > CREATE TABLE cliente (
>> >         name    varchar(100) UNIQUE NOT NULL,
>> >         username        varchar(8) NOT NULL ,
>> >         key     int4 NOT NULL DEFAULT nextval('key_s') PRIMARY KEY,
>> >         );

CD> Hi this is from a previous post regarding auto-incrementing.  I have that
CD> part working, now I am trying to create a table with a field which will
CD> not be null and will be unique to the rest of table.  Is this possible and
CD> what is the correct syntax.  I have tried and receeved the following:

CD> CREATE TABLE cust (FileNumber int4 unique not null, CompName varchar(50),
CD> FirstName varchar(50), LastName varchar(50), BirthDate varchar(8), Suite
CD> varchar(5), Address varchar(50), City varchar(20), Province varchar(20),
CD> PostalCode varchar(7), Email varchar(50), HomePhone varchar(14), WorkPhone
CD> varchar(14), FaxPhone varchar(14));
CD> WARN:parser: parse error at or near "unique"

CD> Can someone point me in the right direction.  Thanks.

CD> --
CD> Colin Dick
CD> On Call Internet Services
CD> cdick@mail.ocis.net


The syntax is correct for v6.3.2 and it works for me, maybe you have
an older release of PostgreSQL.

Best regards,
   Jose'                            mailto:sferac@bo.nettuno.it