[SQL] Need help with 'unique parents' constraint

[Leif B. Kristensen]

This message has also been posted to comp.databases.

I've got a problem that I can't quite wrap my head around, about adding
a constraint to my PostgreSQL genealogy database. If somebody are
interested, I've written some thoughts on the general design at
.

I've got two tables, persons and relations. I need a separate relations
table for source referencing and discussion. Here are my preliminary
definitions (irrelevant columns removed):

CREATE TABLE persons (
person_id   INTEGER PRIMARY KEY,
gender  SMALLINT NOT NULL DEFAULT 0 
CHECK (gender IN (0,1,2,9)) -- ISO gender codes
);

CREATE TABLE relations (
relation_id INTEGER PRIMARY KEY,
child_fk INTEGER REFERENCES persons (person_id),
parent_fk INTEGER REFERENCES persons (person_id),
CONSTRAINT child_parent UNIQUE (child_fk, parent_fk)
);

Now, I want to ensure that each person_id can be assigned only one
father (gender=1) and one mother (gender=2). (Yes, this is old-
fashioned, but I'm working with 18th century people). How do I do it?

I have tried this:

ALTER TABLE relations ADD CONSTRAINT non_unique_father
CHECK (NOT EXISTS
(SELECT persons.person_id, relations.parent_fk
FROM persons AS P, relations AS R
WHERE R.parent_fk = P.person_id
AND P.gender = 1));

But psql replies with:

pgslekt=> \i install/add_unique_father_and_mother_constraint.sql
psql:install/add_unique_father_and_mother_constraint.sql:9: NOTICE:  
adding missing FROM-clause entry in subquery for table "persons"
psql:install/add_unique_father_and_mother_constraint.sql:9: ERROR:  
cannot use subquery in check constraint

>From what I've found on Google, it looks like the "cannot use subquery 
in check constraint" is a real limitation in PostgreSQL. Can I use a 
trigger to achieve what I want? I'm still a little shaky on triggers 
and what they can do, having quite recently converted to PostgreSQL 
from a certain Swedish dinky-db.
-- 
Leif Biberg Kristensen
http://solumslekt.org/

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Need help with 'unique parents' constraint

[Leif B. Kristensen]

On Sunday 11 September 2005 14:24, Leif B. Kristensen wrote:

> ALTER TABLE relations ADD CONSTRAINT non_unique_father
> CHECK (NOT EXISTS
> (SELECT persons.person_id, relations.parent_fk
> FROM persons AS P, relations AS R
> WHERE R.parent_fk = P.person_id
> AND P.gender = 1));

Forget this. Please pretend that you never saw it in the first place :-)

I've done some experimenting:

