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;
inde
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 p
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
> 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
thoma
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 pro
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 release
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 J
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 t
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, i
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
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 fo
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 a
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 t_unit
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 ex
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 wh
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 Myeve
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 readin
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 beh
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 co
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
> c
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 custo
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
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 w
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 cus
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 fro
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, i
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
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,
location_id
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
"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
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.
http://www.postgresql.org/docs/8.3/interactive/ecpg-dy
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 an
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 th
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 clau
"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 constr
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 u
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
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 s
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, s
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
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,
>
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 PostgreS
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 seamless
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 Lan
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 ma
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 us
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
Time:
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; dro
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
Forgot to mention I'm running (PostgreSQL) 8.2.9
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Starting with a database where analyze has never been run I get worse
performance after running it -- is there something I'm missing?
Hopefully the log below shows it clearly:
test=> EXPLAIN ANALYZE
SELECT COUNT(DISTINCT "agent_agent"."id")
FROM "agent_agent" INNER JOIN "auth_user" ON
("agent_a
70 matches
Mail list logo
