>
> If you need only not null-kdt_id and dor_id, then just change
> your joins
> into inner joins.
>
The whole idea is to show expected expenses (in "koostude_detailid") even if they were
actually not spent (the same material is not listed in "dokumentide_read"). And also
show actual expens
Hello,
I have performance problem of an request of type ... WHERE ... IN ( ... ).
How to improve performance of this type of request when a group of id in the
'in' is important.
Thanks for your answers.
Templth
_
Add photos to your
On Wed, 15 Jan 2003, Th Templ wrote:
> Hello,
>
> I have performance problem of an request of type ... WHERE ... IN ( ... ).
> How to improve performance of this type of request when a group of id in the
> 'in' is important.
Try WHERE ... EXISTS (...).
> Thanks for your answers.
> Templth
>
>
>
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
The following does not work:
create index session_u_idx on session (to_char(created, ''));
ERROR: parser: parse error at or near "''" at character 57
Can I make a function to do this and index using the result of that funtion?
Do anyone hav
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wednesday 15 January 2003 11:37, you wrote:
> The following does not work:
>
> create index session_u_idx on session (to_char(created, ''));
> ERROR: parser: parse error at or near "''" at character 57
>
> Can I make a function to do this a
On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On Wednesday 15 January 2003 11:37, you wrote:
> > The following does not work:
> >
> > create index session_u_idx on session (to_char(created, ''));
> > ERROR: parser: parse error at or nea
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wednesday 15 January 2003 16:12, you wrote:
> On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > On Wednesday 15 January 2003 11:37, you wrote:
> > > The following does not work:
> > >
> >
HI, PPL!
How am I able to sort query results by relevance?
I use contrib/tsearch to search using fill text index!
--
WBR, sector119
msg09000/pgp0.pgp
Description: PGP signature
i'm trying to port an existing application from Oracle8i to PostgreSQL but
i'm having problems understanding a certain outer join query type used in
the application. the query includes a normal outer join between two tables
but also uses outer join syntax to join a table with a constant. here's a
On 15 Jan 2003 at 16:31, Marko Asplund wrote:
>
> i'm trying to port an existing application from Oracle8i to PostgreSQL but
> i'm having problems understanding a certain outer join query type used in
> the application. the query includes a normal outer join between two tables
> but also uses out
> -Original Message-
> From: Marko Asplund [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 15, 2003 4:31 PM
> To: [EMAIL PROTECTED]
> Subject: [SQL] Oracle outer join porting question
>
>
>
> i'm trying to port an existing application from Oracle8i to
> PostgreSQL but
> i'm havi
On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On Wednesday 15 January 2003 16:12, you wrote:
> > On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> > > -BEGIN PGP SIGNED MESSAGE-
> > > Hash: SHA1
> > >
> > > On Wednesday 15 January 2
On 15 Jan 2003 at 16:40, Tambet Matiisen wrote:
> Try this:
>
> SELECT doc.id,doc.title,sub.user_id,sub.operation
> FROM document doc LEFT OUTER JOIN document_subscription sub
> ON sub.document_id = doc.id AND sub.user_id = 6;
FWIW:
test=# SELECT doc.id,doc.title,sub.user_id,sub.operation
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wednesday 15 January 2003 18:55, Achilleus Mantzios wrote:
> On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > On Wednesday 15 January 2003 16:12, you wrote:
> > > On Wed, 15 Jan 2003, And
On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On Wednesday 15 January 2003 18:55, Achilleus Mantzios wrote:
> > On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> > > -BEGIN PGP SIGNED MESSAGE-
> > > Hash: SHA1
> > >
> > > On Wednesd
Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> create index session_u_idx on session (drus(created)) where username is null;
> select to_char(created, 'IW') as week, count(session_id) from session WHERE
> username IS NULL and drus(created) = '2002' group by week ORDER BY week;
> This table i
Th,
> > I have performance problem of an request of type ... WHERE ... IN (
> ... ).
> > How to improve performance of this type of request when a group of
> id in the
> > 'in' is important.
>
> Try WHERE ... EXISTS (...).
The explanation of the difference btw. IN and EXISTS is in the
PostgreSQL
I don't have "real-life" experience with partial
indexes but...
You probably won't see an increase in speed unless the index use can get you
down to a really small fraction of your total row count (I don't know just
how small but remember being surprised at the number but realizing, on
reflect
There have been a few posts recently where people have had problems with
nulls. Anyone got comments on the below before I submit it to techdocs?
TIA
- Richard Huxton
A Brief Guide to NULLs
==
What is a null?
===
A null is *not* an empty string.
A null is *not* a
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wednesday 15 January 2003 20:02, you wrote:
> You didnt try it!!
>
> Change your to_char(created, '''')||$2 to
> to_char(created, '''')||(coalesce($2,)
> (provided there is no user named mister '' :)
>
> then perform your query like:
>
>
Vernon,
> In regarding of recomposing multivalued field as a separated table,
I
> have observed some advantages and
> disadvantages of the approach. Good on search as you have pointed out
> and bad on updating data, two operations
> needed: deletion and insertion. A query may need to join a
Steve,
> BTW, I tried to create an index on the to_char function and had no
> luck -
> seems like it should work but it doesn't on 7.2.3 or 7.3.1.
That's because functional indexes can't take any arguments other than
column names. Therefore you'd need to:
CREATE FUNCTION to_year (timestamp) R
On Wed, 15 Jan 2003 [EMAIL PROTECTED] wrote:
> HI, PPL!
>
> How am I able to sort query results by relevance?
> I use contrib/tsearch to search using fill text index!
>
Use OpenFTS (openfts.sourceforge.net) for relevance ranking.
we might add relevance feature to tsearch though.
>
Rega
Sometimes recursion makes my head spin...
Imagine that I have a database that holds the structure of my
filesystem. There is a table called files that contains every piece of
info you would ever want to know about a file, including a unique ID
called fileid.
| files
x| fileid
| filenam
Hi, Josh,
I appreciate you share your experience here. I definitely don't have that many years'
DBA experience behind, and are
learning to get DB design right at the first place.
What I stated is my observation on my project with over twenty multivalued detail
tables. I have a selection quer
Another viewpoint, is to go a bit further into implementation...
i.e. for every field, there are two items...
*one item being the null/notnull info
*other item being the value info of the field per the type at def time
This faciliatates the concepts of
where is null ... testing the null/notnull in
*Disclaimer:* I do not have any formal training in database
theory. However, I have done quite a lot of work with databases in
practice, and have some understanding of "missing values" in statistics.
I would tend to think of the "no applicable value" case of a NULL as being
the result of poor or l
Vernon,
> What I stated is my observation on my project with over twenty multivalued
detail tables. I have a selection query
> contained 200 characters, involving 10 tables, and using subquery. The
performance is not bad after properly indexing,
> least than 3 second (what the planner says).
Richard,
Thanks for writing this! Would it be cool if we included it in the Linux
Weekly News? I'll ask Robert.
Two edits:
On Wednesday 15 January 2003 09:23, [EMAIL PROTECTED] wrote:
> There have been a few posts recently where people have had problems with
> nulls. Anyone got comments on t
[ followup on a gripe from October ]
Tomasz Myrta <[EMAIL PROTECTED]> writes:
> I want to perform query looking like this:
> select
> user_id,
> a/sum_a as percent_a,
> b/sum_b as percent_b
> from
> users join
> (select
> group_id,
> sum(a) as sum_a,
> sum(b) as sum_b
>f
This should be added (From PostgreSQL integrated docs...):
SQL uses a three-valued Boolean logic where the null value represents
"unknown". Observe the following truth tables:
a | b | a AND b | a OR b
--+---+-+
TRUE | TRUE | TRUE| TRUE
TRUE | FALSE | FALSE
Matthew Nuzum wrote:
Sometimes recursion makes my head spin...
Imagine that I have a database that holds the structure of my
filesystem. There is a table called files that contains every piece of
info you would ever want to know about a file, including a unique ID
called fileid.
| files
===
On Wed, Jan 15, 2003 at 09:19:47PM +0300, Oleg Bartunov wrote:
> On Wed, 15 Jan 2003 [EMAIL PROTECTED] wrote:
>
> > HI, PPL!
> >
> > How am I able to sort query results by relevance?
> > I use contrib/tsearch to search using fill text index!
> >
>
> Use OpenFTS (openfts.sourceforge.net) for relev
> -Original Message-
> From: Tomasz Myrta [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 15, 2003 3:00 PM
> To: Matthew Nuzum
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] lost on self joins
> >Finaly, a table to allow a many to many join called files_folders
> > | files_folders
> >==
On Wed, Jan 15, 2003 at 17:23:09 -,
[EMAIL PROTECTED] wrote:
>
> How do nulls work?
> ==
> There is one very important rule when dealing with nulls. The result of
> any operation or comparison, when applied to a null is null. The only
> exception is testing if a value is null
On Wed, Jan 15, 2003 at 13:29:47 -0500,
Matthew Nuzum <[EMAIL PROTECTED]> wrote:
>
> Can anyone suggest a way for me to get the information I need? I'm very
> content to use a simple pl/pgsql function, however I don't know how I'd
> use recursion there.
Look at the tablefunc contrib package. I
A few more comments...
> It might be useful to be able to distinguish between these two cases - not
> applicable and unknown, but there is only one option "Null" available to
> us, so we can't.
If we really need to distinguish between these two cases, I think null
shouldn't be used as a N/A valu
On Wed, Jan 15, 2003 at 03:19:38PM -0500, Matthew Nuzum wrote:
> > -Original Message-
> > From: Tomasz Myrta [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, January 15, 2003 3:00 PM
> > To: Matthew Nuzum
> > Cc: [EMAIL PROTECTED]
> > Subject: Re: [SQL] lost on self joins
> > >Finaly, a tabl
I have a table I want to join on, but the conditions that restrict it span more than one
table. For example:
create table num_tab (thekey int primary key, val int, class char);
create table class_tab (class char primary key, tkey int);
create table txt_tab (thekey int primary key, class int, txt
Well, this is somewhat of a follow up to my previous post regarding self
joins. Now what I'm hoping to do is "virtually" combine the results
from two different record sets into one apparent record set.
Here is the skeleton of my application's data structure. There is a
table called "folders" and
[EMAIL PROTECTED] writes:
> A null is *not* a value.
> A null is *not* a "special" value.
> A null is the absence of a value.
A quotation directly from the SQL standard:
Every data type includes a special value, called the null value,
This seems to directly contradict those three statements
On Wed, 15 Jan 2003, Joseph Shraibman wrote:
> select distinct n.thekey, n.val, t.txt FROM num_tab n LEFT JOIN txt_tab t ON
>n.thekey =
> t.thekey
> WHERE n.thekey < 5 AND t.class = class_tab.tkey AND n.class = class_tab.class;
> produces:
Note that the above uses the non-standard postgres b
--- [EMAIL PROTECTED] wrote:
> There have been a few posts recently where people
> have had problems with
> nulls. Anyone got comments on the below before I
> submit it to techdocs?
>
> TIA
>
> - Richard Huxton
>
> A Brief Guide to NULLs
> ==
>
> What is a null?
> =
On Wed, 15 Jan 2003, Matthew Nuzum wrote:
> Well, this is somewhat of a follow up to my previous post regarding self
> joins. Now what I'm hoping to do is "virtually" combine the results
> from two different record sets into one apparent record set.
Fortunately we have the set functions, specif
Hmm... I've never used this before. I'll try it.
Thanks for your help and your quick reply!
--
Matthew Nuzum
www.bearfruit.org
[EMAIL PROTECTED]
> Fortunately we have the set functions, specifically UNION ALL in this
> case.
>
> Maybe something like (minus the number of files/empty part):
>
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> [EMAIL PROTECTED] writes:
>> A null is *not* a value.
>> A null is *not* a "special" value.
>> A null is the absence of a value.
> A quotation directly from the SQL standard:
> Every data type includes a special value, called the null value,
> Thi
Hi,
After doing a pg_dump on a database I'm unable to access the file.
My command is simply 'mv' ::
mv camper.dump20020116 camper_bak/
The error I get is ::
mv: camper.dump20020116: Value too large for defined data type
Strange. It seems to be saying the file I created is too large to handle.
On Wed, Jan 15, 2003 at 09:19:47PM +0300, Oleg Bartunov wrote:
> On Wed, 15 Jan 2003 [EMAIL PROTECTED] wrote:
>
> > HI, PPL!
> >
> > How am I able to sort query results by relevance?
> > I use contrib/tsearch to search using fill text index!
> >
>
> Use OpenFTS (openfts.sourceforge.net) for relev
Yet another customer for relevence ranking ;-)
malz.
On Wednesday 15 January 2003 11:49 pm, Oleg Bartunov wrote:
> On Wed, 15 Jan 2003 [EMAIL PROTECTED] wrote:
> > HI, PPL!
> >
> > How am I able to sort query results by relevance?
> > I use contrib/tsearch to search using fill text index!
>
> Us
49 matches
Mail list logo
