[SQL] time precision.
Hi All, Im using PG-7.2.4 on Solaries. When I do: template1=# select time(6576); ERROR: TIME(6576) precision must be between 0 and 13 Where am I wrong? Pls. help Thanks, Anagha
Re: [SQL] Problem using Subselect results
I did try the following: SELECT DISTINCT ON (two.two_id) two.two_value FROM one,two WHERE (one.two_id=two.two_id AND one.updatenr > two.updatenr) ORDER BY two.updatenr ASC; I thought this would a) order the result list by updatenr b) return the first record only for records that are equal on the two.two_id field which would return exactly what I need - return the record with the But what I get instead is "ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions" *grrrmpfh* But after a while playing around I noticed that the "initial" in that sentence above is important - adding two.two_id as first argument to the ORDER BY does not change the result but fulfill the requirements - the expressions don't have to be excactly the same - just the initial argument. My view which returns the (hopefully) correct results: SELECT DISTINCT ON (two.two_id) two.two_value FROM one, two WHERE ((one.two_id = two.two_id) AND (one.updatenr > two.updatenr)) ORDER BY two.two_id, two.updatenr; Next step is to include corresponding information from table three - now I need subselects right? no way around with this DISTINCT and ORDER by stuff in it ... Many thanks for your help so far, Oliver Quoting Oliver Heinz <[EMAIL PROTECTED]>: > I'll try this tomorrow - combining DISTINCT ON (two.two_id) and sorting by > two.updatenr could (should) have the desired effect - I never thought about > using ORDER and DISTINCT that way. > > I'll report my success or failure... > > Thanks so far! > > Bye, > Oliver - This mail sent through IMP: http://horde.org/imp/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] time precision.
> I'm using PG-7.2.4 on Solaries. > When I do: > > template1# select time(6576); > ERROR: TIME(6576) precision must be between 0 and 13 > > Where am I wrong? What's the purpose of this function? And where did you find it in the docs? I didn't see it before. Regards, Christoph ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] How can I to solute this problem?
Hi,Thank your help yesterday.
Now I met a new question.
When I continued to clicking UI(jsp) some time, I will get a error in page.It is "Http
404" and Exception is "NullPoinerExcaption".
I checked the log of tomcat. It recorded a message"connected database failed. The user
perporty is missing. It is mandoatory".
netstat | grep postgres |wc -l
The connection number is 278
lsof -i :5432 | wc -l
60
How can I solute this problem?
Is there bug in postgresql's jdbc?
Could you give me help?
Best regards
leon
2003-07-30
- Original Message -
From: "Paul Thomas" <[EMAIL PROTECTED]>
To: "Achilleus Mantzios" <[EMAIL PROTECTED]>
Cc: "LEON" <[EMAIL PROTECTED]>; "pgsql-sql @ postgresql . org" <[EMAIL PROTECTED]>
Sent: Tuesday, July 29, 2003 8:37 PM
Subject: Re: [SQL] How can I to solute this problem?
> On 29/07/2003 17:09 Achilleus Mantzios wrote:
> >
> > You may take a look at jboss connection pool
> > mechanism. (postgresql.xml)
>
> He doesn't mention JBoss, just Tomcat so I don't believe the
> postgresql.xml will help him much. He could use Tomcat's built-in
> connection pooling
> (http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html)
>
> but should note that there is an error in the web.xml fragment:
> jdbc/mydb should be
> jdbc/postgres. Leon, if you need more help
> please feel free to contact me off-list.
>
> >
> > Also a good way of ensuring that your app will
> > not leave open stale postgresql connections
> > is to initially configure your pool to only have a small
> > number of connections (e.g. 2,3).
>
> Good advice for any form of connection pooling.
>
> >
> > If your J2EE components (e.g. jsps) are fast enough
> > you should start facing connection shortages
> > only after the load on your machine increases
> > significantly. (and then safely increase the maximum
> > number of connections in your configuration (both postgresql wise
> > and app server (conn pool) wise).
> >
> >
> > On Tue, 29 Jul 2003, Paul Thomas wrote:
> >
> > >
> > > On 29/07/2003 07:18 LEON wrote:
> > > > I use tomcat+linux_postgresql+jsp to develop system.
> > > >
> > > > I start postgresql with 1024 processes.
> > > > FE:
> > > > postmaster -i -S -N 1024 -B 2048 -D /var/lib/pgsql/data
> > > >
> > > > My jsp doesn't implement connection pool.It directly connects
> > postgresql
> > > > by jdbc.
> > >
> > > IME, that is not a good way to do it. Use a connection pool.
> > >
> > > > After I run the Ui some times, the UI(jsp) would report "ieSorry,too
> > many
> > > > clientslg" .The exception is SQLException.
> > > >
> > > > I must restart tomcat or postgresql I can continue to running my UI.
> > >
> > > My guess would be that you have a bug in your application which is not
> > > always closing the connection so eventually you exceed max_connections.
> >
> > > You should always close the connection in a finally{} block so that is
> > > guaranteed that it will be closed regardless of any earlier exceptions
> > > which are thrown.
> > >
> > > HTH
> > >
> > >
> >
> > --
> > ==
> > Achilleus Mantzios
> > S/W Engineer
> > IT dept
> > Dynacom Tankers Mngmt
> > Nikis 4, Glyfada
> > Athens 16610
> > Greece
> > tel:+30-210-8981112
> > fax:+30-210-8981877
> > email: achill at matrix dot gatewaynet dot com
> > mantzios at softlab dot ece dot ntua dot gr
> >
> >
> > ---(end of broadcast)---
> > TIP 6: Have you searched our list archives?
> >
> >http://archives.postgresql.org
> >
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] now 7.2.3 - 7.3.3
this worked in 7.2.3 "select sum(cr) from ar where date(tdate) = now() -1 " but not in 7.3.3 What's the proper syntax for 7.3.3??? thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] now 7.2.3 - 7.3.3
A. Van Hook writes: > this worked in 7.2.3 > "select sum(cr) from ar where date(tdate) = now() -1 " > but not in 7.3.3 > What's the proper syntax for 7.3.3??? now() - interval '1 day/minute/year/second/???' -- Peter Eisentraut [EMAIL PROTECTED] ---(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] How can I to solute this problem?
On 30/07/2003 10:43 LEON wrote: Hi,Thank your help yesterday. Now I met a new question. When I continued to clicking UI(jsp) some time, I will get a error in page.It is "Http 404" and Exception is "NullPoinerExcaption". I checked the log of tomcat. It recorded a message"connected database failed. The user perporty is missing. It is mandoatory". netstat | grep postgres |wc -l The connection number is 278 lsof -i :5432 | wc -l 60 How can I solute this problem? Is there bug in postgresql's jdbc? Could you give me help? You must supply the user name in your DriverManager.getConnection(url, user, password). HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(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] How can I to solute this problem?
You may take a look at jboss connection pool
mechanism. (postgresql.xml).
Also a good way of ensuring that your app will
not leave open stale postgresql connections
is to initially configure your pool to only have a small
number of connections (e.g. 2,3).
If your J2EE components (e.g. jsps) are fast enough
you should start facing connection shortages
only after the load on your machine increases
significantly. (and then safely increase the maximum
number of connections in your configuration (both postgresql wise
and app server (conn pool) wise).
On Tue, 29 Jul 2003, Paul Thomas wrote:
>
> On 29/07/2003 07:18 LEON wrote:
> > I use tomcat+linux_postgresql+jsp to develop system.
> >
> > I start postgresql with 1024 processes.
> > FE:
> > postmaster -i -S -N 1024 -B 2048 -D /var/lib/pgsql/data
> >
> > My jsp doesn't implement connection pool.It directly connects postgresql
> > by jdbc.
>
> IME, that is not a good way to do it. Use a connection pool.
>
> > After I run the Ui some times, the UI(jsp) would report "ieSorry,too many
> > clientslg" .The exception is SQLException.
> >
> > I must restart tomcat or postgresql I can continue to running my UI.
>
> My guess would be that you have a bug in your application which is not
> always closing the connection so eventually you exceed max_connections.
> You should always close the connection in a finally{} block so that is
> guaranteed that it will be closed regardless of any earlier exceptions
> which are thrown.
>
> HTH
>
>
--
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-210-8981112
fax:+30-210-8981877
email: achill at matrix dot gatewaynet dot com
mantzios at softlab dot ece dot ntua dot gr
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] time precision.
On Wed, 30 Jul 2003, Anagha Joshi wrote: > Hi All, > I'm using PG-7.2.4 on Solaries. > When I do: > > template1=# select time(6576); > ERROR: TIME(6576) precision must be between 0 and 13 > > Where am I wrong? IIRC time(n) refers to the type time with a precision of n and n is limited as stated by the error. What are you trying to do? ---(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] Very strange 'now' behaviour in nested triggers.
Bruce Momjian <[EMAIL PROTECTED]> writes: > Strangely, CURRENT_TIMESTAMP converts to 'now', not now(). Is that a > problem? No, it is not, because the text-to-timestamptz conversion is marked volatile and won't be const-folded. I've always thought it was a very ugly implementation though, mainly because it doesn't reverse-list nicely in rule dumps. Sometime we should try to fix it so that it reverse-lists as "current_timestamp(n)", and likewise for the similar special cases in gram.y. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] now 7.2.3 - 7.3.3
"A. Van Hook" <[EMAIL PROTECTED]> writes: > this worked in 7.2.3 > "select sum(cr) from ar where date(tdate) = now() -1 " > but not in 7.3.3 > What's the proper syntax for 7.3.3??? Perhaps you want current_date - 1. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem using Subselect results
Oliver, > SELECT DISTINCT ON (two.two_id) two.two_value FROM one,two WHERE > (one.two_id=two.two_id > AND one.updatenr > two.updatenr) ORDER BY two.updatenr ASC; FWIW, SELECT DISTINCT ON () is slower than SELECT ORDER BY ... LIMIT 1 on all stable versions of Postgres. Not that the LIMIT 1 method can be used with all queries. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] Fwd: Bad Join moment - how is this happening?
I fully admit that I've been staring at this too long, and simply don't
understand what is wrong. Apologies aside, any kind sql hackers who care
to look this over will earn my undying gratitude, and a beer in the bar
of your choice, should we ever meet.
General issue: I'm getting cartesean products instead of left joins, and
I feel like a moron.
I have two tables (more actually, but the result will fix all of them):
dlm=# \d documents
Table "public.documents"
Column |Type | Modifiers
---+-+---
id| integer | not null default
nextval('public.documents_id_seq'::text)
projects_id | text|
doc_num | text|
description | text|
date | timestamp without time zone |
createdate| timestamp without time zone | default now()
moddate | timestamp without time zone |
people_id | integer |
parent| integer |
document_type | integer |
state | integer |
machines_id | integer |
phases_id | integer |
Indexes: documents_id_pkey primary key btree (id),
documents_parent_seq btree (parent),
documents_people_id_seq btree (people_id),
documents_projects_id btree (projects_id)
Foreign Key constraints: phases_exists FOREIGN KEY (phases_id) REFERENCES phases(id)
ON UPDATE NO ACTION ON DELETE NO ACTION,
parent_exists FOREIGN KEY (parent) REFERENCES documents(id)
ON UPDATE NO ACTION ON DELETE NO ACTION,
machine_exists FOREIGN KEY (machines_id) REFERENCES
machines(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
people_exists FOREIGN KEY (people_id) REFERENCES people(id)
ON UPDATE NO ACTION ON DELETE NO ACTION,
project_exists FOREIGN KEY (projects_id) REFERENCES
projects(id) ON UPDATE NO ACTION ON DELETE NO ACTION
Triggers: documents_timestamp_tr
dlm=# \d d_addenda
Table "public.d_addenda"
Column | Type | Modifiers
---+-+---
id| integer | not null default
nextval('public.d_addenda_id_seq'::text)
documents_id | integer |
item_num | text|
drawing_reference | text|
Indexes: d_addenda_id_pkey primary key btree (id),
d_addenda_documents_id_idx btree (documents_id)
Foreign Key constraints: documents_exists FOREIGN KEY (documents_id) REFERENCES
documents(id) ON UPDATE NO ACTION ON DELETE NO ACTION
I have a view:
create or replace view addenda as
select
documents.id,
documents.oid,
documents.projects_id,
documents.doc_num,
documents.description,
documents.date,
documents.createdate,
documents.moddate,
documents.people_id,
documents.parent,
documents.document_type,
documents.state,
documents.machines_id,
documents.phases_id,
d_addenda.item_num,
d_addenda.drawing_reference
from
d_addenda as a, documents as d
where a.documents_id = d.id;
I appear to be getting a cartesean product when I select against the view
'addenda', when I want a left inner join. That is, I want documents
records matched to addenda records only when there is a record in
d_addenda with a documents_id that matches the id field in documents.
An example:
dlm=# select * from documents;
id | projects_id | doc_num |description | date|
createdate | moddate | people_id | parent |
document_type | state | machines_id | phases_id
+-+-++---+++---++---+---+-+---
10 | 1 | | this is a test description | |
2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.094521 | ||
| | |
2 | 1 | foo123 | description text | 2003-07-27 19:03:01.30362 |
2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | ||
1 | 1 | |
(2 rows)
dlm=# select * from d_addenda;
id | documents_id | item_num | drawing_reference
+--+--+---
7 | 10 | 2| none
2 |2 | 1| foo
(2 row
Re: [SQL] Fwd: Bad Join moment - how is this happening?
Jamie, > General issue: I'm getting cartesean products instead of left joins, and > I feel like a moron. No, this is extremely bizarre. I can't think of an explanation for the results you're getting, except that the view definition isn't what you think it is. Can you do a \d addenda and post the results? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] One to many query question
Hi, I'm having trouble with what I think should be an easy query. For simplicity, I will use a CD database as an example. Each CD may have multiple genres. Here's some sample data: Artist Title Genres -- -- Miles Davis Some Kind of Blue Jazz Metallica Ride the Lightning Rock Chemical Brothers Surrender Electronic Radiohead OK Computer Rock, Electronic For simplicities sake, let's ignore normalization on artist and genre, and say the tables look like: CREATE TABLE cd ( id integer unique, artist varchar(25), title varchar(25) ); CREATE TABLE cd_genres ( cd_id integer, genre varchar(25) ); How do I write a query to find all CDs that are NOT Rock? A co-worker showed me the following query: SELECT cd.*, rock.genre AS rock, jazz.genre AS jazz, electronic.genre AS electronic FROM cd LEFT JOIN cd_genres rock ON (cd.id = rock.cd_id AND rock.genre = 'Rock') LEFT JOIN cd_genres jazz ON (cd.id = jazz.cd_id AND jazz.genre = 'Jazz') LEFT JOIN cd_genres electronic ON (cd.id = electronic.cd_id AND electronic.genre = 'Electronic'); This produces the following results, which seems to essentially de-normalize the data: id | artist | title| rock | jazz | electronic +---++--+--+ 1 | Miles Davis | Some Kind of Blue | | Jazz | 2 | Metallica | Ride the Lightning | Rock | | 3 | Chemical Brothers | Surrender | | | Electronic 4 | Radiohead | OK Computer| Rock | | Electronic (4 rows) Then to filter out those NOT Rock, I can add a: WHERE rock.genre IS NULL While, this *does* work, I have this feeling there is a better way (and I'm not sure of the performance). If I add more genres, I have to add more LEFT JOINs. I *could* actually create a column per genre, but this means adding and removing genres requires an alter table. And I'd rather actually normalize further such that the list of genres is in its *own* table. Any thoughts? I'm pretty much a SQL newbie, so pointers to good books or articles would also be helpful. Thanks! -Dave ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Fwd: Bad Join moment - how is this happening?
On Wednesday 30 July 2003 21:07, Jamie Lawrence wrote: > I fully admit that I've been staring at this too long, and simply don't > understand what is wrong. Apologies aside, any kind sql hackers who care > to look this over will earn my undying gratitude, and a beer in the bar > of your choice, should we ever meet. I'll take that beer (assuming I'm right) > General issue: I'm getting cartesean products instead of left joins, and > I feel like a moron. Nope - it's a subtle one. > I have a view: > > create or replace view addenda as > select > documents.id, > documents.oid, > documents.projects_id, > documents.doc_num, > documents.description, > documents.date, > documents.createdate, > documents.moddate, > documents.people_id, > documents.parent, > documents.document_type, > documents.state, > documents.machines_id, > documents.phases_id, > > d_addenda.item_num, > d_addenda.drawing_reference > > from > d_addenda as a, documents as d > where a.documents_id = d.id; > > > I appear to be getting a cartesean product when I select against the view > 'addenda', when I want a left inner join. That is, I want documents > records matched to addenda records only when there is a record in > d_addenda with a documents_id that matches the id field in documents. I think this is the "adding a table into the FROM" feature of PG. You're referring to documents.xxx in the select and d.id in the FROM. PG tries to help out by adding the table into the FROM for you - hence cartesian join. I think you can turn this "feature" off in the config file in 7.3.x (haven't checked this though) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Fwd: Bad Join moment - how is this happening?
Richard, > I think this is the "adding a table into the FROM" feature of PG. You're > referring to documents.xxx in the select and d.id in the FROM. PG tries to > help out by adding the table into the FROM for you - hence cartesian join. Aha! You're right. > I think you can turn this "feature" off in the config file in 7.3.x (haven't > checked this though) Nope, it's a 7.4 feature to turn it off in .conf. Look under the "compatibility" section (the last section) in 7.4's postgresql.conf. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Fwd: Bad Join moment - how is this happening?
On Wed, 30 Jul 2003, Jamie Lawrence wrote: > I have a view: > > create or replace view addenda as > select > documents.id, > documents.oid, > documents.projects_id, > documents.doc_num, > documents.description, > documents.date, > documents.createdate, > documents.moddate, > documents.people_id, > documents.parent, > documents.document_type, > documents.state, > documents.machines_id, > documents.phases_id, > > d_addenda.item_num, > d_addenda.drawing_reference > > from > d_addenda as a, documents as d > where a.documents_id = d.id; I think you're being bitten by from clause addition. You're referencing documents in the select list rather than d so it's probably adding an unconstrained join to documents. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] One to many query question
On Wed, 2003-07-30 at 12:35, Dave Dribin wrote: > CREATE TABLE cd ( > id integer unique, > artist varchar(25), > title varchar(25) > ); > > CREATE TABLE cd_genres ( > cd_id integer, > genre varchar(25) > ); I think you've got this backwards. There is no advantage in the above table's over simply having a genre varchar(25) in the cd table. You really want: CREATE TABLE genre ( genre_id serial, genre varchar(25) ); CREATE TABLE cd ( cd_id integer unique, artist varchar(25), title varchar(25), genre_id varchar(25) references genre (genre_id) ); > How do I write a query to find all CDs that are NOT Rock? A co-worker > showed me the following query: Now the query is simple: SELECT cd.*, genre.genre FROM cd, genre WHERE cd.genre_id = genre.genre_id AND genre.genre != 'Rock'; Hope that helps, Eric ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] One to many query question
On Wed, Jul 30, 2003 at 01:11:35PM -0700, Eric Clark wrote: > On Wed, 2003-07-30 at 12:35, Dave Dribin wrote: > > CREATE TABLE cd ( > > id integer unique, > > artist varchar(25), > > title varchar(25) > > ); > > > > CREATE TABLE cd_genres ( > > cd_id integer, > > genre varchar(25) > > ); > > I think you've got this backwards. There is no advantage in the above > table's over simply having a genre varchar(25) in the cd table. > > You really want: > > CREATE TABLE genre ( > genre_id serial, > genre varchar(25) > ); > > CREATE TABLE cd ( > cd_id integer unique, > artist varchar(25), > title varchar(25), > genre_id varchar(25) references genre (genre_id) > ); This doesn't allow multiple genre's per CD, though, does it? A CD can only have 1 genre_id. I would like the ability to have multiple genres, in which case a third table is necessary: CREATE TABLE cd_genres ( cd_id integer, genre_id integer ); cd_id references cd.id and genre_id references genre.genre_id. This still requires the complex LEFT JOIN query from my first post, too, I think, *plus* an extra join between cd_genres and genre. -Dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Nonexistent NEW relation in some places of rules
On Tue, Jul 29, 2003 at 06:32:44PM -0400, Tom Lane wrote: > The problem is that the rule gets expanded into something rather like > > insert into a select ... from new, produce(new.b); > > and we can't support that. If we ever add support for SQL99's > LATERAL(), it might help improve matters. Thanks. I was suspecting something like. Does the presence of this leak mean that such a selects are needed very rarely? Or does it mean that some well-known workaround exists? ---(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] One to many query question
On Wednesday 30 July 2003 20:35, Dave Dribin wrote: > Hi, I'm having trouble with what I think should be an easy query. For > simplicity, I will use a CD database as an example. Each CD may have > multiple genres. Here's some sample data: > > Artist Title Genres > -- -- > Miles Davis Some Kind of Blue Jazz > Metallica Ride the Lightning Rock > Chemical Brothers Surrender Electronic > Radiohead OK Computer Rock, Electronic > > For simplicities sake, let's ignore normalization on artist and genre, > and say the tables look like: > > CREATE TABLE cd ( > id integer unique, > artist varchar(25), > title varchar(25) > ); > > CREATE TABLE cd_genres ( > cd_id integer, > genre varchar(25) > ); > > How do I write a query to find all CDs that are NOT Rock? A co-worker > showed me the following query: Basically you need to find all the CDs that ARE "rock" and subtract that set from the set of all CDs. You could use: SELECT id,artist,title FROM cd WHERE NOT EXIST (SELECT 1 FROM cd_genres WHERE cd_id=id AND genre='Rock'); or SELECT id,artist,title,cd_id FROM cd LEFT JOIN (SELECT cd_id FROM cd_genres WHERE genre='Rock') AS rock_cds ON cd_id=id WHERE cd_id IS NULL; or an EXCEPT clause etc. See which you like better. -- Richard Huxton Archonet Ltd ---(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] One to many query question
Dave Dribin wrote: Hi, I'm having trouble with what I think should be an easy query. For simplicity, I will use a CD database as an example. Each CD may have multiple genres. Here's some sample data: Artist Title Genres -- -- Miles Davis Some Kind of Blue Jazz Metallica Ride the Lightning Rock Chemical Brothers Surrender Electronic Radiohead OK Computer Rock, Electronic For simplicities sake, let's ignore normalization on artist and genre, and say the tables look like: CREATE TABLE cd ( id integer unique, artist varchar(25), title varchar(25) ); CREATE TABLE cd_genres ( cd_id integer, genre varchar(25) ); How do I write a query to find all CDs that are NOT Rock? What about select * from cd where not exists (select 1 from cd_genres where cd_id = cd.id and genre='Rock')? Dima ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] One to many query question
> On Wed, Jul 30, 2003 at 01:11:35PM -0700, Eric Clark wrote: > > On Wed, 2003-07-30 at 12:35, Dave Dribin wrote: > > > CREATE TABLE cd ( > > > id integer unique, > > > artist varchar(25), > > > title varchar(25) > > > ); > > > > > > CREATE TABLE cd_genres ( > > > cd_id integer, > > > genre varchar(25) > > > ); > > > > I think you've got this backwards. There is no advantage in the above > > table's over simply having a genre varchar(25) in the cd table. > > > > You really want: > > > > CREATE TABLE genre ( > > genre_id serial, > > genre varchar(25) > > ); > > > > CREATE TABLE cd ( > > cd_id integer unique, > > artist varchar(25), > > title varchar(25), > > genre_id varchar(25) references genre (genre_id) > > ); > > This doesn't allow multiple genre's per CD, though, does it? A CD > can only have 1 genre_id. I would like the ability to have multiple > genres, in which case a third table is necessary: > > CREATE TABLE cd_genres ( > cd_id integer, > genre_id integer > ); > > cd_id references cd.id and genre_id references genre.genre_id. > > This still requires the complex LEFT JOIN query from my first post, > too, I think, *plus* an extra join between cd_genres and genre. > > -Dave What you may be looking for is a not exists subselect. Im not sure if this quite fits your example.. but maybe it will give you some ideas... SELECT cd.*, rock.genre AS rock, jazz.genre AS jazz, electronic.genre AS electronic FROM cd LEFT JOIN cd_genres jazz ON (cd.id = jazz.cd_id AND jazz.genre = 'Jazz') LEFT JOIN cd_genres electronic ON (cd.id = electronic.cd_id AND electronic.genre = 'Electronic'); WHERE NOT EXISTS (SELECT cd.id FROM cd join cd_genres rock ON (cd.id = rock.cd_id AND rock.genre = 'Rock')) This is quite fast in postgres unless configured wrong.. be sure to join your subselect to your outer query. Hope that helps Chad ---(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] One to many query question
> This doesn't allow multiple genre's per CD, though, does it? A CD > can only have 1 genre_id. I would like the ability to have multiple > genres, in which case a third table is necessary: > > CREATE TABLE cd_genres ( > cd_id integer, > genre_id integer > ); > > cd_id references cd.id and genre_id references genre.genre_id. > > This still requires the complex LEFT JOIN query from my first post, > too, I think, *plus* an extra join between cd_genres and genre. Sorry, the cd_genre table would be the way to do it. This was a fairly complex problem so I created the tables in a test database and wrote a few queries that I think solve the problem for you, depending on how you want select to return the genre list. Here's a couple queries that will only get cd's that are not part of Rock. SELECT c.* FROM cd AS c WHERE 'Rock' NOT IN (SELECT g.genre FROM genre AS g, cd_genre AS cg WHERE g.genre_id = cg.genre_id AND cg.cd_id = c.cd_id); SELECT c.* FROM cd AS c WHERE NOT EXISTS (SELECT NULL FROM genre AS g, cd_genre AS cg WHERE g.genre_id = cg.genre_id AND cg.cd_id = c.cd_id AND g.genre = 'Rock'); The second one should be faster. The next query will show all cd's that not exclusively Rock. (OK Computer should be in the result) SELECT c.* FROM cd AS c, cd_genre AS cg, genre AS g WHERE c.cd_id = cg.cd_id AND cg.genre_id = g.genre_id AND g.genre != 'Rock'; If you add g.genre to any of the above queries you will get one row per cd+genre combination. I dont know of any way to make that a delimited list other than writing a function. So I wrote one for fun. The argument is the cd_id. CREATE OR REPLACE FUNCTION genre_list (integer) RETURNS TEXT AS ' DECLARE cdid ALIAS FOR $1; return_val TEXT; r RECORD; BEGIN FOR r IN SELECT g.genre FROM genre AS g, cd_genre AS cg WHERE g.genre_id = cg.genre_id AND cg.cd_id = cdid LOOP IF return_val IS NULL THEN return_val := r.genre; ELSE return_val := return_val || '', '' || r.genre; END IF; END LOOP; RETURN return_val; END ' LANGUAGE 'plpgsql' STABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER; Now I see that while composing this email some others have replied with more simple solutions. For your case I prefer the added tables as they enforce the possible list of genre's. Its also handy to keep them seperate to get the list of genre's to display in a UI. Eric ps: aliasing all the table names is just my habit, do it however you see fit. I also dont like to make my table names plural, its implied. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] One to many query question
On Wed, Jul 30, 2003 at 05:26:23PM -0400, Dmitry Tkach wrote: > >How do I write a query to find all CDs that are NOT Rock? > > > What about > select * from cd where not exists (select 1 from cd_genres where cd_id > = cd.id and genre='Rock')? Thanks everyone! This did indeed work, and it does seem clearer. I never knew about EXISTS before. How portable is this? I'm interested in supporting PostgreSQL and MS SQL Server for now, and possibly Oracle and MySQL in the future. -Dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] function returning setof performance question
Mark, I'm crossing this over to the performance list; it's really appropriate on both lists. So I'm quoting you in full as well. > I have a question regarding the performance of a function returning a > set of a view as opposed to just selecting the view with the same > where clause. Please, if this should go to the performance list instead, > let me know. I'm just wondering about this from the sql end of things. > > Here's the environment: > > I'm working from PHP, calling on the query. > > I have a view that joins 12 tables and orders the results. > > From PHP, I do a select on that view with a where clause. > > I created a function that queries the view with the where clause > included in the function. The function is returning a setof that > view taking one variable for the where clause (there are several > other static wheres in there). > > I have found that querying the view with the where clause is > giving me quicker results than if I call the function. > > The performance hit is tiny, we're talking less than 1/2 a second, > but when I've done this sort of thing in Oracle I've seen a performance > increase, not a decrease. > > Any ideas? Actually, this is exactly what I'd expect in your situation. The SRF returns the records in a very inefficient fashion: by materializing the result set and looping through it to return it to the calling cursor, whereas the View does set-based operations to grab blocks of data. Also PL/pgSQL as a language is not nearly as optimized as Oracle's PL/SQL. It's also possible that PostgreSQL handles criteria-filtered views better than Oracle does. I wouldn't be surprised. The only times I can imagine an SRF being faster than a view with a where clause are: 1) When you're only returning a small part of a complex result set, e.g. 10 rows out of 32,718. 2) When the view is too complex (e.g. UNION with subselects) for the Postgres planner to "push down" the WHERE criteria into the view execution. I've been planning on testing the performance of SRFs vs. views myself for paginated result sets in a web application, but haven't gotten around to it since I can't get my www clients to upgrade to 7.3 ... -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Unsubscribe
Unsubscribe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL]
Unsubscribe ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] ALTER TABLE ... DROP CONSTRAINT
Hi all! Who can tell me what postgres version supports ALTER TABLE... DROP CONSTRAINT without the need of droping the table to remove a simple coinstraint. (link) >\\\!/< 55 11 5080 9283 !_"""_! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0---( )--( ) \ ( ) / \_/ \_/
Re: [SQL] ALTER TABLE ... DROP CONSTRAINT
Hi all! Who can tell me what postgres version supports ALTER TABLE... DROP CONSTRAINT without the need of droping the table to remove a simple coinstraint. (link) >\\\!/< 55 11 5080 9283 !_"""_! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0---( )--( ) \ ( ) / \_/ \_/ 7.3 supports the drop constraint. The only exception begin if you upgraded your database. It keeps the existing trigger like constraints if youve moved from 7.1 or 7.2. But these triggers can be dropped as well. HTHChad P.S. Great signature! :-)
Re: [SQL] ALTER TABLE ... DROP CONSTRAINT
I think you can do some constraints in 7.2, but 7.3 will allow dropping them all in that fashion. On Wed, 2003-07-30 at 11:52, Elielson Fontanezi wrote: > Hi all! > > Who can tell me what postgres version supports ALTER TABLE... DROP > CONSTRAINT without > the need of droping the table to remove a simple coinstraint. (link) > >>\\\!/< 55 11 5080 9283 >!_"""_! Elielson Fontanezi >(O) (o) PRODAM - Technical > Support Analyst > ---oOOO--(_)--OOOo--- > Success usually comes to those who are too busy to be looking for > it. > 0 0 > ---()--( > ) > \ () / > \_/\_/ > > > signature.asc Description: This is a digitally signed message part
Re: [SQL] ALTER TABLE ... DROP CONSTRAINT
It works in 7.3.2. George - Original Message - From: Elielson Fontanezi To: pgsql-general ; pgsql-sql Sent: Wednesday, July 30, 2003 10:52 AM Subject: [SQL] ALTER TABLE ... DROP CONSTRAINT Hi all! Who can tell me what postgres version supports ALTER TABLE... DROP CONSTRAINT without the need of droping the table to remove a simple coinstraint. (link) >\\\!/< 55 11 5080 9283 !_"""_! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0---( )--( ) \ ( ) / \_/ \_/
Re: [SQL] ALTER TABLE ... DROP CONSTRAINT
if the constraint are named $1 $2 etc you will need to quote them eg ALTER TABLE xyz DROP CONSTRAINT "$1" ; in some version you may require ALTER TABLE xyz DROP CONSTRAINT "$1" RESTRICT; What is ur version btw? try to post the table structure also. regds mallah. Elielson Fontanezi wrote: Hi all! Who can tell me what postgres version supports ALTER TABLE... DROP CONSTRAINT without the need of droping the table to remove a simple coinstraint. (link) >\\\!/< 55 11 5080 9283 !_"""_! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst ---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0 ---()--( ) \ () / \_/\_/ ---(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: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs
Does Oracle have a syntax for this?
---
Robert Treat wrote:
> On Wednesday 23 July 2003 19:06, Bruce Momjian wrote:
> > Robert Treat wrote:
> > > On Wed, 2003-07-23 at 15:38, [EMAIL PROTECTED] wrote:
> > > > FOR myrec IN EXECUTE myinfo LOOP
> > > > biglist := myrec.info;
> > > > END LOOP;
> > >
> > > One other thing, I hate when I have to do things like the above, can we
> > > get a TODO like:
> > >
> > > allow 'EXECUTE var INTO record' in plpgsql
> >
> > So the TODO would be?
> >
> > Allow PL/pgSQL EXECUTE to return a single record outside a loop
>
> that's what I wrote, but not what I meant :-) I do like the sound of it
> though, but really what I meant to say was:
> EXECUTE var1 INTO var2
> but this assumes a number of things, namely that executing var1 will return
> only one field, and one row. I guess that would be:
> Allow PL/pgSQL EXECUTE to return a single variable outside a loop
>
> Robert Treat
> --
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
