Re: [GENERAL] Installing on Windows without using msi Installer

t;. http://www.postgresql.org/docs/8.3/static/server-start.html http://www.postgresql.org/docs/8.3/static/app-pg-ctl.html Thomas -- 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] date and time

etterreg_nlreg_id_seq' ), email, session, CURRENT_TIMESTAMP ) There is no SELECT and no semicolon inside the INSERT Thomas - 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, clusters and load-balance

equest is handled by a single postgres process which is spawned by the postmaster upon connection. It has nothing to do with "instances" or high-availibility Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www

Re: [GENERAL] PostgreSQL, clusters and load-balance

Bill Wordsworth wrote on 25.03.2008 21:04: On Tue, Mar 25, 2008 at 2:24 PM, Thomas Kellerer <[EMAIL PROTECTED]> wrote: Bill Wordsworth wrote on 25.03.2008 19:16: When traffic goes up, my webserver creates multiple instances of postgresql.exe. At some basic level, aren't they

Re: [GENERAL] Word boundaries in regular expressions

Ciao, Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] howto set a variable in transaction context

hi list, how can i set a variable, which content is only visible in current transaction? i know the way to create a temp table with create temp table ... on commit drop but i dont like that way. something like | set_config(setting_name, new_value, is_local) is it possible? regards thomas

Re: [GENERAL] howto set a variable in transaction context

thanks, it works A. Kretschmer schrieb: am Thu, dem 10.04.2008, um 13:32:04 +0200 mailte Thomas Markus folgendes: hi list, how can i set a variable, which content is only visible in current transaction? i know the way to create a temp table with create temp table ... on commit drop

Re: [GENERAL] good experience with performance in 8.2 for multi column indexes

Hi, your query cant perform well on 8.1 better use a query like delete from pluext1 using pluext2 where pluext1.plunmbr = pluext2.plunmbr and pluext1.pluexttype = pluext2.pluexttype it should perform much faster. be sure to use indizes regards thomas Michael Enke schrieb: Hi lists, I want

[GENERAL] How is statement level read consistency implemented?

his "test" implemented in Postgres? Does it have a similar concept (SCN) or does WAL imply a completely different way? Thanks in advance Thomas -- 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] How is statement level read consistency implemented?

Pavan Deolasee, 22.04.2008 12:57: On Tue, Apr 22, 2008 at 4:11 PM, Thomas Kellerer <[EMAIL PROTECTED]> wrote: I am interested in the technical details on how PG determines that a block needs to be read from from "some other place than the data block" because another transac

Re: [GENERAL] How is statement level read consistency implemented?

row updated. Oracle's rollback segment can be tuned so that retains data up to a certain age and then it is flushed. If rollback is too small, you can get a "snapshot too old" error. Thanks, this was exactly the answer I was looking for! Cheers Thomas -- Sent via pgsql-gener

Re: [GENERAL] Import German Decimal Numbers

ter. Ciao, Thomas -- Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] changing language of system messages

ge it. i'd just like to have the system/error messages in english... it's a windows 2003 system (english os with German_Switzerland locale). thanks, thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Download HTML documentation?

nload that "sounds" like it contained the pre-built HTML manual. At least no directory or file with "doc" or "manual" in its name. What am I missing? I think it would be a nice idea to add a direkt link on the main documentation page for the HTML download (nex

Re: [GENERAL] Download HTML documentation?

Klint Gore, 20.05.2008 09:39: Thomas Kellerer wrote: Hi, I'm trying to download the HTML version of the manual (8.3.1). It does not seem to be part of the Windows installer any more. Instead the a chm version is included. At http://www.postgresql.org/docs/manuals/ it says the HTML c

Re: [GENERAL] best er modeling tool for postgreSQL

it's OpenSource and free. I have played around with it a bit and it looks quite nice. You can define a schema when creating the DDL http://www.sqlpower.ca/page/architect Printing is a pain though (doesn't remember page settings, but the print out is OK) Regards Thomas -- Sent

[GENERAL] XML2 module and xpath_table

