same kind of goals we did. Anyway, I will send
our schema under separate cover, and I will investigate sending you
the data as well.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your
he number of US "terms of
sovereignty" that exist.
Yah, that's my point - some data sources might lump all these
together as state/province level entities, and some might not.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 1: i
standards.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Even ISO country codes are not guaranteed to be stable
I'm not sure where the idea that primary keys must be stable comes
from. There's nothing necessarily wrong with updating a primary
key. All a primary key does is uniquely identify a row in a table.
If that id changes over time, that's
Tyler Durden wrote:
I'm having some problems in performance in a simple select count(id)
from I have 700 000 records in one table, and when I do:
# explain select (id) from table_name;
-[ RECORD
1 ]
QUERY PLAN | Seq Scan on t
Lonni J Friedman wrote:
I have a need to
determine which rows in a specific table are less than 24 hours old.
I've tried (and failed) to do this with the age() function.
And on the suggestion of a timestamp column with DEFAULT NOW():
Unfortunately, its too late now. The database (and its ta
er join message using (userid)
group by userid, user.name, user.address;
As to whether this is faster or prettier than a subquery, I dunno.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send
Richard Huxton wrote:
Ah, but this just includes the time of the last message, not its data.
Oops, I read the OP's question as "date and time", rather than "data
and time". Nevermind. :)
- John D. Burger
MITRE
---
nute - does this have to do with the varying
number of days in different months?
Thanks.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 6: explain analyze is your friend
Tom Lane wrote:
Anyway, there's no doubt that we can point to the behavior of MAX/MIN
as defense for what we made GREATEST/LEAST do, so I'm inclined to
leave
their behavior alone, at least until such time as they're actually
standardized.
I don't think I buy this - MIN and MAX are aggregate
e
issues (but I have not looked closely at it). You can also see
Wikipedia for one of the most well known, due to Knuth/Wellford:
http://en.wikipedia.org/wiki/Algorithms_for_calculating_variance
- John D. Burger
MITRE
---(end of broadcast)---
tter plan. I don't
know the details of your setup, but you can do things like this with
any ordered type:
where test between '11' and '113'
or test >= '114'
I know this does not match the exact semantics of your query, but
hopefully you get the
rivileges to tables which do not yet exist, which I discovered in
this thread:
http://archives.postgresql.org/pgsql-general/2007-02/msg00911.php
If you follow the thread, you'll find that one reply pointed to some
existing functions for managing this stuff. I found these usef
select results were the
same in both cases, but I'm willing to believe that's an accident of
our data.
(Sorry if no one can answer my question without the table
definitions, etc. - it seemed worthwhile trying to get away without
that for now.)
Thanks.
- John D. Burger
the actual
pulling
would take some code that doesn't exist now, too,
Okay, good to know. The situation is obviously easy to avoid, I just
found the contrast surprising.
Thanks.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 1: if
Richard Broersma Jr wrote:
Here is the example that doesn't do what I expect:
--find all parents that have a mixture of boys and girls.
--but this doesn't return anything
SELECT *
FROM Parents AS P
WHERE 'girl' <> ALL ( SELECT gender
FROM Children AS C1
DB-related humor:
http://xkcd.com/327/
- John D. Burger
MITRE
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
As far as I can tell, all of the proposed solutions lack sample
independence. Take the OP's suggested approach of doing something
like this:
SELECT * FROM mydata
WHERE mydata.random_number >= (SELECT RANDOM() OFFSET 0)
ORDER BY mydata.random_number ASC LIMIT 100
All you're doing is pi
lumn, despite
its renaming. Contrast this with
... order by random; // plain column reference
This substantially breaks the principle of least surprise for me.
Caveat - this is on 7.4 (sigh), perhaps more modern versions have
different behavior.
- John D. Burger
MITRE
Tom Lane wrote:
It seemed reasonable to me that a select on the first element of an
array column could use an index on the column, but, as seen in this
example, I can't get it to do so:
Nope. The operators that go along with a btree index are equality,
less than, etc on the whole indexed colu
I'm developing some triggers for the first time, and I'm having
trouble analyzing their performance. Does anyone have any advice for
doing EXPLAIN and the like on statements involving NEW? For
instance, I'd like to know what plan PG is coming up with for this
fragment of a trigger functio
So two design patterns for a makeshift UPSERT have been presented -
one is to check beforehand, and only insert if the item isn't present
already, the other is to do the insert blindly and let PG check for
you, and catch any exceptions.
I'm also wondering what people's ideas are for a sort
It seemed reasonable to me that a select on the first element of an
array column could use an index on the column, but, as seen in this
example, I can't get it to do so:
=> create temp table tempPaths (path int[] primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"
Quoting the text to which you are responding is often the only
way to provide the necessary specific context for your comments.
As an illustration, which helps you understand the preceding
paragraph better, the extract above, or the mess below?
- John D. Burger
MITRE
On Dec 11, 2007, at 11:54,
ot;Do you prefer the good x above, or the bad x below?".
It's a fair cop (but society's to blame :). Sorry, I thought better
of it right after I hit Send.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 4: Have you search
D. Dante Lorenso wrote:
I'd really like to have ORDER BY and LIMIT for UPDATE and DELETE
commands. Is this possible?
UPDATE invoice i
SET reserve_ts = NOW() + '1 hour'::timestamp
FROM account a
WHERE a.acct_id = i.acct_id
AND i.reserve_ts < NOW()
AND a.status = 'A'
AND i.is_pai
. It turned out that the natural
keys were always positive, so I set up the sequence to range
=downward= from 0.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 6: explain analyze is your friend
ing around such issues, anyway.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
sing
on one delete).
How can I tell what the trigger is doing? I'm using 8.2.5 and I've
ANALYZED everything.
Thanks.
- John D. Burger
MITRE
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
r display (xterm, whatever) is set to Latin1,
presumably. There are at least three different encoding settings here:
how your data is stored in the database, what the server sends to psql,
and what your display thinks its getting. These must all be consistent
with one anot
I know that it was decided a fair few releases ago to stop creating an
implicit index for each foreign key,
By the way, I presume foreign key indices are used to check for
referential integrity on insert. Can the query planner also use then
somehow?
Thanks.
- John D. Burger
MITRE
.
Is that the case?
Thanks.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
I find all these statements about the near-uselessness of
NUMERIC^NUMERIC to be pretty amazing. It's fine to say, "no one seems
to be asking for this, so we haven't implemented it yet", but, c'mon,
folks, Postgres gets used for more than "business cas
ery efficiently.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
If you use a large enough space for the number you can reduce that
probability of an accidental collision to much less than that of
catastrophic hardware failure at which point it isn't noticably better
than having no chance of collisions.
I find the comparison unconvincing - if my hardware cras
7;ve gotten around this in a variety of ways, some less
principled than others.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
ANALYZE cannot run inside a BEGIN/END block
I'll be happy to find out that this restriction's been removed in later
versions.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send
Your running 7.2? That is all kinds of level of... huh? Why?
I'm not running it, my organization is. Not sure how to interpret "all
kinds of level of..." Are there any huge suckages that I can use to
leverage an update? I'm familiar with some of the smaller ones.
-
t get their attention.
Thanks!
- John D. Burger
MITRE
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
s it a Mac? If so,
and its firewall is on, you may need to open up port 5432. If you have
physical access to that machine, you can do this in System Preferences
-> Sharing -> Firewall. I can give more details if necessary.
- John D. Burger
MITRE
---
uld strongly suggest you do this instead:
LOWER(colname) = LOWER('x')
This is far more bullet-proof than lower-casing in the client, in case
the client and the server differ (mismatched locales, etc.). If you
don't want to use ILIKE for portability reasons (perfectly reasona
Well, they would have access to every world readable file on the
system, ie /etc, /usr, /lib, ... most files are world readable.
There's
a lot of discussion about this, yet no-one has demonstrated that COPY
FROM STDIN isn't just as good and avoids all the issues entirely.
Well they're world-re
is at least an order of magnitude higher than I need. Would much be
saved by dropping this down to 10 or less? I gather I could dial
shared_buffers up slightly (SHMMAX is 32M on our Solaris boxes), but is
there any substantive benefit to conservatively setting
max_connections?
Thanks.
-
t, correct?
Thanks.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
To store the detailed records the SQL novice would construct one table
pr. file and exclude any constant columns since these are redundant
(given in summary table). These detailed tables would then have
different column schemas - (mn,tmp,wind) for ~58%, (hr,mn,tmp,wind)
for ~40%, (d_o_y,hr,mn,t
f my own data - I don't know why I never
thought of it before.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 6: explain analyze is your friend
I want to disable dupplicate customer names in a database regardless to
case.
I tried
CREATE TABLE customer ( id SERIAL, name CHARACTER(70));
ALTER TABLE customer
ADD constraint customer_name_unique UNIQUE (UPPER(name));
but this is not allowed in Postgres
As Csaba suggested, a unique fun
ers (cost=0.00..10830971486.25 rows=2935950
width=8)
Filter: (subplan)
SubPlan
-> Seq Scan on tipsterauxiliary (cost=0.00..3330.04 rows=143604
width=0)
and appended below is the verbose output. Any explanation is very much
appreciated. Thanks!
- John
As I understand it, Postgres's query planner considers only trees of
joins - I don't know what the technical implications are of using DAG
plans, other than the obvious blowup in planning space.
I was recently in a similar situation, where a script essentially
needed to do a self-join on the r
I can't figure out why the following doesn't work:
select
(case
when count1 < 300 then 'Other'
else country1
end) as country2,
sum(count1) as count2
from (select coalesce(country, 'None') as country1, count(*) as count1
ns to determine whether cust3 is a date ...) as
dateCusts
where cust3::text::timestamp > CURRENT_DATE - interval '1 month';
- John D. Burger
MITRE
I have a table that has some columns which store 'custom' fields so the
content varies according to the user that the row
be equivalent. I tested
the two queries on small data sets, and they do indeed return the same
results.)
- John D. Burger
MITRE
explain select gazPlaceID from gazPlaces
where gazPlaceID not in (select gazPlaceID from gazContainers);
e chapter and
verse?
Thanks.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 6: explain analyze is your friend
efficient and practical. In fact, it might be the case that mapping
from a sparse DB representation to your internal data structures is
=more= efficient than naively using the same representation in both
places.
- John D. Burger
MITRE
---(e
Tom Lane wrote:
The information we've seen says that the only statistically
reliable way
to arrive at an accurate n_distinct estimate is to examine most of the
table :-(.
IIRC I picked an equation out of the literature partially on the basis
of it being simple and fairly cheap to compute...
have been surprised to find
such a nice comment pointing me at the literature.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
here other languages where sequences behave similarly?
> perl -e '@A = (1, 2, 3); print "@A\n"; $A[10] = 10; print "@A\n";'
1 2 3
1 2 310
- John D. Burger
MITRE
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
es in [1, 10]. If I'm only comparing
within such ratings, and possibly computing floating point averages,
etc., what are the good and bad points of using, say, SMALLINT? What
about NUMERIC(1) or (2)?
Thanks in advance for the usual brilliant replies!
- John D. Burg
ownside= to doing it?
Here's a simpler question - for static data, should I always cluster
on the index I think will be used the most?
Thanks.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
to optionally use parametric
modeling (this column is a Gaussian, let's estimate the mean and
variance, this one is a Beta distribution ...). Then the smarter
planner could spend some cycles applying more sophisticated
statistical modeling to problematic tables/columns.
- John D. B
Jochem van Dieten wrote:
I think you might want to check US Patent 6,763,359 before you
start writing any code.
http://tinyurl.com/yzjdve
- John D. Burger
MITRE
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ
key a
composite:
PRIMARY KEY (user_id_from, user_id_to, message_time)
This should cut way down on the possibility of key collision.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
I'm having trouble figuring out when (if) the planner inlines sql
functions (I'm running 7.4). I was assuming that pure sql functions
are kind of like views with parameters, but I can't seem to see any
cases where functions that select from a table get inlined. For
instance:
create func
Maurice Yarrow wrote:
So it turned out to be possible to do it like this:
CREATE SEQUENCE id_seq;
SELECT setval('id_seq',100111);
FYI, you could have done this:
CREATE SEQUENCE id_seq START 100111;
- John D. Burger
MITRE
---(end of
suppose) and can take a
while. Is there any way to convince the planner that the sorts are
unnecessary, and it can just zip the two tables together as is?
This is under PG 7.4, by the way. Any comments welcome.
- John D. Burger
MITRE
---(end of broadcast
own
problems.
Sorry for the pedantry ...
- John D. Burger
MITRE
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
n the OP's code sample seems problematic
in the other direction:
sprintf(buf, "%u", (unsigned int)PQoidValue(results));
since unsigned int could be as small as 16 bits, thus truncating the
OID value.
Ok, I'll stop now, I promise.
- John D. Burger
MITRE
Scott Ribe wrote:
where a <> b or (a is null and b is not null) or (a is not null and
b is null)
In the absence of IS DISTINCT FROM, I think this has the same semantics:
where coalesce(a, b) <> coalesce(b, a)
although it's not as concise as one might wish.
- John
mething like:
coalesce(a, 'SOME MAGIC VALUE') <> coalesce(b, 'SOME MAGIC VALUE')
and wanted to make it work for any types. Sigh.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
stallation - note that the above is only
guaranteed to work if architecture/compiler/etc. are all the same.
If the files were created by exactly the same PG instance, then you
should be okay.
- John D. Burger
MITRE
---(end of broadcast)---
Steve Crawford wrote:
Of course this breaks apart when dealing with that very rare syndrome
(name escapes me) where the child appears female at birth but is
actually a male whose male sex-organs descend and appear at puberty
so I
guess we need to add apparent_sex_at_birth.
It turns out ther
The good thing is that there are several companies supporting
Postgres,
so whatever one of them does it does not affect the market as a whole.
Surely there are also third-party companies that provide "support"
for MySqueal in some similar sense?
- John Burger
MITRE
Joshua D. Drake wrote:
Surely there are also third-party companies that provide "support"
for MySqueal in some similar sense?
Of course :) but... Fortune 2500+ for the most part will *not* use a
third party for support for something like MySQL.
Sure, but they won't use PG either, for essenti
Tom Lane wrote:
The other point I'd make against John's argument is that there are a
whole lot of Fortune 500 companies buying Red Hat support, and RH is
effectively a third party for large chunks of Linux. (Of course,
there are also large chunks for which Red Hat employees write as much
code a
Mike Poe wrote:
SELECT foo, baz, bar FROM public.table WHERE lastname ~*
'$lastname' OR ssn='$ssn'"
I need to leave the last name a wildcard in case someone enters a
partial name, lower case / upper case, etc.
I want the SSN to match exactly if they search by that.
The way it's written, if
Well, you could always start with something like Drupal:
http://www.drupal.com
I get access denied, seems to be here, rather: drupal.org
- John Burger
MITRE
---(end of broadcast)---
TIP 6: explain analyze is your friend
y more formats than I'd be likely to dream up
on my own.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Ken Johanson wrote:
Just to be sure, will the RETURNING clause work with custom
sequences (say, non numeric or increment by two) or other types of
key generators?... And how will triggers interfere with it (if at
all)?
RETURNING has nothing to do with sequences per se - it's just a way
ed would be to keep the out-of-memory copies of this kind of
data in something faster than a flat file - say Berkeley DB. Do
either of these things make sense?
- John D. Burger
MITRE
---(end of broadcast)---
TIP 3: Have you checked our exte
I had some ideas about this slow flat file issue, but it's apparently
not yet much of an issue, in fact ...
Someone talked about the postmaster having to be "at arms' length"
from the actual tables. But I was looking at the postmaster code,
and it seems to fork a new backend as soon as sel
Joris Dobbelsteen wrote:
Personally I've found nothing that will beat Excel for doing data
analysis. Learn to use the pivot table and pivot charts. They are
extremely powerful.
Funny, there is an on-going discussion about this on one of our
internal mailing lists. Excel is perhaps okay for
Tom Lane wrote:
Since the tables you need to touch are all shared, it's conceivable
that
this could be hacked around, but it seems awfully messy. Another
consideration is that this'd significantly increase the amount of work
done before validating that the connection request is authorized,
quite a lot about the secret. If this is an issue, there are
more sophisticated combining schemes that give the user no advantage
over someone who knows neither half of the secret.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 6: e
s others have observed, a
determined user can sniff the compound password out if they really
wish. I suspect the only really secure approach is some sort of
challenge-response algorithm, or a one-time pad in the application -
in either case, whatever the black-hat user sniffs off the wire or
Sorry if I'm the only one to find this amusing, but I see that the
original message was sent twenty minutes =after= I received it. :)
- John D. Burger
MITRE
---(end of broadcast)---
TIP 3: Have you checked our extensiv
With a slip of the keyboard, I just dropped a database I'd like to
have back. I don't have PITR or anything turned on - if nothing else
has been done to the cluster since then, is there any way to recover
anything at all?
Thanks.
- John Burger
MITRE
---(end of b
John D. Burger wrote:
With a slip of the keyboard, I just dropped a database I'd like to
have
back. I don't have PITR or anything turned on - if nothing else has
And no backups?
been done to the cluster since then, is there any way to recover
anything at all?
I have a backup
I have a backup as of last night, but I'd like to recover something
more recent if I can. If I'm stuck with the backup, I can just
stop the postmaster, drop the whole PG directory into place from
the backup, and restart, yes?
I presume from the near-deafening silence there's nothing else I
?
Thanks.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Alvaro Herrera wrote:
If I am reading the (7.4) docs correctly, privileges can be granted
only with respect to tables that exist at the time the GRANT command
is given
Yes.
In fact, I have to individually grant access to each table, and any
associated sequences, yes? How dangerous is it
Tom Lane wrote:
How dangerous is it to UPDATE pg_class
directly, perhaps copying the relacl column for a table that I've
done by hand with GRANT.
You can do it, and it will seem to work. However, unless you also
make
entries in pg_shdepend, bad things will happen if you later drop
any of
brary that provides rationals, or
model my code closely after one.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
rithms pretty easily. I haven't
looked, but I would be surprised if there was much OOP in the
rational code, it might be fairly trivial to "dumb it down" to C.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 3: Have y
create a function lower index and instead of calling ilike call ~
lower('123')
To clarify a little:
CREATE INDEX table_a_lower_field_1_idx on table_a ((lower(field_1)));
CREATE INDEX table_a_lower_field_2_idx on table_a ((lower(field_2)));
SELECT
*
FROM
table_a
WHERE
id
under the covers, so my client doesn't spin up the cpu?
(Yes, I know I could poll-sleep-poll-sleep - that's what I'll do if
there's nothing cleaner.)
- John D. Burger
MITRE
---(end of broadcast)---
TIP 6: explain analyze is your friend
for
the pointers toward this solution.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
binary exchange of timestamps is complicated. What does libpq do now
with timetamps, if the client requests data in binary form? How does
the client know whether it's getting floats or integers?
- John D. Burger
MITRE
---(end of broadcast)-
On Mar 29, 2007, at 17:39, Bryan Murphy wrote:
Is it possible to declare a unique constraint in combination with a
deleted flag?
For example, if I have a table like this:
CREATE TABLE
(
ID NOT NULL PRIMARY KEY,
Key VARCHAR(32) NOT NULL,
Value VARCHAR(32) NOT NULL,
Deleted INT NOT NUL
Bryan Murphy wrote:
I think the other guys suggestion will work better. ;)
Good lord, yes. Dunno what I was thinking - I use partial indexes
all the time, and I know a unique constraint is implemented with an
index. Just got carried away, I guess. :)
- John Burger
MITRE
nextval() and sequences are not what I'm looking for. I want to
assign the same id to all the rows imported from the same file.
Let's say user A is working on portfolio_id 3, and decides to
upload a spreadsheet with new values. I want to be able to import
the spreadsheet into the staging ta
1 - 100 of 160 matches
Mail list logo