pgslekt=> alter table relations add column rel_type smallint
pgslekt-> not null default 0 check (rel_type in (0,1,2,9));
ALTER TABLE
pgslekt=> update relations set rel_type = (select gender from
pgslekt(> persons where person_id = parent_fk);
UPDATE 20012
pgslekt=> select * from relations where child_fk=1;
 relation_id | child_fk | parent_fk | rel_memo | rel_type
-+--+---+--+--
   3 |1 | 2 |  |1
   4 |1 | 3 |  |2
(2 rows)

pgslekt=> alter table relations add constraint unique_parent
pgslekt-> unique (child_fk,rel_type);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index 
"unique_parent" for table "relations"
ALTER TABLE

And this is more or less what I want. But I don't like the redundant 
relations.rel_type column.
-- 
Leif Biberg Kristensen
http://solumslekt.org/

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Need help with 'unique parents' constraint

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


> Now, I want to ensure that each person_id can be assigned only one
> father (gender=1) and one mother (gender=2). (Yes, this is old-
> fashioned, but I'm working with 18th century people). How do I do it?

Not just old-fashioned, it's the biological law! (among homo sapiens anyway).
I'd approach this with a trigger, as you can do complex checks and get back
nice customized error messages. A sample script follows. Hard to tell without
seeing your whole schema, but I see no need for a relation_id primary key
if you already have a unique constraint on child_fk and parent_fk, so I
made those into the primary key for the relations table:


DROP TABLE relations;
DROP TABLE persons;
DROP FUNCTION relation_check();
DROP SEQUENCE persons_seq_id;
  
CREATE SEQUENCE persons_seq_id;
CREATE TABLE persons (
  person_id   INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('persons_seq_id'),
  gender  SMALLINT NOT NULL DEFAULT 0
CHECK (gender IN (0,1,2,9))
);
COMMENT ON COLUMN persons.gender IS 'ISO Gender code 1=father 2=mother';
  
CREATE TABLE relations (
  child_fk INTEGER REFERENCES persons (person_id) ON DELETE CASCADE,
  parent_fk INTEGER REFERENCES persons (person_id) ON DELETE CASCADE,
  PRIMARY KEY (child_fk, parent_fk)
);
  
CREATE FUNCTION relation_check() RETURNS TRIGGER LANGUAGE plpgsql AS
$$
DECLARE
  xy SMALLINT;
  trace INTEGER;
BEGIN
- -- Assume that child or parent has changed, since this version has no other 
columns
  
IF NEW.child_fk = NEW.parent_fk THEN
  RAISE EXCEPTION 'Bioethics error: Human cloning not supported yet';
END IF;
  
SELECT gender FROM persons WHERE person_id = NEW.parent_fk INTO xy;
  
- -- More than one father?
IF xy = 1 THEN
  SELECT parent_fk FROM relations r, persons p WHERE r.child_fk = NEW.child_fk
AND r.parent_fk = p.person_id AND p.gender = 1 INTO trace;
  IF trace IS NOT NULL THEN
IF TG_OP = 'UPDATE' THEN
  IF OLD.parent_fk != trace THEN
RAISE EXCEPTION 'Error: Cannot change parent: person % is already 
assigned as the father', trace;
  END IF;
ELSE
  RAISE EXCEPTION 'Error: Person % is already assigned as the father', 
trace;
END IF;
  END IF;
END IF;
  
- -- More than one mother?
IF xy = 2 THEN
  SELECT parent_fk FROM relations r, persons p WHERE r.child_fk = NEW.child_fk
AND r.parent_fk = p.person_id AND p.gender = 2 INTO trace;
  IF trace IS NOT NULL THEN
IF TG_OP = 'UPDATE' THEN
  IF OLD.parent_fk != trace THEN
RAISE EXCEPTION 'Error: Cannot change parent: person % is already 
assigned as the mother', trace;
  END IF;
ELSE
  RAISE EXCEPTION 'Error: Person % is already assigned as the mother', 
trace;
END IF;
  END IF;
END IF;
  
RETURN NEW;
END;
$$;
  
CREATE TRIGGER relation_check BEFORE INSERT OR UPDATE ON relations
FOR EACH ROW EXECUTE PROCEDURE relation_check();
  
INSERT INTO persons(gender) VALUES (1);
INSERT INTO persons(gender) VALUES (2);
INSERT INTO persons(gender) VALUES (1);
INSERT INTO persons(gender) VALUES (2);
INSERT INTO persons(gender) VALUES (0);
INSERT INTO persons(gender) VALUES (1);
  
INSERT INTO relations VALUES (3,1);
INSERT INTO relations VALUES (3,2);
  
SELECT 'Cloning test' AS "Test should fail";
INSERT INTO relations VALUES (3,3);
  
SELECT 'Change father to another mother' AS "Test should fail";
UPDATE relations SET parent_fk = 4 WHERE child_fk = 3 AND parent_fk = 1;
  
SELECT 'Add in a second father' AS "Test should fail";
INSERT INTO relations VALUES (3,6);
  
SELECT 'Change fathers' AS "Test should pass";
UPDATE relations SET parent_fk = 6 WHERE child_fk = 3 AND parent_fk = 1;
  
SELECT 'Change mother to another father' AS "Test should fail";
UPDATE relations SET parent_fk = 6 WHERE child_fk = 3 AND parent_fk = 2;
  
SELECT 'Add in a second mother' AS "Test should fail";
INSERT INTO relations VALUES (3,4);
  
SELECT 'Change mothers' AS "Test should pass";
UPDATE relations SET parent_fk = 4 WHERE child_fk = 3 AND parent_fk = 2;
  
SELECT 'Add non-mother/father' AS "Test should pass";
INSERT INTO relations VALUES (3,5);
  
SELECT 'Change non-mother/father to mother' AS "Test should fail";
UPDATE relations SET parent_fk = 2 WHERE child_fk = 3 AND parent_fk = 5;
  
SELECT * FROM relations;
  
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200509110958
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iD8DBQFDJDkzvJuQZxSWSsgRAryTAJ90oT0LWl2ch6c7T7tPsj1/+JpRFwCeOLsV
ceYzuVEHbZPjdCgaMCG65rQ=
=wh38
-END PGP SIGNATURE-



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] Panic: Page Add Item: Corrupted page pointers

[The One]

Hi,
 
I'm getting this error when try to create a table in Postgresql 7.3, windows2000:
 
Panic: Page Add Item: Corrupted page pointers: lower=24258, upper=39318, special=65421
 
LOG: statement = create table data(key unique, status text, info text)
LOG: pg_recvby : unexpected EOF on client connection
LOG: server process  was terminated by signal 6
LOG: all server processes terminated; reinitializing shared memory and semaphores
.
 
Thank you
 
 
 
		Yahoo! for Good 
Watch the Hurricane Katrina Shelter From The Storm concert

Re: [SQL] Need help with 'unique parents' constraint

[John Hasler]

Greg Sabino Mullane writes:
> Not just old-fashioned, [having only one mother is] the biological law!

I see you aren't up on current research.
-- 
John Hasler 
[EMAIL PROTECTED]
Elmwood, WI USA

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Panic: PageAddItem: Corrupted page pointers

[The One]

Actually, I'm getting this error everytime I try to create a table.
 
Can someone please help?The One <[EMAIL PROTECTED]> wrote:

Hi,
 
I'm getting this error when try to create a table in Postgresql 7.3, windows2000:
 
Panic: Page Add Item: Corrupted page pointers: lower=24258, upper=39318, special=65421
 
LOG: statement = create table data(key unique, status text, info text)
LOG: pg_recvby : unexpected EOF on client connection
LOG: server process  was terminated by signal 6
LOG: all server processes terminated; reinitializing shared memory and semaphores
.
 
Thank you
 
 
 


Yahoo! for GoodWatch the Hurricane Katrina Shelter From The Storm concert 
		Yahoo! for Good 
Watch the Hurricane Katrina Shelter From The Storm concert

[SQL] showing multiple reference details from single query

[Ferindo Middleton Jr]

I have a table (table_one) with two columns, both of which are integers 
which reference the same column (on a one-to-many relationship) row back 
at another table (table_two) which has more detailed info on these 
integer columns:


table_one has the following columns:
id (SERIAL), column_one (INTEGER REFERENCES table_two(id)), column_two 
(INTEGER REFERENCES table_two(id))


table_two has the following columns:
id (SERIAL), more_detailed_info (TEXT)

How can I write a single query which will can uniquely identify in the 
result the separated values of the more_detailed_info field of 
table_two? For instance, if I write the following query:
SELECT * FROM table_one WHERE id = 4 AND table_one.column_one = 
table_two.id;


I'll get rows which will list the more_detailed_info fields one time for 
each match but table_two has more_detailed_info for both column_one and 
column_two. However the query above will only be able to show the 
more_detailed_info field for column_one. How can I show the 
more_detailed_info field for column_two as well on the same row, 
simultaneously with that of column_one


Sorry if this is confusing. I don't know of all the technical jargon 
which involves my question above.


Ferindo

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] showing multiple reference details from single query

