[GENERAL] Deadlocks On Demand

such conditions, and if so, what they are. One possibility I've come up with is to finish up the concurrent psql proposal that's been floating around for some time. http://archives.postgresql.org/pgsql-patches/2007-05/msg00112.php What say? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org

Re: [GENERAL] set-level update fails with unique constraint violation

; are there any plans to make this work? This will work in 8.5: CREATE TABLE x ( i int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED ); INSERT INTO x (i) VALUES (1), (2), (3); UPDATE x SET i = i + 1; Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666

Re: [GENERAL] Table Partitioning Advice Request

, and please do trim, and don't top-post. I've fix this in this post. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people

Re: [GENERAL] Tuesday (PST8PDT) Jeff Davis Presents: Operator Exclusion Constraints

On Tue, Dec 08, 2009 at 09:02:41AM +0100, Guillaume Lelarge wrote: Hi David, Le mardi 8 décembre 2009 à 01:46:00, David Fetter a écrit : Folks, If you can't make it to the SFPUG meeting in person on Tuesday, December 8, 2009 at EZRez http://postgresql.meetup.com/1/calendar/11928447

[GENERAL] Tuesday (PST8PDT) Jeff Davis Presents: Operator Exclusion Constraints

://irc.freenode.net/sfpug Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider

Re: [GENERAL] [BUGS] BUG #5211: invalid password

, restart the server, use the ALTER USER command to change the password to something else, change pg_hba.conf back, then restart the server again. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP

Re: [GENERAL] Backup Postgre server

in the crash detection system? In broad generality, a sensor attached directly to an actuator is a booby trap, and this case is no exception. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP

Re: [GENERAL] passing parameters to multiple statements

://people.planetpostgresql.org/dfetter/index.php?/archives/33-Portability-Part-II.html Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical

Re: [GENERAL] Editor for sgml files

On Tue, Nov 10, 2009 at 06:23:16AM -0800, Richard Broersma wrote: On Mon, Nov 9, 2009 at 10:34 PM, David Fetter da...@fetter.org wrote: You might be, but that's approximately what happened.  Common editors for this task are Emacs and Vim, each of which has varying levels of support

Re: [GENERAL] Editor for sgml files

approximately what happened. Common editors for this task are Emacs and Vim, each of which has varying levels of support for SGML :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet

Re: [GENERAL] How to import data from Oracle and SqlServer to a Postgres database?

to the remote database and allows you to import data from it. Sorry for my english. I assure you, my Spanish is much worse, and my Portuguese is purely a caution ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype

Re: [GENERAL] Windowing functions - future plans

list regarding the framing clause (Implement full support for window framing clauses) but the explanation did not mention allowing a number instead of UNBOUND for the frame definition. Oracle and DB2 do support this syntax ;) Patches welcome :) Cheers, David. -- David Fetter da

Re: [GENERAL] Rewriting select statements

might have to try some sort of proxy arrangement. This is not your problem. Treating it as though it were your problem will only lead to enormous pain to you, and will not actually solve the problem. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM

Re: [GENERAL] auto-filling a field on insert

. Triggers? Copy ... From? Table or field definitions? Instead of populating your table with this extra information, create a VIEW with what you want in it. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter

[GENERAL] Re: [sfpug] 10/13 SFPUG meeting, The Mighty GUCS, video now available

him and those services as though they were something you were owed. You are not. The media will get posted when it gets posted, and some gratitude is in order. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype

Re: [GENERAL] Free Tool to design Postgres Databases

details--private methods--rather than looking at the much higher level of abstraction that really concerns it. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember

Re: [GENERAL] Free Tool to design Postgres Databases

, eraser, closed room. 4. Lather, rinse, repeat. 5. psql + $EDITOR 6. Lather, rinse, repeat. 7. $SCM 8. Lather, rinse, repeat. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP

Re: [GENERAL] Urgent Help required

most urgent priority is creating an upgrade strategy and integrating it into your development and deployment processes. I hope you have good backups. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter

Re: [GENERAL] Nested transactions

the sub-commits to roll back, use SAVEPOINTs. If you don't, you'll have to write something in an untrusted PL that uses a separate database connection. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter

Re: [GENERAL] creating a function with another function

an entry in the International Obfuscated Database Programming Contest ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http

Re: [GENERAL] creating a function with another function

. Thanks for giving more context to what you're doing. It's always handy to have some of that :) -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating

Re: [GENERAL] Renaming constraints

On Thu, Oct 08, 2009 at 08:24:06PM +0200, Andreas Kalsch wrote: How do I rename constraints? Renaming columns will not rename constraints. BEGIN; ALTER TABLE foo DROP CONSTRAINT bar; ALTER TABLE foo ADD CONSTRAINT bluf...; COMMIT; Cheers, David. -- David Fetter da...@fetter.org http

Re: [GENERAL] Wishlist of PL/Perl Enhancements for PostgreSQL 8.5

functions (where multiple functions have the same name but different arguments). The names won’t enable inter-function calling Inter-function calling could be handy, too. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter

Re: [GENERAL] Wishlist of PL/Perl Enhancements for PostgreSQL 8.5

On Tue, Oct 06, 2009 at 09:34:52AM -0400, Alvaro Herrera wrote: David Fetter wrote: On Tue, Oct 06, 2009 at 09:57:39AM +0100, Tim Bunce wrote: * Enable configuration of perl at initialization Add ability to specify in postgresql.conf some code to be run when a perl interpreter

Re: [GENERAL] Compilate problems

repository. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql

Re: [GENERAL] Compilate problems

, David -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate #include postgresql/8.3/server/postgres.h

Re: [GENERAL] Time Management - Training Seminar in Cape Town

wasting their time in the first place. LOL! Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org

Re: [GENERAL] generic modelling of data models; enforcing constraints dynamically...

to a data model is not a reason for you to prop up their failure with a technological fix that you know from the outset can't be made to work. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP

Re: [GENERAL] generic modelling of data models; enforcing constraints dynamically...

them together again, etc. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: [GENERAL] Advance SQL subquery

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) ORDER BY date; Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating

Re: [GENERAL] Return 30% of results?

are complicated and bug-prone. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent

Re: [GENERAL] Return 30% of results?

Oracle, not PostgreSQL. Are you really trying to help here? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http

Re: [GENERAL] Unicode normalization

Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general

Re: [GENERAL] Non-check constraint def for a static list ?

, revoking all write permissions, and as a backstop, adding RULEs that say DO INSTEAD NOTHING for data-changing operations, which would then require that someone changing it have DDL permission, a much higher bar. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778

Re: [GENERAL] postgresql.key secure storage

something someone else has written, as rootkits and other malware amply demonstrate. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating

Re: [GENERAL] Order By Date Question

is to ORDER BY date_trunc('day', Event_Date), Name Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http

Re: [GENERAL] [sfpug] Statistics and PostgreSQL: Streaming Webcast tonight

On Tue, Sep 08, 2009 at 10:30:21AM -0700, David Fetter wrote: Folks, For those of you who can't attend in person, we'll be streaming audio and video and having a chat for tonight's SFPUG meeting on how the planner uses statistics. Video: http://media.postgresql.org/sfpug/streaming

[GENERAL] Statistics and PostgreSQL: Streaming Webcast tonight

Folks, For those of you who can't attend in person, we'll be streaming audio and video and having a chat for tonight's SFPUG meeting on how the planner uses statistics. Video: http://media.postgresql.org/sfpug/streaming Chat: irc://irc.freenode.net/sfpug Cheers, David. -- David Fetter da

Re: [GENERAL] [sfpug] Statistics and PostgreSQL: Streaming Webcast tonight

On Tue, Sep 08, 2009 at 10:32:53AM -0700, David Fetter wrote: On Tue, Sep 08, 2009 at 10:30:21AM -0700, David Fetter wrote: Folks, For those of you who can't attend in person, we'll be streaming audio and video and having a chat for tonight's SFPUG meeting on how the planner uses

Re: [GENERAL] where clause question

. This won't be the last time it will get in your way. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http

Re: [GENERAL] [Q] optmizing postgres for 'single client' / many small queries

