Re: [GENERAL] array_agg + limit

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

Re: [GENERAL] could you tell me this..?

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

Re: [GENERAL] Insert and Retrieve unsigned char sequences using C

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

Re: [GENERAL] moderninzing/upgrading mail list format

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

Re: [GENERAL] A thought about other open source projects

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

Re: [GENERAL] A thought about other open source projects

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

Re: [GENERAL] Best way to store case-insensitive data?

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

Re: [GENERAL] Cognitive dissonance

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

Re: [GENERAL] Cognitive dissonance

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

Re: [GENERAL] Random Weighted Result Ordering

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

Re: [GENERAL] UPDATE ... RETURNING atomicity

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

Re: [GENERAL] 'Infinity' in date columns?

-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

Re: [GENERAL] Notification of Limited Account Access

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

Re: [GENERAL] Avoiding surrogate keys

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

Re: [GENERAL] Connection Pooling

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

Re: [GENERAL] Licence

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

Re: [GENERAL] typecaste object to array

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

Re: [GENERAL] GROUP BY column alias?

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

Re: [GENERAL] Shall I apply normalization in the following case?

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

Re: [GENERAL] PostgreSQL licence

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

Re: [GENERAL] Mapping Java BigDecimal

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

Re: [GENERAL] Config help

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

Re: [GENERAL] Absolute value of intervals

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

Re: [GENERAL] Absolute value of intervals

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

Re: [GENERAL] Emal reg expression

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

Re: [GENERAL] Procedure for feature requests?

'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

Re: [GENERAL] is postgres a good solution for billion record data.. what about mySQL?

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. --

Re: [GENERAL] interface for "non-SQL people"

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

Re: [GENERAL] Best data type to use for sales tax percent

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

Re: [GENERAL] What is the difference of foreign key?

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. -

Re: [GENERAL] Storage of Foreign Keys

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

Re: [GENERAL] ETL software and training

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

Re: [GENERAL] plpgsql function to validate e-mail

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

Re: [GENERAL] Generating random unique alphanumeric IDs

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

Re: [GENERAL] Code tables, conditional foreign keys?

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

Re: [GENERAL] Daylight saving time question

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

Re: [GENERAL] I don't want to back up index files

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

Re: [GENERAL] open up firewall from "anywhere" to postgres ports?

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. --

Re: [GENERAL] Need schema design advice

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

Re: [GENERAL] Oracle and Postgresql

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

Re: [GENERAL] DUPS in tables columns ERROR: column ". . . " does not exist

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

Re: [GENERAL] ERROR: relation . . . does not exist

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

Re: [GENERAL] integer values in conf file

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

Re: [GENERAL] can't get UPDATE ... RETURNING ... INTO ... to compile successfully

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

[GENERAL] [OT] newsreader issue? (Was: bytea encode performance issues)

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

Re: [GENERAL] Quick way to alter a column type?

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

Re: [GENERAL] timestamp with time zone output incorrect

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

Re: [GENERAL] reindexing

t obsolete products for free, and that these organizations should upgrade. -- Lew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] reindexing

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

Re: [GENERAL] Auto incrementing primary keys

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

Re: [GENERAL] "advanced" database design (long)

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

Re: [GENERAL] 8.2.4 serious slowdown

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

Re: [GENERAL] Postgresql 8.2.4 on linux-sparc problem

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

Re: [GENERAL] Postgresql 8.2.4 on linux-sparc problem

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

Re: [GENERAL] Need efficient way to do comparison with NULL as an option

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

Re: [GENERAL] top posting

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)--

Re: [GENERAL] Hijack!

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 ---(

Re: [GENERAL] Hijack!

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

Re: [GENERAL] Hijack!

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/

Re: [GENERAL] top posting

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

Re: [GENERAL] Hijack!

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

Re: [GENERAL] top posting

at was the problem with my post? -- Lew ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] Simple math statement - problem

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

Re: [GENERAL] Simple math statement - problem

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

Re: [GENERAL] \copy ... with null as '' csv doesn't get nulls

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

Re: [GENERAL] Error while compiling PostgreSQL with Java

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

Re: [GENERAL] Error while compiling PostgreSQL with Java

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

Re: [GENERAL] Why LIMIT and OFFSET are commutative

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

Re: [GENERAL] Why LIMIT and OFFSET are commutative

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

Re: [GENERAL] Why LIMIT and OFFSET are commutative

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

Re: [GENERAL] \copy ... with null as '' csv doesn't get nulls

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

Re: [GENERAL] Error while compiling PostgreSQL with Java

;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

Re: [GENERAL] Query Performance Test

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 -

Re: [GENERAL] update record with two-column primary key

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 -

Re: [GENERAL] float to int

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

Re: [GENERAL] md5() sorting

, 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

Re: [GENERAL] What makes a Postgres DBA?

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

Re: [GENERAL] young guy wanting (Postgres DBA) ammo

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

Re: [GENERAL] young guy wanting (Postgres DBA) ammo

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

Re: [GENERAL] Trigger Procedure Error: NEW used in query that is not in a rule

'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)-

Re: [GENERAL] Compound Indexes

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

Re: [GENERAL] Bytea question with \208

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

[GENERAL] Re: Best practice for: ERROR: invalid byte sequence for encoding "UTF8"

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

Re: [GENERAL] Writing most code in Stored Procedures

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

Re: [GENERAL] query to match '\N'

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

Re: [GENERAL] Foreign key constraint question

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)--

Re: [GENERAL] CASE in ORDER BY clause

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

Re: [GENERAL] NULLS and User Input WAS Re: multimaster

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

Re: [GENERAL] There can be only one! How to avoid the "highlander-problem".

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

Re: [GENERAL] Faster data type for one-length values

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

Re: [GENERAL] High-availability

aged PG fans: I prefer Postgre, I really do.) -- Lew ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] Delete with subquery deleting all records

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

Re: [GENERAL] Multiple customers sharing one database?

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/

Re: [GENERAL] High-availability

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

Re: [GENERAL] problems with SELECT query results

-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/

Re: [GENERAL] optimisation for a table with frequently used query

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

Re: [GENERAL] Delete with subquery deleting all records

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

Re: [GENERAL] Integrity on large sites

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

Re: [GENERAL] are foreign keys realized as indexes?

ns (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint. So they are "the same" in pg, and you don't syntactically need a NOT NULL constraint on the column(s) involved. -- Lew ---(end of broadcast)---

Re: [GENERAL] are foreign keys realized as indexes?

of information. Just use the syntax that best expresses your structure: PRIMARY KEY and FOREIGN KEY. -- Lew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

  1   2   >