d in X,
y.c2 is NULL so to get 2 rows you would need to write:
SELECT x.c1,y.c3,z.c2
FROM x JOIN z USING (c1)
LEFT OUTER JOIN y ON (x.c2=y.c1)
WHERE y.c2=9 OR y.c2 IS NULL;
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
ursor and then create
a function that pops items off the top of this queuing table as needed by
the different sessions?
--
======
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
n this
What does your query look like? Are you using locking or transactions where
other queries are blocking your query from running?
-Aaron
--
======
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
cy since only the top forum_post record
needs a reference to forum_topic and forum_topic wouldn't even really be
needed.
-Aaron
--
======
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
On 5/7/07, Tom Lane <[EMAIL PROTECTED]> wrote:
Andrew Sullivan <[EMAIL PROTECTED]> writes:
> On Sun, May 06, 2007 at 01:45:54PM -0500, Aaron Bono wrote:
>> So my conclusion is that the function is being treated as volatile even
>> though it is stable because the numbe
On 5/5/07, Jaime Casanova <[EMAIL PROTECTED]> wrote:
On 5/5/07, Aaron Bono <[EMAIL PROTECTED]> wrote:
> On 5/5/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> > "Aaron Bono" <[EMAIL PROTECTED]> writes:
> > > 9. -> Seq
On 5/5/07, Tom Lane <[EMAIL PROTECTED]> wrote:
"Aaron Bono" <[EMAIL PROTECTED]> writes:
> 9. -> Seq Scan on branch (cost=0.00..4.72rows=1
> width=1281) (actual time=130129.988..157492.057 rows=1 loops=1)
> 10.
On 5/5/07, Andrew Sullivan <[EMAIL PROTECTED]> wrote:
On Sat, May 05, 2007 at 05:00:53PM -0500, Aaron Bono wrote:
> We don't allow deletes and updates are fairly infrequent. I also did a
> vacuum analyze to no effect.
How do you "not allow" deletes?
Permissio
On 5/5/07, Tom Lane <[EMAIL PROTECTED]> wrote:
"Aaron Bono" <[EMAIL PROTECTED]> writes:
> 9. -> Seq Scan on branch (cost=0.00..4.72rows=1
> width=1281) (actual time=130129.988..157492.057 rows=1 loops=1)
> 10.
On 5/5/07, Andrew Sullivan <[EMAIL PROTECTED]> wrote:
On Sat, May 05, 2007 at 04:05:52PM -0500, Aaron Bono wrote:
> I have two schemas, both with the exact same DDL. One performs great
and
Do they have the same data?
They have different data. The fast one has about 150 rows and
mas so I cannot figure out why the optimizer is
looking at these differently. In fact, the table the branch_id comes from
has the exact same indices and foreign keys on both schemas.
Any direction would be deeply appreciated.
Thanks!
Aaron
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
Thanks!
--
======
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
hen (id = 5) then 3
when (id = 1) then 4
when (id = 4) then 5
else null
end
;
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
On 4/25/07, Tom Lane <[EMAIL PROTECTED]> wrote:
"Aaron Bono" <[EMAIL PROTECTED]> writes:
> The biggest problem I notice is when I add a join from a child table
> (zip_city) to a parent table (zip). I have filtered the child table
down to
> about 650 records
On 4/25/07, Richard Huxton <[EMAIL PROTECTED]> wrote:
Aaron Bono wrote:
> Performance tanks with this query - it takes over 120 seconds (that is
> where
> I set the timeout).
> BTW, on our Linux box the full query we run (which adds 3 more tables on
> the
> whole o
is appreciated. I did look at the explain plans but I don't
see anything other than what I mentioned above. I can include those if
necessary.
Thanks!
Aaron
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
On 4/21/07, Tom Lane <[EMAIL PROTECTED]> wrote:
"Aaron Bono" <[EMAIL PROTECTED]> writes:
> Is there a good way to split a string into multiple records?
> I have a table "branch" with a column "branch_num" which has a comma
> delimited list of
on ','
from branch
;
Is there a good way (or alternative way) to do this?
Thanks!
--
======
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
icular
application we build.
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
light.flight_id, cmp_flight.cmp_flight_id .
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
why I recommend starting with a logic
structure before moving on to the physical.
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
-- Forwarded message --
From: Aaron Bono <[EMAIL PROTECTED]>
Date: Jan 8, 2007 4:42 PM
Subject: Re: [SQL] Table relationships
To: Curtis Scheer <[EMAIL PROTECTED]>
On 1/8/07, Curtis Scheer <[EMAIL PROTECTED]> wrote:
I'm having trouble determining the be
y be better to ask a JDBC group.
--
======
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
ueries will help us see what you are
really trying to do.
--
======
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
On 12/15/06, Ragnar <[EMAIL PROTECTED]> wrote:
On fös, 2006-12-15 at 16:17 -0600, Aaron Bono wrote:
> On 12/15/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Aaron Bono" <[EMAIL PROTECTED]> writes:
> > My question, what do the SQL Specifi
On 12/15/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Aaron Bono" <[EMAIL PROTECTED]> writes:
> My question, what do the SQL Specifications say should happen on a
Union?
> Is it supposed to eliminate duplicates even WITHIN the individual
queries
> that are being u
ries, not WITHIN the queries.
My question, what do the SQL Specifications say should happen on a Union?
Is it supposed to eliminate duplicates even WITHIN the individual queries
that are being unioned?
Thanks!
--
======
Aaron Bo
t; select a from myt1 natural join myt3;
>
> Hope this helps.
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
If you want to know more about this, check into how Correlated Subqueries
work. I would never recommend using Correlated Subqueries but knowledge of
them and
#x27;s ID",
"Person's Name"
) AS
SELECT
person_id,
first_name || ' ' || last_name
FROM person
;
When I do a SELECT * FROM my_view, I get "Person's ID", not "PERSON'S ID" or
"person's id".
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
files *from registry* also.. but I am getting same error…
*Any one can help to reinstall my postgresql in my machine…*
Have you tried deleting the postgres user on your system? See if this
helps.
--
======
Aaron Bono
Aranya So
On 12/2/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Aaron Bono" <[EMAIL PROTECTED]> writes:
> CONTEXT: PL/pgSQL function "update_web_site_hits_fn" line 200 at
execute
> statement
> My question is this: What is line 200? Is it the 200th line after the
&
es, is line 200 the first line of the
EXECUTE statement or the ending line of the EXECUTE (where the ; is)?
I am using PostgreSQL 8.1.3 on CENTOS Linux.
Thanks,
Aaron
--
======
Aaron Bono
Aranya Software Technologies, Inc.
http:/
On 12/1/06, Chris Dunworth <[EMAIL PROTECTED]> wrote:
Aaron Bono wrote:
On 12/1/06, Stephan Szabo <[EMAIL PROTECTED]> wrote:
>
> On Fri, 1 Dec 2006, Chris Dunworth wrote:
>
> > Hi all --
> >
> > (huge apologies if this is a duplicate post -- I sent
rror:
>
> INSERT INTO endTable SELECT convertStartToEnd(st.*) FROM startTable st;
I think you'd need something like
INSERT INTO endTable SELECT (convertStartToEnd(st.*)).* FROM startTable
st;
to make it break up the type into its components.
INSERT INTO endTable SELECT * FROM (SELECT convertStartToEnd(st.*) FROM
startTable)
that should work too
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
a constraint as mentioned in one of the other
responses to your question.
-Aaron
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
e
http://oreillynet.com/pub/a/databases/2006/09/07/plpgsql-batch-updates.html?page=2
--
======
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
the "random" page picks up where it left off.
Just an idea...
--
======
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
v
Of course if you have commas in the data you will have to play with the sed
command a little but this will get you started.
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
m order by random() limit 1;
Is this a PostgreSQL thing or is it SQL standard?
--
======
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
dn't run it.
-Aaron
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
om xyz
inner join (
select sum(number) / count(*) as mean from xyz
) as xyz_mean
;Note that I did not check the syntax for typos.Anything more than this will require you whip out a Statistics book. ====== Aaron Bono
Aranya Software
cision?====== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com http://codeelixir.com==
need it.
Thanks!====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
http://codeelixir.com==
-- Forwarded message --From: Aaron Bono <[EMAIL PROTECTED]>Date: Nov 6, 2006 4:51 PM
Subject: Re: [SQL] refining view using temp tablesTo: BeemerBiker <[EMAIL PROTECTED]>Cc: pgsql-sql@postgresql.org
On 10/31/06, BeemerBiker <[EMAIL PROTECTED]> wrote:
Using postg
n help reduce database calls and increase performance.
-- ====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
http://codeelixir.com==
interactive/functions-info.html
http://archives.postgresql.org/pgsql-admin/2006-08/msg00147.phpSee if that points you in the right direction.
On Thursday 02 November 2006 16:58, Aaron Bono wrote:> On 11/2/06, Peter Hanson <[EMAIL PROTECTED]> wrote:> > Hello,> >> > First,
eign keys. That is one option for you.
-- ====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
http://codeelixir.com==
attempt to do this.I have to use DB2 sometimes and it also does not allow empty ().
What ORM are you using that is trying to do this?-- == Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com http://codeelixir.com==
On 10/31/06, A. Kretschmer <[EMAIL PROTECTED]> wrote:
am Tue, dem 31.10.2006, um 21:08:24 +0100 mailte A. Kretschmer folgendes:> am Tue, dem 31.10.2006, um 13:32:59 -0600 mailte Aaron Bono folgendes:> > I would go further by adding a type table like this:
> &
ot; and another tableoperation (user int references public.users (PK),task int references public.task (PK),ts timestamptz default now() (PK),
operation_type_id bigint references operation_type)This gives you the flexibility to add more operation types in the future.===
hy not?
-- ====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
http://codeelixir.com==
from the table.
I tried different solutions but no result.
Help && regards,
Andy.
What do your tables look like? This is caused by a data type mismatch so I wonder if the columns nummer and id are different types.-- ==
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
;minute', time)having timeseries.ticker is null... I will leave the update as an exercise ;)== Aaron Bono
Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
nique constraint to enforce your rule on which fields must be null/not null to help preserve your data integrity.
Hope this helps!-- ====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
http://codeelixir.com==
evalue';
Hope this helps.
====== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com http://codeelixir.com==
never be put into the database. Applications that rely on the database formatting - that is tightly coupling your application to the database which does not follow good programming principles.
None-the-less, the feature would be nice and may be very valuable for reporting.==
On 10/5/06, Andrew Sullivan <[EMAIL PROTECTED]> wrote:
On Wed, Oct 04, 2006 at 11:20:07AM -0500, Aaron Bono wrote:>> So do it as needed and convert your application slowly.You don't even need to do that.ALTER TABLE tablename RENAME TO tablename_real;
CREATE VIEW tablename [&
o clean up your SQL you could create a function that takes any timestamp and then make the function IMMUTABLE to tell postgresql it doesn't need to rederive the results every time.
== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
http://codeelixir.com==
On 10/4/06, Daryl Richter <[EMAIL PROTECTED]> wrote:
On 10/4/06 12:20 PM, "Aaron Bono" <[EMAIL PROTECTED]> wrote:> On 10/4/06, Erik Jones <[EMAIL PROTECTED]> wrote:
>>>> Aaron Bono wrote:>>> On 10/4/06, *Erik Jones* <[EMAIL PROTECTED] [EMAIL
On 10/4/06, Erik Jones <[EMAIL PROTECTED]> wrote:
Aaron Bono wrote:> On 10/4/06, *Erik Jones* <[EMAIL PROTECTED] [EMAIL PROTECTED]>>> wrote:>> There is one non-SQL related reason that I like to be able to order
> columns, at least the way they are d
As I know the drop cascade delete in both tables the rows related. Is it correct ?You can drop the foreign keys and then the table. Then when you recreate and repopulate the table, you can add the foreign keys back at the very end.
===
the order the columns will appear.====== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com http://codeelixir.com==
see timestamps based in their own locale.
Can anyone tell me how they're handling this? (sorry - can't get rid of my clients)
====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
http://codeelixir.com==
n.person_id ORDER BY hobby_name ) AS hobby_list FROM person) mysubINNER JOIN person_hobby ON (person_hobby.person_id = mysub.person_id)INNER JOIN hobby ON (hobby.hobby_id = person_hobby.hobby_id)
GROUP BY mysub.hobby_list, hobby.hobby_nameI did not try it so it may require a
ave it figured out by now. Data examples help us help you much better.==
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
ly, monthly or something else?
====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
http://codeelixir.com==
ing in.====== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com http://codeelixir.com==
ce solutions:See if this gives you some insight:select '2006-01-15'::date + s.incfrom generate_series(0, ('2006-02-20'::date - '2006-01-15'::date)::integer) as s(inc)
See http://www.postgresql.org/docs/8.1/static/functions-srf.html and
http://www.postgresql.org/docs
ou are probably trying to put your permission checking in the database. You might want to consider using a session based temp table and run a different function that first checks the temp table, if the table is empty it runs the function above and populates the temp table, if the table is not em
ing in.====== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com http://codeelixir.com==
entation.Guess we will need feedback from someone more knowledgable than I... ======
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Oops, see correction below...On 9/10/06, Aaron Bono <[EMAIL PROTECTED]> wrote:
On 9/10/06, James Cloos <[EMAIL PROTECTED]> wrote:
I've a query which I'd have liked to word akin to: SELECT guid FROM child WHERE the_fkey = ( SELECT id FROM parent WHERE name ~ '
child.the_fkey, child.my_pkey;I am not sure what table name is from but since you say it is n+1 queries I assume it is from the child table? It would help to know what columns are on which tables.
====== Aaron Bono Arany
le to work that way.
What kind of operation are you wanting to do? Would it work if an application like a web site used connection pooling - thus sharing the session across application users and rarely if ever connecting/disconnecting?
=====
In the "using(col2)", what columns and tables are you joining there? I always dislike that syntax as it is ambiguous in some cases and not very easy to read.
====== Aaron Bono Aranya Software Technologies, Inc. http://www.ar
he two.
====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
http://codeelixir.com==
.
Any other thoughts?====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
http://codeelixir.com==
ere? If I remove it I get true.====== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com http://codeelixir.com==
end of the page http://www.postgresql.org/docs/8.1/interactive/triggers.html - how it uses the OLD and NEW implicit variable. You can use
NEW.column_name to access the data being inserted into the table.Does this help?====== Aaro
sed in some rule. My fear is not get correct row and do not update the correct client.Any help would be very welcome.RegardsEzequias2006/8/31, Aaron Bono <
[EMAIL PROTECTED]>:
On 8/30/06, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote:
Hi list,I have a tabl
sed on the percent on the Rule table.
Anyone that would like to help me I will be very welcomed. I am not exactly sure what you are trying to do here. Can you provide an example of the data and what you want to occur?
====== Aaron Bono
I guess there could be performance concerns but then you could create a materialized view for it.
====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
http://codeelixir.com==
but cannot find it on the postgresql web site.
On 8/28/06, Aaron Bono <
[EMAIL PROTECTED]> wrote:
On 8/24/06, Travis Whitton <[EMAIL PROTECTED]
> wrote:
Hello all, I'm running the following query on about 6,000 records worth
of data, and it takes about 8 seconds to complete. Can any
..I would just do a normal inner-join, but then I get a row for each item that would otherwise come back nicely packaged in the array. The overhead of rearranging the data takes even more time than the subquery approach.
On 8/28/06, Aaron Bono <
[EMAIL PROTECTED]> wrote:
On 8/24/06, Tra
ot continue to be an issue.
====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
http://codeelixir.com==
w.postgresql.org/docs/7.4/interactive/sql-explain.html - that will reveal more of where the performance problem is.
== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
http://codeelixir.com==
re looking for a way to just backup the structure in CVS every day, you may consider writing a script (Perl would be an excellent choice for this) that reads the pg_dump and splits it out into separate files for each schema/table.
======
loops=1)You see that the cost jumps significantly.====== Aaron Bono
Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
ission.====== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com http://codeelixir.com==
ATILE CALLED ON NULL INPUT SECURITY INVOKER; CREATE TRIGGER "mytable_modify_dt_tr" BEFORE UPDATE ON "public"."mytable" FOR EACH ROW
EXECUTE PROCEDURE "public"."modify_date_stamp_fn"();== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com http://codeelixir.com==
On 8/2/06, Rodrigo De León <[EMAIL PROTECTED]> wrote:
On 8/2/06, Aaron Bono <[EMAIL PROTECTED]> wrote:> On 8/2/06, Penchalaiah P. <[EMAIL PROTECTED]> wrote:
> > Hi,> >> > I have PostgresSQL database connection from server. Server ip is> 172.16.5.179>
lues ($1,$2);end;
'language'plpgsql';
If any one knows how to store all these 12 values in a rnt table… let me know …..Why not just do a loop and run 12 separate inserts (or 12 separate calls to the function)?
====== Aa
I am waiting for ur
positive reply...Please provide the code for your functions so we can see what you are doing. ==
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
alues('chanukya',lo_import('D:\Vivek\Personal\PICS\IN\chanukya.jpg'));Windows uses \, not /. I am not sure if PostgreSQL will translate for you like Java does.======
Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
ix-ups when hand writing your queries.
I am glad the queries worked for you.-Aaron====== Aaron Bono Aranya Software Technologies, Inc.
http://www.aranya.com==
en you or another developer look at this in the future, you understand right away how the tables are being put together.
== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
s well maintained.
Anyone care to disagree?====== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com
==
ws you to "delete" rows at sometime in the future or make them appear in the future too.
On Jul 31, 2006, at 12:52 AM, Aaron Bono wrote:> CREATE OR REPLACE FUNCTION my_table_history_fn () RETURNS SETOF
> opaque AS> '> BEGIN> -- if a trigger insert or update operati
ot;Scenarios";"Authorise";"Action";"B"
11900;"Scenarios";"Create";"Action";"C"11900;"Scenarios";"Update";"Action";"C"I am guessing it should be an inner join? but by reference book does not
7.4.5 also and it worked. I'll be able to continue with my development using the syntax of that example.
Aaron Bono <
[EMAIL PROTECTED]> wrote: On 7/30/06, Thusitha Kodikara <
[EMAIL PROTECTED]> wrote: Hello,I am interested in developing some triggers to keep track of records tha
1 - 100 of 194 matches
Mail list logo
