pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsimple problem
10+ messages / 10 participants
[nested] [flat]
* simple problem
@ 2002-03-05 11:44 Daniel Ordobas Bortolas <bortolas@inf.ufsm.br>
0 siblings, 0 replies; 10+ messages in thread
From: Daniel Ordobas Bortolas @ 2002-03-05 11:44 UTC (permalink / raw)
To: pgsql-sql
Hello!
I am having problems creating a table and i'd like some help.
-->create table loco( id_loco int4 not null, primary key (id_loco), check(
(select count(*) from loco ) < 3 ) );
I guess the statment is right, but when:
->insert into loco values(1);
the result is: ERROR: ExecEvalExpr: unknown expression type 108
How can I fix this problem?
Thanks.
^ permalink raw reply [nested|flat] 10+ messages in thread
* simple problem
@ 2002-03-06 15:08 Daniel Ordobas Bortolas <bortolas@inf.UFSM.br>
2002-03-06 18:20 ` Re: simple problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-06 20:06 ` Re: simple problem Tom Lane <tgl@sss.pgh.pa.us>
2002-03-16 09:23 ` Re: simple problem Masaru Sugawara <rk73@sea.plala.or.jp>
0 siblings, 3 replies; 10+ messages in thread
From: Daniel Ordobas Bortolas @ 2002-03-06 15:08 UTC (permalink / raw)
To: pgsql-sql
Hello!
I am having problems creating a table and i'd like some help.
-->create table loco( id_loco int4 not null, primary key (id_loco), check(
(select count(*) from loco ) < 3 ) );
I guess the statment is right, but when:
->insert into loco values(1);
the result is: ERROR: ExecEvalExpr: unknown expression type 108
How can I fix this problem?
Thanks.
Daniel Bortolas.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple problem
2002-03-06 15:08 simple problem Daniel Ordobas Bortolas <bortolas@inf.UFSM.br>
@ 2002-03-06 18:20 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-06 19:34 ` Re: simple problem Hano de la Rouviere <hano@linutech.co.za>
2002-03-06 20:13 ` Re: simple problem PG Explorer <pgmail@pgexplorer.com>
2 siblings, 2 replies; 10+ messages in thread
From: Stephan Szabo @ 2002-03-06 18:20 UTC (permalink / raw)
To: Daniel Ordobas Bortolas <bortolas@inf.UFSM.br>; +Cc: pgsql-sql
On Wed, 6 Mar 2002, Daniel Ordobas Bortolas wrote:
>
> Hello!
> I am having problems creating a table and i'd like some help.
>
> -->create table loco( id_loco int4 not null, primary key (id_loco), check(
> (select count(*) from loco ) < 3 ) );
IIRC, check constraints with subselects are not currently supported.
You can make triggers that do the check for you.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple problem
2002-03-06 15:08 simple problem Daniel Ordobas Bortolas <bortolas@inf.UFSM.br>
2002-03-06 18:20 ` Re: simple problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-03-06 19:34 ` Hano de la Rouviere <hano@linutech.co.za>
1 sibling, 0 replies; 10+ messages in thread
From: Hano de la Rouviere @ 2002-03-06 19:34 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; Daniel Ordobas Bortolas <bortolas@inf.UFSM.br>; +Cc: pgsql-sql
try without the " on ''autor''
where m_name = autor;
http://www.pgexplorer.com
PostgreSQL GUI Tool
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Daniel Ordobas Bortolas" <bortolas@inf.UFSM.br>
Cc: <pgsql-sql@postgresql.org>
Sent: Wednesday, March 06, 2002 8:20 PM
Subject: Re: [SQL] simple problem
> On Wed, 6 Mar 2002, Daniel Ordobas Bortolas wrote:
>
> >
> > Hello!
> > I am having problems creating a table and i'd like some help.
> >
> > -->create table loco( id_loco int4 not null, primary key (id_loco),
check(
> > (select count(*) from loco ) < 3 ) );
>
> IIRC, check constraints with subselects are not currently supported.
> You can make triggers that do the check for you.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple problem
2002-03-06 15:08 simple problem Daniel Ordobas Bortolas <bortolas@inf.UFSM.br>
2002-03-06 18:20 ` Re: simple problem Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-03-06 20:13 ` PG Explorer <pgmail@pgexplorer.com>
1 sibling, 0 replies; 10+ messages in thread
From: PG Explorer @ 2002-03-06 20:13 UTC (permalink / raw)
To: pgsql-sql
try without the " on ''autor''
where m_name = autor;
http://www.pgexplorer.com
PostgreSQL GUI Tool
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Daniel Ordobas Bortolas" <bortolas@inf.UFSM.br>
Cc: <pgsql-sql@postgresql.org>
Sent: Wednesday, March 06, 2002 8:20 PM
Subject: Re: [SQL] simple problem
> On Wed, 6 Mar 2002, Daniel Ordobas Bortolas wrote:
>
> >
> > Hello!
> > I am having problems creating a table and i'd like some help.
> >
> > -->create table loco( id_loco int4 not null, primary key (id_loco),
check(
> > (select count(*) from loco ) < 3 ) );
>
> IIRC, check constraints with subselects are not currently supported.
> You can make triggers that do the check for you.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple problem
2002-03-06 15:08 simple problem Daniel Ordobas Bortolas <bortolas@inf.UFSM.br>
@ 2002-03-06 20:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2002-03-06 20:06 UTC (permalink / raw)
To: Daniel Ordobas Bortolas <bortolas@inf.UFSM.br>; +Cc: pgsql-sql
"Daniel Ordobas Bortolas" <bortolas@inf.UFSM.br> writes:
> -->create table loco( id_loco int4 not null, primary key (id_loco), check(
> (select count(*) from loco ) < 3 ) );
> ->insert into loco values(1);
> the result is: ERROR: ExecEvalExpr: unknown expression type 108
Subselects aren't supported in check constraints (and if you were
running a version newer than 7.0.*, it would've told you so ...)
Possibly you could do what you want with a trigger function.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple problem
2002-03-06 15:08 simple problem Daniel Ordobas Bortolas <bortolas@inf.UFSM.br>
@ 2002-03-16 09:23 ` Masaru Sugawara <rk73@sea.plala.or.jp>
2 siblings, 0 replies; 10+ messages in thread
From: Masaru Sugawara @ 2002-03-16 09:23 UTC (permalink / raw)
To: Daniel Ordobas Bortolas <bortolas@inf.UFSM.br>; +Cc: pgsql-sql
On Wed, 06 Mar 2002 15:08:45 GMT
"Daniel Ordobas Bortolas" <bortolas@inf.UFSM.br> wrote:
> Hello!
> I am having problems creating a table and i'd like some help.
>
> -->create table loco( id_loco int4 not null, primary key (id_loco), check(
> (select count(*) from loco ) < 3 ) );
>
> I guess the statment is right, but when:
> ->insert into loco values(1);
>
> the result is: ERROR: ExecEvalExpr: unknown expression type 108
>
By taking advantage of the fact that COUNT() aggregation can be used
in RULEs(at least in v7.2) and that NULL can't be inserted into columns
defined with NOT NULL , you'll be able to set the limit on the number of
the rows. you, however, probably need to take care not to misunderstand the
meaning in the returned error message -- whether it shows the original
meaning or the limit: COUNT() < n. But I would think that using TRIGGERs
seems common at the moment.
create table loco( id_loco int4 not null, primary key (id_loco));
create view loco_view as select id_loco from loco;
create rule rule_loco_view as
on insert to loco_view
do instead insert into loco(id_loco)
select case when (select count(*) from loco) < 3
then new.id_loco
else null
end ;
renew=> insert into loco_view values(1);
INSERT 1712397 1
renew=> insert into loco_view values(1);
ERROR: Cannot insert a duplicate key into unique index loco_pkey
renew=> insert into loco_view values(2);
INSERT 1712399 1
renew=> insert into loco_view values(null);
ERROR: ExecAppend: Fail to add null value in not null attribute id_loco
renew=> insert into loco_view values(3);
INSERT 1712400 1
renew=> insert into loco_view values(3);
ERROR: ExecAppend: Fail to add null value in not null attribute id_loco
renew=>
renew=> select * from loco;
id_loco
---------
1
2
3
(3 rows)
Regards,
Masaru Sugawara
^ permalink raw reply [nested|flat] 10+ messages in thread
* simple problem???
@ 2006-07-20 14:52 ivan marchesini <marchesini@unipg.it>
2006-07-20 20:47 ` Re: simple problem??? Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-21 09:09 ` Re: simple problem??? Patrick Jacquot <patrick.jacquot@anpe.fr>
0 siblings, 2 replies; 10+ messages in thread
From: ivan marchesini @ 2006-07-20 14:52 UTC (permalink / raw)
To: pgsql-sql
Dear users....
It's a simple problem I think (and I hope :-)
I have a table like this:
A|B|C
1|2|20
1|3|10
1|4|21
2|3|12
2|4|22
3|4|23
where the first two column are a combination without repetition (with
k=2) of the numbers 1,2,3,4
for each pair I have a value in the column C.
I would want a select that can extract these records:
1|3|10
2|3|12
3|4|23
i.e. the select must look into the A field first and:
-select all records where A=1,
-find, into this selection, the record where there's the minimum value
of the field C
-print all the fields for this record.
then
-select all records where A=2
-find, into this selection, the record where there's the minimum value
of the field C
-print all the fields of this record.
and so on...
using
SELECT a,MIN(c) FROM table GROUP BY a
is a partial solution because I can't see the value of B
and I obtain:
1|10
2|12
3|23
How can I do for plotting also the value of B???
Thank you very much
Ivan
--
Ivan Marchesini
Department of Civil and Environmental Engineering
University of Perugia
Via G. Duranti 93/a
06125
Perugia (Italy)
e-mail: marchesini@unipg.it
ivan.marchesini@gmail.com
tel: +39(0)755853760
fax: +39(0)755853756
jabber: geoivan73@jabber.org
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple problem???
2006-07-20 14:52 simple problem??? ivan marchesini <marchesini@unipg.it>
@ 2006-07-20 20:47 ` Richard Broersma Jr <rabroersma@yahoo.com>
1 sibling, 0 replies; 10+ messages in thread
From: Richard Broersma Jr @ 2006-07-20 20:47 UTC (permalink / raw)
To: marchesini@unipg.it; pgsql-sql
> A|B|C
> 1|2|20
> 1|3|10
> 1|4|21
> 2|3|12
> 2|4|22
> 3|4|23
> 1|3|10
> 2|3|12
> 3|4|23
> -select all records where A=1,
> -find, into this selection, the record where there's the minimum value
> of the field C
> -print all the fields for this record.
maybe this will work;
select a,b,c
from table as T1
join (select a, min(c) as minc from table group by a) as T2
on (t1.a = t2.a) and (t1.c = t2.c)
;
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: simple problem???
2006-07-20 14:52 simple problem??? ivan marchesini <marchesini@unipg.it>
@ 2006-07-21 09:09 ` Patrick Jacquot <patrick.jacquot@anpe.fr>
1 sibling, 0 replies; 10+ messages in thread
From: Patrick Jacquot @ 2006-07-21 09:09 UTC (permalink / raw)
To: marchesini@unipg.it; +Cc: pgsql-sql
ivan marchesini wrote:
>Dear users....
>It's a simple problem I think (and I hope :-)
>
>I have a table like this:
>A|B|C
>1|2|20
>1|3|10
>1|4|21
>2|3|12
>2|4|22
>3|4|23
>
>where the first two column are a combination without repetition (with
>k=2) of the numbers 1,2,3,4
>for each pair I have a value in the column C.
>
>I would want a select that can extract these records:
>
>1|3|10
>2|3|12
>3|4|23
>
>
>i.e. the select must look into the A field first and:
>-select all records where A=1,
>-find, into this selection, the record where there's the minimum value
>of the field C
>-print all the fields for this record.
>
>then
>
>-select all records where A=2
>-find, into this selection, the record where there's the minimum value
>of the field C
>-print all the fields of this record.
>
>and so on...
>
>using
>SELECT a,MIN(c) FROM table GROUP BY a
>
>is a partial solution because I can't see the value of B
>and I obtain:
>
>1|10
>2|12
>3|23
>
>How can I do for plotting also the value of B???
>
>Thank you very much
>
>Ivan
>
>
Maybe you could try
Select distinct on (a,b) a,b,c from(select * from table order by A,C)
The distinct on construct is a postgreSql-ism
Cordialement
--
Patrick
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-07-21 09:09 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-05 11:44 simple problem Daniel Ordobas Bortolas <bortolas@inf.ufsm.br>
2002-03-06 15:08 simple problem Daniel Ordobas Bortolas <bortolas@inf.UFSM.br>
2002-03-06 18:20 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-06 19:34 ` Hano de la Rouviere <hano@linutech.co.za>
2002-03-06 20:13 ` PG Explorer <pgmail@pgexplorer.com>
2002-03-06 20:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-03-16 09:23 ` Masaru Sugawara <rk73@sea.plala.or.jp>
2006-07-20 14:52 simple problem??? ivan marchesini <marchesini@unipg.it>
2006-07-20 20:47 ` Re: simple problem??? Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-21 09:09 ` Re: simple problem??? Patrick Jacquot <patrick.jacquot@anpe.fr>
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