Hi All,
This is my first post to this mailing list, so apologies if I am sending my
message to the incorrect place...
My question: I have configured a very basic text search for our application,
but got stuck at the point where I need the search vector to filter out some
unwanted text (HTML tags
On Tue, 2 Sep 2008, Pedro Stavrinides wrote:
Hi All,
This is my first post to this mailing list, so apologies if I am sending my
message to the incorrect place...
My question: I have configured a very basic text search for our application,
but got stuck at the point where I need the search
Tom Lane ha scritto:
Edoardo Panfili [EMAIL PROTECTED] writes:
my enumerated type is (this is a subset)
CREATE TYPE hibridation AS ENUM('none','genus','specie');
function declaration
CREATE FUNCTION ename(text,boolean,text,text RETURNS text AS
'funzioniGDB.so' LANGUAGE C IMMUTABLE;
index
Hi
I know the subject has been discussed before, but I dont find what any
information that helps me make it work, so please bear with me.
In pg 8.2 I want to write a function that gathers data from different
tables and joins it into a single resultset, similar to select * from
tableA, but the
On Tue, Sep 02, 2008 at 10:53:03AM +0200, Edoardo Panfili wrote:
But i have a little question about parameters of enum_out.
Datum enum_out(PG_FUNCTION_ARGS);
this is a part of my function
---
Datum esterna_nome2(PG_FUNCTION_ARGS){
On 02/09/2008 11:12, [EMAIL PROTECTED] wrote:
create function test2() returns setof record as
$$
declare
val_list record;
begin
select * into val_list from tableA;
return next val_list;
return:
end
$$ .
Hi there,
You need to do it like this:
with val_list in
Hi there,
You need to do it like this:
with val_list in
select * from tableA do
loop
return next val_list;
end loop;
return;
There's an example here:
Does that work in 8.2, cause i get the same error message as I described
above
regards
thomas
--
I need to merge 2 tables:
update d set c1=s.c1, c2=s.c2... from s where d.pk=s.pk;
insert into d (pk, c1, c2, ...) select pk, c1, c2, c3 from s
where s.pk not in (select pk from d);
Any strategy to make it faster? Including modifying postgres.conf
temporary?
Considering I've no concurrency
On 02/09/2008 12:18, [EMAIL PROTECTED] wrote:
Does that work in 8.2, cause i get the same error message as I described
above
Yep, it does. I should have mentioned that you call your function
like this:
select * from my_function()
- in other words, a SETOF-returning function takes the
I just want to transfer only one db in pg 8.2.5 to pg 8.3.3, others
are still remained on pg 8.2.5.
I call the only one db as db-foo, and the db owner as user-foo following.
The IP for pg 8.2.5 is 10.10.10.1 , IP for pg 8.3.3 is 10.10.10.2.
My operations are listed below:
1. install a new pg
Ivan Sergio Borgonovo [EMAIL PROTECTED] writes:
I need to merge 2 tables:
update d set c1=s.c1, c2=s.c2... from s where d.pk=s.pk;
insert into d (pk, c1, c2, ...) select pk, c1, c2, c3 from s
where s.pk not in (select pk from d);
you could try making the not in an exists. In released
On Tue, Sep 2, 2008 at 4:19 AM, Ivan Sergio Borgonovo
[EMAIL PROTECTED] wrote:
insert into d (pk, c1, c2, ...) select pk, c1, c2, c3 from s
where s.pk not in (select pk from d);
This insert statement might be faster:
INSERT INTO d (pk, c1, c2, ... )
SELECT pk, c1, c2, ...
FROM s
LEFT JOIN
Bill Todd wrote:
If I have a series of RAISE NOTICE 'xxx' statements in a plpgsql
function and I call the function from pgAdmin the notice messages are
concatenated on a single line on the Messages tab. Is there any way to
get each message to appear on a separate line?
Is there a better way
Richard Broersma wrote:
On Tue, Sep 2, 2008 at 4:19 AM, Ivan Sergio Borgonovo
[EMAIL PROTECTED] wrote:
insert into d (pk, c1, c2, ...) select pk, c1, c2, c3 from s
where s.pk not in (select pk from d);
This insert statement might be faster:
INSERT INTO d (pk, c1, c2, ... )
SELECT pk, c1,
Hi all,
if I want to get a list of types (ie., data types or enums), then I can
use the '\dT' command from within the postgreSQL client.
However, I cannot seem to figure out what the SQL alternative is to the
\dT command, so that I might get a list of types scriptable by SQL.
For example,
Is there a better way to query a database definition than select from
pg_catalog tables and views? For example, when I put out a new
software update, I need to verify that all the table, column,
constraint, etc definitions are correct for the update.
Thanks,
Bob
--
Sent via pgsql-general
On Tue, Sep 2, 2008 at 8:10 AM, Steve Clark [EMAIL PROTECTED] wrote:
Is there a way to do something similar with the following? I am an SQL noob
and the
following takes longer to run than is reasonable, on the order of hours.
insert into myevents select * from t_unit_event_log a where exists
Hello
2008/9/2 Bram Kuijper [EMAIL PROTECTED]:
Hi all,
if I want to get a list of types (ie., data types or enums), then I can use
the '\dT' command from within the postgreSQL client.
run psql with -E parameter. You will see all SQL statements used for
metacommands.
[EMAIL PROTECTED] ~]$
Hello
2008/9/2 Robert Gobeille [EMAIL PROTECTED]:
Is there a better way to query a database definition than select from
pg_catalog tables and views? For example, when I put out a new software
update, I need to verify that all the table, column, constraint, etc
definitions are correct for the
On Tue, Sep 02, 2008 at 09:25:50AM -0600, Robert Gobeille wrote:
Is there a better way to query a database definition than select
from pg_catalog tables and views? For example, when I put out a
new software update, I need to verify that all the table, column,
constraint, etc definitions are
Richard Broersma wrote:
On Tue, Sep 2, 2008 at 8:10 AM, Steve Clark [EMAIL PROTECTED] wrote:
Is there a way to do something similar with the following? I am an SQL noob
and the
following takes longer to run than is reasonable, on the order of hours.
insert into myevents select * from
On Tue, Sep 2, 2008 at 9:47 AM, Steve Clark [EMAIL PROTECTED] wrote:
srm2=# explain
srm2-# INSERT INTO Myevents
srm2-# SELECT *
ERROR: INSERT has more expressions than target columns
srm2=# explain
srm2-# INSERT INTO Myevents
srm2-# SELECT *
ERROR: INSERT has more expressions
Richard Broersma wrote:
On Tue, Sep 2, 2008 at 9:47 AM, Steve Clark [EMAIL PROTECTED] wrote:
srm2=# explain
srm2-# INSERT INTO Myevents
srm2-# SELECT *
ERROR: INSERT has more expressions than target columns
srm2=# explain
srm2-# INSERT INTO Myevents
srm2-# SELECT *
ERROR:
Richard Broersma [EMAIL PROTECTED] writes:
There is one possible alteration that may or many not improve
performance. This would be to replace the EXISTS with a LEFT JOIN
WHERE IS NOT NULL;
That changes the behavior, doesn't it? Or is event_log_no a unique
key for Myevents? I think what you
Tom Lane wrote:
Richard Broersma [EMAIL PROTECTED] writes:
There is one possible alteration that may or many not improve
performance. This would be to replace the EXISTS with a LEFT JOIN
WHERE IS NOT NULL;
That changes the behavior, doesn't it? Or is event_log_no a unique
key for
I have a custom inventory system built on JBoss AS, Seam, EJB3, JSF, and
Richfaces with a PostgreSQL back end that runs on Centos.
Being a single developer my time is short and I need to set up remote
replication and fail over without delaying what I am currently working on.
I have been
On Tue, Sep 2, 2008 at 10:58 AM, Tom Lane [EMAIL PROTECTED] wrote:
Richard Broersma [EMAIL PROTECTED] writes:
There is one possible alteration that may or many not improve
performance. This would be to replace the EXISTS with a LEFT JOIN
WHERE IS NOT NULL;
That changes the behavior, doesn't
I'm building an app that has a customers table, a locations table, a
products table, and a product_locations table.
They make a diamond shape.
The locations table and the products table each have a customer_id
column that links back to the customers table.
Then the product_locations table table
you can use setup a foreign key constraint in your create table so that column
is only populated when
there is a value which syncs to the referenced value
http://www.postgresql.org/docs/7.4/interactive/sql-createtable.html
Martin
__
Disclaimer and
On Tue, Sep 2, 2008 at 1:14 PM, Matthew Wilson [EMAIL PROTECTED] wrote:
I'm building an app that has a customers table, a locations table, a
products table, and a product_locations table.
They make a diamond shape.
The locations table and the products table each have a customer_id
column
On Tue 02 Sep 2008 04:06:20 PM EDT, Martin Gainty wrote:
you can use setup a foreign key constraint in your create table so that col=
umn is only populated when
there is a value which syncs to the referenced value
http://www.postgresql.org/docs/7.4/interactive/sql-createtable.html
I don't
On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote:
If the two subordinate tables ALWAYS have to point to the same place,
why two tables? Can't a customer have 1 location? I'm pretty sure
IBM has more than one corporate office you could ship things to.
Yeah, so the idea is one customer
On Tue, Sep 2, 2008 at 2:35 PM, Matthew Wilson [EMAIL PROTECTED] wrote:
On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote:
If the two subordinate tables ALWAYS have to point to the same place,
why two tables? Can't a customer have 1 location? I'm pretty sure
IBM has more than one
Hi!
If I try something like
ALTER TABLE foo
ADD CONSTRAINT foo_unique_xy
UNIQUE ( UPPER( x ), UPPER( y ) );
...I get a syntax error
ERROR: syntax error at or near (
LINE 3: UNIQUE ( UPPER( x ), UPPER( y ) );
Is there a way to do this?
TIA!
Kynn
Raymond O'Donnell wrote:
Can you show us more of your code?
I figured out how to make it work when using for instead of with.
Here is the code and the error message. I couldnt find anything in the
documentation about with but I did find something about for which I
managed to make work. In
HI,
I want to get more information whether MVCC conflicts with manual locking ?
regards,
Jose Lawrence
Unlimited freedom, unlimited storage. Get it now, on
http://help.yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html/
On Tue 02 Sep 2008 04:40:55 PM EDT, Scott Marlowe wrote:
On Tue, Sep 2, 2008 at 2:35 PM, Matthew Wilson [EMAIL PROTECTED] wrote:
On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote:
If the two subordinate tables ALWAYS have to point to the same place,
why two tables? Can't a customer have
Richard Broersma wrote:
On Sun, Aug 31, 2008 at 1:50 PM, Kevin Hunter [EMAIL PROTECTED] wrote:
7. Though I don't personally buy it, I have heard others complain
loudly that there is no print-version of Postgres documentation.
This one should be taken off the list. The postgresql online
Hi again, I tried to take the with form of the function further to
complete the actual method and met with another error message which I
dont understand.
I have a number for tables (partitioned) from which I need to retrieve
data. Another table keeps track of which tables I should read
On 02/09/2008 21:55, Thomas Finneid wrote:
with val_list in
select * from tableA
do
loop
return next val_list;
end loop;
Oops - my mistake - it should indeed be FOR, not WITH, hence your error
message.
One other thing in the above - you don't need the DO, it's
On Tue, Sep 2, 2008 at 2:09 AM, Michael Nolan [EMAIL PROTECTED] wrote:
Oracle handles connecting to multiple databases (even on multiple/remote
computers) fairly seamlessly, PG does not (yet.)
Stuff we do with plProxy on PostgreSQL is in some respects more advanced
than anything Oracle has to
Sounds like you really want this:
create table customer (
id serial primary key,
name text
);
create table location (
id serial primary key,
name text,
customer_id int references customer (id) );
create table product (
id serial primary key,
name text,
On 02/09/2008 22:19, Thomas Finneid wrote:
for num_list inselect num
from table_part_num_list
where se=se_arg
loop
table_name := 'table_part_'|| num_list.num;
select * into val_list
from table_name
where st=st_arg and
Kynn Jones [EMAIL PROTECTED] writes:
ALTER TABLE foo
ADD CONSTRAINT foo_unique_xy
UNIQUE ( UPPER( x ), UPPER( y ) );
...I get a syntax error
This is disallowed by the SQL standard: UNIQUE constraints can only be
on plain columns. (The practical reason for following their rule is
that
You can use a unique index:
CREATE UNIQUE INDEX idx_foo_unique_upper_x_upper_y ON foo
(UPPER(x), UPPER(y));
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
PostgreSQL has table partitioning in it so you don't have to dynamically
figure out which table to get the data from.
http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html
However, you can achieve dynamic SQL in plpgsql too.
Matthew Wilson wrote:
On Tue 02 Sep 2008 04:40:55 PM EDT, Scott Marlowe wrote:
On Tue, Sep 2, 2008 at 2:35 PM, Matthew Wilson [EMAIL PROTECTED] wrote:
On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote:
If the two subordinate tables ALWAYS have to point to the same place,
why two tables?
I believe you need to use for execute '...' loop, since
the table_name is dynamically composed.
Regards,
Alex Vinogradovs
On Tue, 2008-09-02 at 23:19 +0200, Thomas Finneid wrote:
Hi again, I tried to take the with form of the function further to
complete the actual method and met with
I am curious if the motivation is still valid for intentionally
omitting check sub-queries. (what was the motivation to begin with?)
Since we can effectively work around this limitation by doing the same
thing with a function in a CHECK constraint, why would we want to
prevent anyone from using
Richard Broersma escribió:
I am curious if the motivation is still valid for intentionally
omitting check sub-queries. (what was the motivation to begin with?)
The problem is that you have to rerun the query to verify that the CHECK
condition still holds, whenever the table that the CHECK
Richard Broersma [EMAIL PROTECTED] writes:
I am curious if the motivation is still valid for intentionally
omitting check sub-queries. (what was the motivation to begin with?)
Since we can effectively work around this limitation by doing the same
thing with a function in a CHECK constraint,
I have a table, d2, that has a field sacode that is almost always null.
In fact the stanullfrac in pg_statistic for this column is 1. I have
this index on my table:
d2_sgcode_sacode_idx btree (sgcode, sacode) WHERE sacode IS NOT NULL
AND sacode 0
The first version of my query wasn't
On Tue, 2008-09-02 at 15:30 -0700, Richard Broersma wrote:
I am curious if the motivation is still valid for intentionally
omitting check sub-queries. (what was the motivation to begin with?)
Since we can effectively work around this limitation by doing the same
thing with a function in a
On Tue, 2008-09-02 at 18:47 -0400, Alvaro Herrera wrote:
The problem is that you have to rerun the query to verify that the CHECK
condition still holds, whenever the table that the CHECK clause is
checking changes. This is rather problematic, because we'd need to make
the system aware of such
Joseph S [EMAIL PROTECTED] writes:
It seems that postgres can't figure out that it can use the index on
sacode unless I put d2.sacode 0 in my where clause.
Works for me ...
regression=# create table d2(sgcode int, sacode int);
CREATE TABLE
regression=# create index d2i on d2 (sgcode, sacode)
Jeff Davis [EMAIL PROTECTED] writes:
My question is not why don't we allow subqueries in CHECK, my question
is why do we allow stable/volatile functions?
Historically we've allowed it, and it's not clear what we'd buy by
changing that, other than breaking existing applications whose authors
On Tue, 2008-09-02 at 18:57 -0400, Tom Lane wrote:
The standard says that the constraint is guaranteed not to be violated,
which in the worst case means that any time you update the table(s)
referenced in the subquery, you have to retest the CHECK expression
at every row of the table having
On Tue, 2008-09-02 at 19:22 -0400, Tom Lane wrote:
Jeff Davis [EMAIL PROTECTED] writes:
My question is not why don't we allow subqueries in CHECK, my question
is why do we allow stable/volatile functions?
Historically we've allowed it, and it's not clear what we'd buy by
changing that,
On Tue 02 Sep 2008 05:35:25 PM EDT, D. Dante Lorenso wrote:
You could add a trigger to your product_location table that just
double-checked the customers matched or prevents the insert/update. A
PL/PGSQL function like this might help:
-- 8 8 --
On Tue, Sep 2, 2008 at 3:47 PM, Alvaro Herrera
[EMAIL PROTECTED] wrote:
The problem is that you have to rerun the query to verify that the CHECK
condition still holds, whenever the table that the CHECK clause is
checking changes. This is rather problematic, because we'd need to make
the
Jeff Davis [EMAIL PROTECTED] writes:
On Tue, 2008-09-02 at 19:22 -0400, Tom Lane wrote:
Jeff Davis [EMAIL PROTECTED] writes:
My question is not why don't we allow subqueries in CHECK, my question
is why do we allow stable/volatile functions?
Historically we've allowed it,
I suppose this
On Tuesday 02 September 2008 17:21:12 Asko Oja wrote:
On Tue, Sep 2, 2008 at 2:09 AM, Michael Nolan [EMAIL PROTECTED] wrote:
Oracle handles connecting to multiple databases (even on multiple/remote
computers) fairly seamlessly, PG does not (yet.)
Stuff we do with plProxy on PostgreSQL is in
On Tue, 2008-09-02 at 22:56 -0400, Robert Treat wrote:
On Tuesday 02 September 2008 17:21:12 Asko Oja wrote:
On Tue, Sep 2, 2008 at 2:09 AM, Michael Nolan [EMAIL PROTECTED] wrote:
Oracle handles connecting to multiple databases (even on multiple/remote
computers) fairly seamlessly, PG
Creating an index without the IS NOT NULL did not help. The complete
version:
PostgreSQL 8.2.9 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.1 20070105 (Red Hat 4.1.1-52)
Starting with a fresh database I got the same results you did, but not
with my production table.
Tom
Tom Lane wrote:
Can you force it to use the partial index by dropping the other index?
(Use begin; drop index ...; explain ...; rollback; to avoid dropping
the index for real.) It's quite unclear at this point whether it
I tried, and it ends up using a seqscan.
--
Sent via pgsql-general
Joseph S [EMAIL PROTECTED] writes:
Tom Lane wrote:
Can you force it to use the partial index by dropping the other index?
(Use begin; drop index ...; explain ...; rollback; to avoid dropping
the index for real.) It's quite unclear at this point whether it
I tried, and it ends up using a
Actually sacode is an int2. I didn't mention it before because I
already tried explicit casts and that didn't do anything. Now I just
realized that in your testcase you use int instead of int2. I just retried:
[local]:playpen=# create table d2(sgcode int, sacode int2);
CREATE TABLE
Joseph S [EMAIL PROTECTED] writes:
Starting with a fresh database I got the same results you did, but not
with my production table.
So, what's different between your table declaration and my toy example?
Can you force it to use the partial index by dropping the other index?
(Use begin; drop
I can't speak from the dba perspective, but I mainly write applications
against Postgres and Oracle. I've used a dozen or more RDBMS's and
Postgres and Oracle are by far the most similar of any two.
When the two differ, its about an even split for when I say I wish
Oracle did it like Postgres
Joseph S [EMAIL PROTECTED] writes:
Actually sacode is an int2.
Ah. 8.2 is not very good at proving cross-type predicate conditions,
because it lacks the concept of an operator family. You need to declare
the index this way:
create index d2i on d2 (sgcode, sacode)
WHERE sacode IS NOT NULL
70 matches
Mail list logo
