Re: [GENERAL] Why is there no object create date is the catalogs?
Yep. Still "created" once - instantiated repeated times, but "created" once. Try federated metadata records only one "original creation date" which is an explicit attribute of a record. Last copied, updated, edited are different. Creation date can be when first entered into a spreadsheet, or written down... insert date pertains to "creation of the record as a database tuple", etc... A replica can be copied - but that is a date this instance was created, not the original record. One question - does an edit explicitly destroy the original object and create a new (child? linked?) object, or a modified version of the original? Answer "yeah/nah" - whichever you decide is correct for your use case - there no universal yes or no answer. The real issue is confusion about what "created" means - for data audit tracking/provenance, etc - very important in best practice data mgmt in many domains - all these are dates representing different actions which can be defined & maintained - but by the user rather than the system (albeit often by triggers representing local business rules). Postgres has all the tools you need to implement whatever audit trails you need for create (when first written on a piece of paper), inserts, updates/edits, etc... but doing this in a standard way to meet all users needs is a long standing, unsolved & probably unsolvable issue. Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | www.niwa.co.nz<http://www.niwa.co.nz> [NIWA]<http://www.niwa.co.nz> To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems. From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Tom Lane [t...@sss.pgh.pa.us] Sent: Wednesday, May 13, 2015 11:26 AM To: Melvin Davidson Cc: Adrian Klaver; pgsql-general@postgresql.org Subject: Re: [GENERAL] Why is there no object create date is the catalogs? Melvin Davidson writes: > You are over thinking this. An object is only "created" once! Yeah? Would you expect that pg_dump followed by pg_restore would preserve the original creation date? What about pg_upgrade? This has come up many times before, and we've always decided that it was not as simple as it seems at first glance, and that it would be difficult to satisfy all use-cases. Try searching the archives for previous threads. regards, tom lane -- 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] About COPY command (and probably file fdw too)
You can already do that, natively in Linux/Mac & by adding some simple tools to
try & make Windows useful:
cat | grep | psql -d -c "copy ;"
between grep, sed, tr, awk you can do almost any in-line filtering or text
manipulation you are likely to need. Or a bit of Perl/Python...
Brent Wood
Programme leader: Environmental Information Delivery
NIWA
DDI: +64 (4) 3860529
Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington |
www.niwa.co.nz<http://www.niwa.co.nz>
[NIWA]<http://www.niwa.co.nz>
To ensure compliance with legal requirements and to maintain cyber security
standards, NIWA's IT systems are subject to ongoing monitoring, activity
logging and auditing. This monitoring and auditing service may be provided by
third parties. Such third parties can access information transmitted to,
processed by and stored on NIWA's IT systems.
From: pgsql-general-ow...@postgresql.org
on behalf of Nicolas Paris
Sent: Friday, May 22, 2015 8:33 AM
To: Stefan Stefanov
Cc: Forums postgresql
Subject: Re: [GENERAL] About COPY command (and probably file fdw too)
Hi,
To me this would be great. Why not the ability to restrict lines too
COPY stafflist (userid, username, staffid)
FROM 'myfile.txt'
WITH (FORMAT text, DELIMITER E'\t', COLUMNS (1, 2, 7), LINES(2:1000,2000:3000),
ENCODING 'windows-1250')
=> subset of full data.
2015-05-21 22:25 GMT+02:00 Stefan Stefanov
mailto:stefanov...@abv.bg>>:
Hi,
Maybe I need to clarify a little.
The suggested option “[SKIP] COLUMNS ” would contain columns'
positions in the file so that only some of the columns in a text file would be
read into a table.
Example: copy the first, second and seventh columns form myfile.txt into table
"stafflist". myfile.txt has many columns.
COPY stafflist (userid, username, staffid)
FROM 'myfile.txt'
WITH (FORMAT text, DELIMITER E'\t', COLUMNS (1, 2, 7), ENCODING 'windows-1250')
BR, Stefan
Оригинално писмо
От: Nicolas Paris nipari...@gmail.com<mailto:nipari...@gmail.com>
Относно: Re: [GENERAL] About COPY command (and probably file fdw too)
До: Stefan Stefanov mailto:stefanov...@abv.bg>>
Изпратено на: 20.05.2015 23:21
2015-05-20 22:16 GMT+02:00 Stefan Stefanov :
Hi,
I have been using COPY .. FROM a lot these days for reading in tabular data and
it does a very good job. Still there is an inconvenience when a (large) text
file contains more columns than the target table or the columns’ order differs.
I can imagine three ways round and none is really nice -
- mount the file as a foreign table with all the text file’s columns then
insert into the target table a select from the foreign table;
- create an intermediate table with all the text file’s columns, copy into it
from the file then insert into the target table and finally drop the
intermediate table when no more files are expected;
- remove the unneeded columns from the file with a text editor prior to
COPY-ing.
I think that this is happening often in real life and therefore have a
suggestion to add this option “[SKIP] COLUMNS ” to the WITH
clause of COPY .. FROM. It may be very useful in file fdw too.
To be able to re-arrange columns’ order would come as a free bonus for users.
Sincerely,
Stefan Stefanov
Hi,
I guess it already does (from documentation):
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
Then you can order the column_name as the source file has.
Re: [GENERAL] Unique UUID value - PostgreSQL 9.2
Not best practice but perhaps viable...
In the target table add a serial datatype column as part of the unique
constraint.
Do not populate this column explicitly on insert, but have the db do it for
you. It will allocate an incremental (unique) value automatically on insert.
But I think your problem is more fundamental - if you genuinely have duplicate
values in a column - there should not be a unique constraint on it. If it
should be unique, then you should modify your insert data.
Brent Wood
Programme leader: Environmental Information Delivery
NIWA
DDI: +64 (4) 3860529
Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington |
www.niwa.co.nz<http://www.niwa.co.nz>
[NIWA]<http://www.niwa.co.nz>
To ensure compliance with legal requirements and to maintain cyber security
standards, NIWA's IT systems are subject to ongoing monitoring, activity
logging and auditing. This monitoring and auditing service may be provided by
third parties. Such third parties can access information transmitted to,
processed by and stored on NIWA's IT systems.
From: pgsql-general-ow...@postgresql.org
on behalf of drum.lu...@gmail.com
Sent: Tuesday, March 15, 2016 10:56 AM
To: James Keener
Cc: David G. Johnston; Postgres General
Subject: Re: [GENERAL] Unique UUID value - PostgreSQL 9.2
On 15 March 2016 at 10:46, James Keener
mailto:j...@jimkeener.com>> wrote:
Is a uuid a valid value in the application making use of the data? Why can't
you add the column to table b and then import, or use create the uuid in the
import select clause? I'm also having trouble understanding the problem and why
you've discounted the options you've not even told us you've considered.
I want to import data from table A to table B, but when doing it the column
"code" on table B has to have some unique random data.
I could use UUID like:
insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111');
but I'm doing:
INSERT INTO tableb (SELECT * FROM TABLEA)
So, how to use UUID using the SELECT above?
On the target table, I've got a CONSTRAINT:
ALTER TABLE dm.billables
ADD CONSTRAINT uc_billable_code_unique_per_account UNIQUE("account_id",
"code");
So I'm importing a CSV file with repeated values on the field "code"
Example:
'Interpreting Normal/AH'
'Interpreting Normal/AH'
'Interpreting Normal/AH'
'Interpreting Normal/AH'
'Interpreting Normal/AH4'
'Interpreting Normal/AH'
'Interpreting Normal/AH6'
'Interpreting Normal/AH'
So when importing it to the target table I got the error:
ERROR: duplicate key value violates unique constraint
"uc_billable_code_unique_per_account"
DETAIL: Key ("account_id", "code")=(32152, 'Interpreting Normal/AH') already
exists.
Command used to import the values:
INSERT INTO dm.billables (SELECT billable_id, code, info FROM temptable)
OR directly through the CSV file:
COPY dm.billables (code, info, unit_cost, unit_price, account_id) FROM
'/var/lib/pgsql/sql/lucas/charge_test.csv' WITH DELIMITER ',' QUOTE '"' CSV
HEADER;
So. I determined that to do that without dropping the CONSTRAINT, I'll have to
generate a unique but random value to the "code" column.
NOW:
COLUMN CODE| COLUMN INFO
'Interpreting Normal/AH'Travel1
'Interpreting Normal/AH1'trip2
'Interpreting Normal/AH2'test897
'Interpreting Normal/AH3'trip11
'Interpreting Normal/AH4'trave1
NEW:
COLUMN CODE| COLUMN INFO
code_32152563bdc6453645Travel1
code_32152563bdc4566hhhtrip2
code_32152563b654645uuu test897
code_32152563bdc4546uuitrip11
code_32152563bdc4db11aatrave1
How can I do that?
Re: [GENERAL] $foo $bar is BAD
+1 We should be "open" to include various languages, dialect & colloquialisms in documentation... the measure is whether the meaning is clear - foobar has a long history, as do foo & bar, in the communication of ideas. That would mean no jargon, abbreviations, humour, sarcasm, acronyms, etc... If we refused to use any words which had a historical connotation than might offend someone, we might as well forget about documentation altogether. Brent Wood Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | www.niwa.co.nz<http://www.niwa.co.nz> [NIWA]<http://www.niwa.co.nz> To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems. From: pgsql-general-ow...@postgresql.org on behalf of Peter Devoy Sent: Saturday, April 16, 2016 2:00 PM To: Psql_General (E-mail) Subject: Re: [GENERAL] $foo $bar is BAD > Although people commonly use $foo $bar in examples, it is actually a misuse > of a VERY rude acronym. > The next time you need to make an example, please try being a little more > original (or meaningful) with your variable names. In light of recent CoC decisions, I would like to propose the opposite. I think more expletives would diversify the language of the documentation and lower the barriers to contribution by individuals more dispositioned to use colourful language due to their cultural and/or socioeconomic background. O:-) -- 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] Normal distribution et al.?
Jan Danielsson wrote: > Andrej Ricnik-Bay wrote: > > On 6/18/07, Jan Danielsson <[EMAIL PROTECTED]> wrote: > >>UPDATE foo SET value=value+normdistsample(10, 0.2) WHERE id=1; > > Something like this? > > http://www.joeconway.com/plr/ > >That looks too good to be true. > >Many thanks! > See http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01 for a new intro, pretty basic, but a good place to start Brent Wood ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Linux distro
On Wed, 2007-08-01 at 13:29 +0200, [EMAIL PROTECTED] wrote: >> Hello, >> >> I bought a Dell server and I am going to use it for installing PostgrSQL >> 8.2.4. I always used Windows so far and I would like now to install a >> Linux distribution on the new server. Any suggestion on which distribution >> ? Fedora, Ubuntu server, Suse or others? >> >> Thanks in advance, >> Paolo Saudin >> My 02c, I'm pretty promiscuous when it comes to distros, as I want to run applications, not fuss with an OS, so which ever distro works best for me gets used. I've recently played with Ubuntu, Mandriva, Debian, OpenSUSE, SLED, Fedora Core, SImply Mepis & a few others (including running Postgres/PostGIS on them all) I don't think it really matters for Postgresql, most distros will run it fine. If you want a genuine basic server setup, maybe without any GUI, then avoid distros which focus more specifically on desktop ease of use. Perhaps look as BSD? If you want a workstation system, where there needs to be a good mix of desktop & server capabilities, a more generic system is preferable. If you want to set up essentially a desktop system, but run Postgresql on it, then any popular desktop distro will work. While Ubuntu & Mandriva (for example) focus on ease of use, they also have less commonly used server versions. OpenSUSE is the distro I currently prefer, it seems to do all I want better than the others I've tried recently. All the server stuff with a good set of desktop apps. I suggest you look at www.distrowatch.com to see their comments (but remember everyone has different likes & dislikes, so treat any review with caution, as your opinion may vary) HTH, Brent Wood ---(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] Selecting top N percent of records.
Something like this should work - (but is untested), and does require the extra subquery, so there may be a more efficient way? However, off the top of my head: select a,b,c from table where order by c desc limit (select count(*)/10 from table where ); where c is the no of sales column Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Tim Uckun 10/18/10 3:40 PM >>> Is there a way to select the top 10% of the values from a column? For example the top 10% best selling items where number of sales is a column. Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] Database INNOVATION
Have a look at PL/R.
You can embed a command to generate a graphic using R via a user defined SQL
function,
This example from
http://www.varlena.com/GeneralBits/Tidbits/bernier/art_66/graphingWithR.html
HTH
Brent Wood
=
Graphs can be as easy as '123'. Here's an example where two columnsin a table
are plotted against each other.
Create and populate the table using the following commands:
CREATE TABLE temp (x int, y int);
INSERT INTO temp VALUES(4,6);INSERT INTO temp VALUES(8,3);INSERT INTO temp
VALUES(4,7);INSERT INTO temp VALUES(1,5);INSERT INTO temp VALUES(7,8);INSERT
INTO temp VALUES(2,3);INSERT INTO temp VALUES(5,1);INSERT INTO temp VALUES(9,4);
The function f_graph()generates the graph as a pdf document:
CREATE OR REPLACE FUNCTIONf_graph() RETURNS text AS
'str <<- pg.spi.exec(''select x as "my a" ,y as"my b" from temp order by
x,y'');pdf(''/tmp/myplot.pdf'');plot(str,type="l",main="GraphicsDemonstration",sub="Line
Graph");dev.off();print(''done'');'
LANGUAGE plr;
Creating the graph by invoking this query:
SELECT f_graph();
Craig Ringer said:
Now, personally, if we're talking "database innovation" what I'd like to
see is a built-in way to get query results straight from the database as
graphs of tuples and their relationships. Tabular result sets are poorly
suited to some kinds of workloads, including a few increasingly common
ones like document-oriented storage and use via ORMs. In particular, the
way ORMs tend to do multiple LEFT OUTER JOINs and deduplicate the
results or do multiple queries and post-process to form a graph is
wasteful and slow. If Pg had a way to output an object graph (or at
least tree) natively as, say, JSON, that'd be a marvellous option for
some kinds of workloads, and might help the NoSQL folks from whining
quite so much as well ;-)
--
Craig Ringer
Tech-related writing at http://soapyfrogs.blogspot.com/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
Please consider the environment before printing this email.
NIWA is the trading name of the National Institute of Water & Atmospheric
Research Ltd.
Re: [GENERAL] Database INNOVATION
Gotcha. Apologies for the digression, off your exact topic but consistent with the subject :-) I'm interested in both, PL/R & representational graphics from an analytical perspective, doing more than just retrieving raw or accumulated data with SQL. & also from the (mathemetical) graphic perspective to support biological taxonomic trees/heirarchies, which do not easily fit the SQL model, although a number of kludges to traverse such structures are around. (I need to look at the Postgres recursive capability for this sometime) Cheers, Brent Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Craig Ringer 10/20/10 6:12 PM >>> On 10/20/2010 12:35 PM, Brent Wood wrote: > Have a look at PL/R. > > You can embed a command to generate a graphic using R via a user defined > SQL function, In this case, when I say "graph" or "tree" I'm referring to the concept in the graph theory sense, not the "plot" sense. "object graph" not "image representation of data". http://en.wikipedia.org/wiki/Graph_(mathematics) http://en.wikipedia.org/wiki/Graph_theory Sorry, I didn't even think to clarify my usage. What I'm talking about is a way to query the database and obtain a representation of matching tuples where each tuple is represented exactly once, and referential relationships between tuples are included in an efficient way. For a simple tree or forest (ie a directed graph with no cycles) this could be a XML/JSON/YAML/whatever document that uses nesting to represent relationships. For more complex graphs, it'd have to be a list of XML/JSON/YAML/whatever representations of each tuple or (if Pg supported it) multiple tabular result sets, one for each tuple type. An edge list could be included to speed mapping out the inter-object references after deserialization. To say this would be nice when dealing with document-in-database storage and certain types of ORM workload is quite an understatement. Getting rid of all that horrid "multiply left join, filter and de-duplicate" or "n+1 select" crap would be quite lovely. Sure, it's often better to use sane SQL directly, but there are tasks for which ORMs or document-database mappings are a real time and pain saver - it'd just be nice to be able to teach the database their language. Plus, that'd help shut up the "NoSQL" crowd and separate "NoSQL" from "relaxed or no ACID shareded databases", two different things people currently confuse. In any case, thanks for the tip. It's nice to know the PL/R can be used for such in-database processing when I *do* want to plot data. -- Craig Ringer Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] median for postgresql 8.3
Hi Maarten, The best way I know of to do this is not to do statistical queries "in" the DB at all, but use a stats capability embedded in your database, so they still appear to the user to be done in the db. I don't see how you can easily get the functionality you want without user defined functions or addons, While PL/R is a "special addon", and you created a custom median function to do this, there are very good instructions to follow to do this. I think it might be worth your while if you are looking to retrieve stats from SQL queries. See PL/R, and the median how-to at: http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01 HTH, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> maarten 11/17/10 9:15 AM >>> Hello everyone, I was doing some analysis of data to find average delays between some timestamp values etc... When the number of rows the average is computed over is small, this can give distorted values. So I've obviously added a count column to see if the average represents much data. However, I would also like to add the median value to give me a pretty good idea of whats happening even for smaller counts. I couldn't find such an aggregate function in the manual (version 8.3) and some websearching didn't uncover it either. I was thinking about SELECT max(id) FROM test ORDER BY id ASC LIMIT (SELECT count(*)/2 FROM test) But two things are wrong with that: Limit can't use subqueries :( And ORDER BY gives me the error: 'must be used in aggregate function etc...) but I can probably work around this by using an ordered subquery in stead of the table directly. Furthermore, I need the median for a timestamp column, which would probably complicate things more than when it is a number column. I'd like to be able to do this using only the database. (So no programming functions, special addons etc...) Any ideas anyone? regards, Maarten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] techniques for bulk load of spatial data
On 2010-11-30 14.29, Mario Corchero wrote: > Hi, I'm a student of Computer Science, > I know diffrents techniques of bulk load, but I need to know how > specifically postgreSQL make a bulk load of spatial data, could anyone If you are using spatial data in Postgres, this might usefully be addressesd to the Postgis list. Refer to http://www.postgis.org When you say "bulk" loading of spatial data, is this hundreds of thousands or billions of records? Are you needing to include coordinate system/projection info? Have you looked at ogr2ogr or shp2pgsql, or SPIT in QGIS, all of which can lod data into PostGIS, depending on how big a bulk you are talking about. If your spatial data is available in Postgis WKB format, you could generate a file to use with Postgres copy command? Regards, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] Dumping a table from one database and adding it to another
Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> "James B. Byrne" 12/03/10 12:56 PM >>> I have read the documentation respecting backups but I cannot seem to find any mention of the specific case that I wish performed. Hi James, pg_dump can take arguments to dump a specified table, the output can be filtered/edited in a pipe & passed back to psql accessing a different database on the same or another host: You may need to set user & other parameters, but a quick example; to dump a table, rename it & recreate in a different db on another server on the fly: pgdump -h -t| sed 's/src table/target table/g' | psql -h -d < dump named table in specified db>< change all occurences of >< run the SQL in the > original table name to new onetarget db By piping through sed you can also change column names, just as this does the table name. But note that if you have users/tables/columns with the same name, this is not going to be straightforward... HTH, Brent Wood I have a pair of tables in a production database that I wish to dump and then restore to a new, different database. I can, and probably will, recreate the tables and column layouts in the new database. Is there a way to load the data dumped from a single table in one database into a new, possibly differently named, table in a different database, using PG utilities? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] COPY FROM and INSERT INTO rules
>From the 8.3 docs... "Be aware that COPY ignores rules. ... COPY does fire triggers, so you can use it normally if you use the trigger approach." HTH, Brent Wood All, I have a rule written on a temp table which will copy the valuesinserted into it to another table applying a function. The temp tablewill be discarded then. The rules I have written works when I use"Insert into" the temp table. But when I use bulk copy "COPY FROM", therule doesn't get triggered and data is inserted only into the temptable that I created. Is there a way to call a rule when I use "COPY FROM" instead of "INSERTINTO" TIA, Sairam Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] Simple, free PG GUI/query tool wanted
Hi, pgadmin is still an admin tool, NOT a simple user query tool. I'd suggest PG Access as worth a look, but unfortunately it is no longer supported, and I have never tried it with a recent version of Postgres. Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> "Elford,Andrew [Ontario]" 12/15/10 11:05 AM >>> http://www.pgadmin.org/download/windows.php From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Gauthier, Dave Sent: December 14, 2010 1:38 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Simple, free PG GUI/query tool wanted Hi: I'm trying to find a simple, easy-to-use, open (free), Windows based query tool for PG. This would be something for the novice end user, a means to build a query, execute it, export results out to a csv or Excel or something like that. Tools with metadata editors are actually undesirable (they don't need it, will just serve to get the confussed, could give them info that would get them into trouble). GOing through ODBC is undesirable, but understandable if there are no other options. I've looked through the Community_Guide_to_PostgreSQL_GUI_Tools but found nothing that foots that bill (but I may have missed something). I'm looking for something similar to MySQL's "Query Browser" or SQLyog. Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] Web Hosting
Rimu hosting allows you to install whatever you want, including Postgres... which I have done before now. If your project is in support of Open Source software in any way, ask what discount they can offer, they have been pretty generous in that arena. http://rimuhosting.com/ Like many hosting companies, they allow you to install & run Postgres, but do not provide support for it. Although given the technical competencies of their support staff, you may find one of them will be able to help anyway. HTH, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Uwe Schroeder 03/06/11 7:05 PM >>> Godaddy virtual hosting does in fact support postgresql. You have a root account on the virtual server and you can install whatever you want. I run several servers with them and all have postgresql, some virtual, some dedicated servers. Haven't tried their shared servers though, so I can't say anything about those. Hope that helps. PS: for a company that size their customer support isn't too shabby either. Uwe > Gentlemen- > > Go-daddy *claims* to support postgres > http://help.godaddy.com/article/2330 > > YMMV > Martin-- > __ > Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité > > Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene > Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte > Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht > dient lediglich dem Austausch von Informationen und entfaltet keine > rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von > E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message > est confidentiel et peut être privilégié. Si vous n'êtes pas le > destinataire prévu, nous te demandons avec bonté que pour satisfaire > informez l'expéditeur. N'importe quelle diffusion non autorisée ou la > copie de ceci est interdite. Ce message sert à l'information seulement et > n'aura pas n'importe quel effet légalement obligatoire. Étant donné que > les email peuvent facilement être sujets à la manipulation, nous ne > pouvons accepter aucune responsabilité pour le contenu fourni. > > > Date: Sat, 5 Mar 2011 16:40:57 -0800 > > Subject: Re: [GENERAL] Web Hosting > > From: m...@kitchenpc.com > > To: urlu...@gmail.com > > CC: pgsql-general@postgresql.org > > > > On Sat, Mar 5, 2011 at 1:08 PM, matty jones wrote: > > > I already have a domain name but I am looking for a hosting company > > > that I can use PG with. The few I have contacted have said that they > > > support MySQL only and won't give me access to install what I need or > > > they want way to much. I don't need a dedicated host which so far > > > seems the only way this will work, all the companies I have researched > > > so far that offer shared hosting or virtual hosting only use MySQL. I > > > will take care of the setup and everything myself but I have already > > > written my code using PG/PHP and I have no intention of switching. > > > Thanks. > > > > Well there's this list: > > > > http://www.postgresql.org/support/professional_hosting > > > > Also, maybe something like Amazon EC2 if you want your own "box"? I > > think the small instances are even free.. > > > > Mike -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] Postgres 9.1 - Release Theme
I haven't checked to follow this up, but it seems like the sort of announcement one might expect on 1 April. Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Darren Duncan 04/02/11 3:01 PM >>> I was under the impression that QUEL was actually a good language in some ways, and that it was more relational and better than SQL in some ways. http://en.wikipedia.org/wiki/QUEL_query_languages Maybe bringing it back would be a good idea, but as an alternative to SQL rather than a replacement. In any event, QUEL was somewhat similar to SQL. -- Darren Duncan Rajasekhar Yakkali wrote: > "Following a great deal of discussion, I'm pleased to announce that the > PostgreSQL Core team has decided that the major theme for the 9.1 > release, due in 2011, will be 'NoSQL'. > > "... the intention is to remove SQL support from > Postgres, and replace it with a language called 'QUEL'. This will > provide us with the flexibility we need to implement the features of > modern NoSQL databases. With no SQL support there will obviously be > some differences in the query syntax that must be used to access your > data. " > > hmm.. shock it is this shift for 9.1 due in mid 2011 is unexpectedly > soon :) > > Curious to understand as to > > - how this relates to every feature that is provide at the moment based on > RDBMS paradigm. > > ACID compliance, support for the features provided by SQL, referential > integrity, joins, caching etc, .. > > - Also does this shift take into an assumption that all the use cases fit > the likes of data access patterns & usecases similar to facebook/twitter? > or to address the the likes of those ? > > Thanks, > Raj > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] advice sought - general approaches to optimizing queries around "event streams"
We have a similar timeseries database approaching 500m records. We partition the main tables (much like your events) into one year subsets, with a clustered index on timestamp for all but the live year. https://blog.engineyard.com/2013/scaling-postgresql-performance-table-partitioning http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html http://www.postgresql.org/docs/9.3/static/sql-cluster.html As discussed here previously, you can also improve performance using hardware - SSD'd vs spindles. Also note that tablespaces, with indexes on your faster drives & data on slower ones can improve performance. http://www.postgresql.org/docs/9.3/static/manage-ag-tablespaces.html Also make sure your db server is optimised for the database size & hardware configuration - like perhaps alloe fewer concurrent users, but more resources per user, or see what pgtune recommends. Should help your performance, in terms of underlying db efficiency & performance, rather than tweaking your actual queries. Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | www.niwa.co.nz<http://www.niwa.co.nz> [NIWA]<http://www.niwa.co.nz> From: pgsql-general-ow...@postgresql.org on behalf of Jonathan Vanasco Sent: Saturday, September 27, 2014 9:02 AM To: PostgreSQL general Subject: [GENERAL] advice sought - general approaches to optimizing queries around "event streams" I have a growing database with millions of rows that track resources against an event stream. i have a few handfuls of queries that interact with this stream in a variety of ways, and I have managed to drop things down from 70s to 3.5s on full scans and offer .05s partial scans. no matter how i restructure queries, I can't seem to get around a few bottlenecks and I wanted to know if there were any tips/tricks from the community on how to approach them. a simple form of my database would be: -- 1k of create table stream ( id int not null primary key, ) -- 1MM of create table resource ( id int not null primary key, col_a bool, col_b bool, col_c text, ); -- 10MM of create table streamevent ( id int not null, event_timestamp timestamp not null, stream_id int not null references stream(id) ); -- 10MM of create table resource_2_stream_event( resource_id int not null references resource(id), streamevent_id int not null references streamevent(id) ) Everything is running off of indexes; there are no seq scans. I've managed to optimize my queries by avoiding joins against tables, and turning the stream interaction into a subquery or CTE. better performance has come from limiting the number of "stream events" ( which are only the timestamp and resource_id off a joined table ) The bottlenecks I've encountered have primarily been: 1. When interacting with a stream, the ordering of event_timestamp and deduplicating of resources becomes an issue. I've figured out a novel way to work with the most recent events, but distant events are troublesome using no limit, the query takes 3500 ms using a limit of 1, the query takes 320ms using a limit of 1000, the query takes 20ms there is a dedicated index of on event_timestamp (desc) , and it is being used according to the planner... finding all the records is fine; merging-into and sorting the aggregate to handle the deduplication of records in a stream seems to be the issue (either with DISTINCT or max+group_by) 2. I can't figure out an effective way to search for a term against an entire stream (using a tsquery/gin based search) I thought about limiting the query by finding matching resources first, then locking it to an event stream, but: - scanning the entire table for a term takes about 10 seconds on an initial hit. subsequent queries for the same terms end up using the cache, and complete within 20ms. I get better search performance by calculating the event stream, then searching it for matching documents, but I still have the performance issues related to limiting the window of events i didn't include example queries, because I'm more concerned with the general approaches and ideas behind dealing with large data sets than i am with raw SQL right now. i'm hoping someone can enlighten me into looking at new ways to solve these problems. i think i've learned more about postgres/sql in the past 48hour than I h
Re: [GENERAL] synchronize DTAP
Gidday, There was an interesting presentation at the Portland Postgres Users Group meeting in early Sept, from a guy who demo'd a Postgres database mounted as a FUSE filesystem. Not production ready, but with tables manifesting as directories, databases could be synch'ed using filesystem tools like rsynch - which offers intriguing backup & replication possibilities. http://vimeo.com/105493143 the demo of the FUSE functionality starts at 39 minutes into the presentation. Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | www.niwa.co.nz<http://www.niwa.co.nz> [NIWA]<http://www.niwa.co.nz> From: pgsql-general-ow...@postgresql.org on behalf of Willy-Bas Loos Sent: Tuesday, September 30, 2014 8:58 PM To: pgsql-general@postgresql.org Subject: [GENERAL] synchronize DTAP Hi, We have an environment that has a central repository for lookups, which is replicated to several databases, ech for different applications. This has been arranged in a DTAP manner. Sometimes it is necessary to synchronize the lookups of one of the DTAP branches with another. But i can't just overwrite one database with a dump from another branch, as the consumer databases will not follow. What i think i need is a way to compute the differences between two databases that have the same schema, and generate insert/update/delete statements from that. Since this seems as a pretty generic problem, i thought that i should ask around before i start writing my own scripts. Does anyone know of script or application that does this? Cheers -- Willy-Bas Loos
[GENERAL] Postgres char type inconsistency
Looking at the behaviour of char & varchar types, there seems to be an issue.
Can anyone explain this behaviour? Is there a bug of some sort?
According to the docs
http://www.postgresql.org/docs/9.3/static/datatype-character.html)(:
" If the string to be stored is shorter than the declared length, values of
type character will be space-padded; values of type character varying will
simply store the shorter string."
Yet chars are not being padded, in fact they lose trailing spaces which are
retained by varchars. They also return length()'s less than the defined
length... which should not be the case for a padded string as defined in the
documentation.
fish=# create table test(var3 varchar(3),cha3 char(3));
CREATE TABLE
fish=# insert into test values('1','1');
INSERT 0 1
fish=# insert into test values('2 ','2 '); -- one space
INSERT 0 1
fish=# insert into test values('3 ','3 '); --two spaces
INSERT 0 1
fish=# select var3||':' as var3, cha3||':' as char3 from test;
var3 | char3
--+---
1: | 1:
2 : | 2:
3 : | 3:
(3 rows)
test=# select length(var3) as v_lgth, length(cha3) as c_length from test;
v_lgth | c_length
+--
1 |1
2 |1
3 |1
So, in summary, varchar stores whatever feed to it and keeps trailing spaces to
max length, char type will trim off trailing spaces, and stor a string shorter
than the specified length..
Brent Wood
Programme leader: Environmental Information Delivery
NIWA
DDI: +64 (4) 3860529
Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington |
www.niwa.co.nz<http://www.niwa.co.nz>
[NIWA]<http://www.niwa.co.nz>
Re: [GENERAL] Partioning with overlapping and non overlapping constraints
Hi Mark, We have a somewhat similar situation - we have years of continuous data which are managed in Postgis. The tables are partitioned into annual subsets. The previous (static) years' underlying tables have a clustered index on UTC timestamp, the current year table has a conventional index. It works well, with 20 odd partitions to date. An annual basis for partitions may not be ideal in your case, but you have not specified how long it takes for your data to become fixed - or if there is a way the database can tell that records are now static. If there is, a scheduled task which migrates such records from a live to fixed partition would perhaps be appropriate. Organising your data by UTC timestamp may be the simplest approach for you. Cheers Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Melvin Davidson [melvin6...@gmail.com] Sent: Tuesday, February 10, 2015 6:01 AM To: Marc Mamin Cc: Tim Uckun; pgsql-general Subject: Re: [GENERAL] Partioning with overlapping and non overlapping constraints Well, without knowing too much about your application, it certainly sounds like using the metricts_MMDD is the way to go. As for modifying the constraint daily, couldn't you just use where timestamp > current_date - Interval '1 Day' ? On Mon, Feb 9, 2015 at 5:14 AM, Marc Mamin mailto:m.ma...@intershop.de>> wrote: >I have two partitioning questions I am hoping somebody can help me with. > >I have a fairly busy metric(ish) table. It gets a few million records per day, >the data is transactional for a while but then settles down and is used for >analytical purposes later. > >When a metric is reported both the UTC time and the local times are stored >along with the other data belonging to the metric. Don't you have duplicate information within your UTC, location and local_time data ? Maybe you can just attach a timezone to each location... >I want to partition this table to both make it faster to query and also to >spread out the writes. Ideally the partitions would be based on the UTC >timestamp and the sending location. For example > >metrics_location_X_2015_01_01 > >First problem with this approach is that there could be tens of thousands of >locations so this is going to result hundreds of thousands of tables. I know >there are no upper limits to how many tables there are but I am thinking this >might really get me into trouble later. With only a few millions rows per day, weekly or even monthly partitions without regard of locations should be sufficient for older data. It should be possible to partition your hot data differently; But Instead of using one partition per location, you may use a hash/modulo approach to keep the number of partitions in a reasonable count if required at all (This can be helpful: https://github.com/markokr/pghashlib). Here I would avoid to include time information except for the limit between old and hot tables. And depending on the pattern and performance requirement of your analytic queries this may be sufficient (i.e. don't partition on the time at all). With smaller partitions for hot data, it should be quite fast to move them one by one to the old data. I have no experience with the trigger based partitioning of Postgres (we handle partitions logically at the application level), so I'm not sure how difficult this approach is. I suppose that you'll need a function that move data from hot to old partitons and that fix the triggers accordingly. > >Second and possibly more vexing problem is that often the local time is >queried. Ideally I would like to put three constraints on the child tables. >Location id, UTC timestamp and the local time but obviously the local >timestamps would overlap with other locations in the same timezone Even if I >was to only partition by UTC the local timestamps would overlap between tables. > >So the questions in a nutshell are. > >1. Should I be worried about having possibly hundreds of thousands of shards. >2. Is PG smart enough to handle overlapping constraints on table and limit >it's querying to only those tables that have the correct time constraint. If you partition on the UTC time only, you don't have overlapping. When querying on the local time, the planner will consider all partitions, but an additional index or constraint on this column should be sufficient as long as your partition count remains small. regards, Marc Mamin -- Melvin Davidson I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you. [http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif] -- 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] partial time stamp query
Hi Kirk,
We have a (near) real time data database for instrument observations from our
research vessels. All observations (summarised to one minute intervals - the
actual raw data is in netCDF, this database makes for easier access & meets
most users needs) go into a single table, with other tables for metadata about
platforms, instruments, etc. Now approaching 350m records, so reasonably
substantial.
Underlying optimisations include
partitioned readings table, with a separate partition for each year (now 23
years)
clustered index on timestamp for the previous years partitions.
largeish filesystem block size - tested to work well with the clustered index &
small size records)
These generally make a big difference to performance. To address one issue,
much like yours, where some users want hourly data for a year, some want daily
data for 10 years & some want 1 minute data for the last month (& some, no
doubt, want one minute data for 20+ years!) I introduced an integer column
called timer. This value is set according to the time (not date) of each record.
Along the lines of (from memory) :an even no of minutes after the hour is 2, 5
minutes is 4, 10 minutes is 8, 15 minute is 16, 30 minutes is 32, 60 minutes is
64, 6 hourly is 128, 12:00 AM is 256 & 12:00PM is 512. When any timestamp is
in more than one category (eg: 12:00 is all of even, 5, 15m 30m 60 minutes),
the timer value is set to the largest appropriate one.
So a request for:
1 minute data is select from table;
2 minute data is select from table where timer >=2 and timer !=15 and timer
!=4;
hourly data is select from table where timer >=64 and timer != 15 and timer !=
4;
etc
5 & 15 minute add a bit of complexity, but we gave the users what they wanted.
This has worked well for years now, & we have an internal web
(mapserver/openlayers based) application allowing users to visualise & download
their selected data - they choose from an interval pick list & the SQL is
hidden. Some extra enhancements are the automatic collation of lat & lon gps
readings into a Postgis point for each reading record, & the automatic
aggregation of daily points into daily track lines, so the track for any
selected set of dates can easily be displayed on a map (the platforms are
mobile vessels - not fixed sites)
You might adapt some of these ideas for your use case?
Cheers
Brent Wood
Programme leader: Environmental Information Delivery
NIWA
DDI: +64 (4) 3860529
From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org]
on behalf of Kirk Wythers [wythe...@umn.edu]
Sent: Tuesday, February 05, 2013 5:58 AM
To: pgsql-general@postgresql.org
Subject: Fwd: [GENERAL] partial time stamp query
Thanks. That worked great! Now I am trying to aggregate these same fifteen
minute to hourly. I have tried using date_trunk:
date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),
but date_truck only seems to aggriage the timestamp. I thought I could use
AVG(derived_tsoil_fifteen_min_stacked.value)
in combination with date_trunk, but I still get 15 minute values, not the
hourly average from the four 15 minute records.
rowid date_truck time2 site canopy plot variable name value avg
2010-07-07_00:00:00_b4warm_a 2010-07-07 00:00:00 1 2010-07-07 00:00:00 cfc
closed a2 tsoil_sc tsoil_avg1_sc 21.06 21.054659424
2010-07-07_00:15:00_b4warm_a 2010-07-07 00:00:00 1 2010-07-07 00:15:00 cfc
closed a2 tsoil_sc tsoil_avg1_sc 20.96 20.950844727
2010-07-07_00:30:00_b4warm_a 2010-07-07 00:00:00 1 2010-07-07 00:30:00 cfc
closed a2 tsoil_sc tsoil_avg1_sc 20.88 20.871607666
2010-07-07_00:45:00_b4warm_a 2010-07-07 00:00:00 1 2010-07-07 00:45:00 cfc
closed a2 tsoil_sc tsoil_avg1_sc 20.8 20.792370605
2010-07-07_01:00:00_b4warm_a 2010-07-07 01:00:00 1 2010-07-07 01:00:00 cfc
closed a2 tsoil_sc tsoil_avg1_sc 20.72 20.713133545
2010-07-07_01:15:00_b4warm_a 2010-07-07 01:00:00 1 2010-07-07 01:15:00 cfc
closed a2 tsoil_sc tsoil_avg1_sc 20.64 20.633896484
2010-07-07_01:30:00_b4warm_a 2010-07-07 01:00:00 1 2010-07-07 01:30:00 cfc
closed a2 tsoil_sc tsoil_avg1_sc 20.55 20.542370605
2010-07-07_01:45:00_b4warm_a 2010-07-07 01:00:00 1 2010-07-07 01:45:00 cfc
closed a2 tsoil_sc tsoil_avg1_sc 20.47 20.463133545
I was tying to get two records out of this set, with the 'avg" column
representing the mean of the first and last four of each 15 minute records.
Perhaps date_trunk only works for the timestamp?
On Feb 4, 2013, at 8:50 AM, Misa Simic
mailto:misa.si...@gmail.com>> wrote:
WHERE derived_tsoil_fifteen_min_stacked.time2::date = '2010-07-07'::date
On Monday, February 4, 2013, Kirk Wythers wrote:
I am trying to write a query that grabs one particular day from a timestamp
column. The data are ordered in 15 minute chunks like this:
2010-07-07 12:45:00
2010-07-07 13:00:00
2010-07-07
Re: [GENERAL] earthdistance
You might install Postgis to implement very powerful spatial functionality that can easily do what you are asking (plus a whole lot more). http://www.postgis.org Now that v2 installs as a Postgres extension, it is more closely coupled with the underlying database. Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Olivier Chaussavoine [olivier.chaussavo...@gmail.com] Sent: Saturday, August 10, 2013 7:29 AM To: pgsql-general@postgresql.org Subject: [GENERAL] earthdistance I develope a project openbarter that needs to match objects based on a maximum distance between their positions on earth. I saw that the documentation of the extension earthdistance was interesting, but the promise was not in the code. It would be nice to have these functions available independently of sophisticated geographic systems. There is a circle object for flat two dimensional space, but earth deals with spherical caps. It would not be exact but enough to suppose that earth is a sphere and that all dimensions latitude, longitude and distance are in radian. What would need to be done to adapt the circle type to a new type 'spherical cap' that would allow simple geographic indexing? -- Olivier Chaussavoine -- Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] earthdistance
Is there not a precompiled Postgis package you can use? There are a few dependencies, the PROJ.4 libraries you are missing enable projection support, and the package tools automatically manage such dependencies. I know packages are well supported for Debian, Ubuntu/Mint/etc, Suse & Fedora. See: http://trac.osgeo.org/postgis/wiki/UsersWikiInstall Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Olivier Chaussavoine [olivier.chaussavo...@gmail.com] Sent: Saturday, August 10, 2013 10:17 PM To: John R Pierce Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] earthdistance As a simple potential user, I tried to install PostGIS, downloaded all libraries required: proj-4.8.0, gdal-1.10.0,json-c,postgis-2.0.3,geos-3.3.8,libwml2-2.9.0, and tried to build the first library with the simple procedure: ./configure make make install I had a fatal error: make[2]: entrant dans le répertoire « /home/olivier/ob/proj-4.8.0/src » /bin/bash ../libtool --tag=CC --mode=compile gcc -DHAVE_CONFIG_H -I. -DPROJ_LIB=\"/usr/local/share/proj\" -DMUTEX_pthread -g -O2 -MT jniproj.lo -MD -MP -MF .deps/jniproj.Tpo -c -o jniproj.lo jniproj.c libtool: compile: gcc -DHAVE_CONFIG_H -I. -DPROJ_LIB=\"/usr/local/share/proj\" -DMUTEX_pthread -g -O2 -MT jniproj.lo -MD -MP -MF .deps/jniproj.Tpo -c jniproj.c -fPIC -DPIC -o .libs/jniproj.o jniproj.c:52:26: fatal error: org_proj4_PJ.h: No such file or directory compilation terminated. problem out of the scope of this list, and probably not /difficult. Since I look for a simple geographic indexing using imprecise lat,long coordinates that do not deal with precise modeling; that I am afraid of long install procedure, and heavy computations, I also give up. Spacial mysql indexing seems to be included in pre-built packages. What can we do? 2013/8/10 John R Pierce mailto:pie...@hogranch.com>> On 8/9/2013 5:18 PM, Brent Wood wrote: You might install Postgis to implement very powerful spatial functionality that can easily do what you are asking (plus a whole lot more). indeed, PostGIS is the logical answer, but the OP specifically stated he wanted the functionality without 'sophisticated geographic systems'. so I ignored the question. the alternative would be implementing your own spherical geometry functions, and hook them up to GiST indexing, its not that hard, but by the time you got all the functionality you need, you'd be half way to PostGIS, so why fight it? -- john r pierce 37N 122W somewhere on the middle of the left coast -- Olivier Chaussavoine -- Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] Divert triggers on materialized views
Hi Ewen, My advice would be to use table partitions... split your history table up into (maybe annual)? partitions, have these inherited into a parent table, which becomes your new "history table" (perhaps instead of a view?) If times are a common component of a where clause, given the partitions (except for the current one) are static (no new data), you can also apply a clustered index on the static partitions, which reduces disk activity on sequential data reads. We are running a reasonably effective database with approaching a biiliion records & twenty years of data using this approach. There is also a fork of Postgres which is optimised for timeseries data, see: https://www.timescale.com/ Some of their optimisation is to build in automatic partitioning, much as described above. You can use your existing normal view as well, which will only be on the "current" partition, so the overhead of a single large index & table is no longer an issue. Cheers Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | www.niwa.co.nz<http://www.niwa.co.nz> [NIWA]<http://www.niwa.co.nz> To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems. From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Ewen McNeill [pg...@ewen.mcneill.gen.nz] Sent: Wednesday, October 18, 2017 13:34 To: pgsql-general@postgresql.org Subject: [GENERAL] Divert triggers on materialized views PostgreSQL VIEWs have a useful feature where INSTEAD OF triggers can be defined to divert INSERT/DELETE/UPDATE actions into an underlying table (or other location), creating the effect of a "writeable view" (and I believe in more recent PostgreSQL versions this is pretty much automatic). However MATERIALIZED VIEWs are implemented differently (effectively as TABLE storage with a saved SQL query used to REFRESH them), and it appears (at least in 9.5, but AFAICT from documentation through 10) no TRIGGERs at all can be created on MATERIALIZED VIEWs. This makes MATERIALIZED VIEWs difficult to substitute for VIEWs in some instances. In the situation I'm trying to help with: (a) there's a "history" table which has tens of millions of rows of accumulated daily updates; (b) there's a "today view" on the history table which shows the current data via some date range checks on the history table, working out to about 200k rows in the view (c) there's a daily batch task that ingests the latest data, which has a couple of hundred thousand rows of "daily updates", which for legacy reasons is done via (diverted by triggers) actions on the "today view" and they've reported that over time their "dashboards" of reports against the "today view" have become slower, presumably as the underlying history table has grown. Given that almost all of the changes happen in a daily batch update through an easily wrappable process (SFTP, process-on-upload), and the majority of the queries (but not all) are against the current data, turning that "today view" into a MATERIALIZED VIEW seems like an obvious win... except that it breaks the ability to use divert (INSTEAD OF) TRIGGERs which then means a larger application rewrite. Unfortunately the dashboard/reporting front end now has the name of the VIEW hardcoded all over the place (changeable only by one-at-a-time GUI point and click :-( ) so pointing the reporting tool at a different MATERIALIZED VIEW does not seem like a realistic option. Is it likely that TRIGGERs, especially INSTEAD OF triggers, would be supported on MATERIALIZED VIEWs in some later version of PostgreSQL in a similar manner to (non-MATERIALIZED) VIEWs? Ideally 9.6/10, but even being on a todo list would be useful. Alternatively does anyone have any idea of any minimal change rearrangement of TABLEs/VIEWs/TRIGGERs that might achieve the same effect without requiring much of the application to be rewritten? The only one that comes to my mind is to make the "today view" an actual table, with AFTER TRIGGERs on it that mirrors the same action into the "history table" -- which would require recreating all the TRIGGERs, and the VIEWs that hook into that "daily" view, but otherwise superficially seems plausible. It stores the data twice, but so does a MATERIALIZED VIEW, and the dail
[GENERAL] Postgres development
Hi all, I'm interested in finding what would be involved on enhancing Postgres to allow queries run in one database in a cluster to access & join with tables in other databases in the cluster, ie: cross database join support. This would be very useful, & depending on cost, I may be able to arrange for funds to cover this development. Thanks, Brent Wood -- 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] IBM investing in EnterpriseDB
Add Informix to the list of IBM's RDBMS products.. Also note that where Postgres has PistGIC as an OGC compliant geodata extension, IBM already supports this in both DB2 & Informix, so an even higher degree if interoperability is there for geospatial data. Brent Wood >>> Alex Vinogradovs <[EMAIL PROTECTED]> 27/03/08 8:20 AM >>> Shouldn't forget IBM got DB2. Could be they are just seeking additional userbase in opensource market space... On Wed, 2008-03-26 at 12:12 -0700, Ron Mayer wrote: > Clodoaldo wrote: > > ...IBM is investing...What does it mean for Postgresql? > > One cool thing it means is that there are now *two* > companies (thanks again Fujitsu) bigger than > Oracle backing (to some extent) Postgres. > > And now one company bigger than Microsoft. > > Yeah, this doesn't affect the community much. But it > sure comes in useful when your CFO calls you into a > meeting and says "Hey, I just had lunch with > our Microsoft rep and he asked why we're running > some unsupported freeware database." > > Your CFO wouldn't want to run your company on a > database - like Oracle 10i and MySQL and SQLServer - that > are only backed by little (under $50B revenue) guys, would he? > > :-) > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] IBM investing in EnterpriseDB
I need to learn to type!!! try PostGIS (how did that become PistGIC? I have no idea) >>> "Brent Wood" <[EMAIL PROTECTED]> 27/03/08 1:44 PM >>> Add Informix to the list of IBM's RDBMS products.. Also note that where Postgres has PistGIC as an OGC compliant geodata extension, IBM already supports this in both DB2 & Informix, so an even higher degree if interoperability is there for geospatial data. Brent Wood -- 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] Using tables in other PostGreSQL database
I have mixed feelings, I agree that pretty much equivalent functionality CAN be delivered using schemas, but some RDBMS's do not have this restriction. Business cases & preferences do not necessarily follow database design preferences or capabilities, so irrespective of whether a schema approach CAN work, any user is entitled to ask whether an alternative approach is possible. Enough such users & the capability may well be implemented. I am looking to convince a business which does not use schemas, but does use separate databases to move to Postgres & having to shift from this paradigm is an issue for them. They are perfectly entitled to require such an approach, if we want to increase the user base of Postgres, we need to meet more users needs. (simplistic I know, but I think a valid concern). For example, any user may need to be able to create databases, add data & use referentials in a corporate database of lookup records. Using schemas, everyone needs to have create table privileges to the database by default, then this needs to be denied by schema (is this possible???- never tried yet). Cross db queries allow read only access to corporate metadata for data integrity rules to be applied to any database any user creates. Agreed, not a common requirement, but one where schemas are less flexible & less secure. Cheers, Brent Wood >>> "Scott Marlowe" <[EMAIL PROTECTED]> 29/03/08 4:37 AM >>> On Thu, Mar 27, 2008 at 11:29 PM, Swaminathan Saikumar <[EMAIL PROTECTED]> wrote: > I am fairly new to Postgres. However, I have to say that I agree with > Barry's comments. The real problem here is that you are not using the db properly. You should have one db with all these data in it in different schemas. PostgreSQL provides you with the ability to segregate these data via schemas and fine grained (by the table) ACLs. Your refusal to use multiple schemas in one database due to some perceived problem with them all being in the same database is what's causing your issues. Put your data into various schemas in one database and you can then use access control to decide who sees what. -- 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] (FAQ?) JOIN condition - 'WHERE NULL = NULL'
>>> "Ian Sillitoe" <[EMAIL PROTECTED]> 03/04/08 5:49 AM >>>
I'm trying to JOIN two tables (well a table and a resultset from a PL/pgsql
function) where a joining column can be NULL
In a join, no value can be ascribed to a null field, so the equivalence fails.
You can do tests like IS NULL, which strictly speaking is test for meeting a
condition (that of not having any value), not a test for equivalence. As
(simplistically) the condition NULL does equal the condition NULL, (NULL =
NULL) is true.
The simplest approach is perhaps to have a value which does not occur naturally
(like -1), as a substitute for nulls in the relevant columns. I believe this
can be achieved via a view in your case, (pun intended :-), but which may be
less efficient if performance is an issue:
create view depth_v as
select d.id,
d.name,
case when (d.depth1 is null) then -1 else d.depth1 end as depth1,
case when (d.depth2 is null) then -1 else d.depth2 end as depth2,
case when (d.depth3 is null) then -1 else d.depth3 end as depth3,
case when (d.depth4 is null) then -1 else d.depth4 end as depth4,
case when (d.depth5 is null) then -1 else d.depth5 end as depth5
from depth_table d;
You could then join against this view instead of your underlying table, eg:
select c.* from get_cathcode('1.10.8') c JOIN depth_v t USING(depth1, depth2,
depth3, depth4);
The view will not have any NULL values in the depth fields, so the join should
work.
see: http://www.postgresql.org/docs/8.2/static/functions-conditional.html
(Incidentally, if you are storing bathymetry or CTD data, I'd be interested in
seeing your db structures, as I may be doing some work in that area soon :-)
HTH,
Brent Wood
--
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] pg_dump and schemas
Hi Rusty, Try passing the output through a utility like sed, already there under Linux , but versions that work under Windows are available (eg, cygwin) eg, using a pipe: pg_dump -d | sed 's/public/test1/g' > dump.sql or converting a pg_dump output file: pg_dump cat dump.sql | sed 's/public/test1/g' > dump2.sql With tools like these freely available, you don't really need to spend time reinventing them in your database applications. Of course. if you have the "public" schema name used elsewhere in your database, you may need to get a bit creative in your use of sed, but it can pretty well always be made to do this sort of operation. Cheers, Brent Wood >>> Rusty Conover <[EMAIL PROTECTED]> 05/04/08 8:42 AM >>> Hi All, Is there a way to pass a parameter to pg_dump that would make the produced dump be loaded into a different schema rather then the one it is being dumped from? Basically be able to say dump out of public, but write the dump so its restored to say "test1". Thanks, Rusty -- Rusty Conover InfoGears Inc. http://www.infogears.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] pg_dump and schemas
Hi Rusty, In which case can you not include the text around the schema & table creation commands to ensure other instances of "public" do not match the string? I'm not sure exactly what the pg_dump output contains, but you could use something like: cat pg_dump.sql | sed 's/CREATE SCHEMA "public"/CREATE SCHEMA "new_schema"/' | \ sed 's/CREATE TABLE "public"./CREATE TABLE "new_schema"./' > new_schema.sql This should avoid any ambiguity as to which instances of "public" you want changed in the pg_dump sql file. I think adding support for changing schema names in pg_dump would make it unnecessarily complex, as why just schemas? Also rename databases, tables, columns, index names, change comments... I've yet to find something like this I couldn't do with sed, & if there was there is always awk for the truly desparate :-) pg_dump generates the dump, reliably, simply & safely. Any change you want from the original is not, IMHO, the role of a backup program. That should ONLY back up a replica of your data. Make changes afterwards if you like, but a backup program shouldn't modify your data. Just my 02c, & I ain't no Postgres developer, so I'm not speaking for them in this Cheers, Brent Wood Hi Brent, It's not he best solution, because we could have fields containing "public" in their names and sed would happily change those to test1 as well. I'm looking for a safer solution, thats why it should be a part of pg_dump. Rusty On Apr 5, 2008, at 12:41 AM, Brent Wood wrote: > Hi Rusty, > > Try passing the output through a utility like sed, already there > under Linux , but versions that work under Windows are available > (eg, cygwin) > > eg, using a pipe: pg_dump -d | sed 's/public/test1/g' > dump.sql -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG 8.3 review in Linux Mag
You can't read the online article without an account, but the headlines might still be of interest (or you can buy the magazine :-) http://www.linux-mag.com/id/5679 Cheers, Brent Wood -- 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] Temporary Tables and Web Application
Hi Tim, Off the top of my head, from somewhat left field, using filesystems to manage this sort of effect. Would "real" tables in a tablespace defined on a ramdisk meet this need? So the functionality/accessibility of a physical table is provided, along with the performance of a filesystem actually residing in memory. Presumeably viable if you have the memory to spare & know the size of the temp tables won't exceed this. You could also mount a tablespace on a physical disk with a filesystem which has delayed/deferred writes to disk, so that if it is created & deleted quickly enough, it is never actually written to disk, but just generally sits in the cache. Cheers, Brent Wood >>> Bill Moran <[EMAIL PROTECTED]> 06/06/08 8:01 AM >>> In response to Tim Tassonis <[EMAIL PROTECTED]>: > > Bill Moran wrote: > > In response to Tim Tassonis <[EMAIL PROTECTED]>: > > > >> > >> Now, with apache/php in a mpm environment, I have no guarantee that a > >> user will get the same postgresql session for a subsequent request, thus > >> he will not see the temporary table. > >> > >> Is there a way to create temporary tables in another way, so they are > >> visible between sessions, or do I need to create real tables for my > >> purpose? And is the perfomance penalty big for real tables, as they have > >> been written to disk/read from disk? > > > > Build a framework that creates the tables in a special schema, and then > > can access them through any session. Use some method to generate unique > > table names and store the names in the HTTP session. Create some sort > > of garbage collection routines that removes tables when they're no longer > > needed. > > > > The details of exactly how you pull this off are going to depend heavily > > on the rest of your application architecture. > > > > What you describe is what I referred to as "create real tables". I've > done that and it works, but I wondered if there's something similar > built in postgres apart from classical temporary tables. Not that I'm aware of. If you keep the mailing list in the CC, others can answer as well. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Change view definition - do not have to drop it
I believe Postgres only checks the output types & column names for each column in the view. If, as you suggest, you convert these in your view to a standard appropriate datatype, you could then recreate the view with different input column datatypes: eg: in the countries_simpl table, cat is a bigint datatype, gid is an int: bgmaps=# create view v_test as select cat from countries_simpl; CREATE VIEW bgmaps=# create or replace view v_test as select cat::bigint from countries_simpl; CREATE VIEW bgmaps=# create or replace view v_test as select (cat::char)::bigint from countries_simpl; CREATE VIEW bgmaps=# create or replace view v_test as select (cat::int)::bigint from countries_simpl; CREATE VIEW bgmaps=# create or replace view v_test as select cat::char from countries_simpl; ERROR: cannot change data type of view column "cat" bgmaps=# create or replace view v_test as select gid from countries_simpl; ERROR: cannot change name of view column "cat" bgmaps=# create or replace view v_test as select gid::bigint from countries_simpl; ERROR: cannot change name of view column "cat" bgmaps=# create or replace view v_test as select gid::bigint as cat from countries_simpl; CREATE VIEW HTH, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Emi Lu 06/03/09 10:45 AM >>> >> Now I need update view1 definition to >> create or replace view view1 as select col1, col2 from new_table; > >> However, col1 in new_table is not bpchar. This gives me headache! There >> are tens of dependent views based on view1, so I cannot just drop view1 >> and recreate it. > >> How I can redefine view1 without dropping it and recreate it? > > Cast the new column to bpchar? > > If you want to change the output column type of the view, you have to > drop and recreate it. Thank tom. Ok, I will not change view type, just keep bpchar for now. Just a wish, if >=8.4 could allow change view type, that would be great! -- Lu Ying -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] 10 TB database
Hi Artur, Some general comments: I'd look at partitioning and tablespaces to better manage the files where the data is stored, but also look at some efficiently parallelised disks behind the filesystems. You might also look at optimising the filesystem &OS parameters to increase efficiency as well, so it is a mix of hardware/OS/filesystem & db setup to optimise for such a situation. For data retrieval, clustered indexes may help, but as this requires a physical reordering of the data on disk, it may be impractical. Cheers, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Artur 06/16/09 3:30 AM >>> Hi! We are thinking to create some stocks related search engine. It is experimental project just for fun. The problem is that we expect to have more than 250 GB of data every month. This data would be in two tables. About 50.000.000 new rows every month. We want to have access to all the date mostly for generating user requesting reports (aggregating). We would have about 10TB of data in three years. Do you think is it possible to build this with postgresql and have any idea how to start? :) Thanks in advance, Artur -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] indexes on float8 vs integer
Hi Dennis, Is there any reason you are not using PostGIS to store the values as point geometries & use a spatial (GIST) index on them? I have tables with hundreds of millions of point features which work well. On disk data volume is not really worth optimising for with such systems, i suggest flexibility, ease of implementation & overall performance should be more valuable. If you need to store & query coordinates, then a map based tool seems relevant, and there are plenty of tools to do this soirt of thing with PostGIS data, such as Mapserver, GeoServer at the back end & OpenLayers in the front end. Cheers, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Scott Marlowe 07/12/09 10:31 PM >>> On Sat, Jul 11, 2009 at 10:15 PM, Dennis Gearon wrote: > > Anyone got any insight or experience in the speed and size of indexes on > Integer(4 byte) vs float (8byte). For a project that I'm on, I'm > contemplating using an integer for: > > Latitude > Longitude > > In a huge, publically searchable table. > > In the INSERTS, the representation would be equal to: > > IntegerLatOrLong = to_integer( float8LatOrLong * to_float(100) ); > > This would keep it in a smaller (4 bytes vs 8 byte) representation with > simple numeric comparison for indexing values while still provide 6 decimals > of precision, i.e. 4.25 inches of resolution, what google mapes provides. > > I am expecting this table to be very huge. Hey, I want to be the next > 'portal' :-) > Dennis Gearon Well, floats can be bad if you need exact math or matching anyway, and math on them is generally slower than int math. OTOH, you could look into numeric to see if it does what you want. Used to be way slower than int, but in recent versions of pgsql it's gotten much faster. Numeric is exact, where float is approximate, so if having exact values be stored is important, then either using int and treating it like fixed point, or using numeric is usually better. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] commercial adaptation of postgres
Also try Netezza, one data warehouse appliance originally based on Postgres. Although this is not the only such Postgres derivative. Cheers, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Greg Smith 07/24/09 9:10 AM >>> On Mon, 20 Jul 2009, Christophe wrote: > On Jul 20, 2009, at 6:56 PM, Dennis Gearon wrote: >> >> I once talked to a company that made a custome version of Postgres. It >> split tables up on columns and also by rows, had some other custome >> features. It was enormously faster from what I gathered. >> >> I could of sworn it began with the letter 'T', but maybe not. I don't see >> anything like that on the commercial page of the posgres site. > > Truviso? http://truviso.com/ We don't do any column-oriented stuff at Truviso. >From the description Dennis gave, I'm guess he's thinking of the Petabyte database at Yahoo: http://it.toolbox.com/blogs/database-soup/2-petabyte-postgresql-24848 -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] psql crashing - don't know why
Hi... I have a view across 3 tables, total some 5m rows. I can extract parts of the view, entire rows, with a where clause (eg: select * from view where cell_id=10;) If I try to select the entire view (eg: select * from view;) it runs for a while then gives the error msg "Killed" and returns to the system prompt, having exited psql. The log says: 2009-08-10 00:19:01 NZST ben200601 woodb LOG: could not send data to client: Broken pipe 2009-08-10 00:19:48 NZST ben200601 woodb LOG: unexpected EOF on client connection I have written a script along the lines of: get distinct cell_id from table; > file while read cell ; do psql -d db -c "select * from view where cell=$cell;" >> output done < file This worked, & the number of lines in the output file is the same number as that returned by "select count(*) from view;" (which works fine), but I'd like to find out the cause of the error. I assume there is some sort of timeout or overflow occurring, but I can't see any indication of what settings I can change to fix this. All the underlying tables have just had vacuum analyse run on them. I'm running PostgreSQL 8.1.4 on x86_64 Linux, I know it is dated, but I'm not in a position to upgrade at this point. Thanks, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] haversine formula with postgreSQL
A bit out in left field,
Writing your own haversine in Postgres seems a bit like reinventing a wooden
wheel when you gan get a free pneumatic one...
Any reason not to just install PostGIS & fully support geometries & projections
in Postgres?
You can build the geometries provided to the functions on the fly from lat/lon
coordinates stored as numerics in your SQL, so your DB structures don't even
have to change if you don't want them to..
http://www.postgis.org/documentation/manual-1.4/ST_Distance_Sphere.html
http://www.postgis.org/documentation/manual-1.4/ST_Distance_Spheroid.html
HTH
Brent Wood
Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Scott Marlowe 09/18/09 11:35 AM >>>
On Thu, Sep 17, 2009 at 1:16 PM, Jonathan wrote:
> Hi!
>
> I am looking at the PHP/MySQL Google Maps API store locator example
> here:
>
> http://code.google.com/apis/maps/articles/phpsqlsearch.html
>
> And I'm trying to get this to work with PostgreSQL instead of MySQL.
>
> I've (slightly) modified the haversine formula part of my PHP script
> but I keep getting this error:
>
> Invalid query: ERROR: column "distance" does not exist LINE
> 1: ...ude ) ) ) ) AS distance FROM aaafacilities HAVING distance <...
> ^
>
> I'm new to this, but it doesn't look like I need to create a column in
> my table for distance, or at least the directions didn't say to create
> a distance column.
>
> Here is my PHP with SQL:
> $query = sprintf("SELECT 'ID', 'FACILITY', 'ADDRESS', latitude,
> longitude, ( 3959 * acos( cos( radians('%s') ) * cos( radians
> ( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin
> ( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance FROM
> aaafacilities HAVING distance < '%s' ORDER BY dist LIMIT 0 OFFSET 20",
> pg_escape_string($center_lat),
> pg_escape_string($center_lng),
> pg_escape_string($center_lat),
> pg_escape_string($radius));
>
> Does anyone have any ideas on how I can get this to work? I'm not
> sure what is wrong, since it doesn't seem like I need to create a
> distance column and when I do create one, I get this:
Is that really the whole query? Why a having with no group by?
Can you do me a favor and print out $query instead of the php stuff?
It might help you as well to troubleshoot to see the real query.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
NIWA is the trading name of the National Institute of Water & Atmospheric
Research Ltd.
--
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 to get variable out to shell script
On Sun, 2009-09-20 at 16:49 -0500, Alex Gadea wrote: > I am using psql to call an external sql file that executes a simple > select count(*): > > ie: select into ct count(*) from table; > > I can't figure out how to make the ct variable available to the shell > script once the external sql file completes execution. Hi Alex, If you are using bash, for example: COUNT=`psql -d -Atc "select count(*) from table;"` in a shell script, any string enclosed in back-quotes (`) is executed & the result is returned. So the above expression assigns the value returned by the psql command to the variable called COUNT. The -Atc tells psql to return only the unaligned value, no formatting or column names, etc. If you store your SQL command outside the script, then you could use: COUNT=`psql -d -Atf ` HTH, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] db not dumping properly, or at least not restoring
Hi Kirk, How's it going? You can use pg_dump on the local host to access a db on a remote host, & as the output is just SQL, pipe this directly intp a psql command, thus replicating/migrating a database. One note, if you are doing this with a PostGIS db, I find it works better to create an empty target db with PostGIS installed first, then let the constraints on PostGIS objects prevent the old PostGIS being installed in the new db. Or you can copy over the old PostGIS & use the PostGIS upgrade SQL. Cheers, Brent Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Kirk Wythers 10/17/09 4:15 PM >>> On Oct 16, 2009, at 4:51 PM, Scott Marlowe wrote: > On Fri, Oct 16, 2009 at 11:25 AM, Kirk Wythers > wrote: > >> Any ideas what the problem could be here? > > Use the pg_dump from the target (i.e. newer) pgsql. I.e. if going > from 8.3.8 to 8.4.1, use the pg_dump that comes with 8.4.1 to dump the > 8.3.8 database. > Can I assume that this is even more critical if gong from 8.2 to 8.4? > I usually just do it like so: > > (First migrate accounts:) > pg_dumpall --globals -h oldserver | psql -h newserver postgres I'm a little confused here. Are you saying to used the network connections between thetwo servers and to pipe the dumpall directly to the psql load? > (then each database:) > createdb -h newserver dbname Then create new databases on the the new server to match the. The names from the old server? > pg_dump -h oldserver dbname | psql -h newserver dbname > (repeat as needed, save output for error messages) Then dump each database individually and pipe the dump to the psql load? These two procedures seem to duplicate the goal? Or am I mosaic something? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] bulk loading table via join of 2 large staging tables
This should help...
In each temporary table convert the time parts to a timestamp, then create an
index on each of these, then join on the timestamp.
ALTER table mmc add column timer timestamp without time zone;
UPDATE mmc set timer = (utc_year || '-' || utc_month || '-' || utc_day || ' '
||
utc_hour || ':' || utc_minute || ':' || utc_second)::timestamp;
CREATE index mmc_timer_idx on mmc(timer);
ALTER table gyro add column timer timestamp without time zone;
UPDATE gyro set timer = (utc_year || '-' || utc_month || '-' || utc_day || ' '
||
utc_hour || ':' || utc_minute || ':' || utc_second)::timestamp;
CREATE index gyro_timer_idx on gyro(timer);
so something like this should work if you use postgis - which I recommend for
GPS data
SELECT DISTINCT ON (project_id, platform_id, supplier_id, timer)
2 AS project_id,
1 AS platform_id,
6 AS supplier_id,
m.timer,
m.latitude,
m.longitude,
ST_SetSRID(ST_MAKEPOINT(m.longitude, m.latitude),4326) as location,
m.sog AS speed_over_ground,
m.cog AS course_over_ground,
g.heading
FROM rmc m,
gyro g
WHERE m.timer = g.timer;
One comment: If either table has times recorded at better than 1 sec precision
(ie - more than one value per second) you might join with the avg() value and
group by to bring the output into 1 sec values.
Cheers
Brent Wood
Programme leader: Environmental Information Delivery
NIWA
DDI: +64 (4) 3860529
From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org]
on behalf of Seb [splu...@gmail.com]
Sent: Tuesday, December 31, 2013 2:53 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] bulk loading table via join of 2 large staging tables
Hi,
I have two large CSV files that need to be merged and loaded into a
single table of a database in Postgresql 9.3. I thought I'd do this by
first staging the data in these files in two temporary tables:
---<cut here---start--->---
CREATE TEMPORARY TABLE rmc (
utc_year character varying(6),
utc_month character varying(4),
utc_day character varying(4),
utc_hour character varying(4),
utc_minute character varying(4),
utc_second character varying(8),
latitude numeric,
longitude numeric,
sog numeric,
cog numeric);
CREATE TEMPORARY TABLE gyro (
utc_year character varying(6),
utc_month character varying(4),
utc_day character varying(4),
utc_hour character varying(4),
utc_minute character varying(4),
utc_second character varying(8),
heading numeric);
---<cut here---end->---
And the target table in the database looks like this:
---<cut here---start--->---
Table
"public.navigation_series"
Column|Type |
Modifiers
--+-+--
navigation_record_id | integer | not null default
nextval('navigation_series_navigation_record_id_seq'::regclass)
project_id | integer |
platform_id | integer |
supplier_id | integer |
time | timestamp without time zone | not null
longitude| numeric |
latitude | numeric |
speed_over_ground| numeric |
course_over_ground | numeric |
heading | numeric |
Indexes:
"navigation_series_pkey" PRIMARY KEY, btree (navigation_record_id)
"navigation_series_project_id_platform_id_supplier_id_time_key" UNIQUE
CONSTRAINT, btree (project_id, platform_id, supplier_id, "time")
Foreign-key constraints:
"navigation_project_id_fkey" FOREIGN KEY (project_id) REFERENCES
projects(project_id) ON UPDATE CASCADE ON DELETE RESTRICT
"navigation_series_platform_id_fkey" FOREIGN KEY (platform_id) REFERENCES
platforms(platform_id) ON UPDATE CASCADE ON DELETE RESTRICT
"navigation_series_supplier_id_fkey" FOREIGN KEY (supplier_id) REFERENCES
suppliers(supplier_id) ON UPDATE CASCADE ON DELETE RESTRICT
---<cut here---end->---
Loading the temporary tables was very quick (about 3 min; input files
were 580 Mb and 3.5 Gb) in psql, using:
\copy gyro FROM 'gyro.csv' CSV
\copy rmc FROM 'rmc.csv' CSV
I then created a temporary view with:
CREATE TEMPORARY VIEW rmc_gyro AS
SELECT DISTINCT ON (project_id, platform_id, supplier_id, &q
Re: [GENERAL] union of types in a different category
I prefer the explicit approach used by Postgres - MYSQL is simpler, but I'd say simplistic in this area. While it can automate the cating of tpes/catories of variable, it doesn't always do it the way I want - so I need to be explicit anyway. In your second use case, which fails - do you want numerics cast to strings or vice versa? It can make difference, so to get what you want rather than otherwise, I prefer to be explicit. in either Postgres or MySQL. Interestingly - & i'm curious as to why" SELECT '1' UNION SELECT 2; ?column? -- 1 2 (2 rows) SELECT '1' UNION SELECT 1; ?column? -- 1 (1 row) I didn't think UNION did an explicit "distinct" - if that is what is happening? Brent Wood Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of James Harper [james.har...@bendigoit.com.au] Sent: Sunday, February 23, 2014 11:52 AM To: pgsql-general@postgresql.org Subject: [GENERAL] union of types in a different category According to clause 3 on http://www.postgresql.org/docs/9.3/static/typeconv-union-case.html regarding union type matching: 3. If the non-unknown inputs are not all of the same type category, fail. So a query "SELECT 1 UNION SELECT 1.1" works because 1 and 1.1 are of the same category, and one type has an implicit cast to the other, but the query "SELECT '1' UNION SELECT 2" fails because '1' is a string literal and 2 is a number and so they are different categories. Right? Is this an artificial limitation of postgres or is there an underlying technical reason for this behaviour? For my purposes it would be better if the restriction was removed and that the union would work as long as there was an implicit cast that allowed conversion of all fields to the same type. MSSQL doesn't have this restriction and I'd prefer if I didn't have to rewrite these queries (or create a complete set of mssql compatible types in the same category) when porting applications. Thanks James -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] Multiple Schema in One DB
We use this script as a proxy for psql, the user can run this followed by the schema's they want in the search path on the command line & the PGOPTIONS value sets it up for them... schema=`echo $@|sed 's/, /,/g'|tr " " ","|sed 's/,,/,/g'` #echo $schema export PGOPTIONS="-c search_path=$schema,public,maps" psql fish In your case this could perhaps be used by each application to customise the run time environment, so each has it's own PGOPTIONS string, and thus, when Postgres is run, it's own search path. Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Scott Marlowe [scott.marl...@gmail.com] Sent: Thursday, February 27, 2014 7:32 PM To: itishree sukla Cc: Postgres General Subject: Re: [GENERAL] Multiple Schema in One DB On Wed, Feb 26, 2014 at 11:24 PM, itishree sukla wrote: > Hi all, > > I am in a requirement to have multiple schema in one Database. > > Schema structure will be same with respect to DB all db objetcs like tables, > procedure. > > However each schema will be accessed by one one application. > > Problem is as all DB objects are same, when application is calling to a > schema we have set search_path='schema name' in our procedure level. Now we > want to get rid of that. > > I know we have do it based on ROLE Level, question is having more than one > user defined schema is it achievable, i have done a small exercise, seems to > be it is taking the first schema defined in the set search_path. > > As i am in urgent need of it, please help me. You can alter user to each one have their own search path. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] Mysterious DB reset
One point - a serial datatype will not increment to infinity, as it is effectively a 4 byte integer with a sequence imposed, it can only store values upto MAXINT (2147483647) . Above this it may well wrap around where MAXINT + 1 = 1 You can delay the problem (significantly) by using bigserial (8 byte integer) instead of serial - this has MAXINT=9223372036854775807 http://www.postgresql.org/docs/9.2/static/datatype-numeric.html Otherwise you might run a cron job or trigger to reset the serial values & the sequence when you think it timely. I can't see how this would cause the missing records though. Cheers Brent Wood Programme leader: Environmental Information Delivery NIWA DDI: +64 (4) 3860529 From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Thom Brown [t...@linux.com] Sent: Thursday, March 06, 2014 8:01 AM To: Israel Brewster Cc: PGSQL Mailing List Subject: Re: [GENERAL] Mysterious DB reset On 5 March 2014 18:22, Israel Brewster mailto:isr...@eraalaska.net>> wrote: I have a Postgresql 9.2.4 database containing real-time tracking data for our aircraft for the past week (theoretically). It is populated by two different processes: one that runs every few minutes, retrieving data from a number of sources and storing it in the DB, and one that has an "always on" connection to the DB streaming data into the database in realtime (often several records per second). To keep the database size manageable I have a cron job that runs every evening to delete all records that are more than a week old, after archiving a subset of them in permanent storage. This morning my boss e-mailed me, complaining that only a couple of aircraft were showing up in the list (SELECT distinct(tail) FROM data being the command that populates the list). Upon looking at the data I saw that it only went back to 4am this morning, rather than the week I was expecting. My first thought was "Oh, I must have a typo in my cleanup routine, such that it is deleting all records rather than only those a week old, and it's just that no one has noticed until now". So I looked at that, but changing the delete to a select appeared to produce the proper results, in that no records were selected: DELETE FROM data WHERE pointtime
Re: [GENERAL] SSD Drives
have you seen this? http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html Brent Wood Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | www.niwa.co.nz<http://www.niwa.co.nz> [NIWA]<http://www.niwa.co.nz> From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Bret Stern [bret_st...@machinemanagement.com] Sent: Thursday, April 3, 2014 8:37 AM To: pgsql-general@postgresql.org Subject: [GENERAL] SSD Drives Any opinions/comments on using SSD drives with postgresql? -- 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] SSD Drives
Hi David, Does the RAID 1 array give any performance benefits over a single drive? I'd guess that writes may be slower, reads may be faster (if balanced) but data security is improved. Brent Wood Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | www.niwa.co.nz<http://www.niwa.co.nz> [NIWA]<http://www.niwa.co.nz> From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of David Rees [dree...@gmail.com] Sent: Friday, April 4, 2014 8:32 AM To: Merlin Moncure Cc: bret_st...@machinemanagement.com; PostgreSQL General Subject: Re: [GENERAL] SSD Drives On Thu, Apr 3, 2014 at 12:13 PM, Merlin Moncure wrote: > On Wed, Apr 2, 2014 at 2:37 PM, Bret Stern > wrote: >> Any opinions/comments on using SSD drives with postgresql? > > Here's a single S3700 smoking an array of 16 15k drives (poster didn't > realize that; was to focused on synthetic numbers): > http://dba.stackexchange.com/questions/45224/postgres-write-performance-on-intel-s3700-ssd I just ran a quick test earlier this week on an old Dell 2970 (2 Opteron 2387, 16GB RAM) comparing a 6-disk RAID10 with 10k 147GB SAS disks to a 2-disk RAID1 with 480GB Intel S3500 SSDs and found the SSDs are about 4-6x faster using pgbench and a scaling factor of 1100. Some sort of MegaRAID controller according to lspci and has BBU. TPS numbers below are approximate. RAID10 disk array: 8 clients: 350 tps 16 clients: 530 tps 32 clients: 800 tps RAID1 SSD array: 8 clients: 2100 tps 16 clients: 2500 tps 32 clients: 3100 tps So yeah, even the slower, cheaper S3500 SSDs are way fast. If your write workload isn't too high, the S3500 can work well. We'll see how the SMART drive lifetime numbers do once we get into production, but right now we estimate they should last at least 5 years and from what we've seen it seems that SSDs seem to wear much better than expected. If not, we'll pony up and go for the S3700 or perhaps move the xlog back on to spinning disks. -Dave -- 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] Linux vs FreeBSD
Not a great help with which Linux to run, nor Postgres focused, but may be of interest, & very relevant to the subject line.. Given the likely respective numbers of each OS actually out there, I'd suggests BSD is very over-represented in the high uptime list which is suggestive. http://uptime.netcraft.com/perf/reports/performance/Hosters?orderby=epercent Cheers, Brent Wood Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | www.niwa.co.nz<http://www.niwa.co.nz> [NIWA]<http://www.niwa.co.nz> From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Fran?ois Beausoleil [franc...@teksol.info] Sent: Thursday, April 10, 2014 8:36 AM To: Bruce Momjian Cc: Christofer C. Bell; pgsql-general@postgresql.org Subject: Re: [GENERAL] Linux vs FreeBSD Le 2014-04-09 ? 16:20, Bruce Momjian a ?crit : On Wed, Apr 9, 2014 at 10:02:07AM -0500, Christofer C. Bell wrote: This highlights a more fundamental problem of the difference between a workstation-based on OS like Ubuntu and a server-based one like Debian or FreeBSD. I know Ubuntu has a "server" version, but fundamentally Ubuntu's selection of kernels and feature churn make it less than ideal for server deployments. I am sure someone can post that they use Ubuntu just fine for server deployments, but I continue to feel that Ubuntu is chosen by administrators because it an OS they are familiar with on workstations, rather than it being the best choice for servers. I'm not a full-time sysadmin. I chose Ubuntu because I have familiarity with it, and because installing Puppet on it installed the certificates and everything I needed to get going. I tried Debian, but I had to fight and find the correct procedures to install the Puppet certificates and all. Ubuntu saved me some time back then. Cheers! Fran?ois <>
Re: [GENERAL] concatenating with NULLs
Hi Seb, Use CASE to change nulls to empty strings (or a placeholder) as below. See: http://www.postgresql.org/docs/9.0/static/functions-conditional.html if you want a placeholder in the result to indicate the presence of a null, try the second SQL: test=# select (case when 'a' isnull then '' else 'a' end) || (case when 'b' isnull then '' else 'b' end) || (case when NULL is null then '' end); ?column? -- ab (1 row) test=# select (case when 'a' isnull then '_' else 'a' end) || (case when 'b' isnull then '_' else 'b' end) || (case when NULL is null then '_' end); ?column? ------ ab_ (1 row) test=# HTH, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Seb 04/26/11 10:21 AM >>> Hi, A query such as: SELECT 'a' || 'b' || NULL; returns a NULL. How can I ensure I get 'ab' in the result? I'm trying to concatenate columns and one of them might have NULL values, which I simply want to ignore. Cheers, -- Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] Miidpoint between two long/lat points? (earthdistance?)
Why not install PostGIS with full ellipsoidal & projection support & use the azimuth & distance functions available in SQL? Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Carlo Stonebanks 05/27/11 8:20 AM >>> Nicely done, Merlin! Hope others with the same problem can find this post. Thanks a lot. -Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: May 26, 2011 9:53 AM To: Carlo Stonebanks Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Miidpoint between two long/lat points? (earthdistance?) On Thu, May 26, 2011 at 12:05 AM, Merlin Moncure wrote: > Converted from javascript from here: http://en.wikipedia.org/wiki/Atan2 whoops! meant to say here: http://www.movable-type.co.uk/scripts/latlong.html merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] Read MS-SQL data into Postgres via ODBC link?
Hi Jonathan, I haven't done this from MySQL, but have from Postgres & from Oracle >From a command line client, extract the data from the source table, so you get >a stream of csv style records. Then pipe these directly into a psql statement to load them into the target table. A simple pg2pg example: psql -d $DB1 -F '|' -Atc "select * from table;" | psql -d $DB2 -c "copy table from STDIN with delimiter '|' with null '';" A MySQL example would be similar, the second part pretty much identical. HTH, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> "Jonathan Brinkman" 07/05/11 12:45 PM >>> Greetings I'd like to INSERT data into my Postgresql 8.4.8 table directly from an ODBC-linked MS-SQL table or view. I'm looking at using the Cybertec tool "ODBC-LINK" (http://www.cybertec.at/en/postgresql_products/odbc-link) but I'm wondering if there isn't a way to do this through Postgresql directly? I saw a post about someone doing a "SELECT * FROM XXX ODBC SOURCE" or something like that (http://archives.postgresql.org/pgsql-odbc/2009-07/msg00032.php) and that would be cool. I don't need to import massive datasets, only 20-30K records at a time. Thanks much Jonathan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] Error Importing CSV File
Can you show the output of \d geo_data ? Try 'using' delimiters Are you doing this as the postgres superuser? Because COPY can't load from files as a casual user, you need to pipe it to copy & read from stdin. Simple script below works for me, modified copy statement might help?. HTH, Brent Wood #! /bin/bash DB=test psql -d $DB -c "drop table geo_data;" # latitude has only one 't' psql -d $DB -c "create table geo_data ( zip_code text, latitude float8, longitude float8, city text, state text, countytext);" echo "96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO" | \ psql -d $DB -c "copy geo_data from stdin using delimiters ',' null '';" psql -d $DB -c "select * from geo_data;" Output: zip_code | latitude | longitude | city| state | county --+---++---+---+--- 96799| -7.209975 | -170.7716 | PAGO PAGO | AS| AMERICAN SAMOA 96941| 7.138297 | 151.503116 | POHNPEI | FM| FEDERATED STATES OF MICRO 96942 | 7.138297 | 151.503116 | CHUUK | FM| FEDERATED STATES OF MICRO (3 rows) Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Bryan Nelson 07/16/11 7:15 AM >>> I am having problems importing a CSV file of sample data for testing in a web app. Columns & Types --- zip_code - text lattitude - float8 longitude - float8 city - text state - text county - text Some Sample Data From CSV File -- 96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO COPY Command COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV; Error Message - ERROR: invalid input syntax for integer: "96799" CONTEXT: COPY geo_data, line 1, column id: "96799" I can't figure out why it keeps choking with "invalid syntax for integer" since the field was created as "text". Any and all help greatly appreciated! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
[GENERAL] Factors Influencing Participant Satisfaction with Free/Libre and Open Source Software Projects
Apologies for the cross posting, but this thesis may be of interest to a wide array of FOSS related lists. It just went public: Title: Factors Influencing Participant Satisfaction with Free/Libre and Open Source Software Projects Author: Chawner, Brenda Abstract: The purpose of this research was to identify factors that affect participants’ satisfaction with their experience of a free/libre open source software (FLOSS) project. ... http://researcharchive.vuw.ac.nz/handle/10063/1710
Re: [GENERAL] How to force select to return exactly one row
Use a case staement to test for a null output, & return whatever you want in the event of it being null, else the actual value: from the top of my head, something like: SELECT case when (select somecolumns FROM ko RIGHT JOIN (SELECT 1) _forceonerow ON true LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue') not null then (select somecolumns FROM ko RIGHT JOIN (SELECT 1) _forceonerow ON true LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue') else 0 end It does require the query to be run twice, so does have extra overhead. You could wrap a function around this to get & store the result & test that, then having stored it you can use it for the output value without a second query. All depends on how much overhead there is in teh query. HTH, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> "Andrus" 06/22/10 10:12 AM >>> Autogenerated select statement contains 0 .. n left joins: SELECT somecolumns FROM ko LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey='someprimarykeyvalue'; This select can return only 0 or 1 rows depending if ko row with primary key 'someprimarykeyvalue' exists or not. Problem: if there is no searched primary key row in ko database, select should also return empty row. To get this result I added right join: SELECT somecolumns FROM ko RIGHT JOIN (SELECT 1) _forceonerow ON true LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue'; but it still does not return row if primary key row 'someprimarykeyvalue' does not exist. How to force this statement to return one row always ? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] To store and retrive image data in postgresql
>>> aravind chandu <[EMAIL PROTECTED]> 07/08/08 10:30 AM >>> Hello, I need to store an image in postgresql database and after that i need to retrive the image back.Can you please help me how to do this? Hi, I have been involved in building a few applications to manage this capability. In the 3 cases to date, we store the info about the image in the database, including the name/path/URL to the image, and have an application retrieve the image for us from this data. We have not (yet) found any added value in storing the image itself as a binary object in the database. Images are stored as files on the system. One (relatively) easy way to demonstrate this is using QGIS. This is able to display data stored in PostGIS tables (PostGIS adds spatial support to Postgres, so we can store a point location with an image name/path). We can plot a map in QGIS showing the points representing photographs, and via the "action" capability in QGIS, we can create a command to click an image on the map & display it, retrieving the path & name from the database in order to do so. One PostGIS/ImageJ application we are working on does store ImageJ ROI files as binary objects in the database, but the images they are derived from is still stored outside the database as a file, with the path/name stored as database fields as a pointer to the image. HTH, Brent Wood -- 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] storing latitude and longitude
Hi Mark, Look at Postgis, to do this properly. It adds full OGC spec support for managing spatial/querying spatial data within a Postgres database. It is an option included with the Windows Postgres installer, but is generally extra packages under Linux. Cheers, Brent Wood >>> mark <[EMAIL PROTECTED]> 07/11/08 5:34 AM >>> hi.. i want to store latitude and longitude in a users table.. what is the best data type to use for this? i want to be able to find use this info to find users within a distance.. how do i go about doing this? thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Query help
If I read this correctly, you want the output sorted by config_id,start_day(day),start_time, thus: select config_id, start_day as day, start_time, end_time from config order by config_id, start_day, start_time; Cheers, Brent Wood >>> novice <[EMAIL PROTECTED]> 08/15/08 3:55 PM >>> Hi, I have a table select id, config_id, start_day, end_day, start_time, end_time from config; id | config_id | start_day | end_day | start_time | end_time -+---+---+-++-- 1 | 101 | Mon | Sun | 08:30:00 | 18:00:00 2 | 101 | Mon | Sun | 18:00:00 | 22:00:00 3 | 555 | Mon | Fri | 08:30:00 | 16:00:00 I'd like to write a query to generate the following... is it possible at all? config_id | day | start_time | end_time ---+---+-+- 101 | Mon | 08:30:00 | 18:00:00 101 | Mon | 18:00:00 | 22:00:00 101 | Tue | 08:30:00 | 18:00:00 101 | Tue | 18:00:00 | 22:00:00 101 | Wed | 08:30:00 | 18:00:00 101 | Wed | 18:00:00 | 22:00:00 101 | Thu | 08:30:00 | 18:00:00 101 | Thu | 18:00:00 | 22:00:00 101 | Fri | 08:30:00 | 18:00:00 101 | Fri | 18:00:00 | 22:00:00 101 | Sat | 08:30:00 | 18:00:00 101 | Sat | 18:00:00 | 22:00:00 101 | Sun | 08:30:00 | 18:00:00 101 | Sun | 18:00:00 | 22:00:00 555 | Mon | 08:30:00 | 18:00:00 555 | Tue | 08:30:00 | 18:00:00 555 | Wed | 08:30:00 | 18:00:00 555 | Thu | 08:30:00 | 18:00:00 555 | Fri | 08:30:00 | 18:00:00 Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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 size of your PostgreSQL Database?
I have a db (tables with up to 5,000,000 records, up to 70 columns x 1,500,000 records, around 50Gb of disk space for the database (incl data, indexes, etc) Most records have PostGIS geometry columns, which work very well. For read performance this is on a (2 yr old) Linux box with 2x software RAID 0 (striped) WD 10,000RPM Raptor drives. FWIW bonnie gives reads at about 150Mb/sec from the filesystem. We have been more than happy with performance. though the 4Gb of RAM helps For data security, pg_dump backs it up every second day onto another 250Gb drive on the box, & this is copied over the LAN to another server which is backed up to tape every day. It works for us :-) Cheers, Brent Wood >>> Ow Mun Heng <[EMAIL PROTECTED]> 08/19/08 4:00 PM >>> On Mon, 2008-08-18 at 11:01 -0400, justin wrote: > Ow Mun Heng wrote: > > -Original Message- > > From: Scott Marlowe <[EMAIL PROTECTED]> > > > > > If you're looking at read only / read > > > mostly, then RAID5 or 6 might be a better choice than RAID-10. But > > > RAID 10 is my default choice unless testing shows RAID-5/6 can beat > > > it. > > > > > > > I'm loading my slave server with RAID-0 based on 3 IDE 7200 Drives. > > Is this worst off than a RAID 5 implementation? > > > > > > > I see no problem using Raid-0 on a purely read only database where > there is a copy of the data somewhere else. RAID 0 gives performance. > If one of the 3 drives dies it takes the server down and lost of data > will happen. The idea behind RAID 1/5/6/10 is if a drive does fail > the system can keep going.Giving you time to shut down and replace > the bad disk or if you have hot swappable just pull and replace. I'm looking for purely read-only performance and since I didn't have the bandwidth to do extensive testing, I didn't know whether a RAID1 or a Raid 0 will do the better job. In the end, I decided to go with RAID 0 and now, I'm thinking if RAID1 will do a better job. > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Oracle and Postgresql
I agree with David's comment. The business I work for recently proposed a FOSS based solution for a client, but were told no FOSS solutions would be considered. We had a subcontractor offer a support contract for an application based on the same FOSS components, but with a support contract. This was perfectly acceptable to our client, who now has a working solution, for which they pay annual maintenance, in return for a lack of internal liability. For many businesses, risk avoidance is a core driver. What they require is someone else to blame if things go wrong, hence the companies making a living with contracts for Postgres support. Cheers, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> David Fetter <[EMAIL PROTECTED]> 09/01/08 6:09 PM >>> On Sun, Aug 31, 2008 at 10:44:38PM -0400, Guy Rouillier wrote: > M2Y wrote: >> >> Why most enterprises prefer Oracle than Postgres even though it is >> free and has a decent enough user community. > > Databases are a critical part of many companies' business. I work > for telecom company, and if we were to lose our databases, we'd be > out of business, period. So, "free and decent enough" are not good > enough. If you are going to bet your business on anything, you > want to be as sure as possible that "it" is reliable and that you > can expect quick action if "it" should break. What they want to have is a huge entity they can blame when everything goes wrong. They're not interested in the actual response times or even in the much more important time-to-fix because once they've blamed Oracle, they know the responsibility is no longer on their shoulders. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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 TPC-H test result?
My 02c, Pg does itself no favours by sticking with such pessimistic defaults, and a novice user wanting to try it out will find tweaking the pg configuration files for performance quite complicated. Given the general increase in typical hardware specs these days, perhaps the default pg specs could be set for higher spec systems? Or perhaps the standard install could come with 2 or 3 versions of the config files, & the user can simply rename/invoke the one that fits their system best? I figure (somewhat simplistically) that most settings are more related to available memory than anything else, so perhaps config files for typical 1Gb, 4Gb & 8Gb systems could be provided out of the box to make initial installs simpler? Cheers, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Andrew Sullivan <[EMAIL PROTECTED]> 09/10/08 3:47 AM >>> On Tue, Sep 09, 2008 at 07:59:49PM +0800, Amber wrote: > I read something from > http://monetdb.cwi.nl/projects/monetdb/SQL/Benchmark/TPCH/index.html Given that the point of that "study" is to prove something about performance, one should be leery of any claims based on an "out of the box" comparison. Particularly since the "box" their own product comes out of is "compiled from CVS checkout". Their argument seems to be that people can learn how to drive CVS and to compile software under active development, but can't read the manual that comes with Postgres (and a release of Postgres well over a year old, at that). I didn't get any further in reading the claims, because it's obviously nothing more than a marketing effort using the principle that deriding everyone else will make them look better. Whether they have a good product is another question entirely. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] foreign key problem
Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Jorge Godoy <[EMAIL PROTECTED]> 09/17/08 1:36 AM >>> Em Monday 15 September 2008 19:05:25 [EMAIL PROTECTED] escreveu: > Hi, > > I need a foreign key (or equivalent) where the referenced table cannot have > a unique constraint. > > For fisheries surveys we have a table of catch records. For a given event, > several species are caught, and are weighed by one or more methods. Thus a > given event may have more than one record for the same spaecies, as parts > of the catch may be weighed differently. > > When we sample a species for measuring, the event is recorded, but as the > sample may include individuals from throughout the catch, it does not > necessarily relate to the catch-with-method table. Looks like you need a composite primary key here, i.e. a primary key for the "category" of the record will have more than one column (such as species and method of catch). With that you'll be able to uniquely identify the "event" and then associate it with the record. Thanks Jorge, There are many catches per event, one for each species/method, so a composite key would be on event/species/method for the catch. For lengths it would be on event/species (there is no catch weigh method here). There should only be a length record for a matching event/species in the catch (normally constrained via a foreign key) but I cannot create a composite key on catch without including method, so there is no possible unique key on catch to match to the primary key on length. -- Jorge Godoy <[EMAIL PROTECTED]> -- 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] postgres/postgis
You might try the Geo repository for OpenSUSE. This includes builds in 32 & 64 bit for several other distros as well as OpenSUSE for the GIS related applications, but you will probably need to compile Postgres (that version) from scratch. Which is unfortunate, as the default build by compiling Postgres puts files in different places than the standard RPM packages, so if you do compile Postgres, you may also be better off compiling PostGIS/proj/GDAL/GEOS/etc from scratch as well to ensure everything works together. The OpenSUSE geo repository is at: http://download.opensuse.org/repositories/Application:/Geo/ HTH, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> "Eduardo Arévalo" <[EMAIL PROTECTED]> 10/07/08 6:34 AM >>> hello is campatible install postgresql-8.3.4-1-linux-x64 with postgis-1.3.3. postgis there for 64-bit architecture?? There are the libraries and proj4 GEOS arqitectura for 64-bit?? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Defining string to represent null values in select
Hi, I can specify the text used to represent a null value in output from copy, but I'd like to do something similar is select output, eg: all NULL values are represented by NA or NaN. I can't find anything in the docs about this. This could be managed using case statements around all the columns in the query, but is there a simpler way, like setting a system variable to specify this? Thanks, Brent Wood -- 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] Specifying text to substitute for NULLs in selects
Thanks guys,
I'm aware of those options, what I was wondering was if there is a more generic
way,
for example the Empress RDBMS allows 'set MSNULLVALUE "NA"', and all NULLs
will from then on be output as NA.
The COPY option is closest to a generic setting, but doesn't work with a select
query,
just a table dump.
I guess something like the following will work from the shell, although it is
hardly elegant :-)...
psql -d DB -Atc "select '', attr, attr, attr, '' from ;" | sed
's/||/|NA|/' | sed 's/|//' | sed 's/|//' > data.txt
Slightly simpler than the case statement approach in Postgres is COALESCE()
eg: select COALESCE(attr,'NA') as attr from table;
but this still needs to be applied to every column in the outout which may have
nulls. rather than a generic one off setting. A view using COALESCE() may be
the easiest way for users to have this capability automatically..
Thanks,
Brent Wood
Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Said Ramirez <[EMAIL PROTECTED]> 11/08/08 12:34 PM >>>
I think you are more after something like
SELECT CASE WHEN foo IS NULL THEN 'NA' END FROM bar.
-Said
Ivan Sergio Borgonovo wrote:
> On Thu, 6 Nov 2008 17:44:42 -0800 (PST)
> [EMAIL PROTECTED] wrote:
>
> >
> > Hi,
> >
> > I can specify the text used to represent a null value in output
> > from copy, but I'd like to do something similar is select output,
> > eg: all NULL values are represented by NA or NaN.
> >
> > I can't find anything in the docs about this.
> >
> > This could be managed using case statements around all the columns
> > in the query, but is there a simpler way, like setting a system
> > variable to specify this?
>
> wtw_drupal=# create schema test;
> CREATE SCHEMA
> wtw_drupal=# create table test.test(c1 text);
> CREATE TABLE
> wtw_drupal=# insert into test.test values(null);
> INSERT 0 1
> wtw_drupal=# insert into test.test values('test');
> INSERT 0 1
> wtw_drupal=# \copy test.test to stdout null as 'BANANA'
> BANANA
> test
> wtw_drupal=# drop schema test cascade;
> NOTICE: drop cascades to table test.test
> DROP SCHEMA
>
> everything clearly explained in the COPY manual:
> http://www.postgresql.org/docs/8.1/static/sql-copy.html
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
NIWA is the trading name of the National Institute of Water & Atmospheric
Research Ltd.
--
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] Specifying text to substitute for NULLs in selects
Thanks Adrian, That's perfect!! Cheers, Brent Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Adrian Klaver <[EMAIL PROTECTED]> 11/08/08 1:49 PM >>> On Friday 07 November 2008 4:05:08 pm Brent Wood wrote: > Thanks guys, > > I'm aware of those options, what I was wondering was if there is a more > generic way, for example the Empress RDBMS allows 'set MSNULLVALUE "NA"', > and all NULLs will from then on be output as NA. > > The COPY option is closest to a generic setting, but doesn't work with a > select query, just a table dump. > > I guess something like the following will work from the shell, although it > is hardly elegant :-)... > > psql -d DB -Atc "select '', attr, attr, attr, '' from ;" | sed > 's/||/|NA|/' | sed 's/|//' | sed 's/|//' > data.txt > > Slightly simpler than the case statement approach in Postgres is COALESCE() > > eg: select COALESCE(attr,'NA') as attr from table; > > but this still needs to be applied to every column in the outout which may > have nulls. rather than a generic one off setting. A view using COALESCE() > may be the easiest way for users to have this capability automatically.. > > Thanks, > >Brent Wood > > Using psql http://www.postgresql.org/docs/8.2/interactive/app-psql.html lfnw=# \a\t\f ','\pset null 'NA' Output format is unaligned. Showing only tuples. Field separator is ",". Null display is "NA". lfnw=# SELECT null,1; NA,1 -- Adrian Klaver [EMAIL PROTECTED] NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to best resync serial columns
Hi, I have a number of tables with serial columns as a primary key. I'm looking to add lots of records via copy, but should reset the serial counters to the appropriate value after this. Is there a simple way to do this, or do I just update the last_value column in each seq table to the max(id) from the relevant table. Thanks, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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 to best resync serial columns
Thanks Erik...
I found an alternative to psql copy to stdout | psql copy from stdout.
I used pg_dump -n schema | psql
This approach replicated the entire schema, rather than just the table
contents,
into the new database, and therefore copied over all the seq data as well. It
worked well in this situation.
Thanks for the reply, I'll note it for future reference.
Cheers,
Brent Wood
Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Erik Jones <[EMAIL PROTECTED]> 11/11/08 8:03 PM >>>
On Nov 10, 2008, at 6:48 PM, Brent Wood wrote:
> Hi,
>
> I have a number of tables with serial columns as a primary key.
>
> I'm looking to add lots of records via copy, but should reset the
> serial counters to the appropriate value after this.
>
> Is there a simple way to do this, or do I just update the last_value
> column in each seq table to the max(id) from the relevant table.
You shouldn't edit sequence table directly. To set a sequence's value
you should use the setval(seqname, seqval) function like so:
SELECT setval('some_seq', 1000);
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
NIWA is the trading name of the National Institute of Water & Atmospheric
Research Ltd.
--
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] ordered pg_dump
It isn't guaranteed, but I think a clustered index on the attrs you want the dump ordered by will give an ordered dump. This may depend on your filesystem, and on what else your system is doing at the time, as interupted disk reads may disrupt the sequence. It has worked for me on Suse Linux with Reiser FS when the dump was the only (major) process running. You can also cut out the data text of the dump, if you used COPY format & not inserts, then use sort & awk to order the records appropriately, then paste them back in (at least on Linux/UNIX you can, on Windows this would need Cygwin installed). None of which is ideal or robust, & having pg_dump able to generate ordered dumps natively would be useful. Cheers, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Josh Williams <[EMAIL PROTECTED]> 11/11/08 8:04 PM >>> On Mon, 2008-11-10 at 17:05 -0800, Jeff Davis wrote: > Is there any interest in an optional mode for pg_dump to order the > output so that it's easier to use diff? > > I don't think it would make the output 100% deterministic, but it would > make it easier to at least compare the data for small databases. That'd be cool. I'd done some poking around on the topic a little while back. The goal was to make the output more predictable so that backups would be more efficient, specifically with a product that does binary diffs of some sort. I may still have some notes somewhere if you're interested. But I believe the idea was to use COPY with a SELECT statement. The non-trivial part was to figure out a proper ordering to use. Or did you plan on combining it with -t, where you could then specify the ordering for each table? > I think this has been brought up before, but I couldn't find the thread, > so I don't know what conclusion was reached. > > Regards, > Jeff Davis (... Plus, you potentially get a free CLUSTER on a reload.) - Josh Williams -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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 to "group" several records with same timestamp into one line?
You need to use a self relation, not a group by, as no data are being aggregated into a new single value, which is what the group by achieves. This joins a table to itself, so that columns in it can be replicated. The key is that the where clause in each case needs to just select one channel, so it acts like a similar table but contains different rows. Because you used a char() instead of varchar() for channel, you may find your string 'channel1' has spaces in it to pad it to the specified length, in which case the where clauses below can use "like '%channel1%'" instead of "= 'channel1'" or you can strip the spaces before the comparison, eg: where "trim(a.channel)='channel1'". I hope this makes sense. eg: select a.data, a.unit, b.data, b.unit, c.data, c.unit, d.data, d.unit, a.create_on from record data a, record-data b, record_data c, record_data d where a.channel='channel1' and b.channel='channel2' and c.channel='channel3' and d.channel=channel4 and b.create_on = a.create_on and c.create_on = a.create_on and d.create_on = a.create on; Thus table a comprises all records from channel1, etc... and they are joined on a common timestamp. NOTE: if any of the channels are null for any given timestamp, you will get no record for that timestamp using this syntax, even if other channels had data, because the query uses an inner join. If this is a problem then you'll need to reword the query as an outer join. HTH, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> "zxo102 ouyang" <[EMAIL PROTECTED]> 11/13/08 3:15 AM >>> Hi everyone, My data with same timestamp "2008-11-12 12:12:12" in postgresql are as follows rowid data unitchannel create_on -- 11.5 MPa channel1 2008-11-12 12:12:12 22.5 M3 channel2 2008-11-12 12:12:12 33.5 M3 channel3 2008-11-12 12:12:12 44.5 t channel4 2008-11-12 12:12:12 -- I would like to "group" them into one line with SQL like 1.5 MPa 2.5M3 3.5 M3 4.5 t 2008-11-12 12:12:12 The table is defined as CREATE TABLE record_data ( rowid serial NOT NULL, data double precision, unit character(10), channel character(20), create_on timestamp ) Is that possible? Thanks for your help in advance. Ouyang NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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 handle multi-billion row read-only table?
If you will be selecting sets of data within a time range, it should also improve performance if you can build a clustered index on the sample_time. It may also be worth looking at whether partitioning by timestamp & channel offers any advantages. Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Justin Graf 02/10/10 3:07 PM >>> On 2/9/2010 4:41 PM, Asher Hoskins wrote: > > Thanks for that, it looks like partitioning is the way to go. I'm > assuming that I should try and keep my total_relation_sizes less than > the memory size of the machine? This depends on what the quires look like. As other have stated when partitioning you have to consider how the data is quired. > > > If I partition so that each partition holds data for a single channel > (and set a CHECK constraint for this) then I can presumably remove the > channel from the index since constraint exclusion will mean that only > partitions holding the channel I'm interested in will be searched in a > query. Given that within a partition all of my sample_time's will be > different do you know if there's a more efficient way to index these? Given the timestamp will most likely be the where clause, NO on the plus side its only 8 bytes All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
Re: [GENERAL] running multiple versions
Run them in different locations with different addresses (5432 & 5433 for example) see this thread:http://archives.postgresql.org/pgsql-admin/2008-02/msg00084.php Brent Wood GIS/DBA consultant NIWA +64 (4) 4 386-0300 From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Heine Ferreira [heine.ferre...@gmail.com] Sent: Friday, February 17, 2012 11:11 AM To: pgsql-general@postgresql.org Subject: [GENERAL] running multiple versions Hi Is it possible to run more than one instance of Postgresql on Windows? Can you run different versions simultaneously? If so can you run different minor versions or only different major versions? If possible how do you do this? Thanks H.F. -- Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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 Linux to run
Hmm... I tend to _mostly_ run workstations rather than servers, & pick my distro to suit my application needs. My workplace is a SLES site, & I use Open Suse. Given most of my Postgres databases are in fact PostGIS databases, and need to work with a variety of other spatial data & GIS related apps, then I have a set of dependencies to work with for every install. Postgres, Postgis, GEOS, Proj, GDAL, mapserver, Java, python. QGIS, GMT, etc. I have liased with the package maintainers who look after the Suse GEO repository, and they are generally able to build any required package, for both server * workstation distros (SLED, SLES, OpenSuse). Having robust packages built by people who know more than I do about this area is core to my selection of distro. While I'm aware that Debian, Ubuntu, Fedora also have GIS related repositories, the OPenSuse ones have, for me at least, the best mix of currency & stability, & fantastic support. If your goal is to run a robust Postgres server, find the mainstream distro which provides what you want out of the box, so you can run the database, not wrestle with compiling it every time something changes. Only consider compiling your own applications if there is no such distro, or you really want to have that level of control & ownership of the system. Also, if you are running a VM as your server, then under Xen commercial tools, for example, SLES is fully supported by the hypervisor. Ubuntu isn't. Makes choosing easy... YMMV :-) Brent Wood GIS/DBA consultant NIWA +64 (4) 4 386-0300 From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of David Boreham [david_l...@boreham.org] Sent: Sunday, March 04, 2012 3:23 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] what Linux to run On 3/3/2012 7:05 PM, Tom Lane wrote: > > [ raised eyebrow... ] As the person responsible for the packaging > you're dissing, I'd be interested to know exactly why you feel that > the Red Hat/CentOS PG packages "can never be trusted". Certainly they > tend to be from older release branches as a result of Red Hat's desire > to not break applications after a RHEL branch is released, but they're > not generally broken AFAIK. > > No dissing intended. I didn't say or mean that OS-delivered PG builds were generally broken (although I wouldn't be entirely surprised to see that happen in some distributions, present company excluded). I'm concerned about things like : a) Picking a sufficiently recent version to get the benefit of performance optimizations, new features and bug fixes. b) Picking a sufficiently old version to reduce the risk of instability. c) Picking a version that is compatible with the on-disk data I already have on some set of existing production machines. d) Deciding which point releases contain fixes that are relevant to our deployment. Respectfully, I don't trust you to come to the correct choice on these issues for me every time, or even once. I stick by my opinion that anyone who goes with the OS-bundled version of a database server, for any sort of serious production use, is making a mistake. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] huge price database question..
Also look at a clustered index on timestamp Brent Wood GIS/DBA consultant NIWA +64 (4) 4 386-0300 From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Jim Green [student.northwest...@gmail.com] Sent: Wednesday, March 21, 2012 2:50 PM To: David Kerr Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] huge price database question.. On 20 March 2012 21:40, David Kerr wrote: > On 03/20/2012 04:27 PM, Jim Green wrote: > > Greetings list! > I am pretty new to postgresql from mysql and did a fairly extensive > search of the list and came up with a few good ones but didn't find > the exact same situation as I have now. so I am venturing asking here. > > I have daily minute stock price data from 2005 on and each day with > columns timestamp, open,high,low,close,volume and a few more. each > day's data is about 1.2million rows. I want import all the data to > postgresql and analyze using R with the help of Rpostgresql. > > right now I am having about 7000 tables for individual stock and I use > perl to do inserts, it's very slow. I would like to use copy or other > bulk loading tool to load the daily raw gz data. but I need the split > the file to per stock files first before I do bulk loading. I consider > this a bit messy. > > I would seek advise on the following idea: > store everything in a big table, partition by month(this gives a > reasonable number of partitions) and do bulk loading on the daily > file. my queries would consist mostly select on a particular symbol on > a particular day. > > Also in the future, I will import daily data to the db every day. > > my hardware is 16G Ram, 4x5400rpm raid10 with enough space. > > Thanks! > > Jim. > > > Seems like you'd want to do this? > http://search.cpan.org/~turnstep/DBD-Pg-2.19.2/Pg.pm#pg_putcopydata > COPY support > > DBD::Pg allows for quick (bulk) reading and storing of data by using the > COPY command. The basic process is to use $dbh->do to issue a COPY command, > and then to either add rows using "pg_putcopydata", or to read them by using > "pg_getcopydata". Thanks! would you comment on the table setup as well? Jim. > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] CPU Load 100% when pg_dump start in Postgresql 8.4
Can you not nice the dump process to free up resources during the dump? Of course this will not free up any locks, and will make them hang around longer as the dump is slowed down. Brent Wood GIS/DBA consultant NIWA +64 (4) 4 386-0300 From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Prashant Bharucha [prashantbharu...@yahoo.ca] Sent: Wednesday, April 04, 2012 7:48 AM To: pgsql-general@postgresql.org Subject: [GENERAL] CPU Load 100% when pg_dump start in Postgresql 8.4 Hello Everyone I facing a big problem ,when pg_dump start .CPU load become 100%. DB Size 35 GB running with e commerce web site. Insert transaction record successfully but Update transaction is not going through. Could you please help to figure out where is the problem ? Thanks Prashant -- Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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 column order
You can specify the column order in the copy statement: psql -d test -c "create table ttt (id serial primary key, name varchar(10), value int);" echo "10|one" | psql -d test -c "copy ttt (value,name) from stdin with delimiter '|';" psql -d test -c "select * from ttt;" id | name | value +--+--- 1 | one |10 (1 row) HTH Brent Wood GIS/DBA consultant NIWA +64 (4) 4 386-0300 From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of MD33 [mdubosfo...@yahoo.com] Sent: Tuesday, May 08, 2012 12:33 AM To: pgsql-general@postgresql.org Subject: [GENERAL] COPY column order Hi there I'm trying to use COPY with HEADER option but my header line in file is in different order than the column order specified in database. Is the column name order necessary in my file ?? thxs -- View this message in context: http://postgresql.1045698.n5.nabble.com/COPY-column-order-tp5690950.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] Relational database design book
It might be useful to look at the capabilities of the Informix Timeseries
Datablade
(http://www-01.ibm.com/software/data/informix/blades/)
if you want to look at ways of enhancing the temporal data capabilities of
Postgres.
Cheers,
Brent
Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Chris Browne 12/16/08 10:05 AM >>>
rshep...@appl-ecosys.com (Rich Shepard) writes:
>
> [2] Strangely enough -- to me, at least -- the lack of full support for
> date- and time-based SQL in database tools such as PostgreSQL is puzzling.
> Virtually all business-related databases (think accounting systems as a
> prime example) depend on dates. So do many scientific databases.
The support for temporality in PostgreSQL seems above average as far
as I can see...
PostgreSQL has pretty nice time types between the timestamptz type and
interval.
What strikes me as being missing is the ability to create
temporally-aware foreign keys.
That is, suppose the schema is:
create table1 (
nearly_pk integer not null,
from_date timestamptz not null default now(),
to_date timestamptz not null default 'Infinity',
constraint dating_t1 check (from_date < to_date)
-- probably some other data...
);
I'd like to be able to do two more things:
a) Treat the date range as part of the primary key (which isn't
forcibly hard),
b) Have references to table1 that point to the time range for the
"nearly_pk" value but which are a little more liberal with the dates.
create table2 (
t2pk integer primary key,
nearly_pk integer not null,
from_date timestamptz not null default now(),
to_date timestamptz not null default 'Infinity',
-- And have a "foreign key" that requires that
-- for tuple in table2 the combination (nearly_pk, from_date, to_date)
-- is *contained* by relevant ranges of (nearly_pk, from_date, to_date)
-- on table1
foreign key (nearly_pk) references
table1(nearly_pk) with temporal (table2.from_date, table2.to_date)
contained_by (table1.from_date, table1.to_date)
);
I don't think the syntax there is necessarily quite right; I'm just
hoping to express the idea successfully.
I could presumably do this with a trigger; have been trying to avoid
that thus far.
There are, of course, other ways of treating temporality; that is part
of why it's early to treat this approach as worth putting into syntax.
--
output = ("cbbrowne" "@" "acm.org")
http://cbbrowne.com/info/finances.html
"When the grammar checker identifies an error, it suggests a
correction and can even makes some changes for you."
-- Microsoft Word for Windows 2.0 User's Guide, p.35:
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
NIWA is the trading name of the National Institute of Water & Atmospheric
Research Ltd.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to implement a foreign key type constraint against a not unique column
Hi, I have a table with a column of ID's (integer), these are unique except where they = -1 (column 1) I have a partial unique index where the value is not -1 to enforce this. I want to use this column as a foreign key on a column in another table (column 2), but cannot without a full unique index. Is there any way to add an equivalent constraint to a foreign key which restricts entries in column 2 to values in column 1? I tried a check where obs_id in (select id from ..), but subqueries are not supported in a check. I believe it is possible by using a table with nulls for the -1 values with a unique index on it as the foreign key, then a view which uses case or coalesce to present the nulls as -1, but this seems a cumbersome workaround. Thanks, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] Update table with random values from another table
I'm not sure if that query will do what you want, but to make it work, one
thing you might try, is to pre calculate the random values for each record,
then order by those, eg:
select trip_code, random() as rand from obs order by rand;
works for me, so the following might for you:
:
UPDATE
users
SET
t_firstname = x.firstname,
t_surname = x.lastname,
t_username = x.username,
t_contact = x.email
FROM
(select firstname, lastname, username, email, random() as rand
from testnames order by rand)
WHERE
t_firstname <> x.firstname;
Cheers,
Brent Wood
Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Rory Campbell-Lange 02/17/09 4:33 PM >>>
I have a test system for which I need to replace actual user's data (in
'users') with anonymised data from another table ('testnames') on
postgres 8.3.
The tricky aspect is that each row from testnames must be randomised to
avoid each row in users being updated with the same value.
I've been trying to avoid a correlated subquery for each column I am trying
to update, and instead trying the following, which never returns. There are
2000 records in testnames and about 200 in users.
UPDATE
users
SET
t_firstname = x.firstname,
t_surname = x.lastname,
t_username = x.username,
t_contact = x.email
FROM
(select firstname, lastname, username, email
from testnames order by random()) x
WHERE
t_firstname <> x.firstname;
Any help much appreciated
Rory
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
NIWA is the trading name of the National Institute of Water & Atmospheric
Research Ltd.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Appending \o output instead of overwriting the output file
Hi, Using \o to redirect output to a file from the psql command line, is there any way to have the output appended to the output file, rather than overwriting it? Thanks, Brent Woood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] Appending \o output instead of overwriting the output file
Thanks Tom, That will do trick. Perhaps \o+ as a future fix for this? Brent Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Tom Lane 02/18/09 7:46 PM >>> "Brent Wood" writes: > Using \o to redirect output to a file from the psql command line, is there > any way to have the output appended to the output file, rather than > overwriting it? This is pretty grotty, but it works: \o | cat >>target Maybe we should provide another way in future... regards, tom lane NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] Appending \o output instead of overwriting the output file
I'd be happy with either... >> is UNIX-ese for append, which is OK, & if anyone uses command line MSDOS/ >> command prompt, it does the same there. But if we are to follow this logic, >> the \o > file should overwrite/create, etc... which is perhaps a bit >> excessive. I think that having \o write to a file and \o+ add to the file is simple & intutive for those folk who aren't familiar with the command line. The + means \o is adding to a file rather than just (over)writing a file, which I find pretty consistent with + in the other \ commands. However, I think introducing a ">>" into \ syntax is new & different & quite inconsistent with the other \ commands. But if either can be added I'll be happy :-) I'll just have to wait for Novell to formally support whichever version provides it, which shouldn't be much more than 3 years or so after it is released... At home I can use it straight away ... Thanks, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Tom Lane 02/19/09 10:19 AM >>> John R Pierce writes: > Tom Lane wrote: >> "Brent Wood" writes: >>> Perhaps \o+ as a future fix for this? >> I'd prefer "\o >>file" but maybe I'm too steeped in unix-isms. > \o+ is reasonably consistent with the other \ command usages... Not really; none of the other commands interpret + as meaning "append to an existing file". They tend to take it as meaning "do something *in addition to* what you normally do", not to do something that is significantly different from the base command. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] surprising results with random()
Or perhaps: CREATE OR REPLACE VIEW test_view AS SELECT (random()*3)::int as test_value; At least in this case, that should give the same result. in this case 1/3 should be 1, 1/3 = 2 & 1/3=3 in your case 1/3 = 1, 1/2 the remainder (1/2 * 2/3 = 1/3) = 2, remaining 1/3 = 3 Although I'm guessing the original intent is to NOT generate an equal distribution, but I'm not sure what distribution is required. Cheers, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> ries van Twisk 02/24/09 12:13 PM >>> Jessi, should the function not look like this??? CREATE OR REPLACE VIEW test_view AS SELECT CASE WHEN random() < .3 THEN '1' WHEN random() < .5 THEN '2' ELSE '3' END AS test_value FROM client; On Feb 23, 2009, at 5:09 PM, Jessi Berkelhammer wrote: > Hi, > > I have a view in which I want to randomly assign values if certain > conditions hold. I was getting surprising results. Here is a (very) > simplified version of the view, which seems to indicate the problem: > > CREATE OR REPLACE VIEW test_view AS > SELECT > CASE > WHEN random() < . THEN '1' > WHEN random() < . THEN '2' > ELSE '3' > END AS test_value > > FROM client ; > > It seems this should generate a random number between 0 and 1, and set > test_value to '1' if this first generated number is less than .. > Otherwise, it should generate another random number, and set > test_value > to '2' if this is less than .. And if neither of the random > numbers > are less than ., it should set test_value to '3'. It seems to me > that there should be a relative even distribution of the 3 values. > > However when I run this, the values are always similar to what is > below: > > X_test=> select test_value, count(*) from test_view group by 1 > order by 1; > test_value | count > +--- > 1 | 23947 > 2 | 16061 > 3 | 32443 > > Why are there significantly fewer 2s? I understand that random() is > not > truly random, and that the seed affects this value. But it still > confuses me that, no matter how many times I run this, there are > always > so few 2s. If it is generating an independent random number in the > second call to random(), then I don't know why there are more so many > more 1s than 2s. > > Thanks! > -jessi > > -- > Jessi Berkelhammer > Downtown Emergency Service Center > Computer Programming Specialist -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] Uploading data to postgresql database
ogr2ogr can write most formats to most other formats. It can certainly write to a PostGIS database, & read KML., so if it can write it to shape, it can write direct to Postgis You just need to set your output format to postgis. Note: depending on where you got GDAL (ogr2ogr) from, it may or may not have PostGIS drivers compiled in, if it doesn't you can compile it yourself against Postgres/PostGIS to enable this on your platform. Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Subha Ramakrishnan 03/18/09 7:04 PM >>> Hi, Thanks for the reply. I did take a look at ogr2ogr which can convert kml to shape. But i was wondering if there's some direct way..:) And by upload I meant adding geometry data to the DB. Thanks & regards, Subha Stephen Frost wrote: > * Subha Ramakrishnan (su...@gslab.com) wrote: > >> So far, I have been using shp2pgsql to upload data from shape files. >> I don't want to convert it to shape and then upload it. >> > > Have you looked at ogr2ogr? It looks to support KML as a format, and > has PostGIS support, though I'm not sure if it can handle the direction > you're asking for. > > Stephen > - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. - 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] SRID conflict, PostgreSQL 8.3
This should be addressed to the Postgis list.
However, you are spatially joining two geometries, and they need be in the same
coordinate system.
The column "the_geom" has a defined SRID (spatial reference id) when created in
it's original table. Your hard coded POLYGON in the SQL below has a SRID of -1
(the last value in the argument).
You can fix this by either changing the -1 to be the same number as the SRID
specified for the_geom, or by setting this arbitrarily to -1 for this operation
(as below):
SELECT "whatever"
FROM "a view into 'catalog'"
WHERE ((TRUE AND TRUE) AND SetSrid("the_geom", -1) && GeometryFromText('POLYGON
((-83.28 26.07,
-83.28 28.26,
-81.59 28.26,
-81.59 26.07,
-83.28 26.07))', -1));
(This assumes that "the_geom" & the coordinates you specify in the query are in
fact in the same coordinate system
Note that SRID of -1 means unknown coordinate system.)
I'm not sure of the relevance of the (TRUE AND TRUE) in the where clause, it
seems redundant, as it will always return true.
Cheers,
Brent Wood
Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> csmith 03/21/09 8:57 AM >>>
Hello,
I serve a Geospatial IS project that for some years has used PostgreSQL
and PostGIS. A strategy/scheme that has worked for all previous
versions has failed with a recent upgrade to 8.3 (e.g. 8.3.6-1).
Relation "catalog" is a large geospatially-enabled aggregation of data
with a variety of SRID's imbedded within "the_geom" attribute values.
Querying a view into the "catalog" which describes a subset of it's
tuples with identical SRID's (the uniqueness of the SRID associated with
this view's data is easily demonstrable with an ad-hoc query) has always
worked smoothly. With the 8.3 engine, an error is introduced:
SELECT "whatever"
FROM "a view into 'catalog'"
WHERE ((TRUE AND TRUE) AND "the_geom" && GeometryFromText('POLYGON
((-83.28 26.07,
-83.28 28.26,
-81.59 28.26,
-81.59 26.07,
-83.28 26.07))', -1))
results in this error:
"Operation on two geometries with different SRIDs"
The result of the GeometryFromText routine has, of course, but one SRID,
thus the SRID from "the_geom" must be the culprit. It's as if the
query is considering tuples in "catalog" outside of the view's domain.
(note: I can offer further evidence of this behavior- removing all tuples
from "catalog" other than those returned by a query against the view
eliminates the conflict/error).
Can someone comment on this mystery/phenomenon vis-a-vis PostgreSQL
version 8.3 (PostGIS 1.3.5)?
Many thanks,
Christopher Smith
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
NIWA is the trading name of the National Institute of Water & Atmospheric
Research Ltd.
--
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] Proper entry of polygon type data
Hi Peter, If you want to use Postgres to store/manage/query spatial data, I strongly recommend you look at PostGIS, & not the native Postgres geometry types. Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Peter Willis 03/24/09 10:35 AM >>> Hello, I would like to use 'polygon' type data and am wondering about the entry format of the vertex coordinates. Are the coordinates of the polygon type to be entered one entry per polygon vertex, or one entry per polygon edge segment? For example: I have a triangle with vertex corners A, B, C. One entry per vertex format suggests INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Cx,Cy)) ); One entry per edge format suggests INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Bx,By),(Cx,Cy),(Cx,Cy),(Ax,Ay)) ); Which entry format is the correct one? If per vertex format is the correct one, do I need to 'close' the path by entering the first vertex again at the end of the list? ie: INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Cx,Cy),(Ax,Ay)) ); Thanks, Peter -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] Online Backups PostGre (rsync for Windows)
Hi There are a few rsync on Windows options, just google rsync windows One we've found works well is DeltaCopy, which may meet your requirements. Cheers, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Adam Ruth 05/02/09 1:01 PM >>> Cygwin comes with rsync on Windows. On 02/05/2009, at 4:06 AM, John R Pierce wrote: > Joshua D. Drake wrote: >> Well that's just it. Out of the box it doesn't actually work. >> PostgreSQL >> only gives you the facilities to roll your own PITR solution. You can >> look at PITR Tools: >> >> https://projects.commandprompt.com/public/pitrtools >> >> It doesn't quite work on Windows due to lack of rsync and signaling >> differences but could give you an idea of how to move forward with >> your >> own implementation. >> > > Quite possibly 'robocopy' from Microsoft somewhere (doesn't come > with windows, but was part of an admin kit or something) would be a > workable replacement for the rsync part. > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- 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] Statistics with PostgreSQL
> Mean is just sum(col)/count(col) You can also just use avg(col). Either way, be careful because nulls may not be treated as you want for such calculations. The stats package R can access Postgres databases, and can be used for robust statistical analyses of the data. See: http://sourceforge.net/projects/rdbi/ Cheers, Brent Wood ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] UltraSPARC versus AMD
On Sat, 23 Apr 2005, Uwe C. Schroeder wrote: > Well, you overlook one thing there. SUN has always has a really good I/O > performance - something far from negligible for a database application. > A lot of the PC systems lack that kind of I/O thruput. > Just compare a simple P4 with ATAPI drives to the same P4 with 320 SCSI drives > - the speed difference, particularly using any *nix, is surprisingly > significant and easily visible with the bare eye. > There is a reason why a lot of the financial/insurance institutions (having a > lot of transactions in their DB applications) use either IBM mainframes or > SUN E10k's :-) > Personally I think a weaker processor with top of the line I/O will perform > better for DB apps than the fastest processor with crappy I/O. > > i guess the "my $0.02" is in order here :-) > Given that "basic" SQL is getting more analytical in capability, esp if you look at PostGIS/Postgres or Oracle/Informix/DB2 with their respective spatial extensions, then spatial overlays with several tables with polygons with large no's of vertices can get cpu bound as well as the more traditional DB I/O bound limitations. But, I agree that generally I/O is a more typical db issue. Brent Wood ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] bulk loader
On Thu, 19 May 2005, Hrishikesh Deshmukh wrote: > Hi All, > > Is there a "bulk loader" in postgresql with which one can read in say > a tab delimited format text file. Before one does all one has to do is > create the table with text file column names as attributes, once it is > on DBMS world it will be a simple table (non-relational) See copy from memory, pretty much as in: cat | \ psql -d $DB -c "copy from STDIN [with delimiter ','];" You'll see that db users can't copy a file into a table but can copy STDIN, so this approach works well. Brent Wood ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] dbf to pgsql
On Thu, 4 Aug 2005, Piotr wrote: > Hi, > Im lookig for tool to regulary transfer data from dbf files into pgsql. > Would be excellent if the tool would have data tranformation > possibility. > A simplistic approach but workable would be to use the dbfinfo/dbfdump programs which are part of the shapelib tools at www.maptools.org. These allow you to dump the data description & data itself as text which could then be COPY'd into a table built in Postgres. An ODBC link may also work, but it's not something I've tried, (I have a feeling there is an ODBC driver for dbf files, if not then the above dumps can be opened with teh ODBC text driver & transferred to Postgres. Brent Wood ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Removing -'s (header) before records in psql
On Sun, 14 Aug 2005, CSN wrote: > Is it possible to get rid of the "header" of -'s when > selecting rows in psql? For fields with a lot of text, > it looks like: > All I'm familiar with is \t which will return only tuples, so you'll lose the headings as well. Otherwise pipe through sed/head/tail to filter appropriately? Brent Wood > select body from news where id=123; > -[ RECORD 1 > ]--! --- > > ---! --- > > ---! --- > > - > body | Additional details ... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Import File
On Tue, 23 Aug 2005, Rodrigo Africani wrote:
> Hi,
>
> I'm trying to import a sequence txt file.
> The file has fix columns ... i.e the first 10 positions is the primary key ...
> The comand copy need a delimitier but the file is seq without a delimitier.
> I look in the manual but i don't won't if it is possible.
> My problem is the file is very large ...
> 1G and I don't have a lot of memory to change the file and put delimitiers.
>
I'm not sure if you mean your input file is fixed field (eg, col1 = chars
1-10, col2=chars 11-15, etc) instead of having a specified character (eg,
"," or space or tab) between the columns.
With a delimiter, you can (very roughly):
cat | psql -c "copy from stdin;"
with fixed columns you can use awk:
cat | awk '{print "substr(1,10), ..." }' | psql -c "copy from
stdin;"
The only other inerpretation I can see from your description is that the
data is one column pet line, rather than one record per line.
In this case a simple script or program to assemble the lines into the
appropriate number of columns might be necessary, but it should be able to
read stdin and write to stdout so that you can still pipe (|) your file tp
psql.
Hope this helps,
Brent
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] SQL - planet redundant data
> > That is exactly what I want, and now I finally see how to do it (I > think!). However, it is a considerable amount of work to set this up > manually, plus, it has been a headache realizing how to get there at > all. I'm hoping that one or more of the developers think it would be a > good idea for PostgreSQL to perform an internal table optimization > process using run-length encoding. Imagine you could just throw all your > data into one table, run OPTIMIZE TABLE and you'd be done. With SQL > being all about tables I'm surprised this idea (or something even > better) hasn't been implemented already. There was a recent brief thread here on storing timeseries data, where the use of clustered indices for static tables was suggested. This might also be useful in your situation... Cheers, Brent Wood ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Howto create a plperlu function as user (not superuser)??
Hi, I have a PostGIS enabled Postgres database. For institutional (un)reasons I'm the database owner in all but reality. I'm writing a plperl function to get the lat & lon coords from a geometry and execute a system call to get the depth (or altitude) at a location from a global datamodel. So I and other users can do something like: update site set depth = depth(todeg(site_geom)); (where site_geom is a point geometry. The todeg function is plpsql to convert the point to lat long coords from any other projections, to match the coordinate system of the global grid. It works fine.) My problem is that the language needs to be plperlu (the unsecured implementation of plperl) to be allowed to execute the system call to get the depth at the specified location. To work, the plperlu function must be created by the superuser, who I assume is postgres. That is not me. Sigh. Is there any way I can develop (with the various "create or replace function" iterations this wiil probably require) this plperlu function as a non superuser? Thanks, Brent Wood ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Pushing result set to table on different machine?
On Tue, 13 Sep 2005, Jerry LeVan wrote: > Hi, > I recently added a linux/windows laptop to our home network. > > I have started Postgresql (8.0.3) on the laptop and have > "replicated" my main database on the laptop. (pg_dump, > ftp and pg-"undump"). > > I would like to keep them reasonably in sync. Slony is > overkill and I think the "mirror" contrib is possibly > overkill also. > > I have installed the dblink package and can easily "pull" > data from my main server back to the laptop. > > Is there a elegant way to "push" the data from the main > server to the laptop? I have not tried this with Postgres, but have done similar things with other databases and related packages. In the Postgres case, a script on the server which remotely runs a command on the laptop (ssh/rexec/rsh as you prefer) could run a copy from table (on the server) | copy to table from stdin (remotely on the laptop) Something to empty tables first might help, but any command can be set up to run on the laptop, but be invoked from the server. Data from a srever run command can, as above, be piped as input to a command run by the laptop (but started from/by the server) I don't know that I'd recommend it, but you may be able to rsynch the database directory. Set up the script & run it on the server whenever you want. Brent Wood ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Partial dates
On Tue, 13 Sep 2005, Joshua D. Drake wrote: > > > > ERROR: date/time field value out of range: "1997-10-00" > > HINT: Perhaps you need a different "datestyle" setting. > > CONTEXT: COPY Entry, line 1, column EntryDate: "1997-10-00" > > Well the easy solution is to just make the date a text type but that is > the wrong answer. The right answer is to fix the data set. > MySQL should never have allowed you do insert those dates in the first > place. I know that doesn't help you much but at some point > you are going to have to sanitize the data anyway. > Hmmm... given that our real world data, (currently in a commercial RDBMS but I have hopes :-) often has dates where we only have a month and year, is there any way a part of a timestamp can be null? I guess this also has indexing issues. Maybe some form of GIST would work. Sanitizing is one thing, inventing data to fit an incomplete value into a date datatype is not good practice. It would need some arbitrary standard to apply date/time arithmetic & queries. For example, if we wanted all values for 1987, a record from an unknown day in March 1987 would be in the result set. If we wanted all values from March 1987, similarly. All records since 13 March 1987 and the arbitrary rule would come into play. Probably excluded because we couldn't explicitly prove it should be included in the result set. Like other nulls get treated. In case anyone is interested, right now we store year, month & day and have a timestamp field where the entire field is null if any one part is unknown. Are there any better ways in Postgres? Brent Wood ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Howto create a plperlu function as user (not superuser)??
On Wed, 14 Sep 2005, Tom Lane wrote: > Brent Wood <[EMAIL PROTECTED]> writes: > > That is not me. Sigh. Is there any way I can develop (with the various > > "create or replace function" iterations this wiil probably require) this > > plperlu function as a non superuser? > > If you could, it would be a security hole, which we would fix with > utmost alacrity. Untrusted-language functions may only be created > by superusers. Pretty much what I expected but figured I'd ask :-) > > Can you compartmentalize the depth-accessing function as a small > plperlu function, and do all the interesting stuff in plain plperl > atop that? > Much of the preprocessing required is in plpgsql using PostGIS functions. The whole Perl thing is only about 10 lines long so not worth splitting if avoidable. The depth accessing command is a one line system call with coords as parameters. The rest just builds a bounding box for the point location so that the call to GMT restricts it's access to the terrain model to a few square degrees worth of data and not the entire few Gb :-) A bit faster that way! One option is another system with Postgres/PostGIS where I am superuser & can develop, then get the superuser of the working database to run the SQL's for me to create the functions. Or see if the situation can justify me getting superuser status. Working with code can be easier than wrestling with beauracracy :-) Thanks, Brent ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] plperl function to return nulls
I can't find a reference in the docs for this... I have a plperl function returning an int. The int is returned as the result of a system call. It is set to return a null if one of the inputs is null, but I can't see how to return a null if the result is indeterminate. The function currently returns a 0 instead. How do I stick an if in the function to return a null where appropriate? Thanks, Brent Wood ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Data Entry Tool for PostgreSQL
On Fri, 23 Sep 2005, Ian Overton wrote: > Hello, > > We have a web application using PHP, Linux and PostgreSQL. We need a > simple data entry tool to allow non-IT people to edit data in our database > through a web-browser. Are there any free or commercial data management > tools out there, not database management tools like pgAdmin. I would have thought a php appliction would be able to this fairly easily. My data entry scripts insert the new records with just a primary key, then iterate through the various fields using an update sql for each one which is not null. This sort of approach coul easily be used to populate an on-screen table using php, then update any changed fields as required. Cheers, Brent Wood k ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Oracle buys Innobase
On Sun, 9 Oct 2005, CSN wrote: > > Maybe this is a crazy idea, I don't know how > technically or legally feasible it is, but I really > like the idea of the two open-source communities > uniting to battle Oracle. > Two? I haven't used Firebird, but have heard lots of positive comments from users. Firebird/Postgres/MySQL together maybe? Or with all the embedded SQLlite users out there, perhaps all four :-) (& yes, I know there are still others) Cheers Brent Wood ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
