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

Re: primary key and insert

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: Marc Fromm <Marc(dot)Fromm(at)wwu(dot)edu>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: primary key and insert
Date: 2008-12-12 00:39:43
Message-ID: 4941B2CF.1040906@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-admin
Marc Fromm wrote:
> I created this table:
> ...
>        id         | integer                       | not null
> Indexes:
>     "alert_list_pkey" PRIMARY KEY, btree (id)
> I get this error when I run the insert a listed below. The insert does 
> not have an entry for the primary key "id" since I thought it updates 
> automatically:
> *Warning*: pg_query() [function.pg-query 
> <http://finaid46.finaid.wwu.edu/lan/student_alerts/function.pg-query>]: 
> Query failed: ERROR: null value in column "id" violates not-null 
> constraint
> ...
> How do I do an insert on this table and have the primary key "id" 
> update with the record?
>  
I think you are confusing primary key and serial.

A column can be a primary key (not-null, unique row id) or a serial 
(actually automatically creates an integer type, a sequence, and a 
default for the column of nextval('sequence') to automatically generate 
a new value for each record).

A primary key could be a serial, but doesn't have to be.

A serial can be a primary key but doesn't have to be.

Note: due to things like transaction rollbacks, a serial column will 
have unique IDs generated but they are not guaranteed to be consecutive.

Cheers,
Steve


In response to

pgsql-admin by date

Next:From: johnfDate: 2008-12-12 02:09:47
Subject: Re: primary key and insert
Previous:From: Marc FrommDate: 2008-12-11 23:27:59
Subject: primary key and insert

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