I have been useing an UPDATE from a SELECT on a single field and it works great.
Example:
UPDATE reports.mytemptable SET igka =
(SELECT
CASE
WHEN value >= 0 THEN value || ' (G}'
WHEN value < 0 THEN (value * -1) || ' (K)'
END
FROM (
SELECT ((mmpuncorvol - uutuncorvol)::float4 / 1000)::N
On Fri, Jul 08, 2005 at 03:06:29PM -0400, Stephen Bowman wrote:
>
> This is on a Xeon 3ghz with 2gb of RAM. There are 2 SCSI U/320 disks.
>
> For the variables, I have everything defaulted except for:
>
> shared_buffers = 2
> effective_cache_size = 68916
>
> I just added random_page_cost=3
Does anyone have suggestions that could improve performance?
If you have doubled your ram you could definately increase your
effective_cache.
As you also doubled your memory you could increase your work_mem but be
careful with this setting.
Also just to be certain are you running 32bit o
Can someone tell me why the table pg_user would not exist or did not get
created. I am using RedHat ES 3 with the default install. Thanks
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
On 7/8/05, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Fri, Jul 08, 2005 at 12:04:07PM -0400, Stephen Bowman wrote:
> >
> > Clearly it needs to use the index =)
>
> Indeed -- now to figure out why the estimates for index scans are
> so high. The row count estimates are almost spot-on, so that's
On Fri, Jul 08, 2005 at 12:04:07PM -0400, Stephen Bowman wrote:
>
> Clearly it needs to use the index =)
Indeed -- now to figure out why the estimates for index scans are
so high. The row count estimates are almost spot-on, so that's not
it. What are your settings for the following configuration
[Please copy the mailing list on replies so others can contribute
to and learn from the discussion.]
On Fri, Jul 08, 2005 at 09:59:35AM -0700, Tony Smith wrote:
> no, I did not see it. The insert is INSERT into user
> values(5, "George", 1); My dump command is
>
> pg_dump -d databaseName
>
> Th
On Fri, 2005-07-08 at 11:58, Mark Harrison wrote:
> is such a thing possible?
I've certainly seen it mentioned here more than once as working with
postgresql.
---(end of broadcast)---
TIP 6: explain analyze is your friend
is such a thing possible?
TIA!
Mark
--
Mark Harrison
Pixar Animation Studios
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
On Fri, Jul 08, 2005 at 09:03:47AM -0700, Tony Smith wrote:
>
>pg_dump -d
What's the complete command?
> In my dump file I found the insert statements
> something like:
>
> INSERT into user values(5, "George", 1);
> INSERT into user values(6, "Richard", 3);
> INSERT into use
David Gagnon <[EMAIL PROTECTED]> writes:
> update test set id=test2.id from test2 where id=test2.id;
>
> ERROR: column reference "id" is ambiguous
It's complaining about the second use of "id", which could mean either
"test2.id" (which would be a self-join) or "test.id" (which is what
you want).
On 7/8/05, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Fri, Jul 08, 2005 at 11:11:46AM -0400, Stephen Bowman wrote:
> >
> > SCANS=# explain select * from nessus_results where scan_id = 55;
> >QUERY PLAN
> > ---
I have tables create by:
create table address(
id serial PRIMARY KEY,
...);
create table user(
id serial PRIMARY KEY,
name text not NULL,
addressId integer REFERENCES address(id) NOT NULL,
UNIQUE(name)
);
...
I have used the database for sometime and now I would
like to
On Thu, 2005-07-07 at 08:30 +0100, Richard Huxton wrote:
> Ben-Nes Yonatan wrote:
> > Richard Huxton wrote:
> >
> >>> Can anyone tell me if Pl/PgSQL can support a multi dimensional array
> >>> (of up to 5 levels top I guess) with about 100,000 values?
> >>> and does it stress the system too much?
On Fri, Jul 08, 2005 at 11:11:46AM -0400, Stephen Bowman wrote:
>
> SCANS=# explain select * from nessus_results where scan_id = 55;
>QUERY PLAN
> -
> Seq Scan o
On Sun, 2005-03-07 at 23:14 +0300, Andrus Moor wrote:
> > Does the application really need superuser privileges or is that
> > just a convenience? It's usually a good idea to follow the "Principle
> > of Least Privilege" -- do some searches on that phrase to learn
> > more about it and the rationa
I have a construct where column has the same name .. and when I use the
FROM clause I get the following error:
create table test (
id varchar(8)
);
create table test2 (
id varchar(8)
);
update test set id=test2.id from test2 where id=test2.id;
ERROR: column reference "id" is ambi
Hello,
I'm experiencing inconsistent usage of an index that I cannot explain.
This is in postgresql 7.4.8. Details are as follows:
I have a relatively large table (~3.5 million rows):
SCANS=# \d nessus_results;
Table "public.nessus_results"
Column
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Fri, 8 Jul 2005, David Gagnon wrote:
>> UPDATE gl SET gl.glnum = gl.glnum
>> ERROR: column "gl" of relation "gl" does not exist
>>
>> the TABLE.COLUMN is not in the SQL standard ?
> For at least 92 (and I'm almost certain 99) not in the SET list. I
No ... I hadn't realized that the X-Mailing-List stuff 'disappeared' until
D'Arcy just mentioned it on -hackers ... it shoudl be back now, as well as
the List-* headers themselves ...
On Fri, 8 Jul 2005, Klint Gore wrote:
Is the new mailing software for the postgres lists going to stay like
Sure thing ... I turned on VERBOSE so I'll let it run for the next few days
(the weekend is kind of a bad time since activity on the database is low)
but by monday or tuesday I should have a few nightly runs to post to the
list ..
Thanks,
Dave
> -Original Message-
> From: Tom Lane [mailt
On Fri, Jul 08, 2005 at 09:59:03 -0400,
David Gagnon <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I was juste wondering why the following code don't work:
Because the value being set is a column name from the table being updated
and you aren't allowed to qualify it with a table name.
You don't rea
On Fri, 8 Jul 2005, David Gagnon wrote:
> Hi all,
>
> I was juste wondering why the following code don't work:
> UPDATE gl SET gl.glnum = gl.glnum
> ERROR: column "gl" of relation "gl" does not exist
>
> While the following works:
> UPDATE gl SET glnum = glnum;
>
> Query returned successfully: 1
Hi all,
I was juste wondering why the following code don't work:
UPDATE gl SET gl.glnum = gl.glnum
ERROR: column "gl" of relation "gl" does not exist
While the following works:
UPDATE gl SET glnum = glnum;
Query returned successfully: 177 rows affected, 281 ms execution time.
the TABLE.COLUMN
Hi
I am trying to get information about the Scalability options available for
PostgreSQL. Assuming you a database that has a huge volume of reads and
writes happening, what options have you got to scale your database. Many
commercial RDBMS' allow the clustering of Database servers and some other
o
"David Esposito" <[EMAIL PROTECTED]> writes:
> Hmm, how are you getting 1/6? The ballpark seems to be about 50% or more for
> those first 4 ...
Ooops, I got confused about which column was which.
Could we see the results of "vacuum verbose" on this table? Even
better, verbose output from two suc
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 07, 2005 11:53 PM
>
> "David Esposito" <[EMAIL PROTECTED]> writes:
> > Size of "problem" table: 6 million rows
> > Ballpark guess on INSERT/UPDATE/DELETE queries on this table: over 1
> > million/day
Am Freitag, 8. Juli 2005 03:22 schrieb Paul McGarry:
> When I do that I lose all the rows whose grp isn't in both tables. For
> example: ==
> SELECT grp, count(goodamount), sum(goodamount), count(badamount),
> sum(badamount) FROM lefty FULL OUTER JOIN righty USING (grp)
> WHERE lefty.day >= '20
28 matches
Mail list logo
