Postgres Pro
Downloads
Home   >   mailing lists

Thread: primary key

primary key

From
"Peter Evens"
Date:
hello,

i have a question about the PRIMARY KEY,
how can we let it start from for example 1000 instead of 1?

This is our program:
CREATE TABLE hy3_pack
(
 hy3_id serial NOT NULL,
 hy3_serie_nummer text NOT NULL,
 hy3_barcode text NOT NULL,
 hy3_type_vulling text NOT NULL,
 hy3_tarra_gewicht text,
 hy3_artikel_id numeric NOT NULL,
 hy3_refill boolean,
 vernietigd boolean,
 opmerking text,
 CONSTRAINT hy3_pack_pkey PRIMARY KEY (hy3_id)
)
WITH (
 OIDS=FALSE
);
ALTER TABLE hy3_pack OWNER TO postgres;

what must i change in my program?

Met vriendelijke groeten,
Peter Evens
BANDIT nv/sa
Nijverheidslaan 1547
B-3660 Opglabbeek
België
Tel. 0032/89/85.85.65
Fax 0032/89/51.85.47
peter@bandit.be

Re: primary key

From
Raymond O'Donnell
Date:
On 14/03/2011 07:57, Peter Evens wrote:
>
> hello,
>
> i have a question about the PRIMARY KEY,
> how can we let it start from for example 1000 instead of 1?
>
> This is our program:
> CREATE TABLE hy3_pack
> (
>   hy3_id serial NOT NULL,
>   hy3_serie_nummer text NOT NULL,
>   hy3_barcode text NOT NULL,
>   hy3_type_vulling text NOT NULL,
>   hy3_tarra_gewicht text,
>   hy3_artikel_id numeric NOT NULL,
>   hy3_refill boolean,
>   vernietigd boolean,
>   opmerking text,
>   CONSTRAINT hy3_pack_pkey PRIMARY KEY (hy3_id)
> )
> WITH (
>   OIDS=FALSE
> );
> ALTER TABLE hy3_pack OWNER TO postgres;
>
> what must i change in my program?

After the CREATE TABLE, use setval() to set the current value of the
sequence used for hy3_id.

http://www.postgresql.org/docs/9.0/static/functions-sequence.html

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: primary key

From
Adrian Klaver
Date:
On Monday, March 14, 2011 12:57:07 am Peter Evens wrote:
> hello,
>
> i have a question about the PRIMARY KEY,
> how can we let it start from for example 1000 instead of 1?
>
> This is our program:
> CREATE TABLE hy3_pack
> (
>  hy3_id serial NOT NULL,
>  hy3_serie_nummer text NOT NULL,
>  hy3_barcode text NOT NULL,
>  hy3_type_vulling text NOT NULL,
>  hy3_tarra_gewicht text,
>  hy3_artikel_id numeric NOT NULL,
>  hy3_refill boolean,
>  vernietigd boolean,
>  opmerking text,
>  CONSTRAINT hy3_pack_pkey PRIMARY KEY (hy3_id)
> )
> WITH (
>  OIDS=FALSE
> );
> ALTER TABLE hy3_pack OWNER TO postgres;
>
> what must i change in my program?
>

First do something like below to get the name of the sequence associated with
the serial type:

test(5432)aklaver=>\d serial_test
                         Table "public.serial_test"
 Column |  Type   |                        Modifiers
--------+---------+----------------------------------------------------------
 id     | integer | not null default nextval('serial_test_id_seq'::regclass)
 fld_1  | text    |

Then go here:
http://www.postgresql.org/docs/9.0/interactive/functions-sequence.html

and use one of the setval() functions to set the sequence start to 1000.

Note: This is assuming the table is empty and you will not have any id
collisions.

--
Adrian Klaver
adrian.klaver@gmail.com