they (too simplistically, much of the time) think of as the persistence layer. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres

Re: [GENERAL] column level, uid based authorization to update columns

. http://www.postgresql.org/docs/current/static/sql-grant.html Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http

Re: [GENERAL] Regex substring help

','(^.*?)br.*$'); Note the '?' after the '*'. That makes it non-greedy. Another way to do this would be with string_to_array: SELECT (string_to_array('onebrtwobrthree','br'))[1]; Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo

Re: [GENERAL] [Q] optmizing postgres for 'single client' / many small queries

. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql

Re: [GENERAL] Add a serial column to a table based on a sort clause

numbers. Random is how you should think of them. Sequences guarantee only uniqueness. Neither order nor gap-less numbers, nor any other property apply to them. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype

Re: [GENERAL] Connecting to Teradata via Postgresql

of examples. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general

Re: [GENERAL] $Body$

. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] Using WITH queries on VIEWs

-only (for now) row-returning query can, so top-level SELECTs, sub-selects, etc. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating

Re: [GENERAL] $Body$

this will not be needed in the dolar-quoted string? Dollar quoting was specifically invented so that people would not have to use torrents of backslashes and/or single quotes. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo

Re: [GENERAL] Query and the number of row result

. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] Query and the number of row result

On Sun, Aug 30, 2009 at 07:35:42PM -0700, bilal ghayyad wrote: Postgresql. Is there alot of Postgresql? How can I know mine? Are you connecting from C? PHP? Perl? Python? Ruby? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666

Re: [GENERAL] How to simulate crashes of PostgreSQL?

7.0 came out. zero server crashes. In my experience, OS crashes are much more common than PostgreSQL crashes. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com

Re: [GENERAL] Figuring out relationships between tables.

, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list

Re: [GENERAL] New database or New Schema?

contemplating queries that touched both that database and others you already have, it becomes much easier: use a schema. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember

Re: [GENERAL] Postgre RAISE NOTICE and PHP

. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing

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

On Mon, Aug 17, 2009 at 07:50:14AM +0200, Pavel Stehule wrote: 2009/8/17 David Fetter da...@fetter.org: On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote: Hello 2009/8/16 Andre Lopes lopes80an...@gmail.com: Hi, I need a plpgsql function to validade e-mail addresses. I

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

be down, this function is not in fact immutable, as it will fail on data that it passed before. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider

Re: [GENERAL] Accessing a database form another database

, then one for WorldMap. http://www.postgresql.org/docs/current/static/sql-createschema.html Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider

Re: [GENERAL] JOIN a UNION

On Thu, Aug 06, 2009 at 08:33:57PM +0200, Pavel Stehule wrote: Hello SELECT ... FROM (SELECT ... FROM A UNION ALL SELECT FROM B) s1 JOIN C IN C.z = s1.z; That last line should read: JOIN C ON C.z = s1.z; Cheers, David. -- David Fetter da...@fetter.org http://fetter.org

Re: [GENERAL] Refer to another database

simply created an own schema for every database with the same name, but it still does not work. Is there anything plain and simple? SELECT f.one, b.two FROM one_schema.foo AS f JOIN other_schema.bar AS b ON (f.id = b.foo_id) WHERE... Cheers, David. -- David Fetter da...@fetter.org http

Re: [GENERAL] Can I add a super table to existing tables?

On Sat, Aug 01, 2009 at 06:28:54PM -0700, Jun Yang wrote: Hi all, I want to add some common columns to all of my tables. Your case may be very special, but offhand, this sounds like a very bad idea. What task is it you're trying to accomplish? Cheers, David. -- David Fetter da

Re: [GENERAL] Can I add a super table to existing tables?

On Sat, Aug 01, 2009 at 08:10:02PM -0700, Jun Yang wrote: On Sat, Aug 1, 2009 at 7:53 PM, David Fetter da...@fetter.org wrote: On Sat, Aug 01, 2009 at 06:28:54PM -0700, Jun Yang wrote: Hi all, I want to add some common columns to all of my tables. Your case may be very special

Re: [GENERAL] Video available for PGDay SJC '09

