[SQL] constraint
Hi list, I need your help. How I can delete or DROP a constraint? I use POSTGRESQL 7.0.3 _ MSN. Más Útil cada Día. http://www.msn.es/intmap/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] constraint
Hi Ricardo, I assume you're talking about foreign key constraints? Dropping a constraint is a real pain in all versions of Postgres up to and including 7.2.1. You will need to manually drop the RI trigger on the child table and the two triggers on the parent table. Techdocs has some information on doing this here: http://techdocs.postgresql.org/techdocs/hackingreferentialintegrity.php Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Ricardo Javier > Aranibar León > Sent: Wednesday, 3 July 2002 4:08 AM > To: [EMAIL PROTECTED] > Subject: [SQL] constraint > > > > > Hi list, > I need your help. > How I can delete or DROP a constraint? > I use POSTGRESQL 7.0.3 > > _ > MSN. Más Útil cada Día. http://www.msn.es/intmap/ > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Why doesn't it use indexes?
1. ANALYZE both tables. Go 'VACUUM ANALYZE;' to vacuum and analyze your tables. Analyzing means to update the planner statistics for the tables, which might make Postgres use your indices. 2. If you tables are very small (eg. only a few hundred rows) then using an index is usually slower than just scanning the table, so Postgres won't use the index until the table grows. (So long as you regularly update the planner statistics) Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Ahti Legonkov > Sent: Tuesday, 2 July 2002 11:47 PM > To: [EMAIL PROTECTED] > Subject: [SQL] Why doesn't it use indexes? > > > Hi, > > I have this query: > select * from reo inner join usr on reo.owner_id=usr.user_id > > I also have created these indexes: > CREATE INDEX "owner_id_reo_key" ON reo (owner_id); > CREATE INDEX "user_id_user_key" ON usr (user_id); > > Explain tells me this: > Merge Join (cost=1341.74..1481.12 rows=299697 width=461) >-> Sort (cost=775.05..775.05 rows=6629 width=328) > -> Seq Scan on apartment_reo reo (cost=0.00..354.29 rows=6629 > width=328) >-> Sort (cost=566.69..566.69 rows=4521 width=133) > -> Seq Scan on USER usr (cost=0.00..292.21 rows=4521 width=133) > > Why it does not use indexes I have created? > > -- > Ahti Legonkov > > > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pg_restore cannot restore function
In the case that you moved your backup to another system where possibly the shared library (.so) where the function exists is on a different location then thats the problem, in which case you only need to recreate the function (with the same isstrict,iscachable attributes). -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Why doesn't it use indexes?
On Tue, 2 Jul 2002, Ahti Legonkov wrote: Check the actual time by explain analyze. If sequential scan (your table is small e.g.) is faster then there is no need for index use. Also check the enable_indexscan variable. > Hi, > > I have this query: > select * from reo inner join usr on reo.owner_id=usr.user_id > > I also have created these indexes: > CREATE INDEX "owner_id_reo_key" ON reo (owner_id); > CREATE INDEX "user_id_user_key" ON usr (user_id); > > Explain tells me this: > Merge Join (cost=1341.74..1481.12 rows=299697 width=461) >-> Sort (cost=775.05..775.05 rows=6629 width=328) > -> Seq Scan on apartment_reo reo (cost=0.00..354.29 rows=6629 > width=328) >-> Sort (cost=566.69..566.69 rows=4521 width=133) > -> Seq Scan on USER usr (cost=0.00..292.21 rows=4521 width=133) > > Why it does not use indexes I have created? > > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Localization
Hi all Is there a simple way to localize in foreign language error messages without modifying and compiling the sources again ? It should be useful for final users who don't read Shakespeare in the original version ;) Eric GRIMOIS Analyste programmeur SEI - CPAM du Val d'Oise ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pg_restore cannot restore function
This is not the case, because those db on a same server, it's I dump data from one db and try restore one of it function into another db. Thanks for your response anyway. Jie Liang -Original Message- From: Achilleus Mantzios [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 03, 2002 12:52 AM To: Jie Liang Cc: 'Jan Wieck'; 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: [SQL] pg_restore cannot restore function In the case that you moved your backup to another system where possibly the shared library (.so) where the function exists is on a different location then thats the problem, in which case you only need to recreate the function (with the same isstrict,iscachable attributes). -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Localization
GRIMOIS Eric wrote: > Hi all > > Is there a simple way to localize in foreign language error messages without > modifying and compiling the sources again ? > It should be useful for final users who don't read Shakespeare in the > original version ;) Uh, we have error messages localization in 7.2. Not sure how to enable it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Selecting data from a table created in another database...
I don't know if this can be done... In MSSQL Server I can access a table created in another database (on the same server, of course) by using the following syntaxis... select * from databasename..tablename where condition; Can I do this in postgres? I'm using version 7.2 on a redhat server... Thank you for your help... Ligia ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] How do I access the current row from a trigger?
Hi, I have a table as: create table sections ( id serial not null primary key, parent_idreferences sections(id) ); there is only one row where the parent_id is NULL, and that is the root section, all others refers to a section. Now I want to create a trigger, so that whenever I delete a section I can delete all sections that has the deleted section as parent, which in turn will run the triggers for the deleted sections. Thus I will be able to clear an entire branch with only one SQL command from the client. consider the content: id parent_id 0 NULL 1 0 2 1 3 2 and when I run the command delete from sections where id=1; I want this to trigger a function which will do delete from sections where parent_id=( 'id' field in the current row[1] ) which in turn will trigger the same function to do delete from sections where parent_id=( 'id' field in the current row[2] ) is there any way to do this in PostgreSQL? I couldn't find a way of refering to the current row in a trigger in the postgresql reference. Sincerely, /Magnus Sjöstrand ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] pg_restore cannot restore function
OK, we figured it out. The problem is the documentation confused me!!! In man page of pg_restore: -P function-name --function=function name Specify a procedure or function to be restored. User will assume that syntax of restoring a function is same as restoring a table, but it's not true, it's slightly different. To restore a table: pg_restore -Rxt mytable -d mydb2 dbf works, but to restore a function: pg_restore -P myfunction -d mydb2 dbf won't work, and you need to use: pg_restore -P "\"myfunction\" (args and type)" -d mydb2 dbf to make it work! I believe that the man page of pg_restore should be improved. Thanks. Jie Liang -Original Message- From: Jan Wieck [mailto:[EMAIL PROTECTED]] Sent: Monday, July 01, 2002 11:14 AM To: Jie Liang Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: [SQL] pg_restore cannot restore function Jie Liang wrote: > > Oops,my OS is FreeBSD4.3 PostgreSQL7.2 I cannot see such an error message in the pg_restore sources at all. Are you sure to use the right versions together? Jan > > Thanks > > Jie Liang > > -Original Message- > From: Jie Liang > Sent: Friday, June 28, 2002 1:46 PM > To: 'Jan Wieck' > Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' > Subject: RE: [SQL] pg_restore cannot restore function > > No any error msg in the logfile, I didn't see any create function statement > in my logfile which I enabled the query log. > This function is written in PL/pgSQL which is enabled in target db, > If I pg_dump the schema into a plain text file, I can see its defination > there, I can easily copy & paste (restore) it into mydb2. > however, I failed to restore it by using flag -P with compressed file. > I also tried to use > su postgres -c "/usr/local/pgsql/bin/pg_restore --function=myfunction > --dbname=mydb2 dbf" > error msg > pg_restore: [archiver] could not open input file: No such file or directory > > weird??? > > I use > pg_restore -Rxt mytable -d mydb2 dbf > have no such a problem, it works. > > Is any syntax error?? > I am confused by documentation now! > Is it a bug > > Thanks > > Jie Liang > > -Original Message- > From: Jan Wieck [mailto:[EMAIL PROTECTED]] > Sent: Friday, June 28, 2002 12:39 PM > To: Jie Liang > Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' > Subject: Re: [SQL] pg_restore cannot restore function > > Jie Liang wrote: > > > > I use > > pg_dump -Fc mydb > dbf > > then I create another db by: > > createdb mydb2 > > I use > > pg_restore -P myfunction -d mydb2 dbf > > > > cannot restore myfunction into mydb2 > > > > why?? > > Good question. Is there any error message in the postmaster log? > > If the function is written in a procedural language, is that language > enabled in the target database? If the function is written in the SQL > language, do all underlying objects like tables and views exist? If it's > a C language function, does the shared object containing the function > exist at the expected location? > > Jan > > -- > > #==# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #== [EMAIL PROTECTED] # -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Possible Bug regarding temp tables (sql or psql?)
Forgot to mention that adding DROP TABLE v_idx ; before the END WORK will fix things. However, I was under the impression that temporary tables would go away after a transaction in which they were created was committed. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Selecting data from a table created in another database...
Ligia Pimentel wrote: > I don't know if this can be done... > > In MSSQL Server I can access a table created in another database (on the > same server, of course) by using the following syntaxis... > > select * from databasename..tablename where condition; > > Can I do this in postgres? > > I'm using version 7.2 on a redhat server... Sorry, you can't do cross-db queries with PostgreSQL. Take a look at /contrib/dblink as an option. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] bit field changes in 7.2.1
I'm on a Linux RH 7.2 system, which came with Pg 7.1.2 (I think). When
there, I prototyped some code that worked well, and looked like:
create table ref_sp
(
name varchar(10),
sname char(1),
bitmask bit(6)
);
insert into ref_sp values ('one', '1', b'01');
insert into ref_sp values ('two', '2', b'10');
insert into ref_sp values ('four', '4', b'000100');
insert into ref_sp values ('eight', '8', b'001000');
insert into ref_sp values ('sixteen', 's', b'01');
insert into ref_sp values ('thirtytwo', 't', b'10');
create table emp
(
id int,
name varchar(30),
specialties bit(6)
);
insert into emp values (1, 'mary_124', b'000111');
insert into emp values (2, 'joe_14st', b'110101');
Which allowed me to find out who had what bit (specialty) set with:
select sp.name
from emp s, ref_sp sp
where s.specialties & sp.bitmask != b'0'::bit(6)
and s.name = 'joe_14st' ;
EXCEPT that now fails in 7.2.1 (I just upgraded this afternoon). It
forces me to use "b'00'" instead of "b'0'::bit(6)". Searching thru
the docs, I find a note that says:
---
Note: Prior to PostgreSQL 7.2, BIT type data was zero-padded on the
right. This was changed to comply with the SQL standard. To implement
zero-padded bit strings, a combination of the concatenation operator and
the substring function can be used.
---
Obviously the source of my problem. However, whoever wrote that note
didn't say how to do it (examples are *SO* useful), and I can't imagine
the solution.
* Can anyone clue me into how I can do that, in SELECT / INSERT / UPDATE
statements?
* Or must I resort to doing the equivalent in Perl as I create the SQL?
* Or is there a backward-compatibility flag?
Thanks!
Kevin
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Possible Bug regarding temp tables (sql or psql?)
When using the attached script in psql, the temp variables disappear as far as \distv shows, but running the script a second time fails. To reproduce, save the following script as bug.sql, then start psql on an test database. \i bug.sql \distv -- no relations should be shown \i bug.sql -- this will fail. However, if you run psql again, you can \i bug.sql successfully. It will only fail if run twice in the same script. cheers -mark -- Hardy Boys: too easy. Nancy Drew: too hard! - Fry CREATE TABLE foo ( foo_idx SERIAL PRIMARY KEY, foo INTEGER ) ; CREATE TABLE bar ( bar_idx SERIAL PRIMARY KEY, foo_idx INTEGER REFERENCES foo, bar INTEGER ) ; INSERT INTO foo ( foo ) VALUES ( 111 ) ; INSERT INTO foo ( foo ) VALUES ( 222 ) ; INSERT INTO foo ( foo ) VALUES ( 333 ) ; BEGIN WORK ; SELECT foo_idx INTO TEMP v_idx FROM foo WHERE foo.foo = 222 ; INSERT INTO bar ( foo_idx, bar ) VALUES ( v_idx.foo_idx, 888 ) ; END WORK ; DROP TABLE foo ; DROP SEQUENCE foo_foo_idx_seq ; DROP TABLE bar ; DROP SEQUENCE bar_bar_idx_seq ; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] postgres7.2.1 upgrading
http://www.ca.postgresql.org/sitess.html says that: The current version of PostgreSQL is 7.2.1. NOTE: An initdb will only be required if upgrading from pre 7.2 So, if my current version is 7.2.0 and I want upgrade it to 7.2.1, what file should I download in order to get 'intidb'? if only the 'initdb' is required, then what is the upgrade procedure? I don't need to install whole 7.2.1 at all, do I. my guess is that: 1.shutdown the db; 2.copy initdb of 7.2.1 to the location of initdb of 7.2.0; 3.re_intialize db by running initdb; 4.re_start postmaster. However, no documentation says that. Anybody can tell me? Thanks! Jie Liang ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How do I access the current row from a trigger?
On Wed, 3 Jul 2002, [ISO-8859-1] Magnus Sjöstrand wrote: > Hi, > > I have a table as: > create table sections ( > id serial not null primary key, > parent_idreferences sections(id) > ); > > there is only one row where the parent_id is NULL, and that is the root > section, all others refers to a section. Now I want to create a trigger, > so that whenever I delete a section I can delete all sections that has > the deleted section as parent, which in turn will run the triggers for > the deleted sections. Thus I will be able to clear an entire branch with > only one SQL command from the client. How about just adding ON DELETE CASCADE to the references constraint? But that doesn't answer the general question about access, so... > is there any way to do this in PostgreSQL? I couldn't find a way of > refering to the current row in a trigger in the postgresql reference. I believe it's somewhat dependant on the procedural language used to define the trigger function. In plpgsql, you should be able to use OLD and NEW to get column values like OLD.id in the case of an after delete trigger. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Possible Bug regarding temp tables (sql or psql?)
> Forgot to mention that adding > DROP TABLE v_idx ; > before the END WORK will fix things. However, I was under the > impression that > temporary tables would go away after a transaction in which they > were created > was committed. No - they go away at the end of a _connection_. However, there is now a patch floating around on -hackers that would add an ' ON COMMIT DROP;' option to CREATE TEMP TABLE. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Selecting data from a table created in another database...
Hi Ligia, When I need to do this I use a scripting language like PHP, Perl or ColdFusion to select from one db and insert into another. As a matter of fact I had to do this exercise just yesturday. I like it as I find I have much more freedom to gather data, organise it then insert it. It's easy to view the output of your selects on the screen and when happy do the insert(s). Hope this helps. Regards Rudi Starcevic. Ligia Pimentel wrote: >I don't know if this can be done... > >In MSSQL Server I can access a table created in another database (on the >same server, of course) by using the following syntaxis... > >select * from databasename..tablename where condition; > >Can I do this in postgres? > >I'm using version 7.2 on a redhat server... > >Thank you for your help... > >Ligia > > > > > > > > >---(end of broadcast)--- >TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > > > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] postgres7.2.1 upgrading
> http://www.ca.postgresql.org/sitess.html > says that: > The current version of PostgreSQL is 7.2.1. > NOTE: An initdb will only be required if upgrading from pre 7.2 > > So, if my current version is 7.2.0 and I want upgrade it to > 7.2.1, what file should I download in order to get 'intidb'? > if only the 'initdb' is required, > then what is the upgrade procedure? > I don't need to install whole 7.2.1 at all, do I. > my guess is that: > 1.shutdown the db; > 2.copy initdb of 7.2.1 to the location of initdb of 7.2.0; > 3.re_intialize db by running initdb; > 4.re_start postmaster. You do not need to do anything special. Just install the new version of postgres over your existing version (making a backup dump of your data first, of course). 7.2.0 is the same as 7.2, it's not 'pre 7.2'. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
