Re: [SQL] How to max() make null as biggest value?
On 04/14/10 08:33, Feixiong Li wrote: Hi , guys , I am newbie for sql, I have a problem when using max() function, I need get null when there are null in the value list, or return the largest value as usual, who can do this? i.e. max([1,2,3,4,5]) => 5 max([1,2,3,4,5,null]) => null if u want a function, not an aggregate then u have the greatest(...) except it does not return null on null input (i was really surprised with this completely perverted behavior (very unusual for postgres), but it is a fact) if u want to cheat u may just coalesce() each input argument then nullif() a result of the greatest() function (if only u have enough space in a reference range to room the one special value instead of null) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] LEFT OUTER JOIN issue
Hi everyone, here is my problem :
I got two tables :
CREATE TABLE "public"."calendar_temp" (
"id" SERIAL,
"dat" DATE,
"heur" TIME WITHOUT TIME ZONE,
CONSTRAINT "calendar_temp_pkey" PRIMARY KEY("id")
) WITHOUT OIDS;
CREATE TABLE "public"."h_part" (
"idh" SERIAL,
"poste_idposte" INTEGER NOT NULL,
"t" NUMERIC(4,1),
"heuremesure" TIME WITHOUT TIME ZONE,
"datmesure" DATE,
CONSTRAINT "h_part_datmesure_key" UNIQUE("datmesure", "heuremesure",
"poste_idposte"),
CONSTRAINT "h_part_pkey" PRIMARY KEY("idh"),
CONSTRAINT "h_part_fk" FOREIGN KEY ("poste_idposte")
REFERENCES "public"."poste"("idposte")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITHOUT OIDS;
Data in table are like this :
calendar_temp
-
iddat heur
1 15/03/2008 0:00
2 15/03/2008 3:00
3 15/03/2008 6:00
4 15/03/2008 9:00
5 15/03/2008 12:00
6 15/03/2008 15:00
h_part
-
idh poste_idposte t heuremesure datmesure
5001 2758,3 0:00
15/03/2008
5002 27512 3:00
15/03/2008
5003 27515 6:00
15/03/2008
5004 27518 9:00
15/03/2008
I expect the following data set as a result from the following request :
SELECT ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t FROM calendar_temp as ct
LEFT OUTER JOIN h_part as hp
ON ct.dat = hp.datmesure
AND ct.heur = hp.heuremesure
WHERE
hp.poste_idposte = 275
ORDER BY ct.dat, ct.heur
dat heur datmesure heuremesure t
---
15/03/2008 0:00 15/03/2008 0:00 8,3
15/03/2008 3:00 15/03/2008 3:00 12
15/03/2008 6:00 15/03/2008 6:00 15
15/03/2008 9:00 15/03/2008 9:00 18
15/03/2008 12:00 nullnull
null
15/03/2008 15:00 nullnull
null
But unfortunatly all that I get is this set :
dat heur datmesure heuremesure t
---
15/03/2008 0:00 15/03/2008 0:00 8,3
15/03/2008 3:00 15/03/2008 3:00 12
15/03/2008 6:00 15/03/2008 6:00 15
15/03/2008 9:00 15/03/2008 9:00 18
Getting mad with it...
Thanks for any help...
Re: [SQL] LEFT OUTER JOIN issue
Hi, Thomas.
I believe it is because of your WHERE clause, which is filtering out the nulls
from hp table.
According to
WHERE
hp.poste_idposte = 275
You only want registers that have hp.poste_idposte = 275, not the null ones.
HTH
Best,
Oliveiros
- Original Message -
From: Thomas BOURIMECH
To: 'pgsql-sql@postgresql.org'
Sent: Wednesday, April 21, 2010 1:29 PM
Subject: [SQL] LEFT OUTER JOIN issue
Hi everyone, here is my problem :
I got two tables :
CREATE TABLE "public"."calendar_temp" (
"id" SERIAL,
"dat" DATE,
"heur" TIME WITHOUT TIME ZONE,
CONSTRAINT "calendar_temp_pkey" PRIMARY KEY("id")
) WITHOUT OIDS;
CREATE TABLE "public"."h_part" (
"idh" SERIAL,
"poste_idposte" INTEGER NOT NULL,
"t" NUMERIC(4,1),
"heuremesure" TIME WITHOUT TIME ZONE,
"datmesure" DATE,
CONSTRAINT "h_part_datmesure_key" UNIQUE("datmesure", "heuremesure",
"poste_idposte"),
CONSTRAINT "h_part_pkey" PRIMARY KEY("idh"),
CONSTRAINT "h_part_fk" FOREIGN KEY ("poste_idposte")
REFERENCES "public"."poste"("idposte")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITHOUT OIDS;
Data in table are like this :
calendar_temp
-
iddat heur
1 15/03/2008 0:00
2 15/03/2008 3:00
3 15/03/2008 6:00
4 15/03/2008 9:00
5 15/03/2008 12:00
6 15/03/2008 15:00
h_part
-
idh poste_idposte t heuremesure datmesure
5001 2758,3 0:00
15/03/2008
5002 27512 3:00
15/03/2008
5003 27515 6:00
15/03/2008
5004 27518 9:00
15/03/2008
I expect the following data set as a result from the following request :
SELECT ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t FROM calendar_temp as
ct
LEFT OUTER JOIN h_part as hp
ON ct.dat = hp.datmesure
AND ct.heur = hp.heuremesure
WHERE
hp.poste_idposte = 275
ORDER BY ct.dat, ct.heur
dat heur datmesure heuremesure t
---
15/03/2008 0:00 15/03/2008 0:00
8,3
15/03/2008 3:00 15/03/2008 3:00 12
15/03/2008 6:00 15/03/2008 6:00 15
15/03/2008 9:00 15/03/2008 9:00 18
15/03/2008 12:00 nullnull
null
15/03/2008 15:00 nullnull
null
But unfortunatly all that I get is this set :
dat heur datmesure heuremesure t
---
15/03/2008 0:00 15/03/2008 0:00
8,3
15/03/2008 3:00 15/03/2008 3:00 12
15/03/2008 6:00 15/03/2008 6:00 15
15/03/2008 9:00 15/03/2008 9:00 18
Getting mad with it...
Thanks for any help...
Re: [SQL] Problem with insert related to different schemas
Hi Tom, > >> This is a select query. I don't think that's the right error message. > > > Yes, but IS the correct error message. > > The query being complained of appears to be a generated foreign key > checking query. It's not surprising it would appear in the context > of an insert. Yes, that's what I think too... > > > It appeared just after upgrading > > to 8.4 as 8.3 had no problem processing this query. > > You've either changed the permissions on schema public from what they > were in the old installation, or linked an FK constraint to the wrong > table. I see no reason to think there is either a bug or a version > difference here. Maybe but I ran even grant all on schema public for this user to check if this was the problem. About linking the wrong FK... Everything is posible. I have to take a look to them again and try isolate the problem. But I thought that it was a restriction of the jdbc driver used with postgresql. It looks like when you specify a schema for the query no other schema references are allowed... But I tried directly through the psql client and returned same error so it must be isolated at database level. Anyway, let me provide a test case. Thank you > > regards, tom lane > -- 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 insert related to different schemas
On Wed, Apr 21, 2010 at 7:25 AM, Gonzalo Aguilar Delgado wrote: > Hi Tom, > > > >> >> This is a select query. I don't think that's the right error message. >> >> > Yes, but IS the correct error message. >> >> The query being complained of appears to be a generated foreign key >> checking query. It's not surprising it would appear in the context >> of an insert. > > Yes, that's what I think too... > >> >> > It appeared just after upgrading >> > to 8.4 as 8.3 had no problem processing this query. >> >> You've either changed the permissions on schema public from what they >> were in the old installation, or linked an FK constraint to the wrong >> table. I see no reason to think there is either a bug or a version >> difference here. > > Maybe but I ran even grant all on schema public for this user to check > if this was the problem. That doesn't do what you think it does. You need to grant on the actual object. -- 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] LEFT OUTER JOIN issue
Try moving the hp.poste_idposte=275 inside the LEFT JOIN condition, somethin
like :
SELECT ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t FROM calendar_temp as ct
LEFT OUTER JOIN h_part as hp
ON (ct.dat = hp.datmesure
AND ct.heur = hp.heuremesure
AND hp.poste_idposte = 275)
ORDER BY ct.dat, ct.heur
And drop the WHERE clause.
See if it gives the results you intended.
Best,
Oliveiros
- Original Message -
From: Oliveiros
To: Thomas BOURIMECH ; pgsql-sql@postgresql.org
Sent: Wednesday, April 21, 2010 1:53 PM
Subject: Re: [SQL] LEFT OUTER JOIN issue
Hi, Thomas.
I believe it is because of your WHERE clause, which is filtering out the
nulls from hp table.
According to
WHERE
hp.poste_idposte = 275
You only want registers that have hp.poste_idposte = 275, not the null ones.
HTH
Best,
Oliveiros
- Original Message -
From: Thomas BOURIMECH
To: 'pgsql-sql@postgresql.org'
Sent: Wednesday, April 21, 2010 1:29 PM
Subject: [SQL] LEFT OUTER JOIN issue
Hi everyone, here is my problem :
I got two tables :
CREATE TABLE "public"."calendar_temp" (
"id" SERIAL,
"dat" DATE,
"heur" TIME WITHOUT TIME ZONE,
CONSTRAINT "calendar_temp_pkey" PRIMARY KEY("id")
) WITHOUT OIDS;
CREATE TABLE "public"."h_part" (
"idh" SERIAL,
"poste_idposte" INTEGER NOT NULL,
"t" NUMERIC(4,1),
"heuremesure" TIME WITHOUT TIME ZONE,
"datmesure" DATE,
CONSTRAINT "h_part_datmesure_key" UNIQUE("datmesure", "heuremesure",
"poste_idposte"),
CONSTRAINT "h_part_pkey" PRIMARY KEY("idh"),
CONSTRAINT "h_part_fk" FOREIGN KEY ("poste_idposte")
REFERENCES "public"."poste"("idposte")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITHOUT OIDS;
Data in table are like this :
calendar_temp
-
iddat heur
1 15/03/2008 0:00
2 15/03/2008 3:00
3 15/03/2008 6:00
4 15/03/2008 9:00
5 15/03/2008 12:00
6 15/03/2008 15:00
h_part
-
idh poste_idposte t heuremesure datmesure
5001 2758,3 0:00
15/03/2008
5002 27512 3:00
15/03/2008
5003 27515 6:00
15/03/2008
5004 27518 9:00
15/03/2008
I expect the following data set as a result from the following request :
SELECT ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t FROM calendar_temp
as ct
LEFT OUTER JOIN h_part as hp
ON ct.dat = hp.datmesure
AND ct.heur = hp.heuremesure
WHERE
hp.poste_idposte = 275
ORDER BY ct.dat, ct.heur
dat heur datmesure heuremesure t
---
15/03/2008 0:00 15/03/2008 0:00
8,3
15/03/2008 3:00 15/03/2008 3:00
12
15/03/2008 6:00 15/03/2008 6:00
15
15/03/2008 9:00 15/03/2008 9:00
18
15/03/2008 12:00 nullnull
null
15/03/2008 15:00 nullnull
null
But unfortunatly all that I get is this set :
dat heur datmesure heuremesure t
---
15/03/2008 0:00 15/03/2008 0:00
8,3
15/03/2008 3:00 15/03/2008 3:00
12
15/03/2008 6:00 15/03/2008 6:00
15
15/03/2008 9:00 15/03/2008 9:00
18
Getting mad with it...
Thanks for any help...
Re: [SQL] Problem with insert related to different schemas
Scott Marlowe writes: > On Wed, Apr 21, 2010 at 7:25 AM, Gonzalo Aguilar Delgado > wrote: >> Maybe but I ran even grant all on schema public for this user to check >> if this was the problem. > That doesn't do what you think it does. You need to grant on the actual > object. The error he was getting was about permissions for the schema, though. One thought: if you're running any moderately recent version of PG, the FK check query will be run as though by the owner of the table, not whoever issued the INSERT. Maybe that user doesn't have the right permissions? regards, tom lane -- 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] LEFT OUTER JOIN issue
In article <987929295d1345b5bce249f42730c...@marktestcr.marktest.pt>, "Oliveiros" writes: > Hi, Thomas. > I believe it is because of your WHERE clause, which is filtering out the nulls > from hp table. > According to > WHERE > hp.poste_idposte = 275 > You only want registers that have hp.poste_idposte = 275, not the null ones. Yes, the WHERE effectively turns the outer into an inner join, thus removing rows from the right table. By moving the WHERE to the JOIN condition, you get the result you expected. -- 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 insert related to different schemas
Hi Tom, This may be a clue... Will check El mié, 21-04-2010 a las 11:23 -0400, Tom Lane escribió: > Scott Marlowe writes: > > On Wed, Apr 21, 2010 at 7:25 AM, Gonzalo Aguilar Delgado > > wrote: > >> Maybe but I ran even grant all on schema public for this user to check > >> if this was the problem. > > > That doesn't do what you think it does. You need to grant on the actual > > object. > > The error he was getting was about permissions for the schema, though. > > One thought: if you're running any moderately recent version of PG, > the FK check query will be run as though by the owner of the table, > not whoever issued the INSERT. Maybe that user doesn't have the right > permissions? > > regards, tom lane > -- 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] LEFT OUTER JOIN issue
Hi, > SELECT ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t FROM calendar_temp as ct > LEFT OUTER JOIN h_part as hp > ON ct.dat = hp.datmesure > AND ct.heur = hp.heuremesure > WHERE > hp.poste_idposte = 275 > ORDER BY ct.dat, ct.heur > dat heur datmesure heuremesure t > --- > 15/03/2008 0:00 15/03/2008 0:008,3 > 15/03/2008 3:00 15/03/2008 3:0012 > 15/03/2008 6:00 15/03/2008 6:0015 > 15/03/2008 9:00 15/03/2008 9:0018 > 15/03/2008 12:00 nullnull null > 15/03/2008 15:00 nullnull null Would this work? SELECT ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t FROM calendar_temp as ct LEFT OUTER JOIN h_part as hp ON ct.dat = hp.datmesure AND ct.heur = hp.heuremesure WHERE coalesce(hp.poste_idposte,275) = 275 ORDER BY ct.dat, ct.heur dat | heur | datmesure | heuremesure | t +--++-+-- 2008-03-15 | 00:00:00 | 2008-03-15 | 00:00:00| 8.3 2008-03-15 | 03:00:00 | 2008-03-15 | 03:00:00| 12.0 2008-03-15 | 06:00:00 | 2008-03-15 | 06:00:00| 15.0 2008-03-15 | 09:00:00 | 2008-03-15 | 09:00:00| 18.0 2008-03-15 | 12:00:00 || | 2008-03-15 | 15:00:00 || | (6 rows) Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