'xmltest', '/team/member/@id|/team/member/@name', 'true') as t(id integer, member_id varchar, member_name varchar) to get the following output member_id member_name 10 Arthur Dent 11 Ford Prefect How would I achieve the same without using the deprecate

Re: [GENERAL] XML2 module and xpath_table

Peter Eisentraut wrote on 24.05.2008 13:35: Am Mittwoch, 21. Mai 2008 schrieb Thomas Kellerer: How would I achieve the same without using the deprecated xml2 module? xpath_table is probably the major piece that is not directly covered by the new system. So until we have a replacement, we

Re: [GENERAL] best er modeling tool for postgreSQL

more feature richt than Power*Architect especially when it comes to a full-blown round-trip engineering and it can reverse engineer a model from SQL scripts (haven't tested that with PG scripts though). I also like the ability to create sub-models which keeps larger models clean. T

Re: [GENERAL] Need Tool to sync databases with 8.3.1

-commands.html#command-schema-diff It writes out the differences as an XML file. I have sample XSLT transformations to create SQL statements out of that XML on my homepage as well: http://www.sql-workbench.net/xslt.html Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] Need to update all entries of one table based on an earlier backup

? This is crucial and yet I | am stumbling over something. Since I am not updating foo_old I am baffled | as to what this messsage really means. I've tried "where foo_old.foo_id = | foo.foo_id ... same message. Ciao, Thomas -- Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de

Re: [GENERAL] limits?

Steve Atkins wrote on 23.06.2008 20:21: > In real use you're unlikely to hit any limits, theoretical or practical, I imagine that the 1GB column-value limit is something that could be reached though. Especially for BLOB (aka bytea) or CLOB (aka text) columns. Thomas -- Sent v

Re: [GENERAL] Please remove me from the list!

through on the OP's emailI was poised to say the same as you until I > realised this. :-) > > They have come through on every other post, however. That's probably because he sent his mail as "multipart/alternative"; it has a plain text and a html part. Not ea

[GENERAL] Authenticate via SSPI/GSSAPI on Windows Server

nd I tried setting up sspi locally (I can't use the server for playground testing). Strange behaviour: now pgAdmin asks me for the password of the user "postgres" again, although it should actually connect as "Thomas". I tried a lot of things, but I can't explain them her

[GENERAL] Re: How to get the size of non fixed-length field from system catalog ?

x27;t find details on how to decode it. Use the information_schema, that is easier: SELECT character_maximum_length FROM information_schema.columns WHERE table_name = 'your_table' AND column_name = 'the_char_column'; http://www.postgresql.org/docs/8.3/static/infoschema-columns.html Thomas

Re: [GENERAL] Data merging problem

be transformed into a SQL script) WbDataDiff[2] can compare the contents (data) of two databases and either run the necessary UPDATE/DELETE/INSERT statements directly or write them as SQL scripts. Especially generating the DELETE http://www.sql-workbench.com Thomas [1] http://www.sql-workb

Re: [GENERAL] What's wrong with this query?

mparison with a NULL value always returns false (and that is not a Postgres speciality). You need to use select * from test where value != '----' or value is null; Thomas -- 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] What's wrong with this query?

he_table WHERE some_column = some_column; boils down to the same behaviour... Regards Thomas -- 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] Replication

Mike Christensen wrote on 23.06.2009 19:37: Does anyone actually have that (any node can go down and the others still replicate amongst themselves?) I think this is what Oracle promises with their RAC technology. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] Replication

Craig Ringer wrote on 24.06.2009 04:07: Thomas Kellerer wrote: Mike Christensen wrote on 23.06.2009 19:37: Does anyone actually have that (any node can go down and the others still replicate amongst themselves?) I think this is what Oracle promises with their RAC technology. Isn't

Re: [GENERAL] Database schema dumper

mands.html#command-schema-report Regards Thomas -- 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] Regex Character-Class

