[SQL] UPDATE: Apparent referential integrity bug in PL/pgSQL
The workaround I mentioned in the previous message doesn't turn out to work after all. The series of statements seems to require explicitly calling two functions. ---(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] TRIGGERed INSERTS
Howdy; I'm writing a "script" to pre-populate a database system that's already in place. The database system is in an advanced stage of development and includes many stored procedures and TRIGGERS. The "script" is really a stored procedure designed to be executed by the system's admin guy as part of "user system start-up." Within the script I'm calling stored procedures to create "items" (which in my system are "data" table records with corresponding records in an ordering/pointer table). Some of the data tables have "AFTER INSERT" TRIGGERs on them that, in turn, insert some subordinate items into parallel data tables and the central pointer/ordering table. As part of the pre-population "script" I call "add_item" and then want to UPDATE a record in a different data table created by one of the TRIGGERS (i.e. a "sub-record" if you will). When I use a SELECT INTO within the "script" to discover the pointer record key for this new sub-record (so I can go UPDATE it), the SELECT INTO comes up with NULL. After my "script" is done, however, a SELECT from the psql command line discovers the pointer record's key value with no problem! Am I not waiting long enough within my "script" for the engine to settle out? Are the results of that transaction not available to me until I roll out of the transaction initiated by my "script" procedure? I'm somewhat baffled as to why the SELECT INTO is coming up NOT FOUND within the scope of my "script" procedure. I did a search of the mailing lists on "TRIGGERS" but didn't turn up any clues. Thanks for your help! Martin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] error...what to do?
On Sat, 12 Oct 2002, George wrote: > beckerbalab2=> select * from ffix_ability; > > ability_name | ability_description | > type| cost > beckerbalab2=> SELECT ffix_ability.name, ffix_ability.cost ^^ ffix_ability.ability_name, right? Same below. > > beckerbalab2-> FROM ffix_can_learn NATURAL JOIN ffix_ability > > beckerbalab2-> WHERE ffix_can_learn.character_name = 'Zidane' > > beckerbalab2-> EXCEPT --this is the difference operator hope it works > > beckerbalab2-> SELECT ffix_ability.name, ffix_ability.cost > > beckerbalab2-> FROM ffix_can_learn NATURAL JOIN ffix_ability > > beckerbalab2-> WHERE ffix_can_learn.character_name = 'Steiner'; > > ERROR: No such attribute or function 'name' > > beckerbalab2=> > > > > ---(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] Apparent referential integrity bug in PL/pgSQL
I have a function that operates on two tables A and B, such that B has a foreign key on A, as follows: INSERT INTO A (...) several times INSERT INTO B (...) several times, with foreign keys pointing to the new members of A DELETE FROM A (...), possibly including some of the newly added members Even though B's foreign key is defined ON DELETE CASCADE, I get a referential integrity violation when I run this function. If instead, I comment out the DELETE statement, start a transaction block, run the function, run the DELETE statement, and end the transaction, no errors occur. To run those statements with one function call, I need to split the INSERTs and DELETEs into separate functions, and call them separately from a third function. I am using version 7.2.2. Has this been corrected in the beta versions or can someone confirm this for me? ---(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] Locking that will delayed a SELECT
>Suppose I have a transaction (T1) which executes a > complicated stored procedure. While T1 is executing, > trasaction #2 (T2) begins to execute. > > T1 take more time to execute that T2 in such a way > that T2 finished earlier than T1. The result is that > t2 returns set of data before it can be modified by > T1. > >Given the above scenario. Is there a way such that > while T2 will only read that value updated by T1 (i.e. > T2 must wait until T1 is finished) ? What locks should > I used since a portion of T1 contains SELECT > statements? Should I used the "SERIALIZABLE > isolation". What's wrong about this question? I'm interested in an answer, too. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Locking that will delayed a SELECT
On Fri, 18 Oct 2002, Christoph Haller wrote: > >Suppose I have a transaction (T1) which executes a > > complicated stored procedure. While T1 is executing, > > trasaction #2 (T2) begins to execute. > > > > T1 take more time to execute that T2 in such a way > > that T2 finished earlier than T1. The result is that > > t2 returns set of data before it can be modified by > > T1. > > > >Given the above scenario. Is there a way such that > > while T2 will only read that value updated by T1 (i.e. > > T2 must wait until T1 is finished) ? What locks should > > I used since a portion of T1 contains SELECT > > statements? Should I used the "SERIALIZABLE > > isolation". > > What's wrong about this question? > I'm interested in an answer, too. > > Regards, Christoph Second small xaction T2's select statemenst will use values commited before these select statements started. That is, these queries will NOT see values updated by T1. The problem is solved a) Using SERIALIZABLE XACTION ISOLATION LEVEL b) in T2 using "select for update" instead of select. That way T2's queries will wait untill T1's statements commit or rollback. The SERIALIZABLE XACTION ISOLATION LEVEL scheme is heavier and thus maybe less efficient. See http://www.postgresql.org/idocs/index.php?mvcc.html == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] Locking that will delayed a SELECT
On Fri, 18 Oct 2002, Achilleus Mantzios wrote: > Second small xaction T2's select statemenst will use values commited > before these select statements started. That is, these queries > will NOT see values updated by T1. > > The problem is solved > > a) Using SERIALIZABLE XACTION ISOLATION LEVEL > b) in T2 using "select for update" instead of select. That way T2's > queries will wait untill T1's statements commit or rollback. > > The SERIALIZABLE XACTION ISOLATION LEVEL scheme is heavier > and thus maybe less efficient. Also the serialization must be secured from the application side. In your case the program invoking T2 must be prepared to retry the transaction if T1 commits in the meantime. With SERIALIZABLE XACTION ISOLATION LEVEL T2's select statements will use values commited before T2 began (and not before these select statements began as in the READ COMMITED (default) XACTION ISOLATION LEVEL case) == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece 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] Can I create working trigger on view
Hello!
Please, help me!
Can I create working trigger on view?
The problem is:
I need a plpgsql function that execute on insert (or update,
or delete) into view and knows the *OLD* and *NEW*.
(Number of fields can be more than 16)
Something like this:
Create Sequence id;
Create Table t1(id1 int4, last_name varchar(32), time_create_t1);
Create Table t2(id2 int4, first_name varchar(32), time_create_t2);
Create View name as
Select * from t1 a, t2 b where a.id1=b.id2;
Create Rule rl_ins_nameas on INSERT to name do instead nothing;
-- without it trigger is not allowed
Create Function fn_ins_name() returns opaque as
'
Declare
v_time_create timestamp;
Begin
v_tm_create:=current_timestamp;
Insert into t1(id1, last_name, time_create_t1)
values(nextval(''id''), new.last_name, v_time_create);
Insert into t2(id2, first_name, time_create_t2)
values(currval(''id''), new.first_name, v_time_create);
return null;
End;
' Language 'plpgsql';
Create Trigger tr_ins_name before INSERT on now_cis_user
for each row execute procedure fn_ins_name;
This example is very simple. The trigger is not fired.
Sorry for my English.
--
Thanks for everybody.
Acue.
---(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] Can I create working trigger on view
Acue, > Can I create working trigger on view? > > The problem is: > I need a plpgsql function that execute on insert (or update, > or delete) into view and knows the *OLD* and *NEW*. > (Number of fields can be more than 16) No. Create a RULE instead, which can be created on a view: http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/rules.html BTW, you can increase the number of parameters accepted by functions by re-compiling postgres. Also, 7.3 will have 32 as the default. -Josh Berkus ---(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] Can I create working trigger on view
"Acue" <[EMAIL PROTECTED]> writes: > Can I create working trigger on view? Not usefully. No tuple will ever actually be inserted into the view, therefore the trigger will never fire. regards, tom lane ---(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] Locking that will delayed a SELECT
Achilleus Mantzios <[EMAIL PROTECTED]> writes: >> The problem is solved >> >> a) Using SERIALIZABLE XACTION ISOLATION LEVEL >> b) in T2 using "select for update" instead of select. That way T2's >> queries will wait untill T1's statements commit or rollback. ISTM that SERIALIZABLE mode will not solve this problem, since by definition you want T2 to see results committed after T2 has started. A simple answer is to have T1 grab an ACCESS EXCLUSIVE lock on some table to block T2's progress. If that locks out third-party transactions that you'd rather would go through, you can probably use a lesser form of lock --- but then both T1 and T2 will have to cooperate since each will need to explicitly take a lock. I gave a presentation at the O'Reilly conference this year that covered some of these issues. Looks like you can still get the slides from http://conferences.oreillynet.com/cs/os2002/view/e_sess/2681 regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] TRIGGERed INSERTS
"Martin Crundall" <[EMAIL PROTECTED]> writes: >Some of the data tables have "AFTER INSERT" TRIGGERs on them that, in > turn, insert some subordinate items into parallel data tables and the > central pointer/ordering table. It looks to me like AFTER triggers are fired upon return to the main loop in postgres.c, thus only at the end of a querystring sent by the client. This is perhaps wrong, but I'm not sure that allowing them to fire during plpgsql functions would be a good thing either. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Locking that will delayed a SELECT
--- Tom Lane <[EMAIL PROTECTED]> wrote: > Achilleus Mantzios <[EMAIL PROTECTED]> > writes: > >> The problem is solved > >> > >> a) Using SERIALIZABLE XACTION ISOLATION LEVEL > >> b) in T2 using "select for update" instead of > select. That way T2's > >> queries will wait untill T1's statements commit > or rollback. > > ISTM that SERIALIZABLE mode will not solve this > problem, since by > definition you want T2 to see results committed > after T2 has started. > > A simple answer is to have T1 grab an ACCESS > EXCLUSIVE lock on some > table to block T2's progress. If that locks out > third-party > transactions that you'd rather would go through, you > can probably use > a lesser form of lock --- but then both T1 and T2 > will have to cooperate > since each will need to explicitly take a lock. > If I will be using ACCESS EXCLUSIVE lock, should I should SELECT statement only in T1 instead SELECT...FOR UPDATE statement since SELECT...FOR UPDATE uses ROW SHARE MODE lock since the ACCESS EXCLUSIVE lock is in conflict with other lock mode (besides it is pointless to use other locks when using ACCESS EXCLUSIVE lock) ? *** For clarification *** In the SQL command reference of PostgreSQL: in SELECT statement section : "The FOR UPDATE clause allows the SELECT statement to perform exclusive locking of selected rows" in LOCK statement section : "ROW SHARE MODE Note: Automatically acquired by SELECT ... FOR UPDATE." - Isn't this two statements somewhat conflicting? Is the PostgreSQL meaning of SHARE lock and EXCLUSIVE LOCK similar to the definition of Elmasri/Navathe in the book "Fundamentals of Database Systems" where a SHARE lock is a "read lock", while an EXCLUSIVE lock is a "write lock"? Thank you in advance. ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Apparent referential integrity bug in PL/pgSQL
On Fri, 18 Oct 2002, Brian Blaha wrote: > I have a function that operates on two tables A and B, such that B has a > foreign key on A, as follows: > INSERT INTO A (...) several times > INSERT INTO B (...) several times, with foreign keys pointing to the new > members of A > DELETE FROM A (...), possibly including some of the newly added members > > Even though B's foreign key is defined ON DELETE CASCADE, I get a > referential integrity > violation when I run this function. If instead, I comment out the DELETE > statement, start a > transaction block, run the function, run the DELETE statement, and end > the transaction, no > errors occur. To run those statements with one function call, I need to > split the INSERTs and > DELETEs into separate functions, and call them separately from a third > function. I am using > version 7.2.2. Has this been corrected in the beta versions or can > someone confirm this for me? No it hasn't, but I see what's happening. The rows from b are being checked before they are deleted by the trigger that runs after the check. I think that to do this case right, all of the ref actions would need to be done before any of the noaction or check triggers. This is technically what the spec says to do afaict, but I don't think we'd seen a case before where it matters. ---(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] TRIGGERed INSERTS
Thanks Tom. The work-around was to create the "script" in SQL -- using SELECT INTO statements to capture the key values -- instead of creating the "script" as a stored procedure. Seems like a "transaction" issue. Having transactions occur at the SELECT level is very intuitive and a really nice, "protect-us-from-ourselves" feature that I'm not sure I'd want to mess with. Having the ability to execute a stored procedure outside the scope of a SELECT would ultimately resolve this. A topic for another day ... unless I missed something in an update somewhere. Anyway, thanks again. > "Martin Crundall" <[EMAIL PROTECTED]> writes: >>Some of the data tables have "AFTER INSERT" TRIGGERs on them that, >> in >> turn, insert some subordinate items into parallel data tables and the >> central pointer/ordering table. > > It looks to me like AFTER triggers are fired upon return to the main > loop in postgres.c, thus only at the end of a querystring sent by the > client. This is perhaps wrong, but I'm not sure that allowing them to > fire during plpgsql functions would be a good thing either. > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Is there anyway to do this?
I have a table Table "Users" Column | Type | Modifiers ---++--- userid| character varying(40) | not null username | character varying(64) | I want to get all the distinct first character of all usernames. And do it in a way that is the most portable to MS SQL server. Is there anyway? Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(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] Is there anyway to do this?
On Fri, Oct 18, 2002 at 12:45:56 -0400, Wei Weng <[EMAIL PROTECTED]> wrote: > I have a table > >Table "Users" > Column | Type | Modifiers > ---++--- > userid| character varying(40) | not null > username | character varying(64) | > > I want to get all the distinct first character of all usernames. And do > it in a way that is the most portable to MS SQL server. > > Is there anyway? Looking through the documentation on string functions would be helpful. An example solution is: select distinct substring(username from 1 for 1) from users; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Is there anyway to do this?
MSSQL Server does not recognize this syntax, but it does accept select distinct substring(username, 1, 1) Ian A. Harding Programmer/Analyst II Tacoma-Pierce County Health Department (253) 798-3549 [EMAIL PROTECTED] >>> Bruno Wolff III <[EMAIL PROTECTED]> 10/18/02 10:13AM >>> On Fri, Oct 18, 2002 at 12:45:56 -0400, Wei Weng <[EMAIL PROTECTED]> wrote: > I have a table > >Table "Users" > Column | Type | Modifiers > ---++--- > userid| character varying(40) | not null > username | character varying(64) | > > I want to get all the distinct first character of all usernames. And do > it in a way that is the most portable to MS SQL server. > > Is there anyway? Looking through the documentation on string functions would be helpful. An example solution is: select distinct substring(username from 1 for 1) from users; ---(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
[SQL] TRIGGERed INSERTS
Howdy; I'm writing a "script" to pre-populate a database system that's already in place. The database system is in an advanced stage of development and includes many stored procedures and TRIGGERS. The "script" is really a stored procedure designed to be executed by the system's admin guy as part of "user system start-up." Within the script I'm calling stored procedures to create "items" (which in my system are "data" table records with corresponding records in an ordering/pointer table). Some of the data tables have "AFTER INSERT" TRIGGERs on them that, in turn, insert some subordinate items into parallel data tables and the central pointer/ordering table. As part of the pre-population "script" I call "add_item" and then want to UPDATE a record in a different data table created by one of the TRIGGERS (i.e. a "sub-record" if you will). When I use a SELECT INTO within the "script" to discover the pointer record key for this new sub-record (so I can go UPDATE it), the SELECT INTO comes up with NULL. After my "script" is done, however, a SELECT from the psql command line discovers the pointer record's key value with no problem! Am I not waiting long enough within my "script" for the engine to settle out? Are the results of that transaction not available to me until I roll out of the transaction initiated by my "script" procedure? I'm somewhat baffled as to why the SELECT INTO is coming up NOT FOUND within the scope of my "script" procedure. I did a search of the mailing lists on "TRIGGERS" but didn't turn up any clues. Thanks for your help! Martin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] object oriented vs relational DB
Josh Berkus schrieb: I've looked into OODBMS for my business. However, I've kept from using any in production for one simple reason: lack of a standard. There is no international standard for OODBMS, meaning that each OODBMS is its own animal and databases are not at all portable between different software packages. Nor is your knowledge of one OODBMS even 20% tranferrable to another. Same here. I've done a project with Versant five years ago and we were badly hit by the decision of the database vendor to give up a special language binding - just as we had finished the project. For that reason I would never ever do a project again with oodbms - no standard, no way of switching the database product. The problem is, that the ODMG group is so much influenced now by the Java hype, that nobody seems to expect, that the ODMG 3.0 standard seems to be a real thing. Another view about comp.databases.objects shows us, that the Java world produces one new oodbms each week and they do not care about any standards. Their standard is Java and the rest of the world is meaningless. In comparison to relational databases, this is like turning the clock back to 1980, when every database implementation was idiosyncratic and I Well, well spoken and still unbelievable ! :-) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] functions that return a dataset or set of rows
Is it not possible in 7.2? Gaetano Mendola wrote: "Brian Ward" <[EMAIL PROTECTED]> wrote in message news:aofqbd$10v5$1@;news.hub.org... How do I create a function that returns a set of row; I can't seem to find the datatype that the return set should be declared as. You should wait for Postgres 7.3 currently in beta. Ciao Gaetano ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [pgadmin-support] hi
lz,
You
could use the plpgsql function language and create a function that tests for the
existence of the file and drop it if it does.
Something like this:
select
dropTableIfExists('test');
The
dropTableIfExists would be the plpgsql function that you would need to
write.
Later,
Mike
Hepworth..
-Original Message-From: lz John
[mailto:[EMAIL PROTECTED]]Sent: Thursday, October 17, 2002 1:07
AMTo: [EMAIL PROTECTED]Cc:
[EMAIL PROTECTED]Subject: [pgadmin-support]
hi
i don't send mail to [EMAIL PROTECTED],but i need
help
how to migrate sql from MS sql server to postgresql?
i'd like to tranfer sql schema from MS
serverExample:***1*if
exists (select * from sysobjects where id = object_id(N'[admin].[test]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [admin].[test]
go***2*CREATE
TABLE [admin].[test] ([test_name] [char] (50) NOT NULL ,[test_value]
[int] NOT NULL ) i can only realize part 2. i don't know
how to realize part 1.in other words,i want to know how to check if a
table exist in
postgresql***2*
create table test( test_name char (50) not
null, test_value int not null )thanks for any
advice!!
Do You Yahoo!?"·¢¶ÌÐÅÓ®ÊÖ»ú,¿ìÀ´²Î¼ÓÑÅ»¢¾ÞÐÇÐã!"
[SQL] Can I create working trigger on view
Hello!
Please, help me!
Can I create working trigger on view?
The problem is:
I need a plpgsql function that execute on insert (or update,
or delete) into view and knows the *OLD* and *NEW*.
(Number of fields can be more than 16)
Something like this:
Create Sequence id;
Create Table t1(id1 int4, last_name varchar(32), time_create_t1);
Create Table t2(id2 int4, first_name varchar(32), time_create_t2);
Create View name as
Select * from t1 a, t2 b where a.id1=b.id2;
Create Rule rl_ins_nameas on INSERT to name do instead nothing;
-- without it trigger is not allowed
Create Function fn_ins_name() returns opaque as
'
Declare
v_time_create timestamp;
Begin
v_tm_create:=current_timestamp;
Insert into t1(id1, last_name, time_create_t1)
values(nextval(''id''), new.last_name, v_time_create);
Insert into t2(id2, first_name, time_create_t2)
values(currval(''id''), new.first_name, v_time_create);
return null;
End;
' Language 'plpgsql';
Create Trigger tr_ins_name before INSERT on now_cis_user
for each row execute procedure fn_ins_name;
This example is very simple. The trigger is not fired.
Sorry for my English.
--
Thanks for everybody.
Akulov Alexander.
e-mail:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] triggers
Hi. Does anyone know if it is posible to make a trigger that execute an external program ? I want to execute a another program on the server when I get a row in one of my tables... -Stian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL]
how to migrate sql from MS sql server to postgresql? i'd like to tranfer sql schema from MS serverExample:***1*if exists (select * from sysobjects where id = object_id(N'[admin].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [admin].[test] go***2*CREATE TABLE [admin].[test] ([test_name] [char] (50) NOT NULL ,[test_value] [int] NOT NULL ) i can only realize part 2. i don't know how to realize part 1.in other words,i want to know how to check if a table exist in postgresql***2* create table test( test_name char (50) not null, test_value int not null ) thanks for any advice!!
[SQL] help!
how to migrate sql from MS sql server to postgresql? i'd like to tranfer sql schema from MS serverExample:***1*if exists (select * from sysobjects where id = object_id(N'[admin].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [admin].[test] go***2*CREATE TABLE [admin].[test] ([test_name] [char] (50) NOT NULL ,[test_value] [int] NOT NULL ) i can only realize part 2. i don't know how to realize part 1.in other words, i want to know how to check if a table exist in postgresql ***2* create table test( test_name char (50) not null, test_value int not null ) thanks for any advice!!
Re: [SQL] foreign keys again
hi folks, For a certain table A, I need to find out the names of the columns who have a foreign key to a specific table B using the catalog.Has anyone done this before entirely using pgsql.I have been through the archieves and have not been able to find the required information. thanks kprasad _ Send and receive Hotmail on your mobile device: http://mobile.msn.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] hi
i'd like to tranfer sql schema from MS serverExample:***1*if exists (select * from sysobjects where id = object_id(N'[admin].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [admin].[test] go***2*CREATE TABLE [admin].[test] ([test_name] [char] (50) NOT NULL ,[test_value] [int] NOT NULL )i can only realize part 2 and must need the fuction that can check data table. create table test(test_name char (50) not null,test_value int not null)thanks for any advice!!Do You Yahoo!? ÑÅ»¢ÓéÀÖÐÂÏʵ½µ×,µç×ÓÖܱ¨¿ìÀÖµ½¼Ò!
Re: [SQL] date
hi folks,
we have a PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC 2.96
installation on pogo linux 7.2
we are facing a data problem when we do the following
select to_char(to_date('1969-10-22','-MM-DD'),'-MM-DD');
ERROR: Unable to convert date to tm
we have been trying to find a solution for this have you found such
instances before it there a method to over come this.
Any help will be greatly appreciated.
thanks
kris
_
Get faster connections -- switch to MSN Internet Access!
http://resourcecenter.msn.com/access/plans/default.asp
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] error...what to do?
The I am trying to do a set difference query. The query question is as follows: 3.Find the names and costs of all abilities that Zidane can learn, but that Steiner cannot. Can anyone help with this ….please. The tables to use are as follows: beckerbalab2=> select * from ffix_ability; ability_name | ability_description | type | cost --+++-- Flee | Escape from battle with high probability. | Active | 0 Cure | Restores HP of single/multiple. | Active | 6 Power Break | Reduces the enemy's attack power. | Active | 8 Thunder Slash | Causes Thunder damage to the enemy. | Active | 24 Auto-Haste | Automatically casts Haste in battle. | Passive | 9 Counter | Counterattacks when physically attacked. | Passive | 8 MP+20% | Increases MP by 20% | Passive | 8 Thievery | Deals physical damage to the target | Active | 8 Fire | Causes Fire damage to single/multiple targets. | Active | 6 Flare | Causes Non-Elemental damage. | Active | 40 Leviathan | Causes water damage to all enemies. | Active | 42 beckerbalab2=> select * from ffix_can_wear; character_name | equipment_name +-- Dagger | Rod Dagger | Aquamarine Zidane | Aquamarine Vivi | Aquamarine Steiner | Diamond Sword Steiner | Ragnarok Dagger | Running Shoes Zidane | Running Shoes Vivi | Running Shoes Steiner | Running Shoes Dagger | Ritual Hat Zidane | Ritual Hat Vivi | Ritual Hat Dagger | Angel Earrings Zidane | Dagger Zidane | The Tower Dagger | Leather Hat Zidane | Leather Hat Vivi | Leather Hat Vivi | Black Robe Steiner | Aquamarine (21 rows) beckerbalab2=> SELECT ffix_ability.name, ffix_ability.cost beckerbalab2-> FROM ffix_can_learn NATURAL JOIN ffix_ability beckerbalab2-> WHERE ffix_can_learn.character_name = 'Zidane' beckerbalab2-> EXCEPT --this is the difference operator hope it works beckerbalab2-> SELECT ffix_ability.name, ffix_ability.cost beckerbalab2-> FROM ffix_can_learn NATURAL JOIN ffix_ability beckerbalab2-> WHERE ffix_can_learn.character_name = 'Steiner'; ERROR: No such attribute or function 'name' beckerbalab2=>
[SQL] How to create secondary key!!
good day, i'm using pgAdmin II as the remote client, after importing the text file to postgresql database; i'm start trying to find the way to create the primary key and secondary key. unfortunely, under the pgAdmin II there aren't any tools to do that. However i managed to fine the way to create the primary key by using the index function but i still cannot create the secondary key in my database. please advise me! Best Regards, /'"`\ ( - - ) --oooO--(_)--Oooo- Nelson Yong [EMAIL PROTECTED] THE BUILDING MATERIALS PEOPLE ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] isAutoIncrement and Postgres
Do any existing drivers / database version combinations support the isAutoIncrement method? If not - does anyone have any suggested workarounds? I am having problems with autogenrated INSERTS because I can't detect an AutoIncrement column. -jm ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Can I search for an array in csf?
One field of a table stores an array of characters in a string fromat as "a,b,c,d". Is anyway to apply a select statement without using stored procedure? Thanks for your input. Vernon ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] error...what to do?
Read the error text: > beckerbalab2=> SELECT ffix_ability.name, ffix_ability.cost ^ > beckerbalab2-> FROM ffix_can_learn NATURAL JOIN ffix_ability > beckerbalab2-> WHERE ffix_can_learn.character_name = 'Zidane' > beckerbalab2-> EXCEPT --this is the difference operator hope it works > beckerbalab2-> SELECT ffix_ability.name, ffix_ability.cost > beckerbalab2-> FROM ffix_can_learn NATURAL JOIN ffix_ability > beckerbalab2-> WHERE ffix_can_learn.character_name = 'Steiner'; > ERROR: No such attribute or function 'name' Your ffix_ability table contains the columns "ability_name",'ability_description","type", and "cost". There's no column called "name". Try again with the line above as SELECT ffix_ability.ability_name ap -- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill [EMAIL PROTECTED] * andrew_perrin (at) unc.edu On Sat, 12 Oct 2002, George wrote: > The I am trying to do a set difference query. The query question is as > follows: 3.Find the names and costs of all abilities that Zidane can > learn, > > but that Steiner cannot. Can anyone help with this ..please. > > > > The tables to use are as follows: > > beckerbalab2=> select * from ffix_ability; > > ability_name | ability_description | > type| cost > > --++ > +-- > > Flee | Escape from battle with high probability. | > Active |0 > > Cure | Restores HP of single/multiple.| > Active |6 > > Power Break | Reduces the enemy's attack power. | > Active |8 > > Thunder Slash| Causes Thunder damage to the enemy.| > Active | 24 > > Auto-Haste | Automatically casts Haste in battle. | > Passive|9 > > Counter | Counterattacks when physically attacked. | > Passive|8 > > MP+20% | Increases MP by 20%| > Passive|8 > > Thievery | Deals physical damage to the target| > Active |8 > > Fire | Causes Fire damage to single/multiple targets. | > Active |6 > > Flare| Causes Non-Elemental damage. | > Active | 40 > > Leviathan| Causes water damage to all enemies.| > Active | 42 > > > > beckerbalab2=> select * from ffix_can_wear; > > character_name |equipment_name > > +-- > > Dagger | Rod > > Dagger | Aquamarine > > Zidane | Aquamarine > > Vivi | Aquamarine > > Steiner| Diamond Sword > > Steiner| Ragnarok > > Dagger | Running Shoes > > Zidane | Running Shoes > > Vivi | Running Shoes > > Steiner| Running Shoes > > Dagger | Ritual Hat > > Zidane | Ritual Hat > > Vivi | Ritual Hat > > Dagger | Angel Earrings > > Zidane | Dagger > > Zidane | The Tower > > Dagger | Leather Hat > > Zidane | Leather Hat > > Vivi | Leather Hat > > Vivi | Black Robe > > Steiner| Aquamarine > > (21 rows) > > > > beckerbalab2=> SELECT ffix_ability.name, ffix_ability.cost > > beckerbalab2-> FROM ffix_can_learn NATURAL JOIN ffix_ability > > beckerbalab2-> WHERE ffix_can_learn.character_name = 'Zidane' > > beckerbalab2-> EXCEPT --this is the difference operator hope it works > > beckerbalab2-> SELECT ffix_ability.name, ffix_ability.cost > > beckerbalab2-> FROM ffix_can_learn NATURAL JOIN ffix_ability > > beckerbalab2-> WHERE ffix_can_learn.character_name = 'Steiner'; > > ERROR: No such attribute or function 'name' > > beckerbalab2=> > > > > ---(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] functions that return a dataset or set of rows
How do I create a function that returns a set of row; I can't seem to find the datatype that the return set should be declared as. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] foreign key, create table, and transactions
Hello. I was wondering if anybody's run across the problem of creating tables with foreign key constraints out of order. What I mean by this is that say I want a table called that has a foreign key reference to a table . If I define pictures before table, I keep getting an error (Relation "people" doesn't exist). I tried to group them into a transaction with BEGIN; SET CONSTRAINTS ALL DEFERRED; CREATE TABLE pictures... CREATE TABLE people... COMMIT; But still no. Is it a requirement of postgres that all creates essentially be "in order" when there are foreign key constraints? Please cc a response to jng15 at columbia dot edu. Thanks so much. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] triggers
Stian Riis wrote: > Hi. > > Does anyone know if it is posible to make a trigger that execute an > external program ? I want to execute a another program on the server > when I get a row in one of my tables... Yes, you can use plperl and call an external program from there, or us plsh and run it that way. plsh is mentioned on our web site under interfaces. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Locking that will delayed a SELECT
Ludwig Lim <[EMAIL PROTECTED]> writes: > *** For clarification *** >In the SQL command reference of PostgreSQL: >in SELECT statement section : > "The FOR UPDATE clause allows the SELECT > statement to perform exclusive locking of selected > rows" Hmm. That is a misstatement: FOR UPDATE only locks the selected row(s) against other updates (ie UPDATE, DELETE, SELECT FOR UPDATE), so it's not "exclusive" in the usual sense of the word: readers can still see the row. I'll fix that for 7.3, but meanwhile you might care to read the 7.3 development docs' discussion of concurrency, which is (IMHO anyway) more accurate than what was there before: http://developer.postgresql.org/docs/postgres/mvcc.html Note in particular that table-level locks and row-level locks are two independent features. Updates acquire an appropriate table-level lock and then acquire row locks on the rows they are updating. regards, tom lane ---(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] functions that return a dataset or set of rows
Brian wrote: > Is it not possible in 7.2? > No, not really. > Gaetano Mendola wrote: > > "Brian Ward" <[EMAIL PROTECTED]> wrote in message > > news:aofqbd$10v5$1@;news.hub.org... > > > >>How do I create a function that returns a set of row; > >> > >>I can't seem to find the datatype that the return set should be declared > > > > as. > > > > > > You should wait for Postgres 7.3 currently in beta. > > > > Ciao > > Gaetano > > > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] isAutoIncrement and Postgres
Jim, > Do any existing drivers / database version combinations support the > isAutoIncrement method? What programming language are you referring to? VB? Delphi? -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] foreign key, create table, and transactions
On Fri, 11 Oct 2002, Jeffrey Green wrote: > > Hello. I was wondering if anybody's run across the problem of > > creating tables with foreign key constraints out of order. What I > > mean by this is that say I want a table called that has a > > foreign key reference to a table . If I define pictures > > before table, I keep getting an error (Relation "people" doesn't > > exist). I tried to group them into a transaction with > > > > BEGIN; > > SET CONSTRAINTS ALL DEFERRED; > > > > CREATE TABLE pictures... > > > > CREATE TABLE people... > > > > COMMIT; > > > > But still no. Is it a requirement of postgres that all creates > > essentially be "in order" when there are foreign key constraints? Yes. The check for the fact that the constraint is valid (as opposed to the check that the data is valid) is immediate. You can use alter table add constraint to add the foreign key constraint after the second table is created however. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] isAutoIncrement and Postgres
Are you looking for SERIAL data type? Josh Berkus wrote: > > Jim, > > > Do any existing drivers / database version combinations support the > > isAutoIncrement method? > > What programming language are you referring to? VB? Delphi? > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > ---(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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] date
Try
select to_char( '1969-10-22'::date, '-MM-DD');
wishy wishy wrote:
>
> hi folks,
> we have a PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC 2.96
> installation on pogo linux 7.2
> we are facing a data problem when we do the following
> select to_char(to_date('1969-10-22','-MM-DD'),'-MM-DD');
>
> ERROR: Unable to convert date to tm
>
> we have been trying to find a solution for this have you found such
> instances before it there a method to over come this.
> Any help will be greatly appreciated.
> thanks
> kris
>
> _
> Get faster connections -- switch to MSN Internet Access!
> http://resourcecenter.msn.com/access/plans/default.asp
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] join question
For the SQL gurus, a query where I'm not getting the expected results. Trying to write it using sql compatible with both postgres and mysql. There are two tables: table = profile int id char name table = attribute int id int containerId char name char value Multiple attribute rows correspond to a single profile row where attribute.containerId = profile.id These two queries result in one row being returned, which is the expected result: select name from profile where ((profile.state='1020811'));select profile.name from profile,attribute where (((attribute.name='description') and (attribute.value='1020704') and (profile.id=attribute.containerId))); But, I thought this next query would just be a simple way to combine the two queries with an "or" operator, still returning one row, actually returns ALL rows of attribute: select profile.name from profile,attribute where ((profile.state='1020811') or ((attribute.name='marketsegment') and (attribute.value='1020704') and (profile.id=attribute.containerId))); Why doesn't this last query return just one row? TIA
Re: [SQL] date
"wishy wishy" <[EMAIL PROTECTED]> writes:
> we have a PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC 2.96
> installation on pogo linux 7.2
> we are facing a data problem when we do the following
> select to_char(to_date('1969-10-22','-MM-DD'),'-MM-DD');
> ERROR: Unable to convert date to tm
Recent versions of glibc broke mktime() for dates before 1970.
There is a workaround for this silliness in Postgres 7.2.3.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] join question
On Fri, 18 Oct 2002, Frank Morton wrote: > For the SQL gurus, a query where I'm not getting the expected > results. Trying to write it using sql compatible with both postgres > and mysql. > > There are two tables: > > table = profile > int id > char name > > table = attribute > int id > int containerId > char name > char value > > Multiple attribute rows correspond to a single profile row where > attribute.containerId = profile.id > > These two queries result in one row being returned, which is > the expected result: > > select name from profile where ((profile.state='1020811')); > > select profile.name from profile,attribute where > (((attribute.name='description') and (attribute.value='1020704') and > (profile.id=attribute.containerId))); > > But, I thought this next query would just be a simple way to combine the two > queries with an "or" operator, still returning one row, actually returns > ALL rows of attribute: > > select profile.name from profile,attribute where > ((profile.state='1020811') or ((attribute.name='marketsegment') and > (attribute.value='1020704') and (profile.id=attribute.containerId))); > > Why doesn't this last query return just one row? Because for each combination of rows from profile and attribute where profile.state='1020811' the where clause is statisfied. I'm not sure what you're precisely trying to get out, since unless the row that matches each of the clauses is the same I don't see how you'd only get one row out with an or. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] join question
I think you meant: select profile.name from profile,attribute where ( profile.id = attribute.containerId) and ( profile.state =' 1020811' or ( attribute.name = 'marketsegment' and attribute.value = '1020704'); > select profile.name from profile,attribute where > ((profile.state='1020811') or ((attribute.name='marketsegment') and > (attribute.value='1020704') and (profile.id=attribute.containerId))); > > Why doesn't this last query return just one row? > > TIA > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] adding column with not null constraint
I'm looking to add a column to my database with not null and a default value: vk=> alter table msg_owner add column user_optional_fields varchar(255) NOT NULL default ''; ERROR: Adding columns with defaults is not implemented. Add the column, then use ALTER TABLE SET DEFAULT. vk=> alter table msg_owner add column user_optional_fields varchar(255) NOT NULL ; ERROR: Adding NOT NULL columns is not implemented. Add the column, then use ALTER TABLE ADD CONSTRAINT. Ok, so we can succeed with this: ALTER TABLE msg_owner ADD COLUMN user_optional_fields VARCHAR(255); ALTER TABLE msg_owner ALTER user_optional_fields SET DEFAULT ''; UPDATE msg_owner SET user_optional_fields = ''; Now my problem is I cannot find any syntax for ALTER TABLE ADD CONSTRAINT to put a NOT NULL constraint on a column. Can someone help me here? I'm using Postgres 7.2.1 on FreeBSD 4.6. Thanks. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] functions that return a dataset or set of rows
On Thu, Oct 17, 2002 at 05:41:22PM -0400, Brian wrote: > Is it not possible in 7.2? In 7.2 you can return a cursor, which gets close and lets you basically accomplish the goal. See the PL/pgSQL developer documentation for 7.3 (returning cursors was omitted accidentally (?) in the 7.2 documentation). http://developer.postgresql.org/ -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Bill Gates made $6.3 Billion selling us MS-DOS? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
