am Mon, dem 11.06.2007, um 21:23:59 - mailte [EMAIL PROTECTED] folgendes:
> My original table is like that:
>
> IDA1 A2 A3 cnt
> 1234 1 0 0 4
> 1234 1 0 1 8
> 1234 1 1 1 5
> 1235 1 0 0 6
> 1235 1
Hello,
I have a question regarding Postgres 8.2.
I am trying to set the datestyle in the postrgresql.conf permanently to European. But this does seem to work. This is what I did, I hope you can help me.
This is in the postgresql.conf.
datestyle = 'SQL,DMY'
but when I restart the server and u
On Jun 12, 2007, at 12:00 AM, Greg Smith wrote:
On Mon, 11 Jun 2007, Tom Allison wrote:
All of this was run on a Pentium II 450 MHz with 412MB RAM and a
software linear 0 pair or UDMA 66 7200RPM 8MB Cache drives (really
old) on seperate IDE channels with ReiserFS disk format.
Sometimes
Hi,
I am upgrading from 7.4.12 to 8.2.4 and I have run into a difference in date
style handling...
In 7.4.12 this would work
bugasbase2=# create table date_test (name date);
CREATE TABLE
bugasbase2=# insert into date_test values('Wed Jul 11 10:51:14 GMT+01:00
2001');
However in 8.2.4 this happ
Hi,
I have a problem when I create a function that returns a rowtype. I can't
access the individual fields of that rowtype from another function if I put the
results in a row type variable.
I'm inserting many records into a table (let's say, insert_table). This table
has a trigger on it since I
Hello,
my code works well:
CREATE TABLE Foo(a integer, b integer);
CREATE OR REPLACE FUNCTION ret_foo()
RETURNS Foo AS $$
DECLARE r Foo;
BEGIN
r := (10,20); --default
RETURN r;
END; $$
LANGUAGE plpgsql;
SELECT ret_foo();
CREATE OR REPLACE FUNCTION trig()
RETURNS TRIGGER AS $$
BEGIN
NEW :=
On Jun 12, 2007, at 4:04 , Adam Witney wrote:
bugasbase_070529=# insert into date_test values('Wed Jul 11 10:51:14
GMT+01:00 2001');
ERROR: invalid input syntax for type date: "Wed Jul 11 10:51:14 GMT
+01:00
2001"
I have found date_trunc and extract in the docs,
Both date_trunc and extr
At 2:05a -0400 on 12 Jun 2007, Johannes Konert wrote:
> that was a good hint. Suids are not working on bash-scripts, but with a
> restricted entry in /etc/sudoers now the backup-user can execute a
> copy-and-access-script to get the files from within PGDATA-dir.
If you're curious as to /why/ setui
Hi,
Yes that works but when I try to put the results of a rowtype returning
function into a rowtype variable in another function i cannot access the
individual attributes from variable. Only from the direct call.
ie.
x1 := (get_defaults_vals()).a1
x2 := (get_defaults_vals()).a2
x3 := (get_de
Hi all.
I'm trying to use this wonderful feature (thanks to anyone who
suggested/committed/implemented it).
According to the documentation:
(http://www.postgresql.org/docs/8.2/interactive/sql-insert.html)
"The optional RETURNING clause causes INSERT to compute and return
value(s) based on each ro
On Tue, Jun 12, 2007 at 04:18:32PM +0200, Vincenzo Romano wrote:
> Well, at least on v8.2.4 I cannot return count(*), that is the
> number of lines actually inserted into the table. Nor I can return
> any aggregate function of them.
I don't think anybody considered the possibility of using an aggr
i can't do
declare
vals default_vals%rowtype
begin
select get_defaults_vals() into vals;
x1 := default_vals.a1;
try
SELECT INTO vals * FROM get_defaults_vals()
Pavel
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
On Tuesday 12 June 2007 16:35:05 Martijn van Oosterhout wrote:
> On Tue, Jun 12, 2007 at 04:18:32PM +0200, Vincenzo Romano wrote:
> > Well, at least on v8.2.4 I cannot return count(*), that is the
> > number of lines actually inserted into the table. Nor I can
> > return any aggregate function of t
That worked. Thank you very much.
-Original Message-
From: [EMAIL PROTECTED]
Sent: 12 June 2007 16:41
To: Robert Wickert
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PL/PGSQL rowtype return pr
--
Adam Witney <[EMAIL PROTECTED]> writes:
> In 7.4.12 this would work
> bugasbase2=# insert into date_test values('Wed Jul 11 10:51:14 GMT+01:00
> 2001');
Hmm, there's an intentional and an unintentional change here. The
unintentional one is that that field order (tz before year) doesn't work
anym
[EMAIL PROTECTED] writes:
> I have a problem when I create a function that returns a rowtype. I can't a=
> ccess the individual fields of that rowtype from another function if I put =
> the results in a row type variable.
I think this is just a scalar assignment:
> SELECT get_default_values() INT
Vincenzo Romano <[EMAIL PROTECTED]> writes:
> Well, at least on v8.2.4 I cannot return count(*), that is the
> number of lines actually inserted into the table. Nor I can return
> any aggregate function of them.
> Am I doing anything wrong or is there some missing sentence in the
> documentation?
Vincenzo Romano <[EMAIL PROTECTED]> writes:
> Second, I'm not using nested statements, but rather a plain
> INSERT ... RETURNING COUNT(*) INTO var (it's inside a PL/PgSQL
> function body). It should not need any GROUP BY as the query is
> plain.
> Maybe the solution is somewhere in between what yo
On Tuesday 12 June 2007 18:26:35 Tom Lane wrote:
> Vincenzo Romano <[EMAIL PROTECTED]> writes:
> > Second, I'm not using nested statements, but rather a plain
> > INSERT ... RETURNING COUNT(*) INTO var (it's inside a PL/PgSQL
> > function body). It should not need any GROUP BY as the query is
> > p
Hi all.
I'd like to use a development tool for my PGSQL tables, indices and,
most important thing, views and functions.
Writing, fixing and maintaining functions and views is getting more
and more complicated as they are growing in number and complexity.
The tool that more closely mathces my ideas
Hi! And what about pgAdmin? http://www.pgadmin.org/
Jiri
Vincenzo Romano wrote:
Hi all.
I'd like to use a development tool for my PGSQL tables, indices and,
most important thing, views and functions.
Writing, fixing and maintaining functions and views is getting more
and more complicated as the
On Mon, Jun 11, 2007 at 03:08:07PM +0200, Alexander Staubo wrote:
> On 6/11/07, Mike Gould <[EMAIL PROTECTED]> wrote:
> >How can we do this with PostGres? Other than backup and restore or
> >creating
> >SQL scripts I haven't been able to find another method. Some of these
> >tables may have over
in the shameless self-promotion department, I'm rather fond of my own
tool, Andromeda.
http://www.andromeda-project.org
From your post, I think that you will be impressed with our dependency
tracking, change management, and overall build abilities. It was born
on Linux, I'm typing this repl
Excellent, thanks very much.
Will this make it into the general source tree? Or would I have to patch
this with future upgrades?
adam
On 12/6/07 16:51, "Tom Lane" <[EMAIL PROTECTED]> wrote:
> Adam Witney <[EMAIL PROTECTED]> writes:
>> In 7.4.12 this would work
>> bugasbase2=# insert into date
Hi All,
I have recently migrated a Postgres database from 7.4 running on gentoo to
8.1 running on SLES 10. I migrated the data using pg_dump and then running
the SQL in psql. The old server was a dual AMD opteron 2.6 GHz machine with
a RAID 5 array and 4GB memory. The new machine is a dual dual
Porell, Chris wrote:
I have recently migrated a Postgres database from 7.4 running on gentoo to
8.1 running on SLES 10. I migrated the data using pg_dump and then running
the SQL in psql. The old server was a dual AMD opteron 2.6 GHz machine with
a RAID 5 array and 4GB memory. The new machine
Hi,
I have a Pl/Perlu function in which I have written an insert statement in
tbl_xyz.
If there's not foreign key in this table, insert works fine.
As soon as i make one of its fields refer to tbl_abc i.e field a in tbl_xyz
refering to
field a in tbl_abc, insert fails.
Now the problem is, I can't
Porell, Chris wrote:
> ... I snagged a SELECT from one of the reports. It is a
> fairly complex query with 4 joins, which unfortunately I can't share. I can
> say that the plan for the query on both machines looks nearly identical -
> that is there are no sequential scans happening on the old DB
Adam Witney <[EMAIL PROTECTED]> writes:
> Will this make it into the general source tree?
http://archives.postgresql.org/pgsql-committers/2007-06/msg00148.php
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to
On 6/13/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote:
Hi,
I have a Pl/Perlu function in which I have written an insert statement in
tbl_xyz.
If there's not foreign key in this table, insert works fine.
As soon as i make one of its fields refer to tbl_abc i.e field a in tbl_xyz
refering to
Porell, Chris wrote:
> Hi All,
> I've changed shared_buffers, checkpoint_segments, effective_cache_size and
> random_page_cost in an attempt to improve performance. That has helped a
> little...
Another thought. Have you looked at "work_mem" - this is probably a far
more important setting.
The
On 6/13/07, Porell, Chris <[EMAIL PROTECTED]> wrote:
Hi All,
[...]
the SQL in psql. The old server was a dual AMD opteron 2.6 GHz machine with
a RAID 5 array and 4GB memory. The new machine is a dual dual-core AMD
Opteron 2.6GHz with RAID 1 and 16GB memory.
[...]
On my new DB server, it ta
Thanks all for the suggestions and for your willingness to help!
First, I have done a vacuumdb --all --analyze. I have this scheduled
nightly and I've also done it on demand to be sure it is fresh.
My work_mem parameter is currently 32000. When I upped it from 2000, I saw
no visible performance
"Porell, Chris" <[EMAIL PROTECTED]> writes:
> Lastly, the EXPLAIN ANALYZE output.
Do you have the equivalent for the old installation?
>-> Nested Loop (cost=4387.04..9817.54 rows=1 width=4) (actual
> time=1134.020..160195.837 rows=1842 loops=1)
> Join Filter: (("inner".recordnumb
I will get that and post it.
NEW NEWS... turning off "enable_seqscan" made the query run in about .25
seconds!!!
Now we're re-evaluating effective_cache_size
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 12, 2007 5:43 PM
To: Porell, Chris
Cc: 'pgsql-gen
"Porell, Chris" <[EMAIL PROTECTED]> writes:
> NEW NEWS... turning off "enable_seqscan" made the query run in about .25
> seconds!!!
[ squint... ] It was not the seqscans that were killing you, and
changing just that setting wouldn't have moved the rowcount estimates
one millimeter. I suppose thi
Joshua D. Drake wrote:
Joshua D. Drake wrote:
Hello,
Is anyone going to try for this? If not I can have our designer do it,
but I would prefer it be a community deal.
Reminder that we need designs in the next week and a half.
Joshua D. Drake
We are looking to have new signage for the
Hi Andrus!
On Jun 12, 6:38 pm, "Andrus" <[EMAIL PROTECTED]> wrote:
> 1 second if for repeated runs from pgAdmin.
> I my script same CREATE TEMP TABLE command takes appox 11 minutes for same
> data (see log below).
I cannot make much sense of this information. I can see no reason why
your script s
On Jun 7, 9:49 pm, [EMAIL PROTECTED] (Jerry Sievers) wrote:
>
> No sense in writing your own func for this; the feature is already
> provided.
>
> select array_to_string(array(select * from generate_series(1,5)), ',');
Tell me about redundant efforts! :)
Regards
Erwin
--
What year would your example choose?
The following works in 8.2.4:
select 'Wed Jul 11 2007 10:51:14 GMT+01:00'::timestamp with time zone
timestamptz
2007-07-11 06:51:14-05
It appears to ignore the day of the week, though:
select 'Mon Jul 11 2007 10:51:14 GMT+01:00'
Andrew,
How to speed up the query
We don't know.
Thank you.
Explain seems to show that PostgreSQL makes sequential scan of whole dok
table for every rid table row.
This is very slow since dok contains 55963 and rid 202421 rows.
I expected that there exists some trick like to force this D
On Tuesday 12 June 2007 06:04, Tom Allison wrote:
> On Jun 12, 2007, at 12:00 AM, Greg Smith wrote:
> > On Mon, 11 Jun 2007, Tom Allison wrote:
> >> All of this was run on a Pentium II 450 MHz with 412MB RAM and a
> >> software linear 0 pair or UDMA 66 7200RPM 8MB Cache drives (really
> >> old) on
I tried
CREATE TEMP TABLE mydel AS
SELECT r.dokumnr
FROM rid r
LEFT JOIN dok d USING (dokumnr)
WHERE d.dokumnr IS NULL;
DELETE FROM rid USING mydel WHERE rid.dokumnr =mydel.dokumnr;
drop table mydel;
and this runs 1 seconds intead for 2.2 hours.
Thank you very much.
This works!
It's sad th
This whole operation looks contradictory in several ways.
firma1.rid references firma1.dok on (dokumnr)
Therefore, referential integrity commands that there be NO rows in
firma1.rid with a dokumnr not present in firma1.dok.
Therefore your DELETE cannot possibly be deleting anything. It is
nonsens
Theis is also primary key constraint in dok table:
CONSTRAINT dok_pkey PRIMARY KEY (dokumnr),
On a sidenote: this primary implements a unique index anyway. The
additional index is useless. You can delete it to save time and
storage.
(Or maybe this is just another discrepancy between reality a
Ah! 3.) should read:
CREATE TEMP TABLE mydel AS SELECT DISTINCT dokumnr FROM firma1.rid;
DELETE FROM firma1.dok WHERE dokumnr NOT IN (SELECT dukumnr FROM
mydel);
I need to delete from firma1.rid table
So I cannot use this suggestion since firma1.dok.dokumnr is already unique
(primary key).
I'm going to develop a medium sized business desktop client server
application which will be deployed mostly on small sized networks and
later eventually, hopefully, on medium sized networks.
It will probably be developed using C#.
I do need a solid DBMS wich can work with .Net framework. I do kno
On Jun 9, 8:12 am, Rodrigo De León <[EMAIL PROTECTED]> wrote:
> On Jun 8, 7:59 pm, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
> wrote:
>
>
>
> > i have 4 tables :
>
> > date_table (date_id,.)
> > A_table(A_table_id, something1,something2.)
> > A1_table(A1_table_id references A_Table(A_Tabl
On May 31, 12:11 am, [EMAIL PROTECTED] (Tom Lane) wrote:
> [EMAIL PROTECTED] writes:
> > Other than the fact that no space may need to be reclaimed is anyone
> > aware of any circumstance - e.g. use of numeric data types - where a
> > full vacuum will simply not reclaim space?
>
> There are several
We don't know. You don't tell us what version you're running, show
us any EXPLAIN ANALYSE output, tell us about the data. . .
explain analyze delete from firma1.rid where dokumnr not in (select dokumnr
from firma1.dok)
produces
"Seq Scan on rid (cost=7703.59..98570208.00 rows=101210 width=
Hi,
In Oracle there are instances when as a developer I know how the data
is spread in the tables and I want to be sure the database uses the
right index. Does the following hold true in Postgresql for a query
like this:
select s.order_id
from small_orders_table s, orders o
where s.find_these_id
Hi,
I have a problem when I create a function that returns a rowtype. I can't
access the individual fields of that rowtype from another function if I put
the results in a row type variable.
I'm inserting many records into a table (let's say, insert_table). This
table has a trigger on it since I
On Sunday 10 June 2007 18:25, Tom Allison wrote:
> On Jun 10, 2007, at 2:14 PM, Joe Conway wrote:
> > Bill Moran wrote:
> >> Tom Allison <[EMAIL PROTECTED]> wrote:
> >>> If the user base gets to 100 or more, I'll be hitting a billion
> >>> rows before too long. I add about 70,000 rows per user per
i have 4 tables :
date_table (date_id,.)
A_table(A_table_id, something1,something2.)
A1_table(A1_table_id references A_Table(A_Table_id),A11,A12)
A2_table(A2_table_id references A_Table(A_table_id),A21,A22,...)
so i want to create a view with date_id,A_table_id,something1,
On Jun 11, 2007, at 10:44 AM, Gabriele wrote:
I'm going to develop a medium sized business desktop client server
application which will be deployed mostly on small sized networks and
later eventually, hopefully, on medium sized networks.
It will probably be developed using C#.
I do need a soli
Hi,
I have moved my database files from their default location to their own
partition on with their own controller and disks. PostgreSQL works OK
and I can connect with Pgadmin (Debian Lenny AMD64, PostgreSQL 8.2.4).
When I want to connect with psql however (with a non-root account) I get
the fol
On Mon, Jun 11, 2007 at 03:01:08PM +0300, Andrus wrote:
> > delete from firma1.rid where dokumnr not in (select dokumnr from
> >firma1.dok)
>
> Yes, it is nonsensial. However, this command should run fast even if it is
> nonsensial.
For future reference, I beleive the problem is the NOT IN. It
57 matches
Mail list logo
