Re: [GENERAL] ERROR: Gin doesn't support full scan due to it's awful
Hi Tom, Thanks for the quick reply. >> Sorry, mistyped the query causing the problem. It is: > >> select * >> from maps, features >> where maps.query @@ features.tags_vector; > > In that case it's fair to ask what query values you have stored in maps. > In particular I imagine that you'll find that a specific query is > causing the problem ... > >regards, tom lane Interesting...that seems to be the case. For example, this will fail: explain analyze select * from test.features where to_tsquery('') @@ features.vector ERROR: Gin doesn't support full scan due to it's awful inefficiency Interestingly this works: explain analyze select * from test.features where NULL @@ features.vector Here is a slightly bigger test case: --drop schema test cascade; create schema test; CREATE TABLE test.maps ( id serial, query tsquery ); CREATE TABLE test.features ( id serial, vector tsvector ); CREATE INDEX features_vector ON test.features USING gin (vector); INSERT INTO test.maps (query) VALUES (to_tsquery('')); INSERT INTO test.features (vector) VALUES (to_tsvector('test')); analyze test.maps; analyze test.features; -- Now try this, which won't work (ERROR: Gin doesn't support full scan due to it's awful inefficiency): set enable_seqscan to off; explain select * from test.maps, test.features where features.vector @@ maps.query Nested Loop (cost=1.00..10004.04 rows=1 width=36) -> Seq Scan on maps (cost=1.00..10001.01 rows=1 width=12) -> Index Scan using features_vector on features (cost=0.00..3.01 rows=1 width=24) Index Cond: (features.vector @@ "outer".query) However, this works: set enable_seqscan to on; set enable_indexscan to off; set enable_bitmapscan to off; explain analyze select * from test.maps, test.features where features.vector @@ maps.query Nested Loop (cost=2.00..20002.03 rows=1 width=36) (actual time=0.055..0.055 rows=0 loops=1) Join Filter: ("inner".vector @@ "outer".query) -> Seq Scan on maps (cost=1.00..10001.01 rows=1 width=12) (actual time=0.011..0.014 rows=1 loops=1) -> Seq Scan on features (cost=1.00..10001.01 rows=1 width=24) (actual time=0.006..0.010 rows=1 loops=1) Total runtime: 0.129 ms You see the same things if you put a NULL in the query column (unlike above). If instead, you do this in the script above: INSERT INTO test.maps (query) VALUES (to_tsquery('test')); Then it always works. Seems like the moral of the story, tsquery values of '' or NULL don't work. That is surprising to me - maybe the documentation should point out this issue? Thanks, Charlie smime.p7s Description: S/MIME Cryptographic Signature
Re: [OT] sig sizes (was Re: [GENERAL] Porting from ...)
Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ron Johnson Sent: 05 September 2006 20:36 To: pgsql-general@postgresql.org Subject: [OT] sig sizes (was Re: [GENERAL] Porting from ...) -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Joshua D. Drake wrote: Ron Johnson wrote: Eliminating 21 line .signatures would be nice, too. How about your 16 lines ;) ??? It's 8 lines. I count 16, including PGP fluff. Not that I care much :-) Oh, ok. Tbird hides the PGP wrapper. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE/diSS9HxQb37XmcRAgP7AJwIg9zzju3s3JJSz/jq165U7hFUCgCfXVKK HNUAqY5J2urMEI6uGl5cr3Y= =CTJ2 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend Are you serious about the length of my sign, I just think that is a funny sign, but if it cause some trouble to the server y will cat my sign from my mails and say sorry for the problem caused. Regards, Gustavo -- ||\ // \ | \\ // | I'm thinking. \ \\ l\\l_ //| _ _ | \\/ `/ `.|| /~\\ \//~\ | Y | | || Y | | \\ \ // | | \| | |\ / | [ |||| ] \ | o|o | > / ] Y |||| Y [ \___\_--_ /_/__/ | \_|l,--.l|_/ | /.-\() /--.\ | >' `< | `--(__)' \ (/~`----'~\) / U// U / \ `-_>-__-<_-'/ \ / /| /(_#(__)#_)\ ( .) / / ] \___/__\___/`.`' / [ /__`--'__\ |`-'| /\(__,>-~~ __) | |__ /\//\\( `--~~ ) _l |--:. '\/ <^\ /^> | ` ( < \\ _\ >-__-< /_ ,-\ ,-~~->. \ `:.___,/ (___\/___) (/()`---' ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ERROR: Gin doesn't support full scan due to it's awful
Charlie Savage <[EMAIL PROTECTED]> writes: > Sorry, mistyped the query causing the problem. It is: > select * > from maps, features > where maps.query @@ features.tags_vector; In that case it's fair to ask what query values you have stored in maps. In particular I imagine that you'll find that a specific query is causing the problem ... regards, tom lane ---(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] ERROR: Gin doesn't support full scan due to it's awful
Sorry, mistyped the query causing the problem. It is: select * from maps, features where maps.query @@ features.tags_vector; Thanks, Charlie Charlie Savage wrote: I've run across another GIN index issue - using postgresql 8.1.4 on Window/Linux with the GIN/tsearch2 patch. I have two tables like this: CREATE TABLE maps ( id serial, query tsearch2.tsquery ) CREATE TABLE features ( id serial, vector tsearch2.tsvector ) CREATE INDEX idx_features_tags_vector ON features USING gin (tags_vector); Where maps.query contains cached tsquery (they are cached for performance reasons). When I run this query: select * from maps, features where to_tsquery('test') @@ features.tags_vector I get this error: ERROR: Gin doesn't support full scan due to it's awful inefficiency Here is explain (from a very small test database): Nested Loop (cost=0.00..1878.71 rows=370 width=208) -> Seq Scan on maps (cost=0.00..14.80 rows=480 width=136) -> Index Scan using idx_features_tags_vector on features (cost=0.00..3.87 rows=1 width=72) Index Cond: ("outer".query @@ features.tags_vector) I thought that this would solve my problem: set enable_indexscan to off; But it does not. Interestingly, this does work: select * from features where to_tsquery('test') @@ features.tags_vector; Explain: Index Scan using idx_features_tags_vector on features (cost=0.00..3.87 rows=1 width=72) Index Cond: ('''test'''::tsquery @@ tags_vector) At first I thought the issue was that you couldn't use an Index Scan on gin index, but that now seems like an incorrect conclusion. So, two things: 1. How do I work around this issue? 2. Seems like postgresql should be smart enough to pick a query that will run. Thanks, Charlie ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] ERROR: Gin doesn't support full scan due to it's awful inefficiency
I've run across another GIN index issue - using postgresql 8.1.4 on Window/Linux with the GIN/tsearch2 patch. I have two tables like this: CREATE TABLE maps ( id serial, query tsearch2.tsquery ) CREATE TABLE features ( id serial, vector tsearch2.tsvector ) CREATE INDEX idx_features_tags_vector ON features USING gin (tags_vector); Where maps.query contains cached tsquery (they are cached for performance reasons). When I run this query: select * from maps, features where to_tsquery('test') @@ features.tags_vector I get this error: ERROR: Gin doesn't support full scan due to it's awful inefficiency Here is explain (from a very small test database): Nested Loop (cost=0.00..1878.71 rows=370 width=208) -> Seq Scan on maps (cost=0.00..14.80 rows=480 width=136) -> Index Scan using idx_features_tags_vector on features (cost=0.00..3.87 rows=1 width=72) Index Cond: ("outer".query @@ features.tags_vector) I thought that this would solve my problem: set enable_indexscan to off; But it does not. Interestingly, this does work: select * from features where to_tsquery('test') @@ features.tags_vector; Explain: Index Scan using idx_features_tags_vector on features (cost=0.00..3.87 rows=1 width=72) Index Cond: ('''test'''::tsquery @@ tags_vector) At first I thought the issue was that you couldn't use an Index Scan on gin index, but that now seems like an incorrect conclusion. So, two things: 1. How do I work around this issue? 2. Seems like postgresql should be smart enough to pick a query that will run. Thanks, Charlie ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Syntax for converting double to a timestamp
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Frank Church wrote: > On 9/5/06, codeWarrior <[EMAIL PROTECTED]> wrote: >> It's generally considered bad form to use reserved words as column >> names >> > > I am aware of that - in this case the column names are chosen to > reflect exactly the names of the attributes of the event being > recorded. Does the timestamp reflect an insert time, update, widget creation date, etc, etc, etc? All these attributes modify TIMESTAMP. For example, UPDATE_TIMESTAMP, CURRENT_TIMESTAMP, TRANSACTION_TIMESTAMP, CREATION_TIMESTAMP, etc, etc, etc. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE/fJeS9HxQb37XmcRAjpRAJ0V0id/uxVZWE6hC45IZzlJzVKNHgCdEbbN YoMAOqezJ77VAbEnpUNpF1U= =jYb6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Syntax for converting double to a timestamp
On 9/5/06, codeWarrior <[EMAIL PROTECTED]> wrote: It's generally considered bad form to use reserved words as column names I am aware of that - in this case the column names are chosen to reflect exactly the names of the attributes of the event being recorded. ""Frank Church"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >I am trying to create a view based on this query > > 'select *, "timestamp"::timestamp from ccmanager_log' > > > This is the error I get to below, how do I use the time zone syntax > > error: cannot cast tupe double precision to timestamp without time zone > > What is the right syntax? > > > The column to be converted is also called timestamp > > F Church > > ---(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 > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] what fired a trigger
Hi, The most general problem that may be a design problem (and I'm not asking to do my homework even if well, you may still help ) is I've a "temporary relation" and a permanent relation. A typical situation is the one of session in a web app and temporary basket. Once the user log in, the temporary basket becomes "permanent". But I'd like automatic garbage collection for all tables simply related to sessions. When a session get deleted all row that are *just* related to the session, but not to the user, should be deleted. Anyway I still need normal "User related" delete. The partial solution I found need to distinguish what fired a trigger another trigger or a "plain delete" and pass it on to the other triggers. I've something similar: create table T_Session ( idSession char(32) not null default md5(now() || random()) ) alter table T_Session add constraint PK_Session primary key (idSession) ; create table T_User ( idUser integer , idSession char(32) --should stay here or in a t_Session_User(idSession,idUser)? ); alter table T_User add constraint PK_User primary key (idUser) ; create table T_Preferences ( idPreferences integer , idSession char(32) ); alter table t_Preferences add constraint PK_Preferences primary key (idPreferences) ; alter table t_Preferences add constraint FK_Preferences_Session foreign key (idSession) references t_Session (idSession) on delete cascade ; alter table t_Preferences add constraint FK_Preferences_User foreign key (idUser) references t_User (idUser) on delete cascade ; create table t_Preferences_Stuff ( idStuff integer , idPreferences integer ); alter table T_Preferences_Stuff add constraint PK_Preferences_Stuff primary key (idStuff) ; alter table T_Preferences_Stuff add constraint FK_Preferences_Stuff foreign key (idPreferences) references T_Preferences (idPreferences) on delete cascade ; create or replace function TG_SessionDelete( ) returns trigger as ' begin if OLD.idUser is not null then return null; else return OLD; end if; end; ' language plpgsql; create trigger TG_SessionDelete before delete on T_Preferences for each row execute procedure TG_SessionDelete(); Now if I delete a session with something like delete from t_Session where idSession='..'; all the row in the linked tables where the idUser is null should be deleted. If the row has a not null idUser it shouldn't be deleted. And this works. But what if I'd like to obtain those behaviour too: 1) delete from T_Preferences where idSession=''; should delete all row in linked tables, no matter if idUser is null or not I'm still thinking if this is actually what I want since if there is a link to idUser I may use idUser to delete rows. So I could split deletion related to session operations and deletion related to user operations. 2) delete from T_User where idUser=10; delete from T_Preferences where idUser=37; should delete all row in linked tables. the above trigger doesn't work. I've tried to understand if there is a way to exploit TG_ARGV[] & Co. but I didn't understand even how to use it. BTW postgres is 7.4.13 thx -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [OT] sig sizes (was Re: [GENERAL] Porting from ...)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dave Page wrote: > > >> -Original Message- >> From: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] On Behalf Of Ron Johnson >> Sent: 05 September 2006 20:36 >> To: pgsql-general@postgresql.org >> Subject: [OT] sig sizes (was Re: [GENERAL] Porting from ...) >> >> -BEGIN PGP SIGNED MESSAGE- >> Hash: SHA1 >> >> Joshua D. Drake wrote: >>> Ron Johnson wrote: >>> Eliminating 21 line .signatures would be nice, too. >>> How about your 16 lines ;) >> ??? It's 8 lines. > > I count 16, including PGP fluff. Not that I care much :-) Oh, ok. Tbird hides the PGP wrapper. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE/diSS9HxQb37XmcRAgP7AJwIg9zzju3s3JJSz/jq165U7hFUCgCfXVKK HNUAqY5J2urMEI6uGl5cr3Y= =CTJ2 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Upgrade Postgres 8.1.2 to 8.1.4
On Tue, 2006-09-05 at 13:59, Zlatko Matić wrote: > No, nothing changed. > I am the only user of this computer (personal computer). It is the same > account I was using for installing PostgreSQL server. > In fact I had the same problem with upgrading from PostgreSQL 8.0 to 8.1, > then from 8.1 to 8.2...In all theses cases I had to uninstall old version > first and then install new version of the server. I don't want to uninstall > the server this time Man, sorry, you've exhausted all of my windows expertise. I'm a unix guy. I'd suspect some kind of problem with ownership of the files or something like that. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [OT] sig sizes (was Re: [GENERAL] Porting from ...)
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Ron Johnson > Sent: 05 September 2006 20:36 > To: pgsql-general@postgresql.org > Subject: [OT] sig sizes (was Re: [GENERAL] Porting from ...) > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Joshua D. Drake wrote: > > Ron Johnson wrote: > > Eliminating 21 line .signatures would be nice, too. > > > >> How about your 16 lines ;) > > ??? It's 8 lines. I count 16, including PGP fluff. Not that I care much :-) Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[OT] sig sizes (was Re: [GENERAL] Porting from ...)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Joshua D. Drake wrote: > Ron Johnson wrote: > Eliminating 21 line .signatures would be nice, too. > >> How about your 16 lines ;) ??? It's 8 lines. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE/dGeS9HxQb37XmcRAswpAKDq0Zy5sF5nI+Ojl8zkOcDWf3VY8ACeOK6h BJJadV19Ni9bwYPy+Hsq2fc= =x6qe -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Enabling Kerberos
Hi, How do I enable kerberos support? I have run configure with --with-krb5, but when I try to run psql, it still returns "Kerberos 5 authentication not supported". Thanks, Terry
Re: [GENERAL] Porting from ORACLE to PostgSQL
Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Eliminating 21 line .signatures would be nice, too. How about your 16 lines ;) Sincerely, Joshua D. Drake codeWarrior wrote: Perhaps you should look at EnterpriseDB -- It's an Oracle-compliant wrapper for postgreSQL "gustavo halperin" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Hello I need to porting many old ORACLE-oriented-SQL files and I have many problem with this code. Sometimes the code use some types not supported in PosgSQL like "number" or "varchar2", there fore, can I write some type of declaration (like in c : #define alias_name name) in order to make PosgSQL recognize these types? Or there are other solution that you recommend to use ? Thank you, Gustavo - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE/aQWS9HxQb37XmcRArJVAKCNjKPIGC0dZKCZNgZBTz9wdYl7nQCfYZFU etqDdW8rRm+gXShS1iF3Nq8= =g+pL -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Upgrade Postgres 8.1.2 to 8.1.4
No, nothing changed. I am the only user of this computer (personal computer). It is the same account I was using for installing PostgreSQL server. In fact I had the same problem with upgrading from PostgreSQL 8.0 to 8.1, then from 8.1 to 8.2...In all theses cases I had to uninstall old version first and then install new version of the server. I don't want to uninstall the server this time Zlatko - Original Message - From: "Scott Marlowe" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]> Cc: "pgsql general" Sent: Tuesday, September 05, 2006 7:33 PM Subject: Re: [GENERAL] Upgrade Postgres 8.1.2 to 8.1.4 On Tue, 2006-09-05 at 12:20, Zlatko Matić wrote: Does anybody has a clue? Somebody experienced similar problem? Should I be logged to Windows as regular user (administrator) or as "postgres" service account? Thanks. - Original Message - From: Zlatko Matić To: pgsql-general@postgresql.org Sent: Tuesday, September 05, 2006 6:54 AM Subject: [GENERAL] Upgrade Postgres 8.1.2 to 8.1.4 In Windows XP Pro (SP2) I tried to upgrade PostgreSQL server from 8.1.2 to 8.1.4., by using upgrade.bat. Everything goes nice until end of installation when the following error apears: "Service 'PostgreSQL Database Server 8.1' (pgsql-8.1) could not be installed. Verify that you have sufficient priviliges to install system services" . I am logged as administrator, Windows XP Pro SP2. What should I do? Is PostgreSQL the ONLY thing that got changed? It sounds like you simply don't have administrative privileges on this system. You should be logged in as an administrator... ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Dependency graph of all tuples relied upon in a query answer
On 9/1/06, Randall Lucas <[EMAIL PROTECTED]> wrote: On Fri, Sep 01, 2006 at 09:30:57AM -0400, Merlin Moncure wrote: > A key tenet of relational thinking is to reduce all information to its > functional dependencies, and to try and avoid as much as possible > keeping information state in the data in a declarative sense. > last_query_shown_tuples() is imo a violation in that sense. I used last_query_shown_tuples() as a quick example. It seems more likely that it would be implemented in fact as something like EXPLAIN, where it acts upon a given query. Like, EXPLAIN DEPENDENCIES SELECT * FROM ... It seems that the query planner *must* know which rows, from which tables, actually get used in producing the output for a given query. Given that this info is present somewhere in the depths, is there a way to get this information out to the app level? not exactly in the sense you are describing, but you can make the query such that it gives you the knowledge to get back to the original data and look it up, yes? merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Upgrade Postgres 8.1.2 to 8.1.4
On Tue, 2006-09-05 at 12:20, Zlatko Matić wrote: > Does anybody has a clue? > Somebody experienced similar problem? > Should I be logged to Windows as regular user (administrator) or as > "postgres" service account? > > Thanks. > - Original Message - > From: Zlatko Matić > To: pgsql-general@postgresql.org > Sent: Tuesday, September 05, 2006 6:54 AM > Subject: [GENERAL] Upgrade Postgres 8.1.2 to 8.1.4 > > In Windows XP Pro (SP2) I tried to upgrade PostgreSQL server > from 8.1.2 to > 8.1.4., by using upgrade.bat. > Everything goes nice until end of installation when the > following error > apears: "Service 'PostgreSQL Database Server 8.1' (pgsql-8.1) > could not be > installed. Verify that you have sufficient priviliges to > install system > services" . > I am logged as administrator, Windows XP Pro SP2. > What should I do? Is PostgreSQL the ONLY thing that got changed? It sounds like you simply don't have administrative privileges on this system. You should be logged in as an administrator... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Porting from ORACLE to PostgSQL
On Mon, 2006-09-04 at 16:54, gustavo halperin wrote: > Hello > > I need to porting many old ORACLE-oriented-SQL files and I have many > problem with this code. Sometimes the code use some types not supported > in PosgSQL like "number" or "varchar2", there fore, can I write some > type of declaration (like in c : #define alias_name name) in order to > make PosgSQL recognize these types? Or there are other solution that you > recommend to use ? If you just need a simple solution, look up domains: http://www.postgresql.org/docs/8.1/static/sql-createdomain.html specifically: create domain varchar2 as varchar; Note that you won't have a precision argument on the varchar2, but you can put one on the varchar you use during domain creation: This works: create domain varchar2 as varchar(200); create table test (info varchar); This doesn't: create domain varchar2 as varchar; create table test (info varchar(200)); But once the tables are created, insert statements and such should work normally. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Upgrade Postgres 8.1.2 to 8.1.4
Does anybody has a clue? Somebody experienced similar problem? Should I be logged to Windows as regular user (administrator) or as "postgres" service account? Thanks. - Original Message - From: Zlatko Matić To: pgsql-general@postgresql.org Sent: Tuesday, September 05, 2006 6:54 AM Subject: [GENERAL] Upgrade Postgres 8.1.2 to 8.1.4 In Windows XP Pro (SP2) I tried to upgrade PostgreSQL server from 8.1.2 to 8.1.4., by using upgrade.bat. Everything goes nice until end of installation when the following error apears: "Service 'PostgreSQL Database Server 8.1' (pgsql-8.1) could not be installed. Verify that you have sufficient priviliges to install system services" . I am logged as administrator, Windows XP Pro SP2. What should I do? Zlatko
Re: [GENERAL] Porting from ORACLE to PostgSQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Eliminating 21 line .signatures would be nice, too. codeWarrior wrote: > Perhaps you should look at EnterpriseDB -- It's an Oracle-compliant wrapper > for postgreSQL > > > > > "gustavo halperin" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] >> Hello >> >> I need to porting many old ORACLE-oriented-SQL files and I have many >> problem with this code. Sometimes the code use some types not supported in >> PosgSQL like "number" or "varchar2", there fore, can I write some type of >> declaration (like in c : #define alias_name name) in order to make PosgSQL >> recognize these types? Or there are other solution that you recommend to >> use ? >> >> Thank you, Gustavo - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE/aQWS9HxQb37XmcRArJVAKCNjKPIGC0dZKCZNgZBTz9wdYl7nQCfYZFU etqDdW8rRm+gXShS1iF3Nq8= =g+pL -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postrgesql and Mysql in the same server Linux (Fedora core 5)
Could you run the unix command 'top' and figure out where your performance degradation is before you assume it is a database issue... "Toffy" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, > as i put Postrgesql and Mysql in the same server Linux (Fedora core 5), > is it possible that this configuration may cause a strong deceleration > of all system performace? > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Syntax for converting double to a timestamp
It's generally considered bad form to use reserved words as column names ""Frank Church"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >I am trying to create a view based on this query > > 'select *, "timestamp"::timestamp from ccmanager_log' > > > This is the error I get to below, how do I use the time zone syntax > > error: cannot cast tupe double precision to timestamp without time zone > > What is the right syntax? > > > The column to be converted is also called timestamp > > F Church > > ---(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 > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Porting from ORACLE to PostgSQL
Perhaps you should look at EnterpriseDB -- It's an Oracle-compliant wrapper for postgreSQL "gustavo halperin" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hello > > I need to porting many old ORACLE-oriented-SQL files and I have many > problem with this code. Sometimes the code use some types not supported in > PosgSQL like "number" or "varchar2", there fore, can I write some type of > declaration (like in c : #define alias_name name) in order to make PosgSQL > recognize these types? Or there are other solution that you recommend to > use ? > > Thank you, Gustavo > > -- > ||\ // \ > | \\ // | I'm thinking. > \ \\ l\\l_ //| >_ _ | \\/ `/ `.|| > /~\\ \//~\ | Y | | || Y | > | \\ \ // | | \| | |\ / | > [ |||| ] \ | o|o | > / > ] Y |||| Y [ \___\_--_ /_/__/ > | \_|l,--.l|_/ | /.-\() /--.\ > | >' `< | `--(__)' > \ (/~`----'~\) / U// U / \ > `-_>-__-<_-'/ \ / /| > /(_#(__)#_)\ ( .) / / ] > \___/__\___/`.`' / [ > /__`--'__\ |`-'| >/\(__,>-~~ __) | |__ > /\//\\( `--~~ ) _l |--:. > '\/ <^\ /^> | ` ( < \\ > _\ >-__-< /_ ,-\ ,-~~->. \ `:.___,/ > (___\/___) (/()`---' > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tsearch2 & Hebrew
Oleg Bartunov wrote: On Tue, 5 Sep 2006, Yonatan Ben-Nes wrote: No, I didn't thought that it will be useful if it won't be accompanied by an hebrew stemmer which will work with it... I'm wrong? ispell and stemmer are doing the same job, so you may use ispell,simple configuration instead of "ideal" one: ispell, stemmer Of course, some words will not recognized and will leave as is. Also, you may write very simple stemmer using collection of very common endings. Oleg Thanks a lot I'll do that at once! Yonatan Ben-Nes Oleg Bartunov wrote: On Tue, 5 Sep 2006, Yonatan Ben-Nes wrote: Hi all, Well my problem was that I didn't know if Tsearch2 can work on hebrew data without a fitting stemmer, my current solution is to use the 'simple' dictionary so no lexem is returned. I wonder if there is an hebrew stemmer which I can use but I can't seem to find one, so sadly one of the best features of Tsearch2 isn't working for me. Do you use hebrew ispell dictionary ? If I'm wrong please let me know :) Thanks a lot in advance, Yonatan Ben-Nes Oleg Bartunov wrote: You need to provide more details. Oleg On Fri, 1 Sep 2006, Michelle Konzack wrote: Hello Jonatan, Am 2006-08-30 19:09:19, schrieb Yonatan Ben-Nes: I want to use Tsearch2 for a current project I have but I can't seem to find a way to implement it on hebrew content. I have the same problem since I have an UTF-8 Database of arround 380 GByte (growing 100 MByte per day) in over 60 languages and can not search in arabic, farsi and hebrew. It seems, that there is NO solution for those three languages Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org __ NOD32 1.1739 (20060904) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend __ NOD32 1.1739 (20060904) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 __ NOD32 1.1739 (20060904) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com
Re: [GENERAL] Tsearch2 & Hebrew
On Tue, 5 Sep 2006, Yonatan Ben-Nes wrote: No, I didn't thought that it will be useful if it won't be accompanied by an hebrew stemmer which will work with it... I'm wrong? ispell and stemmer are doing the same job, so you may use ispell,simple configuration instead of "ideal" one: ispell, stemmer Of course, some words will not recognized and will leave as is. Also, you may write very simple stemmer using collection of very common endings. Oleg Oleg Bartunov wrote: On Tue, 5 Sep 2006, Yonatan Ben-Nes wrote: Hi all, Well my problem was that I didn't know if Tsearch2 can work on hebrew data without a fitting stemmer, my current solution is to use the 'simple' dictionary so no lexem is returned. I wonder if there is an hebrew stemmer which I can use but I can't seem to find one, so sadly one of the best features of Tsearch2 isn't working for me. Do you use hebrew ispell dictionary ? If I'm wrong please let me know :) Thanks a lot in advance, Yonatan Ben-Nes Oleg Bartunov wrote: You need to provide more details. Oleg On Fri, 1 Sep 2006, Michelle Konzack wrote: Hello Jonatan, Am 2006-08-30 19:09:19, schrieb Yonatan Ben-Nes: I want to use Tsearch2 for a current project I have but I can't seem to find a way to implement it on hebrew content. I have the same problem since I have an UTF-8 Database of arround 380 GByte (growing 100 MByte per day) in over 60 languages and can not search in arabic, farsi and hebrew. It seems, that there is NO solution for those three languages Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org __ NOD32 1.1739 (20060904) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend __ NOD32 1.1739 (20060904) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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] Tsearch2 & Hebrew
No, I didn't thought that it will be useful if it won't be accompanied by an hebrew stemmer which will work with it... I'm wrong? Oleg Bartunov wrote: On Tue, 5 Sep 2006, Yonatan Ben-Nes wrote: Hi all, Well my problem was that I didn't know if Tsearch2 can work on hebrew data without a fitting stemmer, my current solution is to use the 'simple' dictionary so no lexem is returned. I wonder if there is an hebrew stemmer which I can use but I can't seem to find one, so sadly one of the best features of Tsearch2 isn't working for me. Do you use hebrew ispell dictionary ? If I'm wrong please let me know :) Thanks a lot in advance, Yonatan Ben-Nes Oleg Bartunov wrote: You need to provide more details. Oleg On Fri, 1 Sep 2006, Michelle Konzack wrote: Hello Jonatan, Am 2006-08-30 19:09:19, schrieb Yonatan Ben-Nes: I want to use Tsearch2 for a current project I have but I can't seem to find a way to implement it on hebrew content. I have the same problem since I have an UTF-8 Database of arround 380 GByte (growing 100 MByte per day) in over 60 languages and can not search in arabic, farsi and hebrew. It seems, that there is NO solution for those three languages Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org __ NOD32 1.1739 (20060904) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend __ NOD32 1.1739 (20060904) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com
Re: [GENERAL] Porting from ORACLE to PostgSQL
gustavo halperin <[EMAIL PROTECTED]> writes: > 1. The orafce from www.pgfoundry.org doesn't pass the make step, is > looking for some files that doesn't exist, see below: > orafce : make > Makefile:16: ../../src/Makefile.global: No such file or directory > Makefile:17: /contrib/contrib-global.mk: No such file or directory > make: *** No rule to make target `/contrib/contrib-global.mk'. Stop. It looks like it's expecting to be built inside the contrib directory of a configured Postgres source tree. If you have a version that knows about pgxs you can build it outside the source tree with make USE_PGXS=1 regards, tom lane ---(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] Tsearch2 & Hebrew
On Tue, 5 Sep 2006, Yonatan Ben-Nes wrote: Hi all, Well my problem was that I didn't know if Tsearch2 can work on hebrew data without a fitting stemmer, my current solution is to use the 'simple' dictionary so no lexem is returned. I wonder if there is an hebrew stemmer which I can use but I can't seem to find one, so sadly one of the best features of Tsearch2 isn't working for me. Do you use hebrew ispell dictionary ? If I'm wrong please let me know :) Thanks a lot in advance, Yonatan Ben-Nes Oleg Bartunov wrote: You need to provide more details. Oleg On Fri, 1 Sep 2006, Michelle Konzack wrote: Hello Jonatan, Am 2006-08-30 19:09:19, schrieb Yonatan Ben-Nes: I want to use Tsearch2 for a current project I have but I can't seem to find a way to implement it on hebrew content. I have the same problem since I have an UTF-8 Database of arround 380 GByte (growing 100 MByte per day) in over 60 languages and can not search in arabic, farsi and hebrew. It seems, that there is NO solution for those three languages Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org __ NOD32 1.1739 (20060904) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Porting from ORACLE to PostgSQL
> 3. About the ambitious project "protopg", I success to compile this > projects but the parser fall. The parse have a problem with a word > "CONSTRAINT", see below: > protopg-20060905 : ./ora_parser < tes.sql > -- syntax error, unexpected "CONSTRAINT", expecting ORA_ID on line 6 > -- > > -- BEGIN OF UNRECOGNIZED STATEMENT > CREATE TABLE v5templates . cmt_items ( item_id NUMBER ( 7 ) , > template_id NUMBER ( 7 ) , page_title VARCHAR2 ( 100 ) , CONSTRAINT > pk_cmt PRIMARY KEY ( item_id ) ) ; > -- END OF UNRECOGNIZED STATEMENT > > I will try debug it, or maybe send a mail to the developer/contacts. That would be me ;) Protopg knows only a small part of Oracle Syntax. It's origins are a academic background were we had one sample DB and were looking how far we got in translating that one. If you throw random Oracle DBs at it, it will likely stumble over some pieces. Nonetheless it might be partially helpful. If you like it, subscribe to http://pgfoundry.org/mailman/listinfo/protopg-developers If nothing else, when we put out some prerelease, it would be cool if you could help us testing it with your test case :) Bye, Chris. -- Chris Mair http://www.1006.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] how to add postgresql service by command on windows
Merlin Moncure 写道: On 9/5/06, aBBISh <[EMAIL PROTECTED]> wrote: hellow everyone, how to add postgresql service by command on windows? look at pg_ctl command. specifically, the 'register' option. merlin thank you ~ :) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Porting from ORACLE to PostgSQL
Bye, Chris. Thank you, but nothing work. 1. The orafce from www.pgfoundry.org doesn't pass the make step, is looking for some files that doesn't exist, see below: orafce : make Makefile:16: ../../src/Makefile.global: No such file or directory Makefile:17: /contrib/contrib-global.mk: No such file or directory make: *** No rule to make target `/contrib/contrib-global.mk'. Stop. It is designed to be built from the postgresql source tree. If you have a postgresql source tree put the orafce directory in contrib and try again. Sincerely, Joshua D. Drake 2. ora2pg, looks like something that extract DB schemes from a running oracle DB, but I have not ORACLE running. I just have old ORACLE/SQL sources that I need parser to SQL or PostgSQL. 3. About the ambitious project "protopg", I success to compile this projects but the parser fall. The parse have a problem with a word "CONSTRAINT", see below: protopg-20060905 : ./ora_parser < tes.sql -- syntax error, unexpected "CONSTRAINT", expecting ORA_ID on line 6 -- -- BEGIN OF UNRECOGNIZED STATEMENT CREATE TABLE v5templates . cmt_items ( item_id NUMBER ( 7 ) , template_id NUMBER ( 7 ) , page_title VARCHAR2 ( 100 ) , CONSTRAINT pk_cmt PRIMARY KEY ( item_id ) ) ; -- END OF UNRECOGNIZED STATEMENT I will try debug it, or maybe send a mail to the developer/contacts. Any way, thank very much, Gustavo -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] how to add postgresql service by command on windows
On 9/5/06, aBBISh <[EMAIL PROTECTED]> wrote: hellow everyone, how to add postgresql service by command on windows? look at pg_ctl command. specifically, the 'register' option. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Porting from ORACLE to PostgSQL
On 9/5/06, gustavo halperin <[EMAIL PROTECTED]> wrote: Chris Mair wrote: >> I need to porting many old ORACLE-oriented-SQL files and I have many >> problem with this code. Sometimes the code use some types not supported >> in PosgSQL like "number" or "varchar2", there fore, can I write some >> type of declaration (like in c : #define alias_name name) in order to >> make PosgSQL recognize these types? Or there are other solution that you >> recommend to use ? don't the enterprisedb people specialize in oracle porting? merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] The server's LC_CTYPE locale
On Tue, Sep 05, 2006 at 02:56:21PM +0300, Michael Ben-Nes wrote: > For the record: > > Those are the records in my locale.gen > > # cat /etc/locale.gen.old > en_US ISO-8859-1 > he_IL UTF-8 > he_IL ISO-8859-8 Yeah, that's wrong. The first column is the identifier, so the last entry should something like: he_IL.ISO-8859-8 ISO-8859-8 > Why ? i have no idea ( maybe some collisions because the double he_IL ? ). You can't do that. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Tsearch2 & Hebrew
Hi all, Well my problem was that I didn't know if Tsearch2 can work on hebrew data without a fitting stemmer, my current solution is to use the 'simple' dictionary so no lexem is returned. I wonder if there is an hebrew stemmer which I can use but I can't seem to find one, so sadly one of the best features of Tsearch2 isn't working for me. If I'm wrong please let me know :) Thanks a lot in advance, Yonatan Ben-Nes Oleg Bartunov wrote: You need to provide more details. Oleg On Fri, 1 Sep 2006, Michelle Konzack wrote: Hello Jonatan, Am 2006-08-30 19:09:19, schrieb Yonatan Ben-Nes: I want to use Tsearch2 for a current project I have but I can't seem to find a way to implement it on hebrew content. I have the same problem since I have an UTF-8 Database of arround 380 GByte (growing 100 MByte per day) in over 60 languages and can not search in arabic, farsi and hebrew. It seems, that there is NO solution for those three languages Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org __ NOD32 1.1739 (20060904) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com
Re: [GENERAL] The server's LC_CTYPE locale
For the record: Those are the records in my locale.gen # cat /etc/locale.gen.old en_US ISO-8859-1 he_IL UTF-8 he_IL ISO-8859-8 I found out that by removing "he_IL ISO-8859-8" i fixed the problem. Why ? i have no idea ( maybe some collisions because the double he_IL ? ). Cheers Michael Ben-Nes wrote: Hello Im got the following error when the query string was one of the Hebrew chars: SELECT upper('ש'); ERROR: invalid multibyte character for locale HINT: The server's LC_CTYPE locale is probably incompatible with the database encoding. after few minutes while gathering info i stoped getting the previous error and started to get: #SELECT lower('ש'); ERROR: invalid UTF-8 byte sequence detected near byte 0xf9 # SELECT upper('ש'); ERROR: invalid UTF-8 byte sequence detected near byte 0xf9 #SELECT version(); PostgreSQL 8.1.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3 (Debian 4.0.3-1) #show lc_ctype ; he_IL.utf8 #SHOW SERVER_ENCODING; UTF8 Any ideas what the problem ? -- -- Michael Ben-Nes - Internet Consultant and Director. http://www.epoch.co.il - weaving the Net. Cellular: 054-4848113 -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Porting from ORACLE to PostgSQL
Chris Mair wrote: I need to porting many old ORACLE-oriented-SQL files and I have many problem with this code. Sometimes the code use some types not supported in PosgSQL like "number" or "varchar2", there fore, can I write some type of declaration (like in c : #define alias_name name) in order to make PosgSQL recognize these types? Or there are other solution that you recommend to use ? You might look into the contrib package "ora2pg". There's also a more ambitious project called "protopg". That's in pre-alpha state, though. A development snapshots for the *very* *adventurous* can be obtained here: http://protopg.projects.postgresql.org/nightlies/ Bye, Chris. Thank you, but nothing work. 1. The orafce from www.pgfoundry.org doesn't pass the make step, is looking for some files that doesn't exist, see below: orafce : make Makefile:16: ../../src/Makefile.global: No such file or directory Makefile:17: /contrib/contrib-global.mk: No such file or directory make: *** No rule to make target `/contrib/contrib-global.mk'. Stop. 2. ora2pg, looks like something that extract DB schemes from a running oracle DB, but I have not ORACLE running. I just have old ORACLE/SQL sources that I need parser to SQL or PostgSQL. 3. About the ambitious project "protopg", I success to compile this projects but the parser fall. The parse have a problem with a word "CONSTRAINT", see below: protopg-20060905 : ./ora_parser < tes.sql -- syntax error, unexpected "CONSTRAINT", expecting ORA_ID on line 6 -- -- BEGIN OF UNRECOGNIZED STATEMENT CREATE TABLE v5templates . cmt_items ( item_id NUMBER ( 7 ) , template_id NUMBER ( 7 ) , page_title VARCHAR2 ( 100 ) , CONSTRAINT pk_cmt PRIMARY KEY ( item_id ) ) ; -- END OF UNRECOGNIZED STATEMENT I will try debug it, or maybe send a mail to the developer/contacts. Any way, thank very much, Gustavo -- ||\ // \ | \\ // | I'm thinking. \ \\ l\\l_ //| _ _ | \\/ `/ `.|| /~\\ \//~\ | Y | | || Y | | \\ \ // | | \| | |\ / | [ |||| ] \ | o|o | > / ] Y |||| Y [ \___\_--_ /_/__/ | \_|l,--.l|_/ | /.-\() /--.\ | >' `< | `--(__)' \ (/~`----'~\) / U// U / \ `-_>-__-<_-'/ \ / /| /(_#(__)#_)\ ( .) / / ] \___/__\___/`.`' / [ /__`--'__\ |`-'| /\(__,>-~~ __) | |__ /\//\\( `--~~ ) _l |--:. '\/ <^\ /^> | ` ( < \\ _\ >-__-< /_ ,-\ ,-~~->. \ `:.___,/ (___\/___) (/()`---' ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] how to add postgresql service by command on windows
hellow everyone, how to add postgresql service by command on windows? thanks. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Two billion records ok?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Nick Bower wrote: > We're considering using Postgresql for storing gridded metadata - each point > of our grids has a variety of metadata attached to it (including lat/lon, > measurements, etc) and would constitute a record in Postgresql+Postgis. > > Size-wise, grids are about 4000x700 and are collected twice daily over say 10 > years. As mentioned, each record would have up to 50 metadata attributes > (columns) including geom, floats, varchars etc. > > So given 4000x700x2x365x10 > 2 billion, is this going to be a problem if we > will be wanting to query on datetimes, Postgis lat/lon, and integer-based > metadata flags? > > If however I'm forced to sub-sample the grid, what rule of thumb should I be > looking to be constrained by? > > Thanks for any pointers, Nick Tablespaces and table partitioning will be crucial to your needs. I'm not sure if you can partition indexes, though. And too bad that compressed bit-map indexes have not been implemented yet. For indexes with high "key cardinality", they save a *lot* of space, and queries can run a lot faster. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE/Ur7S9HxQb37XmcRAsKLAKDnC36QSzRuaedSsXe+rQp3fbDbOgCfSwlQ ip2em5mEmXF45kek2rHKJvw= =uqTK -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq