Re: [SQL] Alternative to Select in table check constraint
Richard Broersma Jr wrote: But now that I think about it, using the authors suggestion (if it actually worked), how would would it be possible to change the active status from one badge to another? Well, the check constraint as you wrote it requires that there always be a badge listed as active for each employee. If you changed the contstraint to: CHECK ( 2 > .) then you'd be able to unset the active status and then set a new one to active. -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Constraint UNIQUE on a column not case sensitive
Hi,
I would like to find an efficient solution for adding/implementing a constraint
UNIQUE on a VARCHAR column not case sensitive:
ALTER TABLE MyTable
ADD CONSTRAINT UNQ_MyTable_MyColumn
UNIQUE (lower(MyColumn)); -- invalid syntax
The idea is to have an index on that column, in a not case sensitive form, i.e.
lower(MyColumn).
SELECT *
FROM MyTable
WHERE lower(MyColumn) = lower('...');
I don't know how to add such a constraint on MyTable except by defining a
trigger on INSERT clause and checking whether lower(:NEW.MyColumn) has been
already inserted in MyTable.
Is there better and more efficient way to do that?
Regards,
--
Daniel
---(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: [SQL] Constraint UNIQUE on a column not case sensitive
On Jul 1, 2006, at 22:47 , Daniel CAUNE wrote: The idea is to have an index on that column, in a not case sensitive form, i.e. lower(MyColumn). I think you're really close. Try CREATE UNIQUE INDEX ci_mycolumn_idx ON mytable(lower(mycolumn)); Does that do what you're looking for? Michael Glaesemann grzm seespotcode net ---(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 UNIQUE on a column not case sensitive
Daniel CAUNE <[EMAIL PROTECTED]> writes: > Hi, > > I would like to find an efficient solution for adding/implementing a > constraint UNIQUE on a VARCHAR column not case sensitive: > > ALTER TABLE MyTable > ADD CONSTRAINT UNQ_MyTable_MyColumn > UNIQUE (lower(MyColumn)); -- invalid syntax > > The idea is to have an index on that column, in a not case sensitive form, > i.e. lower(MyColumn). What's the problem with CREATE INDEX then? CREATE INDEX idx_something ON mytable (lower(mycolumn)); -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Constraint UNIQUE on a column not case sensitive
> -Message d'origine- > De : Michael Glaesemann [mailto:[EMAIL PROTECTED] > Envoyé : samedi 1 juillet 2006 10:01 > À : Daniel CAUNE > Cc : pgsql-sql@postgresql.org > Objet : Re: [SQL] Constraint UNIQUE on a column not case sensitive > > > On Jul 1, 2006, at 22:47 , Daniel CAUNE wrote: > > > The idea is to have an index on that column, in a not case > > sensitive form, i.e. lower(MyColumn). > > I think you're really close. Try > > CREATE UNIQUE INDEX ci_mycolumn_idx ON mytable(lower(mycolumn)); > > Does that do what you're looking for? > > Michael Glaesemann > grzm seespotcode net > > Yes, indeed! I'm stupid! I was searching a constraint form while the creation of an UNIQUE index makes the job too! The following form is not valid: ALTER TABLE MyTable ADD CONSTRAINT UNQ_MyTable_MyColumn UNIQUE (lower(MyColumn)); But your form makes the expected job: CREATE UNIQUE INDEX IDX_MyTable_MyColumn ON MyTable (lower(MyColumn)); Thanks, -- Daniel CAUNE ---(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] Alternative to Select in table check constraint
On Fri, 2006-06-30 at 18:41 -0700, Richard Broersma Jr wrote: > > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > > FROM BADGES > > > WHERE STATUS = 'A' > > > GROUP BY EMPNO)) > > > > From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/ > > interactive/sql-createtable.html) > > > > CREATE UNIQUE INDEX one_a_badge_per_employee_idx > > ON badges (empno) > > WHERE status = 'A'; > > http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html > > http://www.postgresql.org/docs/8.1/interactive/indexes-partial.html > > Michael, > > Partial indexs seem to be "what the doctor ordered!" And your suggest is > right on, the idea of > the constraint is to allow only one active badge status at a time. > > But now that I think about it, using the authors suggestion (if it actually > worked), how would > would it be possible to change the active status from one badge to another? Unset the status first then set on the new one. Same transaction of course. You may find this type of constraint is more workable with a TRIGGER deferred until commit time than a unique constraint which cannot (at this time in PostgreSQL) be deferred. -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Alternative to Select in table check constraint
This is more of an implementation option, but when I worry about what is active/inactive I put start/end dates on the tables. Then you don't need active indicators. You just look for the record where now() is >= start date and now() <= end date or end date is null. You can even activate/deactivate a badge on a future date. Of course, this complicates the data integrity - you will need some kind of specialized trigger that checks the data and makes sure there are no date overlaps to ensure you don't have two badges active at the same time. But is also gives you a history of badges and their activities. -AaronOn 6/30/06, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS)> > FROM BADGES> > WHERE STATUS = 'A'> > GROUP BY EMPNO)) >> From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/> interactive/sql-createtable.html)>> CREATE UNIQUE INDEX one_a_badge_per_employee_idx > ON badges (empno)> WHERE status = 'A';> http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html> http://www.postgresql.org/docs/8.1/interactive/indexes-partial.htmlMichael,Partial indexs seem to be "what the doctor ordered!" And your suggest is right on, the idea of the constraint is to allow only one active badge status at a time.But now that I think about it, using the authors suggestion (if it actually worked), how wouldwould it be possible to change the active status from one badge to another? Oh well, partial index are obvious the superior solution since the entire table doesn't not haveto be scanned to determine if the new badge can be set to active.Once again thanks for the insight. Regards,Richard Broersma Jr.
[SQL] Left join?
Hi, In the following table, codsol, codate and codfec are foreign keys referencing table func and I need some help to codify a SELECT command that produces the following result set but instead of codsol, codate and codfec I need the respectives names (column nome from table func). postgres=# select * from reqtran; codreq | codsol | codate | codfec +++ 1 ||| 2 | 1 || 3 | 1 | 1 | 4 | 1 | 1 | 1 (4 rows) Thanks in advance, Carlos __ Table definitions: postgres=# \d func Table "public.func" Column | Type | Modifiers +-+--- codfun | integer | not null nome | text| Indexes: "func_pkey" PRIMARY KEY, btree (codfun) postgres=# \d reqtran Table "public.reqtran" Column | Type | Modifiers +-+--- codreq | integer | not null codsol | integer | codate | integer | codfec | integer | Indexes: "reqtran_pkey" PRIMARY KEY, btree (codreq) Foreign-key constraints: "reqtran_codate_fkey" FOREIGN KEY (codate) REFERENCES func(codfun) "reqtran_codfec_fkey" FOREIGN KEY (codfec) REFERENCES func(codfun) "reqtran_codsol_fkey" FOREIGN KEY (codsol) REFERENCES func(codfun) __ Table contents: postgres=# select * from func; codfun | nome +--- 1 | nome1 2 | nome2 3 | nome3 (3 rows) postgres=# select * from reqtran; codreq | codsol | codate | codfec +++ 1 ||| 2 | 1 || 3 | 1 | 1 | 4 | 1 | 1 | 1 (4 rows) ---(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: [SQL] Left join?
> In the following table, codsol, codate and codfec are foreign keys > referencing table func and I need some help to codify a SELECT command that > produces the following result set but instead of codsol, codate and codfec I > need the respectives names (column nome from table func). > > postgres=# select * from reqtran; > codreq | codsol | codate | codfec > +++ > 1 ||| > 2 | 1 || > 3 | 1 | 1 | > 4 | 1 | 1 | 1 > postgres=# \d func > Table "public.func" > Column | Type | Modifiers > +-+--- > codfun | integer | not null > nome | text| > Indexes: > "reqtran_codate_fkey" FOREIGN KEY (codate) REFERENCES func(codfun) > "reqtran_codfec_fkey" FOREIGN KEY (codfec) REFERENCES func(codfun) > "reqtran_codsol_fkey" FOREIGN KEY (codsol) REFERENCES func(codfun) Would this do what you need? select R1.codreq, CS.nome, CD.nome, CF.nome from rectran as R1 left join func as CS on (R1.codsol=CS.codefun) left join func as CD on (R1.codate=CD.codefun) left join func as CF on (R1.codfec=CF.codefun) ; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