[Michael Fuhr]

On Sun, Sep 11, 2005 at 06:37:52PM -0400, Ferindo Middleton Jr wrote:
> table_one has the following columns:
> id (SERIAL), column_one (INTEGER REFERENCES table_two(id)), column_two 
> (INTEGER REFERENCES table_two(id))
> 
> table_two has the following columns:
> id (SERIAL), more_detailed_info (TEXT)
> 
> How can I write a single query which will can uniquely identify in the 
> result the separated values of the more_detailed_info field of 
> table_two?

I think you're looking for something like this:

SELECT t1.id,
   t1.column_one, t2a.more_detailed_info,
   t1.column_two, t2b.more_detailed_info
FROM table_one AS t1
JOIN table_two AS t2a ON t2a.id = t1.column_one
JOIN table_two AS t2b ON t2b.id = t1.column_two;

-- 
Michael Fuhr

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Panic: PageAddItem: Corrupted page pointers

[Alvaro Herrera]

On Sun, Sep 11, 2005 at 02:20:01PM -0700, The One wrote:
> Actually, I'm getting this error everytime I try to create a table.
>  
> Can someone please help?

Where did you get that version of PostgreSQL?  Is it the Cygwin version?
AFAIK it's labelled as "not robust enough for production".

> The One <[EMAIL PROTECTED]> wrote:
> Hi,
>  
> I'm getting this error when try to create a table in Postgresql 7.3, 
> windows2000:
>  
> Panic: Page Add Item: Corrupted page pointers: lower=24258, upper=39318, 
> special=65421

