[GENERAL] Constraint problem
Hi everybody , I try using Postgre, but I have some problems. I create a constraint ( R1>=0 ), and after connect to server from VFP via ODBC . In the client program I turn on the transactions . ( SQLSETPROP(nHandle,'Transactions',2) ) When I run UPDATE statements one after the other , and one return false because of constraint , then the server rolling back all changing automatically. I would like to: server ignore the bad statements (return false) and after I call rollback or commit manuallypossible ? Thanks your help , Zoltan
[GENERAL] constraint problem
I have a table with a login, password and confirmed columns (besides others), and I'm having so trouble getting this contraint to work. The account is created with login and password NULL and confirmed set to false. Once the user gives the app he's login and password (login is unique) the system updates that row with the new login, password and sets confirmed to true. Now to the constraint: I don't want the login and password columns to have nulls when the account (row) is confirmed (confirmed column is set to true). I tried adding this CONSTRAINT to the table definition, but with no luck: CONSTRAINT nonuloconfirmado CHECK ((login NOT NULL AND password NOT NULL) OR NOT confirmado) It gives an error on the first NULL. How can this be solved? -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués | Programador, DBA Centro de Telemática| Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Constraint Problem
I want to create a constraint that's a little different, but don't know how to implement it. I have an intermediate table with a compound PK and a boolean 'ysnDefault' column: comanyID INTEGER REFERENCES companies, assocationID INTEGER REFERENCES associations, ysnDefault BOOLEAN I just want to constrain the ysnDefault field to only allow one TRUE value for any companyID/associationID pair, with no restrictions on the number of FALSES. At first glance I thought it would be easy, but I can't see how to do it. Any suggestions? Thanks Ron ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Constraint problem
On Mar 22, 2005, at 7:26 AM, Szmutku Zoltán wrote: Hi everybody , I try using Postgre, but I have some problems. I create a constraint ( R1>=0 ), and after connect to server from VFP via ODBC . In the client program I turn on the transactions . ( SQLSETPROP(nHandle,'Transactions',2) ) When I run UPDATE statements one after the other , and one return false because of constraint , then the server rolling back all changing automatically. I would like to: server ignore the bad statements (return false) and after I call rollback or commit manuallypossible ? Within a transaction, if one statement fails, all changes will be rolled back. If you want to commit each statement that works, you can commit (or rollback) after each statement. Sean ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Constraint problem
On Tue, Mar 22, 2005 at 07:41:50AM -0500, Sean Davis wrote: > > Within a transaction, if one statement fails, all changes will be > rolled back. If you want to commit each statement that works, you can > commit (or rollback) after each statement. ...or use savepoints (new in PostgreSQL 8.0). -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Constraint problem
Sean Davis <[EMAIL PROTECTED]> writes: > On Mar 22, 2005, at 7:26 AM, Szmutku Zoltán wrote: >> I would like to: server ignore the bad statements (return false) and >> after I call rollback or commit manuallypossible ? > Within a transaction, if one statement fails, all changes will be > rolled back. If you want to commit each statement that works, you can > commit (or rollback) after each statement. In PG 8.0, you can recover from errors without having to commit the whole transaction by using savepoints. There isn't any way to do that pre-8.0. regards, tom lane ---(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
Re: [GENERAL] constraint problem
On Mon, 25 Jul 2005, [iso-8859-1] Martín Marqués wrote: > I have a table with a login, password and confirmed columns (besides others), > and I'm having so trouble getting this contraint to work. > > The account is created with login and password NULL and confirmed set to > false. Once the user gives the app he's login and password (login is unique) > the system updates that row with the new login, password and sets confirmed > to true. > > Now to the constraint: > > I don't want the login and password columns to have nulls when the account > (row) is confirmed (confirmed column is set to true). > > I tried adding this CONSTRAINT to the table definition, but with no luck: > > CONSTRAINT nonuloconfirmado CHECK > ((login NOT NULL AND password NOT NULL) OR NOT confirmado) > > It gives an error on the first NULL. > > How can this be solved? I think you'd want (login IS NOT NULL AND password IS NOT NULL) OR NOT confirmado ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] constraint problem
On Mon, Jul 25, 2005 at 08:28:32PM -0300, Martín Marqués wrote: > I tried adding this CONSTRAINT to the table definition, but with no luck: > > CONSTRAINT nonuloconfirmado CHECK > ((login NOT NULL AND password NOT NULL) OR NOT confirmado) > > It gives an error on the first NULL. Use IS NOT NULL instead of NOT NULL. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] constraint problem
El Lun 25 Jul 2005 23:29, Stephan Szabo escribió: > > > > Now to the constraint: > > > > I don't want the login and password columns to have nulls when the account > > (row) is confirmed (confirmed column is set to true). > > > > I tried adding this CONSTRAINT to the table definition, but with no luck: > > > > CONSTRAINT nonuloconfirmado CHECK > > ((login NOT NULL AND password NOT NULL) OR NOT confirmado) > > > > It gives an error on the first NULL. > > > > How can this be solved? > > I think you'd want > (login IS NOT NULL AND password IS NOT NULL) OR NOT confirmado HOW COULD I HAVE MISSED THAT!!! I feel so stupid today. :-( -- 07:31:18 up 23 days, 16:16, 1 user, load average: 1.09, 0.96, 0.74 - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática | '@' || 'unl.edu.ar'; Universidad Nacional | DBA, Programador, del Litoral | Administrador - ---(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
Re: [GENERAL] Constraint Problem
Ron St-Pierre <[EMAIL PROTECTED]> writes: > I just want to constrain the ysnDefault field to only allow one TRUE > value for any companyID/associationID pair, with no restrictions on the > number of FALSES. You could do that with a partial unique index. There is an example at the bottom of http://www.postgresql.org/docs/7.3/static/indexes-partial.html regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Constraint Problem
Tom Lane wrote: Ron St-Pierre <[EMAIL PROTECTED]> writes: I just want to constrain the ysnDefault field to only allow one TRUE value for any companyID/associationID pair, with no restrictions on the number of FALSES. You could do that with a partial unique index. There is an example at the bottom of http://www.postgresql.org/docs/7.3/static/indexes-partial.html regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html Thanks Tom. That's exactly what I need. Ron ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Constraint Problem
On Tue, 4 Nov 2003, Ron St-Pierre wrote: > Greg Stark wrote: > > >Ron St-Pierre <[EMAIL PROTECTED]> writes: > > > > > > > >>This is not quite what I need. I need to create a constraint to allow only > >>-one- of > >>company<->association<->default(=true) value > >>but any number of > >>company<->association<->default(=false) values > >> > >> > > > >So a unique index on "(company,association) where default" doesn't do what you > >want? > > > No it doesn't. For example, after I create the unique index I can still > input: > company10 association7 true > company10 association7 true > company10 association7 true You shouldn't be able to and I can't replicate similar behavior in a simple test on 7.3.4. I get "Cannot insert a duplicate key into unique index" errors. create table a(a int, b int, c boolean); create unique index a_ind on a(a,b) where c; insert into a values (1,1,true); insert into a values (1,1,true); insert into a values (1,1,false); insert into a values (1,1,false); insert into a values (1,2,true); Where the second insert fails, but the others succeed. ---(end of broadcast)--- TIP 3: 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: [GENERAL] Constraint Problem
Ron St-Pierre <[EMAIL PROTECTED]> writes:
> No it doesn't. For example, after I create the unique index I can still input:
> company10 association7 true
> company10 association7 true
> company10 association7 true
> I want to prevent this from happening, but still allow multiple
> company10 association7 false
> company10 association7 false
> entries for example.
For example:
test=# create table test (company integer, association integer, isdefault boolean);
CREATE TABLE
test=# create unique index testi on (company,association) where isdefault;
ERROR: syntax error at or near "(" at character 30
test=# create unique index testi on test (company,association) where isdefault;
CREATE INDEX
test=# insert into test values (10,7,true);
INSERT 6888594 1
test=# insert into test values (10,7,true);
ERROR: duplicate key violates unique constraint "testi"
test=# insert into test values (10,7,false);
INSERT 6888596 1
test=# insert into test values (10,7,false);
INSERT 6888597 1
test=# select * from test;
company | association | isdefault
-+-+---
10 | 7 | t
10 | 7 | f
10 | 7 | f
(3 rows)
--
greg
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Constraint Problem
Greg Stark wrote: Ron St-Pierre <[EMAIL PROTECTED]> writes: This is not quite what I need. I need to create a constraint to allow only -one- of company<->association<->default(=true) value but any number of company<->association<->default(=false) values So a unique index on "(company,association) where default" doesn't do what you want? No it doesn't. For example, after I create the unique index I can still input: company10 association7 true company10 association7 true company10 association7 true I want to prevent this from happening, but still allow multiple company10 association7 false company10 association7 false entries for example. The idea of using NULLs is a good idea, but this is a production database and would require changes to the web-based front end (7 of them), not to mention each database. That's why I want to change the behaviour to only allow one unique company<-->association<-->TRUE combination. Right now there are a number of companies which have multiple default associations in the database, so I am going to have to back-trace and find out which association is actally the correct default. BTW I am using postgresql 7.3.4 Ron ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Constraint Problem
You probably didn't quite understand the suggestion. You should create a unique index like: create unique index your_index_name on your_table (companyID, associationID) where ysnDefault; This will restrict the uniqueness checks to the records where ysnDefault is true (and not null, of course). HTH, Csaba. On Tue, 2003-11-04 at 17:39, Ron St-Pierre wrote: > Greg Stark wrote: > > >Ron St-Pierre <[EMAIL PROTECTED]> writes: > > > > > > > >>This is not quite what I need. I need to create a constraint to allow only > >>-one- of > >>company<->association<->default(=true) value > >>but any number of > >>company<->association<->default(=false) values > >> > >> > > > >So a unique index on "(company,association) where default" doesn't do what you > >want? > > > > > > > > > > > No it doesn't. For example, after I create the unique index I can still > input: > company10 association7 true > company10 association7 true > company10 association7 true > I want to prevent this from happening, but still allow multiple > company10 association7 false > company10 association7 false > entries for example. > > The idea of using NULLs is a good idea, but this is a production > database and would require changes to the web-based front end (7 of > them), not to mention each database. That's why I want to change the > behaviour to only allow one unique company<-->association<-->TRUE > combination. Right now there are a number of companies which have > multiple default associations in the database, so I am going to have to > back-trace and find out which association is actally the correct default. > > BTW I am using postgresql 7.3.4 > > Ron > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 3: 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
