Re: [SQL] Constraint question
On Thursday 18 May 2006 18:38, Markus Schaber wrote: > Hi, Andreas, > > Andreas Joseph Krogh wrote: > > create table onp_crm_businessfield_company( > > businessfield_id integer not null references onp_crm_businessfield(id), > > company_id integer not null references onp_crm_relation(id), > > is_preferred boolean, > > UNIQUE(businessfield_id, company_id) > > ); > > > > I want a constraint on "is_preffered" so that it's only allowed to be set > > once pr. businessfield_id pr. company so that only one businessfield can > > be preferred for a company. Does anyone have an idea how to enforce this? > > CREATE UNIQUE INDEX foo ON onp_crm_businessfield_company(company_id) > WHERE is_prefferred; Thanks. I also figured out that an index like this also works: UNIQUE(company_id, is_preferred) This works because NULL = NULL is false. But I guess your solution is cleaner. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ ---(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] Constraint question
Hi, Andreas, Andreas Joseph Krogh wrote: >>CREATE UNIQUE INDEX foo ON onp_crm_businessfield_company(company_id) >>WHERE is_prefferred; > I also figured out that an index like this also works: > UNIQUE(company_id, is_preferred) > This works because NULL = NULL is false. But I guess your solution is cleaner. Yes, it works, but it needs more disk space (it has to index all rows, and it needs to save both columns), and thus is slower, too. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] usernames of a group from SQL
Hi, At the first time: I'm in newbie in SQL... I'm sorry! My question is: How can I get the usernames of a group (for example 'osztatlan_users') from SQL? I try it: SELECT usename FROM pg_user WHERE usesysid IN (SELECT grolist FROM pg_group WHERE groname='osztatlan_users') ORDER BY pg_user; But I get the next error message: ERROR: operator does not exist: oid = oid[] HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. I see, what is the problem, but what can I do...? Thans, kjt McAfee SCM 4.1 által ellenőrizve! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] usernames of a group from SQL
O Kis Jαnos Tamαs έγραψε στις May 19, 2006 : > Hi, > > At the first time: I'm in newbie in SQL... I'm sorry! > > My question is: How can I get the usernames of a group (for example > 'osztatlan_users') from SQL? > > I try it: > > SELECT usename > FROM pg_user > WHERE usesysid IN (SELECT grolist FROM pg_group WHERE > groname='osztatlan_users') > ORDER BY pg_user; > > But I get the next error message: > > ERROR: operator does not exist: oid = oid[] > HINT: No operator matches the given name and argument type(s). You > may need to add explicit type casts. > > I see, what is the problem, but what can I do...? SELECT u.usename from pg_user u,pg_group g where u.usesysid = any (g.grolist) and g.groname='osztatlan_users'; > > Thans, > kjt > > > McAfee SCM 4.1 αltal ellenυrizve! > > ---(end of broadcast)---TIP > 4: Have you searched our list archives? >http://archives.postgresql.org > -- -Achilleus ---(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
[SQL] Problem with SET CONSTRAINTS ALL DEFERRED;
Hi Friends, I am trying to postpone the foreign key constraint check till the end of transaction but it doesn't work. Can anyone help me with a tip what I am doing wrong. Thanks in advance. Kaloyan Iliev test=# BEGIN; BEGIN test=# test=# SET CONSTRAINTS ALL DEFERRED; SET CONSTRAINTS test=# DELETE FROM picture test-# USING element_picture test-# WHERE picture.id = element_picture.picture_id test-# AND element_picture.element_id = 8; ERROR: update or delete on "picture" violates foreign key constraint "picture_id" on "element_picture" DETAIL: Key (id)=(223) is still referenced from table "element_picture". select version(); version PostgreSQL 8.1.2 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518 (1 row) ---(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] Problem with SET CONSTRAINTS ALL DEFERRED;
On Fri, 19 May 2006, Kaloyan Iliev wrote: > Hi Friends, > > I am trying to postpone the foreign key constraint check till the end of > transaction but it doesn't work. > Can anyone help me with a tip what I am doing wrong. Was the constraint created as deferrable (which is not the default)? ALL DEFERRED actually only applies to deferrable constraints. > > Thanks in advance. > > Kaloyan Iliev > > test=# BEGIN; > BEGIN > test=# > test=# SET CONSTRAINTS ALL DEFERRED; > SET CONSTRAINTS > test=# DELETE FROM picture > test-# USING > element_picture > test-# WHERE > picture.id = element_picture.picture_id > test-# > AND element_picture.element_id = 8; > ERROR: update or delete on "picture" violates foreign key constraint > "picture_id" on "element_picture" > DETAIL: Key (id)=(223) is still referenced from table "element_picture". > > > select version(); > version > > PostgreSQL 8.1.2 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC) > 3.4.4 [FreeBSD] 20050518 > (1 row) > > > ---(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 > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