ckslash (\) already has a special meaning in PostgreSQL string literals. To write a pattern constant that contains a backslash, you must write two backslashes in the statement, assuming escape string syntax is used (see Section 4.1.2.1). Ciao, Thomas -- Sent via pgsql-general mailing list (

[GENERAL] Will there be an official pg_migrator release?

Hi, the project page of pg_migrator still shows it as a release candidate. Will there be an official release of it, now that 8.4 is out of the door? Including Windows binaries? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] Will there be an official pg_migrator release?

Bruce Momjian wrote on 05.07.2009 02:13: Will there be an official release of it, now that 8.4 is out of the door? Including Windows binaries? Yes, I expect sometime in the next five days. Great, thanks for the info. Thomas -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] Sugestion a db modele like mysql workbrench

ndows ;-). This is interesing for people needing more options as given in pgAdmin. Do you know something else? Try out Power*Architect: http://www.sqlpower.ca/page/architect Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your sub

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

h the ANSI standard had adopted the CONNECT BY, it's really very elegant) Thomas -- 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] Start With... Connect By?

em as well. Firebird implemented recursive CTEs with Version 2.1 which was released April, 2008, more than a year ago. Which makes it the first OpenSource DBMS to implement them I guess. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your su

Re: [GENERAL] Question]

Martie Krukkeland wrote on 13.07.2009 21:11: If you are using Windows: Windows has the build in: Scheduled-Task (this is the Windows-equivelant of the Unix-Cron). It can be found in the Configuration-Screen. Or simply using the "at" command -- Sent via pgsql-general mailing list (pgsql-ge

Re: [GENERAL] problem with pg_restore?

and free: http://www.pnotepad.org http://www.pspad.com http://notepad-plus.sourceforge.net Commercial (but not expensive) http://www.textpad.com I have edited files with more than 1 characters per line in PNotepad, PSPad and Textpad Thomas -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] How to DB export XML File in PostgreSQL?

nto a (single?) XML file, or do you want to export XML files that stored in the database? Thomas -- 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 in insert statement

hen doing normal DML. Out of curiosity: why do you prefix the table with "tbl"? Don't you know it's a table? Sounds like a strange naming scheme to me. Thomas -- 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] COPY command on windows???

Andreas wrote on 17.07.2009 20:06: Hi, I'd like to read a csv file into PG 8.4. COPY relations FROM E'd:\\relations.csv' CSV HEADER; It throws (translated): ERROR: can't open file >>d:\relations.csv<< for reading file or directory not found Try COPY relations FROM 'd:/relations.csv' CSV HEAD

Re: [GENERAL] comparing NEW and OLD (any good this way?)

Pavel Stehule, 23.07.2009 13:45: Hello http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body regards Pavel Stehule That collection of tips is really nice. Why isn't there a link from the Postgres Wiki to your page? Regards T

Re: [GENERAL] comparing NEW and OLD (any good this way?)

that page and your excellent collection. Regards Thomas -- 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] Copying only incremental records to another DB..

#x27;s not possible to do an "offline-diff") Feel free to contact me if you have any questions (support email address is on the homepage). Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresq

[GENERAL] Using unnest

#x27;; gives me the error argument declared "anyarray" is not an array but type anyarray I'm pretty sure I'm overlooking something obvious with regards to the unnest syntax, but what? Thanks Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chang

Re: [GENERAL] Using unnest

, when I supply a literal like '{1,2,3}'::text[] Thomas -- 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] Transitive Closure and CONNECT BY

Robert James wrote on 26.07.2009 21:35: Anything like CONNECT BY? Or any recommended way of querying hiearchial data? Yes, recursive common table expression (since 8.4) http://www.postgresql.org/docs/current/static/queries-with.html Thomas -- Sent via pgsql-general mailing list (pgsql

Re: [GENERAL] For production: 8.4 or 8.3?

Tory M Blue wrote on 27.07.2009 22:45: And those that have multiple TB's of data, weee another dump and restore upgrade (pt!) Isn't that what pg_migrator is for? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.pos

Re: [GENERAL] selecting all columns but one

do a select * but omit one? You need to list all columns that you want explicitely. Using SELECT * in a production application is considered bad practice anyway Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

