[SQL] Double aggregate problem

[David Weilers]

Dear anyone,

I have the following query:

select v.id, array_to_string(array_accum(s.name),', ') as sector , 
array_to_string(array_accum(p.name),', ') as provincie from tblvacature 
v, tblaccount a , tblvacaturesector vs, tblsector s , 
tblvacatureprovincie vp, tblprovincie p where v.id = 11 and v.account = 
a.id and vs.vacature = v.id and s.id = vs.sector and vp.vacature = v.id 
and p.id = vp.provincie group by v.id, v.inserted order by v.inserted 
desc

That currently produces the following output:

id |  sector   | 

   provincie
+---+---
---
 11 | Gaafjes, Eerder, Gaafjes, Eerder, Gaafjes, Eerder | Noord-Holland, 

Noord-Holland, Limburg, Limburg, Oost-Vlaanderen, Oost-Vlaanderen

If i leave out one aggregate, the result is as i expect (if I leave out 
'provincie', sector gives):

Gaafjes, Eeerder

Only two results.

I would like both array_accum returning only what they should and not 
doubles.

Any help is appreciated.

--
Regards,

David Weilers

-- 
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] Double aggregate problem

[David Weilers]

Hello,

I have the following tables:

CREATE TABLE tblvacature (
id serial PRIMARY KEY,
account int NOT NULL REFERENCES tblaccount (id) ON DELETE CASCADE,

title varchar(128),
bedrijfsprofiel text,
functieomschrijving text,
functieeisen text,
arbeidsvoorwaarden text
overig text,
sollicitatieinfo text,

inserted timestamp DEFAULT now()
);

CREATE TABLE tblvacaturesector ( vacature int NOT NULL REFERENCES 
tblvacature (id) ON DELETE CASCADE, sector int NOT NULL REFERENCES 
tblsector (id) ON DELETE CASCADE );
select * from tblvacaturesector where vacature = 11;
 vacature | sector
--+
   11 |  5
   11 |  2

CREATE TABLE tblvacatureprovincie ( vacature int NOT NULL REFERENCES 
tblvacature (id) ON DELETE CASCADE, provincie int NOT NULL REFERENCES 
tblprovincie (id) ON DELETE CASCADE );
select * from tblvacatureprovincie where vacature = 11;
 vacature | provincie
--+---
   11 | 7
   11 | 1
   11 | 8

CREATE TABLE tblprovincie (
id serial PRIMARY KEY,
land int NOT NULL REFERENCES tblland (id) ON DELETE RESTRICT,

name varchar(128)
);
select * from tblprovincie;
 id | land |  name
+--+-
  1 |1 | Noord-Holland
  2 |1 | Zuid-Holland
  3 |1 | Groningen
  4 |2 | Brabant
  5 |1 | Utrecht
  6 |2 | Antwerpen
  7 |2 | Limburg
  8 |2 | Oost-Vlaanderen

CREATE TABLE tblsector (
id serial PRIMARY KEY,

name varchar(128)
);
select * from tblsector;
 id |  name
+-
  4 | Aap
  1 | Cool
  5 | Eerder
  2 | Gaafjes
  6 | Later
  3 | Netjes
 11 | ICT

I hope that's enough information?

> -Oorspronkelijk bericht-
> Van: Peter Eisentraut [mailto:pete...@gmx.net]
> Verzonden: woensdag 22 juli 2009 20:05
> Aan: pgsql-sql@postgresql.org
> CC: David Weilers
> Onderwerp: Re: [SQL] Double aggregate problem
> 
> On Wednesday 22 July 2009 19:16:21 David Weilers wrote:
> > I have the following query:
> >
> > select v.id, array_to_string(array_accum(s.name),', ') as sector ,
> > array_to_string(array_accum(p.name),', ') as provincie from 
tblvacature
> > v, tblaccount a , tblvacaturesector vs, tblsector s ,
> > tblvacatureprovincie vp, tblprovincie p where v.id = 11 and 
v.account =
> > a.id and vs.vacature = v.id and s.id = vs.sector and vp.vacature = 
v.id
> > and p.id = vp.provincie group by v.id, v.inserted order by 
v.inserted
> > desc
> >
> > That currently produces the following output:
> 
> No one is going to be able to reproduce that without the table 
definitions and
> data.
> 



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Foreign key constraint referencing a parent table

[David Weilers]

Hi,

I have a problem when inserting rows into my table tblfactuurpost that 
references a table tblfactuur which is a parent to tblclientfactuur.  
The actual row is inserted into tblfactuur.

The problem is that i cannot insert the row into tblfactuurpost (which 
references the parent table tblfactuur) because PSQL complains the 
referencing ID (2) is missing while it is actually *not* missing at all.

Thanks to anyone who can shed some light ...

I have the following table set-up:

CREATE TABLE tblfactuur (
id serial PRIMARY KEY,
number int NOT NULL,
year int NOT NULL CHECK (year>=1900 AND year<=2999),

vanaf date NOT NULL,
tot date NOT NULL CHECK (tot > vanaf),

total int -- totaalbedrag in centen
);

CREATE TABLE tblclientfactuur (
client int NOT NULL REFERENCES tblclient (id) ON DELETE RESTRICT
, PRIMARY KEY (id)
) INHERITS (tblfactuur);

CREATE TABLE tblfactuurpost (
id serial PRIMARY KEY,
factuur int NOT NULL REFERENCES tblfactuur (id) ON DELETE CASCADE 
DEFERRABLE,

omschrijving varchar(128),

btw smallint DEFAULT 19, -- standaard 19% btw
aantal decimal(8,1) NOT NULL CHECK (aantal >= 0), -- aantal
perstuk int NOT NULL CHECK (perstuk >= 0), -- bedrag per stuk in 
centen
amount int NOT NULL CHECK (amount >= 0), -- totaal bedrag in centen

position smallint CHECK (position >= 0)
);

This is the data in tblfactuur:

(db)=> select * from tblfactuur;
 id | number | year |   vanaf|tot | total
++--+++
  2 |  1 | 2010 | 2010-03-01 | 2010-04-01 | 397800
(1 row)

I try to insert: 

insert into tblfactuurpost (factuur, omschrijving, btw, aantal, perstuk, 

amount, position)
select ?, ?, ?, ?, ?, ?, count(*) from tblfactuurpost where factuur 

= ?;
parameters: (2, Gespecialiseerde 24 uur zorg ma. t/m vr., 19, 156, 1425, 

397800, 2)

Regards, David


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql