[SQL] unsuscribe
unsuscribe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL]
For example, consider the queres: SELECT * FROM table1 WHERE field1=1 AND field2=1; SELECT * FROM table1 WHERE field2=1 AND field1=1; These two queries are logically equivalent. But in all cases the planner generates a query plan that performs field1=1 condition, and then field2=1 condition, as there is a index on field1. Is it possible to instruct the PostgreSQL query planner to perform field2=1 condition first, and then field1=1 condition? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Blank-padding
On Sat, 2005-10-22 at 00:39, Chris Travers wrote: > Tom Lane wrote: > > >"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes: > > > > > >>I remember that discussion, and I was for the change. However, upon > >>doing some testing after reading the above, I wonder if the > >>blank-stripping isn't too aggressive. I have a CHAR(6) field (say, > >>named Z) that has "abc " in it. Suppose I want to append "x" to Z, > >>with any leading spaces in Z PRESERVED. > >> > >> > > > >(You meant trailing spaces, I assume.) Why exactly would you want to do > >that? You decided by your choice of datatype that the trailing spaces > >weren't significant. > > > I once built a telecom billing app where this might be important (fixed > length fields). Lets say you have fixed length fields defined as > char(n) datatypes. You may want to build a query to generate billing > records like: > select field1 || field2 || field3 || field4 || field5 ... AS bill_record > FROM lec_billing_entries; > > It seels to me that I would expect trailing spaces to be preserved in > these cases. Having an implicit rtrim function is asking for problems. > Personally I would rather have to call rtrim explicitly than have the > backend treat the concatenation differently than if I do it on the client. If I rememberate correctificantly, this problem is or was caused by the || operator using the same internal logic for varchar / text AND char. Tom, is it reasonable / doable to have || have a different set of internal functions for each of those types. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Blank-padding
I also have an issue with the blank padding at the end of my fields. Is there any way to eliminate the blank padding at the end of character fields in a table. Such as you have field X as a char(6), but storing "abc" in it. Well postgres seems to add the padding on the end of string that is being stored. So it is stored as "abc " instead of "abc". I don't want that padding there. I am fairly new to Postgres and have only dealt with Informix database systems, which don't store data this way. Any help is very much appreciated. Thanks, Shaun Shaun Watts Programmer/Analyst CSI - Computer Systems, Inc. Phone: 317.913.4160 12975 Parkside Drive Fax: 317.913.4175 Fishers, IN 46038 Toll Free: 800.860.1274 "To give anything less than your best is to sacrifice the gift." -- Steve Prefontaine -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe Sent: Monday, October 24, 2005 9:46 AM To: Chris Travers Cc: Tom Lane; Dean Gibson (DB Administrator); pgsql-sql Subject: Re: [SQL] Blank-padding On Sat, 2005-10-22 at 00:39, Chris Travers wrote: > Tom Lane wrote: > > >"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes: > > > > > >>I remember that discussion, and I was for the change. However, upon > >>doing some testing after reading the above, I wonder if the > >>blank-stripping isn't too aggressive. I have a CHAR(6) field (say, > >>named Z) that has "abc " in it. Suppose I want to append "x" to Z, > >>with any leading spaces in Z PRESERVED. > >> > >> > > > >(You meant trailing spaces, I assume.) Why exactly would you want to > >do that? You decided by your choice of datatype that the trailing > >spaces weren't significant. > > > I once built a telecom billing app where this might be important > (fixed length fields). Lets say you have fixed length fields defined > as > char(n) datatypes. You may want to build a query to generate billing > records like: > select field1 || field2 || field3 || field4 || field5 ... AS > bill_record FROM lec_billing_entries; > > It seels to me that I would expect trailing spaces to be preserved in > these cases. Having an implicit rtrim function is asking for problems. > Personally I would rather have to call rtrim explicitly than have the > backend treat the concatenation differently than if I do it on the client. If I rememberate correctificantly, this problem is or was caused by the || operator using the same internal logic for varchar / text AND char. Tom, is it reasonable / doable to have || have a different set of internal functions for each of those types. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL]
> For example, consider the queres: > > SELECT * FROM table1 WHERE field1=1 AND field2=1; > > SELECT * FROM table1 WHERE field2=1 AND field1=1; > > > These two queries are logically equivalent. But in all cases the planner > generates a query plan that performs field1=1 condition, and then field2=1 > condition, as there is a index on field1. > > Is it possible to instruct the PostgreSQL query planner to perform field2=1 > condition first, and then field1=1 condition? > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > EXPLAIN [ ANALYZE ] [ VERBOSE ] statement This command displays the execution plan that the PostgreSQL planner generates for the supplied statement. This command displays plan ONLY! But I want to instruct the PostgreSQL query planner to perform field2=1 condition first, and then field1=1 condition. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Blank-padding
"Shaun Watts" <[EMAIL PROTECTED]> writes: > Is there any way to eliminate the blank padding at the end of character > fields in a table. Use varchar, or text. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL]
Илья Конюхов wrote: >>For example, consider the queres: >> >>SELECT * FROM table1 WHERE field1=1 AND field2=1; >> >>SELECT * FROM table1 WHERE field2=1 AND field1=1; >> >> >>These two queries are logically equivalent. But in all cases the planner >>generates a query plan that performs field1=1 condition, and then field2=1 >>condition, as there is a index on field1. >> >>Is it possible to instruct the PostgreSQL query planner to perform field2=1 >>condition first, and then field1=1 condition? >> You might get the desired result, if you switch off the index scan: set ENABLE_INDEXSCAN = OFF. But there is no way to tell Postgres what to use first. Usually the query planer is quite good, so there is no reason to fiddle around. And why on earth would you like that. In the end you get the same result. Hope, that helps Silke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problema con migracion de SQL a PostgreSQL
PostgreSQL si tiene procedimientos almacenados, claro se crean con FUNCTION pero pueden hacer lo mismo que un Stored Procedure en SQL server (con algunas excepciones como retornar varios resultsets diferentes de una vez). SQL Server usa Transac-SQL y PostgreSQL aunque puede usar varios lenguajes para escribir un SP aun no tiene soporte para SPs hechos en Transac-SQL, eso significa que lo que debes hacer es pasar los 40 sps a un lenguaje de procedimientos en PgSQL (te recomiendo PL/pgSQL, es muy similar a PL/SQL y creeme q valdra la pena hacerlo) Quizas debes comenzar importando solamente las tablas (el esquema) a una BD PgSQL, cambiaran algunos tipos y definiciones, pero seria lo mas facil Luego deberias importar la data que tienes, preferiblemente no en sentencias inserts, sino en simples archivos de texto tipo CSV, no te preocupes, PgSQL puede manejarlos trankilos usando COPY La parte divertida es pasar los SPs y vistas que tengas en SQL Server 2000, no hay nada “automatico” que creo que te lo haga, aunq puedes evaluar algunas aplicaciones de EMS (una empresa rusa q se dedica a eso, buscalo en google y triunfaras). From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fernando Garcia Sent: Jueves, 20 de Octubre de 2005 09:25 a.m. To: pgsql-sql@postgresql.org Subject: [SQL] Problema con migracion de SQL a PostgreSQL Subject: Migracion de SQL Server 2000 a PostgreSQL hola a todos, tengo un problema hace unos dias y necesito resolverlo cuanto antes. Necesito migrar la Base de Datos de un Portal Corporativo que desarrolle en SQL a Postgresql, pero no encuentro manera de hacerlo, trate de hacerlo por el export de SQL SErver 2000 pero no me exporta los SP, y para colmo tengo mas de 40 Procedimientos Almacenados (SP) que no quisiera reimplementar ademas que no tendria sentido. Yo se que Postgresql no tiene procedimientos almacenados de forma literal pero si tiene funciones o algo para encapsular consultas y devolver atributos. Necesito que si alguien ha resuelto esto me diga que hizo pues me urge saber. Gracias a todos. Me pueden responder por aqui [EMAIL PROTECTED]
Re: [SQL] query tables based on a query
On Fri, Sep 30, 2005 at 18:47:48 -0400, solarsail <[EMAIL PROTECTED]> wrote: > I have a large number of tables with a common naming convention > > basically: > > table001, table002, table003 ... table00n > > > I would like to do a query across all of the tables, however I do not know > all of the tables before hand, and I do not want to manually generate a > query like > > select * from table001, table002, table003 > > > I have a query that returns the names of the tables I want to query, it is > basically like this: > > select tablename from pg_tables where tablename like 'table%' > > > How do I do this? I've tried creating a Table Function that returns the > above set and tried to use that in a select clause, but I cant get it to > work either. The simplest way to do this is to have the application use the results of the above query to write a query using UNION ALL that selects from each of the tables. Also, unless you have a good reason to partition your data, you may want to consider a design where all of this data is in one table. > > > Thanks for the help ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] how to create rule as on delete
This is my rule for doing the delete event. The rule can be created. But the problem is, after the record has been deleted from the 'mytable', this record cannot be inserted into table 'maytable_log'. CREATE RULE on_delete AS ON DELETE TO mytable DO INSERT INTO mytable_log values (old.id,old.name); If I add the DO INSTEAD,the record can be inserted into 'maytable_log' and also still remain in the table 'maytable'. Which is exactly not exist at all. It just show it as im using the DO INSTEAD. CREATE RULE on_delete AS ON DELETE TO mytable DO INSTEAD INSERT INTO mytable_log values (old.id,old.name); My problem is, how to insert the deleted record into table 'mytable_log' without showing it in table 'maytable'. I really need the solution..please __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] SQL language
Dear all, I am a new user to PostGRE SQL. When i installed the application on the windows xp machine, There were no language for the template database created. Is there a way to add pl/sql to the databse that i create. Can some one guide me on how to do this. Thanks Krishnaa
[SQL] writable joined view
Hi! I have one table referencing an object in another table through an ID, and a view joining those tables on the ID. I want to create rules to rewrite updates/deletes/inserts on the joined view to act on the real tables. Can you give me some pointers? The documentation has only examples for views depending on single tables. Thank you, Sarah ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] automatic update or insert
Hi. I have a little problem. In a system of mine i need to insert records into table [tbStat], and if the records exist i need to update them instead and increase a column [cQuantity] for every update. I.e. the first insert sets cQuantity to 1, and for every other run cQuantity is increased. Currently i have implemented this as a stored procedure in the plpgsql language. This means that in my stored procedure i first do a select to find out if the row exists or not, then i do a insert or update depending if the row existed. Unfortunately, stored procedures seems awfully slow. And i need the application to go faster. One solution could be to implement the stored procedure in my program instead. I think that this will be atleast 50% faster than my stored procedure, so that would be ok. However, this has made me thinking. Couldn't this be done directly in SQL? Brgds Robert ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Article on Oracle & MySQL in E-week
You are quite incorrect in stating that MySQL can now claim "parity" with PostgreSQL through it's release of version 5.0. Knowledgeable database experts and administrators who are famiiar with both technologies are aware of "considerable " differences that, at this time still accrue to postgreSQL, e.g. for one example - Object Relational functions. Please limit your statements to areas where you have "some" knowledge or understanding of the subject matter. W. Anderson Ontario, Canada ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Merging lines with NULLs (with example data)
Good afternoon, I have a simple problem, and I feel stupid not finding myself what's the solution... I try to explain shortly, but as I'm not really confident with my explanation, I provided a complete example with data below. How can I "merge" this gday,count_udp,count_tcp '2005-10-20','','2' '2005-10-20','3','' '2005-10-21','','1' '2005-10-21','5','' into that: gday,count_udp,count_tcp '2005-10-20','3','2' '2005-10-21','5','1' in a single query??? Thanks in advance, MaXX Here's all the details: I have a table CREATE TABLE test ( id serial NOT NULL, tstamp timestamptz, host varchar(80), rulenr int4, act varchar(10), proto varchar(4), src_ip inet, src_port int4, dst_ip inet, dst_port int4, dir varchar(3), if varchar(5), reported bool, protected bool, CONSTRAINT pk_ipfw_id PRIMARY KEY (id) ) WITH OIDS; Data: INSERT INTO test VALUES (453639,'2005-10-21 09:39:19+02','akar',600,'Deny','UDP','10.182.144.167',52616,'10.0.0.1',2290,'in','rl0',TRUE,NULL); INSERT INTO test VALUES (453634,'2005-10-21 09:36:21+02','akar',600,'Deny','UDP','10.182.144.167',52616,'10.0.0.1',2290,'in','rl0',TRUE,NULL); INSERT INTO test VALUES (453633,'2005-10-21 09:36:20+02','akar',600,'Deny','UDP','10.182.144.167',52616,'10.0.0.1',2290,'in','rl0',TRUE,NULL); INSERT INTO test VALUES (452610,'2005-10-21 03:33:28+02','akar',600,'Deny','TCP','10.182.174.7',4310,'10.0.0.1',2290,'in','rl0',TRUE,NULL); INSERT INTO test VALUES (451735,'2005-10-21 00:11:52+02','akar',600,'Deny','UDP','10.216.48.231',6778,'10.0.0.1',2290,'in','rl0',TRUE,NULL); INSERT INTO test VALUES (451734,'2005-10-21 00:11:50+02','akar',600,'Deny','UDP','10.216.48.231',6778,'10.0.0.1',2290,'in','rl0',TRUE,NULL); INSERT INTO test VALUES (448277,'2005-10-20 16:31:17+02','akar',600,'Deny','UDP','10.244.165.152',31262,'10.0.0.1',2290,'in','rl0',TRUE,NULL); INSERT INTO test VALUES (448276,'2005-10-20 16:31:15+02','akar',600,'Deny','UDP','10.244.165.152',31262,'10.0.0.1',2290,'in','rl0',TRUE,NULL); INSERT INTO test VALUES (448266,'2005-10-20 16:29:08+02','akar',600,'Deny','TCP','10.224.32.85',63891,'10.0.0.1',2290,'in','rl0',TRUE,NULL); INSERT INTO test VALUES (448265,'2005-10-20 16:29:05+02','akar',600,'Deny','TCP','10.224.32.85',63891,'10.0.0.1',2290,'in','rl0',TRUE,NULL); INSERT INTO test VALUES (448258,'2005-10-20 16:28:16+02','akar',600,'Deny','UDP','10.244.165.152',31262,'10.0.0.1',2290,'in','rl0',TRUE,NULL); when I execute a query like this, select to_date(tstamp,'-MM-DD')as gday, case when proto='UDP' then count(id) else NULL end as count_udp, case when proto='TCP' then count(id) else NULL end as count_tcp from test where tstamp >= (now() - interval '$days days' ) and dst_port = $port group by gday, proto order by gday; I get: gday,count_udp,count_tcp '2005-10-20','','2' '2005-10-20','3','' '2005-10-21','','1' '2005-10-21','5','' This is not what I want, I want that: gday,count_udp,count_tcp '2005-10-20','3','2' '2005-10-21','5','1' -- MaXX ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] SQL Functions
I have been trying to find a way to return more than one but different types of variables. How do I return more than one but mix types of variables. Any help is appriaciated. Thanks; CREATE FUNCTION allMoney_con(integer,integer,date,date) RETURNS AS ' SELECT DISTINCT(inv_accts.id), inv_accts.description, inv_accts.amtReceived, consultant.lastname, consultant.firstname, consultant.unumber, officeDef.name, companyDef.abbreviation, inv_accts.dateReceived, employee.firstname, employee.lastname, inv_accts.type, contractDef.phase_id FROM dbuser as consultant, employee, inv_accts, officeDef, employee_offices, companyDef, acctDef, inv_contracts, contractDef, invention WHERE consultant.id = employee.user_id AND consultant.id = employee_offices.user_id AND officeDef.id = employee_offices.office_id AND invention.company_id = companyDef.id AND inv_accts.inv_id = inv_contracts.inv_id AND invention.id = inv_contracts.inv_id AND inv_contracts.con_id = consultant.id AND consultant.id = $1 AND invention.id = $2 --AND inv_accts.dateReceived BETWEEN $3 AND $4 AND inv_accts.acct_id = acctDef.id AND acctDef.contract_id = inv_contracts.contract_id AND inv_accts.type NOT LIKE ''DISCOVER%'' AND ( (acctDef.description LIKE ''%PAYMENT%'' --AND acctDef.description NOT LIKE ''3\\%%'' AND strpos(acctDef.description, ''3%'') = 0 AND acctDef.description NOT LIKE ''%DROP SELL%'' ) OR inv_accts.type LIKE ''%BOUNCED CHECK%'' OR inv_accts.description LIKE ''%BC%'' OR inv_accts.type = ''REFUND'' ) AND contractDef.id = inv_contracts.contract_id AND contractDef.id = acctDef.contract_id ORDER BY officeDef.name ASC, consultant.lastname ASC, inv_accts.dateReceived ASC; ' LANGUAGE SQL; - This mail sent through IMP: www.resolution.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Sql - Error : Relation tmp_datos already exists
PgAdmin probably encloses all querys in transactions. Do you? If not, try execute "BEGIN" and "COMMIT" just before and after your query.. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Aggregate versus lineitem report
How do you get a report that lists items and then produces a sum at the end. Like this: name date amount1 amount2 name date amount1 amount2 name date amount1 amount2 totals sum(amount1) sum(amount2) The sql command I am using now is SELECT SUM(Tcode.T_amount) Fees, SUM(Tcharges.T_discount) Discount, Tcode.T_prov Provider FROM "C:\zz\Tcode.DAT" Tcode INNER JOIN "C:\zz\Tcharges.DAT" Tcharges ON Tcode.T_acctnum = Tcharges.T_acctnum AND Tcode.T_link = Tcharges.T_link WHERE (((Tcode.T_posted BETWEEN "01/01/2004" AND "01/31/2004") AND (Tcode.T_tcode < 3))) GROUP BY Provider And that shows a list of amounts, for each provider, but I want to be able to show all the line items that added up to those totals. (I didnt include the group by provider issue in my question because that is nice but not necessary- I can always just do one provider at a time with a clause like "WHERE provider = 1") TIA Jerry ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] SQL Functions
I have been trying to find a way to return more than one but different types of variables. How do I return more than one but mix types of variables. Any help is appriaciated. Thanks; CREATE FUNCTION allMoney_con(integer,integer,date,date) RETURNS AS ' SELECT DISTINCT(inv_accts.id), inv_accts.description, inv_accts.amtReceived, consultant.lastname, consultant.firstname, consultant.unumber, officeDef.name, companyDef.abbreviation, inv_accts.dateReceived, employee.firstname, employee.lastname, inv_accts.type, contractDef.phase_id FROM dbuser as consultant, employee, inv_accts, officeDef, employee_offices, companyDef, acctDef, inv_contracts, contractDef, invention WHERE consultant.id = employee.user_id AND consultant.id = employee_offices.user_id AND officeDef.id = employee_offices.office_id AND invention.company_id = companyDef.id AND inv_accts.inv_id = inv_contracts.inv_id AND invention.id = inv_contracts.inv_id AND inv_contracts.con_id = consultant.id AND consultant.id = $1 AND invention.id = $2 --AND inv_accts.dateReceived BETWEEN $3 AND $4 AND inv_accts.acct_id = acctDef.id AND acctDef.contract_id = inv_contracts.contract_id AND inv_accts.type NOT LIKE ''DISCOVER%'' AND ( (acctDef.description LIKE ''%PAYMENT%'' --AND acctDef.description NOT LIKE ''3\\%%'' AND strpos(acctDef.description, ''3%'') = 0 AND acctDef.description NOT LIKE ''%DROP SELL%'' ) OR inv_accts.type LIKE ''%BOUNCED CHECK%'' OR inv_accts.description LIKE ''%BC%'' OR inv_accts.type = ''REFUND'' ) AND contractDef.id = inv_contracts.contract_id AND contractDef.id = acctDef.contract_id ORDER BY officeDef.name ASC, consultant.lastname ASC, inv_accts.dateReceived ASC; ' LANGUAGE SQL; - This mail sent through IMP: www.resolution.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] automatic update or insert
> Currently i have implemented this as a stored procedure in the plpgsql > language. This means that in my stored procedure i first do a > select to > find out if the row exists or not, then i do a insert or update > depending if the row existed. > > Unfortunately, stored procedures seems awfully slow. And i need the > application to go faster. Do you have indexes on the columns being looked up? Make sure you create the index then ANALYZE. > One solution could be to implement the stored procedure in my program > instead. I think that this will be atleast 50% faster than my stored > procedure, so that would be ok. > > However, this has made me thinking. Couldn't this be done directly in > SQL? You could probably implement this as a trigger on the table for INSERT. Have a squiz through the documentation on triggers. Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Student Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] how to create rule as on delete
> If I add the DO INSTEAD,the record can be inserted > into 'maytable_log' and also still remain in the table > 'maytable'. Which is exactly not exist at all. It just > show it as im using the DO INSTEAD. > > My problem is, how to insert the deleted record into > table 'mytable_log' without showing it in table > 'maytable'. I really need the solution..please Add a DELETE clause on the table 'maytable' as part of the DO INSTEAD, after the INSERT INTO. Or, IIRC, you could INSERT INTO (without the DO INSTEAD) with the variable NEW - the NEW variable will store the row being deleted. Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Student Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA Tel: +61 3 9541-7002 Fax: +61 3 9541-7700 mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] SQL language
On Oct 22, 2005, at 0:24 , krishnaa sridharan wrote: I am a new user to PostGRE SQL. Welcome! [Please note that the it is PostgreSQL or Postgres.] When i installed the application on the windows xp machine, There were no language for the template database created. Is there a way to add pl/sql to the databse that i create. Can some one guide me on how to do this. PL/sql is available by default. You should be able to create a function without installing anything. To test, try creating a simple function such as this: test=# create function test_true() returns boolean language SQL as 'select true;'; CREATE FUNCTION test=# select test_true(); test_true --- t (1 row) If you want to install other procedural languages, perhaps the documentation can help. http://www.postgresql.org/docs/8.0/interactive/xplang.html#XPLANG- INSTALL Hope this helps. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Article on Oracle & MySQL in E-week
On Oct 21, 2005, at 0:38 , Wendell Anderson wrote: You are quite incorrect in stating that MySQL can now claim "parity" with PostgreSQL through it's release of version 5.0. Mr Anderson, Who are you writing to? cegarry(at)yahoo.com? Are you referring to this article? http://www.eweek.com/article2/0,1895,1876702,00.asp I skimmed it, but didn't see any mention of PostgreSQL. Perhaps I missed it, or perhaps you mean another article? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] how to create rule as on delete
I think you want a delete trigger which does your insert and then follows through with the delete by returning old. --elein [EMAIL PROTECTED] On Tue, Oct 18, 2005 at 09:43:34PM -0700, efa din wrote: > This is my rule for doing the delete event. The rule > can be created. But the problem is, after the record > has been deleted from the 'mytable', this record > cannot be inserted into table 'maytable_log'. > > CREATE RULE on_delete AS ON DELETE TO mytable DO > INSERT INTO mytable_log values (old.id,old.name); > > > > If I add the DO INSTEAD,the record can be inserted > into 'maytable_log' and also still remain in the table > 'maytable'. Which is exactly not exist at all. It just > show it as im using the DO INSTEAD. > > CREATE RULE on_delete AS ON DELETE TO mytable DO > INSTEAD INSERT INTO mytable_log values > (old.id,old.name); > > My problem is, how to insert the deleted record into > table 'mytable_log' without showing it in table > 'maytable'. I really need the solution..please > > > > > > > __ > Yahoo! Mail - PC Magazine Editors' Choice 2005 > http://mail.yahoo.com > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] convert timezone to string ...
I know that the server knows that ADT == -0400, and AST == -0300 ... is there any way of reversing that? Basically, I want to say: SELECT timezone_str(-0400, 'not dst'); and have it return ADT ... I've got a method of doing it right now, using a function, but just find it looks so messy, just wondering if there is a clean way of doing it ... Thanks ... ---(end of broadcast)--- TIP 6: explain analyze is your friend
