xception or whatever, boom.
One assumes you mean implicit foreign key relations, since MyISAM doesn't
enforce them (hence the reason they're "broken", potentiated by the lack of
transaction support).
Sadly, there is a market for wrong answers faster.
--
Lew
ELETE FROM export_messages WHERE export_id NOT IN
( SELECT export_id FROM exports );
--
Lew
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
two indexes, one on
each column?
I am guessing that changes to the table are slower with two indexes. How
could it affect queries?
What if the typical query pattern was more balanced among constraints on one
column, on the other, and on both?
--
Lew
---(end of
-posting (sending to each group separately) is frustrating because it
fragments the conversation.
--
Lew
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
of this category of issue as the Special Relativity of information.
--
Lew
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
e OP's case, "some of the databases" is "one of the databases".
--
Lew
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Francisco Reyes wrote:
Lew writes:
Strange? Why? Did you expect a particular statistical distribution?
Perhaps
The impression was that one query was returning everything.. and the
other only the records that did not exist in the one table.
you were surprised by the extent of the
aged PG fans: I prefer Postgre, I really
do.)
--
Lew
---(end of broadcast)---
TIP 6: explain analyze is your friend
aligning indexes on one-byte data.
The OP didn't say one-byte data, they said one-char data.
--
Lew
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
id )
-- , other information about a nation
, FOREIGN KEY ( king, nation_id )
REFERENCES nationalities ( man_id, nation_id )
);
The circular foreign-key relationships might be problematic - would someone
comment on that?
To handle that I would ensure that any transaction that updates
"natio
en-valued logic, not 3-valued, and that seems somehow
appropriate to me.
--
Lew
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
lt;= CURRENT_DATE will appear, in start_date
ascending order.
Is CURRENT_DATE evaluated once for the query or twice for each row?
--
Lew
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
is always set to "Company" but
that seems like a waste. I tried the above and I got a syntax error.
Can there be more than one row in "item_bases" with the same "item_id" but
different "item_type"s?
--
Lew
---(end of broadcast)--
erent from NULL, doesn't that mean that the
imported data still differ from the exported?
--
Lew
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
x27;s
invalid input without regard for their intent.
Calling rejection of invalid input "puritanical" is not an engineering
evaluation.
--
Lew
---(end of broadcast)---
TIP 6: explain analyze is your friend
your
responsibility that the byte sequences you create are valid characters in the
server character set encoding.)
<http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS>
Amazing how useful the docs are.
--
Lew
---(end o
ractive/indexes-bitmap-scans.html>
Sometimes multicolumn indexes are best, but sometimes it's better to create
separate indexes and rely on the index-combination feature.
The selectivity of each column is also relevant. If you have ten million rows
with "s_id" values
database engine capabilities for this
stuff.
As Richard metioned, there are plenty of boundary cases that require a
judgment call. Try to analyze which approach will have more risk in such
cases; sometimes that helps discriminate. It ain't always easy; that's why
they pay us the
'new' values but
I want to do my trigger as flexible as possible (I have several tables
to audit).
Somebody has any suggestion?
You got some suggestions in response to your multipost of this question in
pgsql.sql.
--
Lew
---(end of broadcast)-
idea that MySQL or SQL Server (assuming that's the
one you meant) do not need a DBA simply have their head up their ass. Someone
has to handle these tasks, and if the workload is high enough, that needs to
be someone's primary duty.
Unless, of course, you simply don't care about
as fast or faster than MySQL,
but almost as fast as Oracle (since the hardware platforms
are different, it's hard to compare directly).
This is something we've been saying for the last 2 years, and now we can prove it.
There are links to the SPEC site which show
A and I am comfortable
with the role." Variations might include, "I have a lot of DBA experience,
but somewhat more limited PG-specific knowledge", or, "I've been honing my PG
DBA skills, and I have a solid but basic competence", and the like.
Chances are you w
, using text (or any locale-encoded
representation) of it is a mistake. Hashes are not strings and shouldn't be
treated as such.
--
Lew
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choos
on AS type )
expression::type
Incidentally, float is about the worst data type to represent monetary amounts
that one can choose.
--
Lew
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
eans that updates are only for dependent columns.
What you want to do is literally remove the old record and insert a new one.
The duplicate key violation is the purpose of having a primary key, after all.
Otherwise you'd create two rows where you should only have one.
--
Lew
-
standard deviation of your k
runs. Compare with and without anti-virus running.
How much control do you have over the test machine's operating profile, i.e.,
what services and background tasks are running, network I/O, other factors
that can influence timing?
--
Lew
-
;http://java.sun.com/docs/books/tutorial/jdbc/index.html>
SunIYF, PGIYF (in this case, jdbc.postgresql.org IYF) and GIYF.
--
Lew
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
ty value with no quotes
in CSV mode.
That "with no quotes" phrase caught my attention.
Try eliminating the double quotes in the CSV file. "Wannabe" NULL would then be
,, (consecutive commas)
in the CSV. From the docs, you don't even need the "NULL AS" clause
ave to stop resisting that, now, and accept it.
--
Lew
---(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
IMIT appear, then OFFSET rows are skipped
*before starting to count the LIMIT rows that are returned.*
(emphasis added)
That's SQL, my friend. OFFSET first, then LIMIT. Irrespective of the order
in the query statement. It is what it is. SQL doesn't depend on LINQ for its
consistency.
Also, neither LIMIT nor OFFSET is a binary operator, so the term "commutative"
has to be understood metaphorically at best. What exactly do you mean by
"commutative"?
--
Lew
This post contained three requests for answers.
---(end of broadcast)---
TIP 6: explain analyze is your friend
made an external
project instead of bundled with the server source code. The JDBC driver
is available from http://jdbc.postgresql.org/download.html
Cross-posted to correct the OP's multi-post, so that pgsql.admin will also
contain the response(s).
--
Lew
---(e
looking for the JDBC driver. If
so, see http://jdbc.postgresql.org/.
Yes, this answer has been provided to the OP already.
--
Lew
---(end of broadcast)---
TIP 6: explain analyze is your friend
Lew wrote:
Try eliminating the double quotes in the CSV file. "Wannabe" NULL
would then be ,, (consecutive commas)
in the CSV. From the docs, you don't even need the "NULL AS"
clause in your COPY statement.
Ivan Sergio Borgonovo wrote:
Exactly what I did because
retval numeric(6,2);
rec table2%ROWTYPE;
begin
rec.s_val = 100;
rec.e_val = 101;
retval = (rec.s_val - rec.e_val) / rec.s_val;
return retval;
end
Also, one wonders why you need to do the calculation via a row or record at
all, when it would seem so easy just t
Lew wrote:
Postgres User wrote:
The problem turned out to be related to my function..
Given this table:
CREATE TABLE "table2" (
"s_val" numeric(6,2),
"e_val" numeric(6,2)
) WITH OIDS;
The following functions of code will set retval = NULL;
declare
retv
circularity in such convention-picking (because
by choosing English, we surely discriminate against the unilingual Latin
speakers).
De mortuis nil nisi bonum dicendum est.
--
Lew
---(end of broadcast)---
TIP 6: explain analyze is your friend
r were to take such
education as a "scolding" and take offense.
--
Lew
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
e '-um', p.658).
From <http://en.wiktionary.org/wiki/forum>
--
Lew
You want picky? I got picky!
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] s
uch marked communications in certain
jurisidictions? IANAL, but as I understand it from /The Hacker Crackdown/ by
Bruce Sterling, it figured into the defense of a BBS operator accused of
disseminating "confidential" AT&T information in the U.S. ca. 1990.
--
Lew
---(
m list posters. I'm going to read it on the list;
why in the world would I want that clutter in my inbox?
That's why my email address here is a separate one just for Usenet; I can
pretty much ignore replies that come directly to it.
--
Lew
---(end of broadca
at was the problem with my post?
--
Lew
---(end of broadcast)---
TIP 6: explain analyze is your friend
estion, and the
question isn't really related to the content of the email.
Strictly speaking, then, that isn't top-posting but inline posting, where "in
line" is position 0, with trim, where the amount trimmed is none.
--
Lew
---(end of broadcast)--
table WHERE COALESCE( folder_id, 0 ) = 0;
--
Lew
---(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
Lew wrote:
Tom Lane wrote:
Before you get into that, try reducing these parameters:
maintenance_work_mem = 1572864
work_mem = 1048576
They are way too high, especially the second one.
So if 1.5 MB maintenance_work_mem and 1 MB work_mem are "way too high",
why are the default
ues in the postgresql.conf on installation 16 MB and 1 MB,
respectively?
--
Lew
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
ordered.
I just want to know the times when I haven't gotten the complete order yet.
If we get more then we ordered, I don't want it to be in this query.
Huh?
How does that relate to the suggestion?
The suggested expression is mathematically equivalent to and perfectly
substitutable
071,852043,1537
11,952549,129124,446671,476345,168905,308274,404812,501024,738098,636071}
(1 row)
I now [know] why it happens an[d] how [to] avoid it, but it's ok?
Must work like this?
Yes and yes. LIMIT applies to the number of rows, and that's how it's
documented.
<http://www.postgresql.org/docs/9.0/interactive/queries-limit
s EAV is one of the big strikes against it. The quoted citation evidences
that difficulty quite well - even trying to come up with an example of an EAV
structure wound up with a non-EAV description.
--
Lew
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
it is.
Chances are good that the auto-genned sequence will work for you, if you think
about it. If not, would you share why you anticipate that gaps will cause
trouble for you?
--
Lew
---(end of broadcast)---
TIP 4: Have you searched our list arc
tions still use Oracle 8, or Java 1.3, for example, to see how
conservative many shops are with respect to upgrades.
I'm not saying they should be that conservative, but many organizations are
and we must be ready to deal with that.
--
Lew
t obsolete products for free, and
that these organizations should upgrade.
--
Lew
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
ne.net/wtz020.php>
for example, and it shows NZST as "GMT+12", plus one more for Daylight Saving
Time.
Likewise,
<http://en.wikipedia.org/wiki/Wellington>
lists Wellington's time zone as
Time zone NZST (UTC+12)
- Summer (DST) NZDT (UTC+13)
--
Lew
--
Sent via pgs
idly approaches one. The 50% point is a bit under
n=23.
Substitute 33 million for 365 for the OP's problem.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
and whose email address is
similar to mine, but has a zero where mine has an "o" (letter "oh"). In both
cases the email address is valid in and of itself, but is not valid for the
purpose intended.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
T
bOS" (later "WebOS"). Spanish
speakers thought that a very amusing name.
Pentaho looks good based on their website, though. I bookmarked them.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ever has a few rows in it, and in those cases, you likely don't
need an index.
And it is likely that the referenced table has an index since foreign keys
reference a primary key or unique-constrained (combination of) column(s).
--
Lew
--
Sent via pgsql-general mailing list (pgs
sheepjxx wrote:
To be precise,
The difference between
create table a (
foreign key (id) referenced by b(name),
);
create table b(
id integer referenced by b(name),
);
They're just two different forms of invalid SQL. The difference between them
makes no difference.
-
es tax can be 9.241%, and
equal or exceed 10%, as in Alabama, Arizona, California and Illinois.
<http://en.wikipedia.org/wiki/Sales_taxes_in_the_United_States>
It's a good idea to research the domain before deciding on the representation.
--
Lew
--
Sent via pgsql-general mailin
ligatoire. Étant donné que les email peuvent facilement
être sujets à la manipulation, nous ne pouvons accepter aucune
responsabilité pour le contenu fourni.
Confidentiality disclaimers on messages posted to a public forum are rather
pointless, especially if in languages other than the /lingua
ght hardware and good tuning, PG will happily deal with large volumes
of data; and 300kb a minute isn't really very much by any standards.
You can get a few numbers here: http://www.postgresql.org/about/
I know folks who've successfully worked with multi-terabyte databases with PG.
--
't forget that day length can vary by at least an hour either way from
24 depending on the date and geographic location. Here in the U.S., tomorrow
(November 1, 2009) will be 25 hours long in most, but not all, jurisdictions.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ch someone, but not "valid" to reach the intended
party. Often I'll send a courtesy reply informing the sender that they have
not reached the correct party and advising them to fix their address lists.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
erval can have different signs, so should abs('-1 day 1 hour') be
'1 day -1 hour' or '1 day 1 hour'? Or what about corner cases like
'1 day -25 hours'?
I'm writing this at about 8:35 p.m. New York time on October 31, 2009. From
now, adding interval &#x
r months with 30 days,
adds 1 day for months with 31 days and subtracts 2 days for February.
or 1 day for February, 2012.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
OpenJPA (for Java applications), natural keys are sufficient and far more
straightforward than surrogate keys.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
regardless.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
-time data types.
But I can’t get ‘infinity’ to work for columns of type “date”.
Shoaib Mir wrote:
I don't have version 8.3 with me right now but I just gave it a try with
8.4 and it gave me the expected output:
As Tom Lane points out, that's a difference between 8.3 and 8.4.
--
Le
On 05/23/2010 02:15 AM, rihad wrote:
In this query:
UPDATE foo
SET allocated_to=?
WHERE id=(SELECT MIN(id) FROM foo WHERE allocated_to IS NULL)
AND allocated_to IS NULL
RETURNING id
Isn't the "AND allocated_to IS NULL" clause redundant?
--
Lew
--
Sent via pgsql-general mail
e. Your query lacks an ORDER BY clause. You could therefore get any
order back, including the possibility of different orders from different runs
of the same query.
Tables in a relational database have no inherent order. You took no steps
whatsoever to guarantee the order of rows returned by the S
r the manuals is available as part of the
PostgreSQL source download available in the FTP area."
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
generate pretty much any format you
want, right?
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
in.
If your db contains international text there are some corner cases where
lower( upper( val )) != val or upper( lower( val )) != val. Or there should
be, because that's what happens in certain languages.
For example, upper-case 'ß' should be 'SS' in German. Lower-cas
nt to encourage the use of databases that don't support relational
integrity?
Really?
I think that is a simply terrible idea.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
te to the lowest common denominator, particularly
as such an LCD is nonexistent.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
attributes of the google groups email system.
Thomas Kellerer wrote:
I read it through the gmane newsreader, so I get threaded display and
can easily "scan" the subjects.
Ditto Thunderbird's news reader.
Google Groups is not a very good news reader.
--
Lew
--
Sent via pgsql-ge
will no longer be at the same address, if anywhere.
That's because a C pointer doesn't represent a struct, or anything else other
than an address. It *points to* the struct.
You need to serialize the struct itself then allocate the pointer when you
deserialize the struct.
--
Lew
Please do not top-post.
On 08/05/2010 09:42 AM, Ketema Harris wrote:
Never kill -9. Use kill -INT, whatever signal num that is, 11?
"man kill"
INT2
SEGV 11
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscrip
maybe with a little extra just in case the future brings more.
It's a question of what represents the data most accurately and completely.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
in my newsreader.
Who wrote the message you quoted (you failed to cite the source)?
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
PDATE statement?
<http://www.postgresql.org/docs/8.3/static/sql-update.html>
does not list an INTO clause for UPDATE, and when you think about it, indeed
such a clause doesn't make sense.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
iplier for memory units is
1024, not 1000.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
soning about the application domain, not lucky
guesses about a limited sample of inputs.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
BY md5cnt DESC;
Use HAVING instead of WHERE.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
of the
PDF-reader "Print" action on one of their workstations. (Binding extra.)
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Martin Gainty wrote:
could you provide a brief explanation of EAV ?
Please avoid HTML and eschew top-posting. The post from Jeff Soules in this
thread included the advice:
See e.g. http://en.wikipedia.org/wiki/Entity-Attribute-Value_model
which points to an explanation.
--
Lew
--
Sent
aling with unusually
large numbers of connections and/or unusually large working sets, I'm guessing
as you approach terabyte-scale dbs and up, it pays to go to even larger
shared_buffers and work_mem and do other arcane tuning magic.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general
igDecimal.
Are you saying that the 'scale' of the returned values differs between the
JDBC for Oracle and that for PG?
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
but as an indicative one. The actual license offered by the actual
copyright holder always trumps. Anyone who disbelieves the official site in
favor of Wikipedia has a fool as a researcher and a bigger one as a client.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To ma
specially concurrent activity,
it's impossible to know whether the cost outweighs the benefit.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
precedence.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
supertype to either of those is 'Object', clearly
not what we're looking for here.)
For a Java 'PreparedStatement' you can set a parameter to a SQL ARRAY value
only if it's of type 'java.sql.Array' but I don't know offhand how to
construct such an ob
e licensing problems.
In at least some jurisdictions, if one party to a contract writes the language
without input or emendation from the other party, that allows the other party
to impose any reasonable interpretation on the wording. IOW, ambiguity is
resolved in favor of the party who had no choic
further
effort on the programmer's or even the deployer's part.
So to what coding changes do you refer?
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
y with a mediating application to vet the access.
There certainly are dangers to letting the world in to your network. There
are a lot of ways to mitigate the risk. A firewall blockade in conjunction
with pg_hba.conf rules is one standard, relatively simple and fairly effective
tactic.
--
y respond in kind is, well, silly.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ult.getTime()'? How exactly are you
determining that its value is "off" by one hour? Can you show us Java code?
Is this a flaw in the JDBC driver or is that the expected behavior? In
either case I do now have a workaround but would like to know.
It is not a flaw in the JDBC dr
n
projects that tried to use it, and while that didn't make me an expert on the
matter by any means, it gave me some cause to trust Mr. Celko's opinion on the
matter.
--
Lew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ion, yielding "accounts" and
"accountsaudit" tables.
I am not yet convinced that I have the answers on this matter.
-- Lew
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
h a function, though, why don't you write one?
I recommend sticking with the SQL semantics instead of trying to change them.
-- Lew
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
1 - 100 of 107 matches
Mail list logo
