Re: [GENERAL] Postgis error
Hi, for adding a geometry-column to an existing table use one of the postgis-functions select AddGeometryColumn(...); described in: http://postgis.refractions.net/documentation/manual-1.5/AddGeometryColumn.html Ludwig Am 17.01.2011 18:35, schrieb Plata Martínez, Álvaro (KNMI): Hi, Using postgreSQL 8.4 and Postgis 1.4 in an Ubuntu 10.04 Server, when trying to create a table: CREATE TABLE observation ( (...) spatial_value geometry, ); I get this error message: ERROR: type geometry does not exist I supposed that installing postgis I would get the geographical object, so I am not sure if I have to do anything else. Thanks in advance for your help, Alvaro. -- 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] PostGIS return multiple points
Just some thoughts:
- create a multipoint with 100 vertices instead of a single point and query
once with st_Intersect
- prepare the single-point-query and execute the prepared query 100 times with
the changing coordinates
Ludwig
- Ursprüngliche Nachricht -
Von: trevor1940
Gesendet: 28.10.10 10:00 Uhr
An: pgsql-general@postgresql.org
Betreff: [GENERAL] PostGIS return multiple points
Hi I have a PostGIS table and I wish to get the location/name of multiple
points at once the command for selecting one point is select PolyName from
MyPolygones where st_Contains(the_geom, GeomFromText('point($LAT $LONG)4326');
where $LAT $LONG are perl varables So how can i do this if iI have 100 points
without hitting the database 100 times? -- View this message in context:
http://postgresql.1045698.n5.nabble.com/PostGIS-return-multiple-points-tp3240107p3240107.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
Re: [GENERAL] Could not Store French Accent Marks Correctly in Postgres
Am 20.08.2010 23:10, schrieb Wang, Mary Y:
Hi,
I'm having a problem right now. Some of our French users uploaded
some files with file names that had French accent marks, and those
file names were inserted into the Postgres database. When I examined
the value of those file names, they all had some weird characters (the
weird characters were in the same position where the accent marks were
entered). I do not know how to handle this kind of situation. Most
of my users are US based, but I have been told that there will be more
international users in the future.
So my questions are:
(1) What is the best character encoding that would work for most
of those languages that have accent marks?
(2) I assume that I also need to do some kind of conversion in the
front end (PHP) as well.
I'm running on Linux and Postgres 8.3.8.
Any ideas?
Thanks in advance.
Mary Wang
Hi,
our solution for storing uploaded files in database/filesystem with php
uses utf-8 for the filenames in the database in combination with
string-replacement for some special characters in php. These are in our
case the german Umlaute (ä,ö,ü,ß), because otherwise we get the
problem of strange translations of these characters (php uses utf-8,
german windows uses cp-1250), that made them unusable for
download-links. You can use the function below, just add your special
characters to the $trans-array. As another benefit this function returns
unique filenames that can be used for storing the files in a
target-directory.
SNIP
public static function get_unique_file_name($target_dir,
$current_file_name){
$trans = array (ä = ae, ö = oe, ü = ue, ß = ss,
Ä = Ae, Ö = Oe, Ü = Ue);
target_file_name = strtr($current_file_name, $trans);
$i = 0;
$old_target_file_name = $target_file_name;
while(file_exists($target_dir . '/' . $target_file_name)){
$i++;
$target_file_name = $i . $old_target_file_name;
}
return $target_file_name;
}
/SNIP
Ludwig
Re: [GENERAL] to_date function
Hi,
on a Windows/PostgrSQL 8.4 three from your queries throw errors (sorry,
but they are in german):
select to_date('01/04/2013 23:59:59','DD/MM/ HH24:Mi:SS');
= FEHLER: ungültige Kombination von Datumskonventionen
HINT: Die Gregorianische und die ISO-Konvention für Wochendaten können
nicht einer Formatvorlage gemischt werden.
select to_date('01/04/2013 23:59:59','DD/MM/ HH24:mi:SS');
= 2013-04-01
select to_date('01/04/2013 23:59:59','DD/MM/ HH24:mI:SS');
= same error as above
select to_date('01/04/2013 23:59:59','DD/MM/ hH24:MI:SS');
= FEHLER: ungültiger Wert »:5« für »MI«
DETAIL: Der Wert muss eine ganze Zahl sein.
Regards
Ludwig
Am 07.07.2010 23:07, schrieb Carlos Henrique Reimer:
Hi
I've a Linux box running postgresql 8.2.17 and facing some strange
results from the to_date function.
As you can see in the following tests the problem occurs when the
template used includes upper and lower case characters for the minute
(Mi or mI).
Am I using the incorrect syntax or is it a bug?
Thank you in advance!
template1=# select to_date('01/04/2013 23:59:59','DD/MM/
HH24:Mi:SS') ;
to_date
2009-04-01
(1 row)
template1=# select to_date('01/04/2013 23:59:59','DD/MM/ HH24:mi:SS')
;
to_date
2013-04-01
(1 row)
template1=# select to_date('01/04/2013 23:59:59','DD/MM/ HH24:mI:SS')
;
to_date
2009-04-01
(1 row)
template1=# select to_date('01/04/2013 23:59:59','DD/MM/
hH24:MI:SS') ;
to_date
2013-04-01
(1 row)
--
Reimer
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Synchronize filenames in table with filesystem
Hi List, not another question on how to store files (db or filesystem), i decided to use the filesystem. I'm now searching for a trigger, that deletes the physical file when deleting a database-record containing the filename in one of its fields. Is there a sample somewhere how this could be done? I'm runnig PG 8.4 on a windows machine. Regards Ludwig -- 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] problem with array query
I don't know about Postgres 8.3, but with 8.4-docs the Syntax of your
query could be
select * from tblretrain where NOT ('ms-ap-t2-02c9' = ANY (owners));
regards
Ludwig
Grant Maxwell schrieb:
Hi Folks
According to the 8.3 docs I should be able to write:
select * from tblretrain where 'ms-ap-t2-02c9' NOT IN (owners);
where owners is an array per the following definition
CREATE TABLE tblretrain
(
pkretrainid integer NOT NULL,
mailid integer NOT NULL,
train_to smallint NOT NULL,
owners character varying(1024)[],
bayes_trained boolean DEFAULT false,
contents text NOT NULL,
CONSTRAINT tblretrain_pk PRIMARY KEY (pkretrainid)
)
The problem is that it generates an error:
ERROR: array value must start with { or dimension information
** Error **
ERROR: array value must start with { or dimension information
SQL state: 22P02
It seems as though postgres is not recognising owners as an array.
Any suggestions please ?
regards
Grant
--
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 match sets?
Hello,
this query on the two tables you suggested (named test_left and
test_right) returns the correct result without transformations:
select distinct
t1.unit
from
test_left as t1 inner join
test_left as t2 on t1.unit = t2.unit and t1.token != t2.token and
t1.exponent != t2.exponent
inner join test_right as t3 on t1.token = t3.token and t1.exponent =
t3.exponent
inner join test_right as t4 on t2.token = t4.token and t2.exponent =
t4.exponent;
Regards
Ludwig Kniprath
Alban Hertroys schrieb:
Greetings!
I'm having some troubles creating a query, or rather, I can write one
that works but the approach feels wrong! The problem at hand boils
down to finding a record in a group where each result of two
result-sets matches on some columns.
The actual data I need to match isn't directly from tables but both
sides of the equation are the results of a set-returning function that
breaks up a unit string into separate tokens (base-unit exponent).
An example of the two sets I need to join are, at the left hand side:
unit | token | exponent
---+---+--
m.s^-1 | m | 1
m.s^-1 | s | -1
m.s^-2 | m | 1
m.s^-2 | s | -2
And at the right hand side:
token | exponent
---+--
m | 1
s | -2
The goal of the query is to find which unit at the left hand side
matches all the tokens and exponents at the right hand side, which
would be 'm.s^-2' in the above example. The order in which the tokens
are returned can be random, there isn't really a defined order as it
doesn't change the meaning of a unit.
I do have a possible solution using array_accum [1][2] on an ordered
version (on unit,token,exponent) of these sets. It's not a pretty
solution though, I'm not happy with it - it's a transformation (from a
set to an array) where I feel none should be necessary. Isn't there a
better solution?
To illustrate, I'd prefer to perform a query somewhat like this:
SELECT unit
FROM unit, tokenize_unit('m.s^-2') AS token
WHERE each(unit.token) = each(token.token)
GROUP BY unit;
But I'm pretty sure it's not possible to use aggregates in the
WHERE-clause.
Definitions for the above are:
CREATE TYPE unit_token AS (
unittext,
exponentint
);
CREATE OR REPLACE FUNCTION tokenize_unit(unit text)
RETURNS SETOF unit_token
AS '@MODULE_PATH@', 'tokenize_unit_text'
LANGUAGE C IMMUTABLE STRICT;
CREATE TABLE token (
unittextNOT NULL REFERENCES unit,
tokenunit_token NOT NULL
);
[1] array_accum is an aggregate from the documentation that transforms
a set into an array.
[2] The SRF's actually return a type unit_token(token text, exponent
int) which makes using array_accum and comparisons easier.
Regards,
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4aacebc413788472316367!
--
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] 8.3: timestamp subtraction
Scott Marlowe schrieb:
On Sat, May 23, 2009 at 7:18 AM, Christophe x...@thebuild.com wrote:
On May 23, 2009, at 9:13 AM, Daniel Verite wrote:
I don't know why this query returns false:
SELECT '20040506 070809.01'::timestamp(6) - '20010203
040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3
millisecond'::interval;
If I just subtract the two timestamps, its result is the interval I
specified.
What may cause this?
It works for me:
test= SELECT '20040506 070809.01'::timestamp(6) -
'20010203 040506.007000'::timestamp(6)=
'1188 day 3 hour 3 minute 3 second 3 millisecond'::interval;
?column? --
t
(1 row)
Could this be due to the OP's build of PG using floating point timestamps?
That's what I'm thinking
Me too, a testquery-result on a Windows-System with version PostgreSQL
8.3.0, compiled by Visual C++ build 1400:
SELECT ('20040506 070809.01'::timestamp(6) - '20010203
040506.007000'::timestamp(6) - '1188 day 3 hour 3 minute 3 second 3
millisecond'::interval) * 1e10;
= -00:01:28.220986
--
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] ask: select right(column) ???
Hello Hendra, there is no function right(column, n-Chars), but you can use substring(column-name from offset for num_chars) in combination with char_length for getting the right-n-characters as f. e.: select substring(column from (char_length(column) - 3) for 4) from table Ludwig Dear all, I have simple question I tried following code select right(column, number_of_character) from table but it didn't work, saying that pg doesn't have the function is there any way to achieve such output? honestly I have no idea that such simple feature doesn't exist in postgresql or am I wrong? since I look at SQL Key Words table and it's written as reserved Thank you Regards Hendra -- 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] inconsistency in aliasing
I don't know, if this is an inconsistence or a bug, but here a possible workaround: select current_timestamp::abstime::int4 as score, current_timestamp::abstime::int4 + 1 as score + 1 order by score + 1; Ludwig This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ...
[GENERAL] Query m:n-Combination
Dear list, I have to solve a simple Problem, explained below with some sample-Data. A typical M:N-constellation, rivers in one table, communities in the other table, m:n-join-informations (which river is running in which community) in a third table. Table rivers: R_ID R_Name 1 river_1 2 river_2 3 river_3 4 river_4 5 river_5 Table communities : C_ID C_Name 1 community_1 2 community_2 3 community_3 4 community_4 5 community_5 Join-table mn_2_r_id mn_2_c_id 1 1 1 2 1 3 1 4 2 1 3 2 3 5 4 3 ... (in real database this relation is an gis-relation with thousands of rivers and countries, related by spatial join, but the problem is the same...) I want to know, which river is running through communities 1,2,3 *and* 4? You can see the solution by just looking at the data above (only river_1 is running through all these countries), but how to query this by sql? Thanks in advance Ludwig -- 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] Field size
On Sun, Jul 20, 2008 at 05:50:30PM -0500, Martin wrote: Ok, this should be simple. How do I find the defined maximum length of a varchar field? SELECT character_maximum_length FROM information_schema.columns WHERE table_schema = 'name_of_your_schema' and table_name = 'name_of_your_table' and column_name = 'name_of_your_column' bye... Ludwig -- 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] Windows Crash
Hallo Bob, I also use pgDmin on XP with postGIS and imported Shape-Files, but without problems. As far as I know pgadmin uses gtk, and there are some google-hits for searchvalues pgadmin and gtk reporting hardware-crashes on windows and linux Systems. Perhaps the same problem? Ludwig MS's web site has a good summary at http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/w2000Msgs/6 0 93.mspx?mfr=true . Their bottom line: Faulty hardware, a buggy system service, antivirus software, and a corrupted NTFS volume can all generate this type of error. If you haven't installed anything else recently or changed any other drivers (and you've tried the same w/your AV turned off), I'd strongly suspect a hardware error. Run a CHKDSK to check the system drive volume and a RAM test to rule out bad RAM (bad RAM would be the first thing I'd check). - Bill -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bob Pawley Sent: Thursday, June 26, 2008 11:57 AM To: PostGIS Users Discussion Cc: PostgreSQL Subject: [GENERAL] Windows Crash Hi I'm copying PostgreSQL discussion group in case the following problem involves their efforts. I am running Postgresql 8.3 with Postgis latest version and PGAdmin 1.8.2 on Windows XP. I imported a shapefile using conversion and upload and it installed with no problem. When I viewed the table through PGAdmin and scrolled, Windows crashed with a message that the graphics driver had an error. I downloaded and installed the latest graphics driver and tried to view the table again. This time I used the Select * option and the table opened normally, but when I attempted to scroll, Windows again crashed. This time I got the blue screen with the error message Page_Fault_In_NonPaged_Area. I restarted and again used Select * all and was able to read the table. Very slow scrolling allowed me to read a few rows, some of which had distorted views of the information in the geometriy column. Would anyone have any thoughts - other than to buy new hardware?? Bob -- 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 -- 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
Perhaps trivial, but: Additional to or instead of triggers You can use grants to allow updates only to special users. Ludwig Hello there Can anyone please tell me how to make a column uneditable.. Plz give me some output thans -- 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] E_PARSE error ?
Hi,
I think, this is the wrong list, it appears to be a PHP error.
Anyway, try to put the global $_SERVER['SCRIPT_NAME'] into {}brackets:
list($page_id)=sqlget(select page_id from pages where
name='{$_SERVER['SCRIPT_NAME']}');
Hope, You're not lost anymore ...
Ludwig
PJ schrieb:
I'm using php5, postgresql 8.3, apache2.2.8, FreeBSD 7.0
I don't understand the message:
*Parse error*: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE,
expecting T_STRING or T_VARIABLE or T_NUM_STRING
the guilty line is:
list($page_id)=sqlget(
select page_id from pages where name='$_SERVER['SCRIPT_NAME']');
the variable value is /index.php
however, at the time of execution this has been cleared
So, the question is - What is the unexpected T_ENCAPSED_AND_WHITESPACE?
and What is actually expected? Are we talking about the content of
$_SERVER['SCRIPT_NAME'] or what is the syntax error? This is within
php code; could it be that the parser is reading this as something
else, like HTML?
I'm lost :((
--
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] Password safe web application with postgre*s*
In our web-based-solution (PHP) the database credentials (username and password) are encrypted and stored by PHP as session-Variables.Yes, there is the risk, they could be read by someone, who has access to the apache-sessions-directory, but this user also must have access to the php-scripts with the encrypt-functions to get the unencryption-keys and he must be able to work with these informations.But I think, this solution is much more save then storing or comitting the credentials as clear-text in cookies, hidden formular-elements or as sessions. But when you try to login to the database, somehow the credentials must be cleartext, so you cant get rid of this lack of security in my opinion.By the way, this is an *intra*net-solution, and we dont have hackers in our staff, I hope...Ludwig
Re: [GENERAL] Underscore _ in LIKE pattern
Hi Stef,the underscore has to be escaped:SELECT * FROM pg_tables WHERE schemaname=public AND tablename LIKE in\\_% ORDER BY tablename ASCExcerpt from Manual:To match a literal underscore or percent sign without matching other characters, the respective characterin pattern must be preceded by the escape character. The default escape character is the backslashbut a different one can be selected by using the ESCAPE clause. To match the escape characteritself, write two escape characters.Note that the backslash already has a special meaning in string literals, so to write a pattern constantthat contains a backslash you must write two backslashes in an SQL statement (assuming escape stringsyntax is used, see Section 4.1.2.1). Thus, writing a pattern that actually matches a literal backslashmeans writing four backslashes in the statement. You can avoid this by selecting a different escapecharacter with ESCAPE; then a backslash is not special to LIKE anymore. (But it is still special to thestring literal parser, so you still need two of them.) Alternative use of a regular _expression_: SELECT * FROM pg_tables WHERE schemaname=public AND tablename *~ in_ ORDER BY tablename ASCbye...Ludwig
Re: [GENERAL] Substring Problem
Type casting is required since 8.3, trySELECT substring(date :: varchar from 1 for 7) AS stryearmonth, COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY stryearmonth ORDER BY stryearmonth ASCBye...LudwigHi there, it seems to work with 8.1, but not anymore with 8.3. What is wrong with this substring request? Or is it some installation issue? Thanks for any suggestion! SELECT substring(date from 1 for 7) AS stryearmonth, COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY stryearmonth ORDER BY stryearmonth ASC It says: ERROR: function pg_catalog.substring(date, integer, integer) does not exist LINE 1: SELECT substring(date from 1 for 7) as stryearmonth, COUNT(i...
Re: [GENERAL] Storing and accessing GeoData( Latitude and Longitude ) in PostgreSQL 8.3
Yes, there is a great solution,take a look at PostGIS: http://www.postgis.org/It shurely will do all you want with geodata, but perhaps not as simple as you like...bye...LudwigHello,I have a lot of GeoData( Latitude and Longitude ) in my PostgreSQL Database. Is store them as numeric at this time.But now I want to access the data and want to have all Datasets around a Geographic point. eg: within a radius of 5 km...Is there a simple way to do something like this?Thanks for your Help,Stefan Sturm
Re: [GENERAL] Trouble with foreign key
Hi,whats the result of SELECT * from tmp_stamp where _stamp = 2f980de9f2297c7902f3415f6537c6be;?Perhaps different fieldtypes (f. e. VARCHAR in table www_es_orderhead_cs versus CHAR(n) in Table _stamp filled up with blanks)?LudwigHi,there is a problem with foreign key (PG 8.3RC2). Please, look at below. I dont understand this behavior. Why this INSERT is ending with this error message. In my opinion, the key is obviously present. Thank you.--Otakarek# INSERT INTO www_es_orderhead_cs(_status,_tmp_ip,_tmp_stamp) VALUES(n,xx.121.111.31,2f980de9f2297c7902f3415f6537c6be);ERROR: insert or update on table www_es_orderhead_cs violates foreign key constraint www_es_orderhead_cs__tmp_stamp_fkeyDETAIL: Key (_tmp_stamp)=(2f980de9f2297c7902f3415f6537c6be) is not present in table tmp_stamp.# SELECT * from tmp_stamp; _ip| _stamp | _expired| _var--+--+---+-- xx.121.111.31/32 | 2f980de9f2297c7902f3415f6537c6be | 2008-04-08 17:49:33.193914+02 |(1 row)-- 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] Users, groups and inheritance questions
Hello Glyn,its confusing, but You didnt read the manual very carefully!Short excerpt:The INHERIT attribute governs inheritance of grantable privileges (that is, access privileges fordatabase objects and role memberships). It does not apply to the special role attributes set by CREATEROLE and ALTER ROLE. For example, being a member of a role with CREATEDB privilege does notimmediately grant the ability to create databases, even if INHERIT is set; it would be necessary tobecome that role via SET ROLE before creating a database.bye...Ludwig
Re: [GENERAL] Problem compiling PostGIS 1.1.4
Devrim GUNDUZ schrieb: Hello, On Tue, 2006-10-10 at 11:30 +0200, Ludwig Kniprath wrote: /usr/lib/postgresql/8.1/lib/liblwgeom.so.1.1: libgeos_c.so.1: cannot open shared object file: No such file or directory Did you run make install agains geos? Is the path that libgeos_c.so.1 lives in ldconfig path? Regards, Hi Devrim, I did run make against geos, but ldconfig afterwards had no entries for libgeos_c.so.l. So I did it again, but read the geos-Install-document more carefully: Linux: Make sure that /usr/local/lib is added to /etc/ld.so.conf Make sure that you run /sbin/ldconfig afterwards Afterwards the PostGIS-compilation and installation run without problems. Thanks Ludwig ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Problem compiling PostGIS 1.1.4
PostgreSQL 8.1 on ubuntu-linux 6.06 (kernel 2.6.15-27-686):
Compiled proj-4.4.9 and geos-2.2.3 without problems.
Output from postgis-./configure-Command:
SUMMARY
---
HOST_OS: linux-gnu
PGSQL: /usr/bin/pg_config
GEOS: /usr/local/bin/geos-config (with C-API)
(ldflags: -L/usr/local/lib)
PROJ: prefix=/usr/local libdir=/usr/local/lib
ICONV: 1
PORTNAME: linux
PREFIX: /usr
EPREFIX: ${prefix}
DOC: /usr/share/doc/postgresql-doc-8.1/contrib
DATA: /usr/share/postgresql/8.1/contrib
MAN: /usr/share/postgresql/8.1/man
BIN: /usr/lib/postgresql/8.1/bin
EXT: /usr/lib/postgresql/8.1/lib (\$$libdir)
---
make check with problems:
...ERROR: function postgis_lib_version() does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts
Running lwpostgis.sql on my database results in multiple errors:
BEGIN
psql:lwpostgis.sql:39: NOTICE: type histogram2d is not yet defined
DETAIL: Creating a shell type definition.
psql:lwpostgis.sql:39: ERROR: could not load library
/usr/lib/postgresql/8.1/lib/liblwgeom.so.1.1: libgeos_c.so.1: cannot
open shared object file: No such file or directory
...
What went wrong?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Unable to Start PostgreSQL 8.1.4 on Windows XP
Hi: I installed PostgreSQL 8.1.0 on my computer running on Windows XP Service Pack 2 last April. It works fine (I don't use the database everyday though). Today, after being unable to connect to the server, I realized there must be something wrong. Looking at the logs I found out that there were no more entries after August 8. I surmise that after August 8, the PostgreSQL service is not starting anymore. I tried to start the service manually and through the command prompt, but still it didn't work. After reading the FAQ, I did the following: a) Uninstall a anti-spyware software (which was installed around August 8). b) Upgrade the server to PostgreSQL 8.1.4 c) Reboot the server c) Temporarily disable the antivirus (Rising Antirus), I did not uninstall the Antivirus because prior to August the antivirus and the database server were running together (ie. the antivirus didn't affect PostgreSQL). After taking these measures, the service is still unable to start on its own . I tried to start the service through the command line by typing the following command: C:\Program Files\PostgreSQL\8.1\binC:\Program Files\PostgreSQL\8.1\bin\pg_ctl. exe runservice -N pgsql-8.1 -D C:\Program Files\PostgreSQL\8.1\data\ It gives me the following error message: pg_ctl: could not start service pgsql-8.1: error code 1063 Searching the web using google gives me the following information about error 1063: Error code 1063: ERROR_FAILED_SERVICE_CONTROLLER_CONNECT - The service process could not connect to the service controller. (from http://user.tninet.se/~tdf275m/wincode2.htm) Here are I believe the last entries in the logfile: 2006-08-08 08:14:02 LOG: database system was shut down at 2006-08-07 22:50:56 2006-08-08 08:14:02 LOG: checkpoint record is at 0/4AEF40 2006-08-08 08:14:02 LOG: redo record is at 0/4AEF40; undo record is at 0/0; shu tdown TRUE 2006-08-08 08:14:02 LOG: next transaction ID: 40061; next OID: 16447 2006-08-08 08:14:02 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2006-08-08 08:14:03 LOG: database system is ready 2006-08-08 08:14:05 LOG: transaction ID wrap limit is 2147484148, limited by da tabase postgres 2006-08-08 09:43:03 LOG: received fast shutdown request 2006-08-08 09:43:07 LOG: shutting down 2006-08-08 09:43:07 LOG: database system is shut down 2006-08-08 09:43:09 LOG: logger shutting down Hope this helps. Thanks in advance, ludwig lim __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Unable to Start PostgreSQL 8.1.4 on Windows XP
Hi Thomas : --- Thomas Kellerer [EMAIL PROTECTED] wrote: Ludwig Isaac Lim wrote on 15.08.2006 18:05: Searching the web using google gives me the following information about error 1063: Error code 1063: ERROR_FAILED_SERVICE_CONTROLLER_CONNECT - I had a similar issue several weeks ago. In my case the UMTS connection software killed my Postgres installation. Did you change anything with your network installation? In my case either de-installing the UMTS software or applying a tool called LSPFix would fix the problem: http://www.cexx.org/lspfix.htm Hope this helps Thomas Thanks for the quick reply. Actually, after running the newly installed anti-spyware program a week ago I was unable to access my internet. I'm not sure if the anti-spyware program caused it though. During that period, my computer was infected with spware,adware,virus. It was only until after running lspfix that I was able to access the internet. After reading your email, I tried running lspfix again but it seems that this time, lspfix didn't detect any problem in my network protocol drivers. Thanks for the idea though, it was a good one. From what you had said, I have a hunch that the problem lies with the network protocol driver. ludwig lim __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PGSQL 7.4 - 8.1 migration performance problem
Hi : Maybe you forget to run the ANALYZE command afterwards. ludwig. --- Philippe Lang [EMAIL PROTECTED] wrote: Hi, For an unknown reason, I cannot post this message to the mailing-list! Here it is: http://www.attiksystem.ch/postgresql-general.txt Cheers, Philippe Lang __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PGSQL 7.4 - 8.1 migration performance problem
Hi: How about the postgresql.conf settings? Did you changed them? ludwig. --- Philippe Lang [EMAIL PROTECTED] wrote: Hi, I did not mention it in my first email, but I did run ANALYSE before running the query... So statistics are just fine for the execution plan engine. Philippe -Message d'origine- De : Ludwig Isaac Lim [mailto:[EMAIL PROTECTED] Envoy#65533;: mercredi, 31. mai 2006 15:52 #65533;: Philippe Lang Cc : pgsql-general@postgresql.org Objet : Re: [GENERAL] PGSQL 7.4 - 8.1 migration performance problem Hi : Maybe you forget to run the ANALYZE command afterwards. ludwig. --- Philippe Lang [EMAIL PROTECTED] wrote: Hi, For an unknown reason, I cannot post this message to the mailing-list! Here it is: http://www.attiksystem.ch/postgresql-general.txt Cheers, Philippe Lang __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PGSQL 7.4 - 8.1 migration performance problem
Hi: Just a hunch: What about adding an index to the field etapes_lignes_commandes(code_etape). Assuming your database uses C locales. This might work for your particular query since your filter is something like code_etape~~'COMP%'. What about increasing the settings of work_mem, etc (check http://www.powerpostgresql.com/PerfList) . ludwig. --- Philippe Lang [EMAIL PROTECTED] wrote: Hi, I have now disabled hyperthreading in /etc/grub.conf (added noht at the end of kernel lines), rebooted the server, run ANALYSE on the database again, and launched the query: things are even a little bit worse, it completes in 540 seconds now. So this is no ANALYSE or hyperthreading problem. Here are both EXPLAIN ANALYSE results, plus the query itself: Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt Postgresql 8.1.4: http://www.attiksystem.ch/explain_analyze_81.txt Query is here: http://www.attiksystem.ch/big_query.txt The freebsd box, as far as I can remember, has not been specifically tuned. I did recompile a kernel with a few things inside, but really, I did not spend hours on that two years ago. Thanks, Philippe -Message d'origine- De : Tom Lane [mailto:[EMAIL PROTECTED] Envoy#65533;: mercredi, 31. mai 2006 16:39 #65533;: Philippe Lang Cc : pgsql-general@postgresql.org Objet : Re: [GENERAL] PGSQL 7.4 - 8.1 migration performance problem Philippe Lang [EMAIL PROTECTED] writes: http://www.attiksystem.ch/postgresql-general.txt Please provide EXPLAIN ANALYZE, not just EXPLAIN, output ... and try to post it in an un-line-wrapped form. Also, as Ludwig mentioned, a common gotcha is to forget to ANALYZE your data after moving it to a new database. regards, tom lane __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] Ordering of records in group by not possible
Hi: You could order by column 2 if you want to order on the results on your aggregate: Select a, aggregate(b) from c group by a order by a,2 another alternative is : select a , aggregrate(b) as alias from c group by a order by a, alias e.g. select a,sum(b) as sum_b from c group by a order by a, sum_b ludwig lim __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] evaluating equation stored in a string
Hi : How about creating a view that converts integer into numeric? create view name as select col1::numeric as col1, select col2::numeric as col2 ... and then query the view instead. This way your formula doesn't need to use cast anymore. ludwig lim --- SunWuKung [EMAIL PROTECTED] wrote: I have equations stored in strings and I would need to evaluate them in pgsql - get the result. Maybe there is an eval() function but I couldn't find it. The only way I found was to use something like this: calcstring_arg:= 'SELECT ' || calcstring_arg || ' ::numeric AS outparam'; FOR tmp IN EXECUTE calcstring_arg LOOP END LOOP; RETURN tmp.outparam; However I have a problem with this cast. I don't know how to handle brackets in the equation, since SELECT 35/124 ::numeric --0.28 while SELECT (35/124) ::numeric -- 0 so I should do something like SELECT (35::numeric/124::numeric) --0.28 But than I would need to start to look inside the string and do manipulation to it that I would like to avoid. How could I evaluate these strings independently from whether there are brackets in them or not? Thanks for the help. Balázs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Clarification Regarding Vacuum and template1
Hi : Saw this post on BUGS mailing list : Olleg Samoylov olleg_s ( at ) mail ( dot ) ru writes: Opps, template1 must not be vacuumed. Says who? If we didn't vacuum template1 then it would be subject to XID wraparound problems, unless it had never been modified, which is something vacuumdb can't count on. regards, tom lane Under normal circumstances, there's no need to vacuum template1 right? Its quite difficult to imagine XID wraparound problems occuring in template1. I can't think how can 4 billion transactions occur in template1 if template1 is just used as a template in creating a new database. Am I missing something here? Thank you very much, ludwig lim __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Clarification Regarding Vacuum and template1
Yes: the wraparound limit is for transactions cluster-wide, not per database. If you make 1 change in template1 and then 2 billion changes in some other databases, template1 is broken unless it's been vacuumed meanwhile. regards, tom lane So in practice, should one vacuum template1 when vacuuming other database (Assuming one doesn't use vaccumdb script when vacuuming database)? Will this problem occur if I vacuum all database except template1? Thanks for the previous quick response. Thank you once again. ludwig lim __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] \dD does not show check constraint for domain on version 8.0.4?
Hi: I just upgraded my PostgreSQL from version 8.0.3 to version 8.0.4. I noticed that the \dD command in psql does not show the check constaint of a domain. I am surpised becaused the archives stated that a patch for displaying check constraint in \dD command has already been applied (http://archives.postgresql.org/pgsql-patches/2005-04/msg00034.php). psql --version psql (PostgreSQL) 8.0.4 Was the patch for version 8.1? I'm just wondering why the patch was not applied to PostgreSQL version 8.0.4 Thank you! Ludwig Lim __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Pg - Perl 5 prob
Hallo! I made an debian update of postgresql, from 6.5.?? to 7.0.1. (?) ... That worked fine. I then created my database and access user again, started psql and created the database from the dump I have made some time ago. In fact, the database is created, all the table are there, empty, as I have saved them. Logged in as user ludwig I can insert data with no problem. But as my small Perl-program tries to connect to the database, that does not work. postgres.log: Peer authentication failed for user 'ludwig' What to do? Ludwig ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Sequences - problem
Hallo!
I created several tables using a id-sequence for each:
create sequence portid;
create table ports(id integer primary key default nextval('portid'), name
varchar);
Now, since it is a web-application I am working on, I have several
Perl-scripts acting on/with the database.
THe informations-program simply has to read out each sequence in order to
give some statistical data about the database (number of ports, people
...)
As I try a
Pg::doQuery(select currval('portid');, \@ports);
the program gets no reply, on the Postmaster-task (I did not get
postmaster start on startup/background, runs on a task in foreground) I
that message:
ERROR: regionid.currval is not yet defined in this session
ERROR: jpid.currval is not yet defined in this session
ERROR: countri.currval is not yet defined in this session
ERROR: jprid.currval is not yet defined in this session
ERROR: portid.currval is not yet defined in this session
What does the problem consist in, and how can I solve it?
In my opinion it is better to read out the counter instead of querying all
elements in a table, so to get the number of elements ...
Pg::doQuery(select * from ports;, \@ports);
Where the number I am looking for is $#ports then ...
I think this method will take a lot of time and is not very effective, as
I do not think I wil ever access one of the entrys read (at least not
while putting some information) ...
Saluti!
Ludwig
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Sequences/Problem II.
Hallo! I temporary solved the problem with the sequences by making a query: select id from ports order by id desc limit 1; But I am not convinced this is the real way to get the number of element the table ports has Saluti! Ludwig ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Newbie Question
Hallo!
How do I create an autoincrement field in a postgresql table???
What are the correct field type and parameters
Well, what about using sequences?
create sequence tralalala;
create table huibui
(
id integer primary key default nextval('tralalala'),
field1 references table1,
and-so-on references all-other-tables
);
How, each time You insert some data in huibui using
insert into huibui (field1, and-son-on) values (?, ..);
the 'tralala' counter will be increased by one (nextval).
Saluti!
Ludwig
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Invoices
Hallo! Maybe this is a bit off-topic, as this problem is more a design-one, but I wanted to write a web-application write invoices more easy. I wonder if it was a good idea to try this using Postgres or if it was better to write the data of each invoice in a separate file in a separate directory. I doubt it was a good idea to put all the data into one database like create table invoices( invoice integer primary key, datum date, customer integer references customers, clerk integer references clerks ); create table invoicedata( invoice integer references invoices, item integer references services, ); as this would mean all the information of all invoices is stored in one table, meaning the table will grow to a HUGE size making queries very slow. On the other side I doubt following solution will be a good idea, too! create table invoices ( invoice integer primary key, datum date, customer integer references customers, clerk integer references clerk ); create table INVOICENUMBER ( item integer references services, amount integer, ); as this will create a HUGE number of tables and I think it was not a good idea to give users permission to create new tables. Maybe someone can help? Saluti! Ludwig ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
