pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL Problem
8+ messages / 7 participants
[nested] [flat]
* SQL Problem
@ 1999-10-28 10:47 Imtiaz. S. M <imtiaz_sm@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Imtiaz. S. M @ 1999-10-28 10:47 UTC (permalink / raw)
To: pgsql-sql
I have two tables which have exactly the same structure. Both of them
have one datetime column along with three other columns. But the
datetime column value in the second table is NULL for all the rows. Now
I want select the least date(date with the least value in the datetime
column) from the first table and update all the rows in the second table
with this least date value from the first table. I do not want to use
any compiler or host variables. I should be able to do this only using
SQL statements. Cn somebody please help me? Thanks in advance.
regards
Imtiaz
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] SQL Problem
@ 1999-10-28 10:58 Peter Eisentraut <e99re41@DoCS.UU.SE>
parent: Imtiaz. S. M <imtiaz_sm@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Peter Eisentraut @ 1999-10-28 10:58 UTC (permalink / raw)
To: Imtiaz. S. M <imtiaz_sm@yahoo.com>; +Cc: pgsql-sql
UPDATE second_table SET date_column = (SELECT min(date_column) FROM
first_table);
On Thu, 28 Oct 1999, Imtiaz. S. M wrote:
> I have two tables which have exactly the same structure. Both of them
> have one datetime column along with three other columns. But the
> datetime column value in the second table is NULL for all the rows. Now
> I want select the least date(date with the least value in the datetime
> column) from the first table and update all the rows in the second table
> with this least date value from the first table. I do not want to use
> any compiler or host variables. I should be able to do this only using
> SQL statements. Cn somebody please help me? Thanks in advance.
>
> regards
> Imtiaz
>
>
> ************
>
>
--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Re: [SQL] SQL Problem
@ 1999-10-29 13:18 Imtiaz. S. M <imtiaz_sm@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Imtiaz. S. M @ 1999-10-29 13:18 UTC (permalink / raw)
To: pgsql-sql
First of all thanks to both Peter Eisentraut and Fabio Ambrosanio for
their help. I still haven't been able to fix the problem I'm facing.
My problem was
> I have two tables which have exactly the same structure. Both of them
> have one datetime column along with three other columns. But the
> datetime column value in the second table is NULL for all the rows.
> Now I want to select the least date(date with the least value in the
>datetime column) from the first table and update all the rows in the
second table
> with this least date value from the first table. I do not want to use
> any compiler or host variables. I should be able to do this only using
> SQL statements. Can somebody please help me? Thanks in advance.
>
> regards
> Imtiaz
Peter Eisentraut wrote
UPDATE second_table SET date_column = (SELECT min(date_column) FROM
first_table);
But this give me the following error
ERROR: parser: parse error at or near select
Then Fabio Ambrosanio wrote
update second_table set datetime_field =
min(first_table.datetime_field)
from first_table;
This works for smaller tables alright.
But my first table contains about 100000 rows and the second table
contains about 9000 rows
After submitting the query for execution, it takes a very long time and
then gives the following error.
FATAL 1: Memory exhausted in AllocSetAlloc()
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.
I have even tried increasing the size of the shared memory. I think this
might be an optimization problem.
I'm using a PII 350Mhz machine(i686) with 32 Mb Ram and 4gb hard disk.
My OS is Linux Kernel v 2.0.36 Red Hat v 5.2
PostgreSQL version is 6.5.1
I would also prefer the SQL statement which solves this prolem to be
SQL92 compliant.
Can somebody solve this please?
regards
Imtiaz
^ permalink raw reply [nested|flat] 8+ messages in thread
* SQL problem
@ 2001-03-07 14:23 Salvador Mainé <salvamaine@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Salvador Mainé @ 2001-03-07 14:23 UTC (permalink / raw)
To: pgsql-sql
Hello:
I have a table with pluviometrical data
meteo (rain float, day date)
I want to select the the day of maximum value for each year.It should be
something like :
select max(rain),day from meteo group by date_part('year', day);
but it obiously doesn't work.
I thought of doing it with aggregates, but it's far too complicated. Is
there an 'easy' way to do this?
--
Salva
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL problem
@ 2001-03-07 18:12 Kevin T. Manley (Home) <kmanley@uswest.net>
parent: Salvador Mainé <salvamaine@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Kevin T. Manley \(Home\) @ 2001-03-07 18:12 UTC (permalink / raw)
To: pgsql-sql
Here's one approach:
create view vw_maxrain as select max(rain) as rain, date_part('year',day) as
year from meteo group by year;
select day, meteo.rain from meteo, vw_maxrain where
meteo.rain=vw_maxrain.rain;
"Salvador Mainé" <salvamaine@yahoo.com> wrote in message
news:3AA6445B.F409966F@yahoo.com...
> Hello:
>
> I have a table with pluviometrical data
>
> meteo (rain float, day date)
>
> I want to select the the day of maximum value for each year.It should be
> something like :
>
>
> select max(rain),day from meteo group by date_part('year', day);
>
>
> but it obiously doesn't work.
> I thought of doing it with aggregates, but it's far too complicated. Is
> there an 'easy' way to do this?
>
>
>
>
> --
> Salva
^ permalink raw reply [nested|flat] 8+ messages in thread
* sql problem
@ 2004-11-05 14:34 Flavio Fonseca <ff@dr.ufu.br>
0 siblings, 1 reply; 8+ messages in thread
From: Flavio Fonseca @ 2004-11-05 14:34 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am having a problem with a system I developed using php with postgres.
Take a look at this:
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.
NetAdmin=# delete from operador where oplogin = 'ff';
ERROR: fk_historicosessao_operador referential integrity violation - key in
operador still referenced from historicosessao
and then, this:
Welcome to psql 7.4.2, the PostgreSQL interactive terminal.
NetAdmin=# delete from operador where oplogin = 'ff';
ERROR: update or delete on "operador" violates foreign key constraint
"fk_historicosessao_operador" on "historicosessao"
I am not able to get a error_number in pgsql errors, only the error message.
So when I used pgsql 7.3 a used the string "referential integrity violation"
to detect this error, but on version 7.4 of pgsql this string was replaced
with "violates foreign key constraint" .
Anyone has a definitive solution on this or the way is to wait and change all
my code on future releases of pgsql?
Thank you all for the attention.
--
Att.
Flavio Fonseca
Administrador de Redes
Divisao de Redes
Universidade Federal de Uberlandia
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: sql problem
@ 2004-11-05 16:50 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Flavio Fonseca <ff@dr.ufu.br>
0 siblings, 0 replies; 8+ messages in thread
From: Achilleus Mantzios @ 2004-11-05 16:50 UTC (permalink / raw)
To: Flavio Fonseca <ff@dr.ufu.br>; +Cc: pgsql-sql
O Flavio Fonseca Ýãñáøå óôéò Nov 5, 2004 :
> Hi,
>
> I am having a problem with a system I developed using php with postgres.
>
> Take a look at this:
>
> Welcome to psql 7.3.2, the PostgreSQL interactive terminal.
> NetAdmin=# delete from operador where oplogin = 'ff';
> ERROR: fk_historicosessao_operador referential integrity violation - key in
> operador still referenced from historicosessao
>
> and then, this:
>
> Welcome to psql 7.4.2, the PostgreSQL interactive terminal.
> NetAdmin=# delete from operador where oplogin = 'ff';
> ERROR: update or delete on "operador" violates foreign key constraint
> "fk_historicosessao_operador" on "historicosessao"
>
> I am not able to get a error_number in pgsql errors, only the error message.
> So when I used pgsql 7.3 a used the string "referential integrity violation"
> to detect this error, but on version 7.4 of pgsql this string was replaced
> with "violates foreign key constraint" .
> Anyone has a definitive solution on this or the way is to wait and change all
> my code on future releases of pgsql?
The right way to do so is to query for the SQLSTATE codes.
For instance the jdbc7.4.6 driver supports sqlstate, which you can
retrieve by sqle.getSQLState()
That text of an Error may change across releases but the error code should
be constant.
p.s
I dont do it my self. i just let my users educate themselves :)
>
>
> Thank you all for the attention.
>
--
-Achilleus
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL problem...
@ 2007-06-28 19:54 Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Rodrigo De León @ 2007-06-28 19:54 UTC (permalink / raw)
To: pgsql-sql
On Jun 28, 1:43 pm, "Bauhaus" <niemandh...@pandora.be> wrote:
> I have the following table Price:
>
> FuelID PriceDate Price
> LPG 1/05/2007 0,2
> LPG 13/05/2007 0,21
> SPS 2/05/2007 1,1
> SPS 15/05/2007 1,08
>
> And I have to make the following query:
>
> FuelID PriceDate_from PriceDate_To Price
> LPG 1/05/2007 13/05/2007 0,2
> SPS 2/05/2007 15/05/2007 1,1
> LPG 13/05/2007 0,21
> SPS 15/05/2007 1,08
SELECT fuelid, pricedate AS pricedate_from
, (SELECT MIN(pricedate)
FROM price
WHERE fuelid = s.fuelid
AND pricedate > s.pricedate) AS pricedate_to, price
FROM price s
ORDER BY pricedate_from;
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-06-28 19:54 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-10-28 10:47 SQL Problem Imtiaz. S. M <imtiaz_sm@yahoo.com>
1999-10-28 10:58 ` Peter Eisentraut <e99re41@DoCS.UU.SE>
1999-10-29 13:18 Re: Re: [SQL] SQL Problem Imtiaz. S. M <imtiaz_sm@yahoo.com>
2001-03-07 14:23 SQL problem Salvador Mainé <salvamaine@yahoo.com>
2001-03-07 18:12 ` Re: SQL problem Kevin T. Manley (Home) <kmanley@uswest.net>
2004-11-05 14:34 sql problem Flavio Fonseca <ff@dr.ufu.br>
2004-11-05 16:50 ` Re: sql problem Achilleus Mantzios <achill@matrix.gatewaynet.com>
2007-06-28 19:54 Re: SQL problem... Rodrigo De León <rdeleonp@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox