[SQL] search/replace in update
Hi all, here in England OFTEL, the governing body for all things telephone recent hand another phone number dialing code change (one of many). This means that e.g. all mobile numbers that used to start 0589 now start 07889. Is there a way in SQL to update the phone number in-place? -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] pl/pgsql - code review + question
Hi all, I've just written my first pl/pgsql function (code included below for you to pull apart). It takes an int4 mid (e.g. 15) and then using a select pulls out the team number (e.g. 'NE/012' and a unit number (e.g. 2) and returns the full unit number NE/012-02. I now want to write the reverse function, where I can enter 'NE/012-02' and get back the mid 15. The bit I'm stuck on is now I split the team part from the member part so that I can build the select statement. TIA Gary __BEGIN__ CREATE FUNCTION getunitno(int4) RETURNS varchar AS ' DECLARE mid ALIAS FOR $1; results RECORD; BEGIN select into results t.tnumber as tnumber, m.mnumber as mnumber from teams t, members m where t.tid = m.mteam and m.mid = mid; if results.mnumber < 10 then return results.tnumber || ''-0'' || results.mnumber; else return results.tnumber || ''-'' || results.mnumber; end if; END; ' LANGUAGE 'plpgsql'; __END__ -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] pl/pgsql - code review + question
Okay, I've been hit round the back of the head, and I realised that the
postgresql functions (inc subtring) are available in pl/pgsql, so that's my
problem solved.
I've written the getmid function as below, which is basically the same as the
getunitno I included in my first post.
My problem now is that when I include the code to handle the record not being
there, from the pgsql chapter (section 23.2.3.3) I get the following errors
based of the function below. Can anyone explain why the concat of the string
is failing. If I simply "raise exception ''member not found''" all works fine.
__BEGIN__ (screen output)
[revcom@curly revcom]$ psql -f t
DROP
CREATE
[revcom@curly revcom]$ psql -c "select getmid('NE/011-06');"
NOTICE: plpgsql: ERROR during compile of getmid near line 15
ERROR: parse error at or near "|"
[revcom@curly revcom]$
__END__
__BEGIN__ (~/t which contains the function def)
drop function getmid(varchar);
CREATE FUNCTION getmid(varchar) RETURNS int4 AS '
DECLARE
unitno ALIAS FOR $1;
teamno varchar;
munit int4;
results RECORD;
BEGIN
teamno := substring(unitno from 1 for 6);
munit := substring(unitno from 8);
select into results m.mid as mid
from teams t, members m
where t.tid = m.mteam and
t.tnumber = ''teamno'' and
m.mnumber = munit;
if not found then
raise exception ''Member '' || unitno || '' not found'';
return 0;
end if;
return results.mid;
END;
' LANGUAGE 'plpgsql';
__END__
Gary
On Wednesday 18 July 2001 3:10 pm, Gary Stainburn wrote:
> Hi all, I've just written my first pl/pgsql function (code included below
> for you to pull apart).
>
> It takes an int4 mid (e.g. 15) and then using a select pulls out the team
> number (e.g. 'NE/012' and a unit number (e.g. 2) and returns the full unit
> number NE/012-02.
>
> I now want to write the reverse function, where I can enter 'NE/012-02' and
> get back the mid 15. The bit I'm stuck on is now I split the team part
> from the member part so that I can build the select statement.
>
> TIA Gary
>
> __BEGIN__
> CREATE FUNCTION getunitno(int4) RETURNS varchar AS '
> DECLARE
> mid ALIAS FOR $1;
> results RECORD;
> BEGIN
> select into results t.tnumber as tnumber, m.mnumber as mnumber
> from teams t, members m
> where t.tid = m.mteam and m.mid = mid;
> if results.mnumber < 10 then
> return results.tnumber || ''-0'' || results.mnumber;
> else
> return results.tnumber || ''-'' || results.mnumber;
> end if;
> END;
> ' LANGUAGE 'plpgsql';
> __END__
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] Re: pl/pgsql - code review + question
Hi Jeff,
That's sorted my exceptions out, now all I've got to do is find out why it's
not finding the record in the first place.
Gary.
On Wednesday 18 July 2001 4:48 pm, Jeff Eckermann wrote:
> I think you need to use syntax:
> raise exception ''Member % Not Found'', unitno;
>
> > -Original Message-
> > From: Gary Stainburn [SMTP:[EMAIL PROTECTED]]
> > Sent: Wednesday, July 18, 2001 10:24 AM
> > To: pgsql-sql
> > Subject:Re: pl/pgsql - code review + question
> >
> > Okay, I've been hit round the back of the head, and I realised that the
> > postgresql functions (inc subtring) are available in pl/pgsql, so that's
> > my
> > problem solved.
> >
> > I've written the getmid function as below, which is basically the same as
> > the
> > getunitno I included in my first post.
> >
> > My problem now is that when I include the code to handle the record not
> > being
> > there, from the pgsql chapter (section 23.2.3.3) I get the following
> > errors
> > based of the function below. Can anyone explain why the concat of the
> > string
> > is failing. If I simply "raise exception ''member not found''" all works
> > fine.
> >
> > __BEGIN__ (screen output)
> > [revcom@curly revcom]$ psql -f t
> > DROP
> > CREATE
> > [revcom@curly revcom]$ psql -c "select getmid('NE/011-06');"
> > NOTICE: plpgsql: ERROR during compile of getmid near line 15
> > ERROR: parse error at or near "|"
> > [revcom@curly revcom]$
> > __END__
> > __BEGIN__ (~/t which contains the function def)
> > drop function getmid(varchar);
> > CREATE FUNCTION getmid(varchar) RETURNS int4 AS '
> > DECLARE
> > unitno ALIAS FOR $1;
> > teamno varchar;
> > munit int4;
> > results RECORD;
> > BEGIN
> > teamno := substring(unitno from 1 for 6);
> > munit := substring(unitno from 8);
> > select into results m.mid as mid
> > from teams t, members m
> > where t.tid = m.mteam and
> > t.tnumber = ''teamno'' and
> > m.mnumber = munit;
> > if not found then
> > raise exception ''Member '' || unitno || '' not found'';
> > return 0;
> > end if;
> > return results.mid;
> > END;
> > ' LANGUAGE 'plpgsql';
> > __END__
> >
> > Gary
> >
> > On Wednesday 18 July 2001 3:10 pm, Gary Stainburn wrote:
> > > Hi all, I've just written my first pl/pgsql function (code included
> >
> > below
> >
> > > for you to pull apart).
> > >
> > > It takes an int4 mid (e.g. 15) and then using a select pulls out the
> >
> > team
> >
> > > number (e.g. 'NE/012' and a unit number (e.g. 2) and returns the full
> >
> > unit
> >
> > > number NE/012-02.
> > >
> > > I now want to write the reverse function, where I can enter 'NE/012-02'
> >
> > and
> >
> > > get back the mid 15. The bit I'm stuck on is now I split the team part
> > > from the member part so that I can build the select statement.
> > >
> > > TIA Gary
> > >
> > > __BEGIN__
> > > CREATE FUNCTION getunitno(int4) RETURNS varchar AS '
> > > DECLARE
> > > mid ALIAS FOR $1;
> > > results RECORD;
> > > BEGIN
> > > select into results t.tnumber as tnumber, m.mnumber as mnumber
> > > from teams t, members m
> > > where t.tid = m.mteam and m.mid = mid;
> > > if results.mnumber < 10 then
> > > return results.tnumber || ''-0'' || results.mnumber;
> > > else
> > > return results.tnumber || ''-'' || results.mnumber;
> > > end if;
> > > END;
> > > ' LANGUAGE 'plpgsql';
> > > __END__
> >
> > --
> > Gary Stainburn
> >
> > This email does not contain private or confidential material as it
> > may be snooped on by interested government parties for unknown
> > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> >
> >
> > ---(end of broadcast)---
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to [EMAIL PROTECTED] so that your
> > message can get through to the mailing list cleanly
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] multiple lookup per row
Hi all, I've got a table 'phones' which has an indexed key 'pid' of type int4, and a phone number of type varchar(12). I've then got a table 'members' which as an index key 'mid' of type int4. Now, obviously, if each member only had one phone number, I could simply pull it in using a join. My problem is that each member has (potentially) a voice line 'mphone', a fax line 'mfax', and a mobile (that's a cellphone to you lot over the pond) 'mmobile'. How would I embelish a 'select * from members' so that it included the three phone numbers if they exist? The only solution I can think of is to write a plpgsql function to do the lookup and call that three times as part of the select, something like select *, getphone(mphone) as phone, getphone(mfax) as fax. but a purely SQL solution would be nicer. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] example of [outer] join
Hi, me again. I'm having fun here, but here's another question for you. Could someone please give me an example of a join where one of the fields is missing - I think reading from the docs that this is an OUTER join, but I'm having a problem with the syntax. I'm trying to create a view that pulls in all the relevent details for a single member. I'm still with my members table, which has links to the address table (m.madd = a.aid), teams table (m.mteam = t.tid) and the email table (m.memail = e.eid). While every member has an address, and every member belongs to a team, not everyone has an email address. My problem is that every member without an email address gets omitted from the result set. My view so far is: CREATE view member_dets as select *, getphone(m.mphone) as phone, getphone(m.mfax) as fax, getphone(m.mmobile) as mobile, getunitno(m.mid) as munitno from members m, address a, teams t, emails e where m.madd = a.aid and m.memail = e.eid and m.mteam = t.tid; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] where'd the spaces come from
Hi all, Can someone please explain how to remove the spaces from the results of the query below. The current output is also included. What I want out of the query is something like 'NE/027-05'. psql -c "select t.tregion || '/' || to_char(t.tnumber,'000') || '-' || to_char(m.mnumber,'00') as unitno from teams t, members m where m.mteam = t.tid;" unitno - SW/ 041- 03 SW/ 041- 05 NE/ 011- 06 NE/ 011- 01 NE/ 011- 03 NE/ 011- 02 NE/ 011- 10 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] where'd the spaces come from
Hi all, forget it, I've solved it. I converted the calls to: to_char(t.tnumber,'FM000') and it worked. Gary On Monday 23 July 2001 10:18 am, Gary Stainburn wrote: > Hi all, > > Can someone please explain how to remove the spaces from the results of the > query below. The current output is also included. What I want out of the > query is something like 'NE/027-05'. > > psql -c "select t.tregion || '/' || to_char(t.tnumber,'000') || '-' || > to_char(m.mnumber,'00') as unitno from teams t, members m > where m.mteam = t.tid;" >unitno > - > SW/ 041- 03 > SW/ 041- 05 > NE/ 011- 06 > NE/ 011- 01 > NE/ 011- 03 > NE/ 011- 02 > NE/ 011- 10 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Possible problems with cyclic references
Hi all, me again. I've been looking at the doc's again (must stop doing that!) I've been looking at the 'references' clause to implement referential integrity. My problem is that I'm wanting to create a cyclic reference, and was wondering what problems this may cause, e.g. when restoring from a pg_dump. I have a region table (rregion character(2), rname varchar(40), rliasson int4). I have a teams table (ttid int4, tregion character(2) references region(rregion),...) I have a members table (mid int4, mteam references teams(tid),.) Pretty straight forward so far, a member must be a part of a team and a team must be in a region. My problem is that I want to set rliasson as a reference to members (mid) as the Regional Liasson Officer for each region is a member. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Possible problems with cyclic references
Hi Jan, Thanks for the additional info. I did, having already posted the msg, tru to create the references, but found that it would not let me do that as I was trying to create a reference to a table that didn't exist yet. I ended up setting up a one-way reference, running pg_dump to see how to set up the reference after creating the tables (it uses create triggers), and then changing/adding these lines to my create script. Your way seems much nicer. Gary On Monday 23 July 2001 3:18 pm, Jan Wieck wrote: > Gary Stainburn wrote: > > Hi all, me again. > > > > I've been looking at the doc's again (must stop doing that!) > > > > I've been looking at the 'references' clause to implement referential > > integrity. My problem is that I'm wanting to create a cyclic reference, > > and was wondering what problems this may cause, e.g. when restoring from > > a pg_dump. > > > > I have a region table (rregion character(2), rname varchar(40), rliasson > > int4). > > I have a teams table (ttid int4, tregion character(2) references > > region(rregion),...) > > I have a members table (mid int4, mteam references teams(tid),.) > > > > Pretty straight forward so far, a member must be a part of a team and a > > team must be in a region. My problem is that I want to set rliasson as a > > reference to members (mid) as the Regional Liasson Officer for each > > region is a member. > > No problem. pg_dump outputs commands to disable referential > integrity checks during the restore. > > And you could even make rliasson NOT NULL. All you have to do > then is to have the constraints INITIALLY DEFERRED and insert > all the cyclic rows in one transaction. > > Add the constraint to the region table with ALTER TABLE after > creating the members table. > > > Jan > > -- > > #==# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #====== [EMAIL PROTECTED] # > > > > _ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] union in subselect?
Hi all, Can you use (or work around towards) a union in a subquery? I have :- Members mid int4; mnec bool; -- many NEC members Positions (one position = one holder - excludes NEC) posnchar(4); pholder int4; -- refers to mid Actions caction char(4) -- e.g. UPDT = update team cposn char(4) -- refers to posn clevle int4 -- increasing permission level select 'NEC' as posn from members where mid = 81 and mnec = true; posn -- NEC (1 row) select posn from positions where pholder = 81; posn -- MSEC ITA REG (3 rows) select posn from positions where pholder = 81 union select 'NEC' as posn from members where mnec = true and mid = 81; posn -- ITA MSEC NEC REG (4 rows) So far so good. select * from actions where cposn in (select posn from positions where pholder = 81); caction | cposn | clevel -+---+ ENQT| REG | 2 ENQM| REG | 2 AMET| REG | 2 AMET| ITA | 3 Still works - looking good select * from actions where cposn in (select posn from positions where pholder = 81 union select 'NEC' as posn from members where mnec = true and mid = 81); gives me: ERROR: parser: parse error at or near "union" -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] where'd the spaces come from
Hi Bruce, a fix for what? If you're meaning the leading space, then the fix is in the followup post that I made to my original quiestion. i.e. psql -c "select to_char(12,'xFM000');" to_char - x012 (1 row) The 'FM' removes the space. Gary On Thursday 02 August 2001 4:50 pm, Bruce Momjian wrote: > Does anyone have a fix for this? > > > From: "Gary Stainburn" <[EMAIL PROTECTED]> > > > > > psql -c "select t.tregion || '/' || to_char(t.tnumber,'000') || '-' || > > > to_char(m.mnumber,'00') as unitno from teams t, members m > > > where m.mteam = t.tid;" > > >unitno > > > - > > > SW/ 041- 03 > > > SW/ 041- 05 > > > > Looks like a buglet in to_char()s handling of numbers (unless I > > misunderstand the way the formatting is supposed to work). > > > > select '[' || to_char(12,'x000') || ']'; > > ?column? > > -- > > [x 012] > > > > If you're running the current version, might be worth posting a bug > > report. You can work around it with something like: > > > > ... substr(to_char(t.tnumber,'000'),2,3) ... > > > > HTH > > > > - Richard Huxton > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Are circular REFERENCES possible ?
Hi Denis, I've just had a similar experience with a 3-way circle. I have members, who belong in regions. Each region had a Regional Liasson Officer who was a member. I got round it by creating the three tables, but missing out one of the references - i.e. the one that links table 1 to table 3 which doesn't exist yet. I then used pg_dump to see how that would re-create the tables. It didn't create any references/foreign keys etc. when it created the tables, but right at the end, aftter the 'copy's and index creations it did a load of CREATE CONSTRACT TRIGGER entries. I edited these to generate the ones that were missing. This was a bit messy, but it meant that I could keep the logic of my data. As stated in some of the other posts, you will have problems updating your data, with inserts. One thing to remember here is that references aren't checked if the reference value is NULL. So, you could add a customer with the default shop as NULL, then add a shop, and then update the customer. I haven't checked this, but I seam to remember reading that if you do it all inside a transaction, the references aren't checked until the transaction is comitted, so you could do something like: begin insert customer insert shop comit Gary On Tuesday 07 August 2001 10:54 am, Denis Bucher wrote: > Hello ! > > I have a case where I wanted to do circular REFERENCES, is this > impossible ? > > Just an example where it would be useful : > > We deliver to the *shops* of our *customers*. > We have therefore two tables : > - customers (enterprise, financial information, and so on...) > - shop (with a name, street, phone number, name of manager) > > Now, each shop REFERENCES a customer so that we know > to which customer belongs a shop. > > AND, each customer has a DEFAULT shop for deliveries, i.e. most > customers only have one shop, or a main shop and many small ones. > Therefore a customer should REFERENCES the 'main' or 'default' shop. > > Which leads to : > > CREATE TABLE shops ( id_shop SERIAL PRIMARY KEY, id_cust integer REFERENCES > customers, ...) > CREATE TABLE customers ( id_cust SERIAL PRIMARY KEY, id_defaultshop integer > REFERENCES shops, ...) > > But this doesn't work ! Postgres complains like "ERROR: Relation > 'customers' does not exist" > when creating 'shops'. > > Someone told me I should create a third table, ok, but in this case I loose > the total > control about my logic... Do you have a suggestion ? > > Thanks a lot in advance ! > > Denis > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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] Are circular REFERENCES possible ?
Hi all, On Tuesday 07 August 2001 7:35 pm, Tom Lane wrote: > Jan Wieck <[EMAIL PROTECTED]> writes: > > The point is that we based our implementation of foreign keys > > on the SQL3 specs. DEFERRED is not in SQL-92 AFAIK. > > I still have a concern about this --- sure, you can set up the circular > references using ALTER TABLE, but will pg_dump dump them correctly? Based on a small example I've done, I'd say yes. This is because pg_dump doesn't specify the contraints when it creates the tables, it does it at the end by using 'CREATE CONSTRAINT' commands. I have a relationship Members -> teams -> regions -> members and it dumped and restored fine. Gary > > 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]) -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] create function using language SQL
Hi all, As I was reading up on create function etc. while learning plpgsql, I seam to remember it stated that as well as plpgsql, that other languages are/will be available including using SQL as the language. However, I cannot find the syntax to create a function in SQL. Specifically, how you return the result. As an example, how would I create a SQL function to match the plpgsql function below? CREATE FUNCTION getteamno(int4) RETURNS varchar AS ' DECLARE unitno varchar; BEGIN select into unitno tregion || ''/'' || to_char(tnumber,''FM000'') from teams where tid = $1; if not found then raise exception ''Team % not found'',$1; return ''''; end if; return unitno; END; ' LANGUAGE 'plpgsql'; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Re: Are circular REFERENCES possible ?
Hi Mike, A few people have suggested this, but the thing I don't like (and I think at some point William has also stated this) is that doing it this way, you lose the logic (and the purity) of the data. If you allow the cyclic reference, then the data behaves -and looks - exactly as it should do. The customer refers to the shop, and the shop refers to the customer. If I remember correctly, one of the cardinal rules of normalising data is that all related data (e.g. customer) should be together (one table) - hense, the default_shop belongs to the customer table . Relationship table should only be used for n-to-n links. Gary On Tuesday 14 August 2001 2:16 pm, Michael Ansley (UK) wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > The easiest way out is probably to create a relationship entity > called 'default' between customer and shop for the default > relationship. This way you only have to have one direct > relationship, because the other is expressed through the 'default' > table. > > Just a thought... > > > MikeA > > >> -Original Message- > >> From: William Courtier [mailto:[EMAIL PROTECTED]] > >> Sent: 07 August 2001 11:10 > >> To: [EMAIL PROTECTED] > >> Subject: [SQL] Re: Are circular REFERENCES possible ? > >> > >> > >> I denis, > >> > >> I don't know if you can use a circular REFERENCES, but why > >> you try made a > >> references before the table is created (customers). You > >> should create the > >> references after and use the foreign key if circular > >> references does not > >> work. > >> > >> William > >> "Denis Bucher" <[EMAIL PROTECTED]> a écrit dans le message > >> news: [EMAIL PROTECTED] > >> > >> > Hello ! > >> > > >> > I have a case where I wanted to do circular REFERENCES, is this > >> > impossible ? > >> > > >> > Just an example where it would be useful : > >> > > >> > We deliver to the *shops* of our *customers*. > >> > We have therefore two tables : > >> > - customers (enterprise, financial information, and so on...) > >> > - shop (with a name, street, phone number, name of manager) > >> > > >> > Now, each shop REFERENCES a customer so that we know > >> > to which customer belongs a shop. > >> > > >> > AND, each customer has a DEFAULT shop for deliveries, i.e. most > >> > customers only have one shop, or a main shop and many small > >> > ones. Therefore a customer should REFERENCES the 'main' or > >> > >> 'default' shop. > >> > >> > Which leads to : > >> > > >> > CREATE TABLE shops ( id_shop SERIAL PRIMARY KEY, id_cust integer > >> > >> REFERENCES > >> > >> > customers, ...) > >> > CREATE TABLE customers ( id_cust SERIAL PRIMARY KEY, > >> > id_defaultshop > >> > >> integer > >> > >> > REFERENCES shops, ...) > >> > > >> > But this doesn't work ! Postgres complains like "ERROR: > >> > Relation 'customers' does not exist" > >> > when creating 'shops'. > >> > > >> > Someone told me I should create a third table, ok, but in > >> > >> this case I > >> loose > >> > >> > the total > >> > control about my logic... Do you have a suggestion ? > >> > > >> > Thanks a lot in advance ! > >> > > >> > Denis > >> > > >> > > >> > ---(end of > >> > >> broadcast)--- > >> > >> > TIP 1: subscribe and unsubscribe commands go to > >> > >> [EMAIL PROTECTED] > >> > >> > >> > >> ---(end of > >> broadcast)--- > >> TIP 2: you can get off all lists at once with the unregister > >> command > >> (send "unregister YourEmailAddressHere" to > >> [EMAIL PROTECTED]) > > -BEGIN PGP SIGNATURE- > Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com> > > iQA/AwUBO3kkqnympNV/C086EQKcWgCfd1Z2Hbi/g7Rj633Myj67HxkjgvkAn1n+ > hXvHqca0bqE73XY4tmjDq/7v > =2nf2 > -END PGP SIGNATURE- Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: quoted-printable Content-Description: -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] references definition to multi-field primary key
Hi all, I've got some fuel pumps, each of which sell a number of fuel grades - one nozzle per grade, each nozzle connected to a specified tank containing that grade. I can define the tanks, the pump numbers, and the pump grade/nozzle config using the tables below. create table grades ( -- different types of fuel sold gid character, gdesc varchar(20), gprice float, primary key (gid) ); create table tanks ( -- storage tanks tid int4 not null, tgrade character references grades(gid), primary key (tid) ); create table pumps ( -- list of pumps pid int4 not null, primary key (pid) ); create table pgrades ( -- list of nozzles/grades per pump pgpid int4 not null references pumps(pid), pgnozzle int4 not null, pgtank int4 not null references tanks(tid), primary key (pgpid, pgseq) ); My problem is that I want to be able to define a 'Pump Readings' table to show per pump/nozzle the opening and closing reading. However, my problem is that I don't know how to define the references so that I can only create a preadings row for an existing pgrages entry. Here's the table less the required references entry. create table preadings ( -- daily reading per pump/nozzle prdate date not null, prpump int4 not null prnozzle int4, propen integer, prclose integer, primary key (prdate, prpump, prseq) ); I only want the insert to work if prpid matches pgpid and prnozzle matches pgnozzle. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] master-detail relationship and count
Hi folks.
I've got a master detail relationship where I have a railway route table
listing landmarks along the route, and a Links table listing URL's
associated with that landmark. Listed below:
How can I do a query showing the landmark ID, the landmark name, and a count
of links associated with that landmark. Below is a SQL statement that
although is illegal, gives a good idea of what I'm looking for.
select r.rtid, r.rtname, l.count(*) from route r, links l where l.lktype = 'R'
and l.lklid = r.rtid;
nymr=# \d route
Table "route"
Attribute | Type | Modifier
+---+--
rtid | integer | not null default
nextval('route_rtid_seq'::text)
rtmile | integer | not null
rtyards| integer | not null
rtname | character varying(40) |
rtspeed| integer |
rtgradient | integer |
rtsection | integer |
rtphone| character(1) |
rtcomments | text |
Indices: route_index,
route_rtid_key
nymr=# select r.rtid, l.count(*) from route r, links l where
nymr=# \d links
Table "links"
Attribute | Type |Modifier
---+---+-
lkid | integer | not null default
nextval('staff_sid_seq'::text)
lkdesc| character varying(40) |
lkurl | character varying(40) |
lktype| character(1) |
lklid | integer |
Index: links_lkid_key
lktype indicates the link type - 'R' indicates a route entry
lklid indicates the link ID. For a 'R' it is the rtid of the route entry
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] master-detail relationship and count
As you can see from the extract below, your statement has worked for all
landmarks that have links, but ignores any landmarks with out links. How can
I adjust this so that all landmarks are listed, but with a zero count where
appropriate?
select r.rtid, r.rtname, subsel.cnt from route r,
(select r2.rtid as rid, count(lnk.lkid) as cnt from route r2,links lnk
where lnk.lktype='R'
and lnk.lklid = r2.rtid group by r2.rtid) as subsel
where r.rtid = subsel.rid;
[gary@larry gary]$ psql -d nymr On Fri, 29 Nov 2002, Gary Stainburn wrote:
> > Hi folks.
> >
> > I've got a master detail relationship where I have a railway route table
> > listing landmarks along the route, and a Links table listing URL's
> > associated with that landmark. Listed below:
> >
> > How can I do a query showing the landmark ID, the landmark name, and a
> > count of links associated with that landmark. Below is a SQL statement
> > that although is illegal, gives a good idea of what I'm looking for.
> >
> > select r.rtid, r.rtname, l.count(*) from route r, links l where l.lktype
> > = 'R' and l.lklid = r.rtid;
>
> select r.rtid,r.rtname,subsel.cnt from route r,
> (select r2.rtid as rid,count(lnk.lkid) as cnt from route r2,links lnk
> where lnk.type='R'
> and lnk.lklid = r2.rtid group by r2.rtid) as subsel
> where r.rtid = subsel.rid
>
> or something like that.
>
> > nymr=# \d route
> > Table "route"
> > Attribute | Type | Modifier
> > +---+
> >-- rtid | integer | not null default
> > nextval('route_rtid_seq'::text)
> > rtmile | integer | not null
> > rtyards| integer | not null
> > rtname | character varying(40) |
> > rtspeed| integer |
> > rtgradient | integer |
> > rtsection | integer |
> > rtphone| character(1) |
> > rtcomments | text |
> > Indices: route_index,
> > route_rtid_key
> >
> > nymr=# select r.rtid, l.count(*) from route r, links l where
> > nymr=# \d links
> > Table "links"
> > Attribute | Type |Modifier
> > ---+---+-
> > lkid | integer | not null default
> > nextval('staff_sid_seq'::text)
> > lkdesc | character varying(40) |
> > lkurl | character varying(40) |
> > lktype| character(1) |
> > lklid | integer |
> > Index: links_lkid_key
> >
> > lktype indicates the link type - 'R' indicates a route entry
> > lklid indicates the link ID. For a 'R' it is the rtid of the route entry
> > --
> > Gary Stainburn
> >
> > This email does not contain private or confidential material as it
> > may be snooped on by interested government parties for unknown
> > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> >
> >
> > ---(end of broadcast)---
> > TIP 4: Don't 'kill -9' the postmaster
>
> ==
> Achilleus Mantzios
> S/W Engineer
> IT dept
> Dynacom Tankers Mngmt
> Nikis 4, Glyfada
> Athens 16610
> Greece
> tel:+30-10-8981112
> fax:+30-10-8981877
> email: [EMAIL PROTECTED]
> [EMAIL PROTECTED]
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] master-detail relationship and count
I've worked out a way of doing it by vreating a view for the tally info as:
create view link_tally as
select lklid, lktype, count(*) from links group by lklid, lktype;
and then doing:
select r.rtid, r.rtname, l.count from route r
left outer join link_tally l on r.rtid = l.lklid and l.lktype = 'R';
(this works apart from the coalesce bit which I haven't worked out where to
put yet, and for the moment isn't important as NULL is okay as a result).
However, I still can't get it to work straight from the tables. The nearest
I'ev got is:
select r.rtid, r.rtname, subsel.cnt from route r,
(select r2.rtid as rid, count(lnk.lkid) as cnt from route r2, links lnk
where lnk.lktype='R' and lnk.lklid = r2.rtid group by r2.rtid) as subsel
left outer join subsel on r.rtid = subsel.rid;
which comes back with the error:
[gary@larry gary]$ psql On Fri, 29 Nov 2002, Gary Stainburn wrote:
> > As you can see from the extract below, your statement has worked for all
> > landmarks that have links, but ignores any landmarks with out links. How
> > can I adjust this so that all landmarks are listed, but with a zero count
> > where appropriate?
>
> Then, use LEFT OUTER JOIN ... USING (),
> in combination with COALESCE().
>
> (read the docs)
>
> > select r.rtid, r.rtname, subsel.cnt from route r,
> > (select r2.rtid as rid, count(lnk.lkid) as cnt from route r2,links lnk
> > where lnk.lktype='R'
> > and lnk.lklid = r2.rtid group by r2.rtid) as subsel
> > where r.rtid = subsel.rid;
> > [gary@larry gary]$ psql -d nymr > rtid | rtname | cnt
> > --++-
> > 1 | The Grange | 1
> > (1 row)
> > [gary@larry gary]$
> >
> > Gary
> >
> > On Friday 29 Nov 2002 10:36 am, Achilleus Mantzios wrote:
> > > On Fri, 29 Nov 2002, Gary Stainburn wrote:
> > > > Hi folks.
> > > >
> > > > I've got a master detail relationship where I have a railway route
> > > > table listing landmarks along the route, and a Links table listing
> > > > URL's associated with that landmark. Listed below:
> > > >
> > > > How can I do a query showing the landmark ID, the landmark name, and
> > > > a count of links associated with that landmark. Below is a SQL
> > > > statement that although is illegal, gives a good idea of what I'm
> > > > looking for.
> > > >
> > > > select r.rtid, r.rtname, l.count(*) from route r, links l where
> > > > l.lktype = 'R' and l.lklid = r.rtid;
> > >
> > > select r.rtid,r.rtname,subsel.cnt from route r,
> > > (select r2.rtid as rid,count(lnk.lkid) as cnt from route r2,links lnk
> > > where lnk.type='R'
> > > and lnk.lklid = r2.rtid group by r2.rtid) as subsel
> > > where r.rtid = subsel.rid
> > >
> > > or something like that.
> > >
> > > > nymr=# \d route
> > > > Table "route"
> > > > Attribute | Type | Modifier
> > > > +---+
> > > > -- rtid | integer | not null
> > > > default nextval('route_rtid_seq'::text)
> > > > rtmile | integer | not null
> > > > rtyards| integer | not null
> > > > rtname | character varying(40) |
> > > > rtspeed| integer |
> > > > rtgradient | integer |
> > > > rtsection | integer |
> > > > rtphone| character(1) |
> > > > rtcomments | text |
> > > > Indices: route_index,
> > > > route_rtid_key
> > > >
> > > > nymr=# select r.rtid, l.count(*) from route r, links l where
> > > > nymr=# \d links
> > > > Table "links"
> > > > Attribute | Type |Modifier
> > > > ---+---+-
> > > > lkid | integer | not null default
> > > > nextval('staff_sid_seq'::text)
> > > > lkdesc| character varying(40) |
> > > > lkurl | character varying(40) |
> > > > lktype| character(1) |
> > > > lklid | integer |
> > > > Index: links_lkid_key
> >
[SQL] join and dynamic view
Hi folks is it possible to make a dynamically declare a view based on a table? I have 3 tables create table depts ( did character unique not null, -- key dsdesc character (3), -- short desc ddesc character varying(40) -- long desc ); create table staff ( sid int4 not null unique, -- key sname character varying(40), -- name ); create table ranks ( rsidint4 not null references staff(sid), rdidcharacter not null references depts(did), rrank int4 not null, primary key (rsid, rdid) ); copy "depts" from stdin; O OPS Operations M MPD Motive Power Dept \. copy "staff" from stdin; 1 Rod 2 Jayne 3 Freddie \. copy "ranks" from stdin; 1 M 3 2 M 2 2 O 5 3 O 3 \. Is it possible to now define a view such that it returns: select * from myview; sid | Name| OPS | MPD -+-+-+- 1 | Rod | | 3 2 | Jayne | 2 | 5 3 | Freddie | 3 | and if I add another row to depts, that the new row would be included? -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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] join and dynamic view
Hi Christoph, On Tuesday 17 Dec 2002 12:06 pm, Christoph Haller wrote: > > is it possible to make a dynamically declare a view based on a table? > > Yes, by all means. > > > Is it possible to now define a view such that it returns: > > > > select * from myview; > > sid | Name| OPS | MPD > > -+-+-+- > > 1 | Rod | | 3 > > 2 | Jayne | 2 | 5 > > 3 | Freddie | 3 | > > > > and if I add another row to depts, that the new row would be included? > > you mean column, don't you? What I mean here was that if I add another row to the depts table, e.g. A ADM Administrative I would like the ADM column to automatically appear in the 'myview' view without having to recreate the view - i.e. the rows in the 'depts' table become columns in 'myview' view > The closest query I can get so far is > SELECT staff.*, >CASE dsdesc WHEN 'OPS' THEN rrank ELSE NULL END AS "OPS", >CASE dsdesc WHEN 'MPD' THEN rrank ELSE NULL END AS "MPD" > FROM staff,depts,ranks WHERE sid=rsid AND did=rdid ; Surely the problem with this is that I'd have to drop/amend/create the view every time I add a row to 'depts'. Couldn't I just do that using an outer join instead of a case? > > sid | sname | OPS | MPD > -+-+-+- >1 | Rod | | 3 >2 | Jayne | | 2 >2 | Jayne | 5 | >3 | Freddie | 3 | > (4 rows) > > but > > sid | sname | OPS | MPD > -+-+-+- >1 | Rod | | 3 >2 | Jayne | 5| 2 >3 | Freddie | 3 | > (3 rows) > > is what you want (I suppose Jayne's 2 in OPS and 5 in MPD is a mismatch > of yours). Yes it was, sorry. > As soon as you are somebody else can tell me how to merge Jayne's two > rows into one, > I'm sure I can write a plpgsql function to dynamically create the view > you're looking for. How could a plpgsql dynamically create the view? How about a trigger from the on-update of the depts table to drop the view and then create a new one. Could it not do the same thing using outer joins. (I've done VERY little plpgsql and even less with triggers. > > Regards, Christoph -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] join and dynamic view
0 END) AS "mr.
> brown",
>SUM(CASE vendor WHEN 'mr. green' THEN sales ELSE 0 END) AS "mr.
> green",
>SUM(sales) AS "sum of sales"
> FROM sales GROUP BY product ;
> SELECT * FROM sales_report ;
>
> product | mr. pink | mr. brown | mr. green | sum of sales
> -+---+---+---+--
> butter |17 | 2 | 0 | 19
> honey |19 | 0 | 2 | 21
> milk|12 | 8 |34 | 54
> (3 rows)
> It's obvious this approach is most inflexible.
> As soon as there is a new vendor, one has to re-write the query and add
> SUM(CASE vendor WHEN 'mr. new' THEN ... ,
>
> So what we need is a tool to automatically adapt the view to new vendors
>
> resp. new products.
> Here it is (choosing good mnemonics is not my favourite discipline):
>
> CREATE OR REPLACE FUNCTION
> create_pivot_report(TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS INTEGER AS '
> DECLARE
> pg_views_rtype pg_views%ROWTYPE;
> vname_paramALIAS FOR $1;
> pivot_column ALIAS FOR $2;
> select_column ALIAS FOR $3;
> pivot_tableALIAS FOR $4;
> aggregate_func ALIAS FOR $5;
> aggr_columnALIAS FOR $6;
> pivot_record RECORD;
> create_viewTEXT;
> BEGIN
>
> SELECT INTO pg_views_rtype * FROM pg_views WHERE viewname = vname_param;
>
> IF FOUND THEN
> EXECUTE ''DROP VIEW '' || quote_ident(vname_param) ;
> END IF;
> create_view :=
> ''CREATE VIEW '' || quote_ident(vname_param) ||
> '' AS SELECT '' || quote_ident(select_column) ;
> FOR pivot_record IN
> EXECUTE ''SELECT DISTINCT CAST(''
>
> || quote_ident(pivot_column)
> || '' AS TEXT) AS col1 FROM ''
> || quote_ident(pivot_table)
> ||
> || '' ORDER BY '' || quote_ident(pivot_column)
>
> LOOP
> create_view :=
>create_view || '','' || aggregate_func ||
>''(CASE '' || quote_ident(pivot_column) ||
>'' WHEN '' || quote_literal(pivot_record.col1) ||
>'' THEN '' || quote_ident(aggr_column) ||
>'' ELSE 0 END) AS "'' || pivot_record.col1 || ''"'' ;
> END LOOP;
> create_view :=
> create_view || '','' || aggregate_func ||
> ''('' || quote_ident(aggr_column) || '') AS "'' || aggregate_func ||
> '' of '' || aggr_column || ''" FROM '' || quote_ident(pivot_table) ||
> '' GROUP BY '' || quote_ident(select_column);
> EXECUTE create_view ;
>
> RETURN 0;
> END;
> ' LANGUAGE 'plpgsql' ;
>
> -- where
> -- vname_paramALIAS FOR $1; -- the view's name to create
> -- pivot_column ALIAS FOR $2; -- the pivot column (entries to be
> CASEd)
> -- select_column ALIAS FOR $3; -- the select column (entries to be
> grouped)
> -- pivot_tableALIAS FOR $4; -- the name of the table to work on
> -- aggregate_func ALIAS FOR $5; -- the name of the aggregate function
> -- aggr_columnALIAS FOR $6; -- the aggregate column (entries to be
> aggregated)
>
> First try:
> SELECT create_pivot_report
> ('sales_report2','vendor','product','sales','sum','sales');
> SELECT * FROM sales_report2 ;
> gives you 'sales_report2' as a copy of 'sales_report'.
>
> Now add another data set:
> INSERT INTO sales VALUES ( 'butter', 'mr. blue' , 11 ) ;
> Re-write the view by:
> SELECT create_pivot_report
> ('sales_report2','vendor','product','sales','sum','sales');
> And here we go
> SELECT * FROM sales_report2 ;
> product | mr. blue | mr. brown | mr. green | mr. pink | sum of sales
> -+--+---+---+--+--
> butter | 11 | 2 | 0 | 17 | 30
> honey |0 | 0 | 2 | 19 | 21
> milk|0 | 8 |34 | 12 | 54
> (3 rows)
>
> More examples:
> SELECT create_pivot_report
> ('sales_report3','vendor','product','sales','avg','sales');
> SELECT create_pivot_report
> ('sales_report4','vendor','product','sales','stddev','sales');
> SELECT create_pivot_report
> ('sales_report5','product','vendor','sales','sum','sales');
> SELECT create_pivot_report
> ('sales_report6','product','vendor','sales','max','sales');
> SELECT create_pivot_report
> ('sales_report7','vendor','product','sales','max','sales');
>
> As you can see even interchanging the pivot column and the select column
>
> works. Feel free to use the code.
>
> Regards, Christoph
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(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] join and dynamic view
Hi Christoph, Tomasz,
Thanks to you both, I now have:
garytest=# select * from users;
sid | sname | ops | mpd
-+-+-+-
1 | Rod | | 3
2 | Jayne | 5 | 2
3 | Freddie | 3 |
(3 rows)
garytest=# insert into depts values ('A', 'ADM', 'Administrative');
INSERT 237559 1
garytest=# select * from users;
sid | sname | adm | mpd | ops
-+-+-+-+-
1 | Rod | | 3 |
2 | Jayne | | 2 | 5
3 | Freddie | | | 3
(3 rows)
garytest=#
I found that the compile error complaining about the 'OR' was on the
CREATE OR REPLACE FUNCTION
line. I removed the 'OR REPLACE' and everything worked fine.
Also I had to change the returns to 'opaque' and 'return 0' to 'return null'
Thanks again
Gary
On Tuesday 17 Dec 2002 1:45 pm, Christoph Haller wrote:
> > I've now started amending your plpgsql script to create this, but as
>
> you can
>
> > see I've cocked up somewhere. I wonder if you could have a peek at it
>
> for
>
> > me.
>
> Gary,
>
> CREATE OR REPLACE FUNCTION
> create_users_view() returns integer as '
> DECLARE
> pg_views_rtype pg_views%ROWTYPE;
> vname_paramTEXT;
> ranks_record RECORD;
> create_viewTEXT;
> join_text TEXT;
> BEGIN
>
> vname_param:=''users'';
>
> SELECT INTO pg_views_rtype * FROM pg_views WHERE viewname = vname_param;
>
> IF FOUND THEN
> EXECUTE ''DROP VIEW '' || quote_ident(vname_param) ;
> END IF;
> create_view :=
> ''CREATE VIEW '' || quote_ident(vname_param) ||
> '' AS SELECT s.* '';
> join_text:='' from staff s '';
> FOR ranks_record IN
> EXECUTE ''SELECT did, dsdesc from depts ORDER BY did;''
> LOOP
> create_view :=
>create_view || '', '' || ranks_record.did ||
>''.rrank AS '' || ranks_record.dsdesc;
> join_text :=
> join_text || '' left outer join ranks '' || ranks_record.did ||
> '' ON '' || ranks_record.did || ''.rsid = s.sid and '' ||
> ranks_record.did || ''.rdid = '' || quote_literal(ranks_record.did)
> ;
> END LOOP;
> create_view :=
> create_view || join_text || '';'';
> EXECUTE create_view ;
>
> RETURN 0;
> END;
> ' LANGUAGE 'plpgsql' ;
>
> should work.
>
> > I don't think it's good idea to do this, but you can recreate views
> > inside trigger on insert/update into depts.
>
> Tomasz,
> Could you please point out why this is not a good idea. Thanks.
>
> Regards, Christoph
>
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Upgrade question - was Re: [SQL] join and dynamic view
On Tuesday 17 Dec 2002 2:31 pm, Tomasz Myrta wrote: > Gary Stainburn wrote: > > I found that the compile error complaining about the 'OR' was on the > > > > CREATE OR REPLACE FUNCTION > > > > line. I removed the 'OR REPLACE' and everything worked fine. > > OR REPLACE is since postgres 7.2 That explains it - the server I'm developing on is quite old - I didn't realise how old. I'm about to do an upgrade from 7.1.3 to 7.2.1-5 over christmas in fact. Will I need to dump/restore the database for this upgrade? > > > Also I had to change the returns to 'opaque' and 'return 0' to 'return > > null' > > In this case it's ok to "return null", but if you create "before" > trigger you shoud "return new", because "return null" forces postgres > not to insert any data. > > Tomasz Myrta -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] references table(multiple columns go here)
Hi folks,
how do I define a referene from 2 columns in 1 table to 2 columns in another.
I have:
create table ranks (
rid int4 default nextval('ranks_rid_seq'::text) unique not null,
rdidcharacter references depts(did), -- department
rrank int4 not null, -- departmental rank
rdesc character varying(40) -- Rank Description
);
create unique index "ranks_drank_index" on ranks using btree ("rdid",
"rrank");
copy "ranks" from stdin;
1 O 1 Trainee TTI
2 O 2 TTI
3 M 1 Cleaner
4 M 2 Passed Cleaner
5 M 3 Fireman.
\.
I would now like to define the following table so that inserts can only happen
if jdid matches rdid and jrank matches rrank.
create table jobtypes (
jid int4 default nextval('jobs_jid_seq'::text) unique not null,
jdidcharacter references ranks(rdid), -- This joint reference
jrank int4 not null references ranks(rrank), -- needs sorting
jdesc character varying(40) -- job description
);
copy "jobtypes" from stdin;
1 M 3 Charge Cleaner
2 O 3 Lock Carriages
\.
(I want the first row to work and the second to be rejected)
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] references table(multiple columns go here)
Hi Tomasz,
On Wednesday 18 December 2002 4:46 pm, Tomasz Myrta wrote:
> Hello again
>
> Gary Stainburn wrote:
> > Hi folks,
> >
> > how do I define a referene from 2 columns in 1 table to 2 columns in
> > another.
> >
> > I have:
> >
> > create table ranks (
> > rid int4 default nextval('ranks_rid_seq'::text) unique not null,
> > rdidcharacter references depts(did), -- department
> > rrank int4 not null, -- departmental rank
> > rdesc character varying(40) -- Rank Description
> > );
> >
> >
> >
> >
> > I would now like to define the following table so that inserts can
> > only happen
> > if jdid matches rdid and jrank matches rrank.
> >
> > create table jobtypes (
> > jid int4 default nextval('jobs_jid_seq'::text) unique not null,
> > jdidcharacter references ranks(rdid), -- This joint reference
> > jrank int4 not null references ranks(rrank), -- needs sorting
> > jdesc character varying(40) -- job description
>
> !!!here!!! ,CONSTRAINT c2 FOREIGN KEY(jid,jdid) REFERENCES ranks (rid,rdid)
Did this come in with 7.2? I get parse error on or near 'FOREIGN'. Note I
changed the field names to the ones I wanted.
create table jobtypes (
jid int4 default nextval('jobs_jid_seq'::text) unique not null,
jdidcharacter, -- This joint reference
jrank int4 not null references ranks(rrank), -- needs sorting
jdesc character varying(40), -- job description
contraint c2 foreign key (jdid,jrank) references ranks (rdid,rrank)
);
ERROR: parser: parse error at or near "foreign"
>
> > );
>
> that's all
> Tomasz Myrta
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] references table(multiple columns go here)
On Wednesday 18 December 2002 4:56 pm, Gary Stainburn wrote:
> Hi Tomasz,
[snip]
> > > create table jobtypes (
> > > jid int4 default nextval('jobs_jid_seq'::text) unique not null,
> > > jdid character references ranks(rdid), -- This joint reference
> > > jrank int4 not null references ranks(rrank), -- needs sorting
> > > jdesc character varying(40) -- job description
> >
> > !!!here!!! ,CONSTRAINT c2 FOREIGN KEY(jid,jdid) REFERENCES ranks
> > (rid,rdid)
>
> Did this come in with 7.2? I get parse error on or near 'FOREIGN'. Note I
> changed the field names to the ones I wanted.
I've just tried this on a 7.2.1-5 system and get the same error.
>
> create table jobtypes (
> jid int4 default nextval('jobs_jid_seq'::text) unique not null,
> jdid character, -- This joint reference
> jrank int4 not null references ranks(rrank), -- needs sorting
> jdesc character varying(40), -- job description
> contraint c2 foreign key (jdid,jrank) references ranks (rdid,rrank)
> );
> ERROR: parser: parse error at or near "foreign"
>
> > > );
> >
> > that's all
> > Tomasz Myrta
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] references table(multiple columns go here)
Thanks for that Tom
On Wednesday 18 Dec 2002 5:50 pm, Tom Lane wrote:
> Gary Stainburn <[EMAIL PROTECTED]> writes:
> > I've just tried this on a 7.2.1-5 system and get the same error.
> >
> >> create table jobtypes (
> >> jidint4 default nextval('jobs_jid_seq'::text) unique not null,
> >> jdid character, -- This joint reference
> >> jrank int4 not null references ranks(rrank), -- needs sorting
> >> jdesc character varying(40), -- job description
> >> contraint c2 foreign key (jdid,jrank) references ranks (rdid,rrank)
>
>^
>
> >> );
> >> ERROR: parser: parse error at or near "foreign"
>
> If that's an accurate transcription, I think "contraint" -> "constraint"
> would help...
>
That did the trick. However, I now have another problem with the constraint
complaining about there not being an index to refer to. However, there is.
Output below:
create table ranks (
rid int4 default nextval('ranks_rid_seq'::text) unique not null,
rdidcharacter references depts(did), -- department
rrank int4 not null, -- departmental rank
rdesc character varying(40) -- Rank Description
);
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'ranks_rid_key' for
table 'ranks'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
create unique index "ranks_drank_index" on ranks using btree ("rdid",
"rrank");
CREATE
create table jobtypes (
jid int4 default nextval('jobs_jid_seq'::text) unique not null,
jdidcharacter, -- This joint reference
jrank int4 not null references ranks(rrank), -- needs sorting
jdesc character varying(40), -- job description
constraint c2 foreign key (jdid,jrank) references ranks (rdid,rrank)
);
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'jobtypes_jid_key' for
table 'jobtypes'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR: UNIQUE constraint matching given keys for referenced table "ranks" not
found
> regards, tom lane
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] references table(multiple columns go here)
On Thursday 19 Dec 2002 9:58 am, Tomasz Myrta wrote:
> Gary Stainburn wrote:
> > That did the trick. However, I now have another problem with the
> > constraint
> > complaining about there not being an index to refer to. However,
> > there is.
> > Output below:
> >
> > create table ranks (
> > rid int4 default nextval('ranks_rid_seq'::text) unique not null,
> > rdidcharacter references depts(did), -- department
> > rrank int4 not null, -- departmental rank
> > rdesc character varying(40) -- Rank Description
>
> ,CONSTRAINT ranks_pkey PRIMARY KEY (rid,rrank)
> or
> ,CONSTRAINT ranks_unq UNIQUE (rid,rrank)
>
> > );
I added the 2nd constraint but used rdid instead of rid as that's the field I
need the constraint on. I also removed the create unique index statement.
I ended up with the same result tho' - the ranks_unq constraint created an
index with the same definition as the one created by 'create unique index'.
I still get the same error when trying to create the constraint on the
jobtypes table.
>
> Create primary key on two fields in table ranks, or at least create
> unique constraint on them.
>
> If rid is unique, why do you use two fields as foreign key? "rid" is
> enough. You can get rid of "rrank" in table jobtypes.
'rid' is the primary key and is used as a reference from other tables for ease
as much as anything.
The rdid,rrank pair I want as a constraint for data integrity reasons.
>
> And one more question - why you don't use the same names in all tables?
> "did" instead of "did" "rdid" "jdid" ? It's much easier to create joins
> when using the same names.
This is probably because of my background in as a COBOL programmer where
having multiple fields of the same name can cause problems (especially with
MF Cobol which only partially supports it) as well as early (read early 80's)
database experince where it wasn't allowed. Also, I find it usefull because
I know immediately which table a field has come from.
Why does it make joins easier to use the same name for fields?
> Tomasz Myrta
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] references table(multiple columns go here)
On Thursday 19 Dec 2002 11:30 am, Tomasz Myrta wrote:
> Gary Stainburn wrote:
> > On Thursday 19 Dec 2002 9:58 am, Tomasz Myrta wrote:
> > >Gary Stainburn wrote:
> > >>That did the trick. However, I now have another problem with the
> > >>constraint
> > >>complaining about there not being an index to refer to. However,
> > >>there is.
>
> The error is inside declaration of table "ranks.
> You can't create two similiar foreign keys: one based on field (rrank)
> and second one based on fields (rdid,rrank).
> You have to change:
> jrank int4 not null references ranks(rrank), -- needs sorting
> to
> jrank int4 not null, -- needs sorting
Oops, thought I'd removed that one. Sorry. Thanks for all the help here.
SQL's a totally different thought process to anything I'm used to, but I'm
getting there slowly.
>
> > This is probably because of my background in as a COBOL programmer where
> > having multiple fields of the same name can cause problems (especially
> > with
> > MF Cobol which only partially supports it) as well as early (read
> > early 80's)
> > database experince where it wasn't allowed. Also, I find it usefull
> > because
> > I know immediately which table a field has come from.
>
> As you wish.
>
> > Why does it make joins easier to use the same name for fields?
>
> If you create queries like this, you get rid of duplicates.
> select *
> from
> jobtypes
> join departments using (did)
I see your point. Maybe I'll have to rethink a bit. As I said, I'm having to
do a lot of rethinking.
>
> If you are afraid of duplicates, you can always use an alias:
> select
> ranks.rank_id as rid,
> ...
>
> If you want, here is my minimal version of your tables:
>
> create table depts (
> dept_id int4 primary key,
> ...
> };
>
> create table ranks (
> rank_id int4 default nextval('ranks_rid_seq') primary key,
> dept_id int4 references depts, -- department
> rank int4 not null, -- departmental rank
> rdesc character varying(40) -- Rank Description
> );
>
> create table jobtypes (
> jobtype_idint4 default nextval('jobs_jid_seq') primary key,
> rank_id int4 references ranks(rank_id),
> jdesc character varying(40) -- job description
> );
>
> Tomasz Myrta
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] references table(multiple columns go here)
On Thursday 19 Dec 2002 3:17 pm, Tom Lane wrote: > Gary Stainburn <[EMAIL PROTECTED]> writes: > > That did the trick. However, I now have another problem with the > > constraint complaining about there not being an index to refer to. > > However, there is. > > No there isn't: > > jrank int4 not null references ranks(rrank), -- needs sorting > > ^^^ > > You have no index constraining rrank (by itself) to be unique. Thanks for that Tom. The reason that I didn't have that index is because rrank is not unique thus the need for the 2 field constraint that started this thread. I'd just forgot to remove the references clause. Thanks to you too for your help. Between you and Tomasz, I'm making pretty good progress on what is (not very) slowly turning from a small project to the largest database project I've done to date. > > regards, tom lane -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Help on (sub)-select
Hi Folks, I have two tables roster holds the duties to be performed and the number of people required per duty. roster_staff holds the people allocated to perform that duty. I'm trying to create a select that will tally the roster_staff and include it with the roster details. I've managed to get it working for a specific day, but I can't seem to manage to get it working generically. here's the select I've got that works, along with the output: nymr=# select r.*, s.tally from roster r, nymr-# (select count(*) as tally from roster_staff where nymr(# rsdate = '2002-01-01' and rsgid = 11 and rsgsid = 2) as s nymr-# where rodate = '2002-01-01' and rogid = 11 and rogsid = 2; rodate | rogid | rogsid | rorequired | rooptional | tally +---++++--- 2002-01-01 |11 | 2 | 0 | 1 | 2 (1 row) nymr=# What I want to be able to do is select multiple rows and have the correct tally appear for that row. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Help on (sub)-select
On Friday 20 Dec 2002 10:51 am, Philip Warner wrote: > At 10:21 AM 20/12/2002 +0000, Gary Stainburn wrote: > >nymr=# select r.*, s.tally from roster r, > >nymr-# (select count(*) as tally from roster_staff where > >nymr(# rsdate = '2002-01-01' and rsgid = 11 and rsgsid = 2) as s > >nymr-# where rodate = '2002-01-01' and rogid = 11 and rogsid = 2; > >rodate | rogid | rogsid | rorequired | rooptional | tally > >+---++++--- > > 2002-01-01 |11 | 2 | 0 | 1 | 2 > >(1 row) > > Try something like: > > select r.*, count(*) from roster r, roster_staff s > where rsdate = rodate and rsgid = rogid and rsgsid = rogid > group by r.* This one came up with a parser error near '*' but I don't understand it enough to debug it. > > or > > select r.*, (select count(*) from roster_staff s > where rsdate = rodate and rsgid = rogid and rsgsid = rogid > ) roster r; This one ran, but the count column had the same value in every row - the total count for the table. I've managed it using an intermediate view. I've also extended it to show everything I need - see below. I'd still like to hear from anyone who could tell me how I can do this without the intermediate view tho' create table roster ( -- roster definition table - holding jobs to be done rodate date not null, rogid int4 references diagrams(gid), -- diagram rogsid int4 references jobtypes(jid), -- jobtype rorequired int4, -- essential staff rooptional int4, -- optional staff primary key (rodate, rogid, rogsid) ); create table roster_staff ( -- people on the roster rsdate date not null, rsgid int4 references diagrams(gid), -- diagram rsgsid int4 references jobtypes(jid), -- jobtype rssid int4 references staff(sid), -- staff id. constraint r2 foreign key (rsdate,rsgid,rsgsid) references roster (rodate,rogid,rogsid) ); create view roster_tally as select rsdate, rsgid, rsgsid, count(*) as rocount from roster_staff group by rsdate, rsgid, rsgsid; create view roster_details as select r.*, coalesce(t.rocount,0) as rocount, coalesce(a.rocount,0) as roavail from roster r left outer join roster_tally t on r.rodate = t.rsdate and r.rogid = t.rsgid and r.rogsid = t.rsgsid left outer join roster_tally a on r.rodate = a.rsdate and a.rsgid is null and r.rogsid = a.rsgsid; nymr=# select * from roster_details where rocount < rorequired and roavail > 0; rodate | rogid | rogsid | rorequired | rooptional | rocount | roavail +---++++-+- 2002-01-01 |12 | 4 | 1 | 0 | 0 | 1 (1 row) nymr=# > > May not be exactly right, but you should get the idea > > > > Philip Warner| __---_ > Albatross Consulting Pty. Ltd. |/ - \ > (A.B.N. 75 008 659 498) | /(@) __---_ > Tel: (+61) 0500 83 82 81 | _ \ > Fax: (+61) 03 5330 3172 | ___ | > Http://www.rhyme.com.au | / \| > > |---- > > PGP key available upon request, | / > and from pgp5.ai.mit.edu:11371 |/ -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] COPY fails but INSERT works
Hi folks,
I've got three tables (amongst others). The ranks table holds the various
ranks that people can hold within each department.
The jobtypes table holds the various jobs that need doing.
The abilities holds the relationship defining which ranks carry out which
jobs.
When I create the database, I use the COPY command. The ranks and jobtypes
are populated okay but the abilities table is empty. However, if I then
INSERT the data the inserts work fine.
Anyone got any ideas why?
create table ranks (-- staff promotion ladders by dept.
rid int4 default nextval('ranks_rid_seq'::text) unique not null,
rdidcharacter references depts(did), -- department
rrank int4 not null, -- departmental rank
rdesc character varying(40), -- Rank Description
constraint ranks_rank unique (rdid, rrank)
);
create unique index rk_index on ranks using btree ("rid", "rdid");
create table jobtypes ( -- jobs that require doing
jid int4 default nextval('jobs_jid_seq'::text) unique not null,
jdidcharacter references depts(did), -- Department ID
jdesc character varying(40) -- job description
);
create unique index jt_index on jobtypes using btree ("jid", "jdid");
create table abilities (-- defines which jobtypes ranks are able for
ejidint4 not null, -- jobtype ID
edidcharacter not null, -- dept ID
eridint4, -- rank ID
constraint c2 foreign key (ejid, edid) references jobtypes (jid, jdid),
constraint c2 foreign key (erid, edid) references ranks (rid, rdid)
);
copy "ranks" from stdin;
1 F 1 Cleaner
2 F 2 Passed Cleaner
3 F 3 Fireman
4 F 4 Passed Fireman
5 F 5 Diesel Driver
6 F 6 Driver
7 F 7 Inspector
8 O 1 Trainee TTI
9 O 2 Ticket Inspector
10 O 3 Trainee Guard
11 O 4 Guard
12 M 1 Volunteer
13 M 2 Apprentice
14 M 3 Fitter
15 M 4 Charge Fitter
16 M 5 Manager
17 A 1 Admin Staff
18 A 2 Roster Admin
19 A 3 Webmaster
20 S 1 Station Staff
21 S 2 Station Foreman
\.
copy "jobtypes" from stdin;
1 F Cleaner
2 F Ride-Out
3 F Fireman
4 F Driver
5 F Charge Cleaner
6 O Guard
8 M Duty Fitter
\.
copy "abilities" from stdin;
1 F 1
2 F 1
3 F 2
3 F 3
4 F 4
4 F 5
4 F 6
5 F 3
5 F 4
6 O 3
8 M 3
\.
nymr=# select count(*) from ranks;
count
---
21
(1 row)
nymr=# select count(*) from jobtypes;
count
---
7
(1 row)
nymr=# select count(*) from abilities;
count
---
0
(1 row)
nymr=# insert into abilities values (1, 'F', 1);
INSERT 404593 1
nymr=# select count(*) from abilities;
count
---
1
(1 row)
nymr=#
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] COPY fails but INSERT works
On Saturday 28 December 2002 3:48 pm, Stephan Szabo wrote:
> On Sat, 28 Dec 2002, Gary Stainburn wrote:
> > When I create the database, I use the COPY command. The ranks and
> > jobtypes are populated okay but the abilities table is empty. However,
> > if I then INSERT the data the inserts work fine.
>
> Do *all* of the inserts work? If any one of the rows fails I believe
> the entire copy fails. Specifically, unless I miss something:
>
> inserting into abilities
> ejid edid erid
> 6 O 3
>
> So looking for
>(6,'O') in jobtypes
> and
>(3,'O') in ranks.
>
> I don't see the latter row so you're violating the constraint I believe.
> Are you sure you want to reference (rid, rdid) in ranks and not
> (rrank, rdid)?
Hi Stephan,
Thanks for this. The constraint was correct but the data was wrong. I've
fixed it and it's now working.
Gary
>
> > Anyone got any ideas why?
> >
> > create table ranks (-- staff promotion ladders by dept.
> > rid int4 default nextval('ranks_rid_seq'::text) unique not null,
> > rdidcharacter references depts(did), -- department
> > rrank int4 not null, -- departmental rank
> > rdesc character varying(40), -- Rank Description
> > constraint ranks_rank unique (rdid, rrank)
> > );
> > create unique index rk_index on ranks using btree ("rid", "rdid");
> >
> > create table jobtypes ( -- jobs that require doing
> > jid int4 default nextval('jobs_jid_seq'::text) unique not null,
> > jdidcharacter references depts(did), -- Department ID
> > jdesc character varying(40) -- job description
> > );
> > create unique index jt_index on jobtypes using btree ("jid", "jdid");
> >
> > create table abilities (-- defines which jobtypes ranks are able for
> > ejidint4 not null, -- jobtype ID
> > edidcharacter not null, -- dept ID
> > eridint4, -- rank ID
> > constraint c2 foreign key (ejid, edid) references jobtypes (jid, jdid),
> > constraint c2 foreign key (erid, edid) references ranks (rid, rdid)
> > );
> >
> > copy "ranks" from stdin;
> > 1 F 1 Cleaner
> > 2 F 2 Passed Cleaner
> > 3 F 3 Fireman
> > 4 F 4 Passed Fireman
> > 5 F 5 Diesel Driver
> > 6 F 6 Driver
> > 7 F 7 Inspector
> > 8 O 1 Trainee TTI
> > 9 O 2 Ticket Inspector
> > 10 O 3 Trainee Guard
> > 11 O 4 Guard
> > 12 M 1 Volunteer
> > 13 M 2 Apprentice
> > 14 M 3 Fitter
> > 15 M 4 Charge Fitter
> > 16 M 5 Manager
> > 17 A 1 Admin Staff
> > 18 A 2 Roster Admin
> > 19 A 3 Webmaster
> > 20 S 1 Station Staff
> > 21 S 2 Station Foreman
> > \.
> >
> > copy "jobtypes" from stdin;
> > 1 F Cleaner
> > 2 F Ride-Out
> > 3 F Fireman
> > 4 F Driver
> > 5 F Charge Cleaner
> > 6 O Guard
> > 8 M Duty Fitter
> > \.
> >
> > copy "abilities" from stdin;
> > 1 F 1
> > 2 F 1
> > 3 F 2
> > 3 F 3
> > 4 F 4
> > 4 F 5
> > 4 F 6
> > 5 F 3
> > 5 F 4
> > 6 O 3
> > 8 M 3
> > \.
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] sub-select, view and sum()
Hi folks,
I've got 3 tables (plus others), and want to create a view joining them.
Below are the two main tables and the view I'm trying to create. Anyone, got
any idea how I need to word the 'create view'
create table turns (-- Turns Table. Hold details of my turns
tid int4 default nextval('turns_tid_seq'::text) unique not null,
tdate date, -- date of turn
tseqint4, -- sheet reference number
ttype char references ttypes(ttid), -- Turn type
tfitter int4 references staff(sid), -- fitter or driver
tccleaner int4 references staff(sid), -- charge cleaner or fireman
tcomments text-- free type description of turn
);
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'turns_tid_key' for
table 'turns'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
create table rides (-- work details list by turn/category + mileage
rtidint4 references turns(tid), -- TID of associated turn
rlidint4 references locos(lid), -- LID of associated engine
rcidcharacter references categories(cid), -- CID of category
rmiles int4-- miles travelled on ride-out
);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
create unique index "rides_index" on rides using btree ("rtid", "rlid",
"rcid");
CREATE
create view turn_details as
select t.*, d.sid as dsid, d.sname as dname,
f.sid as fsid, f.sname as fname,
(select sum(r.rmiles) as rmiles from rides r where r.rtid = tid)
as rmiles
from turns t
left outer join staff d on t.tfitter = d.sid
left outer join staff f on t.tccleaner = f.sid
where r.rtid = t.tid
order by tdate;
ERROR: Relation 'r' does not exist
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(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] sub-select, view and sum()
On Monday 06 January 2003 6:31 pm, Stephan Szabo wrote: > On Mon, 6 Jan 2003, Gary Stainburn wrote: > > create view turn_details as > > select t.*, d.sid as dsid, d.sname as dname, > > f.sid as fsid, f.sname as fname, > > (select sum(r.rmiles) as rmiles from rides r where r.rtid = tid) > > as rmiles > > from turns t > > left outer join staff d on t.tfitter = d.sid > > left outer join staff f on t.tccleaner = f.sid > > where r.rtid = t.tid > > order by tdate; > > ERROR: Relation 'r' does not exist > > Do you really need the outer where r.rtid=t.tid? I would think that the > subselect where clause would already handle that for you. Hi Stephan, Thanks for that. That was the problem. I'd left it after trying to use a normal join in an earlier attempt. Gary -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] returning setof in plpgsql
On Tuesday 21 Jan 2003 10:40 am, David Durst wrote:
> I have a function that I want to return setof a table in plpgsql.
>
> Here is what I have:
>
> CREATE FUNCTION lookup_account(varchar(32)) RETURNS SETOF accounts AS '
> DECLARE
> aname ALIAS FOR $1;
> rec RECORD;
> BEGIN
> select into rec * from accounts where accountname = aname;
> return rec;
> END;'
> LANGUAGE 'plpgsql';
>
> This seems to hang when I attempt to select it using:
>
> select accountid(
> lookup_account('some account')),
> accountname(lookup_account('some account')),
> type(lookup_account('some account')),
> balance(lookup_account('some account'));
>
> Does anyone see a problem w/ my approach??
Hi David,
I've never done this but I seem to remember seeing something about this
recently.
Firstly, I think you need 7.3.1 to do this.
You then have to create a 'type' as being a set of your table. You then
define the function as returning that type.
Sorry I can't be more specific, but as I said, I've never done it.
Gary
>
>
>
>
> ---(end of broadcast)-------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] CSV import
On Wednesday 29 January 2003 5:50 am, Oliver Vecernik wrote: > Oliver Vecernik schrieb: > > Hi again! > > > > After investigating a little bit further my CSV import couldn't work > > because of following reasons: > > > > 1. CSV files are delimited with CR/LF > > 2. text fields are surrounded by double quotes > > > > Is there a direct way to import such files into PostgreSQL? Here's a simple command that will take "hello","world","splat","diddle" "he said "hello world" to ","his mate" and convert it to the following tab delimited file that can be COPYed using psql. It even handles quotes inside fields. (^m and ^i are done by typing CTRL+V CTRL+M and CTRL+V CTRL+I) hello world splat diddle he said "hello world" tohis mate sed 's/^"//' t1.txt Gary > > The answer seems to be no. But after googeling a bit a found a wonderful > Python module called csv at: > > http://www.object-craft.com.au/projects/csv/ > > A minimal script called 'csv2tab.py' for conversion to a tab delimited > file could be: > > #!/usr/bin/env python > > import csv > import sys > > def convert(file): > try: > f = open(file, 'r') > lines = f.readlines() > p = csv.parser() > for line in lines: > print '\t'.join(p.parse(line)) > except: > print 'Error opening file!' > > if __name__ == '__main__': > convert(sys.argv[1]); > > Regards, > Oliver -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] efficient count/join query
On Friday 07 Feb 2003 1:26 pm, Tomasz Myrta wrote: > Gary Stainburn wrote: > > On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote: > > > > > Hi Tomasz, > > [snip] > > I understood your problem well and I just thought some idea will be enough > to continue work. > > Here is detailed query for your problem: > > create view some_view as > select > coalesce(hjid,rjid) as jid, > coalesce(hsid,rsid) as sid, > coalesce(hcount,1)+count(*)-1 as hcount > from > history > full outer join roster on (hjid=rjid and hsid=rosid) > group by hjid,rjid,hsid,rosid; > > Regards, > Tomasz Myrta Many appologoes Tomasz. Because your select *LOOKED* wrong to me, I didn't even try it. Upon looking at it again I can see what you're doing. When I tried, it complained about the counts and grouping, so I moved the count(*) to a sub-select and changed the coalesce and it's working. Thanks, Gary > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Generating serial number column
On Thursday 13 Feb 2003 4:21 pm, Mintoo Lall wrote:
> Hi Everybody ,
>
> Is there a way in postgresql by which I can generate serial number column
> for the resultset. I wanted the resulset to look like below
>
>
> sno Name
> ---
> 1JOE
> 2JOHN
> 3MARY
> 4LISA
> 5ANN
> 6BILL
> 7JACK
> 8WILL
> 9GEORGE
> 10 NANCY
> 11 JANE
> ..
> ..
> ..
>
> My query is basically select * name from tblcontact. I wanted to generate
> the column "sno" which basically numbers each row returned. Any help is
> appreciated.
> Regards,
> Tarun
create sequence myseq;
select nextval('myseq'), name from tblcontact;
drop sequence myseq;
There may well be a way to do it without the sequence, bit I can't think of
one.
Gary
>
>
>
> -----
> Do you Yahoo!?
> Yahoo! Shopping - Send Flowers for Valentine's Day
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Table Pivot
On Thursday 13 Feb 2003 10:48 am, V. Cekvenich wrote: > How do you do a table Pivot in PostgreSQL? Hi, I've noticed that you've posted this twice now with no response. I can't help you because I don't know what you mean by doing a 'table pivot', but maybe if you describe what you're trying to do I'll see if I can help Gary > > tia, > .V > > > = > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] efficient count/join query
Hi folks, I've got two tables, first a history table containing tallies for staff/jobs prior to going live, and second a roster table showing date, diagram, job with one record per person per job per day. the tables are: create table history ( hsidint4 not null references staff(sid), hjidint4 not null references jobs(jid), hcount int4, primary key (hsid,hjid)); create table roster ( rodate date not null, rogid int4 not null references diagrams(gid), rojid int4 not null references jobs(jid), rosid int4 references staff(sid), primary key (rodate, rogid, rojid)); What's the best/quickest/cheapest way to create a view in the format of the history table but including the details from the roster table for all records prior to today. I've been looking at some form of sub-select/join scheme but as some will only exist on the history and some will only exist on the roster while many will exist on both. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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] efficient count/join query
On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote: > Gary Stainburn wrote: > > Hi folks, > > > > I've got two tables, first a history table containing tallies for > > staff/jobs prior to going live, and second a roster table showing date, > > diagram, job with one record per person per job per day. the tables are: > > > > create table history ( > > hsidint4 not null references staff(sid), > > hjidint4 not null references jobs(jid), > > hcount int4, > > primary key (hsid,hjid)); > > > > create table roster ( > > rodate date not null, > > rogid int4 not null references diagrams(gid), > > rojid int4 not null references jobs(jid), > > rosid int4 references staff(sid), > > primary key (rodate, rogid, rojid)); > > > > What's the best/quickest/cheapest way to create a view in the format of > > the history table but including the details from the roster table for all > > records prior to today. > > > > I've been looking at some form of sub-select/join scheme but as some will > > only exist on the history and some will only exist on the roster while > > many will exist on both. > > Hello again. > > What if they exists in both tables - you need only one row result? > If yes, you should use FULL OUTER JOIN and COALESCE. > > select > coalesce(hjid,rjid) as jid, > coalesce(hsid,rsid) as sid, > hcount, > rodate, > rogid > from > history > full outer join roster on (hjid=rjid and hsid=rosid) > > Using other names for the same field in other tables comes again - > If you have the same name for jid and sid, you wouldn't need coalesce. > > Regards, > Tomasz Myrta Hi Tomasz, I don't think you understand what I mean. The history table could be thought of as the following SQL statement if the data had actually existed. This table actually represents a manually input summary of the pre-computerised data. insert into history select rosid, rojid, count(*) from roster_staff group by rssid, rsgsid; If I have a history of hsid | hjid | hcount --+--+ 1 |2 | 3 1 |3 | 1 5 |5 | 4 6 |5 | 3 9 |4 | 4 14 |5 | 4 and I have a roster of rodate | rogid | rojid | rosid ---+---+---+--- 2003-02-15 | 1 | 2 | 1 2003-02-15 | 1 | 5 | 5 2003-02-16 | 1 | 5 | 1 I want my view to show hsid | hjid | hcount --+--+ 1 |2 | 4 1 |3 | 1 1 |5 | 1 5 |5 | 5 6 |5 | 3 9 |4 | 4 14 |5 | 4 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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] efficient count/join query
On Friday 07 Feb 2003 12:09 pm, Gary Stainburn wrote: > On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote: > > Gary Stainburn wrote: > > > Hi folks, > > > > > > I've got two tables, first a history table containing tallies for > > > staff/jobs prior to going live, and second a roster table showing date, > > > diagram, job with one record per person per job per day. the tables > > > are: > > > > > > create table history ( > > > hsid int4 not null references staff(sid), > > > hjid int4 not null references jobs(jid), > > > hcountint4, > > > primary key (hsid,hjid)); > > > > > > create table roster ( > > > rodatedate not null, > > > rogid int4 not null references diagrams(gid), > > > rojid int4 not null references jobs(jid), > > > rosid int4 references staff(sid), > > > primary key (rodate, rogid, rojid)); > > > > > > What's the best/quickest/cheapest way to create a view in the format of > > > the history table but including the details from the roster table for > > > all records prior to today. > > > > > > I've been looking at some form of sub-select/join scheme but as some > > > will only exist on the history and some will only exist on the roster > > > while many will exist on both. > > > > Hello again. > > > > What if they exists in both tables - you need only one row result? > > If yes, you should use FULL OUTER JOIN and COALESCE. > > > > select > > coalesce(hjid,rjid) as jid, > > coalesce(hsid,rsid) as sid, > > hcount, > > rodate, > > rogid > > from > > history > > full outer join roster on (hjid=rjid and hsid=rosid) > > > > Using other names for the same field in other tables comes again - > > If you have the same name for jid and sid, you wouldn't need coalesce. > > > > Regards, > > Tomasz Myrta > > Hi Tomasz, > > I don't think you understand what I mean. > > The history table could be thought of as the following SQL statement if the > data had actually existed. This table actually represents a manually input > summary of the pre-computerised data. > > insert into history > select rosid, rojid, count(*) from roster_staff group by rssid, rsgsid; > > If I have a history of > > hsid | hjid | hcount > --+--+ > 1 |2 | 3 > 1 |3 | 1 > 5 |5 | 4 > 6 |5 | 3 > 9 |4 | 4 >14 |5 | 4 > > and I have a roster of > > rodate | rogid | rojid | rosid > ---+---+---+--- > 2003-02-15 | 1 | 2 | 1 > 2003-02-15 | 1 | 5 | 5 > 2003-02-16 | 1 | 5 | 1 > > I want my view to show > > hsid | hjid | hcount > --+--+ > 1 |2 | 4 > 1 |3 | 1 > 1 |5 | 1 > 5 |5 | 5 > 6 |5 | 3 > 9 |4 | 4 >14 |5 | 4 Thinking about it, I'm not wanting to perform a join as such, but a merge of the two selects below, then some form of group by to sum() the two counts. select rosid as sid, rojid as jid, count(*) as count from roster group by sid, jid order by sid, jid; select hsid as sid, hjid as jid, hcount as count from history order by sid, jid; so that 1 2 1 1 3 2 and 1 3 1 1 4 2 becomes 1 2 1 1 3 3 1 4 2 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Insert multiple Rows
On Tuesday 04 Mar 2003 10:54 am, Oleg Samoylov wrote:
> Hi,
>
> Saint X wrote:
> > Hi, I have a program that need to send a Postgresql database a lot of
> > rows, i'm doing it using FOR an INSERT, something like this
> > for i = 0 to 1000
> > {
> > insert into table(item1) VALUES (i);
> > }
> >
> > And so on, as you can imagine these consume a lot of resources and
> > move so slowly, that's why I'm looking for a command to send more than
> > one row at the time, something like
> >
> > insert into table (item1) values ('1' / '2' / '3' / '4' ... / '1000');
>
> You can do instead:
>
> insert into table (item1) values ('1');
> insert into table (item1) values ('2');
>
> insert into table (item1) values ('1000');
>
> As single query.
>
> But better use COPY command or prepared statment.
>
> Inserts slow commonly due to indexis and foreing keys.
If you use a 'prepared' insert within a transaction, would that speed things
up - maybe by defering index updates?
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] sort / limit / range problem
Hi folks,
I've got a glossary table that I'm trying to render to HTML. However, I've
got a problem when using order by and limit. Below is an example of a psql
session showing my problem. Anyone got any reasons why the last select misses
'Driver'?
nymr=# select glterm from glossary where glterm like 'D%' order by glterm;
glterm
Dampers
Dart
Detonators
Disposal
Dome
Draw Bar
Driver
Driving Wheels
Duty Fitter
(9 rows)
nymr=# select glterm from glossary where glterm > 'Driving Wheels' limit 1;
glterm
-
Duty Fitter
(1 row)
nymr=# select glterm from glossary where glterm > 'Draw Bar' limit 1;
glterm
Driving Wheels
(1 row)
nymr=# \d glossary
Table "glossary"
Attribute | Type | Modifier
---+---+-
glid | integer | not null default
nextval('glossary_glid_seq'::text)
glterm| character varying(30) | not null
gldesc| text |
Indices: glossary_pkey,
glossary_term_index
nymr=#
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] create view error
Hi folks,
I know I'm missing something blindingly obvious, can someone point it out to
me please.
create table locos (-- Locos table - contains details of locos
lid int4 default nextval('loco_lid_seq'::text) unique not null,
lclass int4 references lclass(lcid), -- Loco Class
lbuilt date, -- Date off-shed
lcmeint4 references cme(cmid), -- Chief Mechanical Engineer
lname character varying(30), -- Name of Loco
lcomments text-- free text comments
);
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'locos_lid_key' for
table 'locos'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
create table lnumbers ( -- alternate loco numbers
lnidint4 not null references locos(lid),
lnumber character varying(10),
lncurrent bool,
primary key (lnid, lnumber)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'lnumbers_pkey'
for table 'lnumbers'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
create view loco_dets as
select * from locos l
left outer join
(select * from lclass) lc on lc.lcid = l.lclass
left outer join
(select lnumber from lnumbers) ln on ln.lnid = l.lid and ln.lncurrent
= true
left outer join
(select * from company) c on c.coid = lc.lcompany;
ERROR: No such attribute or function ln.lnid
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 8: explain analyze is your friend
Concat and view - Re: [SQL] create view error
On Monday 07 Jul 2003 1:07 pm, Richard Huxton wrote:
> On Monday 07 Jul 2003 12:40 pm, Gary Stainburn wrote:
> > left outer join
> > (select lnumber from lnumbers) ln on ln.lnid = l.lid and
>
> ^^^ ^^^
>
> > ERROR: No such attribute or function ln.lnid
>
> Is is this?
Yup, thanks to both of you for this answer.
Is there any way to do this so that lnid is not visible in the resulting view?
Also, using examples from this list, I've created a concat function and
aggregate so that I can convert a number of rows to a comma delimited string.
I can then use this in a select as shown below, but what I can't work out is
how to put this into my join.
I want to include the second of the two selects shown below (the one with
'lncurrent = true' where clause) into my view (shown at bottom).
I can't work out where to put the where and group by clauses.
nymr=# select lnid, concat(lnumber) as lnalternate from lnumbers group by
lnid;
lnid | lnalternate
--+--
1 | 29
2 | 2392,65894
3 | 4277
4 | 80135
5 | 30926,926
6 | 45212
7 | 44767
8 | 60532
9 | 75014
10 | 75029
11 | 60007
12 | 25 278,D7628
13 | 08850,4518
14 | 62005,62012
15 | 24 061,D5061
16 | 45337
17 | 6619
18 | 64360,901
19 | 5
20 | 825
21 | 45157
22 | 76079
23 | 4771,60800
24 | 55019,D9019
25 | D9009
26 | 08556,D3723
(26 rows)
nymr=# select lnid, concat(lnumber) as lnalternate from lnumbers where
lncurrent = false group by lnid;
lnid | lnalternate
--+-
2 | 2392
5 | 926
12 | 25 278
13 | 08850
14 | 62012
18 | 64360
23 | 4771
24 | D9019
26 | D3723
(9 rows)
nymr=#
create view loco_dets as
select * from locos l
left outer join
lclass lc on lc.lcid = l.lclass
left outer join
lnumbers n on n.lnid = l.lid and n.lncurrent = true
left outer join
(select lnid, concat(lnumber) as lnalternate, lncurrent from lnumbers
) na on na.lnid = l.lid and na.lncurrent = false
left outer join
company c on c.coid = lc.lcompany;
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: Concat and view - Re: [SQL] create view error
On Monday 07 Jul 2003 1:36 pm, you wrote: > On Monday 07 Jul 2003 1:07 pm, Richard Huxton wrote: > > On Monday 07 Jul 2003 12:40 pm, Gary Stainburn wrote: > > > left outer join > > > (select lnumber from lnumbers) ln on ln.lnid = l.lid and > > > > ^^^ ^^^ > > > > > ERROR: No such attribute or function ln.lnid > > > > Is is this? > > Yup, thanks to both of you for this answer. > > Is there any way to do this so that lnid is not visible in the resulting > view? > I've managed to get the view I wanted by using sub-selects - as shown below, but I now have the 'lid' field showing in the resulting view three times (as lid, lnid and lnaid). How can I remove lnid and lnaid from the result? create view loco_dets as select * from locos l left outer join lclass lc on lc.lcid = l.lclass left outer join lnumbers n on n.lnid = l.lid and n.lncurrent = true left outer join (select lnid as lnaid, concat(lnumber) as lnalternate from (select lnid, lnumber from lnumbers where lncurrent = false order by lnid, lnumber) alt group by lnaid) na on na.lnaid = l.lid left outer join company c on c.coid = lc.lcompany; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Concat and view - Re: [SQL] create view error
On Monday 07 Jul 2003 3:34 pm, Richard Huxton wrote: > On Monday 07 Jul 2003 2:12 pm, Gary Stainburn wrote: > > I've managed to get the view I wanted by using sub-selects - as shown > > below, but I now have the 'lid' field showing in the resulting view three > > times (as lid, lnid and lnaid). How can I remove lnid and lnaid from the > > result? > > > > create view loco_dets as > > select * from locos l > > left outer join > > [snip] > > Don't do "select *" do "select field_a,field_b..." - the * doesn't just > refer to the locos table. Sorry if I didn't make myself plain enough, but I had create view loco_dets as select * from locos l -- includes lid which I want left outer join (select lnid, lnumber...) ln on ln.lnid = l.lid ... The problem is that I have to have lnid in the sub-select to allow the 'on' clause to work, but I don't want lnid to appear in the resulting view. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] numerical sort on mixed alpha/numeric data
Hi folks, I've got a table holding loco numbers and an id which references the locos table. How can I sort this table, so that numeric values appear first in numerical order followed by alpha in alpha order.? nymr=# \d lnumbers Table "lnumbers" Column | Type | Modifiers ---+---+--- lnid | integer | not null lnumber | character varying(10) | not null lncurrent | boolean | Primary key: lnumbers_pkey Triggers: RI_ConstraintTrigger_7121182 nymr=# select * from lnumbers order by lnumber; lnid | lnumber | lncurrent --+-+--- 26 | 08556 | t 13 | 08850 | f 2 | 2392| f 15 | 24 061 | t 12 | 25 278 | f 1 | 29 | t 5 | 30926 | t 3 | 4277| t 7 | 44767 | t 21 | 45157 | t 13 | 4518| t 6 | 45212 | t 16 | 45337 | t 23 | 4771| f 19 | 5 | t 24 | 55019 | t 27 | 59 | f 11 | 60007 | t 8 | 60532 | t 23 | 60800 | t 14 | 62005 | t 14 | 62012 | f 18 | 64360 | f 2 | 65894 | t 17 | 6619| t 27 | 69023 | t 9 | 75014 | t 10 | 75029 | t 22 | 76079 | t 4 | 80135 | t 20 | 825 | t 18 | 901 | t 5 | 926 | f 26 | D3723 | f 15 | D5061 | t 12 | D7628 | t 25 | D9009 | t 24 | D9019 | f (38 rows) nymr=# -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] numerical sort on mixed alpha/numeric data
On Wednesday 16 July 2003 3:27 pm, Dmitry Tkach wrote: > Gary Stainburn wrote: > >Hi folks, > > > >I've got a table holding loco numbers and an id which references the locos > >table. How can I sort this table, so that numeric values appear first in > >numerical order followed by alpha in alpha order.? > > What about > > select lnid,lnumber,lncurrent from > (select *, case when lnumber ~ '^[0-9]+' then lnumber::int else null > end as number from lnumber) > order by number, lnumber > > > I hope, it helps... > > Dima Hi, thanks for this. I had to alias the sub-select, and the cast from varchar to int didn't work, below is the working version. select lnid,lnumber,lncurrent from (select *, case when lnumber ~ '^[0-9]+' then lnumber::text::int else null end as number from lnumbers) foo order by number, lnumber; Gary > > >nymr=# \d lnumbers > > Table "lnumbers" > > Column | Type | Modifiers > >---+---+--- > > lnid | integer | not null > > lnumber | character varying(10) | not null > > lncurrent | boolean | > >Primary key: lnumbers_pkey > >Triggers: RI_ConstraintTrigger_7121182 > > > >nymr=# select * from lnumbers order by lnumber; > > lnid | lnumber | lncurrent > >--+-+--- > > 26 | 08556 | t > > 13 | 08850 | f > >2 | 2392| f > > 15 | 24 061 | t > > 12 | 25 278 | f > >1 | 29 | t > >5 | 30926 | t > >3 | 4277| t > >7 | 44767 | t > > 21 | 45157 | t > > 13 | 4518| t > >6 | 45212 | t > > 16 | 45337 | t > > 23 | 4771| f > > 19 | 5 | t > > 24 | 55019 | t > > 27 | 59 | f > > 11 | 60007 | t > >8 | 60532 | t > > 23 | 60800 | t > > 14 | 62005 | t > > 14 | 62012 | f > > 18 | 64360 | f > >2 | 65894 | t > > 17 | 6619 | t > > 27 | 69023 | t > >9 | 75014 | t > > 10 | 75029 | t > > 22 | 76079 | t > >4 | 80135 | t > > 20 | 825 | t > > 18 | 901 | t > >5 | 926 | f > > 26 | D3723 | f > > 15 | D5061 | t > > 12 | D7628 | t > > 25 | D9009 | t > > 24 | D9019 | f > >(38 rows) > > > >nymr=# -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] unique value - trigger?
Hi folks, I'm back with my lnumbers table again. nymr=# \d lnumbers Table "lnumbers" Column | Type | Modifiers ---+---+--- lnid | integer | not null lnumber | character varying(10) | not null lncurrent | boolean | Primary key: lnumbers_pkey Triggers: RI_ConstraintTrigger_7575462 While each loco can have a number of different numbers, only one can be current at any one time. I want to make it so that if I set lncurrent to true for one row, any existing true rows are set to false. I'm guessing that I need to create a trigger to be actioned after an insert or update which would update set lncurrent=false where lnid not = but I can't seem to sus it put. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] unique value - trigger?
On Thursday 17 Jul 2003 3:34 pm, Dmitry Tkach wrote: > Gary Stainburn wrote: > >Hi folks, > > > >I'm back with my lnumbers table again. > > > >nymr=# \d lnumbers > > Table "lnumbers" > > Column | Type | Modifiers > >---+---+--- > > lnid | integer | not null > > lnumber | character varying(10) | not null > > lncurrent | boolean | > >Primary key: lnumbers_pkey > >Triggers: RI_ConstraintTrigger_7575462 > > > >While each loco can have a number of different numbers, only one can be > >current at any one time. > > > >I want to make it so that if I set lncurrent to true for one row, any > > existing true rows are set to false. > > > >I'm guessing that I need to create a trigger to be actioned after an > > insert or update which would update set lncurrent=false where lnid not = > > > > Why "not"? I thought, you wanted just the opposite - update the ones > that *do* have the same lnid? > I'd also recommend you to add ' and lncurrent' to the query - otherwise > every insert would be updating *every* row with the same lnid (it > doesn't check if the new row is actually the same as the old one) before > updating, and that may be expensive. > You may also want to create a pratial index on lnumbers (lnid) where > lncurrent to speed up your trigger All good and valid points. > > >but I can't seem to sus it put. > > What is the problem? The problem is I don't know how to convert the following pseudo code to valid SQL: create trigger unique_current on insert/update to lnumbers if new.lncurrent = true update lnumbers set all other records for this loco to false > > Dima > > > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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] sub-sel/group problem
On Monday 11 August 2003 11:24 am, Gary Stainburn wrote: > Hi folks, > > I don;t know if it's cos it's Monday or what, but I can't see what's wrong > here. > > I've got two tables, turns which holds a turn number, a task, and where > appropriate a link to a loco. The select below works but only shows those > tasks where a loco is involved.: > [snip] Having re-read my email and had another go, I've opted for the sub-select approach, and come up with: select rtid, concat(task) from (select rtid, case when r.rlid > 0 then r.rcid::text || ' on ' || l.lnumber::text else r.rcid::text end as task from rides r left outer join loco_dets l on r.rlid = l.lid) r group by rtid order by rtid ; Can anyone see any problems with this, or come up with a better approach? -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] sub-sel/group problem
Hi folks, I don;t know if it's cos it's Monday or what, but I can't see what's wrong here. I've got two tables, turns which holds a turn number, a task, and where appropriate a link to a loco. The select below works but only shows those tasks where a loco is involved.: select r.rtid, concat(r.rcid::text || ' on ' || l.lnumber::text) as task from rides r, loco_dets l where r.rlid = l.lid group by rtid; rtid | task --+- 5 | G on 60007 6 | A on 75014, C on 75014, A on 75029, C on 75029 7 | C on 4277, A on 44767, C on 44767 8 | A on 30926, C on 30926, G on 60532 9 | A on 30926, C on 30926, A on 75014, C on 75014 10 | F on 2392, F on 75029, L on 75029 11 | A on 44767, C on 44767, A on 75029 However, when I tried to change this to using an outer join I'm getting stuck. Can anyone see my stumbling point, which I think is to do with the condition part of the case statement. Do I need to do that in a sub-select first or is there an alternative? =# select r.rtid, -# case when r.rlid > 0 then -#concat(r.rcid::text || ' on ' || l.lnumber::text) -# else -#r.rcid::text -# end as task -# from rides r -# left outer join loco_dets l on r.rlid = l.lid -# group by rtid -# order by rtid -# ; ERROR: Attribute r.rlid must be GROUPed or used in an aggregate function =# -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] URGENT!!! changing Column size
On Monday 27 Oct 2003 5:10 pm, [EMAIL PROTECTED] wrote: > Hi can we change the size of a column in postgres. I have a table named > institution and column name is name varchar2(25), i want to change it to > varchar2(50). Please let me know. > > --Mohan try alter table institution add column newname varchar2(50); update institution set newname = name; alter table institution drop column namel; alter table institution rename column newname to name; > > > > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] update from select
Hi folks, don't know if it's cos of the 17 hours I've just worked (sympathy vote please) but I can't get this one worked out I've got table names with nid as name id field and nallowfollow flag. I've got a vehicles table with vowner pointing at nid and a vallowfollow field. How can I update nallowfollow from the appropriate vallowfollow flag? -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] update from select
On Wednesday 29 Oct 2003 2:58 pm, Stephan Szabo wrote: > On Wed, 29 Oct 2003, Gary Stainburn wrote: > > Hi folks, > > > > don't know if it's cos of the 17 hours I've just worked (sympathy vote > > please) but I can't get this one worked out > > > > I've got table names with nid as name id field and nallowfollow flag. > > I've got a vehicles table with vowner pointing at nid and a vallowfollow > > field. > > > > How can I update nallowfollow from the appropriate vallowfollow flag? > > If vehicles.vowner is unique, something like this maybe (using extensions > to sql)? > update names set nallowfollow=vehicles.vallowfollow > from vehicles where vehicles.vowner=names.nid; > > I think it'd be the follwoing in straight sql: > update names set nallowfollow= > (select vallowfollow from vehicles where vehicles.vowner=names.nid); > > > If it's not unique, what do you do if there are two vehicles with the same > vowner and different values for vallowfollow? Thanks for this Stephan, although the vowner is not unique, the update has worked sufficantly. Gary > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] alias problem on join
Hi folks, I've got an accounts table and a transactions table and want to do a summary join. If I do: bank=# select aid, aname, aodraft from accounts a bank-# left outer join (select account, sum(amount) as balance bank(# from statement group by account) as s bank-# on s.account = a.aid; I get the results I want, but I need to rename the aid column to 'key' so that I can feed it into a standard routine I have in PHP to build a hash. If I do: bank=# select aid as key, aname, aodraft from accounts a bank-# left outer join (select account, sum(amount) as balance bank(# from statement group by account) as s bank-# on s.account = a.key; I get ERROR: No such attribute or function 'key' Any ideas why? Also, I'm sure I can do this more efficiently as a single select/join, but can't seem to work out why (Friday morning syndrome). Anyone give me a clue? -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] where not unique
Hi folks, I've got a table which contains amongst other things a stock number and a registration number. I need to print out a list of stock number and reg number where reg number is not unique (cherished number plate transfer not completed). I've tried variations of a theme based on select stock_number, registration from stock where registration in (select registration, count(registration) as count from stock where count > 1 group by registration); but I have two problems. Firstly with the sub-select I get: usedcars=# select registration, count(registration) as count from stock where count > 1 group by registration; ERROR: Attribute 'count' not found usedcars=# although if I miss out the where clause I get the expected results. Secondly, when I run the full query I get: usedcars=# select stock_number, registration from stock usedcars-# where registration in usedcars-# (select registration, count(registration) as count from stock group by registration); ERROR: Subselect has too many fields usedcars=# which is obviously because of the count field. Can anyone tell me where I'm going wroing with these count fields? (I've tried renaming the field to regcount in case it was a reserved word problem). -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] working with unix timestamp
Hi folks. I've got a last_updated field on my stock records of type timestamp. This last_updated field I get using the perl code: my $timestamp=(stat "$localcsv/VehicleStock.$data_suffix")[10]; How can I insert the integer timestamp in $timestamp into my table? -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] working with unix timestamp
On Tuesday 16 March 2004 5:56 pm, Frank Finner wrote:
> On Tue, 16 Mar 2004 16:54:18 +0000 Gary Stainburn
> <[EMAIL PROTECTED]> sat down, thought
>
> long and then wrote:
> > Hi folks.
> >
> > I've got a last_updated field on my stock records of type timestamp.
> >
> > This last_updated field I get using the perl code:
> >
> > my $timestamp=(stat "$localcsv/VehicleStock.$data_suffix")[10];
> >
> > How can I insert the integer timestamp in $timestamp into my table?
>
> I usually use somethinge like the following little function for getting an
> ISO timestamp. The result is suitable for a PostgreSQL timestamp field
> (without special timezone).
>
> # Subroutine for ISO-Timestamp
> sub mydatetime
> {
> my ($time)[EMAIL PROTECTED];
> my ($sec,$min,$hou,$mday,$mon,$yea,$wday,$jday,$sz)=localtime($time);
> if ($sec < 10) {$sec="0".$sec;}
> if ($min < 10) {$min="0".$min;}
> if ($hou < 10) {$hou="0".$hou;}
> if ($mday < 10) {$mday="0".$mday;}
> $mon++;
> if ($mon < 10) {$mon="0".$mon;}
> $yea=$yea+1900;
> my $t=$yea."-".$mon."-".$mday." ".$hou.":".$min.":".$sec;
> return $t;
> }
>
> Regards, Frank.
Thanks Frank,
My code's not as padantic, but produces a string hat is acceptable to
Postgrresql.
my $timestamp=(stat "$localcsv/VehicleStock.$data_suffix")[9];
my ($sec,$min,$hour,$mday,$mon,$year) =localtime($timestamp);
$year+=1900;
$mon++;
$timestamp="$year-$mon-$mday $hour:$min:$sec";
However, I think I'll use Tom's suggestion and do the conversion in SQL.
Gary
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] three-way join
Hi folks, here's a straight forward join that I simply can't get my head round. I've got consumables:cs_id, cs_make, cs_comments cons_locations: cl_id, cl_desc cons_stock: cs_id, cl_id, status (1=ordered, 2=in-stock) (one stock record per stock item, qty=3 means 3 records) I'm struggling to create a quiery to produce cs_id, cs_make, cs_comments, cl_desc, hand_qty, order_qty where hand_qty and order_qty is the number of records grouped by cs_id, cl_id, and status. I've done the simple part and created a view balances to tally the cons_stock as: create view balances as select cost_cs_id, cost_cl_id, cost_css_id, count(*) as qty from cons_stock group by cost_cs_id, cost_cl_id, cost_css_id; I then have trouble joining this to the consumables and locations table to get the results I need. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] three-way join
o cons_stock (cost_cs_id, cost_css_id, cost_cl_id) values (1, 2, 1); insert into cons_stock (cost_cs_id, cost_css_id, cost_cl_id) values (1, 2, 1); create view balances as select cost_cs_id, cost_cl_id, cost_css_id, count(*) as qty from cons_stock group by cost_cs_id, cost_cl_id, cost_css_id; > > > Regards, > > Stijn Vanroye > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] three-way join - solved
On Monday 19 April 2004 4:01 pm, Gary Stainburn wrote:
> On Monday 19 April 2004 3:06 pm, Stijn Vanroye wrote:
> > Gary wrote:
> > > Hi folks,
> > >
> > > here's a straight forward join that I simply can't get my head round.
> > >
> > > I've got
> > >
> > > consumables: cs_id, cs_make, cs_comments
> > > cons_locations: cl_id, cl_desc
> > > cons_stock: cs_id, cl_id, status (1=ordered, 2=in-stock)
> > >
> > > (one stock record per stock item, qty=3 means 3 records)
> >
> > assuming that the PK's are:
> > consumables : cs_id
> > cons_loacations: cl_id
> > cons_stock: cs_id, cl_id
> > You could only have 1 record in cons_stock for each unique combination of
> > consumable and location. If the primary key for cons_stock would also
> > include the field status you could have 2 records for each unique
> > combination of consumable and location, one where status is ordered, and
> > one where status is in-stock.
>
> Sorry for the confusion. For the purpose of simplicity I trimmed the info
> - a little too far it seems.
>
> cons_stock has as it's PK a serial field, cost_id (cost_ is the prefix I
> use for fields on this table, the other fields therefore are cost_cs_id and
> cost_cl_id). I need to be able to track individual items, and thus give it
> a unique id. If I ordered 4 HP 4100 toners, they'd create 4 seperate
> records even though the cost_cs_id and cost_cl_id's would all be the same.
>
> > > I'm struggling to create a quiery to produce
> > >
> > > cs_id, cs_make, cs_comments, cl_desc, hand_qty, order_qty
> > >
> > > where hand_qty and order_qty is the number of records grouped
> > > by cs_id, cl_id,
> > > and status.
> >
> > Given the previous, the result for qty would be pretty obvious I think,
> > since you would have only 1 record for the combination cs_id,cl_id and
> > status.
> >
> > > I've done the simple part and created a view balances to
> > > tally the cons_stock
> > > as:
> > >
> > > create view balances as
> > > select cost_cs_id, cost_cl_id, cost_css_id, count(*) as qty
> > > from cons_stock
> > > group by cost_cs_id, cost_cl_id, cost_css_id;
> >
> > I don't understand where the cost_* fields come from, especially the
> > cost_css_id field. Assuming that these fields are the cs_id, cl_id and
> > status qty is most likley going to be 1 all the time? Maybe it's worth to
> > rethink your database structure, or adding the qty fields to the table
> > cons_stock and keeping them up-to-date? (eg. CONS_STOCK (cs_id, cl_id,
> > hand_qty, order_qty) PK(cs_id, cl_id) ) that way you simply change the
> > quantity fields for each combination of location-consumable according to
> > the situation (and sound the alarm if the reach a certain level?).
>
> the cost_ (abrev of cons_stock) is the prefix of the fields on the
> cons_stock field. consumables have prefix cs_ and locations have cl_.
> Therefore when cons_stock references consumables id field it is called
> cost_cd_id.
>
> > If anyone thinks I'm wrong, please correct me.
>
> I hope my my explanation's cleared up the grey area. I've included all of
> the relevent schema below to help show what I want.
>
> create table cons_types (
> cst_id serial not null unique,
> cst_descvarchar(40),
> primary key (cst_id)
> );
> insert into cons_types (cst_desc) values ('Toner cartridge'); -- 1
> insert into cons_types (cst_desc) values ('Ink cartridge'); -- 2;
>
> create table consumables (
> cs_id serial not null unique,
> cs_make varchar(40),
> cs_code varchar(20),
> cs_type int4 references cons_types(cst_id) not null,
> cs_colour varchar(40),
> cs_comments text,
> primary key (cs_id)
> );
>
> insert into consumables (cs_make, cs_code, cs_type,cs_colour, cs_supp,
> cs_comments) values
> ('HP', 'C4096A', 1, 'BLACK', 5, '2100 2 0');
>
> create table cons_locations (
> cl_id serial not null unique,
> cl_desc varchar(40),
> primary key (cl_id)
> );
> insert into cons_locations (cl_desc) values ('Leeds Computer Room'); -- 1
>
> create table cons_status (
> css_id serial not null unique,
> css_descvarchar(40),
> primary key (css_id)
> );
> insert into cons_status (css_desc) values ('Ordered');-- 1
> insert into cons_st
[SQL] view problem - too many rows out
1 32 | HP | C3903A | Toner cartridge | BLACK | 2 | 1 32 | HP | C3903A | Toner cartridge | BLACK | 2 | 1 32 | HP | C3903A | Toner cartridge | BLACK | 2 | 1 32 | HP | C3903A | Toner cartridge | BLACK | 2 | 1 32 | HP | C3903A | Toner cartridge | BLACK | 2 | 1 32 | HP | C3903A | Toner cartridge | BLACK | 2 | 1 17 | Epson | T0442 | Ink cartridge | CYAN | 1 | 2 17 | Epson | T0442 | Ink cartridge | CYAN | 1 | 2 17 | Epson | T0442 | Ink cartridge | CYAN | 1 | 2 17 | Epson | T0442 | Ink cartridge | CYAN | 1 | 2 [snip] 32 | HP | C3903A | Toner cartridge | BLACK | 2 | 6 34 | SAMSUNG | SF-5100 | Ink Film| BLACK | 1 | 6 34 | SAMSUNG | SF-5100 | Ink Film| BLACK | 1 | 6 34 | SAMSUNG | SF-5100 | Ink Film| BLACK | 1 | 6 34 | SAMSUNG | SF-5100 | Ink Film| BLACK | 1 | 6 34 | SAMSUNG | SF-5100 | Ink Film| BLACK | 1 | 6 34 | SAMSUNG | SF-5100 | Ink Film| BLACK | 1 | 6 34 | SAMSUNG | SF-5100 | Ink Film| BLACK | 1 | 6 (112 rows) hardware=# -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 8: explain analyze is your friend
solved Re: [SQL] view problem - too many rows out
On Wednesday 12 May 2004 11:02 am, Gary Stainburn wrote: > Hi folks. > [snip] > create view order_summary as > select > c.cs_id, cs.count as qty, c.cs_make, c.cs_code, c.cs_type, > cst.cst_desc, c.cs_colour, > o.or_id, > o.or_supp, o.or_date, o.or_received, o.or_no, orst.orst_desc as > order_state, > co.co_id, co.co_name, co.co_person, co.co_tel, > co.co_mobile, co.co_fax, co.co_email, co.co_type, > c.cs_comments > from consumables c, orders o, > (select cost_cs_id, cost_or_id, count(cost_cs_id) from cons_stock >where cost_or_id is not null >group by cost_cs_id, cost_or_id > ) cs, contacts co, > cons_locations cl, cons_types cst, order_states orst cons_locations shouldn't have been there > where cs.cost_cs_id = c.cs_id > and cs.cost_or_id = o.or_id > and c.cs_type = cst.cst_id > and o.or_supp = co.co_id > and o.or_state = orst.orst_id; [snip] -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] subselect prob in view
Hi folks, I've got the view: create view nrequest_details as select r.r_id, r_registration, r.r_chassis, r.r_vehicle, r.r_fuel,r.r_pack_mats, r.r_delivery, r_delivery::date-now()::date as r_remaining, r.r_created, r.r_completed, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des from requests r, users u, request_types t, request_states s, dealerships d, departments de, customers c where r_d_id = d.d_id and r_s_id = s.s_id and r_c_id = c.c_id and r_t_id = t.t_id and r_d_id = d.d_id and r_de_id = de.de_id and r_u_id = u.u_id; to which I want to add a count (2 eventually), so that it becomes: create view nrequest_details as select r.r_id, r_registration, r.r_chassis, r.r_vehicle, r.r_fuel,r.r_pack_mats, r.r_delivery, r_delivery::date-now()::date as r_remaining, r.r_created, r.r_completed, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des, co.count as comments -- cor.count as comments_unseen from requests r, users u, request_types t, request_states s, dealerships d, departments de, customers c left outer join (select co_r_id, count(co_r_id) from comments group by co_r_id) co on co.co_r_id = r.r_id --left outer join (select co_r_id, count(co_r_id) from comments where cor_viewed is null -- group by co_r_id) co on -- co.co_r_id = r.r_id where r_d_id = d.d_id and r_s_id = s.s_id and r_c_id = c.c_id and r_t_id = t.t_id and r_d_id = d.d_id and r_de_id = de.de_id and r_u_id = u.u_id; but I get the error: [EMAIL PROTECTED] gary]$ psql -f goole1.sql DROP psql:goole1.sql:45: ERROR: Relation "r" does not exist [EMAIL PROTECTED] gary]$ I tried using the table name instead of the alias but instead got the error: [EMAIL PROTECTED] gary]$ psql -f goole1.sql psql:goole1.sql:1: ERROR: view "nrequest_details" does not exist psql:goole1.sql:45: NOTICE: Adding missing FROM-clause entry for table "requests" psql:goole1.sql:45: ERROR: JOIN/ON clause refers to "requests", which is not part of JOIN [EMAIL PROTECTED] gary]$ which at least makes sense. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] subselect prob in view
On Monday 21 Jun 2004 3:19 pm, Tom Lane wrote: > Gary Stainburn <[EMAIL PROTECTED]> writes: > > from requests r, users u, request_types t, > > request_states s, dealerships d, departments de, customers c > > left outer join (select co_r_id, count(co_r_id) from comments > > group by co_r_id) co on > > co.co_r_id = r.r_id > > psql:goole1.sql:45: ERROR: Relation "r" does not exist > > I think you have learned some bad habits from MySQL :-( > > PostgreSQL follows the SQL spec and makes JOIN bind more tightly than > comma. Therefore, in the above the LEFT JOIN is only joining "c" to > "co" and its JOIN ON clause can only reference those two relations. > > You could get the behavior you seem to expect by changing each comma > in the from-list to CROSS JOIN. Then the JOINs all bind left-to-right > and so "r" will be part of the left argument of the LEFT JOIN. > > Note that if you are using a pre-7.4 release this could have negative > effects on performance --- see the user's guide concerning how explicit > JOIN syntax constrains the planner. > > regards, tom lane Thanks for this Tom, but I've never used MySQL. I'll look at the docs and have another go. Gary -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] subselect prob in view
On Monday 21 Jun 2004 4:11 pm, Gary Stainburn wrote: > On Monday 21 Jun 2004 3:19 pm, Tom Lane wrote: > > Gary Stainburn <[EMAIL PROTECTED]> writes: > > > from requests r, users u, request_types t, > > > request_states s, dealerships d, departments de, customers c > > > left outer join (select co_r_id, count(co_r_id) from comments > > > group by co_r_id) co on > > > co.co_r_id = r.r_id > > > psql:goole1.sql:45: ERROR: Relation "r" does not exist > > > > I think you have learned some bad habits from MySQL :-( > > > > PostgreSQL follows the SQL spec and makes JOIN bind more tightly than > > comma. Therefore, in the above the LEFT JOIN is only joining "c" to > > "co" and its JOIN ON clause can only reference those two relations. > > > > You could get the behavior you seem to expect by changing each comma > > in the from-list to CROSS JOIN. Then the JOINs all bind left-to-right > > and so "r" will be part of the left argument of the LEFT JOIN. > > > > Note that if you are using a pre-7.4 release this could have negative > > effects on performance --- see the user's guide concerning how explicit > > JOIN syntax constrains the planner. > > > > regards, tom lane > > Thanks for this Tom, but I've never used MySQL. > > I'll look at the docs and have another go. > > Gary In order to simplify things, I'm just concentrating on the view to give me the two tallies. The two selects work seperately, but I'm still getting the syntax for the combined quiery wrong. I'm asuming that the problem's before the 'on' clause and not the clause itself (I've also tried using 'using' instead but that didn't work either. goole=# select co_id, co_r_id, cor_viewed goole-# from comments c, co_recipients co goole-# where c.co_id = co.cor_co_id; co_id | co_r_id | cor_viewed ---+-+--- 1 | 1 | 2004-06-22 10:15:52.945065+01 1 | 1 | 2004-06-22 10:15:52.952895+01 2 | 1 | 2 | 1 | 3 | 2 | (5 rows) goole=# select co_r_id, count(co_r_id) from comments group by co_r_id; co_r_id | count -+--- 1 | 2 2 | 1 (2 rows) goole=# select co_r_id, count(co_r_id) from comments where co_id in goole-# (select distinct co_id goole(#from comments c, co_recipients co goole(#where c.co_id = co.cor_co_id and co.cor_viewed is null) goole-# group by co_r_id; co_r_id | count -+--- 1 | 1 2 | 1 (2 rows) goole=# select co.co_r_id, co.count as com_count, cor.count as com_unseen goole-# from goole-# (select co_r_id, count(co_r_id) goole(# from comments group by co_r_id) co, goole-# (select co_r_id, count(co_r_id) goole(# from comments where co_id in goole(# (select distinct co_id goole(# from comments c, co_recipients co goole(# where c.co_id = co.cor_co_id and co.cor_viewed is null) goole(# group by co_r_id) cor on co.co_r_id = cor.co_r_id; ERROR: parser: parse error at or near "on" goole=# -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] table update using result from join
Hi folks. I'm annoyed that I can't sus out something this simple. The join below gives the output I want, but I can't work out how to convert it to be an update on the requests field. I want to append the customer name to the fuel (delivery details) field and then update the customer field to be 'retail'. but I can't work out the description update bit. goole=# select r.r_fuel||' - Customer: '||c.c_des from requests r, customers c goole-# where r.r_c_id = c.c_id and c.c_id in ( goole(# 9, 10, 13, 27, 35, 36, 39, 42, 44, 51, 53, 54, 55, 56, 57, 58, 60, 65, 67, 69, goole(# 74, 75, 77, 81, 82, 84, 88, 89, 90, 91, 92, 96, 98, 99, 100, 101, 102, 103, goole(# 105, 108, 113, 114, 117, 118, 124, 125, 126, 131, 132, 135, 136, 137, 144, goole(# 145, 148, 149, 150, 151, 154, 11, 37, 40, 41, 43, 48, 52, 59, 62, goole(# 63, 68, 70, 71, 83, 86, 93, 104, 119, 120, 121, 123, 128, 129, 130, goole(# 134, 138, 142, 146, 147, 152, 153, 19, 38, 85, 87, 94, 106, 112, 116, goole(# 141, 143, 18, 110, 111, 115, 140, 24, 50, 133, 47, 64, 76, 95, 107, goole(# 109, 127, 33, 46, 97); ?column? - RINGWAYS - Customer: SUBSCAN RINGWAYS - Customer: MARTIN ? - Customer: N G BAILEY ? - Customer: CHECRON SITE SERVICES LTD 10 litres - Customer: CONSULTANT SERVICES ? - Customer: ALD AUTOMOTIVE eskrigg yo19 6ez 9am - Customer: TRANSPORT MANAGEMENT TRAILER / PE18 9UH / HALF ON DELIVERY - Customer: ALD AUTOMOTIVE DRIVEN / WF16 0NF - Customer: CONSULTANT SERVICES TRAILER / HALF TANK ON DELIVERY - Customer: LEX / SWINTON DN14 0HR - Customer: CHECRON SITE SERVICES LTD . - Customer: other fleet [snip] (256 rows) goole=# What I want is something like update requests set r_fuel=, r_c_id = 7 where r_c_id in (... -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] curious delay on view/where
Hi folks. I have the following view: CREATE VIEW "stock_available" as SELECT * FROM stock_details WHERE available = true AND visible = true AND location not in (SELECT descr FROM ignored); Stock_details is itself a view pulling in a number of tables. Everything works fine until I try to pull in only the details for a specific branch, using the following. select * from stock_available where branch = 'Leeds'; or select * from stock_available where branch = 'Doncaster'; At this point, the query takes 11 seconds. Any other quiery, including select * from stock_available where branch != 'Doncaster' and select * from stock_available where branch != 'Leeds' which only return the equivelent of the top two (we only have Leeds and Doncaster) are les than 1 second. Anyone got any ideas of the cause, or thoughts on how I can trace the problem? -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] extra info - curious delay on view/where
On Thursday 28 October 2004 11:16 am, Gary Stainburn wrote: > Hi folks. > > I have the following view: > > CREATE VIEW "stock_available" as > SELECT * FROM stock_details > WHERE available = true AND visible = true AND > location not in (SELECT descr FROM ignored); > > Stock_details is itself a view pulling in a number of tables. > Everything works fine until I try to pull in only the details for a > specific branch, using the following. > > select * from stock_available where branch = 'Leeds'; > or > select * from stock_available where branch = 'Doncaster'; > > At this point, the query takes 11 seconds. Any other quiery, > including [snip] Once thing I forgot to mention. If I run the above on the base view stock_details, it returns in < 1 second too. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] curious delay on view/where
On Thursday 28 October 2004 3:25 pm, Tom Lane wrote: > Gary Stainburn <[EMAIL PROTECTED]> writes: > > Anyone got any ideas of the cause, or thoughts on how I can trace > > the problem? > > EXPLAIN ANALYZE results for the fast and slow cases would be > interesting. Also, have you ANALYZEd the underlying tables lately? > And what PG version is this? > > regards, tom lane Hi Tom. I've the analyze but don't understand what it's telling me. I've made it available at http://www.stainburn.com/analyze.txt Gary -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] tree structure photo gallery date quiery
Hi folks. I'm looking at the possibility of implementing a photo gallery for my web site with a tree structure, something like: create table gallery ( id serial, parent int4, name varchar(40), primary key (id)); create table photos ( pid serial, id int4 references gallery not null, added timestamp, pfile varchar(128) not null, pdesc varchar(40) not null, primary key (pid)); copy "gallery" from stdin; 1 0 Root 2 1 NYMR 3 1 Middleton 4 2 Steam Gala 5 2 Diesel Gala 6 2 From The Footplate 7 3 From The Footplate \. copy "photos" from stdin; 1 4 2004-11-10 10:10:00 80135-1.jpg 80135 light-engine 2 4 2004-11-10 12:12:00 6619-1.jpg 6619 on-shed 3 5 2004-10-01 10:00:00 D7628.jpg Sybilla 4 7 2004-01-01 09:12:12 mm-21.jpg No. 4 Mathew Murrey \. How would I go about creating a view to show a) the number of photos in a gallery and b) the timestamp of the most recent addition for a gallery, so that it interrogates all sub-galleries? For example NYMR should return 3, 2004-11-10 12:12, Middleton should return 1, 2004-01-01 09:12:12 and Root should return 4, 2004-11-10 12:12:00 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] tree structure photo gallery date quiery
On Tuesday 16 November 2004 1:08 pm, sad wrote: > On Tuesday 16 November 2004 14:29, Gary Stainburn wrote: > > Hi folks. > > > > I'm looking at the possibility of implementing a photo gallery for > > my web site with a tree structure > > > > How would I go about creating a view to show a) the number of > > photos in a gallery and b) the timestamp of the most recent > > addition for a gallery, so that it interrogates all sub-galleries? > > nested-tree helps you > associate a numeric interval [l,r] with each record of a tree > and let father interval include all its children intervals > and brother intervals never intersect > > see the article http://sf.net/projects/redundantdb > for detailed examples and templates Hi Sad, I had actually started working on this because I found an old list posting archived on the net at http://www.net-one.de/~ks/WOoK/recursive-select. As you can see below, I've got the tree structure working and can select both a node's superiors and it's subordinates. Using these I can also find a node's last added date and photo count. However, I've got two problems. Firstly, below I've got the two example selects for listing owners and owned nodes. I can't work out how to convert these two parameterised selects into views. Secondly, in order to get the results shown here, I've had to write two seperate but similar pl/pgsql functions to return the photo_count and photo_updated columns, which result in 2 * select per call * twice per line * 7 lines = 28 selects Is there a more efficient way? nymr=# select *, photo_count(id), photo_updated(id) from gallery; id | parent |name| photo_count | photo_updated +++-+ 1 | 0 | Root | 4 | 2004-11-10 12:12:00+00 2 | 1 | NYMR | 3 | 2004-11-10 12:12:00+00 3 | 1 | Middleton | 1 | 2004-01-01 09:12:12+00 4 | 2 | Steam Gala | 2 | 2004-11-10 12:12:00+00 5 | 2 | Diesel Gala| 1 | 2004-10-01 10:00:00+01 6 | 2 | From The Footplate | 0 | 7 | 3 | From The Footplate | 1 | 2004-01-01 09:12:12+00 (7 rows) Below is everything I have so far, including one of the functions I'm using: create table gallery ( id serial, parent int4, namevarchar(40), primary key (id)); create table photos ( pid serial, id int4 references gallery not null, added timestamp, pfile varchar(128) not null, pdesc varchar(40) not null, primary key (pid)); create table tree ( -- seperate for now to ease development id int4 references gallery not null, lft INTEGER NOT NULL UNIQUE CHECK (lft > 0), rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1), CONSTRAINT order_okay CHECK (lft < rgt) ); copy "gallery" from stdin; 1 0 Root 2 1 NYMR 3 1 Middleton 4 2 Steam Gala 5 2 Diesel Gala 6 2 From The Footplate 7 3 From The Footplate \. copy "photos" from stdin; 1 4 2004-11-10 10:10:00 80135-1.jpg 80135 light-engine 2 4 2004-11-10 12:12:00 6619-1.jpg 6619 on-shed 3 5 2004-10-01 10:00:00 D7628.jpg Sybilla 4 7 2004-01-01 09:12:12 mm-21.jpg No. 4 Mathew Murrey \. copy "tree" from stdin; 1 1 14 2 2 9 3 10 13 4 3 4 5 5 6 6 7 8 7 11 12 \. -- select leaf and parents -- want to convert to a view so I can type something like -- 'select * from root_path where id = 7; nymr=# select p2.id, g.parent, g.name from gallery g, tree p1, tree p2 where g.id = p2.id and p1.lft between p2.lft and p2.rgt and p1.id = 7; id | parent |name ++ 1 | 0 | Root 3 | 1 | Middleton 7 | 3 | From The Footplate (3 rows) -- Select parent and subordinates - also want to convert to view nymr=# select p1.*, g.* from tree as p1, tree as p2, gallery g where g.id = p1.id and p1.lft between p2.lft and p2.rgt and p2.id = 1; id | lft | rgt | id | parent |name +-+-+++ 1 | 1 | 14 | 1 | 0 | Root 2 | 2 | 9 | 2 | 1 | NYMR 3 | 10 | 13 | 3 | 1 | Middleton 4 | 3 | 4 | 4 | 2 | Steam Gala 5 | 5 | 6 | 5 | 2 | Diesel Gala 6 | 7 | 8 | 6 | 2 | From The Footplate 7 | 11 | 12 | 7 | 3 | From The Footplate (7 rows) -- use the one above to select photos - another view nymr=# select count(pid), max(added) from photos where id in ( nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft and p2.rgt and p2.id = 1 nymr(# ); count | max ---
[SQL] simple update from select ??
Hi folks I've got a table of pieces of equipment. One piece of equipment may be owned by another piece of equipment, and may own multiple other pieces. To hold the relationship I have a piece_pieces table holding the owner (pp_id) part id and the owned part ids (pp_part). I'v realised I can store this within the original table so I've added an owner attribute. I need to now populate this now from the other table, but I can't work out how. I've got pieces table p_id-- part id p_owner -- new owner attribute piece_pieces table pp_id -- id of owner pp_part -- id of owned -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] simple update from select ??
On Monday 07 March 2005 12:28 pm, you wrote: > Hi folks > > I've got a table of pieces of equipment. > One piece of equipment may be owned by another piece of equipment, > and may own multiple other pieces. > > To hold the relationship I have a piece_pieces table holding the > owner (pp_id) part id and the owned part ids (pp_part). > > I'v realised I can store this within the original table so I've added > an owner attribute. I need to now populate this now from the other > table, but I can't work out how. > > I've got pieces table > > p_id -- part id > p_owner -- new owner attribute > > piece_pieces table > > pp_id -- id of owner > pp_part -- id of owned For my own future reference as much as anything, here's the required statement, which is a syntactically corrected version of a suggestion by Sean Davis update pieces set p_owner=piece_pieces.pp_id from piece_pieces where piece_pieces.pp_part=pieces.p_id; Under Sean's suggestion I did this inside a transaction block so I could check that it did what I wanted. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] order by question
Hi folks. I seem to remember somewhere being shown how to bump specific rows to the top of a list; something along the lines of: select c_id as key, c_des as value from customers order by c_id = 7, c_id = 160, value; however, although the statement is accepted the two rows specified are not bumped to the top of the list, but instead appear in their correct position in the order by value part. Is it possible and if so how do I do it? -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] order by question
On Wednesday 09 March 2005 1:06 pm, you wrote: > Gary Stainburn wrote: > > Hi folks. > > > > I seem to remember somewhere being shown how to bump specific rows > > to the top of a list; something along the lines of: > > > > select c_id as key, c_des as value from customers order by c_id = > > 7, c_id = 160, value; > > Looks roughly right. > > SELECT * FROM foo ORDER BY not(a=6),not(a=4),a; > a | b | c > ---+--+- > 6 | ccc | BBB > 4 | aaa | BBB > 1 | aaa | AAA > 2 | zxxx | AAA > 3 | ccc | ZZZ > 5 | zxxx | BBB > (6 rows) > > Alternatively: (a<>6),(a<>4),a Although this does exactly what I want, at first glance it should do exactly the oposite. I'm guessing that for each line it evaluates not (a=6) 0 for true else 1 not (a=4) 0 for true else 1 everything else -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] best way to swap two records (computer details)
Hi folks. I have a table called pieces which contain every piece of hardware and software within my company. Each piece has an owner attribute which points to another piece which - funnily enough - owns it. For example records for CPU, motherboard, HDD, O/S, and applications will all be owned by a piece record representing a computer. I'm currently going through an upgrade process at the moment where I build a new PC, install all relevent software and use Documents and Settings Transfer Wizard to move a user onto the new PC before wiping and disposing the old PC. My question is what's the best way to swap settings between the two computer records and swap any software installed? Ideally I'd like it in the form of a function where I can pass the two p_id's and return a boolean reflecting success (true) or fail (false). Currently I do this manually with: update pieces set p_name = 'LSALES1', p_location = 'Mike Haley', p_site = 'L' where p_id = 724; update pieces set p_name = 'SPARE', p_location = 'spare', p_site = 'L' where p_id = 305; update pieces set p_owner = 724 where p_owner = 305 and p_type in ( select hwt_id from hw_types where hwt_cat in ( select hwc_id from hw_categories where hwc_hwg_id = 7)); The hw_types and hw_categories select all O/S and application software. This doesn't put any software currently on 305 onto 724 which would be nice. (I'm not after someone to do my work for me, but a good starting point would be very much appreciated) -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] best way to swap two records (computer details)
On Friday 18 March 2005 7:54 pm, you wrote:
> > My question is what's the best way to swap settings between the two
> > computer records and swap any software installed? Ideally I'd like
> > it in the form of a function where I can pass the two p_id's and
> > return a boolean reflecting success (true) or fail (false).
>
> I'd say something like that (generic table names) :
>
> If you're confident :
> UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE 'A' END)
> WHERE owner IN ('A','B')
>
> If you're paranoid :
> UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE IF
> owner='B' THEN 'A' ELSE owner END) WHERE owner IN ('A','B')
Hello again.
I've tried the first one but get a syntax error. Anyone see why?
hardware=# update pieces set p_owner = (case if p_owner = 305 then 724
else 305 end) where p_owner in (305, 724);
ERROR: parser: parse error at or near "p_owner"
hardware=#
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] best way to swap two records (computer details)
On Friday 18 March 2005 4:32 pm, you wrote: > How about a user defined function ??? > > CREATE OR REPLACE FUNCTION harwareupdate(integer, integer) RETURNS > BOOLEAN AS ' > > update pieces set p_name = \'LSALES1\', p_location = \'Mike > Haley\', p_site = \'L\' where p_id = $1; > > update pieces set p_name = \'SPARE\', p_location = \'spare\', > p_site = \'L\' > where p_id = 2; > This isn't quite what I want. I want to SWAP the p_name and p_location between the two records, not set them to static values. Any help would be appreciated. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] best way to swap two records (computer details)
On Wednesday 23 March 2005 5:26 pm, you wrote:
> On Friday 18 March 2005 7:54 pm, you wrote:
> > > My question is what's the best way to swap settings between the
> > > two computer records and swap any software installed? Ideally
> > > I'd like it in the form of a function where I can pass the two
> > > p_id's and return a boolean reflecting success (true) or fail
> > > (false).
> >
> > I'd say something like that (generic table names) :
> >
> > If you're confident :
> > UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE 'A' END)
> > WHERE owner IN ('A','B')
> >
> > If you're paranoid :
> > UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE IF
> > owner='B' THEN 'A' ELSE owner END) WHERE owner IN ('A','B')
>
> Hello again.
>
> I've tried the first one but get a syntax error. Anyone see why?
>
> hardware=# update pieces set p_owner = (case if p_owner = 305 then
> 724 else 305 end) where p_owner in (305, 724);
> ERROR: parser: parse error at or near "p_owner"
> hardware=#
Sorted it. The 'IF' should be 'WHEN'
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] gone blind - can't see syntax error
Hi folks. I've been looking at this for 10 minutes and can't see what's wrong. Anyone care to enlighten me. Thanks Gary [EMAIL PROTECTED] webroot]# psql -a -f new-view.sql SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, r.r_completed, r.r_salesman, sm.r_salesman as salesman_name, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des, co.com_count, co.com_unseen FROM requests r, left outer join users sm on sm.u_id = r.r_salesman, left outer join users u on r.r_u_id = u.u_id, left outer join request_types t on r.r_t_id = t.t_id, left outer join request_states s on r.r_s_id = s.s_id, left outer join dealerships d on r.r_d_id = d.d_id, left outer join departments de on r.r_de_id = de.de_id, left outer join customers c on r.r_c_id = c.c_id, left outer join comment_tallies co on r.r_id = co.r_id ORDER BY r.r_id; psql:new-view.sql:19: ERROR: parser: parse error at or near "left" -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] gone blind - can't see syntax error
On Friday 01 April 2005 1:01 pm, you wrote: > On Friday 01 Apr 2005 5:06 pm, Gary Stainburn wrote: > > Hi folks. > > > > I've been looking at this for 10 minutes and can't see what's > > wrong. Anyone care to enlighten me. > > comma after 'r'? I'd only just added that comma, to try to fix it. That shouldn't have been there. The problem was the comma after each of the joins. They should not have been there either. Gary -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Speed up slow select - was gone blind
Hi folks. I've got my select working now, but I haven't received the speed increase I'd expected. It replaced an earlier select which combined a single explicit join with multiple froms. The first select is the old one, the second one is the new one (with a new join). The new one takes 24 seconds to run while the old one took 29. How can I redo the select to improve the speed, or what else can I do to optimaise the database? original (ugly) ~ SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, r.r_completed, r.r_salesman, r.salesman_name, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des, co.com_count, co.com_unseen FROM (SELECT r.r_id, r.r_t_id, r.r_d_id, r.r_de_id, r.r_s_id, r.r_registration, r.r_chassis, r.r_c_id, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, r.r_salesman, r.r_created, r.r_completed, r.r_u_id, u.u_username AS salesman_name FROM (requests r LEFT JOIN users u ON ((r.r_salesman = u.u_id r, users u, request_types t, request_states s, dealerships d, departments de, customers c, comment_tallies co WHERE (r.r_d_id = d.d_id) AND (r.r_s_id = s.s_id) AND (r.r_c_id = c.c_id) AND (r.r_t_id = t.t_id) AND (r.r_d_id = d.d_id) AND (r.r_de_id = de.de_id) AND (r.r_u_id = u.u_id) AND (r.r_id = co.r_id)) ORDER BY r.r_id; new ~~~ SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, r.r_completed, r.r_salesman, sm.u_username as salesman_name, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des, co.com_count, co.com_unseen, pl.pl_id, pl.pl_desc as plates FROM requests r left outer join users sm on sm.u_id = r.r_salesman left outer join users u on r.r_u_id = u.u_id left outer join request_types t on r.r_t_id = t.t_id left outer join request_states s on r.r_s_id = s.s_id left outer join dealerships d on r.r_d_id = d.d_id left outer join departments de on r.r_de_id = de.de_id left outer join customers c on r.r_c_id = c.c_id left outer join comment_tallies co on r.r_id = co.r_id left outer join plates pl on r.r_plates = pl.pl_id ORDER BY r.r_id; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Speed up slow select - was gone blind
Hi folks. I did send an explain analyze last week but for some reason it didn't appear on the list. However, I've looked into the delay and it doesn't seem to be the SQL. I'm now looking into why my PHP seems to sit there for 20+ seconds doing nowt. Thanks to everyone for the help anyway. Gary On Friday 01 Apr 2005 1:46 pm, you wrote: > Can you send the EXPLAIN ANALYZE of each? We can't really tell where > the slowdown is without that. > > On Apr 1, 2005 12:32 PM, Gary Stainburn <[EMAIL PROTECTED]> wrote: > > Hi folks. > > > > I've got my select working now, but I haven't received the speed > > increase I'd expected. It replaced an earlier select which > > combined a single explicit join with multiple froms. > > > > The first select is the old one, the second one is the new one > > (with a new join). The new one takes 24 seconds to run while the > > old one took 29. > > > > How can I redo the select to improve the speed, or what else can I > > do to optimaise the database? > > > > original (ugly) > > ~ > > > > SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, > > r.r_fuel, r.r_pack_mats, r.r_delivery, > > (date(r.r_delivery) - date(now())) AS r_remaining, > > r.r_created, r.r_completed, r.r_salesman, r.salesman_name, > > d.d_des, de.de_des, > > u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, > > t.t_id, t.t_des, > > s.s_id, s.s_des, > > c.c_id, c.c_des, > > co.com_count, co.com_unseen > > FROM (SELECT r.r_id, r.r_t_id, r.r_d_id, r.r_de_id, r.r_s_id, > > r.r_registration, r.r_chassis, r.r_c_id, r.r_vehicle, > > r.r_fuel, r.r_pack_mats, r.r_delivery, r.r_salesman, > > r.r_created, r.r_completed, r.r_u_id, > > u.u_username AS salesman_name > > FROM (requests r LEFT JOIN users u ON > > ((r.r_salesman = u.u_id r, > > users u, > > request_types t, > > request_states s, > > dealerships d, > > departments de, > > customers c, > > comment_tallies co > > WHERE (r.r_d_id = d.d_id) AND > > (r.r_s_id = s.s_id) AND > > (r.r_c_id = c.c_id) AND > > (r.r_t_id = t.t_id) AND > > (r.r_d_id = d.d_id) AND > > (r.r_de_id = de.de_id) AND > > (r.r_u_id = u.u_id) AND > > (r.r_id = co.r_id)) > > ORDER BY r.r_id; > > > > new > > ~~~ > > SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, > > r.r_fuel, r.r_pack_mats, r.r_delivery, > > (date(r.r_delivery) - date(now())) AS r_remaining, > > r.r_created, r.r_completed, r.r_salesman, > > sm.u_username as salesman_name, > > d.d_des, de.de_des, > > u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, > > t.t_id, t.t_des, > > s.s_id, s.s_des, > > c.c_id, c.c_des, > > co.com_count, co.com_unseen, > > pl.pl_id, pl.pl_desc as plates > > FROM requests r > > left outer join users sm on sm.u_id = r.r_salesman > > left outer join users u on r.r_u_id = u.u_id > > left outer join request_types t on r.r_t_id = t.t_id > > left outer join request_states s on r.r_s_id = s.s_id > > left outer join dealerships d on r.r_d_id = d.d_id > > left outer join departments de on r.r_de_id = de.de_id > > left outer join customers c on r.r_c_id = c.c_id > > left outer join comment_tallies co on r.r_id = co.r_id > > left outer join plates pl on r.r_plates = pl.pl_id > > ORDER BY r.r_id; > > > > -- > > Gary Stainburn > > > > This email does not contain private or confidential material as it > > may be snooped on by interested government parties for unknown > > and undisclosed purposes - Regulation of Investigatory Powers Act, > > 2000 > > > > ---(end of > > broadcast)--- TIP 9: the planner will > > ignore your desire to choose an index scan if your joining column's > > datatypes do not match -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] diary constraints
Hi folks I know this has been discussed in the past, but no amount of keywords has returned anything from the archives. I want to create a courtesy car diary diary system where I have a table containing all of the cortesy cars in the pool, and then an allocation table which has two timestamps, one for the start date/time and one for the return date/time. How do I go about creating constraints on inserts and updates to ensure that a) the finish is after the start b) two allocations for a single vehicle don't overlap. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] select best price
Hi folks I've got a table holding item code(cs_id), supplier a/c (co_id) , and price (cs_price). How can I select the rows containing the lowest price for each item code? I've tried various forms of min() etc and know it must be simple but I'm stumped. Gary -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] select best price
On Wednesday 26 October 2005 4:21 pm, Gary Stainburn wrote: > Hi folks > > I've got a table holding item code(cs_id), supplier a/c (co_id) , and > price (cs_price). > > How can I select the rows containing the lowest price for each item > code? > > I've tried various forms of min() etc and know it must be simple but > I'm stumped. > > Gary I've come up with the select below. Is there a better/more efficient way of doing this? select cp.cs_id, from cons_price_details cp, (select cs_id, min(cs_price) as cs_price from cons_price_details group by cs_id ) v where cp.cs_id = v.cs_id and cp.cs_price = v.cs_price; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Update from join
I know this is probably a FAQ but Google etc hasn't helped. I have two tables, both with stock number and registration number in. The second table always has the correct stock number, the first doesn't. I want to copy the data across where the stock number is missing. The select with join shows the rows requiring update, but I can't think how to do the update. goole=# \d test1 Table "public.test1" Column | Type | Modifiers -+---+--- ud_id | integer | not null ud_registration | character varying(20) | ud_stock| character varying(20) | Indexes: "test1_pkey" PRIMARY KEY, btree (ud_id) goole=# \d test2 Table "public.test2" Column | Type | Modifiers +---+--- s_stock_no | character varying(8) | not null s_regno| character varying(12) | Indexes: "test2_pkey" PRIMARY KEY, btree (s_stock_no) goole=# select ud.ud_id, ud.ud_registration, ud.ud_stock, s.s_stock_no from test1 ud, test2 s where upper(ud.ud_registration) = upper(s.s_regno) and upper(ud.ud_stock) ~ '^[NU][LD]$'; ud_id | ud_registration | ud_stock | s_stock_no ---+-+--+ 2359 | YF06YMT | NL | NL6321 2397 | YF06YNC | NL | NL6334 2400 | YB06MJX | ND | ND8402 2422 | YH06VGJ | ND | ND9055 2380 | YF06ZKC | ND | ND9566 2447 | YB06MHX | ND | ND9661 2132 | YC06RZM | ND | ND9527 2429 | YB06SFE | ND | ND9611 2448 | YB06PXV | ND | ND9689 2417 | YF06MXN | ND | ND9012 2489 | YB06HHM | ND | ND9542 2456 | YB06SFJ | ND | ND9675 1666 | YC06RYR | ND | NH310 2455 | YB06ZFH | ND | ND9754 2508 | YF06GWU | NL | NL6245 2655 | YC06SDV | ND | ND9270 2591 | YF06OJM | NL | NL6351 2627 | YC06SGX | ND | ND9057 1795 | YC06SGX | ND | ND9057 2634 | YB06KHT | NL | NL6450 2620 | YF06ZKD | ND | ND9236 (21 rows) -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Update from join
On Friday 07 July 2006 11:29, Gary Stainburn wrote: > I know this is probably a FAQ but Google etc hasn't helped. > > I have two tables, both with stock number and registration number in. > The second table always has the correct stock number, the first doesn't. > > I want to copy the data across where the stock number is missing. The > select with join shows the rows requiring update, but I can't think how to > do the update. > I came up with: goole=# update test1 set ud_stock=(select s_stock_no from test2 where test1.ud_registration = test2.s_regno) where ud_stock ~ '^[NU][LD]$'; UPDATE 679 but as you can see, it updated a hell of a lot more than 21 rows. Can anyone improve on this? Gary -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SELECT substring with regex
On Friday 07 July 2006 14:51, T E Schmitz wrote:
> I would like to split the contents of a column using substring with a
> regular expression:
>
> SELECT
> substring (NAME, '^\\d+mm') as BASE_NAME,
> substring (NAME, ??? ) as SUFFIX
> FROM MODEL
>
> The column contains something like
> "150mm LD AD Asp XR Macro"
> I want to split this into
> "150mm", "LD AD Asp XR Macro"
>
> How can I extract the bit following the matching substring?
select substring('150mm LD AD Asp XR Macro','^\\d+mm') as BASE_NAME,
substring('150mm LD AD Asp XR Macro','^\\d+mm (.*)$') as SUFFIX;
base_name | suffix
---+
150mm | LD AD Asp XR Macro
(1 row)
The brackets surround the required match
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] simple join is beating me
hi folks i have the following: select o_ord_date as o_date, count(o_id) as orders from orders where o_de_id in (5,6) and o_ord_date > CURRENT_DATE-'1 month'::interval group by o_ord_date order by o_date desc and select o_act_del_date as o_date, count(o_id) as delivery from orders where o_de_id in (5,6) and o_act_del_date > CURRENT_DATE-'1 month'::interval and o_act_del_date <= CURRENT_DATE group by o_act_del_date order by o_date desc These give me o_date | orders + 2009-07-10 | 4 2009-07-09 | 5 2009-07-08 | 12 2009-07-07 | 5 2009-07-06 | 2 2009-07-03 | 2 2009-07-02 | 7 2009-07-01 | 19 2009-06-30 | 20 2009-06-29 | 28 and o_date | delivery +-- 2009-07-13 |5 2009-07-10 |3 2009-07-09 |4 2009-07-08 |2 2009-07-07 |4 2009-07-06 |7 2009-07-03 |6 2009-07-02 |5 2009-07-01 |3 2009-06-30 |3 How do i get o_date | orders | delivery ++-- 2009-07-13 ||5 2009-07-10 | 4 |3 2009-07-09 | 5 |4 2009-07-08 | 12 |2 2009-07-07 | 5 |4 2009-07-06 | 2 |7 2009-07-03 | 2 |6 2009-07-02 | 7 |5 2009-07-01 | 19 |3 2009-06-30 | 20 |3 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 -- 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] simple join is beating me
Hi Oliveiros, Thank you for this. However, this does not give me what I want. If a date exists where we have orders but no deliveries the row does not appear. I have tried doing a union to link the two selects together, but i still cannot get anything to work. Gary On Monday 13 July 2009 12:45:49 Oliveiros wrote: > Howdy, Gary, > > I have not the database in this computer, so I cannot test the sql I'm > sending you, but > if you do an outer join won't it result in what you need? Maybe I am not > reaching what you want to do... > > SELECT deliveryQuery.o_date , orders, delivery > FROM (/* ur first query here */) ordersQuery > NATURAL RIGHT JOIN (/* ur second query goes here */) deliveryQuery > ORDER BY deliveryQuery.o_date DESC > > Tararabite, > > Oliveiros > @Allgarve > > > > 2009/7/13 Gary Stainburn > > > hi folks > > > > i have the following: > > > > select o_ord_date as o_date, count(o_id) as orders > >from orders where o_de_id in (5,6) and o_ord_date > CURRENT_DATE-'1 > > month'::interval > >group by o_ord_date > > order by o_date desc > > > > and > > > > select o_act_del_date as o_date, count(o_id) as delivery > >from orders > >where o_de_id in (5,6) and > > o_act_del_date > CURRENT_DATE-'1 month'::interval and > > o_act_del_date <= CURRENT_DATE > >group by o_act_del_date > > order by o_date desc > > > > These give me > > > > o_date | orders > > + > > 2009-07-10 | 4 > > 2009-07-09 | 5 > > 2009-07-08 | 12 > > 2009-07-07 | 5 > > 2009-07-06 | 2 > > 2009-07-03 | 2 > > 2009-07-02 | 7 > > 2009-07-01 | 19 > > 2009-06-30 | 20 > > 2009-06-29 | 28 > > > > and > > > > o_date | delivery > > +-- > > 2009-07-13 |5 > > 2009-07-10 |3 > > 2009-07-09 |4 > > 2009-07-08 |2 > > 2009-07-07 |4 > > 2009-07-06 |7 > > 2009-07-03 |6 > > 2009-07-02 |5 > > 2009-07-01 |3 > > 2009-06-30 |3 > > > > How do i get > > > > o_date | orders | delivery > > ++-- > > 2009-07-13 ||5 > > 2009-07-10 | 4 |3 > > 2009-07-09 | 5 |4 > > 2009-07-08 | 12 |2 > > 2009-07-07 | 5 |4 > > 2009-07-06 | 2 |7 > > 2009-07-03 | 2 |6 > > 2009-07-02 | 7 |5 > > 2009-07-01 | 19 |3 > > 2009-06-30 | 20 |3 > > -- > > Gary Stainburn > > > > This email does not contain private or confidential material as it > > may be snooped on by interested government parties for unknown > > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] simple (?) join
Hi folks. I have two tables create table orders ( o_id serial primary key ... ); create table orders_log ( ol_id serial primary key, o_id int4 not null references orders(o_id), ol_timestamp timestamp, ol_user, ); How can I select all from orders and the last (latest) entry from the orders_log? Cheers -- Gary Stainburn Gary's Haircut 700 Please visit http://www.justgiving.com/Gary-Stainburn/ to help me raise money for Cancer Research - in return I'll have my head shaved -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
