Re: [GENERAL] Postgresql and scripting
On Wed, 2004-09-08 at 00:33, Jerome Lyles wrote: ... Can someone point me to a sed or shell script that I can use on a text file to remove the whitespace between the letters in the words only? Here is the top of the script file as it stands now: - - C r e a t e C u s t o m e r s t a b l e $ echo '- - C r e a t e C u s t o m e r s t a b l e' | sed -e 's/\([^ ]\) \([^ ]\)/\1\2/g' -e 's/\([^ ]\) \([^ ]\)/\1\2/g' -e 's/ */ /g' -- Create Customers table -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Put on the whole armor of God, that ye may be able to stand against the wiles of the devil. Ephesians 6:11 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Postgresql and scripting
I took the white space between characters out of my script 'create.txt4' and ran it on database 'test'. There were errors: 26 27 -- Create Orders Table 28 29 CREATE TABLE Orders 30 ( 31Order_num int NOT NULL, 32Order_datedatetimeNOT NULL, 33cust_id char(10)NOT NULL 34 ); CREATE TABLE psql:create.txt4:34: ERROR: type datetime does not exist Does this mean this datatype doesn't exist on this line (34) or that it doesn't exist at all? If it doesn't exist at all what is the correct datatype to use here? Also: 66 67 -- Define Foreign Keys 68 ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY(order_num) REFERENCE Orders(order_num); 69 ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCE Products(prod_id); 70 ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCE Customers(cust_id); 71 ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCE Vendors(vend_id); generates these errors: psql:create.txt4:68: ERROR: syntax error at or near REFERENCE at character 83 psql:create.txt4:69: ERROR: syntax error at or near REFERENCE at character 84 psql:create.txt4:70: ERROR: syntax error at or near REFERENCE at character 77 psql:create.txt4:71: ERROR: syntax error at or near REFERENCE at character 79 What, where is the syntac error?? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Postgresql and scripting
On Tuesday 07 September 2004 10:22 pm, Dorward villaruz wrote: hi! try this in commandline i assume you save the file in sams.txt final file will be sams2.txt cat sams.txt | sed -e 's/ /@@/g' -e 's/ //g' -e 's/@@/ /g' sams2.txt or put this in a script say convert.sh script start: #!/bin/bash cat $1 | sed -e 's/ /@@/g' -e 's/ //g' -e 's/@@/ /g' $2 script end: how to use? convert.sh input file output file best regards, wardy Thank you for this. I've spent a few hours today searching and experimenting to produce this code. I can almost understand the sed command. Everything between the quotes is the action to be taken. The 's' is for substitution, the / / is the whitespace to be replaced, the/@@/ is what the whitespace is replaced with but I don't know what the @@ stands for, the g means it's applied globally. I think what I just said is wrong because I have no clue why the second and third substitution are there. And finally the output is directed to $2. I'll have to wait until tomorrow evening to play with it. I look forward to it. thanks, Jerome ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Function caches wrong OID of temporary table?
Dear PostgreSQL experts, I have encountered a problem with temporary tables inside plpgsql functions. I suspect that this is a known issue; if someone could confirm and suggest a workaround I'd be grateful. My function creates a couple of temporary tables, uses them, and drops them before returning: create temporary table s as select 123 as id; create temporary table t ( id integer ); insert into t (select id from s); drop table s; drop table t; return; When I run this the first time it works as expected. When I run it a second time I get this message: ERROR: relation with OID 590209 does not exist CONTEXT: PL/pgSQL function f line 18 at SQL statement (Line 18 is the insert-select statement.) I imagine that it has cached that one of the tables is object 590209, but has not noticed that the table has been dropped and recreated before the second invokation of the function. I can supply a better test case if that would help. Regards, --Phil. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Function caches wrong OID of temporary table?
On Wed, 2004-09-08 at 12:37, Phil Endecott wrote: Dear PostgreSQL experts, I have encountered a problem with temporary tables inside plpgsql functions. I suspect that this is a known issue; if someone could confirm and suggest a workaround I'd be grateful. My function creates a couple of temporary tables, uses them, and drops them before returning: ... ERROR: relation with OID 590209 does not exist CONTEXT: PL/pgSQL function f line 18 at SQL statement (Line 18 is the insert-select statement.) I imagine that it has cached that one of the tables is object 590209, but has not noticed that the table has been dropped and recreated before the second invokation of the function. That is correct. You need to EXECUTE the command instead, so that it is planned afresh each time it is used. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Put on the whole armor of God, that ye may be able to stand against the wiles of the devil. Ephesians 6:11 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] How to determine a database is intact?
On 9/5/04 9:04 AM, Jan Wieck [EMAIL PROTECTED] wrote: 24 hours to do what? The pg_dumpall, the restore or both? There's more than 250 million rows. If I remember right, it's ballpark 25% data reload, 75% index/foreign constraint rebuild. Pg_dumpall is something like 3 hours or so. Wes ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ERROR: canceling query due to user request
On Sep 8, 2004, at 11:25, Tore Halset wrote: I installed beta2 on a local linux box instead and now everything is working :/ Something must trigger Mac OS X to send those evil SIGINTs. Will it happens on the linux box as well, but not that often. Grr. - Tore. ---(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: [GENERAL] Function caches wrong OID of temporary table?
Phil Endecott wrote: Dear PostgreSQL experts, I have encountered a problem with temporary tables inside plpgsql functions. I suspect that this is a known issue; if someone could confirm and suggest a workaround I'd be grateful. My function creates a couple of temporary tables, uses them, and drops them before returning: create temporary table s as select 123 as id; create temporary table t ( id integer ); insert into t (select id from s); drop table s; drop table t; return; When I run this the first time it works as expected. When I run it a second time I get this message: ERROR: relation with OID 590209 does not exist CONTEXT: PL/pgSQL function f line 18 at SQL statement (Line 18 is the insert-select statement.) I imagine that it has cached that one of the tables is object 590209, but has not noticed that the table has been dropped and recreated before the second invokation of the function. Your analysis is correct - Using execute is the work workaround. While I'm all for function caching, this *feature* in the scenario above is verging on a bug as far as I am concerned. I think that this item should move from a nice to have / to do item to the bug list. Other database systems that I have used are able to detect when a procedure/function need recompiling and do it on the fly. If postgres implemented this it would have another benefit. You could create objects that reference other objects that don't yet exist (or are not yet created) for example when applying a schema build script. The first time the object is used/called it is compiled and all the dependencies are resolved then (this is what Oracle is able to do). Not forcing SQL developers to use DB-specific workarounds would be a benefit too! :) John Sidney-Woollett ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Heritage
Hi everyone. Im having a problem with heritage. The situation is the following: I have two tables, tbl_everyone and tbl_employees. tbl_employees inherits from tbl_everyone. In tbl_everyone, i store some information about everyone who is related with the place where i work: Name, ID (PK), Birth Date, Adress... Then, in tbl_employees i have aditional information, like area, position, etc. The problem appears when i have someone in tbl_everyone that becomes an employee. What do i have to do ? Consider that ID in tbl_everyone is used in auxiliar tables, so i cannot easily delete the person in tbl_everyone and insert it again in tbl_employees... Thanks in advance Sebastian Davancens ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Postgresql and scripting
Jerome Lyles [EMAIL PROTECTED] writes: the/@@/ is what the whitespace is replaced with but I don't know what the @@ stands for, It doesn't stand for anything, it just means replace with @@. Now look again at the second and third substitution... -- greg ---(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: [GENERAL] ERROR: canceling query due to user request
Tore Halset [EMAIL PROTECTED] writes: On Sep 7, 2004, at 20:03, Tom Lane wrote: I suspect that it's got nothing at all to do with the traffic between the server and the client, and that the SIGINT is coming from some outside agency. Yes, you are correct. No, I'm not ;-) I added the debug flag and reproduced the problem. This was written to the log: 2004-09-08 08:55:43 CEST 413eacee.3402DEBUG: processing cancel request: sending SIGINT to process 13311 This is a smoking gun: your client *is* issuing cancel requests, whether you know it or not. (Either that or some other process has magically acquired the secret cancel key that was issued to your connection.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Postgresql and scripting
Jerome Lyles [EMAIL PROTECTED] writes: psql:create.txt4:34: ERROR: type datetime does not exist Does this mean this datatype doesn't exist on this line (34) or that it doesn't exist at all? If it doesn't exist at all what is the correct datatype to use here? It doesn't exist at all (any more). That's an obsolete equivalent for the more standard datatype TIMESTAMP WITH TIME ZONE. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] How to determine a database is intact?
Wes [EMAIL PROTECTED] writes: There's more than 250 million rows. If I remember right, it's ballpark 25% data reload, 75% index/foreign constraint rebuild. Pg_dumpall is something like 3 hours or so. FWIW, increasing sort_mem for the reload process would probably help with the index and FK rebuilds. (8.0 rejiggers things so that the memory constraints for these operations are driven off a separate variable, but in 7.* you need to muck about with sort_mem. The appropriate value for one-off operations is a lot higher than what you would want multiple competing backends to be using.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Need Help in interface..
Dear Group. I want to read vrml file and store the same in the postgresql.So please send me some links or solution to achieve this. Ashok Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage!
[GENERAL] import mysql database...
Hi, I have a running PHPBB mySQL dump that I would like to load/import in postgreSQL... I didn't try yet but I wonder if it's straightforward and just import the mySQL dump into postgreSQL ? (Of course, I want to migrate PHPBB running on a server with mySQL to another server running with postgreSQL). Yes PHPBB has postgreSQL support. Thanks. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] import mysql database...
On Wed, 2004-09-08 at 10:07, Eric wrote: Hi, I have a running PHPBB mySQL dump that I would like to load/import in postgreSQL... I didn't try yet but I wonder if it's straightforward and just import the mySQL dump into postgreSQL ? (Of course, I want to migrate PHPBB running on a server with mySQL to another server running with postgreSQL). Yes PHPBB has postgreSQL support. I found this utility very helpful in a similar situation: http://freshmeat.net/projects/my2pg/ -- Greg Donald ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] import mysql database...
Hi, You may find something here: http://www.rot13.org/~dpavlin/sql.html. In addition, I always thought that Postgres came with a MySQL to Postgres converter, but I might be wrong. Bye, Arthur On Wed, 08 Sep 2004 11:07:46 -0400, Eric [EMAIL PROTECTED] wrote: Hi, I have a running PHPBB mySQL dump that I would like to load/import in postgreSQL... I didn't try yet but I wonder if it's straightforward and just import the mySQL dump into postgreSQL ? (Of course, I want to migrate PHPBB running on a server with mySQL to another server running with postgreSQL). Yes PHPBB has postgreSQL support. Thanks. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(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: [GENERAL] import mysql database...
Eric wrote: Hi, I have a running PHPBB mySQL dump that I would like to load/import in postgreSQL... I didn't try yet but I wonder if it's straightforward and just import the mySQL dump into postgreSQL ? (Of course, I want to migrate PHPBB running on a server with mySQL to another server running with postgreSQL). Yes PHPBB has postgreSQL support. I do this semi-regularly. You usually end up doing some minor corrections, not much though. You might want to check http://techdocs.postgresql.org/ -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] SQL query - single text value from group by
Hi I am trying to work out if this is possible in a select query I have a group by query which could result in several rows, what I want to do is do a text equivalent of a sum() eg: SELECT sum(inv_id),date,cust from invoice group by date,cust Is there any way to get to a single concatenated inv_id field with just one row? thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL query - single text value from group by
mike wrote: Hi I am trying to work out if this is possible in a select query I have a group by query which could result in several rows, what I want to do is do a text equivalent of a sum() eg: SELECT sum(inv_id),date,cust from invoice group by date,cust Is there any way to get to a single concatenated inv_id field with just one row? Yes, you can define your own aggregate. For example, I did this the other day: /* Custom aggregate This aggregate is so we can aggregate text into paragraph blocks */ CREATE OR REPLACE FUNCTION join_paras(text, text) RETURNS text AS ' SELECT CASE WHEN ($1 = ) THEN $2 ELSE $1 || ''\n'' || $2 END; ' LANGUAGE 'SQL' IMMUTABLE; CREATE AGGREGATE agg_paras (sfunc1=join_paras, basetype=text, stype1=text, initcond1=''); Note I defined my own text-concatenation function because I wanted to insert newlines between each block of text. If you just wanted joined text you could use the built-in textcat() Full specs of create aggregate are in the manuals. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Problems importing data from plain text file
From: Mário Gamito [EMAIL PROTECTED] I have this plain text file with about 5000 lines. Each line may have 4 or 5 fields, all delimited with a tab. I've made a table named t_zip_codes with 5 fields. When i run (in postgres command line) the command COPY t_zip_code FROM zip_codes.txt; it aborts as soon as it reaches a line with only 4 fields in the text file, because the table t_zip_codes have 5 fields. How can i solve this annoyance ? by adding the 5th field where missing ? with something like: perl -pi.bak -e's/$/\t/ if tr/\t/\t/4' zip_codes.txt should be easy with sed, as well (or just with your favorite editor) gnari ---(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
[GENERAL] 'order by' in an insert into command
I have the following insert to populate a new table: insert into pending_tnmt_sec select tseceventid, tsecsecno, nextval('sec_seq'), tsecrtddt from tnmtsec order by tsecrtddt,tseceventid,tsecsecno; I need to access this data in a particular order which may change over time but the initial order I want is in the order by clause. The problem is, I'm not getting the data into the right order based on the sequence values being inserted: tsecrtddttseceventid tsecsecno seq 2004-08-30 | 20040731910 | 1 | 356270### out of sequence 2004-07-08 | 20040531897 | 2 | 360792 2004-06-03 | 20040425023 | 1 | 354394 2004-04-23 | 20040320702 | 1 | 353557 2004-02-18 | 20040117178 | 2 | 359387### out of sequence 2004-01-10 | 20031213418 | 1 | 351315 I can't tell whether this is because the order by clause in the insert is being ignored or because the sequence is incrememted before the sort takes place. Is there a way to do this insert? -- Mike Nolan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] 8.0.0beta2: gcc: unrecognized option `-pthreads'
Is this pthreads warning of any concern? gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -pthread -pthreads -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -fpic -shared -Wl,-soname,libecpg.so.4 execute.o typename.o descriptor.o data.o error.o prepare.o memory.o connect.o misc.o path.o exec.o -L../../../../src/port -L../pgtypeslib -lpgtypes -L../../../../src/interfaces/libpq -lpq -lcrypt -lm -lpthread -Wl,-rpath,/opt/pgsql/installs/postgresql-8.0.0beta2/lib -o libecpg.so.4.2 gcc: unrecognized option `-pthreads' Ed ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] SQL query - single text value from group by
On Wed, 2004-09-08 at 17:13 +0100, Richard Huxton wrote: mike wrote: Hi I am trying to work out if this is possible in a select query I have a group by query which could result in several rows, what I want to do is do a text equivalent of a sum() eg: SELECT sum(inv_id),date,cust from invoice group by date,cust Is there any way to get to a single concatenated inv_id field with just one row? Yes, you can define your own aggregate. For example, I did this the other day: /* Custom aggregate This aggregate is so we can aggregate text into paragraph blocks */ CREATE OR REPLACE FUNCTION join_paras(text, text) RETURNS text AS ' SELECT CASE WHEN ($1 = ) THEN $2 ELSE $1 || ''\n'' || $2 END; ' LANGUAGE 'SQL' IMMUTABLE; CREATE AGGREGATE agg_paras (sfunc1=join_paras, basetype=text, stype1=text, initcond1=''); Note I defined my own text-concatenation function because I wanted to insert newlines between each block of text. If you just wanted joined text you could use the built-in textcat() Full specs of create aggregate are in the manuals. HTH thanks - as sods law dictates I found a solution just after I posted along the same lines, after a couple of hours of fruitless googling. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Grant Issues with groups
Hello all, I have setup 2 groups on a database: admin users I have granted all on testing to admin and tried to grant select on testing to users; If I do a \d as an admin I see the schema if i do it as user I see nothing.. If i select * from testing i see everything in the table as a admin but if i do the same thing as a member of users i get an error that nothing exists... what am i missing? If is set the users group to full access it is fine... how do I limit the access? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] 'order by' in an insert into command
Try: insert into pending_tnmt_sec select tseceventid, tsecsecno, nextval('sec_seq'), tsecrtddt from ( select tseceventid, tsecsecno, tsecrtddt from tnmtsec order by tsecrtddt,tseceventid,tsecsecno) as ss; Mike Nolan wrote: I have the following insert to populate a new table: insert into pending_tnmt_sec select tseceventid, tsecsecno, nextval('sec_seq'), tsecrtddt from tnmtsec order by tsecrtddt,tseceventid,tsecsecno; I need to access this data in a particular order which may change over time but the initial order I want is in the order by clause. The problem is, I'm not getting the data into the right order based on the sequence values being inserted: tsecrtddttseceventid tsecsecno seq 2004-08-30 | 20040731910 | 1 | 356270### out of sequence 2004-07-08 | 20040531897 | 2 | 360792 2004-06-03 | 20040425023 | 1 | 354394 2004-04-23 | 20040320702 | 1 | 353557 2004-02-18 | 20040117178 | 2 | 359387### out of sequence 2004-01-10 | 20031213418 | 1 | 351315 I can't tell whether this is because the order by clause in the insert is being ignored or because the sequence is incrememted before the sort takes place. Is there a way to do this insert? -- Mike Nolan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] 'order by' in an insert into command
Mike Nolan wrote: I have the following insert to populate a new table: insert into pending_tnmt_sec select tseceventid, tsecsecno, nextval('sec_seq'), tsecrtddt from tnmtsec order by tsecrtddt,tseceventid,tsecsecno; I need to access this data in a particular order which may change over time but the initial order I want is in the order by clause. The problem is, I'm not getting the data into the right order based on the sequence values being inserted: In your example, I would expect the nextval() to be called during the fetch, before the ordering. You could probably do something like: INSERT INTO pending_tnmt_sec SELECT foo.*, nextval('sec_seq') FROM ( SELECT tseceventid, ... ORDER BY tsecrtddt,tseceventid,tsecsecno ) AS foo ; I'm not sure whether the SQL standard requires the ORDER BY to be processed in the sub-select. From a relational viewpoint, I suppose you could argue that ordering is strictly an output feature. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Salt in encrypted password in pg_shadow
On Wed, Sep 08, 2004 at 00:33:39 -0400, Tom Lane [EMAIL PROTECTED] wrote: I've been hearing rumblings that MD5 and all other known crypto protocols are known vulnerable since the latest crypto symposiums. (Not that we didn't all suspect the NSA et al could break 'em, but now they've told us exactly how they do it.) Things aren't currently that bad. So far people have found a way to find two strings that give the same hash using MD5. They haven't yet found a way to find a string which hashes to a given hash. SHA-0 was also shown to have some weakness. From comments I have read, I don't think SHA-1 was shown to have any weaknesses. One comment specifically mentioned that the change made between SHA-0 and SHA-1 seems to have been made to address the weakness found in SHA-0. I haven't read the source papers, so take this all with a grain of salt. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] import mysql database...
On Wed, 2004-09-08 at 09:07, Eric wrote: Hi, I have a running PHPBB mySQL dump that I would like to load/import in postgreSQL... I didn't try yet but I wonder if it's straightforward and just import the mySQL dump into postgreSQL ? (Of course, I want to migrate PHPBB running on a server with mySQL to another server running with postgreSQL). Yes PHPBB has postgreSQL support. Is this the dump from mysqldump or the one from PHPBB? I think the dump from phpbb directly could be imported to another instance of phpbb running on top of postgresql. I haven't ever tried it, I'm just guessing. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] test
I don't seem to see my messages in the digest. Is this a mail setting? I thought that only blockable in the single message mode. ---(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: [GENERAL] Heritage
On Wed, 2004-09-08 at 14:27, Sebastian Davancens wrote: Hi everyone. Im having a problem with heritage. ... whatever that might be... The situation is the following: I have two tables, tbl_everyone and tbl_employees. tbl_employees inherits from tbl_everyone. In tbl_everyone, i store some information about everyone who is related with the place where i work: Name, ID (PK), Birth Date, Adress... Then, in tbl_employees i have aditional information, like area, position, etc. The problem appears when i have someone in tbl_everyone that becomes an employee. What do i have to do ? Consider that ID in tbl_everyone is used in auxiliar tables, so i cannot easily delete the person in tbl_everyone and insert it again in tbl_employees... Rather than inheriting, tbl_employees could be a separate table that holds only the additional information and it should have a foreign key reference to tbl_everyone. When someone becomes an employee, create a row in tbl_employees with the extra information. Create a View that combines tbl_employees with tbl_everyone to return the information that your inheriting table would have given you. Alternatively, create a separate index of keys to the hierarchy and use it as the target for foreign key references. Use triggers to keep it up to date. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Put on the whole armor of God, that ye may be able to stand against the wiles of the devil. Ephesians 6:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 'order by' in an insert into command
Richard Huxton [EMAIL PROTECTED] writes: In your example, I would expect the nextval() to be called during the fetch, before the ordering. You could probably do something like: INSERT INTO pending_tnmt_sec SELECT foo.*, nextval('sec_seq') FROM ( SELECT tseceventid, ... ORDER BY tsecrtddt,tseceventid,tsecsecno ) AS foo ; I'm not sure whether the SQL standard requires the ORDER BY to be processed in the sub-select. From a relational viewpoint, I suppose you could argue that ordering is strictly an output feature. I believe the SQL standard disallows this entirely, precisely because it considers ordering to be strictly an output feature. Postgres will take it though (in recent releases), and should produce the results Mike wants. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html