[SQL] Unique constraint over null values
Hi all, I have a table like CREATE TABLE a ( typeCHAR (1) NOT NULL, data1 CHAR (16) NOT NULL, data2 CHAR (16) NULL ); where type can be 's' for 'single' and 'x' for extended, so s should mean that there is only the type and data1 field, and x means that all fields are set. How can I set a unique constraint like CHECK (type = 's' AND UNIQUE (type, data1)) OR (type = 'x' AND UNIQUE (type, data1, data2)) ? The documentation says that 2 NULL values are different, so no unique constraint can be set over it. Thanks in advance, Thrasher ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Unique constraint over null values
Hi Josh, I think I'll follow your advice, as this is the method that I had in my pocket to use if I had no response. I tried with TRIM and other functions in the CHECK constraint, but I guess that I cannot use functions over a check field. It surprised me, but anyway, I'll do that. Thanks a lot for your prompt reply Thrasher Josh Berkus wrote: > Thrasher, > > >>where type can be 's' for 'single' and 'x' for extended, so s should >>mean that there is only the type and data1 field, and x means that all >>fields are set. >> >>How can I set a unique constraint like CHECK (type = 's' AND UNIQUE >>(type, data1)) OR (type = 'x' AND UNIQUE (type, data1, data2)) ? >> >>The documentation says that 2 NULL values are different, so no unique >>constraint can be set over it. > > > Simple. Don't use NULLs. NULL means "unknown". You should use a > differnent value, such as a blank string or "N/A" or "000" to > represent "intentionally left blank". > > Then you can set data2 NOT NULL and use a regular UNIQUE constraint. > > BTW, you have defined your data columns as CHAR, not VARCHAR. You do know > that this implies that your data columns will be *exactly* 16 characters in > length? > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Updating from select
Hi all I have two related tables: CREATE TABLE trans_log ( id serial, datetimestamp not null, costnumeric(12,5) NULL ); CREATE TABLE products_log ( id serial, trans integer not null references trans_log(id), costnumeric(12,5) NOT NULL ); So, a transaction can have from zero to a lot of products (1:N) Ok. I have data in the tables. The transaction tables have their id and date, but no costs, so costs = 0.0 for all transactions. The products do have their cost set. I can do a SELECT trans, SUM(cost) FROM products_log GROUP BY trans; to get the total amount of each transactions. What I would like is to update the transactions table with the sum of its products cost, some kind of UPDATE trans_log t SET t.cost = (SELECT SUM(p.cost) FROM products_log p WHERE p.trans = t.id) But I'm not able. Thanks in advance Thrasher ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Updating from select
Hi Manfred, Such a silly thing blocked me, and I was about to write a stored procedure. Thanks a lot !! BTW, does anyone knows about a RH7.2 or newer RPM of v7.3? Do I need any other tools not included in RH7.2 to compile it? Thrasher Manfred Koizar wrote: > On Wed, 02 Oct 2002 19:11:19 +0200, Thrasher <[EMAIL PROTECTED]> > wrote: > >>UPDATE trans_log t SET t.cost = >> (SELECT SUM(p.cost) FROM products_log p WHERE p.trans = t.id) > > > Thrasher, try it without the table alias t: > > UPDATE trans_log SET cost = >(SELECT SUM(p.cost) FROM products_log p > WHERE p.trans = trans_log.id) > > Servus > Manfred > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] NOTICE: generated by sequence nextval()
Hi
Just to explain you that I received this email and I am not an intended
or authorized recipient. So, as you ask, I'm notifying you and deleting
the mail from my computer.
Thank you
Michael Ansley (UK) wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I would hope that this is normal, and I can see it being particularly
useful. We frequently run into problems where databases ahve been
reloaded, and the sequences have not been reset. And the DBAs are
not going to take notice unless it's shoved in their face. It's only
a NOTICE, so it shouldn't affect anything.
MikeA
- -Original Message-
From: John Duffy [mailto:jbduffy@;ntlworld.com]
Sent: 10 October 2002 16:18
To: [EMAIL PROTECTED]
Subject: [SQL] NOTICE: generated by sequence nextval()
Postgresql 7.1.3-2
Red Hat 7.2
I've noticed that if I create a sequence, and then do a select on it
using nextval() everything works fine. However, if I drop the
sequence and then create it again, the same select statement
generates a NOTICE.
Is this normal behaviour or a bug? See below.
test=> create sequence serial;
CREATE
test=> select nextval('serial');
nextval
- -
1
(1 row)
test=> drop sequence serial;
DROP
test=> create sequence serial;
CREATE
test=> select nextval('serial');
NOTICE: serial.nextval: sequence was re-created
nextval
- -
1
(1 row)
-BEGIN PGP SIGNATURE-
Version: PGPfreeware 7.0.3 for non-commercial use
iQA/AwUBPaaUlnympNV/C086EQJNgwCg1TmU/dsMr7ul14koMB5l88ecUcQAoNP6
jH1lHILqYCRzfPOTQ9+lq0BA
=DWs5
-END PGP SIGNATURE-
|
This e-mail and any attachments are confidential and may also be
privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If
you are not an
intended or authorised recipient of this e-mail or have received it in
error, please delete
it immediately and notify the sender by e-mail. In such a case, reading,
reproducing,
printing or further dissemination of this e-mail is strictly prohibited
and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an
attachment hereto is free
from computer viruses or other defects. The opinions expressed in this
e-mail and any
attachments may be those of the author and are not necessarily those of
Intec Telecom
Systems PLC.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
|
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Date trunc in UTC
Hi
I do not know if it's an error, but in this query
=# select date_trunc ('month', now ());
date_trunc
2002-11-01 00:00:00+01
(1 row)
I've got the truncated date dependant to my timezone.
Instead, I would like to have as a result
2002-11-01 01:00:00+01
which is correct, but I cannot set the whole server to UTC. Any way to
get this ?
Thanks
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Date trunc in UTC
No I cannot use SET TIME ZONE.
SET TIME ZONE will be set by any client backend. But what I want to get
is that DATE_TRUNC('month', ) = DATE_TRUNC('month',
).
Richard Huxton wrote:
On Wednesday 20 Nov 2002 9:44 am, Thrasher wrote:
Hi
I do not know if it's an error, but in this query
=# select date_trunc ('month', now ());
date_trunc
2002-11-01 00:00:00+01
(1 row)
I've got the truncated date dependant to my timezone.
Instead, I would like to have as a result
2002-11-01 01:00:00+01
which is correct, but I cannot set the whole server to UTC. Any way to
get this ?
Perhaps SET TIME ZONE is what you want. See the manual section on date/time
types for details.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Date trunc in UTC
The biggest point that I see is that it would be nice to have some kind
of function that works with UTC values, regarding of which timezone the
user has set. Let's say, something like
SELECT UTC_DATE_TRUNC ('month', NOW ());
utc_date_trunc
2002-11-01 01:00:00+01
because if you work with international applications, the beggining of
the month in Spain should be the same as in Australia. But everyone will
see it in its own timezone.
I think that it would be also interesting to have the UTCeed versions of
EXTRACT and AGE.
Tom Lane wrote:
Richard Huxton <[EMAIL PROTECTED]> writes:
Hmm - good point. You can revert to the client default but not to the
previous value. I don't know of any way to read these SET values
either - a quick poke through pg_proc didn't show anything likely.
In 7.3 you can use current_setting() and set_config() to access SHOW/SET
functionality. However, I agree with your suggestion of AT TIME ZONE
to rotate a timestamp into a target timezone, rather than mucking with
the TimeZone setting.
BTW, Thomas: is AT TIME ZONE supposed to accept
timestamp-without-timezone input? If so, what's it supposed to do with
it? The current behavior seems unintuitive to say the least:
regression=# select now();
now
---
2002-11-21 10:19:14.591001-05
(1 row)
regression=# select now() at time zone 'UTC';
timezone
2002-11-21 15:19:18.588279
(1 row)
regression=# select localtimestamp;
timestamp
2002-11-21 10:19:22.629865
(1 row)
regression=# select localtimestamp at time zone 'UTC';
timezone
---
2002-11-21 05:19:26.178861-05
(1 row)
It seems to me that the last case should give either an error or
2002-11-21 15:19:26.178861 (ie, assume that the timestamp without time
zone is in my TimeZone zone). In any case, surely the result should
be of type timestamp WITHOUT time zone?
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Date trunc in UTC
Hi all
Finally, I am using a plpgsql procedure that accomplish that in
PostgreSQL 7.2.1. The code follows:
CREATE OR REPLACE FUNCTION utc_date_trunc (TEXT, TIMESTAMP) RETURNS
TIMESTAMP AS '
DECLARE
utcts TIMESTAMP WITHOUT TIME ZONE;
utcdt TIMESTAMP WITHOUT TIME ZONE;
BEGIN
--> First get the indicated timestamp at UTC <--
utcts := $2 AT TIME ZONE ''UTC'';
--> Get the date trunc <--
utcdt := DATE_TRUNC ($1, utcts);
--> Return the result <--
RETURN (utcdt at time zone ''UTC'');
END
' LANGUAGE 'plpgsql';
I guess that it can be adapted for other time functions with ease, but I
do not need them yet.
Thanks to everybody involved,
Thrasher
Tom Lane wrote:
Thrasher <[EMAIL PROTECTED]> writes:
The biggest point that I see is that it would be nice to have some kind
of function that works with UTC values, regarding of which timezone the
user has set.
You can do that in 7.3, using the AT TIME ZONE construct. Observe:
regression-# begin;
BEGIN
regression=# select now();
now
---
2002-11-22 09:59:48.706508-05 'now' in local time (EST)
(1 row)
regression=# select now() at time zone 'UTC';
timezone
2002-11-22 14:59:48.706508 'now' in UTC
(1 row)
regression=# select date_trunc('month', now() at time zone 'UTC');
date_trunc
-
2002-11-01 00:00:00month start in UTC
(1 row)
regression=# select date_trunc('month', now() at time zone 'UTC') at time zone 'UTC';
timezone
2002-10-31 19:00:00-05month start in local time
(1 row)
regression=# commit;
This is a tad bulky maybe, but it'd be easy to wrap up in a user-defined
function.
AT TIME ZONE was less functional, and very poorly documented, in 7.2.
The 7.3 version is described at
http://developer.postgresql.org/docs/postgres/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Backend message type 0x50 arrived while idle
Hi Phil It also happened to me last week. I found the problem is that you cannot share the same connection among processes unless you implement some kind of locking (semaphores, ...). More clearly, I had this problem. Query QA lasts 5 seconds to perform, and returns answer AA. Query QB lasts 10 seconds and returns AB. B is a child process of A. I launch the parent A. Performs QA. Waits for answer. I launch the child B. Performs QB. Waits for answer. Server returns AA, but it gets caught by B !! So, solutions are: - You open a new backend connection for each process. - You make each process lock before sending QA, and unlock when receiving AA. Hope it helps !! Thrasher Berman, Phil wrote: To all- I am using Postgres version 6.5.1 in a multithreaded program which is written in C. All inserts and updates into any tables are done within the child process, and there are retrieves from tables both in the parent and child processes. Additionally, the child process will insert into and update a table, and the parent process will retrieve from the same table. There are loops in both the parent and child processes, so every postgres call may be made multiple times, with a significant amount of time (seconds or minutes) between calls. Invariably, I get a message "backend message type 0x50 arrived while idle" before the process completes, although the point where I get this message can vary somewhat. Can anyone tell me what this message means, why I am getting it, and/or how to avoid getting it in the future? Thanks in advance, Phil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
