Re: [GENERAL] Why is there no object create date is the catalogs?

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: Envir

Re: [GENERAL] About COPY command (and probably file fdw too)

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

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

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

Re: [GENERAL] $foo $bar is BAD

asm, 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

Re: [GENERAL] Normal distribution et al.?

ks 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

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

Re: [GENERAL] Selecting top N percent of records.

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 m

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

Re: [GENERAL] Database INNOVATION

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

Re: [GENERAL] median for postgresql 8.3

he 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 avera

Re: [GENERAL] techniques for bulk load of spatial data

n 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

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 argumen

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 tab

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 >&g

Re: [GENERAL] Web 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 >

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 lang

Re: [GENERAL] advice sought - general approaches to optimizing queries around "event streams"

ne 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

Re: [GENERAL] synchronize DTAP

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

[GENERAL] Postgres char type inconsistency

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 spe

Re: [GENERAL] Partioning with overlapping and non overlapping constraints

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

Re: [GENERAL] partial time stamp query

t & 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

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

Re: [GENERAL] earthdistance

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

Re: [GENERAL] Divert triggers on materialized views

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

[GENERAL] Postgres development

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

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 c

Re: [GENERAL] Using tables in other PostGreSQL database

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

Re: [GENERAL] (FAQ?) JOIN condition - 'WHERE NULL = NULL'

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

Re: [GENERAL] pg_dump and schemas

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 pr

Re: [GENERAL] pg_dump and schemas

am 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 ha

[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

Re: [GENERAL] Temporary Tables and Web Application

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 Mor

Re: [GENERAL] Change view definition - do not have to drop it

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_tabl

Re: [GENERAL] 10 TB database

ciency 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 co

Re: [GENERAL] indexes on float8 vs integer

& 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

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

[GENERAL] psql crashing - don't know why

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

Re: [GENERAL] haversine formula with postgreSQL

ance_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

Re: [GENERAL] How to get variable out to shell script

lue 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 cons

Re: [GENERAL] db not dumping properly, or at least not restoring

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, Wellingto

Re: [GENERAL] bulk loading table via join of 2 large staging tables

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.

Re: [GENERAL] union of types in a different category

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

Re: [GENERAL] Multiple Schema in One DB

$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 Woo

Re: [GENERAL] Mysterious DB reset

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,

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

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 L

Re: [GENERAL] Linux vs FreeBSD

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

Re: [GENERAL] concatenating with NULLs

st=# 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=#

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 ot

Re: [GENERAL] Read MS-SQL data into Postgres via ODBC link?

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 W

Re: [GENERAL] Error Importing CSV File

t 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,

[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 thi

Re: [GENERAL] How to force select to return exactly one row

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 sele

Re: [GENERAL] To store and retrive image data in postgresql

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

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

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:

Re: [GENERAL] What's size of your PostgreSQL Database?

s 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: > > -

Re: [GENERAL] Oracle and Postgresql

anies 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

Re: [GENERAL] PostgreSQL TPC-H test result?

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:4

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 ha

Re: [GENERAL] postgres/postgis

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

[GENERAL] Defining string to represent null values in select

nd 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

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 thin

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

[GENERAL] how to best resync serial columns

e 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 change

Re: [GENERAL] how to best resync serial columns

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: > H

Re: [GENERAL] ordered pg_dump

is 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

Re: [GENERAL] how to "group" several records with same timestamp into one line?

nnels 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

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 consul

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

Re: [GENERAL] what Linux to run

u 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

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

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

Re: [GENERAL] COPY column order

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

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

[GENERAL] how to implement a foreign key type constraint against a not unique column

he -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 Institu

Re: [GENERAL] Update table with random values from another table

tname, 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 >>

[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

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,

Re: [GENERAL] Appending \o output instead of overwriting the output file

houldn'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: >&g

Re: [GENERAL] surprising results with random()

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 functi

Re: [GENERAL] Uploading data to postgresql database

y 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, Thank

Re: [GENERAL] SRID conflict, PostgreSQL 8.3

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 P

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

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

Re: [GENERAL] Statistics with PostgreSQL

ttp://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 mail

Re: [GENERAL] UltraSPARC versus AMD

nal 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

nce 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 bu

Re: [GENERAL] dbf to pgsql

ver 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

ell. Otherwise pipe through sed/head/tail to filter appropriately? Brent Wood > select bo

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

Re: [GENERAL] SQL - planet redundant data

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

[GENERAL] Howto create a plperlu function as user (not superuser)??

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

t 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

ase 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

[GENERAL] plperl function to return nulls

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

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

Re: [GENERAL] Oracle buys Innobase

ts 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: i

  1   2   >