[GENERAL] pg_stat_activity undocumented?

Hi, is there a reason why pg_stat_activity is not documented in the chapter "System Catalogs"? Is this not a "offical" view? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresq

Re: [GENERAL] pg_stat_activity undocumented?

Magnus Hagander, 30.07.2009 09:24: On Thu, Jul 30, 2009 at 08:37, Thomas Kellerer wrote: Hi, is there a reason why pg_stat_activity is not documented in the chapter "System Catalogs"? Is this not a "offical" view? It's not a catalog, it's a statistics view.

Re: [GENERAL] pg_stat_activity undocumented?

e a remark in the system catalogs documentation would be helpful. Yes, definitely. At least that's the chapter where I start looking for things like that. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgres

Re: [GENERAL] Can I have a look at your TuningWizard generated config file?

settings that it usually provides. Especially max_fsm_pages max_fsm_relations Which Postgres version are you using? IIRC those settings are not longer valid for 8.4 THomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] export csv file on XP

blem - check that the user as which the server is running has read permissions on that file. I think *write* permission is the approriate ;) Thomas -- 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] Does derby have an embedded Mode like Derby ?

x27;t really need to run an installer and/or create registry entries (for windows). This would then resemble more the Derby network server setup. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/

Re: [GENERAL] Does derby have an embedded Mode like Derby ?

thing". We have e.g. unit test for one of our application that we ran on MySQL just for the fun of it, and it failed miserable due to the different locking behaviour between Postgres and MySQL. I would expect similar problems with heavy transactional tests with any embedded engine as well. Th

[GENERAL] Getting listed on "Community Guide to PostgreSQL GUI Tools"

(as a matter of fact I do most of the DBMS independent development agains my local PG database). I have also seen that some of the listed applications don't seem to be active any longer (PGAccess, Xpg, pginhaler). Wouldn't it make sense to clean up a bit there as well? Cheers Thomas

Re: [GENERAL] Install from Source On Windows - University of Sydney Research

on monster command.com . I don't think there is any difference between .cmd and .bat in any of the NT based Windows versions. At least on my WinXP (and earlier with W2K) double-clicking a .bat file always starts cmd.exe *not* command.com Regards Thomas -- Sent via pgsql-general mailin

Re: [GENERAL] Getting listed on "Community Guide to PostgreSQL GUI Tools"

7;t really apply here :) As far as dead software, I think you should create a section at the end of the page and move dead projects there. How do I get an account to edit the page? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your su

[GENERAL] No download of Windows binaries without registering?

age. I would appreciate it very much if the download link for the "plain" PostgreSQL builds from http://www.enterprisedb.com/products/download.do would lead to the download page directly without the registration screen. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-genera

Re: [GENERAL] No download of Windows binaries without registering?

points to the wrong EDB page somewhere? A sorry, I did not follow the link from the Postgres page, but came from http://www.enterprisedb.com/products/download.do Sorry for the confusion, I didn't realise the link from the postgresql.org site bypassed the registration screen. T

Re: [GENERAL] Select data for current week only

with the format -mm-dd hh:mm:ss . A date column does not have a format :) Thomas -- 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] Select data for current week only

d the year into the where condition: SELECT * FROM the_table WHERE extract(week from the_date_column) = extract(date from current_date) AND extract(year from the_date_column) = extract(year from current_date); but your solution is definitely more elegant ... Thomas -- Sent via pgsql-general mail

[GENERAL] Getting the column to a which a sequence belongs.

27;s "owned" by a column. So far "debugging" psql using the -E option didn't show up anything and I couldn't find any hints in the system catalogs documentation. Is this possible at all? As neither psql nor pgAdmin display this information, I suspect it's not.

Re: [GENERAL] Getting the column to a which a sequence belongs.

ass t, pg_attribute a WHERE c.relkind = 'S' AND d.objid = c.oid AND d.refobjid= t.oid AND (d.refobjid,d.refobjsubid) = (a.attrelid,a.attnum); Ah great, I didn't realize I could use pg_depend for this. Thanks, works like a charm! This is exactly what I was