Clearly the page is corrupt.  The fact that you are using an unsupported
version does not help you get a lot of support ...  Also, note that this
is quite off-topic for pgsql-sql.

-- 
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
Este mail se entrega garantizadamente 100% libre de sarcasmo.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] Panic: Page Add Item: Corrupted page pointers

[Tom Lane]

The One <[EMAIL PROTECTED]> writes:
> I'm getting this error when try to create a table in Postgresql 7.3, 
> windows2000:
 
> Panic: Page Add Item: Corrupted page pointers: lower=24258, upper=39318, 
> special=65421

Apparently you've got at least one corrupt page in at least one system
table.  I'd recommend pg_dump'ing as much as you can and then recreate
the database.

If pg_dump doesn't work you could try turning on zero_damaged_pages,
and praying that most of what you need is on undamaged pages.  It's
quite possible though that that will hose the DB completely, so don't do
it till you've exhausted the possibilities for pg_dump without it.

Consider updating to a more recent PG release while you are
recovering...

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[SQL] howto insert dynamic value

[liofr]

hi
i want to put the actual date in cell
what is the name of the functrion to do that (date()) ?
thnak's 



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] howto insert dynamic value

[Michael Fuhr]

On Mon, Sep 12, 2005 at 04:19:14AM +0200, liofr wrote:
> i want to put the actual date in cell
> what is the name of the functrion to do that (date()) ?

See "Date/Time Functions and Operators" in the "Functions and
Operators" chapter of the documentation.  Here's a link to the
latest version:

http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html

-- 
Michael Fuhr

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] howto insert dynamic value

[wendell]

http://www.postgresql.org/docs/7.3/interactive/functions-datetime.html

  - Original Message - 
  From: 
  liofr 
  To: pgsql-sql@postgresql.org 
  Sent: Monday, September 12, 2005 
  10:19 AM
  Subject: [SQL] howto insert 
  dynamic value
  hii want to put the actual date in cellwhat is the name 
  of the functrion to do that (date()) ?thnak's 
  ---(end of 
  broadcast)---TIP 2: Don't 'kill -9' the 
  postmaster