box and FF 3.5 I can't. You can use xine on your Linux box :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http

Re: [GENERAL] PostgreSQL-License

you would lose the rights to your license to use the code would be by violating the terms of that license. http://www.postgresql.org/about/licence.html Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter

Re: [GENERAL] Request for features

favorably toward setting your ideas at a higher priority, even if some of them don't fly :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider

Re: [GENERAL] Start With... Connect By?

. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql

[GENERAL] Re: [BUGS] BUG #4916: wish: more statistical functions (median, percentiles etc)

enough using count(1) order by count desc]. You can get that with windowing functions, too. :) According to google, this has been a wish since at least year 2000 for various people, but doesn't seem to be implemented. Patches are welcome :) Cheers, David. -- David Fetter da...@fetter.org http

Re: [GENERAL] combine multiple row values in to one row

create a similar aggregate with CREATE AGGREGATE. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org

Re: [GENERAL] combine multiple row values in to one row

On Tue, Jul 07, 2009 at 08:40:06AM -0700, David Fetter wrote: On Tue, Jul 07, 2009 at 01:59:35AM +0430, Lee Harr wrote: Is there a generic way to do this? An aggregate maybe? The aggregate is called array_agg() and it's in 8.4. You can then wrap array_to_string() around it and get

Re: [GENERAL] Q: data modeling with inheritance

subclasses). So, how would you model this data? Do I ditch inheritance? Thanks, Reece -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- David Fetter da...@fetter.org http

Re: [GENERAL] Q: data modeling with inheritance

On Fri, Jul 03, 2009 at 05:37:20PM -0700, Reece Hart wrote: On Fri, 2009-07-03 at 11:29 -0700, David Fetter wrote: I'm missing what you're doing here that foreign keys don't cover. Could you send along your DDL? No DDL yet... I'm just in the thinking stages. FKs technically would do

Re: [GENERAL]

On Tue, Jun 30, 2009 at 10:22:23AM -0700, Erik Jones wrote: postgres=# select null = null; ?column? -- (1 row) Actually, it's NULL. shac...@postgres:5432=# SELECT (NULL = NULL) IS NULL; ?column? -- t (1 row) Cheers, David. -- David Fetter da...@fetter.org http

Re: [GENERAL] Replication and coding good practices

On Mon, Jun 29, 2009 at 07:11:43PM +0800, Craig Ringer wrote: On Sun, 2009-06-28 at 09:01 -0700, David Fetter wrote: Are there any rules of thumb to consider for making an application easier to work with a general replication solution? The applications I mostly deal with are e

Re: [GENERAL] Replication and coding good practices

allow them in files which track in your SCM system. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http

Re: [GENERAL] Optimal values for 500 connections

G2dZFIB7BzU3bWcGBhikQaE= =YSCi -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM

Re: [GENERAL] masking the code

to expose this to clients, don't ship it to them. If you wish to restrict clients' use of what you do ship them, the appropriate place to do so is in the license and/or contract. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo

Re: [GENERAL] horizontal sharding

hiring one of the PostgreSQL consulting outfits like Command Prompt, Endpoint, OmniTI, or the one I work for, PostgreSQL Experts http://www.pgexperts.com/ Cheers, David. On Mon, Jun 15, 2009 at 8:07 AM, David Fetter da...@fetter.org wrote: On Mon, Jun 15, 2009 at 12:32:15AM -0700, mobiledream

Re: [GENERAL] [BUGS] Integrity check

in the first place. The existence of integrity checking tools means the DBMS is done with high incompetence. * how do we confirm that dump file is proper data? See above. * do you any doc to check the integrity of psql db? See above. Cheers, David. -- David Fetter da...@fetter.org http

Re: [GENERAL] [BUGS] Integrity check

with the premises underlying a question, and I believe this is one of those times. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating

Re: [GENERAL] [BUGS] Integrity check

thing of trying to figure out whether there's a way they can push off their work to someone or something else. In this case, they can't. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet

Re: [GENERAL] [BUGS] Integrity check

-beer conference talk... Excellent idea :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org

Re: [GENERAL] default data sort