Re: [GENERAL] GUI to edit a table's content

Gauthier, Dave wrote on 27.08.2009 23:17: Are there any GUI apps out there that can be used to edit (insert, update, delete) table data? SOmething with enough smarts to sniff out constraint violations and report accordingly. Also, ability to eval constraints at commit time inside a transactio

Re: [GENERAL] GUI to edit a table's content

mean with "edit in a GUI". e.g. with Squirrel or SQL Workbench/J you can run "SELECT * FROM my_table" and edit the contents of the result directly. Are you talking about something different? Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] PosgreSQL Service does not Start In Vista

ar users (for a good reason) Thomas -- 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] PosgreSQL Service does not Start In Vista

Inigo Barandiaran, 02.09.2009 14:53: Thanks Thomas!. That sounds very interesting. How can I set privileges for writing in data directory for the postgres user account?. Right click on the directory and choose "Security". Anything after that is off-topic in this list ;) Or is

Re: [GENERAL] PosgreSQL Service does not Start In Vista

Inigo Barandiaran, 03.09.2009 14:47: Finally, I installed posgre database out of "program Files" as Thomas suggested and I gave full privileges to posgre user to data folder and everything works correctly :) Do you know how can I install posgre in "program Files" in Vista?

[GENERAL] Swapped download links for 8.4.1 zip binaries

7;ll get the windows binaries :) Regards Thomas -- 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] help me please with function

I can not find command in postgres - With ... as You need Postgres 8.4 for that: http://www.postgresql.org/docs/current/static/queries-with.html Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [GENERAL] Open source database design tool , alternative to MicroOLDAP

also looks quite promising and a lot more mature than Power*Architect. It's called Open ModelSphere: http://www.modelsphere.org/ Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/

Re: [GENERAL] invalid byte sequence for encoding

time-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS Thomas -- 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] Return 30% of results?

; 0.3 Works for me. Are you sure you are using Postgrs 8.4? (Windowing functions are not available in earlier versions) Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: The password specified does not meet the local or domain policy during install.

password rules you posted apply to your domain and not to your server? You can try to add the user before starting the Postgres installer and verify if adding the user through the usual Windows tools works. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] sync structures

n two databases. The output is an XML file that can easily be transformed into the approriate SQL scripts (a sample XSLT for Postgres is available) http://www.sql-workbench.net Description of the WbSchemaDiff command: http://www.sql-workbench.net/manual/wb-commands.html#command-schema-diff

[GENERAL] Updating row with updating function, bug or feature?

ck -- t (1 row) UPDATE 1 id | locked | accessed ++--- 1 | t | 2 | t | 2009-09-30 15:27:20.497355+02 (2 rows) ROLLBACK == END OUTPUT == Thanks & Regards, Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.

Re: [GENERAL] Updating row with updating function, bug or feature?

On Wed, 2009-09-30 at 10:17 -0400, Tom Lane wrote: > Thomas Jacob writes: > > I've run into some weirdness in PSQL 8.3.8 (Ubuntu 8.04 LTS x86_64 > > package). When I update a row while using a function result > > that updates that very same row in the "WHERE&qu

Re: [GENERAL] Query to find contiguous ranges on a column

plan with a self join and a subselect), but it does sort the whole table which might be a problem. Regards Thomas -- 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] Forms generator ?

for http://www.oncetechnologies.com/newonceradix/index.html Thomas -- 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] Correlated Subquery and calculated column non-functional

ld * sales.unitprice) as turnover, rank() over (partition by manufacturer, brand order by SUM(sales.qtysold * sales.unitprice)) as rank FROM cube_sales.sales INNER JOIN cube_sales.product ON sales.productid = product.productid GROUP BY product.manufacturer, product.brand; (Not te

Re: [GENERAL] sudoku in an sql statement

Merlin Moncure wrote on 31.10.2009 14:32: an oracle guy wrote an sql statement that solves a sudoku puzzle...using an oracle specific feature. Still, it's pretty neat, and an absolute gem of lateral thinking. http://technology.amis.nl/blog/6404/oracle-rdbms-11gr2-solving-a-sudoku-using-recursiv

[GENERAL] Windowing functions - future plans

t 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 ;) Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscrip

Re: [GENERAL] Store images on database ou in external files?

ALUES (42, {$blobfile=c:/temp/postgres.gif}); Regards Thomas [1] http://www.sql-workbench.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Migrate from mysql

Hy folks, I'm trying to migrate a database running on mysql for the famous www.redmine.org from mysql to postgresql. I was looking for ressources and I found this : http://www.olimpiks.ru/2011/03/redmine-mysqlpostgresql-converter.html The process is almost perfect except for the binary datas.

Re: [GENERAL] Call for design: PostgreSQL mugs

On 9/3/2013 6:08 PM, Andreas 'ads' Scherbaum wrote: PostgreSQL folks! We are looking for the next big thing. Actually, it's a bit smaller: a new design for mugs. So far we had big blue elephants, small blue elephants, frosty elephants, white SQL code on black mugs ... Now it's time to design so

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

Merlin Moncure wrote on 12.09.2013 18:37: By the way, for in-place major version upgrade (not dumping DB and import again), MySQL is doing a better job in here. http://www.postgresql.org/docs/9.3/static/pgupgrade.html pgupgrade has nothing to do with this: that's just a tool that does in plac

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

Patrick Dung wrote on 12.09.2013 18:11: For PostgreSQL, it seems I can't find the list (it just say see the Appendix E / release notes). I think it is a plus for PostgreSQL if it has few incompatibilities between major versions. There is such a list in the release notes: http://www.postgresql.

Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

Patrick Dung wrote on 13.09.2013 18:17: The problem of pg_upgrade is that it needed to hold two set of databases data in the server. This is not be desirable (very slow) or possible (space limitation) for database with huge data. For example, if the old version is already using over 50% of the

[GENERAL] Something Weird Going on with VACUUM ANALYZE

quote_ident(tablename); END LOOP; END; $$ LANGUAGE plpgsql; -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and con

Re: [GENERAL] nested partitioning

estimated partitions. However you decide to do it, don't optimize prematurely. Monthly partitions are enough for us and our 130GB table with 250M rows, and that's for a single year. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago I

Re: [GENERAL] nested partitioning

exclusion checks. My guess based on the fact the planner has no concept of ranges aside from their width, is that this is the approach it'll likely take. You can't really teach it that your particular inheritance tree is range constrained per level, so it has to check every rang

Re: [GENERAL] Something Weird Going on with VACUUM ANALYZE

e attempt though. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent

Re: [GENERAL] Something Weird Going on with VACUUM ANALYZE

essage? This all seems a little sketchy. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to

Re: [GENERAL] Something Weird Going on with VACUUM ANALYZE

, if you have a workload which triggers these issues, the workaround is to schedule ANALYZE jobs. Thanks Kevin, that explanation totally makes sense. I can switch to a separate VACUUM and ANALYZE step to avoid this until the fix comes through. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd

Re: [GENERAL] need elegant way to store and query tables with variable headers

Gauthier, Dave wrote on 08.10.2013 20:27: Someone is asking me for a way to architect a model which will store basic table data (columns with names and rows), but the number and name of the columns are both variables. I'll call these "data-tables" here. sounds like the hstore extension could

Re: [GENERAL] can you do rollback in a trigger function?

subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row)" You could also raise an explicit error so the user sees something. To fake a foreign key violation, you'd do: RAISE EXCEPTION foreign_key_violation; So you don't need a rollback anyway. --

[GENERAL] Backup Question

hard links to save space. I can back up a 800GB database in less than 20 minutes a night, or 45 minutes for a non-incremental backup. Without disturbing the primary node. Like I said, I can enable filesystem snapshots to fix this, but it feels like something more obvious is going on. Any ideas?

<    2   3   4   5   6   7   8   9   10   11   >