Re: [GENERAL] Not Geography
I have Postgresql database that I would like to link to the geometry side of Postgis. (This has little comparison to geography) Does anyone know of a tool or documentation that could get me started?? Bob Bob, I don't quite understand your question. Are you not sure how to install PostGIS, how to integrate it in your existing datbase, or once you install how to get started using it? How to do it depends on the operating system you are using. Once you have it installed you may want to join the Postgis newsgroup for further help http://postgis.refractions.net/mailman/listinfo/postgis-users http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01 Hope that helps, Regina - The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
Re: [GENERAL] Not Geography
Try this http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01 As far as tools for viewing the data go, I would recommend Quantum GIS if you are a beginner. http://qgis.org/index.php?option=com_contenttask=viewid=65Itemid=71 Hope that helps, Regina From: Bob Pawley [mailto:[EMAIL PROTECTED] Sent: Tue 4/15/2008 1:54 PM To: Obe, Regina; PostgreSQL Subject: Re: [GENERAL] Not Geography I have PostgreSQL, c/w the geometry attachment, installed on Win XP. I need to get started using it. I am best able to do that by example, however a simple (geometry for dummies) description or a tool would also help. I am on the Postgis list but everyone there seems to be so geographically oriented that I wanted to query a larger audience. Bob - Original Message - From: Obe, Regina mailto:[EMAIL PROTECTED] To: Bob Pawley mailto:[EMAIL PROTECTED] ; PostgreSQL mailto:pgsql-general@postgresql.org Sent: Tuesday, April 15, 2008 10:50 AM Subject: RE: [GENERAL] Not Geography I have Postgresql database that I would like to link to the geometry side of Postgis. (This has little comparison to geography) Does anyone know of a tool or documentation that could get me started?? Bob Bob, I don't quite understand your question. Are you not sure how to install PostGIS, how to integrate it in your existing datbase, or once you install how to get started using it? How to do it depends on the operating system you are using. Once you have it installed you may want to join the Postgis newsgroup for further help http://postgis.refractions.net/mailman/listinfo/postgis-users http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01 Hope that helps, Regina The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. - The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
[GENERAL] Operator COMMUTATOR - how does postgresql use this information
Does PostgreSQL use the COMMUTATOR property of an operator to determine if flip-flopped arguments can be collapsed. I used to think it did until someone pointed it doesn't - For example in the below SELECT b.* FROM boszip b INNER JOIN landparcels l ON (b.the_geom l.the_geom AND l.the_geom b.the_geom AND l.the_geom b.the_geom ) WHERE l.gid = b.gid and b.gid = l.gid limit 1 If I look at the query plan - I see the plan has reduced things down to l.gid = b.gid AND (b.the_geom l.the_geom AND l.the_geom b.the_geom) Why is (b.the_geom l.the_geom AND l.the_geom b.the_geom) not reduced down to just b.the_geom l.the_geom even though is defined as the commutator of ? Thanks, Regina - The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
[GENERAL] HOW does 8.3 CREATE REPLACe .. FUNCTION ..COST work
I think I am missing something about how the new CREATE OR REPLACE
FUNCTION ...COST works or I am missing some setting in postgresql conf.
I was hoping I could use it to control the function that is used in
cases where only one needs to be evaluated. Regardless of what I do it
seems to always evaluate the first function in the list. I'm running on
PostgreSQL 8.3.0, compiled by Visual C++ build 1400
Here is an example of my test: Functions and tables
CREATE TABLE log_call
(
fn_name character varying(100) NOT NULL,
fn_calltime timestamp with time zone NOT NULL DEFAULT now()
)
WITH (OIDS=FALSE);
CREATE OR REPLACE FUNCTION fn_pg_costlyfunction()
RETURNS integer AS
$$
BEGIN
INSERT INTO log_call(fn_name) VALUES('fn_pg_costlyfunction()');
RETURN 5;
END$$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION fn_pg_cheapfunction()
RETURNS integer AS
$$
BEGIN
INSERT INTO log_call(fn_name) VALUES('fn_pg_cheapfunction()');
RETURN 5;
END$$
LANGUAGE 'plpgsql' VOLATILE
COST 1;
--- Now for the test -
--Test 1: This shows that fn_pg_costlyfunction() is the only function
that is run -
-- unexpected to me shouldn't no function be evaluated or the cheap one?
--What's the difference between Test 1 and Test 2 that makes Test 2 do
the RIGHT thing?
TRUNCATE TABLE log_call;
SELECT (fn_pg_costlyfunction() 2 OR fn_pg_cheapfunction() 2 OR 5
2);
--Test 2: This works as I would expect - shows that none of the
functions are run presumably its going straight for 5 2
--becuase it recognizes its the cheapest route
TRUNCATE TABLE log_call;
SELECT foo.value
FROM (SELECT (fn_pg_costlyfunction() 2 OR fn_pg_cheapfunction() 2 OR
5 2 ) as value) as foo
--Test 3: It always runs the first function even though the cost of the
first is higher than the second
(in this case log_call contains fn_pg_costlyfunction())
TRUNCATE TABLE log_call;
SELECT foo.value
FROM (SELECT (fn_pg_costlyfunction() 2 OR fn_pg_cheapfunction() 2)
as value) as foo;
TRUNCATE TABLE log_call;
SELECT (fn_pg_costlyfunction() 2 OR fn_pg_cheapfunction() 2) as
value;
--Test 4: It always runs the first function even though the cost of the
first is higher than the second
(in this case log_call contains fn_pg_cheapfunction())
TRUNCATE TABLE log_call;
SELECT foo.value
FROM (SELECT (fn_pg_cheapfunction() 2 OR fn_pg_costlyfunction() 2 )
as value) as foo;
TRUNCATE TABLE log_call;
SELECT (fn_pg_cheapfunction() 2 OR fn_pg_costlyfunction() 2 ) as
value;
Thanks,
Regina
-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
Re: [GENERAL] HOW does 8.3 CREATE REPLACe .. FUNCTION ..COST work
--Test 2: This works as I would expect - shows that none of the functions are run presumably its going straight for 5 2 --becuase it recognizes its the cheapest route TRUNCATE TABLE log_call; SELECT foo.value FROM (SELECT (fn_pg_costlyfunction() 2 OR fn_pg_cheapfunction() 2 OR 5 2 ) as value) as foo That's just constant-folding: x OR TRUE is TRUE. It has exactly zero to do with the cost of anything. Offhand I think the behavior you are looking for of choosing to run more expensive subexpressions later only occurs for top-level WHERE clauses that are combined with AND. regards, tom lane Tom thanks for the clarification - based on your comment I verified with these -- fn_pg_cheapfunction() is the only one run as you predicted TRUNCATE TABLE log_call; SELECT true as value WHERE (fn_pg_costlyfunction() 2 AND fn_pg_cheapfunction() 5 ); -- fn_pg_costlyfunction() is the only one run - again as predicted by your statement TRUNCATE TABLE log_call; SELECT true as value WHERE (fn_pg_costlyfunction() 2 OR fn_pg_cheapfunction() 2 ); It would be really nice if this worked with OR as well. Is it just much harder to deal with the OR case in the planner or was there some other reason why the OR case was left out? Thanks, Regina - The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_ctl start fails on Windows
Could still be a permission problem. Whatever NT account you are using as the service account (usally called postgres) for the PostgreSQL server is the one that needs to be able to create the postmaster.pid. Make sure that account has full rights to the data folder. Hope that helps, Regina -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Abraham, Danny Sent: Monday, December 24, 2007 9:38 AM To: pgsql-general@postgresql.org Subject: [GENERAL] pg_ctl start fails on Windows The error message is could not open PID file. I can manually create the file postmaster.pid, so the problem Does not look like a permission problem. Thanks Danny ---(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 - The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How can I insert NULL into column with the type of timestamp?
Actually what you are doing below is trying to stuff '' in a timestamp
field.
Keep in mind '' and NULL are not the same. '' is invalid for timestamp
where as NULL is fine. Your example should be
insert into T_Admin(name,key,regDate,isLock,realName)
values('aaa','aaa',NULL,'1','aaa');
I think the bcp is trying to insert 'NULL' instead of NULL.
According to the docs - looks like you can tell copy that
http://www.postgresql.org/docs/techdocs.15
So my guess is you should do
copy t_admin from /home/postgres/data/admin.txt USING DELIMITERS '\t'
WITH NULL As 'NULL'
Hope that helps,
Regina
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of bookman bookman
Sent: Monday, December 10, 2007 9:29 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How can I insert NULL into column with the type of
timestamp?
H i ,
I want to tansfer a database from sqlserver2005 to postgresql8.2.5.I
use bcp to export a table named admin in sqlserver to a text file:
--table T_admin
id name key regDate
isLock realName
1 rison 9988772007-08-27 10:24:57 False
admin
2 lijun 778899NULL
False NULL
3 guanliyuan112007-11-05 10:30:08 False
myAdmin
--admin.txt
id name key regDate
isLockrealname
1 ris 998877 2007-08-27 10:24:57.000 0 admin
2 lij 778899 0
3 guanliyuan 11 2007-11-05 10:30:08.813 0
myAdmin
I created a table in postgresql,and I use copy to import datas
create table T_Admin(
adminID serial not null primary key,
name varchar(30) null,
key varchar(30) null,
regDate timestamp null,
isLock bool null,
realName varchar(30) null
)
copy admin from /home/postgres/data/admin.txt
Then error occured:
error:invalid input syntax for type timestamp:
context:copy T_Admin ,line 2,column regDate:
It seemed that the column REGDATE cannot accept a NULL.I tested it use:
insert into T_Admin(name,key,regDate,isLock,realName)
values('aaa','aaa','','1','aaa');
The same error occured.
So it means that the column with type timestamp cannot accept a NULL
..Is there any way I can tansfer this table into postgre?How can i deal
with NULL in this case?
Thank you!
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
---(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] Hijack!
Well said Greg. I have the same problem too of having a crippled mail reader :) Really I find mid posting hard to follow especially if I'm the one that posted the question. I hope we aren't going to hit people with hammers over this minor infraction. It really makes one feel unwelcome. I guess we have beaten this horse enough though. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gregory Williamson Sent: Tuesday, December 11, 2007 12:04 PM To: Joshua D. Drake; [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Hijack! -Original Message- From: [EMAIL PROTECTED] on behalf of Joshua D. Drake Sent: Tue 12/11/2007 9:43 AM To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Hijack! -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 11 Dec 2007 16:31:40 + Raymond O'Donnell [EMAIL PROTECTED] wrote: // Please note in particular the following points of netiquette: * Don't top-post, as it makes for confusing reading. * Don't start a new thread by replying to an old one, because [insert suitable technical explanation here]. Failure to observe the above may result in your question going unanswered. // O.k. this might be a bit snooty but frankly it is almost 2008. If you are still a top poster, you obviously don't care about the people's content that you are replying to, to have enough wits to not top post. However, I would also note that in windows world, it is very common to top post. I am constantly retraining very smart, just very ignorant customers. * Not all mail clients deal well with inline/bottom quoting (manually added to lines here since my mail reader does not do so automatically -- imagine doing so for a complex quote!) * Top posting is very common in companies with lots of blackberry (etc) users since they seem to see only tops easily. * my mail client *always* starts at the top of the message. For rapid/internal mails top posting works better because the answer/most recent is always at the top. Complex messages do deserve in-posting but not always easy, especially if you have to do it manually). Does your mail browser always start at the bottom ? I always see the top of a message first. Simple threads work very well this way -- complicated ones collapse under top-posting. * a lot of us have to use what ever the company provides as mail server. Exchange sucks but I'd rather not quit my job just because _you_ have a problem reading mail that does not conform to the T to your expectations. And there is a limit to how much time I want to spend manually formatting your mail to respond to it. Note that a lot of postGIS mail list posts are top-posted and the complaint rate is vanishingly small. Yet somehow business clanks on. Imagine that! And I can't even use exchange/outlook -- web interface to Micro$soft really sucks. * Try to see the world from a perspective other that your own (admittedly superior) one ! Not everyone is so advanced. * Get a life -- how people post is _trivial_. *content* over *form* ! Beating dead horses is of no interest other than the inherent joy in the thing. Deal with the fact that an open mail ist will have users from *all* backgrounds and origins and it you can't make everything a fight. Pick the most important battles. Top-posting is not the worst sin. (not reading the manuals is the by the worst transgression, IMHO). And for those who really care, email etiquette in painful detail here http://tools.ietf.org/html/rfc1855. Hijacking seems to be more of a Bozo No-No than top posting. Or maybe that's just me. Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.)
Re: [GENERAL] Simpler dump?
I think PgAdmin ,in 1.8 at least, is by default set to hide system objects like the template databases. To enable this go to File-Options-Display and make sure to check the Show system objects in treeview. You may want to check some of the other options as well. Hope that helps, Regina -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Douglas McNaught Sent: Monday, December 10, 2007 11:27 AM To: Ted Byers Cc: Richard Huxton; Tino Wildenhain; Uwe C. Schroeder; pgsql-general@postgresql.org Subject: Re: [GENERAL] Simpler dump? On 12/10/07, Ted Byers [EMAIL PROTECTED] wrote: So, how do I determine whether or not template1 really exists on my server and is a copy of template0 (as I'd infer from what I see in postgres) rather than template_postgis, and then modify things so that the default is the normal template1 rather than template_postgis, but leaving the latter in place so I can use it when I need it? Try using the 'psql' command line tool to list your databases--it sounds like pgAdmin might be hiding some of them from you (which isn't the fault of anyone on this list since pgAdmin is a separate project). It's certainly conceivable that someone before you set up template_postgis as the default template database, but getting an accurate catalog of what you've got sounds like the first step. -Doug ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ - The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Script to reset all sequence values in the a given DB?
Did you want to set to a specific known value or the min value of the
sequence. I think Pavel's sets to the min value of the sequence.
The below sets all the sequences to the same value
CREATE AGGREGATE sum ( BASETYPE = text,
SFUNC = textcat,
STYPE = text,
INITCOND = '' );
CREATE OR REPLACE FUNCTION cp_resetsequences(resetto integer)
RETURNS void AS
$BODY$
BEGIN
EXECUTE (SELECT SUM('ALTER SEQUENCE ' || sequence_schema || '.'
|| sequence_name || ' RESTART WITH ' || CAST(resetto As varchar(50)) ||
'; ' )
FROM information_schema.sequences);
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--Note this will set all the sequences in the database to 150
SELECT cp_resetsequences(150);
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Pavel Stehule
Sent: Monday, December 10, 2007 4:33 PM
To: Nathan Wilhelmi
Cc: PGSQL Mailing List
Subject: Re: [GENERAL] Script to reset all sequence values in the a
given DB?
On 10/12/2007, Nathan Wilhelmi [EMAIL PROTECTED] wrote:
Hello - Does anyone happen to have a SQL script or function that can
reset all the sequence values found in a given DB? When we rebuild the
DB it would be handy to be able to set all the sequence back to a
known
starting place.
create or replace function resetall()
returns void as $$
declare
v varchar;
m integer;
begin
for v in
select n.nspname || '.' || c.relname
from pg_catalog.pg_class c
left join
pg_catalog.pg_namespace n
on n.oid = c.relnamespace
where c.relkind = 'S'
loop
execute 'select min_value from '||v into m;
setval(v, m, false);
end loop;
return;
end; $$ language plpgsql;
Regards
Pavel Stehule
Thanks!
-Nate
---(end of
broadcast)---
TIP 5: don't forget to increase your free space map settings
---(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
-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] simple update on boolean
You could use a COALESCE instead of a case statement for simple case like this. The below will treat a NULL as false and then when you do not it becomes true. So NULLS will be set to true UPDATE boolean_column SET boolean_column = NOT COALESCE(boolean_column, false) hope that helps, Regina -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ivan Sergio Borgonovo Sent: Thursday, December 06, 2007 10:19 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] simple update on boolean On Thu, 06 Dec 2007 14:53:13 +0100 Cedric Boudin [EMAIL PROTECTED] wrote: What if boolean_column is NULL? btw set bolean_column= not bolean_column works as expected. template1=# select (not 't'::boolean),(not 'f'::boolean),(not NULL::boolean); ?column? | ?column? | ?column? --+--+-- f| t| (1 riga) If it was null before it has to be null afterwards (IMHO). If you don't want to have null, take care of it somewhere else but not here. That is the as expected part. The case case, other than being more verbose, do more than what I would expect since all NULL are converted to t. template1=# select case when NULL then 'f'::boolean else 't'::boolean end; case -- t (1 riga) -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 6: explain analyze is your friend - The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. ---(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] stored procedures and dynamic queries
As a side note, there is actually a book on design patterns in SQL, although I personally haven't read it. From the reviews I recall reading about it, I think its mostly based on Oracle Features. Still might be a good read as far as PostgreSQL is concerned except for the sections on Graphs and recursive trees since Oracle has special syntactical sugar for that kind of stuff that is unique to Oracle. http://www.rampant-books.com/book_2006_1_sql_coding_styles.htm Hope that helps, Regina -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ted Byers Sent: Tuesday, December 04, 2007 9:59 AM To: Richard Huxton; Ivan Sergio Borgonovo Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] stored procedures and dynamic queries --- Richard Huxton [EMAIL PROTECTED] wrote: Ivan Sergio Borgonovo wrote: On Tue, 04 Dec 2007 08:14:56 + Richard Huxton [EMAIL PROTECTED] wrote: Unless it's an obvious decision (millions of small identical queries vs. occasional large complex ones) then you'll have to test. That's going to be true of any decision like this on any system. :( I'm trying to grasp a general idea from the view point of a developer rather than a sysadmin. At this moment I'm not interested in optimisation, I'm interested in understanding the trade off of certain decisions in the face of a cleaner interface. Always go for the cleaner design. If it turns out that isn't fast enough, *then* start worrying about having a bad but faster design. I don't know about clean, but the mantra here is First you make it provably correct, then you make it fast. I am a fan of making things as simple as practicable, BUT NO SIMPLER. I don't know how that meshes with clean, but the general idea is right. If you look at my code, my C++ and my java code is infinitely better than my SQL, so with either of those, I end up making fewer variants, and my starting point is generally much closer to my end point, and that is just a function of experience. With my SQL code, I generally find myself producing a variety of scripts to support a given task. And these include exploration of just about everything SQL has to offer. I TRY everything, from joins to correlated subqueries to stored procedures to views, and beyond. And I am still trying to develop an intuition as to which options will serve best in a given situation, analogous to design patterns I routinely use in my C++ and Java code. Your reaction to Richard's advice to test seems to imply you want a more direct approach to THE answer. That is chasing something that doesn't exist! I tell you, as a developer (and NOT a sysadmin), there is no substitute for experience and THAT can ONLY be obtained through trial and error. I haven't seen any books about design patterns in SQL (while there are plenty in C++ and Java, and other languages), so there is no short cut. In short, the general idea most developers I know work with is code and test, and then code again and test again, until you've tried the variety of options that exist. There is no substitute for testing yourself. I have seen repeated advice to replace correlated subqueries by left joins, claiming that ubiquitous experience is that the joins are faster, and yet, in my tests, in most cases there was little difference in speed while in others the correlated subqueries were faster. So it appears the advice was based on experience with dated software and the RDBMS in question had subsequently dramatically improved how it handled correlated subqueries. And my use of EXPLAIN confirmed what I was finding through testing. Most of the documents available are from a sysadmin point of view. That makes me think that unless I write terrible SQL it won't make a big difference and the first place I'll have to look at if the application need to run faster is pg config. The whole point of a RDBMS is so that you don't have to worry about this. If you have to start tweaking the fine details of these things, then that's a point where the RDBMS has reached its limits. In a perfect world you wouldn't need to configure PG either, but it's not that clever I'm afraid. I am not sure I buy this, if I properly understand it. Trust me, I have written some really bad but simple queries that took hours to complete a task that was completed in less than a minute with smarter code. And yet the bad code I'd written was similar in nature to examples used in some texts to explain ideas in SQL. The point is, until you get extensive experience in SQL programming and optimization, you won't know what is bad code until you test it. Personally, I rely on the sysadmin to administer the RDBMS properly, to ensure it is configured appropriately for our application, AND I ask his or her advice and input on how I design and implement my SQL code, as well as for input on distributed application architecture. You can't do it all. On my
Re: [GENERAL] [NOVICE] How to split a table?
I would do select * into mynewtable frommyoldtableORDER by random() LIMIT 15000 where 15000 in this case is your table row count*.6 If you want to create another table with 40% of the remaining data then something like select * into mynewtable2 from myoldtable where myoldtable.primarykey NOT IN(select primarykey from mynewtable) ] In this case primarykey you would replace with the primary key field of your table. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Felix ZhangSent: Tuesday, October 17, 2006 3:39 AMTo: pgsql-sql@postgresql.org; pgsql-general@postgresql.org; [EMAIL PROTECTED]Subject: [NOVICE] How to split a table? Hi, I want to split a table to 2 small tables. The 1st one contains 60% records which are randomly selected from the source table. How to do it? Regards, Felix The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
Re: [GENERAL] [postgis-users] postgresql8.0 and postgis1.0.0
The short answer is that you leave out all the postgis function calls in your dump, create a new database and load the postgis functions using the new scripts. Then you just load the data. I didn't find an easy way to selectively load tables and views using the .sql format (and I actually didn't want to bring over some old junky tables I had) so I opted for the dumping using .tar format. Not sure if this is the easiest way, but this is what I did. 1) Make sure you have the new postgis installed and have run the lwgeom, spatial_ref.sql files in your new db 2) I used the new pg_dump util (from 8.0 to dump the old db) - I'm not sure what happens if you use the old one. Anyrate the general command was pg_dump -h myoldpgserver -n public -F t myolddb -f mydata.tar (you might need to change the above a bit if you are backing up from a non-local pc that does not have a trust relationship with your old server) 3) pg_restore --list mydata.tar restoreitems.txt (this gives you a list of the items backuped up - which you can then edit to only restore the stuff you want) 4)Edit the restoreitems.txt - cutting out all references to postgis functions and any other stuff you don't want to restore 5) pg_restore --use-list=restoreitems.txt --dbname=mynewdb --username=postgres mydata.tar (here I was on the real server so had no need for the -h flag) -Original Message- From: Pritesh Shah [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 16, 2005 4:09 AM To: pgsql-general@postgresql.org; [EMAIL PROTECTED] Subject: [postgis-users] postgresql8.0 and postgis1.0.0 hi, I'm trying to backup a server and restore it on a different machine with newer versions of postgresql and postgis. Dumps have been created for the following versions from the old database server: Postgresql 7.4.6 Postgis 0.8.2 Now since both the packages have released newer versions i've installed the following on my newer machine where i would like to restore the dumped databases. Postgresql 8.0.1 and Postgis 1.0.0 For restoring the information i'm using psql -e -f abc.sql template1 While restoring the dumps i've collected the following information where the problem occurs: .. .. .. CREATE FUNCTION histogram2d_in(cstring) RETURNS histogram2d AS '$libdir/libpostgis.so.0.8', 'histogram2d_in' LANGUAGE c STRICT; psql:abc.sql:3947: ERROR: could not access file $libdir/libpostgis.so.0.8: No such file or directory .. .. .. I understand that this is due to the following: libpostgis.so.8.0 is now liblwgeom.so.1.0 and also histogram2d_in is now lwhistogram2d_in histogram2d_out is now lwhistogram2d_out and so on. Now my problem is there are a lot of databases that use the postgis stuff (like the histogram2d_in) which has changed from the older version to the newer version. What do i do to overcome this problem?? Can somebody help me out with this?? Cheers, Pritesh ___ postgis-users mailing list [EMAIL PROTECTED] http://postgis.refractions.net/mailman/listinfo/postgis-users ---(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