sort is the name column. How can I have it default to sort by name rather than primary key? Whatever tool you're using for visualization can sort for you. In SQL, tables don't have an ordering, so any ordering is the responsibility of external tools. Cheers, David. -- David Fetter da

Re: [GENERAL] Naming functions with reserved words

*can* do it by double-quoting each. SELECT SELECT FROM FROM WHERE WHERE = =; Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating

Re: [GENERAL] Playing with set returning functions in SELECT list - behaviour intended?

| 1 2 | 2 1 | 3 2 | 1 1 | 2 2 | 3 (6 rows) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org

Re: [GENERAL] Custom Fields Database Architecture

there are, just not until every other option has been exhausted. The amount of maintenance needed for EAV always increases, usually with quite nasty complexity terms, which means you need to budget resources for that maintenance if it turns out you can't do it any other way. Cheers, David. -- David Fetter

Re: [GENERAL] Custom Fields Database Architecture

://archives.postgresql.org/pgsql-general/2008-02/msg00075.php http://decipherinfosys.wordpress.com/2007/01/29/name-value-pair-design/ http://en.wikipedia.org/wiki/Inner-Platform_Effect Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo

Re: [GENERAL] horizontal sharding

be one of the trade-offs mentioned above. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about

Re: [GENERAL] Custom Fields Database Architecture

mention that. The OpenStreetMap people are hitting exactly the issues I named, and they're ruing the day they decided on that schema. I don't recommend that anybody re-make their mistake. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666

Re: [GENERAL] WITH RECURSIVE clause -- all full and partial paths

. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql

Re: [GENERAL] [pgsql-general] Daily digest v1.9081 (14 messages)

)); This creates a unique expressional index on the table. This expression evaluates as true only in cases where the table has at most one row, independently of what the possible row contains. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666

Re: [GENERAL] Sum of multiplied deltas

. Ciao, Gerhard -- http://www.wiesinger.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM

Re: [GENERAL] Using results from DELETE ... RETURNING

. This could look like: WITH t AS ( DELETE FROM foo RETURNING * FROM a ) INSERT INTO foo_audit ... Cheers, David (Yes, I know the spec doesn't have row-changing operations in either part of the above. Yet ;) -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM

Re: [GENERAL] Move PGdata to a different drive

your scripts or PGDATA environment variable. Another way to do this is to mount the drive at the place where PGDATA used to be. The down side of this approach is that it only works if you want your PGDATA at the root of that new drive. Cheers, David. -- David Fetter da...@fetter.org http

Re: [GENERAL] Pl/java in 8.4 bet1 sources compilation failed

, you can get one of the daily tarballs. Are you *sure* you can't use CVS or git, though? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider

Re: [GENERAL] Aggregate Function to return most common value for a column

. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing

Re: [GENERAL] Aggregate Function to return most common value for a column

(), median() and range(). Thanks! :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about

Re: [GENERAL] Providing an alternative result when there is no result

FROM ... ; IF temp_table IS NULL THEN resp:= 'NO'; ELSE resp:= 'YES'; END IF; SELECT EXISTS (SELECT 1 FROM ); will get you a boolean which can't be NULL. You can either map that to yes/no or return it as is. Hope this helps :) Cheers, David. -- David Fetter da...@fetter.org http

Re: [GENERAL] Luhn algorithm (credit card verify / check) implementation - FIX

digit from a number including a Luhn''s algorithm check digit.'; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415

Re: [GENERAL] limit-offset different result sets with same query

it during devel testing on toy tables ... Good point. It's important not to test only on toy-sized tables for lots and lots of good reasons, scale-dependence of sync scans being a small one. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666

Re: [GENERAL] prepared statements and DBD::Pg

On Fri, May 08, 2009 at 09:44:56AM +0100, Tim Bunce wrote: On Thu, May 07, 2009 at 06:08:12PM -0700, David Fetter wrote: On Fri, May 08, 2009 at 01:02:04AM +0100, Tim Bunce wrote: On Thu, May 07, 2009 at 06:50:11AM -0700, David Fetter wrote: On Thu, May 07, 2009 at 02:31:08PM +0100, Tim

<    1   2   3   4   5   6   >