What would you recommend for say, 500 global national statistical
variables,
500 regional and 500 subregional and 500 global aggregations?
Years being
covered having something between 10 and 60 years for each of these
variables. All available for 240 countries/territories.
I generally
Alban Hertroys skrev:
Nis Jørgensen wrote:
If you can't wait, you are probably better off working around the
problem. Standard solution is to do:
UPDATE master SET m2 = -m2;
UPDATE master SET m2 = -m2+1;
or something similar.
Would something like
UPDATE master set m2 = master2.m2
A few more comments on your table design.
Stefan Schwarzer skrev:
So, instead of the earlier mentioned database design, I would have
something like this:
- one table for the country names/ids/etc. (Afghanistan, 1; Albania,
2)
There is a well-established natural key for countries -
Hello List,
I wonder if it is possible to make data selected in one query NOT
accessible for a SELECT in another query?
The thing is that I have a cleaning script that selects some rows
from a table and processes them.
During that processing I don't want these rows to end up in another
On Sep 12, 2007, at 3:52 AM, Tino Wildenhain wrote:
Hi,
Tom Allison schrieb:
On Sep 11, 2007, at 5:49 AM, Tom Allison wrote:
I was able get my database working again.
Never figured out why...
My database data (sorry about the redundancy there) is sitting on
a RAID1 array with LVM and
On 9/27/07, John Smith [EMAIL PROTECTED] wrote:
On 9/27/07, John Smith [EMAIL PROTECTED] wrote:
how'd i grant select on (all current and future tables inside a
private schema) to username without turning that user into superuser?
grant usage on... doesn't do it.
or do i, everytime i
Ottavio Campana wrote:
Richard Huxton ha scritto:
Ottavio Campana wrote:
Is there a way to export tables in order, so that dependencies are
always met? reading the manpage of pg_dump I found the -Fc flag, but I
haven't understood if it is good for me and how it works. Or is there a
On Fri, Sep 28, 2007 at 02:08:18PM +0200, Stefan Schwarzer wrote:
how can I avoid results like this: 9.50184e+06
Instead it should return the real value, as 950184.
Presumably to_text would do what you want. Alternatively, perhaps you
intended your column to be type numeric?
Have a nice day,
On 9/28/07, Stefan Schwarzer [EMAIL PROTECTED] wrote:
Hi there,
how can I avoid results like this: 9.50184e+06
Instead it should return the real value, as 950184.
Thanks for any hints!
Cast it to numeric:
select 9.50184e+06::real::numeric;
But know that you're losing accuracy if you're
I don't think this would be too hard to effect:
When pg_dumping a schema, have an additional flag -m newschemaname,
that would convert all references in the dump from the original
schema to the new schema name.
Thus the command:
pg_dump -c -s myoldschemaname -m mynewschemaname mydatabase
--- Stefan Schwarzer [EMAIL PROTECTED]
wrote:
Hi there,
how can I avoid results like this: 9.50184e+06
Instead it should return the real value, as
950184.
But 9.50184e+06 IS the real value! That is about nine
and a half million, not nine hundred and fifty
thousand, BTW. I do not see
Estimados...tengo un problema con la zona horaria de (GMT-06:00) Guadalajara,
Ciudad de México, Monterrey - Nuevo.
Cuando me conecto desde una aplicacion java a un servidor postgres todo en la
misma maquina a el momento de insertar registros me cambia las horas me
adelanta 5 horas imagino
Hi there,
how can I avoid results like this: 9.50184e+06
Instead it should return the real value, as 950184.
Thanks for any hints!
Stef
Lean Back and Relax - Enjoy some Nature Photography:
Triggers have never been inherited, right? Not in any version?
I'm pretty sure that's the case, but I'm debugging some old logging and just
need to confirm it.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of
On Fri, Sep 28, 2007 at 12:50:34PM -0400, Alvaro Herrera wrote:
But he does have a WHERE condition. THe problem is, probably, that the
condition is not selective enough so the planner chooses to do a
seqscan.
Or else the planner has a bad idea of how selective the condition is.
I've found
John Smith escribió:
On 9/27/07, John Smith [EMAIL PROTECTED] wrote:
On 9/27/07, John Smith [EMAIL PROTECTED] wrote:
how'd i grant select on (all current and future tables inside a
private schema) to username without turning that user into superuser?
grant usage on... doesn't do it.
Hi,
is there any way to cast a generic row to an array or to a table type?
The example is trivial, but it explains what I'm trying to do:
nb1=# select * from tab1;
a | t
---+---
1 | a
2 | b
3 | c
(3 rows)
nb1=# select r from (select row(tab1.*) as r from tab1)x;
r
---
(1,a)
(2,b)
(3,c)
Richard Huxton ha scritto:
Ottavio Campana wrote:
Is there a way to export tables in order, so that dependencies are
always met? reading the manpage of pg_dump I found the -Fc flag, but I
haven't understood if it is good for me and how it works. Or is there a
way to relax constraints while
Tom Lane ha scritto:
Nico Sabbi [EMAIL PROTECTED] writes:
is there any way to cast a generic row to an array or to a table type?
row(...)::composite_type should work in 8.2 and up.
regards, tom lane
---(end of
A. Kretschmer wrote:
am Fri, dem 28.09.2007, um 11:56:46 -0400 mailte Mike Charnoky folgendes:
Hi,
I am still having problems performing a count(*) on a large table. This
Now, certain count(*) queries are failing to complete for certain time
ranges (I killed the query after about
On Thu, 2007-09-27 at 23:58 +0100, Gregory Stark wrote:
It keeps the same information in more than one place. Consider:
1
1.1
1.1.1
Note that all three records contain the root's id of 1. If you want to
reparent 1.1 to be 2.1 you have to know that all its children also need to be
Nis Jørgensen wrote:
Alban Hertroys skrev:
Would something like
UPDATE master set m2 = master2.m2
FROM (
SELECT m2 +1
FROM master m
WHERE m.master_id = master.master_id
ORDER BY m2 DESC
) master2
work? I think it might be faster (and possibly cause less index
Ah, but there is a standardised list of country-codes ideal for the
DBA since the code is (usually) easily understandable.
http://en.wikipedia.org/wiki/ISO_3166
The only problem might be if historical data uses old boundaries
(e.g. Soviet Union, Yugoslavia).
Yep, have all of them (ISO-2,
On Sep 28, 2007, at 5:09 AM, Tom Allison wrote:
I know reiserfs does better performance wise, but there's no point
in going fast if you can't steer.
I recently had to replace 16 Western Digital 10kRPM SATA drives with
Hitachi 7.2kRPM drives because the WD drives kept randomly (and
Stefan Schwarzer wrote:
Ok, I do understand that.
So, instead of the earlier mentioned database design, I would have
something like this:
- one table for the country names/ids/etc. (Afghanistan, 1; Albania,
2)
- one table for the variable names/ids/etc. (GDP, 1; Population, 2;
Stefan Schwarzer skrev:
What would you recommend for say, 500 global national statistical
variables,
500 regional and 500 subregional and 500 global aggregations? Years
being
covered having something between 10 and 60 years for each of these
variables. All available for 240
Nis Jørgensen wrote:
If you can't wait, you are probably better off working around the
problem. Standard solution is to do:
UPDATE master SET m2 = -m2;
UPDATE master SET m2 = -m2+1;
or something similar.
Would something like
UPDATE master set m2 = master2.m2
FROM (
SELECT m2
On Sep 28, 2007, at 9:07 AM, Ottavio Campana wrote:
But why does pg_dump does not already exports data such that previous
tables do not depend on successive ones?
Because you can't always sort your tables that way. The restore
procedure is responsible for either sorting or disabling the
John Smith wrote:
and grant usage on new tables in schema... doesn't exist yet. which
leads to my next question (see
http://svr5.postgresql.org/pgsql-hackers/2005-01/msg01070.php)- are we
there yet?
If I understand your question, you cannot grant anticipatory
privileges to tables which do
Ottavio Campana wrote:
Is there a way to export tables in order, so that dependencies are
always met? reading the manpage of pg_dump I found the -Fc flag, but I
haven't understood if it is good for me and how it works. Or is there a
way to relax constraints while loading data?
Try a pg_dump
Hi,
I am still having problems performing a count(*) on a large table. This
is a followup from a recent thread:
http://archives.postgresql.org/pgsql-general/2007-09/msg00561.php
Since the last time these problems happened, we have tweaked some
postgresql config parameters (fsm, etc). I also
On 9/28/07, Stefan Schwarzer [EMAIL PROTECTED] wrote:
Hi there,
how can I avoid results like this: 9.50184e+06
Instead it should return the real value, as 950184.
The type 'real' in postgresql comes from the mathematical definition
of real, numbers that can be expressed as a fraction, or
Alban Hertroys wrote:
If you _do_ need this table (because you want to constrain your
statistical data to only contain a specific set of years, or because you
need a quick list of available years to select from): Make the year
primary key and drop the artificial index.
Years are perfectly fine
am Fri, dem 28.09.2007, um 11:56:46 -0400 mailte Mike Charnoky folgendes:
Hi,
I am still having problems performing a count(*) on a large table. This
Now, certain count(*) queries are failing to complete for certain time
ranges (I killed the query after about 24 hours). The table is
Mike Charnoky wrote:
Hi,
I am still having problems performing a count(*) on a large table. This
is a followup from a recent thread:
http://archives.postgresql.org/pgsql-general/2007-09/msg00561.php
Since the last time these problems happened, we have tweaked some
postgresql config
am Fri, dem 28.09.2007, um 12:50:34 -0400 mailte Alvaro Herrera folgendes:
A. Kretschmer wrote:
am Fri, dem 28.09.2007, um 11:56:46 -0400 mailte Mike Charnoky folgendes:
Hi,
I am still having problems performing a count(*) on a large table. This
Now, certain count(*) queries
Alban Hertroys skrev:
Nis Jørgensen wrote:
Alban Hertroys skrev:
Would something like
UPDATE master set m2 = master2.m2
FROM (
SELECT m2 +1
FROM master m
WHERE m.master_id = master.master_id
ORDER BY m2 DESC
) master2
work? I think it might be faster (and
I had to manipulate the headers a bit, as I hadn't noticed the message
that reached me first was from the newsgroup instead of the ML.
Nis Jørgensen wrote:
Alban Hertroys skrev:
As I said, I don't understand what you think it does. What you are doing
is similar to writing
SELECT m2
FROM
Alban Hertroys skrev:
Nis Jørgensen wrote:
Alban Hertroys skrev:
As I said, I don't understand what you think it does. What you are doing
is similar to writing
SELECT m2
FROM master, (
SELECT m2
FROM master m
WHERE m.master_id = master.master_id
)
Which doesn'
Nico Sabbi [EMAIL PROTECTED] writes:
is there any way to cast a generic row to an array or to a table type?
row(...)::composite_type should work in 8.2 and up.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you
The autovacuum is turned on. Since this is pg8.1, I don't know when the
table was actually last vacuumed. I *did* run analyze on the table,
though. Also, nothing has been deleted in this table... so vacuum
should have no affect, right?
Mike
Sean Davis wrote:
Mike Charnoky wrote:
Hi,
I am
Alvaro Herrera [EMAIL PROTECTED] writes:
A. Kretschmer wrote:
am Fri, dem 28.09.2007, um 11:56:46 -0400 mailte Mike Charnoky folgendes:
Hi,
I am still having problems performing a count(*) on a large table. This
Now, certain count(*) queries are failing to complete for certain
Nico Sabbi [EMAIL PROTECTED] writes:
nb1=# select r.a from (select row(tab1.*)::tab1 as r from tab1)x;
ERROR: missing FROM-clause entry for table r
LINE 1: select r.a from (select row(tab1.*)::tab1 as r from tab1)x;
^
I tried many variations (including casting x as tab1)
In response to Mike Charnoky [EMAIL PROTECTED]:
The autovacuum is turned on. Since this is pg8.1, I don't know when the
table was actually last vacuumed. I *did* run analyze on the table,
though. Also, nothing has been deleted in this table... so vacuum
should have no affect, right?
Ben wrote:
Woah, when did that come around? Talk about sweet syntactic sugar
8.2
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Vivek Khera ha scritto:
On Sep 28, 2007, at 9:07 AM, Ottavio Campana wrote:
But why does pg_dump does not already exports data such that previous
tables do not depend on successive ones?
Because you can't always sort your tables that way. The restore
procedure is responsible for either
With respect to the ALTER TABLE SET STATISTICS... how do I determine a
good value to use? This wasn't really clear in the pg docs. Also, do I
need to run ANALYZE on the table after I change the statistics?
Here are the EXPLAINs from the queries:
db=# explain select count(*) from
Scott Ribe [EMAIL PROTECTED] writes:
Triggers have never been inherited, right? Not in any version?
AFAIR, no. If they had been I kinda doubt we would have removed it.
regards, tom lane
---(end of broadcast)---
TIP 4:
On 9/28/07, Mike Charnoky [EMAIL PROTECTED] wrote:
Hi,
I am still having problems performing a count(*) on a large table. This
is a followup from a recent thread:
http://archives.postgresql.org/pgsql-general/2007-09/msg00561.php
Since the last time these problems happened, we have tweaked
Hi!
initdb use SQL_ASCII as the default characterset encoding when it is
not given option -E and when it can not correctly derive one from
locale. I suggest initdb use UNICODE instead of SQL_ASCII because
UNICODE is far more useful than SQL_ASCII.
Not all webmasters are willing to spend time
CN wrote:
Hi!
initdb use SQL_ASCII as the default characterset encoding when it is
not given option -E and when it can not correctly derive one from
locale. I suggest initdb use UNICODE instead of SQL_ASCII because
UNICODE is far more useful than SQL_ASCII.
Not all webmasters are willing to
On Fri, Sep 28, 2007 at 09:32:43PM -0400, Carlos Moreno wrote:
Oh, and BTW, welcome to version 8 of PostgreSQL ... The default
encoding for initdb is . Ta-d!!! Unicode !!!
No, it isn't. If you get UTF8 (formerly UNICODE) as a default then
it's because initdb is picking it up from your
Michael Fuhr [EMAIL PROTECTED] writes:
No, it isn't. If you get UTF8 (formerly UNICODE) as a default then
it's because initdb is picking it up from your environment.
Which initdb has done since 8.0. If the OP is such a rabid UTF8 fan,
one wonders why his default locale setting isn't using
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 09/28/07 21:12, Tom Lane wrote:
Michael Fuhr [EMAIL PROTECTED] writes:
No, it isn't. If you get UTF8 (formerly UNICODE) as a default then
it's because initdb is picking it up from your environment.
Which initdb has done since 8.0. If the OP
Michael Fuhr wrote:
On Fri, Sep 28, 2007 at 09:32:43PM -0400, Carlos Moreno wrote:
Oh, and BTW, welcome to version 8 of PostgreSQL ... The default
encoding for initdb is . Ta-d!!! Unicode !!!
No, it isn't. If you get UTF8 (formerly UNICODE) as a default then
it's because
55 matches
Mail list logo