pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedprimary key and insert
4+ messages / 4 participants
[nested] [flat]
* primary key and insert
@ 2008-12-11 23:27 Marc Fromm <Marc.Fromm@wwu.edu>
0 siblings, 3 replies; 4+ messages in thread
From: Marc Fromm @ 2008-12-11 23:27 UTC (permalink / raw)
To: pgsql-admin
I created this table:
Column | Type | Modifiers
------------+------------------------+------------------------
first_name | character varying(20) |
last_name | character varying(30) |
w_number | character varying(9) |
alert | character varying(200) |
post_date | date |
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
$sql = "INSERT INTO alert_list (
w_number,
first_name,
last_name,
alert,
post_date
)
VALUES (
'$txtStudentNumber',
'$txtStudentFirstName',
'$txtStudentLastName',
'$txtStudentAlert',
'now()'
)";
How do I do an insert on this table and have the primary key "id" update with the record?
thanks
Marc
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: primary key and insert
@ 2008-12-12 00:39 Steve Crawford <scrawford@pinpointresearch.com>
parent: Marc Fromm <Marc.Fromm@wwu.edu>
2 siblings, 0 replies; 4+ messages in thread
From: Steve Crawford @ 2008-12-12 00:39 UTC (permalink / raw)
To: Marc Fromm <Marc.Fromm@wwu.edu>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: primary key and insert
@ 2008-12-12 02:09 johnf <jfabiani@yolo.com>
parent: Marc Fromm <Marc.Fromm@wwu.edu>
2 siblings, 0 replies; 4+ messages in thread
From: johnf @ 2008-12-12 02:09 UTC (permalink / raw)
To: pgsql-admin
On Thursday 11 December 2008 03:27:59 pm Marc Fromm wrote:
> id | integer | not null
I believe you did not set the field 'id' to a data type of serial. If you had
you would have "nextval(" in the Modifiers column.
--
John Fabiani
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: primary key and insert
@ 2008-12-12 05:38 Suresh Borse <s.borse@direction.biz>
parent: Marc Fromm <Marc.Fromm@wwu.edu>
2 siblings, 0 replies; 4+ messages in thread
From: Suresh Borse @ 2008-12-12 05:38 UTC (permalink / raw)
To: Marc Fromm <Marc.Fromm@wwu.edu>; +Cc: pgsql-admin
Create a sequence and use the sequence in your insert statement.
On Thu, 2008-12-11 at 15:27 -0800, Marc Fromm wrote:
> I created this table:
> Column | Type | Modifiers
> ------------+------------------------+------------------------
> first_name | character varying(20) |
> last_name | character varying(30) |
> w_number | character varying(9) |
> alert | character varying(200) |
> post_date | date |
> 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]: Query failed: ERROR: null
> value in column "id" violates not-null constraint
>
> $sql = "INSERT INTO alert_list (
> w_number,
> first_name,
> last_name,
> alert,
> post_date
> )
> VALUES (
> '$txtStudentNumber',
> '$txtStudentFirstName',
> '$txtStudentLastName',
> '$txtStudentAlert',
> 'now()'
> )";
>
> How do I do an insert on this table and have the primary key "id"
> update with the record?
>
> thanks
>
> Marc
Thanks & Regards,
Suresh Borse
(DBA)
_________________________________________________________________________________________
DIRECTION SOFTWARE SOLUTIONS
5, Brady Gladys Plaza, 1/447, Senapati Bapat Marg, Lower Parel, Mumbai -
400 013
Tel. : 91 22 66615000 (Ext: 350) Fax : 91 22
24911046
Cell : 91 9422 239338 Site :
www.direction.biz
_________________________________________________________________________________________
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-12-12 05:38 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-11 23:27 primary key and insert Marc Fromm <Marc.Fromm@wwu.edu>
2008-12-12 00:39 ` Steve Crawford <scrawford@pinpointresearch.com>
2008-12-12 02:09 ` johnf <jfabiani@yolo.com>
2008-12-12 05:38 ` Suresh Borse <s.borse@direction.biz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox