The last part got scrambled, should read like this:
(...)
Use it like this:
SELECT * FROM foo WHERE foo_id > myval();
Or, for the case at hand, an example in sql:
CREATE FUNCTION my_colors()
RETURNS text[] AS
$$ SELECT ARRAY['red','green','blue'] $$
LANGUAGE 'sql' IMMUTABLE;
Use it like thi
On Jul 30, 10:53 pm, [EMAIL PROTECTED] ("Richard Broersma")
wrote:
> On Wed, Jul 30, 2008 at 1:16 PM, EXT-Rothermel, Peter M
> <[EMAIL PROTECTED]> wrote:
> > Is there any means like (#define or DECLARE ) where I can write SQL like
(...)
> CREATE VIEW primary_colors_foos AS
> SELECT * FROM foo
>
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes:
> I'm doing something like:
> delete from table1 where id not in (select id from table2).
> table1 contains ~1M record table2 contains ~ 600K record and id is
> unique.
That's going to pretty much suck unless you've got work_mem set high
enough to
Oh, I see what you mean. Use EJBQL on a view. That would probably
work. Have to get going, so will try that when I get back in several
hours. I'll let you know how I go.
Andrew wrote:
I have given it consideration, but haven't tried it as I have
concluded that I would still have the same i
I have given it consideration, but haven't tried it as I have concluded
that I would still have the same issue. The problem occurs with the
client attempting to apply a UUID value to filter the result set. So I
would still have a datatype mismatch if I were to use a view.
I am confident that
Yeah, tried that, but get the following:
org.hibernate.MappingException: No Dialect mapping for JDBC type:
Thanks for the suggestion though.
Douglas McNaught wrote:
On Thu, Jul 31, 2008 at 5:57 PM, Andrew <[EMAIL PROTECTED]> wrote:
The only relevant thing I have been able to find r
On Thu, 31 Jul 2008 14:59:29 -0700 (PDT)
Lennin Caro <[EMAIL PROTECTED]> wrote:
> > The box is a 2x dual core Xeon (below 2GHz) with 4Gb ram.
> > Default debian etch setup.
> you recently run vacuum ?
The tables are pretty stable. I think no more than 20 records were
modified (update/insert/del
On Thu, Jul 31, 2008 at 5:57 PM, Andrew <[EMAIL PROTECTED]> wrote:
> The only relevant thing I have been able to find relating to it is
> http://archives.postgresql.org/pgsql-bugs/2007-12/msg00061.php which
> suggests adding a ::uuid cast to the parameter.
>
> However, when doing that, hibernate t
I'm currently using JPA with Hibernate as my ORM and have been able to
convince hibernate to play nicely with the Postgresql UUID. Most of my
queries have been in EJBQL using the JPA entity manager's createQuery.
However when I try to do a UNION, JPA only returned the results of the
first que
--- On Thu, 7/31/08, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote:
> From: Ivan Sergio Borgonovo <[EMAIL PROTECTED]>
> Subject: [GENERAL] eliminating records not in (select id ... so SLOW?
> To: "PostgreSQL"
> Date: Thursday, July 31, 2008, 9:45 PM
> I'm doing something like:
>
> delete fr
I'm doing something like:
delete from table1 where id not in (select id from table2).
both id are indexed.
table1 contains ~1M record table2 contains ~ 600K record and id is
unique.
The box is a 2x dual core Xeon (below 2GHz) with 4Gb ram.
Default debian etch setup.
It has been working for ove
On Thursday 31 July 2008 15:28:14 Mike Gould wrote:
> We currently use SQL Anywhere 9.0.2 as our database in our current product.
> The main reason is the low maintenance that is required and the
> installation is a breeze. All we need to do is to ship 3 dll's and a db
> and log file. I understan
I guess it would help if I cast to the correct type. I was doing cast(
cast( 1000 as integer ) as char ) instead of character varying, char(n)
or text.
Thanks,
Warren
Warren Bell wrote:
I am trying to cast an int to a character. The int is the number 1000
it gets cast down to "1" and not "
On Thu, Jul 31, 2008 at 4:17 PM, Richard Broersma
<[EMAIL PROTECTED]> wrote:
> On Thu, Jul 31, 2008 at 1:03 PM, Warren Bell <[EMAIL PROTECTED]> wrote:
>> I am trying to cast an int to a character. The int is the number 1000 it
>> gets cast down to "1" and not "1000". How do I cast from int to chara
--- On Thu, 7/31/08, Warren Bell <[EMAIL PROTECTED]> wrote:
> From: Warren Bell <[EMAIL PROTECTED]>
> Subject: [GENERAL] CAST(integer_field AS character) truncates trailing zeros
> To: pgsql-general@postgresql.org
> Date: Thursday, July 31, 2008, 8:03 PM
> I am trying to cast an int to a charac
On Thu, Jul 31, 2008 at 1:03 PM, Warren Bell <[EMAIL PROTECTED]> wrote:
> I am trying to cast an int to a character. The int is the number 1000 it
> gets cast down to "1" and not "1000". How do I cast from int to character
> without loosing the trailing zeros?
Here is what I get when I try:
postg
On Thu, Jul 31, 2008 at 4:03 PM, Warren Bell <[EMAIL PROTECTED]> wrote:
> I am trying to cast an int to a character. The int is the number 1000 it
> gets cast down to "1" and not "1000". How do I cast from int to character
> without loosing the trailing zeros?
Please supply the exact syntax that y
I am trying to cast an int to a character. The int is the number 1000 it
gets cast down to "1" and not "1000". How do I cast from int to
character without loosing the trailing zeros?
--
Thanks,
Warren Bell
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes
I cannot seem to create an environment to recompile the DLL. Does anyone
have a compiled version of the DLL that is compatible with PostgreSQL
8.2 that they can share with me?
TNX David
Magnus Hagander wrote:
IIRC the binary release that's on there is too old to be supported on
recent versions
I created this script sometime ago and it works fine for me and
others.. Maybe it might work for you
http://www.zeroaccess.org/postgresql-backup
1.0RC1 is pretty stable I have ran it for 3 weeks without any problems
Quoting "Rob Richardson" <[EMAIL PROTECTED]>:
Greetings again!
A few day
Christophe wrote:
>
> On Jul 31, 2008, at 11:49 AM, Alvaro Herrera wrote:
>> Why does it matter?
>
> I'm attempting to "clean out" a connection that is in an unknown state
> (along the lines of what pgpool does when reusing an open connection).
> Of course, I could just fire an ABORT down, but i
We currently use SQL Anywhere 9.0.2 as our database in our current product.
The main reason is the low maintenance that is required and the installation is
a breeze. All we need to do is to ship 3 dll's and a db and log file.
I understand that with PostGres that the installation will end up bei
Christophe wrote:
On Jul 31, 2008, at 11:12 AM, Richard Huxton wrote:
A pl/pgsql function *always* executes within a transaction.
Indeed so. What I'm looking for is a way of detecting if a transaction
block has been opened (i.e., we're within a BEGIN).
There is no difference between a tra
On Jul 31, 2008, at 11:49 AM, Alvaro Herrera wrote:
Why does it matter?
Ah, I see, deep confusing on my part regarding PL/pgSQL and
tranasctions! Ignore question. :)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.post
On Jul 31, 2008, at 11:49 AM, Alvaro Herrera wrote:
Why does it matter?
I'm attempting to "clean out" a connection that is in an unknown
state (along the lines of what pgpool does when reusing an open
connection). Of course, I could just fire an ABORT down, but it
seems nicer to avoid d
Christophe wrote:
>
> On Jul 31, 2008, at 11:12 AM, Richard Huxton wrote:
>> A pl/pgsql function *always* executes within a transaction.
>
> Indeed so. What I'm looking for is a way of detecting if a transaction
> block has been opened (i.e., we're within a BEGIN).
Why does it matter?
--
Alvar
On Jul 31, 2008, at 11:12 AM, Richard Huxton wrote:
A pl/pgsql function *always* executes within a transaction.
Indeed so. What I'm looking for is a way of detecting if a
transaction block has been opened (i.e., we're within a BEGIN).
--
Sent via pgsql-general mailing list (pgsql-general@
IIRC the binary release that's on there is too old to be supported on
recent versions of PostgreSQL. You will need to build it from source,
which should be patched with the magic block.
//Magnus
David R Robison wrote:
> I copied the file to the "lib" directory under the PostgreSQL install
> and
Is it allowed to declare a cursor in this manner??
Declare
procgraphic cursor for select p_id.p_id.process_id from p_id.p_id,
processes_count
where p_id.p_id.p_id_id = processes_count.p_id_id;
Bob
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Bob Pawley" <[EMAIL
I copied the file to the "lib" directory under the PostgreSQL install
and ran the SQL command:
CREATE FUNCTION ldap_search(text, text, text, text,text,text) RETURNS
SETOF RECORD AS 'libdblink-ldap.dll', 'pg_ldap_search' LANGUAGE C STABLE;
it gave me the following error:
ERROR: incompatible l
Christophe wrote:
My apologies if this is in the docs and I missed it, but is there a
PL/pgSQL function equivalent for the pglib function PQtransactionStatus
(i.e., a way to find out if we're in an open transaction block, and if
that transaction is in an error status)?
A pl/pgsql function *al
On 12:54 pm 07/31/08 "Mike Gould" <[EMAIL PROTECTED]> wrote:
> 1. Is the planner/optimizer intelligent enough to know when we are
> not doing a query based on location?
In short yes.
If the DB doesn't see the condition by which your tables are partitioned it
will search all the partitions.
> 2.
My apologies if this is in the docs and I missed it, but is there a
PL/pgSQL function equivalent for the pglib function
PQtransactionStatus (i.e., a way to find out if we're in an open
transaction block, and if that transaction is in an error status)?
--
Sent via pgsql-general mailing list
I have several tables that we have partitioned by physical location. This
seems to give us the best overall performance when doing location specific
queries. I have a few questions.
1. Is the planner/optimizer intelligent enough to know when we are not doing a
query based on location? For ex
Thanks, I found the extension but no documentation on how to install it
or use it. Can you give me some pointers? TNX David
Magnus Hagander wrote:
David R Robison wrote:
I have an LDAP directory that contains contact information and a
PostgreSQL table that has contact names. What I want to
"Bob Pawley" <[EMAIL PROTECTED]> writes:
> Right.
> This is the cursor statement.
> Open procgraphic for select p_id.p_id.process_id from p_id.p_id,
> processes_count
> where p_id.p_id.p_id_id = processes_count.p_id_id;
Sorry, we're not bright enough to handle WHERE CURRENT OF on a join
--- pe
Right.
This is the cursor statement.
Open procgraphic for select p_id.p_id.process_id from p_id.p_id,
processes_count
where p_id.p_id.p_id_id = processes_count.p_id_id;
If process_total = 1 Then
Fetch first from procgraphic into process_id;
Update p_id.p_id
set proc_graphic_position = '1
"Bob Pawley" <[EMAIL PROTECTED]> writes:
> Could somebody translate this error message for me??
> "cursor is not simply updateable scan of table "p_id"
You're trying to do an "UPDATE WHERE CURRENT OF cursor", right?
What it means is that the cursor definition is too complicated for
Postgres to fi
Could somebody translate this error message for me??
Bob
"cursor is not simply updateable scan of table "p_id"
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Bohdan Linda wrote:
> Hello,
>
> > If you just want to ship segments to a standby server on a timely basis,
> > the setting to tune should be archive_timeout, no?
>
> just curious, how would the stand-by DB process the segments?
You mean this?
http://www.postgresql.org/docs/8.3/static/pgstandby
I am loading a huge file using C, STDIN
The program fails immediately on "canceling statement due to statement
timeout"
Any idea?
Thanks
Danny
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql
Hello,
> If you just want to ship segments to a standby server on a timely basis,
> the setting to tune should be archive_timeout, no?
just curious, how would the stand-by DB process the segments?
Regards,
Bohdan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make ch
David R Robison wrote:
> I have an LDAP directory that contains contact information and a
> PostgreSQL table that has contact names. What I want to do is write a
> SELECT that will join the names in the table with the data in the LDAP
> directory (such as phone number, e-mail address, etc). Is this
I have an LDAP directory that contains contact information and a
PostgreSQL table that has contact names. What I want to do is write a
SELECT that will join the names in the table with the data in the LDAP
directory (such as phone number, e-mail address, etc). Is this possible?
Has anyone done
Greg Smith wrote:
> On Wed, 30 Jul 2008, Rob Adams wrote:
>
>> Could someone please explain in layman's terms the implications of
>> using a checkpoint_timeout of ~1min as well? Is it a bad idea?
>
> Lowering checkpoint_timeout makes checkpoints more frequent, causing the
> database to go thro
marko <[EMAIL PROTECTED]> writes:
> # Error: Can't load '/usr/home/markuser/DBD-Pg-2.8.7/blib/arch/
> auto/DBD/Pg/Pg.so' for module DBD::Pg: /usr/home/markuser/DBD-Pg-2.8.7/
> blib/arch/auto/DBD/Pg/Pg.so: Undefined symbol "BIO_new_mem_buf" at /
> usr/local/lib/perl5/5.8.8/mach/DynaLoader.pm li
One thing I left out of my last post:
Thanks to all of you for your assitance.
RobR
I found my problem. I trimmed down the function I was having trouble
with to the following:
CREATE OR REPLACE FUNCTION recalculate_heating_time(int4)
RETURNS int4 AS
$BODY$
declare
ChargeNum ALIAS for $1;
ChargeReccharge%rowtype;
HeatingTime int4;
IntervalMinutes float4
I tried to write a variadic function with pl/perl, but apparently arrays
are passed to pl/perl in their textout format.
Parsing it in pl/perl is possible but *very* cumbersome. Is there any
plan to add some sanity to passing arrays to plperl function?
I can already return [1,2,3], so maybe such tra
> Kev <[EMAIL PROTECTED]> writes:
> > ...because the case should force it to only evaluate 'old' when
> > TG_OP
> > = 'UPDATE' and otherwise ('INSERT') skip through to 't'. But this
> > causes the same error on insert. I suspect it's because the select
> > query gets parameterized and at that p
My thanks to all for the quick replies. Now I can't get it not to work!
I guess the computer gremlins that all of us are paid to deny they exist
were playing games with me.
RobR
"Rob Richardson" <[EMAIL PROTECTED]> writes:
> So how do I specify an interval in a function?
Does this help?
([EMAIL PROTECTED]:5432/test) [2008-07-31 15:49:54]
# CREATE OR REPLACE FUNCTION time_cmp_with_1w_offset
(_l_ts timestamp, _r_ts timestamp)
RETURNS boolean AS $$
DECLARE
On 2008-07-31, at 8:36 AM, Rob Richardson wrote:
declare
ThreeHours interval;
begin
ThreeHours = interval '3 hours'; -- throws a syntax error
ThreeHours = '3 hours'::interval; -- also throws a syntax error
end;
So how do I specify an interval in a function?
Works for me:
CREATE
Greetings!
In the help file under date and time functions, I see that intervals can
be specified as "interval '3 hours' ". In a PgAdmin SQL window, I can
enter "select interval '3 hours' ", and it will return me "03:00:00", as
expected. I can also enter "select '3 hours'::interval", and get the
locate libpq.so returned:
/usr/home/markuser/postgresql-8.2.4/src/interfaces/libpq/libpq.so
/usr/home/markuser/postgresql-8.2.4/src/interfaces/libpq/libpq.so.5
/usr/local/lib/libpq.so
/usr/local/lib/libpq.so.3
/usr/local/pgsql/lib/libpq.so
/usr/local/pgsql/lib/libpq.so.5
from the location of the
On Tue, Jul 29, 2008 at 7:52 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Ivan Zolotukhin" <[EMAIL PROTECTED]> writes:
>> In pseudo code it looks like the following. There are 2 tables, empty
>> abstract_table with 3 columns (id, col1, col2) and many tables (e.g.
>> inherited_table1_with_data) that i
On Jul 31, 1:34 am, [EMAIL PROTECTED] (Tom Lane) wrote:
> marko <[EMAIL PROTECTED]> writes:
> > I'm running FreeBSD 6.2 and I have Postgresql 8.2.4 installed with
> > Perl v5.8.8. I'm trying to test DBD-Pg-2.8.7 after compilation and I
> > get this error after 'make test':
> > # Error: Can't
On Jul 31, 1:34 am, [EMAIL PROTECTED] (Tom Lane) wrote:
> marko <[EMAIL PROTECTED]> writes:
> > I'm running FreeBSD 6.2 and I have Postgresql 8.2.4 installed with
> > Perl v5.8.8. I'm trying to test DBD-Pg-2.8.7 after compilation and I
> > get this error after 'make test':
> > # Error: Can't
On Thu, Jul 31, 2008 at 11:24:35AM +0200, Bohdan Linda wrote:
> /opt/postgres-8.3.3/bin/psql -h remote -p postgres
> psql: FATAL: the database system is starting up
>
I am attaching additional info. The /dev/null is understable, but what I
am worried is that if I query status of a server vi
Cousin Florence souhaite rappeler le message «».
Florence Cousin
02 51 78 38 46
[EMAIL PROTECTED]
-
Ce qui se conçoit bien s'énonce clairement. Et ce qui va sans dire va mieux en
le disant. Anonyme (inspiré par Boileau)
--
Sent via pgsql-general mailing list (pgsql-g
Hello,
I am fiddling around with pgpool-II and online recovery. Recovery depends
on remote starting of a cluster. This means I need to ssh into a box,
start clustern (with PITR recovery) and terminate that ssh connection.
If I use the following script:
ssh -T remote "export LD_LIBRARY_PATH=/opt/
Pascal Cohen wrote:
> But to sum up we would like to collect statistics (write mostly tables,
> high volume generation, data not critical) on an application usage on a
> read mostly DB with the least impact on this DB perfs. ANn we would also
> like to be able to archive outside the DB, the old co
Hello
We are developing an application and would like to compute statistics on
it in order:
- to have a better understanding of what is used mostly in our
application to model at best our load test scenarios.
- to get information on the usage of the application for other departments.
The probl
Garg, Manjit wrote:
> Hi ,
>
> Actually I was looking for some method / tool (utility) which keeps both
> the database on different servers in sync automatically.
>
> Looking for some features into postgres.conf file if possible.
There's no built in replication for postgres at the moment.
Che
65 matches
Mail list logo
