"Girish Bajaj" <[EMAIL PROTECTED]> writes:
> I cant possibly index all the cols in the table. So I thought Id best manage
> the data by splitting up the table into multiple partitions and eventually
> depending on application logic, only scan those tables that are necessary to
> scan sequentially
Joe Conway <[EMAIL PROTECTED]> writes:
> BenLaKnet wrote:
> > I see connect by in Oracle
> > ??? is there an equivalent in PostgreSQL or not ??
>
> Someone is working on the SQL99 equivalent, but it isn't done yet. Perhaps for
> 7.5.
There's a connectby hack in the contrib/tablefunc directory. I
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Thursday 17 July 2003 03:46, Terence Kearns wrote:
> CREATE FUNCTION base.fn_fkey_check(text, text, int4) RETURNS bool AS
> 'DECLARE
> BEGIN
>RETURN (SELECT count($1) FROM $2 where $1 = $3)::bool;
> END;'
> LANGUAGE 'sql';
>
> produces this erro
Actually, I have used a UNION to replace OR's, the case (simpliefied to)
something like this:
Sample 1:
WHERE (f1 = 'v1' OR f1 = '')
AND (f2 = 'v2' OR f2 = '')
Changed to Sample 2:
WHERE (f1 = 'v1')
AND (f2 = 'v2')
UNION
WHERE (f1 = 'v1')
AND (f2 = '')
UNION
WHERE (f1 = '')
AND (f2 = '')
But what makes you think, that it is quicker to scan 10 tables with 25
million rows each than it would be to scan one table with 250 million rows?
It won't... In fact, it will be *longer*.
If you have a way to narrow the number of tables to scan down based on
the condition, you can have that log
OK, so basically you are trying to keep a hundered some odd attributes on
everyone in the US. It's possible that a 150 column table is properly
normalized (I have a similar situation) but it is rare.
Suppose it is really properly normalized. You can still benefit from indexes
on just some of th
Girish,
> I cant possibly index all the cols in the table.
Why not?
Seriously, what you can do is use query logging to log the columns and
combinations of columns that users most frequently ask for. Then you can
index for those queries.
Your partitioning scheme assumes that users will be s
Josh Berkus <[EMAIL PROTECTED]> writes:
> Certainly a query of the above form would not benefit from being a union.
Actually we used to have code in the planner that would automatically
transform an OR query to a UNION ALL construct (the old "ksqo" option).
It fell into disfavor, partly because it
The problem is that Im worried about sequential scans. This particular table
can have upto 150 cols and 250 million records. Now we have a reporting
requirement that someone could select on ANY col and filter on any col as
well. Meaning someone could so a SELECT on col number 1,2,310,1000 from
cont
On Thu, 2003-07-17 at 19:03, Josh Berkus wrote:
> Girish,
>
> > > Essentially Im trying to store a persons information in a table in the
> > > database. Since we could have millions of people, with duplicates! Ive
> > > decided we need to partition the table into segments where all people with
> >
On Thu, 2003-07-17 at 15:00, Josh Berkus wrote:
> Scott,
>
> > I have a query that uses a series of ORs and I have heard that sometimes
> > this type of query can be rewritten to use UNION instead and be more
> > efficient.
>
> I'd be interested to know where you heard that; as far as I know, i
Girish,
> > Essentially Im trying to store a persons information in a table in the
> > database. Since we could have millions of people, with duplicates! Ive
> > decided we need to partition the table into segments where all people with
> > the LastName starting from A to G will be in one table. H
Scott,
> I have a query that uses a series of ORs and I have heard that sometimes
> this type of query can be rewritten to use UNION instead and be more
> efficient.
I'd be interested to know where you heard that; as far as I know, it could
only apply to conditional left outer joins.
> s
On Thursday 17 Jul 2003 6:20 pm, Girish Bajaj wrote:
> Hello All,
>
> I have a set of tables partitioned horizontally. DML below.
>
> Essentially Im trying to store a persons information in a table in the
> database. Since we could have millions of people, with duplicates! Ive
> decided we need to
Hello,
I have a query that uses a series of ORs and I have heard that sometimes
this type of query can be rewritten to use UNION instead and be more
efficient. Are there any rules of thumb for when this might be the
case? As an example here is a query of the type I am discussing:
select di
BenLaKnet wrote:
I see connect by in Oracle
??? is there an equivalent in PostgreSQL or not ??
Someone is working on the SQL99 equivalent, but it isn't done yet.
Perhaps for 7.5.
Joe
---(end of broadcast)---
TIP 8: explain analyze is your friend
Hello All,
I have a set of tables partitioned horizontally. DML below.
Essentially Im trying to store a persons information in a table in the
database. Since we could have millions of people, with duplicates! Ive decided
we need to partition the table into segments where all people
n Thu, Jul 17, 2003 at 12:56:52PM +0100, Gary Stainburn wrote:
>
> nymr=# \d lnumbers
>Table "lnumbers"
> Column | Type | Modifiers
> ---+---+---
> lnid | integer | not null
> lnumber | character varying
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
I see... The bad news is you can't do it directly... You can
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
> >---+---+---
>
On Thu, 17 Jul 2003, Terence Kearns wrote:
> CREATE FUNCTION base.fn_fkey_check(text, text, int4) RETURNS bool AS
> 'DECLARE
> BEGIN
>RETURN (SELECT count($1) FROM $2 where $1 = $3)::bool;
> END;'
> LANGUAGE 'sql';
>
> produces this error
> ERROR: parser: parse error at or near "RETURN" at c
Hello,
Just to say thanks for such fast responses. The full working query is
indeed as suggested (I cut the demo query down when I posted it, as it's got
some awfully long names in it in real life):
DELETE FROM eulepersongroup2workspaceitem
WHERE NOT EXISTS (
SELECT 1 FROM workspaceitem WHERE w
And after reading Viorel's response I realized that you wanted the record
deleted if EITHER "foreign key" was broken, not just if BOTH fkeys are
broken, therefore simply change the AND to an OR:
DELETE FROM myTable
WHERE NOT (SELECT 1 FROM item WHERE myTable.item_id = item.item_id)
OR NOT (SELEC
I can't help you explain what is going on with this query - like you, I
am puzzled by the fact that it actually works, and have no idea how it
is interpreted, and what it is doing...
The right way to do what you want, I think, would be something like:
delete from mytable where not exists (select
DELETE FROM myTable
WHERE NOT (SELECT 1 FROM item WHERE myTable.item_id = item.item_id)
AND NOT (SELECT 1 FROM ep WHERE myTable.group_id = ep.group_id);
Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
[EMAIL PROTECTED]
Fax: (416) 441-9085
> -O
- Original Message -
From: "Richard Jones" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 17, 2003 5:29 PM
Subject: [SQL] NOT and AND problem
> Dear All,
>
> I am having some confusion over a query which is supposed to achieve the
> following: To remove a record from a
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) |
Dear All,
I am having some confusion over a query which is supposed to achieve the
following: To remove a record from a table if the one or both of the
columns containing references to other tables no longer point to table rows
which still exist. There are good reasons why I cannot use foreign k
Terence Kearns wrote:
CREATE FUNCTION base.fn_fkey_check(text, text, int4) RETURNS bool AS
'DECLARE
BEGIN
RETURN (SELECT count($1) FROM $2 where $1 = $3)::bool;
END;'
LANGUAGE 'sql';
produces this error
ERROR: parser: parse error at or near "RETURN" at character 20
I'm trying to create a functi
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> I'm trying to create a function to use on a trigger to check reference
> to views since pg does not support foreign keys referencing views.
Can you explain exactly what you are trying to do and why? You are getting
the error because a SQL functi
$db_conn1 = pg_connect("dbname=db1");
$db_conn2 = pg_connect("dbname=db2");
.
You can't join two tables from different databases(, as far as i know :).
- Original Message -
From: "BenLaKnet" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 17, 2003 5:03 PM
Subject: [SQ
Take a look at dblink in the contrib directory... This may do what you need
adam
> Hi,
>
> I try to find how is it possible to connect 2 databases, with a symbolic
> link.
>
> I have to use tables in another database to test user or other information.
>
>
> Ben
>
>
>
>
> ---
Hi,
I try to find how is it possible to connect 2 databases, with a symbolic
link.
I have to use tables in another database to test user or other information.
Ben
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
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
CREATE FUNCTION base.fn_fkey_check(text, text, int4) RETURNS bool AS
'DECLARE
BEGIN
RETURN (SELECT count($1) FROM $2 where $1 = $3)::bool;
END;'
LANGUAGE 'sql';
produces this error
ERROR: parser: parse error at or near "RETURN" at character 20
I'm trying to create a function to use on a trigger
Rajesh Kumar Mallah wrote:
On Thursday 17 Jul 2003 8:21 am, Terence Kearns wrote:
select id from tablename where message like '%sql%';
If there any way to determine exactly how many times 'sql' is matched in
that search in each particular row, and then sort by the most matches,
or am I going to
On Thursday 17 Jul 2003 8:21 am, Terence Kearns wrote:
> >>select id from tablename where message like '%sql%';
> >>
> >>If there any way to determine exactly how many times 'sql' is matched in
> >>that search in each particular row, and then sort by the most matches,
> >>or am I going to have t
37 matches
Mail list logo
