Hello, I'm trying to write a query to return an XML document like
...
I started with
select xmlelement(name range, xmlattributes(m.range, count(s.id) as "count"))
from mb_sale s
inner join mb_lead m on m.sale_id = s.id
where
s.sale_date >= date('2007-08-01') and s.sale_date <=
On 12/10/07, Colin Wetherbee <[EMAIL PROTECTED]> wrote:
> Vivek Khera wrote:
> > On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:
> >> IF (a query matching your old data returns rows) THEN UPDATE with
> >> your new data ELSE INSERT your new data
> > Still exists race condition. Your race come
On 12/10/07, Keith Turner <[EMAIL PROTECTED]> wrote:
> We are running 8.1 on Windows 2003 server and have had a server crash
> over the weekend. A virus is suspected - we maintain an app server on
> someone else's network, though we do have anti-virus running, the
> symptoms were worrying - so we
am Mon, dem 10.12.2007, um 12:30:14 -0800 mailte Keith Turner folgendes:
> Hi first post here, I hope you can help.
>
> We are running 8.1 on Windows 2003 server and have had a server crash
> over the weekend. A virus is suspected - we maintain an app server on
Please don't hijack other threads,
On Mon, 10 Dec 2007, Brett Neumeier wrote:
It seems that the recovery command always copies the source WAL file (with a
name like 00010002009C) to a file path "pg_xlog/RECOVERYXLOG",
which is fine. However, then when we abort recovery, postgresql seems to
expect that the most recent
"Trevor Talbot" <[EMAIL PROTECTED]> writes:
> Erik is questioning is why it has to assume anything. Why can't it
> just execute the expression and find out?
Because the whole point of the problem is to *not* execute the
expression, but to assume that it must yield false, for every row
of a given p
Vivek Khera wrote:
On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:
For what it's worth, the real algorithm would be as follows. I
hadn't had enough coffee yet, and I forgot the UPDATE bit.
IF (a query matching your old data returns rows) THEN UPDATE with
your new data ELSE INSERT your n
On 12/10/07, Trevor Talbot <[EMAIL PROTECTED]> wrote:
> On 12/10/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> > Erik Jones <[EMAIL PROTECTED]> writes:
> > > I guess what I don't understand is that given the query
> >
> > > SELECT COUNT(*)
> > > FROM table
> > > WHERE some_id=34;
> >
> > > on a table w
On 12/10/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> Erik Jones <[EMAIL PROTECTED]> writes:
> > I guess what I don't understand is that given the query
>
> > SELECT COUNT(*)
> > FROM table
> > WHERE some_id=34;
>
> > on a table with the much discussed constraint (34 % 100) = 32 isn't
> > simply evalu
Hi,
I set up a warm standby failover system on Redhat, using built-from-source
postgresql 8.2.5 on (of course) both the master and standby systems.
The setup of the system was very easy, and the recovery script we have in
place on the standby system correctly copies in the archived WAL log files,
Erik Jones <[EMAIL PROTECTED]> writes:
> I guess what I don't understand is that given the query
> SELECT COUNT(*)
> FROM table
> WHERE some_id=34;
> on a table with the much discussed constraint (34 % 100) = 32 isn't
> simply evaluated as a one-time filter whenever whatever constraint
> excl
Richard Broersma Jr wrote:
> --- On Mon, 12/10/07, Ted Byers <[EMAIL PROTECTED]> wrote:
>
> > but how do you
> > do it using SQL in an RDBMS?
>
> I believe that there is an ANSI SQL command "MERGE" that is yet to be
> implemented into PostgreSQL.
IIRC the standard's definition of MERGE is sti
Scott Marlowe wrote:
On Dec 10, 2007 2:19 PM, Chris Browne <[EMAIL PROTECTED]> wrote:
[EMAIL PROTECTED] ("Josh Harrison") writes:
Does slony support postgres major version upgrade ? ie., will it
replicate between different major versions?
Yes, that's one of the major "use cases" for Slony-I.
On Dec 10, 2007, at 5:50 PM, Tom Lane wrote:
Erik Jones <[EMAIL PROTECTED]> writes:
Forgive me if I'm nagging on this, I just want to understand this
better. Why does evaluating a CHECK constraint like 'CHECK some_id %
100 = 32' against WHERE clause like 'WHERE some_id=1132' need to know
anyt
Erik Jones <[EMAIL PROTECTED]> writes:
> Forgive me if I'm nagging on this, I just want to understand this
> better. Why does evaluating a CHECK constraint like 'CHECK some_id %
> 100 = 32' against WHERE clause like 'WHERE some_id=1132' need to know
> anything about equality properites of %?
on 12/10/07 2:27 PM, [EMAIL PROTECTED] purportedly said:
>>> To work around this I add an dbCon.rollBack() after select statement
>>> above in good and in bad times. After that ALTER works.
>>> Can someone explain me why I need this rollback ?
>>
>> You only need the rollback when the SELECT stat
--- On Mon, 12/10/07, Ted Byers <[EMAIL PROTECTED]> wrote:
> but how do you
> do it using SQL in an RDBMS?
I believe that there is an ANSI SQL command "MERGE" that is yet to be
implemented into PostgreSQL.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
On Dec 10, 2007, at 4:29 PM, Tom Lane wrote:
Erik Jones <[EMAIL PROTECTED]> writes:
You beat me to the punch on this one. I was wanting to use modulo
operations for bin style partitioning as well, but this makes things
pretty awkward as well as unintuitive. So, to the postgres gurus:
What ar
Thanks Erik
>
> In a stored procedure you'd just execute the UPDATE
> and then check
> the FOUND variable to see if it found a row to
> update:
>
> UPDATE table_name SET foo='bar' WHERE id=5;
>
> IF NOT FOUND THEN
> INSERT INTO table_name (id, foo) VALUES (5, 'bar');
> END IF;
>
To be c
On Dec 10, 2007, at 4:48 PM, Ted Byers wrote:
--- Vivek Khera <[EMAIL PROTECTED]> wrote:
On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:
For what it's worth, the real algorithm would be
as follows. I
hadn't had enough coffee yet, and I forgot the
UPDATE bit.
IF
(a query matchin
--- Vivek Khera <[EMAIL PROTECTED]> wrote:
>
> On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:
>
> > For what it's worth, the real algorithm would be
> as follows. I
> > hadn't had enough coffee yet, and I forgot the
> UPDATE bit.
> >
> > IF
> > (a query matching your old data returns r
Erik Jones <[EMAIL PROTECTED]> writes:
> You beat me to the punch on this one. I was wanting to use modulo
> operations for bin style partitioning as well, but this makes things
> pretty awkward as well as unintuitive. So, to the postgres gurus:
> What are the limitations of check constra
On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:
For what it's worth, the real algorithm would be as follows. I
hadn't had enough coffee yet, and I forgot the UPDATE bit.
IF
(a query matching your old data returns rows)
THEN
UPDATE with your new data
ELSE
INSERT your new data
Still
please don't hijack old threads ("partitioned table query question" in
this case) and change the subject line to start your new question. it
messes up threaded mail readers.
thanks.
On Dec 10, 2007, at 3:00 PM, Nathan Wilhelmi wrote:
Hello - Does anyone happen to have a SQL script or funct
Vivek Khera wrote:
On Dec 7, 2007, at 11:42 AM, Colin Wetherbee wrote:
You can do this with a conditional. Something like the following
should work.
IF
NOT (a query matching your data returns rows)
THEN
INSERT (your new data)
There exists a race condition here unless you've locked your t
On Dec 10, 2007 2:19 PM, Chris Browne <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] ("Josh Harrison") writes:
> > Does slony support postgres major version upgrade ? ie., will it
> > replicate between different major versions?
>
> Yes, that's one of the major "use cases" for Slony-I.
>
> Version 1
On Mon, 2007-12-10 at 13:31 -0800, hjenkins wrote:
> I would like to take a timeseries of data and extract the rows of data
> flanking the gaps in it. So I need to compare timestamps from two
> adjacent
> rows, and determine if the interval is greater than the standard
> sampling
> interval.
It
Did you want to set to a specific known value or the min value of the
sequence. I think Pavel's sets to the min value of the sequence.
The below sets all the sequences to the same value
CREATE AGGREGATE sum ( BASETYPE = text,
SFUNC = textcat,
STYPE
[EMAIL PROTECTED] ("Josh Harrison") writes:
> Does slony support postgres major version upgrade ? ie., will it
> replicate between different major versions?
Yes, that's one of the major "use cases" for Slony-I.
Version 1.0 and 1.1 support (loosely) from PostgreSQL 7.3 to 8.1-ish.
Version 1.2 dro
On 10/12/2007, Nathan Wilhelmi <[EMAIL PROTECTED]> wrote:
> Hello - Does anyone happen to have a SQL script or function that can
> reset all the sequence values found in a given DB? When we rebuild the
> DB it would be handy to be able to set all the sequence back to a known
> starting place.
>
cr
Hello, all,
I would like to take a timeseries of data and extract the rows of data
flanking the gaps in it. So I need to compare timestamps from two adjacent
rows, and determine if the interval is greater than the standard sampling
interval.
Thanks for any help.
Regards,
H. Jenkins
---
On Dec 10, 2007 2:48 PM, Keary Suska <[EMAIL PROTECTED]> wrote:
> on 12/10/07 12:15 PM, [EMAIL PROTECTED] purportedly said:
>
> > To work around this I add an dbCon.rollBack() after select statement
> > above in good and in bad times. After that ALTER works.
> > Can someone explain me why I need th
On Mon, Dec 10, 2007 at 08:13:09PM +0100, Thomas Carsten Franke wrote:
> If I do so I get following error by Postgres:
>
> org.postgresql.util.PSQLException: ERROR: current transaction is
> aborted, commands ignored until end of transaction block
It means exactly what it says. You (or Java for yo
On 10/12/2007 16:39, Douglas McNaught wrote:
It sounds like the thing for you to do is drop template1 (which will
have no effect on template_postgis), create it again from template0,
and use template_postgis when you need it (otherwise template1 will be
used by default for new databases).
As I
on 12/10/07 12:15 PM, [EMAIL PROTECTED] purportedly said:
> To work around this I add an dbCon.rollBack() after select statement
> above in good and in bad times. After that ALTER works.
> Can someone explain me why I need this rollback ?
You only need the rollback when the SELECT statement fails
Hi first post here, I hope you can help.
We are running 8.1 on Windows 2003 server and have had a server crash
over the weekend. A virus is suspected - we maintain an app server on
someone else's network, though we do have anti-virus running, the
symptoms were worrying - so we had to wipe and rein
On Dec 10, 2007 2:13 PM, rihad <[EMAIL PROTECTED]> wrote:
> Hi, is there a way to get the difference in hours between two
> timestamps?
SELECT (EXTRACT (EPOCH FROM TIMESTAMP '20071211 00:00') - EXTRACT
(EPOCH FROM TIMESTAMP '20071209 01:00')) * INTERVAL '1 second';
---(end
Hello - Does anyone happen to have a SQL script or function that can
reset all the sequence values found in a given DB? When we rebuild the
DB it would be handy to be able to set all the sequence back to a known
starting place.
Thanks!
-Nate
---(end of broadcast)
On Dec 10, 2007, at 1:21 PM, Erik Jones wrote:
You beat me to the punch on this one. I was wanting to use modulo
operations for bin style partitioning as well, but this makes things
pretty awkward as well as unintuitive. So, to the postgres gurus:
What are the limitations of check cons
Hi,
following I tried for some application logic based data migration
Statement stmt = dbCon.createStatement();
try {
ResultSet geo_columns_rs = stmt.
executeQuery("SELECT baseline_check_version from geodb limit 1");
source_version = Versions.R03_00;
source_version = Versi
Hi, is there a way to get the difference in hours between two
timestamps? The HH{1,}:MM:SS format will do.
foo=> select timestamp '20071211 00:00' - timestamp '20071210 00:01';
?column?
--
23:59:00
(1 row)
foo=> select timestamp '20071211 00:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
following I tried for some application logic based data migration
Statement stmt = dbCon.createStatement();
try {
ResultSet geo_columns_rs = stmt.
executeQuery("SELECT baseline_check_version from geodb limit 1");
source_vers
On Dec 7, 2007, at 11:42 AM, Colin Wetherbee wrote:
You can do this with a conditional. Something like the following
should work.
IF
NOT (a query matching your data returns rows)
THEN
INSERT (your new data)
There exists a race condition here unless you've locked your tables.
--
On Dec 7, 2007, at 10:51 PM, Mason Hale wrote:
I'm implementing table partitioning on 8.2.5 -- I've got the tables
set up to partition based on the % 10 value of a key.
My problem is that I can't get the planner to take advantage of the
partitioning without also adding a key % 10 to the wh
Thanks all. I tried the appended code in a trigger
function, but postgresql won't take it.
It complains that assets.quantity is not a scalar.
However, the WHERE clause in that select statement
guarantees that at most only one record will be
returned. An open position on a given kind of asset
is
On Dec 10, 2007 10:53 AM, Robert Fitzpatrick <[EMAIL PROTECTED]> wrote:
> I have a PHP 5 app using pgsql 8.2 and a HTML table of clients now grown
> to almost 10,000 loading 25 per page. There is a filter feature atop all
> seven columns in the table listing (all varchar except one date column).
>
Thanks...Ill check that list
josh
On Dec 10, 2007 12:37 PM, Joshua D. Drake <[EMAIL PROTECTED]> wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On Mon, 10 Dec 2007 12:20:07 -0500
> "Josh Harrison" <[EMAIL PROTECTED]> wrote:
>
> > Hi,
> > Does slony support postgres major version upgr
Thanks All. I learned plenty this morning.
--- Douglas McNaught <[EMAIL PROTECTED]> wrote:
> On 12/10/07, Ted Byers <[EMAIL PROTECTED]>
> wrote:
>
> > OK. A worry. How is template_postgis
> constructed?
> > Is it just a handy reference to template1? Or
> does it
> > exist independantly? I do
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Mon, 10 Dec 2007 12:20:07 -0500
"Josh Harrison" <[EMAIL PROTECTED]> wrote:
> Hi,
> Does slony support postgres major version upgrade ? ie., will it
> replicate between different major versions?
Yes. But for further questions on Slony please see th
Robert Fitzpatrick escreveu:
I have a PHP 5 app using pgsql 8.2 and a HTML table of clients now grown
to almost 10,000 loading 25 per page. There is a filter feature atop all
seven columns in the table listing (all varchar except one date column).
Also, sorting can be done by clicking any column
On 10/12/2007 17:20, Josh Harrison wrote:
Does slony support postgres major version upgrade ? ie., will it
replicate between different major versions?
You'll get a more definitive answer on the Slony list, but I think so -
IIRC that's one of Slony's /raisons d'etre/.
Ray.
-
Hi,
Does slony support postgres major version upgrade ? ie., will it replicate
between different major versions?
Thanks
josh
Robert Fitzpatrick wrote:
Now my question, would it be better to create one index with all columns
in the table -or- a separate index for each column field? I was assuming
the latter, but would the index with all columns be beneficial as well?
Generally it's much better to have an index deal w
I think PgAdmin ,in 1.8 at least, is by default set to hide system
objects like the template databases.
To enable this
go to File->Options->Display and make sure to check the "Show system
objects in treeview". You may want to check some of the other options
as well.
Hope that helps,
Regina
--
I have a PHP 5 app using pgsql 8.2 and a HTML table of clients now grown
to almost 10,000 loading 25 per page. There is a filter feature atop all
seven columns in the table listing (all varchar except one date column).
Also, sorting can be done by clicking any column header. Some complain
of speed
On 12/10/07, Ted Byers <[EMAIL PROTECTED]> wrote:
> OK. A worry. How is template_postgis constructed?
> Is it just a handy reference to template1? Or does it
> exist independantly? I don't want to be dropping
> template1 only to find that breaking template_postgis.
All databases are separate
On 12/10/07, Ted Byers <[EMAIL PROTECTED]> wrote:
> So, how do I determine whether or not template1 really
> exists on my server and is a copy of template0 (as I'd
> infer from what I see in postgres) rather than
> template_postgis, and then modify things so that the
> default is the normal templa
Thanks Richard.
--- Richard Huxton <[EMAIL PROTECTED]> wrote:
> Ted Byers wrote:
> > Amyway, when I look at the server using pgadmin, I
> > don't see either template0 or template1. I see
> only
> > template_postgis. Should I be able to see
> template0
> > and template1 among the databases on th
Ted Byers wrote:
OK, Now I am a bit confused. The manual doesn't say
what to do if you don't see template1 or template0 on
the server, or even whether or not you should be able
to see them when using a tool like pgAdmin. But it
does say:
Well, it won't mention pgadmin because that's a separat
Tom Lane wrote:
I would think UNIQUE => one row is pretty obvious - what am I
missing? (Unless it's that I'm still stuck in 7.4.)
That would be the problem :-( ... a look at the code suggests that the
ability to do anything intelligent with expression indexes was added
in 8.0.
Whaa. Okay,
Ted Byers <[EMAIL PROTECTED]> writes:
> OK, Now I am a bit confused. The manual doesn't say
> what to do if you don't see template1 or template0 on
> the server, or even whether or not you should be able
> to see them when using a tool like pgAdmin.
Our manual is not in charge of documenting pgAd
On Dec 10, 2007 9:32 AM, John Burger <[EMAIL PROTECTED]> wrote:
> I have a unique function index on one of my tables:
>
> create table allWords (
>wordID serial PRIMARY KEY,
>word textNOT NULL
> );
> create unique index ix_allWords_lower on allWords (lower(word));
>
> To
John Burger <[EMAIL PROTECTED]> writes:
> create unique index ix_allWords_lower on allWords (lower(word));
> To my surprise, the planner does not seem to realize that only one
> row can result from using this index:
> I would think UNIQUE => one row is pretty obvious - what am I
> missing? (
OK, Now I am a bit confused. The manual doesn't say
what to do if you don't see template1 or template0 on
the server, or even whether or not you should be able
to see them when using a tool like pgAdmin. But it
does say:
"The postgres database is also created when a database
cluster is initializ
Ted Byers wrote:
Amyway, when I look at the server using pgadmin, I
don't see either template0 or template1. I see only
template_postgis. Should I be able to see template0
and template1 among the databases on the server, or
are they normally hidden to minimise the chances of
getting them screwe
--- Richard Huxton <[EMAIL PROTECTED]> wrote:
> Tino Wildenhain wrote:
> > Hi Ted,
> >
> > Ted Byers wrote:
> >> Thanks Uwe
> >>
> >> This is a great start. It reduces the dump from
> 2 MB
> >> down to 167K, but out of 6833 lines of SQL, 5744
> >> relate to the public schema in the DB, and I
>
Douglas McNaught wrote:
On 12/10/07, Ted Byers <[EMAIL PROTECTED]> wrote:
Where will I find template1? When I look at the
databases on the server, the only template I see is
called "template_postgis". Most of the extra stuff I
see in all my databases relates to geometry that I
find in this tem
Hi Ted,
Ted Byers wrote:
--- Tom Lane <[EMAIL PROTECTED]> wrote:
...
it's not pg_dump's fault --- you need to clean out
template1.
Thanks Tom,
Where will I find template1? When I look at the
databases on the server, the only template I see is
called "template_postgis". Most of the extra s
On Dec 8, 2007, at 9:21 AM, Geoffrey wrote:
I am quite new to Slony as well, but one of the first requirements
the docs state is:
Thus, examples of cases where Slony-I probably won't work out well
would include:
* Sites where connectivity is really "flakey"
* Replication to nodes t
On 12/10/07, Ted Byers <[EMAIL PROTECTED]> wrote:
>
> Where will I find template1? When I look at the
> databases on the server, the only template I see is
> called "template_postgis". Most of the extra stuff I
> see in all my databases relates to geometry that I
> find in this template. When I
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> Ted Byers <[EMAIL PROTECTED]> writes:
> > It seems the public schema is
> > automagically created by Postgres every time I
> create
> > a new database on a given server, and it has over
> a
> > dozen types, over 400 functions, &c. I don't
> really
> > un
I have a unique function index on one of my tables:
create table allWords (
wordIDserial PRIMARY KEY,
word textNOT NULL
);
create unique index ix_allWords_lower on allWords (lower(word));
To my surprise, the planner does not seem to realize that only one
row can resul
=?us-ascii?Q?Martin=20Korous?= <[EMAIL PROTECTED]> writes:
> < You're running a 7.something pg_dump against an 8.something server.
> < This will not work because that pg_dump doesn't know about 8.x
> < catalog layout.
> its standard answer for message `ERROR: column "datpath"`
> but I wrot
< You're running a 7.something pg_dump against an 8.something server.
< This will not work because that pg_dump doesn't know about 8.x
< catalog layout.
its standard answer for message `ERROR: column "datpath"`
but I wrote:
DATABASE:
#/var/pgsql/bin/pg_dump --version
pg_dump (PostgreSQL)
Ted Byers <[EMAIL PROTECTED]> writes:
> It seems the public schema is
> automagically created by Postgres every time I create
> a new database on a given server, and it has over a
> dozen types, over 400 functions, &c. I don't really
> understand why it needs to be duplicated in every Db
> on a se
On Dec 9, 2007 10:34 PM, x asasaxax <[EMAIL PROTECTED]> wrote:
> Hi everyone,
>
>I had the folowing problem: when i try to execute a xml_string function
> on a database 'a' it works, but when i try this in database 'b' it doesen´t
> works. Did anyone knows what its going on? Is that some kind o
Tino Wildenhain wrote:
Hi Ted,
Ted Byers wrote:
Thanks Uwe
This is a great start. It reduces the dump from 2 MB
down to 167K, but out of 6833 lines of SQL, 5744
relate to the public schema in the DB, and I didn't
touch that. It has over a dozen types, 419 functions,
&c., that were put there
Hi Ted,
Ted Byers wrote:
Thanks Uwe
This is a great start. It reduces the dump from 2 MB
down to 167K, but out of 6833 lines of SQL, 5744
relate to the public schema in the DB, and I didn't
touch that. It has over a dozen types, 419 functions,
&c., that were put there by postgresql the moment
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> Ted Byers <[EMAIL PROTECTED]> writes:
> > Is there a way to tell pg_dump to just dump the
> SQL
> > statements required to create the tables,
> sequences,
> > indeces, keys, &c.?
>
> pg_dump -s ?
>
Thanks Tom
> > I DON'T need to restore or
> > recreat
Thanks Uwe
This is a great start. It reduces the dump from 2 MB
down to 167K, but out of 6833 lines of SQL, 5744
relate to the public schema in the DB, and I didn't
touch that. It has over a dozen types, 419 functions,
&c., that were put there by postgresql the moment I
created the database. I'
On Mon, 10 Dec 2007 12:23:49 + (GMT)
Ashish Karalkar <[EMAIL PROTECTED]> wrote:
> Hello there,
> I am having data in table something like below:
>
> user_idtype_id
> 11
> 12
> 21
> 33
> 4
Ashish Karalkar wrote:
Hello there,
I am having data in table something like below:
user_idtype_id
11
12
21
33
43
51
1 10
7
Hello there,
I am having data in table something like below:
user_idtype_id
11
12
21
33
43
51
1 10
76
What i want is the co
x asasaxax wrote:
Hi everyone,
I had the folowing problem: when i try to execute a xml_string function
on a database 'a' it works, but when i try this in database 'b' it doesen´t
works. Did anyone knows what its going on? Is that some kind of permission?
No-one can tell, you didn't provide
Anton Nikiforov wrote:
Dear all,
i'm trying to create tables using pg_user (pg_authid) as a foreign key
for my table. I need to log and control that only registered users can
modify data and i want to control data changes via logging triggers. I
need to know who exactly was modifying data. To
85 matches
Mail list logo
