get 7,8,10. I have written recursive functions which return all nodes
on a branch of a tree but I can't think of a way to return the end nodes
does anyone know of a solution?
Many thanks,
Mike
---(end of broadcast)---
TIP 4: Have you searche
Thankyou very much Yasir and Ross for your help and advice.
I have created a pl/pgsql version of Yasirs algorithm which
works perfectly, I am also looking into improving efficiency
by flaging leaf records. Here is my pl/pgsql solution in case
it helps anyone out:
CREATE OR REPLACE FUNCTION paren
table')), ...)
RETURNING (SELECT currval ('seq_table')) AS id
Much simplier:
INSERT INTO table (name, email, salary) VALUES ('joe', 'j...@example.com',
100) RETURNING id;
Cheers,
-Mike
--
Michał Roszka
m...@if-then-else.pl
--
Sent via pgsql-sql mailing
ake a nextval of the sequence and update the id
accordingly once the record *has been actually inserted* instead of poking
the sequence each time you *are going to insert* something.
I am pretty sure, that the table is locked to prevent inserts until the
after-insert-trigger is finished.
Cheers,
De: Mike Baroukh <[EMAIL PROTECTED]>
À: <[EMAIL PROTECTED]>
Objet: lo_export and files permissions
Date : lundi 14 août 2000 10:44
Hi everybody.
Who can help me with a lo_export() problem ? :
I'm using lo_export function in sql statement with pgsql 6.5 on Linux RH 6.2.
Data
"rm -f".
But I can't delete using Java's File object.
Mike
- Original Message -
From: Guo Bin <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, August 16, 2000 11:31 AM
Subject: Re: [SQL] lo_export and files permissions
There is a sample in postgres documentation. (See below).
the only problem is for using langage plpgsql.
If it is not understand by your database, you must use command
createlang plpgsql dbname
as the owner of the database.
CREATE TABLE emp (
empname text,
salary int4,
ipcclean
pg_ctl stop
Though finding out about ipcclean was not easy considering the output
given. There is no mention of it in any of the FAQs.
mrc
--
Mike Castle Life is like a clock: You can work constantly
[EMAIL PROTECTED] and be right all the time, or not work at all
setval();
Sometimes it's good to read files in the source tree (such as HISTORY).
mrc
--
Mike Castle Life is like a clock: You can work constantly
[EMAIL PROTECTED] and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice
On Sat, Dec 09, 2000 at 08:16:06AM +, feblec wrote:
> FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the
> created procedural languages.
Look at: postgresql-7.0.3/src/pl/plpgsql/enable_plpgsql
mrc
--
Mike Castle Life is like a cloc
On Thu, Dec 21, 2000 at 11:10:00AM +0100, Kaare Rasmussen wrote:
> - Sequences are not rollback'able.
Did you mean SERIAL instead of sequence here?
If so, why is no rollbackable an issue? All you should need is unique
numbers. Not necessarily exactly sequential numbers.
mrc
--
closer to Postgres, e.g. in plpgsql?
Can this be done using tcl or perl?
mrc
--
Mike Castle Life is like a clock: You can work constantly
[EMAIL PROTECTED] and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc
We are all
On Thu, Dec 21, 2000 at 05:50:43PM +, Oliver Elphick wrote:
> Mike Castle wrote:
> >On Thu, Dec 21, 2000 at 11:10:00AM +0100, Kaare Rasmussen wrote:
> >> - Sequences are not rollback'able.
> >
> >Did you mean SERIAL instead of sequence here?
> &
t; > select user_name from tbacct where extract(month from acct_timestamp) = 11 ...
>
> is there any way of using an index for selecting some rows, e.g.
> selecting all data from one month?
What about select blah from foo where month >= 2000-11-01 and month < 2000-12-01
Fix up as
-
2001-12-31 00:00:00-08
(1 row)
template1=> select '2000-12-31'::timestamp+'1 year'::timespan;
?column?
2001-12-31 00:00:00-08
(1 row)
mrc
--
Mike Castle Life is like a clock: You can work constantly
[EMAIL PROTEC
t '31/12/2000'::date;
select '31/12/2000'::date + '365 days'::timespan;
and 364, 363, etc.
Not sure if gets you anywhere. But data points.
There is an email archive on the postgresql.org website you could search if
you think it's been answered before.
mrc
--
Hi,
I have a number of empty tables and I want to get the column names and
data types with an SQL statement. I want to do this procedurally, not
interactively (so I can't use \d in psql). Postgres doesn't
support DESCRIBE... is there any other way to do this?
Thanks!
Mike
"\d " will give you
information similar to what you would normally get from a DESCRIBE.
Mike
--
"Mike D'Agosta" <[EMAIL PROTECTED]> wrote in message
94n93j$2j6j
How about:
SELECT '2001-03-06'::timestamp + '1 Year';
Hope that helps,
Mike Mascari
-Original Message-
From: Boulat Khakimov [SMTP:[EMAIL PROTECTED]]
Sent: Tuesday, March 06, 2001 2:20 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Subj
On Tue, Apr 17, 2001 at 06:12:40PM -0700, clayton wrote:
> if a spec file was made avail we could all make rpms!
You too can ./configure ; make ; make install !!
--
Mike Castle Life is like a clock: You can work constantly
[EMAIL PROTECTED] and be right all the time, or
ding it yet? :->
mrc
--
Mike Castle Life is like a clock: You can work constantly
[EMAIL PROTECTED] and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc
We are all of us living in the shadow of Manhattan
these may sound like terribily stupid questions. but we need some
quick guidance before proceeding with a schema that relies on these
advanced data features of postgres
tia
mike
___
This communication is intended for the use of the recipient to whom it
is addressed, and may contain
On Mon, 30 Sep 2002, Bruno Wolff III wrote:
> > 3) can you do selects on only a portion of a multidimensional array. That
> > is, if you were storing multilanguage titles in a two dimensional array,
> >
> > [en], "english title"
> > [fr], "french title"
> >
> > could you select where title[0]
ike Cache').
>
> Good luck.
>
> -Josh Berkus
>
>
>
>
Mike Sosteric <[EMAIL PROTECTED]> Managing Editor, EJS
<http://www.sociology.org/>
Department of Global and Social Analysis Executive Director, ICAAP
<http://www.icaap.org/>
Athabasca Un
On Mon, 30 Sep 2002, Josh Berkus wrote:
thanks for this. we will stick with the relational model.
m
>
> Mike,
>
> > I have a very good sense of the strengths of relational databases. But
> > they are also limited when it comes to object orientaed data (like XML
> >
any ideas?
m
>
> Mike,
>
> > thanks for this. we will stick with the relational model.
>
> Hey, don't make your decision entirely based on my advice.Do some
> research! I'm just responding "off the cuff" to your questions.
>
> If you do
t;
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
Mike Sosteric <[EMAIL PROTECTED]> Managing Editor, EJS
<http://www.sociology.org/>
Department of Global and Soc
e.
Later,
Mike
Hepworth..
-Original Message-From: lz John
[mailto:[EMAIL PROTECTED]]Sent: Thursday, October 17, 2002 1:07
AMTo: [EMAIL PROTECTED]Cc:
[EMAIL PROTECTED]Subject: [pgadmin-support]
hi
i don't send mail to [EMAIL PROTECTED],but i need
help
how to m
Hi all, I hope someone can help me out.
I'm doing single-table select statements on a large table and I could use
some help in speeding it up.
My query is of the form:
SELECT col, count(col) FROM tab WHERE id IN (3,
4,7,2, ...) GROUP BY COL ORDER BY count
for a very large number of rows.
I have
Hi all, I hope someone can help me out.
I'm doing single-table select statements on a large table and I could use
some help in speeding it up.
My query is of the form:
SELECT col, count(col) FROM tab WHERE id IN (3,
4,7,2, ...) GROUP BY COL ORDER BY count
for a very large number of rows.
I have
ole LOT of postgres processes running, idle. BTW, one of the postgres
processes was doing a vacuum analyze. I'm running 7.2.
Can anyone tell me how to fix this? The out put of the ps command can be
seen at http://dominion.dyndns.org/~mdiehl/ps.txt
Thanx in advance,
--
Mike Diehl
Network Tool
On Wednesday 04 December 2002 03:25 pm, Roberto Mello wrote:
> On Wed, Dec 04, 2002 at 03:08:35PM -0700, Mike Diehl wrote:
> > Can anyone tell me how to fix this? The out put of the ps command
> > can be seen at http://dominion.dyndns.org/~mdiehl/ps.txt
>
r schema b.
Any ideas?
Thanks,
Mike Hepworth.
---(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
Ok, I found the documentation on using views to udpate the underlying
database. But I can't seem to get my head around making it actually
work, because updates may change only a few columns, and the columns
in my views come from multiple tables.
Could someone provide an example (CC'ing me, please,
!
--
Mike Rylander
---(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
Thank you! This is great news. Is there a projected release date for 7.4?
Also, is there a published roadmap, or should I just get on the developers
list?
Thanks again.
---
Mike Rylander
On Friday 18 July 2003 05:34 pm, Joe Conway wrote:
> Mike Rylander wrote:
> > I have a rather
>
> Also does anyone know what field type an interval would map to in jdbc?
>
> thanks
>
>
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining colu
I'm sure many on this list are sick of hearing about this problem, but it
was on the fix list for 7.4, but doesn't appear to have been changed.
You can see one of the many threads on the problem at:
http://archives.postgresql.org/pgsql-sql/2003-05/msg00352.php
Basically, queries of the form SELEC
On Wed, 20 Aug 2003, Stephan Szabo wrote:
>
> On Wed, 20 Aug 2003, Mike Winter wrote:
>
> > I'm sure many on this list are sick of hearing about this problem, but it
> > was on the fix list for 7.4, but doesn't appear to have been changed.
>
> IN (subselect)
On Wed, 20 Aug 2003, Rod Taylor wrote:
> Ensure your IN list is unique. You might find better times by through
> an indexed temp table.
That is what I ended up doing, but it's not a very elegant solution.
MySQL does queries of this type orders of magnitudes faster than Postgres
on large value li
igo!
>
> ---(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 clean
I'm on PHP 4.2.2 and RedHat 9 with PGSQL. I want to turn quoted
identifiers off with my SQL queries. What SQL statement or .CONF
setting do I need to change so that I can turn quoted identifiers off?
Quoted identifiers, as I understand them, are where you must put
double quotes around any table or
Jeff Boes <[EMAIL PROTECTED]> wrote in message news:<[EMAIL PROTECTED]>...
> At some point in time, [EMAIL PROTECTED] (Mike Moran) wrote:
>
> >Hi. I currently have a fairly large query which I have been optimizing
> >for later use in a function. There are things in
On Wednesday 12 May 2004 07:05 am, Mats Sjöberg wrote:
> Hello everyone
> In a table i have a column status of type bigint.
> I need to create a view of the table including all rows with bit 4 set
> (value 8).
> At the same time i need to exclude excludig all rows with bit 2 set.
>
> What is the sy
On Tuesday 11 May 2004 09:44 am, Bruce Momjian wrote:
[snip]
> > > > Bruce Momjian kirjutas E, 10.05.2004 kell 06:58:
> > > > > Added to TODO:
> > > > >
> > > > > * Add MERGE command that does UPDATE, or on failure, INSERT
> > > >
[snip]
Hello all.
I have been lurking here for a bit and the
kasper wrote:
> Hi guys
>
> Im tryint to make a trigger that marks a tuble as changed whenever someone
> has updated it
>
> my table looks something like this
>
> create table myTable (
> ...
> changed boolean;
> )
>
> now ive been working on a trigger and a sp that looks like this, bu
B.W.H. van Beest wrote:
>
>
> I have a table where one of the columns is of type 'TIMESTAMP'
>
> How can I do a query to filter on the TIMESTAMP value, e.g. to obtain
> all rows earlier than a certain time stamp?
Think of the math opperators '<' and '>' as 'before' and 'after',
respectively.
>I've come across a situation where I'd like to use some kind of
"out-of-transaction
>trigger" to do some processing after changes to some tables, but
without extending
>the duration of the main transaction. Of course, it's important that
the processing be
>completed so it has to be, as far as poss
On Mon, 25 Oct 2004 10:13:37 +0200, cristivoinicaru
<[EMAIL PROTECTED]> wrote:
> I have a postgres table like this:
>
> CREATE TABLE "temp50" (
> "gc" character varying(36),
> "co" character varying(7),
> "data" date,
> "ora" smallint
>
> );
>
> It contains the following records:
>
> 500370
On Mon, 25 Oct 2004 05:44:06 -0600, Andrew J. Kopciuch
<[EMAIL PROTECTED]> wrote:
> On Monday 25 October 2004 05:20, Mike Rylander wrote:
> > SELECT * FROM temp50 GROUP BY gc ORDER BY ora DESC;
>
> You can not have have expressions (columns etc.) in the SELECT list that
rom photos where id in (
> select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft
> and p2.rgt and p2.id = gallery_id
> );
> return pcount;
> 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 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
>
--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
END LOOP;
RETURN
END;' LANGUAGE 'plpgsql';
Hope that helps!
> --
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [E
Forgot one line. See below
On Mon, 22 Nov 2004 11:54:30 -0500, Mike Rylander <[EMAIL PROTECTED]> wrote:
> I'm feeling sausey today, so here is my (untested) attempt to
[snip]
> > CREATE FUNCTION svp_getparentproviderids (@child_provider INTEGER)
> > RETURNS @provid
t; CREATE FUNCTION
> sp_demo_505=# select * from svp_getparentproviderids(21112);
> ERROR: incorrect argument to RETURN NEXT at or near "cid"
> CONTEXT: compile of PL/pgSQL function "svp_getparentproviderids" near
> line 13
>
>
--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
On Wed, 15 Dec 2004 12:54:44 -0600, Richard Rowell
<[EMAIL PROTECTED]> wrote:
> I have a table with a unary (recursive) relationship that represents a
> hierarchy. With the gracious help of Mike Rylander I was able to port a
> TSQL function that would traverse "up" the hi
Arg! One more change below
On Wed, 15 Dec 2004 21:48:57 -0500, Mike Rylander <[EMAIL PROTECTED]> wrote:
> On Wed, 15 Dec 2004 12:54:44 -0600, Richard Rowell
> <[EMAIL PROTECTED]> wrote:
> > I have a table with a unary (recursive) relationship that represents a
> >
nt4 NOT NULL references contact(id),
> id_wk int4 NOT NULL references contact(id)
> );
> but how do I check also the type column?
>
> Best regards,
> Andy.
--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org
--
Mike Rylande
istgreSQL manual. Is
> this true? Thanks.
The FAQ entry is incorrect. If you make your SERIAL column the
PRIMARY KEY of the table, however, a UNIQUE index will be created.
--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org
-
-createrule.html
Hope that helps!
--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
e_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"
Don't put commas between your joins.
--
Mike Rylan
>
> 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)--
't create a generic type to handle this, you can
create a DOMAIN to wrap up your constraint for each "enum" type field
that you want:
CREATE DOMAIN fruit AS TEXT CHECK (VALUE IN ('apple','orange','banana'));
CREATE TABLE eat (
food fruit
);
http://ww
there suggesting you go look at
> > the PL languages first, but obviously it's not getting the job done.
>
> Chapter 35 is plpgsql.. do you mean chapter 32.4?
>
> > Anybody have a better idea?
>
> What about a See Also section ala man pages that links to
tblStuff WHERE ides LIKE '%$1%' LOOP
RETURN NEXT myrec;
END LOOP;
RETURN;
END;
$$ LANGUAGE 'plpgsql';
Thanks so much for any insight you can give me!!!
Mike
st (registered) values ('1');
> else
> insert into test (registered) values ('0');
> end if;
Perhaps UPDATE is what you're looking for?
http://www.postgresql.org/docs/8.4/static/sql-update.html
UPDATE test SET registered = '1' WHERE uid = 'janvleuven1
> From: Relyea, Mike [mailto:mike.rel...@xerox.com]
> Sent: Thursday, August 13, 2009 10:47 PM
>
> > From: pgsql-sql-ow...@postgresql.org
> [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Jan Verheyden
> > Subject: [SQL] simple? query
> >
> > Hi,
> &g
> From: Jan Verheyden [mailto:jan.verhey...@uz.kuleuven.ac.be]
> Sent: Friday, August 14, 2009 9:03 AM
> To: Relyea, Mike
> Subject: RE: [SQL] simple? query
>
> The goal is, where uid not equals to 'janvleuven10' a new
> record should be inserted with the uid
Hi
I see that some queries are not Order(n) where n=number of partitions.
However, if one were to run the query separately against different
partitions and aggregate the results it could be Order(n). Can such an
approach be implemented in a more generic manner in pgsql?
Thanks
Mike
yield Is Not Null
GROUP BY printers.make, printers.model, consumables.color,
consumables.type
ORDER BY make, model;
After doing a google search I didn't come up with anything that I was
able to use so I'm asking you fine folks!
Mike
--
Sent via pgsql-sql mailing list (pgsql-sql@postgres
> -Original Message-
> From: David Johnston [mailto:pol...@yahoo.com]
> Sent: Friday, June 01, 2012 11:13 AM
> To: Relyea, Mike
> Cc:
> Subject: Re: [SQL] Lowest 2 items per
>
>
> I would recommend using the "RANK" window function with an appropri
> -Original Message-
> From: Oliveiros d'Azevedo Cristina
[mailto:oliveiros.crist...@marktest.pt]
> Sent: Friday, June 01, 2012 11:21 AM
> To: Oliveiros d'Azevedo Cristina; Relyea, Mike;
pgsql-sql@postgresql.org
> Subject: Re: [SQL] Lowest 2 items per
>
>
> -Original Message-
> From: Oliveiros d'Azevedo Cristina
[mailto:oliveiros.crist...@marktest.pt]
> Sent: Friday, June 01, 2012 12:28 PM
> To: Relyea, Mike
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Lowest 2 items per
>
> Yes, you are right, now, thin
> -Original Message-
> From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-
> ow...@postgresql.org] On Behalf Of Mario Dankoor
> Sent: Friday, June 01, 2012 2:31 PM
> To: Relyea, Mike
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Lowest 2 items per
> Mike,
> -Original Message-
> From: Oliveiros d'Azevedo Cristina
[mailto:oliveiros.crist...@marktest.pt]
> Sent: Friday, June 01, 2012 12:59 PM
> To: Relyea, Mike
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Lowest 2 items per
>
> * I see...
>
>
used a pivot table in Microsoft Excel. Not
sure what your environment or requirements are but pivot tables are widely used
in business, easy to share, can be formatted, and give the user the ability to
drill down and navigate to the data they want to see.
I'd set up a query to pull the
s in it but the unique id is unique.
I need to get a list of distinct phone numbers and the coorisponding largest
call duration.
I've got the idea that this should be a self-join on phone number where
a.id<>b.id, but I just can't seem to get the max duration.
A
Yup, that did it. I don't know why I made it harder than it had to be.
Thank you.
Mike.
On Wednesday 15 August 2007 02:58:22 pm Fernando Hevia wrote:
> Try this:
>
> Select *
> from view v1
> where duration = (select max(duration) from view v2 where v2.phone_number =
> v
Hello! I'm a long time lurker who has become responsible for maintaining
/ updating utility queries at work. I've reworked two queries (as text
attachment as they are wide lines) to enhance the planner's chance of
speeding up the queries (Oracle8i's).
I'm looking for someone to eyeball them a
Richard Huxton wrote:
(quoted OP lines edited for brevity...)
Mike Adams wrote:
...
I've reworked two
queries (as text attachment as they are wide lines) to enhance the
planner's chance of speeding up the queries (Oracle8i's).
Well, I can't say it's standard pr
Richard Huxton wrote:
Mike Adams wrote:
So.
The first query should pull all 'MOM' records that have one or more
corresponding, and possibly orphaned, unassigned receiving records
belonging to the same po_cd and item_cd.
The second query should pull all unassigned, and possibl
In the application that we are working on, all data searches must be case
insensitive.
Select * from test where column1 = 'a' and Select * from test where column1 =
'A' should always be the same and use the index if column1 is indexed. In
order to do this am I going to be required to us
Richard Huxton wrote:
Michael Toews wrote:
You could accumulate the values in an array and then sort that with
the final-func that create aggregate supports.
Thanks for the help. Here was my final functions to aggregate things
into a comma serpented text element (if anyone is interested):
im to the first line only
(the normal trim function doesn't appear to do this)
Thanks in advance.
-Mike
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
> Is there a function or special system label I can use that would
generate a sequence number in the returning result set?
Would something like this work for you?
CREATE TEMP SEQUENCE foo;
SELECT a, b, c, nextval('foo') AS order FROM t1 ORDER BY a;
Mike Relyea
Product Develop
nd when processing a SQL statement, much like sendmail. You
should be able to determine who's connected and what they're
doing with something like:
ps axf
You'll see who's connected to what database from what machine and
the type of query being executed. I don't know
85 matches
Mail list logo
