Thread: data integrity and inserts
I want to ensure data integrity when inserting into a table, preventing multiple entries of identical rows of data. Does this call for using a trigger? How would triggers perform a query to test if data already exists in the table? (The doco outlines how triggers perform tests on NEW data inserted into a table; but I haven't found anything on data already extant.) Thanks in advance! Scott sample table: CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text);
From: "Scott Frankel" <leknarf@pacbell.net> > > I want to ensure data integrity when inserting into a table, preventing > multiple > entries of identical rows of data. > > sample table: > > CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text); sounds like a job for a UNIQUE constraint gnari
Scott Frankel wrote: > > I want to ensure data integrity when inserting into a table, preventing > multiple > entries of identical rows of data. Just use a unique index on the columns you want to make sure are not duplicated. > > Does this call for using a trigger? > How would triggers perform a query to test if data already exists in the > table? > > (The doco outlines how triggers perform tests on NEW data inserted into a > table; but I haven't found anything on data already extant.) > > Thanks in advance! > Scott > > > sample table: > > CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text); > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Attachment
CREATE UNIQUE INDEX uidx_thename ON names(the_name); Should prevent duplicates. Ian Harding Programmer/Analyst II Tacoma-Pierce County Health Department iharding@tpchd.org Phone: (253) 798-3549 Pager: (253) 754-0002 >>> Scott Frankel <leknarf@pacbell.net> 12/01/04 10:11 AM >>> I want to ensure data integrity when inserting into a table, preventing multiple entries of identical rows of data. Does this call for using a trigger? How would triggers perform a query to test if data already exists in the table? (The doco outlines how triggers perform tests on NEW data inserted into a table; but I haven't found anything on data already extant.) Thanks in advance! Scott sample table: CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text); ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
1.
CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text);
CREATE UNIQUE INDEX uidx_thename ON names(the_name);
vs.
2.
CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text UNIQUE);
Is the UNIQUE constraint in the second solution merely short-hand for
the explicit
index declaration of the first solution? Or is there a functional
difference between
them that I should choose between?
Thanks again!
Scott
On Dec 1, 2004, at 10:11 AM, Scott Frankel wrote:
>
> I want to ensure data integrity when inserting into a table,
> preventing multiple
> entries of identical rows of data.
>
> Does this call for using a trigger?
> How would triggers perform a query to test if data already exists in
> the table?
>
> (The doco outlines how triggers perform tests on NEW data inserted
> into a
> table; but I haven't found anything on data already extant.)
>
> Thanks in advance!
> Scott
>
>
> sample table:
>
> CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text);
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
On Wed, Dec 01, 2004 at 10:48:40 -0800, Scott Frankel <leknarf@pacbell.net> wrote: > > 1. > CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text); > CREATE UNIQUE INDEX uidx_thename ON names(the_name); > > vs. > > 2. > CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text UNIQUE); > > > Is the UNIQUE constraint in the second solution merely short-hand for > the explicit > index declaration of the first solution? Or is there a functional > difference between > them that I should choose between? Currently the only way to enforce a UNIQUE constraint is by using an index. So there isn't really much difference between the two. However, I think using the UNIQUE constraint provides better meaning than using an index for people who might look at your definitions later.
The second is shorthand for the first. you get to choose the index name
in the first one.
Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
Phone: (253) 798-3549
Pager: (253) 754-0002
>>> Scott Frankel <leknarf@pacbell.net> 12/01/04 10:48 AM >>>
1.
CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text);
CREATE UNIQUE INDEX uidx_thename ON names(the_name);
vs.
2.
CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text UNIQUE);
Is the UNIQUE constraint in the second solution merely short-hand for
the explicit
index declaration of the first solution? Or is there a functional
difference between
them that I should choose between?
Thanks again!
Scott
On Dec 1, 2004, at 10:11 AM, Scott Frankel wrote:
>
> I want to ensure data integrity when inserting into a table,
> preventing multiple
> entries of identical rows of data.
>
> Does this call for using a trigger?
> How would triggers perform a query to test if data already exists in
> the table?
>
> (The doco outlines how triggers perform tests on NEW data inserted
> into a
> table; but I haven't found anything on data already extant.)
>
> Thanks in advance!
> Scott
>
>
> sample table:
>
> CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text);
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
"Ian Harding" <iharding@tpchd.org> writes:
> The second is shorthand for the first. you get to choose the index name
> in the first one.
IIRC you can force the index name in the second case too, by using
the fully unabbreviated CONSTRAINT syntax:
..., CONSTRAINT indexname UNIQUE(colname), ...
regards, tom lane
The first way also makes it possible to put the constraint on multiple fields: create unique index uidx_abc on my_table(col_a, col_b, col_c); On Dec 2, 2004, at 1:51 PM, Ian Harding wrote: > The second is shorthand for the first. you get to choose the index > name > in the first one. > > Ian Harding > Programmer/Analyst II > Tacoma-Pierce County Health Department > iharding@tpchd.org > Phone: (253) 798-3549 > Pager: (253) 754-0002 > >>>> Scott Frankel <leknarf@pacbell.net> 12/01/04 10:48 AM >>> > > 1. > CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text); > CREATE UNIQUE INDEX uidx_thename ON names(the_name); > > vs. > > 2. > CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text UNIQUE); > > > Is the UNIQUE constraint in the second solution merely short-hand for > the explicit > index declaration of the first solution? Or is there a functional > difference between > them that I should choose between? > > Thanks again! > Scott > > > > > On Dec 1, 2004, at 10:11 AM, Scott Frankel wrote: > >> >> I want to ensure data integrity when inserting into a table, >> preventing multiple >> entries of identical rows of data. >> >> Does this call for using a trigger? >> How would triggers perform a query to test if data already exists in >> the table? >> >> (The doco outlines how triggers perform tests on NEW data inserted >> into a >> table; but I haven't found anything on data already extant.) >> >> Thanks in advance! >> Scott >> >> >> sample table: >> >> CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text); >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 4: Don't 'kill -9' the postmaster >> > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if > your > joining column's datatypes do not match >
On Thu, Dec 02, 2004 at 14:20:35 -0600, Timothy Perrigo <tperrigo@wernervas.com> wrote: > The first way also makes it possible to put the constraint on multiple > fields: > > create unique index uidx_abc on my_table(col_a, col_b, col_c); You can do that with unique constraints as well.