[GENERAL] FOREIGN KEY: difference between NOT DEFERRABLE and INITIALLY IMMEDIATE

Hi, What's the difference between NOT DEFERRABLE and INITIALLY IMMEDIATE for a FOREIGN KEY specification ? It seems they are both defining the same: the moment the constraint will be checked: the instant the statement is processed or the end of transaction. Thanks, Gabi :-)

[GENERAL] FOREIGN KEY: MATCH FULL

Hi, What does it mean the MATCH FULL parameter in a foreign key specfication ? It seems it is only valid for a multi-column foreign key: What's a multi-column foreign key ? I've tried to search this in Bruce's book but I have a lot of problems to connect to it at http://www.postgresql.org/doc

[GENERAL] Problem with drop table within a transaction

Hi, I have a problem when doing drop table within a transaction: It seems that if you execute a drop table sentence but after the transaction is aborted because an error occurs, the result is a corruption of the database: the drop table sentence deletes the physical files corresponding to the ta

[GENERAL] Table whose name has Capital letters

Hi , I have created a table with the name 'Nov2000' and now it is impossible to access it, because psql is always searching for the table 'nov2000'. The only solution I have found is to manually modify the pg_class table and the name of the physical file associated to the table. Have I done som

[GENERAL] Index on a function(field)

Hi, Is it possible to create an index using a function(field) sintaxis ? For instance: CREATE INDEX "i1_cdu" on "cdu" using btree ( substr(cdu_code,1,1) "varchar_ops" ); If not, should I alter the table to include a field with the value 'substr(codigo,1,1)'. Thanks Gabi :-) --

[GENERAL] Vacuumdb message: AbortTransaction and not in-progress ...

Hi, We have some db's in our server. When executing a vacuumdb, ONLY FOR SOME of them, the following message is shown: AbortTransaction and not in in-progress state After this, the vacuum process is aborted, so we cannot vacuum these 'problematic' db's. What can we do ? Gabi :-) --

[GENERAL] Confussion with table-lock levels and isolation levels

Hi fellows ! I would only want to ask some questions concerning table-locking levels and isolation levels: * First of all: should I assume that AccessXXX modes imply locking the complete table and Row imply locking only the rows which have been accessed ? Will then the conflicts be so

[GENERAL] Problems with operator '%' within a select

Hi ! I've just had some problems using the operator '%' within a select. This is an example: "select codigo from codis where codigo like '%3' " And i've found that all the rows which contains more than one three won't be selected. (For example: values 'a34ree3', '34fg3') It seems as if you pu

Re: Re: [GENERAL] Regular expressions syntax: is \ the escape character ?

Thanks a million to you all. Finally, as someone in the list suggested, i'm using the double backslash (\\) and it seems it works ok. The only problem i have is when i want to escape a single quote (') or the backslash (\). For example: i have one row with the value 'ONE\SECOND' I try to recove

[GENERAL] locale & mb support

Hi all: I'm using Postgres-6.5.3 with Red Hat 6.0 and I've a problen using LC_TYPE and LC_COLLATE ... I compiled Postgres --wiht-locale --with-mb=LATIN1. I did initdb -e LATIN1 And finally I createdb -E LATIN1 test. I also have defined LC_TYPE=LATIN1 and L

[GENERAL] Regular expressions syntax: is \ the escape character ?

Hi, I'm using regular expressions in 6.5.3 version but i cannot find the character to escape special caracters as ?, $, ^. I've tried with \ but it doesn't works. Example: i want to find the row with the value 'fisica?' and i do: select title from libros where title ~* 'fisica\?' ; but i also

[GENERAL] Problem with LIKE operator

Hi, I have some problems using LIKE within strings which have brackets. For example if i do: select code from codes where code like '(4)' ; i do not obtain nothing. But in the DB indeed there is a row whose code is '(4)'. I have tried to escape the brackets with '\\(4\\)' or with ' \(4\)' but

Re: [GENERAL] Problem with LIKE operator

Hi, Finally I have found the problem: the table was corrupted, so i have recreated it and it works fine. I wonder if instead of recreating it i would have had to execute a vacuum. Thanks to you all for your help and your suggestions (i will keep them for the future). Gabi :-)