[SQL] uniqueness constraint with NULLs
I have a table with a uniqueness constraint on three columns:
# \d bobtest
Table "public.bobtest"
Column | Type | Modifiers
+-+--
id | integer | not null default nextval('bobtest_id_seq'::regclass)
a | integer |
b | integer |
c | integer |
Indexes:
"bobtest_id_key" UNIQUE, btree (id)
"bobtest_unique" UNIQUE, btree (a, b, c)
I can insert multiple rows with identical a and b when c is NULL:
...
# insert into bobtest (a, b) values (1, 4);
INSERT 0 1
# insert into bobtest (a, b, c) values (1, 4, NULL);
INSERT 0 1
# select * from bobtest;
id | a | b | c
+---+---+---
1 | 1 | 2 | 1
2 | 1 | 3 | 1
3 | 1 | 4 | 1
4 | 1 | 4 |
5 | 1 | 4 |
6 | 1 | 4 |
(6 rows)
Can anyone suggest a way that I can impose uniqueness on a and b when
c is NULL?
In the real app., c is a date field and I require it to be NULL for
some rows. In these cases, I only want at most one row with identical
a and b, but I can have identical a and b when c is a real date as long
as that date is also unique for a given a and b.
I'm guessing I'm going to need to use a function and that someone will
yell at me for using NULLs to represent real data, but I thought I'd be
brave and ask anyway, in case I am missing some other solution that
doesn't involve the use of triggers etc.
Cheers,
Bob Edwards.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] uniqueness constraint with NULLs
A. Kretschmer wrote: In response to Robert Edwards : Can anyone suggest a way that I can impose uniqueness on a and b when c is NULL? Sure, use a functional index: test=# create table bobtest (a int, b int, c int); CREATE TABLE test=*# create unique index idx_bobtest on bobtest(a,b,coalesce(c::text,'NULL')); CREATE INDEX test=*# insert into bobtest (a, b) values (1, 4); INSERT 0 1 test=*# insert into bobtest (a, b, c) values (1, 4, NULL); ERROR: duplicate key value violates unique constraint "idx_bobtest" test=!# Regards, Andreas Beautiful! Many thanks, Bob Edwards. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] uniqueness constraint with NULLs
Thanks for all these great ideas! Craig Ringer wrote: On Mon, 2009-06-29 at 15:42 +1000, Robert Edwards wrote: Can anyone suggest a way that I can impose uniqueness on a and b when c is NULL? One way is to add an additional partial index on (a,b): CREATE INDEX bobtest_ab_unique ON bobtest(a,b) WHERE (c IS NULL); Would this be in addition to a unique constraint on (a, b, c) (for the cases where c is not null)? ... however, if you want to do the same sort of thing for all permutations (a, null, null), (b, null, null), (c, null, null), (a, b, null), (a, c, null), (b, c, null), (a, b, c)that'll be a LOT of indexes. In the real app. a and b are not null ints and c is a date. The date indicates if and when a row has expired (there are other columns in the table). I am trying to avoid having separate columns for the "if" and the "when" of the expiry. One alternate would be to use a date way off into the future (such as the famous 9/9/99 case many COBOL programmers used back in the 60's...) and to test on expired < now (). Another option is to use a separate shadow table for the expired rows and to use a trigger function to "move" expired rows to that shadow table. Then need to use UNION etc. when I need to search across both current and expired rows. In that case you might be better off just using a trigger function like (untested but should be about right): CREATE OR REPLACE FUNCTION bobtest_unique_trigger() RETURNS trigger AS $$ declare conflicting_id integer; begin if TG_OP = 'INSERT' or TG_OP = 'UPDATE' then select into conflicting_id from bobtest where (NOT new.a IS DISTINCT FROM a) and (NOT new.b IS DISTINCT FROM b) and (NOT new.c IS DISTINCT FROM c); if found then raise exception 'Unique violation in bobest: inserted row conflicts with row id=%',conflicting_id; end if; end if; end; $$ LANGUAGE 'plpgsql'; ... which enforces uniqueness considering nulls. I am "guessing" that the "functional index" that Andreas Kretschmer proposed would be a lot "lighter-weight" than a full trigger. This table will get quite a bit of insert activity and some update activity on the "c" (expired) column, so this uniqueness index will get exercised quite a lot. I am concerned that this could cause performance issues with a heavier-weight trigger function (but have no empirical data to back up these concerns...). In the real app., c is a date field and I require it to be NULL for some rows. Oh. Er, In that case, the partial unique index is your best bet (but 'a' and 'b' should ne NOT NULL, right). Right - see above. in case I am missing some other solution that doesn't involve the use of triggers etc. Sometimes a trigger is the right solution. Yep - I have many of those in other places as well. Cheers, Bob Edwards. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
On 14/06/12 18:39, Achilleas Mantzios wrote:
dynacom=# SELECT id from items_tmp WHERE id=1261319 AND
xid=currval('xadmin_xid_seq');
id
(0 rows)
dynacom=# -- THIS IS INSANE
Have you tried:
SELECT id from items_tmp WHERE id=1261319 AND
xid=currval('xadmin_xid_seq'::text)
or even:
SELECT id from items_tmp WHERE id=1261319 AND
xid=currval(('xadmin_xid_seq'::text)::regclass)
Bob Edwards.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] on connect/on disconnect
(this is my first post to this list...) I am wondering if Postgres, and/or SQL in general, has a facility to run a function at connection set-up time (after a successful connection attempt) and/or at session completion (or disconnect)? I want to pre-populate a table (actually an INSERT rule on a view) with some user-specific data that is unlikely to change during the session and which is "difficult" to process (ie. affects performance to do it too often). Clearly, I can do this manually anyway as the first operation after a connection is established, but I would like also to clear it out when the session terminates (so, I guess I am really interested in a "trigger" of some sort on end-of-session). Purely session/connection-based temporary tables would also do what I need, but temporary tables don't seem to be able to work that way. Cheers, Bob Edwards. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] on connect/on disconnect
Hi Aaron, Thanks for your response. I guess I wasn't clear about "users". I am referring to database users, not application users (although they are the same in my application in any case - that is, each application user is known to the database as a different database user). As I understand it, connection pooling, as used by a web app, still needs to connect "per-database-user" (and usually this is the same "web-server" user). If the web app is connecting to the database server as different database users, then different connections would be set up. If the number of open connections exceeds the number allowed to the database server, then older unused connections would be terminated to allow new ones to be created. Is this correct? Or is it possible, over the same connection, to change the database user? My understanding of the frontend/backend protocol is that this is not allowed. Anyway, I still need to know if running functions during connection setup and tear-down, or change of user, is possible or not. Cheers, Bob Edwards. Aaron Bono wrote: On 9/10/06, *Robert Edwards* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> wrote: (this is my first post to this list...) I am wondering if Postgres, and/or SQL in general, has a facility to run a function at connection set-up time (after a successful connection attempt) and/or at session completion (or disconnect)? I want to pre-populate a table (actually an INSERT rule on a view) with some user-specific data that is unlikely to change during the session and which is "difficult" to process (ie. affects performance to do it too often). Clearly, I can do this manually anyway as the first operation after a connection is established, but I would like also to clear it out when the session terminates (so, I guess I am really interested in a "trigger" of some sort on end-of-session). Purely session/connection-based temporary tables would also do what I need, but temporary tables don't seem to be able to work that way. What kind of operation are you wanting to do? Would it work if an application like a web site used connection pooling - thus sharing the session across application users and rarely if ever connecting/disconnecting? == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com == ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] on connect/on disconnect
Markus Schaber wrote: Hi, Robert, Robert Edwards wrote: (this is my first post to this list...) Welcome here. :-) I am wondering if Postgres, and/or SQL in general, has a facility to run a function at connection set-up time (after a successful connection attempt) and/or at session completion (or disconnect)? Most JDBC connection pooling implementations will do that, at least for connection and session setup. (JBoss is one of them.) I want to pre-populate a table (actually an INSERT rule on a view) with some user-specific data that is unlikely to change during the session and which is "difficult" to process (ie. affects performance to do it too often). The problem here is that the INSERT rule might be globally visible to other, concurrent users on the database. Indeed it is, but the sole reason to use a rule (instead of a straight INSERT) is that it qualifies the INSERT against the current user. Could you explain what exactly you want to achieve, may be we find a better way to do the whole thing. Basically, I have a heirarchical arrangement of users in "roles" (almost the same as the 8.1 user/group/role mechanism, but in "PUBLIC" schema space, and with various triggers etc. in play). The access controls apply conditions based on which "roles" (groups) the current user is a member of (and these users never have "super-user" privilege, so the SET SESSION AUTHORIZATION mechanism does not apply here). The heirarchy is not a "tree" structure - any role can belong to any number of other roles (have many parents), so it is a Digraph (directed graph). I have some plpgsql functions, one of which is used to determine which roles a user is in, but it is necessarily recursively called, which means it runs in non-deterministic time. (Just for completeness, I'll include that function here: create or replace function get_anc () returns setof member as ' declare rMem member; begin for rMem in select * from member where child = $1 loop return next rMem; for rMem in select * from get_anc (rMem.parent) loop return next rMem; end loop; end loop; return; end; ' language plpgsql; my intention is to re-implement this in C once I get some other logic sorted out - if anyone can see a "better" way, please let me know!) So, to cut to the short of it, I want to call this function at connection set up and "cache" the results into a "system" table that the user can't insert (or update), using an insert rule on a view: SELECT DISTINCT parent FROM get_anc (mypid); Using a non-temporary table means I can use indexes etc. properly and do O(1) lookups to quickly determine if the user has the access they need for other SQL trigger functions and rules to use. What I really need is to be able to automatically clear the users entries back out of the table when they disconnect, just in case. Looks like there is no "ON DISCONNECT" style trigger capability, so I might have to look at implementing something there as well. Purely session/connection-based temporary tables would also do what I need, but temporary tables don't seem to be able to work that way. What's the exact problem with them? Sorry - I got the "sense" of that statement the wrong way around. Temporary tables do work fine, but I need to control inserts and deletes (using views and rules) to prevent someone from giving themselves access to stuff they shouldn't. I don't think I can create a temporary table as a different user, or maybe I can with a "setuid" function? Cheers, Bob Edwards. Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] hi i am gettin error when i am deleting a function from
Can you give us the actual delete command you are issuing? You need to specify the types of the function arguments when deleting functions, for example: DELETE FUNCTION my_sum (int, int); etc. Cheers, Bob Edwards. Penchalaiah P. wrote: Hi good morning to all…. I created some functions in my pgadmin… when I am deleting those functions from that pgadmin its giving error…i.e An ERROR has occurred ERROR:function function_name1(character varying, character varying, character varying, date, character varying) does not exist… May I know the reason y its not deleting…. *Thanks & Regards* *Penchal reddy **|** Software Engineer * *Infinite Computer Solutions **|** Exciting Times…Infinite Possibilities... * *SEI-CMMI level 5 **| **ISO 9001:2000* *IT SERVICES **|** BPO * *Telecom **|** **Finance **|** **Healthcare **| **Manufacturing **|** **Energy & Utilities **|** **Retail & Distribution **|** **Government* *Tel +91-80-5193-(Ext:503)**|** Fax +91-80-51930009 **|** Cell No +91-9980012376**|**www.infics.com** * *Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at** [EMAIL PROTECTED] _**and delete this mail from your records.* Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] query to select a linked list
Hi Louis-David,
I also have written a forum application using PostgreSQL.
My schema has a "threadid" for each posting, which is actually also the
"messageid" of the first posting in the thread, but that is irrelevant.
I can then just select all messages belonging to that thread. The actual
hierarchy of messages (which posting is in response to which) is dealt
with by a "parentid", identifying the messageid of the post being
responded to. Sorting that out is done by the middleware (PHP in this
case) - the SQL query simply returns all messages in the thread in a
single query. Because our database is somewhat busy, I have opted to
keep the queries to the database simple and let the middleware sort
out the heirarchical structure (which it is quite good at).
I hope this helps.
Bob Edwards.
Louis-David Mitterrand wrote:
Hi,
To build a threaded forum application I came up the following schema:
forum
--
id_forum | integer| not null default nextval('forum_id_forum_seq'::regclass)
id_parent| integer|
subject | text | not null
message | text |
Each message a unique id_forum and an id_parent pointing to the replied
post (empty if first post).
How can I build an elegant query to select all messages in a thread?
Thanks,
---(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 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] accounting schema
Medi Montaseri wrote: Hi, I am learning my way into Accounting and was wondering how Accounting applications are designed. perhaps you could point the way On one hand, accountants talk about a sacret equation A = L + OE (Asset = Libility + Owner Equity) and then under each categories there are one or many account. On the other hand a DBA thinks in terms of tables and relations. Instead of getting theoritical, allow me to setup an example Say you have have construction project (like a room addition) or one of those flip this house deals Owner brings the land (equity) of say worth $100K Expenses begin to mount ( that is a minus against OE) Account Payble begins to mount (that is a liability) And one day you experience a sale As a DBA, (and keeping it simple) I am thinking I need a table for every account which migh look like id, description, credit, debit, validated, created_on, created_by, modified_on, modified_by Is that pretty match it ? Please let me know if you have seen some accounting or DB book that addresses this problem domain. Thanks Medi My home-grown system uses three key tables: account, transaction and split. The split joins a transaction to an account and an amount. All the splits for a single transaction must sum to zero (checked by a PL/pgSQL function triggered on insert, update and delete on the split table). For example, my pay is a single transaction with typically 8 splits reflecting what my pay-master does with my pay (tax, superannuation, health contrib, etc.) I also have other tables for managing reconciliations - each split has a reconciliation ID that indicates if/when that split was reconciled. Once reconciled, the split becomes, effectively, immutable (by the same PL/pgSQL function). Transactions contain date, description, who etc. (all from the top of my head - I should check what I really did many years ago). Most of the rest of it is then just mapping the accounts to the various ledgers and bank accounts etc. This model is very simple, for very simple people like me. Cheers, Bob Edwards. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] May I have an assistance on CREATE TABLE Command
You could: INSERT INTO REGION VALUES (33, 'New Dar'); UPDATE DISTRICT SET region_id = 33 WHERE region_id = 99; DELETE FROM REGION WHERE region_id = 99; UPDATE REGION SET region_name = 'Dar es Salaam' WHERE region_id = 33; Of course, if there is no uniqueness constraint on region_name then you can just put the final region_name in the INSERT and you won't need to do the final UPDATE. This won't break any Foreign Keys. (been to Dodoma and Dar, but not Tabora - yet). Cheers, Bob Edwards. James Kitambara wrote: Thank you ! But I think that there is a solution. If it happens that you have the following data in your tables REGION -- region_id | region_name -- 11| Dodoma 22| Tabora 99| Dar es Salaam THIS ROW WAS SUPPOSED TO BE: '33', 'Dar es Salaam' DISTRICT dist_id | dist_name |region_id 001 | Kongwa |11 002 | Ilala|99 003 | Temeke |99 003 | Kinondoni |99 For this UPDATE I wanted, when I change the region _id from '99' to '33' of the last ROW in REGION table AUTOMATICALLY to change the last three ROWS of the DISTRICT table which reference to '99', 'Dar es Salaam'. If I do this, I will get the error message "You can not change region_id other tables are reference to it. HOW CAN DO THIS? (AUTOMATIC UPDATE OF FOREIGN KEY) ---ORGINAL MESSAGE--- I think (one of) the point(s) of id fields is not to change them. You can update the region_name field (eg a correct a misspelling), but the id stays the same. That way the district stays connected to the same region. >>> James Kitambara <[EMAIL PROTECTED]> 2008-09-17 8:50 >>> Hello Mambers of PGSQL-SQL, I have two tables namely: REGION (region_id, region_name) DISTRICT (dist_id, dist_name, region_id (FK)) I would like to have the CREATE TABLE Command which will create these tables in such a way that when REGION table is UPDATED automatical the FOREGN KEY in DISTRICT table is also updated. I will appriciate for your assistance ! Regards James Kitambara -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with pg_connect() in PHP
Looks like you are missing the php_pgsql extension (I assume you
are running on a Windows server). On Debian GNU/Linux, it is called
php5-pgsql (or php4-pgsql, as appropriate). Not sure what it would
be called for Windows, but something similar.
Simply enabling it (by uncommenting the line in your php.ini file)
is not sufficient - the actual library needs to be installed as well.
Cheers,
Bob Edwards.
James Kitambara wrote:
Dear Members of
I have installed the Apache 2.0.61, PHP 5.2.4 and PostgreSQL 8.1 on my
local computer.
All three software were successfully tested. I changed
“/;extension=php_pgsql.dll”/ to
/“extension=php_pgsql.dll”/in the php.ini file in order to enable
PostgreSQL in PHP.
The problem comes when I try to connect to the PostgreSQL Database using
php function pg_connect
$dbconn = pg_connect("host=".PG_HOST_NAME." port=".PG_PORT_NUM."
dbname=".PG_DB_NAME." user=".PG_USER." password=".PG_PASSWORD);
All the arguments in the function pg_connect() are defined.
Unfortunately I am getting the Fatal error: “/Call to undefined function
pg_connect() in C:\Web\html\Staff_Management\example1.php on line 23/”
C:\Web\html is my document root.
What could be the possible mistake?
Anyone to assist me!
Best regards,
James Kitambara
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
