[GENERAL] function corresponging to over in db2
Hi, I have a query of the form select min(tab1.a) over(partition by tab1.b tab2.a tab1.c) from . in db2. What will be the corresponding query for postgres. Does Postgres have a function which provides the same functionality?? Regards, Vinita Bansal _ Enhance your profile. http://www.bharatmatrimony.com/cgi-bin/bmclicks1.cgi?74 Inform a prospective life partner about the real you. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Merging Data from Multiple DB
Thank you for the feedback. I am considering just centralizing pk
generation to the HQ. Basically, every so often, the branch will connect
back to HQ to get back a block of pk for each table. This way, performance
impact is limited and maintainence is simple.
thanks.
From: Joe Conway [EMAIL PROTECTED]
To: anon permutation [EMAIL PROTECTED]
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Merging Data from Multiple DB
Date: Mon, 03 Jan 2005 07:20:54 -0800
anon permutation wrote:
For performance reasons, each branch must has its own database and a
centralized transactional system is not an option.
I was considering just centralizing primary keys generation, but that
seems very slow too.
Segmenting primary keys among the branches is doable, but it is too much
of a maintainence nightmare.
What do you suggest?
We have a similar application. What we did is this:
1. Each database instance is assigned a unique identifier, stored in a 1
row, 1 column table (with a trigger to ensure it stays that way).
2. Write a function that can take two integers, convert them to text, and
concatenate them. In our case we convert to hex and concatenate with a
delimiter character.
3. Write another function, called something like 'nextrowid', that takes a
sequence name as its argument. Use the sequence name to get the next value
from the sequence, lookup the local unique identifier from the table
defined in #1, and pass them both to the function defined in #2.
4. Use nextrowid('seq_name') to generate your primary keys.
HTH,
Joe
---(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] function corresponging to over in db2
On Thu, Jan 06, 2005 at 08:00:13AM +, vinita bansal wrote: Hi, I have a query of the form select min(tab1.a) over(partition by tab1.b tab2.a tab1.c) from . in db2. What will be the corresponding query for postgres. Does Postgres have a function which provides the same functionality?? I've never heard of it, perhaps you could explain what it does? Perhaps input and output example? Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpYlBZcyFTvF.pgp Description: PGP signature
[GENERAL] postgresql-contrib risks?
Hello, My hosting provider is unwilling to install postgresql-contrib package, fearing that it will negatively impact other users, brake their sites, or security flaws and so on. Are there any risks involved? I need pgcrypto digest function. Is there a no risk install procedure. If he installs only the pgcrypto, not the whole contrib package, are there risks remaining? Regards. Andre Felipe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] postgresql-contrib risks?
Generally, the stuff in pgcontrib, when installed, only affect a single database. Any external C code is implemented as shared libraries and is loaded on demand. For databases not configured, they won't even see them. As for security risks, this is the only real issue. The code is not in the main server and so may not have been checked as thoroughly. The pgcrypto module is (AFAIK) just a group of standard algorithms so I'd be surprised if there were any issues there. One question though, if you are granted filesystem access to the server, there's no reason why you couldn't just get the pgcrypto module in your home directory and load it into the server yourself. I think all you need is superuser access to your database to loaded untrusted modules... Hope this helps, On Thu, Jan 06, 2005 at 01:28:18PM +, Andre Felipe Machado wrote: Hello, My hosting provider is unwilling to install postgresql-contrib package, fearing that it will negatively impact other users, brake their sites, or security flaws and so on. Are there any risks involved? I need pgcrypto digest function. Is there a no risk install procedure. If he installs only the pgcrypto, not the whole contrib package, are there risks remaining? Regards. Andre Felipe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpJVznlCDbiM.pgp Description: PGP signature
[GENERAL] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Query, view join question.
Hi all, I have 2 tables: table1 with the columns objectid, refobjectid, commonvaluecol and value1. table2 with the columns objectid, refobjectid, commonvaluecol and value2. A select * from table2 where commonvaluecol = 123 and value2 0 returns no rows. I create a view: create view miracle as select table1.objectid, table1.value1, table1.commonvalue, table1.refobjectid, table2.value2 from table1 joing table2 on table1.refobjectid = table2.refobjectid where commonvaluecol = 123 Than I do a select * from miracle where commonvaluecol = 123 and value2 0 This query returns many rows. (How) Is this possible? Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Happiness is a cross database link...
On Wed, 2005-01-05 at 18:23, Alex Turner wrote: I am trying to find out some information on how to set up a cross database links so that I can have queries and foreign keys that cross a physical database. This is not just a technical requirement, but a legal one (The client requires that we do not co-mingle data). I have a new database that has tables and indices named using a new naming schema, and an old database that was implemented based on a customer requirement that is ugly, and I don't really want to put everything into one database (technical reason). I have read that contrib/dblink can do this, but I am unable to find any documentation for this features, or infact any documentation on what is in the 'contrib' pacakge. Can anyone point me to a good place to find contrib docs, or and point me to database cross linking stuff. My boss will be really pissed if I tell him we need to spend $10k to implement Oracle to have a good solution ;) The docs for a contrib package are in their directory. For PostgreSQL 7.4.6, the readme file for the contrib package is here: postgresql-7.4.6/contrib/dblink/README.dblink and the docs are int the directory postgresql-7.4.6/contrib/dblink/doc Assuming you have the .tar.gz version of postgresql. IF you install postgresql via packages then the location of such files are dependent on how your packages are installed by your distribution. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Query, view join question.
Joost Kraaijeveld [EMAIL PROTECTED] writes: Hi all, I have 2 tables: table1 with the columns objectid, refobjectid, commonvaluecol and value1. table2 with the columns objectid, refobjectid, commonvaluecol and value2. A select * from table2 where commonvaluecol = 123 and value2 0 returns no rows. I create a view: create view miracle as select table1.objectid, table1.value1, table1.commonvalue, table1.refobjectid, table2.value2 from table1 joing table2 on table1.refobjectid = table2.refobjectid where commonvaluecol = 123 regression=# create table table1(objectid int, refobjectid int, commonvaluecol int, value1 int); CREATE TABLE regression=# create table table2(objectid int, refobjectid int, commonvaluecol int, value2 int); CREATE TABLE regression=# create view miracle as regression-# select table1.objectid, table1.value1, table1.commonvalue, table1.refobjectid, table2.value2 regression-# from table1 joing table2 on table1.refobjectid = table2.refobjectid regression-# where commonvaluecol = 123; ERROR: syntax error at or near table2 at character 135 LINE 3: from table1 joing table2 on table1.refobjectid = table2.refo... ^ regression=# create view miracle as regression-# select table1.objectid, table1.value1, table1.commonvalue, table1.refobjectid, table2.value2 regression-# from table1 join table2 on table1.refobjectid = table2.refobjectid regression-# where commonvaluecol = 123; ERROR: column table1.commonvalue does not exist regression=# create view miracle as regression-# select table1.objectid, table1.value1, table1.commonvaluecol, table1.refobjectid, table2.value2 regression-# from table1 join table2 on table1.refobjectid = table2.refobjectid regression-# where commonvaluecol = 123; ERROR: column reference commonvaluecol is ambiguous regression=# Please don't waste our time with erroneous examples. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Query, view join question.
Hi Tom, I could give you access to the database itself if needed. But these are the actual tables and view. I hope I will never make any tpo's again to upset you this way. CREATE TABLE abo_his ( klantnummer int4, abonnement int2, artikelnummer int4, omschrijving char(40), nummer_vd_levering int2, artikelnummer_gratis int4, artikelnummer_gratis_2 int4, artikelnummer_gratis_3 int4, omschrijving_gratis_artikel char(40), omschrijving_gratis_artikel_2 char(40), omschrijving_gratis_artikel_3 char(40), datum_selectie date, ordernummer int4, code_retour int2, briefnummer int2, orderbedrag_guldens numeric(8,2), orderbedrag_valuta numeric(8,2), aantal_besteld int4, verzendkosten numeric(8,2), handmatige_toevoeging int2 ) WITH OIDS; CREATE TABLE abo_klt ( klantnummer int4 NOT NULL, abonnement int2 NOT NULL, waardering_klant char(10), gem_betaaltermijn int4, reden_blokkade_oud char(40), aantal_abonnementen int2, herkomst int4, datum_abonnee date, datum_laatste_selectie date, reden_blokkade int2, datum_blokkade date, max_bedrag_lev_jaar numeric(8,2), bestelfrequentie_in_dagen int2, incasso int2, instap_categorie int2, afgewerkt int2, eenmaligemachtigingeerstekeer int2, naar_ander_abo int2 ) WITH OIDS; CREATE OR REPLACE VIEW even AS SELECT DISTINCT abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt FROM abo_his JOIN abo_klt ON abo_his.klantnummer = abo_klt.klantnummer WHERE abo_his.abonnement = 238 ORDER BY abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt; Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] pg_dump dependencies
Hi all, I have a question about pg_dump. I am backing up a database using pg_dump in text mode, which works perfectly for me, with one exception. I have a view vwDependentView which references another view, vwIndependentView. However, when I feed my dump script to psql, it is attempting to make vwDependentView first and failing. Short of editing the dump script manually, is there any way to force checking these kinds of dependencies, or alternately manually specify vwIndependentView to be restored before vwDependentView? Are there any plans to implement this kind of behavior? By the way, I presume this occurs because vwDependentView is ahead of vwIndependentView alphabetically, so that to force vwDependentView to be restored later, I could name it vwZDependentView. Is this correct? Thanks, Eric ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Question about varchar and text
Title: Question about varchar and text What is the difference between varchar and text? I have heard that you can use text instead of varchar and this could speed up your tables. Robert Stewart Network Eng Governor's Office of Technology 101 Cold Harbor Work # 502 564 9696 Cell # 502 330 5991 Email [EMAIL PROTECTED]
[GENERAL] functions in postgresql
Title: functions in postgresql Is there a way to write a function in postgresql and have the database run it on a schedule? I would like the database to run a function on every Friday. Thanks Robert Stewart Network Eng Governor's Office of Technology 101 Cold Harbor Work # 502 564 9696 Cell # 502 330 5991 Email [EMAIL PROTECTED]
Re: [GENERAL] functions in postgresql
[EMAIL PROTECTED] wrote: Is there a way to write a function in postgresql and have the database run it on a schedule? I would like the database to run a function on every Friday. Use cron or windows scheduler. J Thanks Robert Stewart Network Eng Governor's Office of Technology 101 Cold Harbor Work # 502 564 9696 Cell # 502 330 5991 Email [EMAIL PROTECTED] -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] pg_dump dependencies
On Thu, Jan 06, 2005 at 12:10:26PM -0500, Eric E wrote: I have a question about pg_dump. I am backing up a database using pg_dump in text mode, which works perfectly for me, with one exception. I have a view vwDependentView which references another view, vwIndependentView. However, when I feed my dump script to psql, it is attempting to make vwDependentView first and failing. What version of PostgreSQL are you using? pg_dump tends to improve with each release; I couldn't duplicate your problem in 8.0.0rc3 or 7.4.6. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Question about varchar and text
I don't know about running faster, but I believe the only difference between the two in postgres is that a varchar has a limit on input. They are stored and indexed the same. On January 6, 2005 12:25 pm, Stewart, Robert D (COT) wrote: What is the difference between varchar and text? I have heard that you can use text instead of varchar and this could speed up your tables. Robert Stewart Network Eng Governor's Office of Technology 101 Cold Harbor Work # 502 564 9696 Cell # 502 330 5991 Email [EMAIL PROTECTED] -- Givex - http://www.givex.com/ Derik Barclay [EMAIL PROTECTED], Systems Software Engineer +1 416 350 9660 +1 416 250 9661 (fax) ---(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] Question about varchar and text
Hi! Stewart, Robert D (COT) wrote: What is the difference between varchar and text? I have heard that you can use text instead of varchar and this could speed up your tables. http://www.postgresql.org/docs/7.4/interactive/datatype-character.html Quote: Tip: There are no performance differences between these three types, apart from the increased storage size when using the blank-padded type. -- Thomas Braad Toft ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Query, view join question.
You haven't given the complete information but the following is just a guess. It seems that abonnement is in both tables. The view is stating abo_his.abonnement = 238 I assume that your initial query was WHERE abo_klt.abonnement = 238 and AND abo_klt.afgewerkt 2 My guess is that you are asking the view a different question: WHERE abo_his.abnnement = 238 AND abo_klt.afgewerkt 2 Hi all, I have 2 tables: table1 with the columns objectid, refobjectid, commonvaluecol and value1. table2 with the columns objectid, refobjectid, commonvaluecol and value2. A select * from table2 where commonvaluecol = 123 and value2 0 returns no rows. I create a view: create view miracle as select table1.objectid, table1.value1, table1.commonvalue, table1.refobjectid, table2.value2 from table1 joing table2 on table1.refobjectid = table2.refobjectid where commonvaluecol = 123 Than I do a select * from miracle where commonvaluecol = 123 and value2 0 This query returns many rows. (How) Is this possible? Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Editor: Syntax highlighting and stored procedures surrounded by '
I use emacs and syntax highlighting is great -- except that because stored procedures are completely enclosed between two single quotes, all the coloring is off for that portion. Is there a way to not surround stored procedures by quotes or does anybody have a solution that works for them? Thanks. Eric Brown 408-571-6341 www.propel.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Question about varchar and text
Derik Barclay wrote: I don't know about running faster, but I believe the only difference between the two in postgres is that a varchar has a limit on input. They are stored and indexed the same. There is a theory that text is faster than varchar, because postgresql doesn't have to check text. So it is one less thing for PostgreSQL to do before it provides the tuple. J On January 6, 2005 12:25 pm, Stewart, Robert D (COT) wrote: What is the difference between varchar and text? I have heard that you can use text instead of varchar and this could speed up your tables. Robert Stewart Network Eng Governor's Office of Technology 101 Cold Harbor Work # 502 564 9696 Cell # 502 330 5991 Email [EMAIL PROTECTED] -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(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] pg_dump dependencies
Hi Michael, Well, I dumped the database from two different versions of Postgres (on different machines), using both pg_dump 8.0.0beta2 and pg_dump 7.4.2 (which correspond to the respective server versions). I only restored on the 8.0.0beta machine, though. Would that make a difference? I will also poke around in my database to see if perhaps something else is blocking a proper dump. Thanks, EE Michael Fuhr wrote: On Thu, Jan 06, 2005 at 12:10:26PM -0500, Eric E wrote: I have a question about pg_dump. I am backing up a database using pg_dump in text mode, which works perfectly for me, with one exception. I have a view vwDependentView which references another view, vwIndependentView. However, when I feed my dump script to psql, it is attempting to make vwDependentView first and failing. What version of PostgreSQL are you using? pg_dump tends to improve with each release; I couldn't duplicate your problem in 8.0.0rc3 or 7.4.6. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Query, view join question.
Joost Kraaijeveld [EMAIL PROTECTED] writes: CREATE OR REPLACE VIEW even AS SELECT DISTINCT abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt FROM abo_his JOIN abo_klt ON abo_his.klantnummer = abo_klt.klantnummer WHERE abo_his.abonnement = 238 ORDER BY abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt; Okay ... but the view is constraining abo_his.abonnement and outputting abo_klt.aantal_abonnementen. Why would you assume that joining on klantnummer would cause these two fields to necessarily be the same? 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: [GENERAL] pg_dump dependencies
Michael Fuhr [EMAIL PROTECTED] writes: On Thu, Jan 06, 2005 at 12:10:26PM -0500, Eric E wrote: I have a question about pg_dump. I am backing up a database using pg_dump in text mode, which works perfectly for me, with one exception. I have a view vwDependentView which references another view, vwIndependentView. However, when I feed my dump script to psql, it is attempting to make vwDependentView first and failing. What version of PostgreSQL are you using? pg_dump tends to improve with each release; I couldn't duplicate your problem in 8.0.0rc3 or 7.4.6. 8.0 is the first version in which pg_dump really is capable of avoiding this sort of problem. In older releases the dump order is basically the same as the order of original creation of the objects --- so you can easily confuse it by, for example, doing CREATE OR REPLACE VIEW to modify a view to reference a table that didn't exist when the view was first defined. A workaround that may or may not be worse than the disease is to drop the dependent view completely and then recreate it. If there's other stuff that depends on the dependent view this can cascade into a real PITA :-( regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Query, view join question.
Hi Tom, [EMAIL PROTECTED] schreef: Joost Kraaijeveld [EMAIL PROTECTED] writes: CREATE OR REPLACE VIEW even AS SELECT DISTINCT abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt FROM abo_his JOIN abo_klt ON abo_his.klantnummer = abo_klt.klantnummer WHERE abo_his.abonnement = 238 ORDER BY abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt; Okay ... but the view is constraining abo_his.abonnement and outputting abo_klt.aantal_abonnementen. Why would you assume that joining on klantnummer would cause these two fields to necessarily be the same? In the table abo_klt there is no record where abo_klt.abonnement = 238 and abo_klt.afgewerkt 0: munt=# select * from abo_klt where abonnement = 238 and afgewerkt 0; ... (0 rows) So I assumed that in no join between abo_his (which has no afgewerkt column at all ) and abo_klt (which has 0 records with a afgewerkt columns 0) as created above ( with WHERE abo_his.abonnement = 238) there could be a record with both abonnement = 238 and afgewerk 0. But there are: on the view there are : munt=# select * from even where afgewerkt 0; . (797 rows) SO I must understand something wrong... Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(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] ltree valid characters
Hello, I'm using ltree but I have a requirement to use the - character in the text of a node. Can I just change (in ltree.h) the following line: #define ISALNUM(x) ( isalnum((unsigned char)(x)) || (x) == '_') to: #define ISALNUM(x) ( isalnum((unsigned char)(x)) || (x) == '_' || (x) == '-') .. or will there be hidden consequences of this?... Thanks! - Greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Query, view join question.
Joost Kraaijeveld [EMAIL PROTECTED] writes: CREATE OR REPLACE VIEW even AS SELECT DISTINCT abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt FROM abo_his JOIN abo_klt ON abo_his.klantnummer = abo_klt.klantnummer WHERE abo_his.abonnement = 238 ORDER BY abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt; In the table abo_klt there is no record where abo_klt.abonnement = 238 and abo_klt.afgewerkt 0: munt=# select * from abo_klt where abonnement = 238 and afgewerkt 0; Yes, but the join isn't testing abo_klt.abonnement. It's testing abo_his.abonnement. If there's a reason to think that rows in the two tables with the same klantnummer must also have the same abonnement, you have not said what it is. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Query, view join question.
I think I stated in my previous post but in order to make your view consistent with your original query I think you should do: CREATE OR REPLACE VIEW even AS SELECT DISTINCT abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt FROM abo_his JOIN abo_klt ON abo_his.klantnummer = abo_klt.klantnummer WHERE abo_klt.abonnement = 238 // I CHANGED THIS LINE ORDER BY abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt; It should not be WHERE abo_his.abonnement = 238 Unless you expect abo_his.abonnement always equal to abo_klt.abonnement Hi Tom, [EMAIL PROTECTED] schreef: Joost Kraaijeveld [EMAIL PROTECTED] writes: CREATE OR REPLACE VIEW even AS SELECT DISTINCT abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt FROM abo_his JOIN abo_klt ON abo_his.klantnummer = abo_klt.klantnummer WHERE abo_his.abonnement = 238 ORDER BY abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt; Okay ... but the view is constraining abo_his.abonnement and outputting abo_klt.aantal_abonnementen. Why would you assume that joining on klantnummer would cause these two fields to necessarily be the same? In the table abo_klt there is no record where abo_klt.abonnement = 238 and abo_klt.afgewerkt 0: munt=# select * from abo_klt where abonnement = 238 and afgewerkt 0; ... (0 rows) So I assumed that in no join between abo_his (which has no afgewerkt column at all ) and abo_klt (which has 0 records with a afgewerkt columns 0) as created above ( with WHERE abo_his.abonnement = 238) there could be a record with both abonnement = 238 and afgewerk 0. But there are: on the view there are : munt=# select * from even where afgewerkt 0; . (797 rows) SO I must understand something wrong... Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Query, view join question.
I think there is an echo in here :) It's probably me. I pass for the rest of this thread. Joost Kraaijeveld [EMAIL PROTECTED] writes: CREATE OR REPLACE VIEW even AS SELECT DISTINCT abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt FROM abo_his JOIN abo_klt ON abo_his.klantnummer = abo_klt.klantnummer WHERE abo_his.abonnement = 238 ORDER BY abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen, abo_klt.afgewerkt; In the table abo_klt there is no record where abo_klt.abonnement = 238 and abo_klt.afgewerkt 0: munt=# select * from abo_klt where abonnement = 238 and afgewerkt 0; Yes, but the join isn't testing abo_klt.abonnement. It's testing abo_his.abonnement. If there's a reason to think that rows in the two tables with the same klantnummer must also have the same abonnement, you have not said what it is. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Editor: Syntax highlighting and stored procedures surrounded
Eric Brown wrote: I use emacs and syntax highlighting is great -- except that because stored procedures are completely enclosed between two single quotes, all the coloring is off for that portion. Is there a way to not surround stored procedures by quotes or does anybody have a solution that works for them? In 8.0 final when released there is special $$ quoting, but not earlier versions. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Editor: Syntax highlighting and stored procedures surrounded by '
On Jan 6, 2005, at 11:43 AM, Bruce Momjian wrote: Eric Brown wrote: I use emacs and syntax highlighting is great -- except that because stored procedures are completely enclosed between two single quotes, all the coloring is off for that portion. Is there a way to not surround stored procedures by quotes or does anybody have a solution that works for them? In 8.0 final when released there is special $$ quoting, but not earlier versions. Great! Well, I had to move to 8.0 anyway to get better support for passing around composite types. So where is the $$ quoting stuff documented? How do I use it? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] question about plpythonu
Dear all, I was setting up plpythonu with port system on FreeBSD. All ports related plpythonu are installed well. But there was an error when I issued createlang plpythonu template1 with postgres user account. It said like bellow. $ createlang plpythonu template1 createlang: language installation failed: ERROR: could not load library /usr/local/lib/postgresql/plpython.so: dlopen '/usr/local/lib/postgresql/plpython.so' failed. (/usr/local/lib/libpython2.4.so: Undefined symbol pthread_attr_destroy) It seems I miss something but I have no idea. Thanks in advance. Doseok Kim ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] DBMS_File Equivalent?
Can a trigger, written in plperl, write to the OS when it fires? I want to write a trigger, in plperl, that builds an RSS feed file with each row that gets added to one of my tables. My database (8 rc 3, on Redhat 9) seems to choke (terminated on signal 11) each time I try to write to the OS - I would imagine because it's a major security threat to have the DB blindly writing to the filesystem. I guess what I really need is something like dbms_file in Oracle, where one predefines where the DB is allowed to read and write files. Can this be done with Postgres? Dunc ---(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] DBMS_File Equivalent?
Dunc wrote: Can a trigger, written in plperl, write to the OS when it fires? I want to write a trigger, in plperl, that builds an RSS feed file with each row that gets added to one of my tables. My database (8 rc 3, on Redhat 9) seems to choke (terminated on signal 11) each time I try to write to the OS - I would imagine because it's a major security threat to have the DB blindly writing to the filesystem. I guess what I really need is something like dbms_file in Oracle, where one predefines where the DB is allowed to read and write files. Can this be done with Postgres? You can do it with plperlu but not plperl. The procedure will be fired as your postgresql user (the owner of the actual catalog) so you want to make sure that where the procedure is trying to write can be accessed by that user. Sincerely, Joshua D. Drake Dunc ---(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 -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Query, view join question.
On Thu, 2005-01-06 at 17:57 +0100, Joost Kraaijeveld wrote: Hi Tom, I could give you access to the database itself if needed. But these are the actual tables and view. I hope I will never make any tpo's again to upset you this way. no-one was upset. the point is just that you are more likely to get useful answers when those who would help you do not first have to guess what you did. a simplified case, as you tried to show us, is excellent, but you should test it first, and post a cut-and-paste copy of your commands and output to minimize typos. gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] DBMS_File Equivalent?
I wrote: Can a trigger, written in plperl, write to the OS when it fires? I want to write a trigger, in plperl, that builds an RSS feed file with each row that gets added to one of my tables. My database (8 rc 3, on Redhat 9) seems to choke (terminated on signal 11) each time I try to write to the OS - I would imagine because it's a major security threat to have the DB blindly writing to the filesystem. I guess what I really need is something like dbms_file in Oracle, where one predefines where the DB is allowed to read and write files. Can this be done with Postgres? Dunc In thinking about this I'm wondering if I'm making this harder then it needs to be. If I could write a function that builds, and returns the RSS file that would accomplish what I want - assuming that I could call the function from a webpage. Can Postgres functions be called from a URL, similar to how PL/SQL files can be in Oracle? Dunc ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] DBMS_File Equivalent?
Dunc [EMAIL PROTECTED] writes: Can a trigger, written in plperl, write to the OS when it fires? No, but a plperlu trigger can. My database (8 rc 3, on Redhat 9) seems to choke (terminated on signal 11) each time I try to write to the OS - I would imagine because it's a major security threat to have the DB blindly writing to the filesystem. It is a security hole, but sig 11 is not the expected response :-(. Can you provide a debugger backtrace from the crash, or even better a complete test case? What Perl version are you using? regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] DBMS_File Equivalent?
In thinking about this I'm wondering if I'm making this harder then it needs to be. If I could write a function that builds, and returns the RSS file that would accomplish what I want - assuming that I could call the function from a webpage. Can Postgres functions be called from a URL, similar to how PL/SQL files can be in Oracle? Not without a wrapper to the connection. If in perl... just have a cgi call it. Sincerley, Joshua D. Drake Dunc ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Global/persistent variables
I'm analysing a conversion of a system from Oracle to PG. The system in case uses a lot of PL/SQL packages and lots of them uses public/static global/persistent variables declared inside its package specification or body. AFAIK PG doesn't have packages - this is not a problem since I can handle it with different schemas or some naming convention, but is there a way to declare persistent variables (that would be visible to any function up to the end of the session) ? TIA Ronnie begin:vcard fn:Ronnie Meier Ramos n:Ramos;Ronnie org;quoted-printable:Viler Cal=C3=A7ados Ltda. adr;quoted-printable:;;RS 239, n=C2=BA500;Novo Hamburgo;RS;93352-000;Brasil email;internet:[EMAIL PROTECTED] title;quoted-printable:Gerente de Inform=C3=A1tica tel;work:+55 (51) 2129-3800 tel;fax:+55 (51) 2129-3801 x-mozilla-html:TRUE version:2.1 end:vcard ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Global/persistent variables
Ronnie Meier Ramos [EMAIL PROTECTED] writes: AFAIK PG doesn't have packages - this is not a problem since I can handle it with different schemas or some naming convention, but is there a way to declare persistent variables (that would be visible to any function up to the end of the session) ? plpgsql doesn't have this at present, but some of the other PLs do. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Query, view join question.
Hi both, Thanks for taking the trouble to help me. Based on your responses I realized that a multi key join should do what I wanted and it does. Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Global/persistent variables
AFAIK PG doesn't have packages - this is not a problem since I can handle it with different schemas or some naming convention, but is there a way to declare persistent variables (that would be visible to any function up to the end of the session) ? AFAIK you'd have to fake it using a temp table. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] warning: pg_query(): Query failed
On Wed, Jan 05, 2005 at 11:16:29 -0800, Aaron Steele [EMAIL PROTECTED] wrote: would it be useful to see the IF PLSQL function, or would you recommend a modification to the database.pgsql.inc file? You should probably try looking at the IF function first to see if you can spot it using 2 as a boolean and why it is doing that. It may be simple to fix. It didn't look to me like the problem was with the php code unless the code you showed us was getting changed by php before getting sent to postgres. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Global/persistent variables
I understand your pain, because PL/SQL is so close to plpgsql the functions should just drop into quotes with renamed parameters, but they don't. Using another language makes the port more difficult. Have you considered functions that store the variable value in the database and read it back from there? The functions would have to store variables values by some kind of session or user ID so concurrent sessions wouldn't trump one another. This would help with variable reference but variable assignment would require more recoding. There is also the issue of initializing sessions and cleaning up after them. Not pretty. Rick Tom Lane [EMAIL PROTECTED]To: Ronnie Meier Ramos [EMAIL PROTECTED] Sent by: cc: pgsql-general@postgresql.org [EMAIL PROTECTED]Subject: Re: [GENERAL] Global/persistent variables tgresql.org 01/06/2005 03:43 PM Ronnie Meier Ramos [EMAIL PROTECTED] writes: AFAIK PG doesn't have packages - this is not a problem since I can handle it with different schemas or some naming convention, but is there a way to declare persistent variables (that would be visible to any function up to the end of the session) ? plpgsql doesn't have this at present, but some of the other PLs do. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] postgresql-contrib risks?
On Thu, Jan 06, 2005 at 15:09:54 +0100, Martijn van Oosterhout kleptog@svana.org wrote: One question though, if you are granted filesystem access to the server, there's no reason why you couldn't just get the pgcrypto module in your home directory and load it into the server yourself. I think all you need is superuser access to your database to loaded untrusted modules... Hopefully his provider doesn't let people install functions written in untrusted languages. pgcrypto uses C functions and he will probably need his provider to load it for him. If there is some trusted language installed that he has access to, he may be able to create a function in that language that does what he wants. He doesn't need file system access to do that. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] warning: pg_query(): Query failed
On Thu, Jan 06, 2005 at 14:38:53 -0800, Aaron Steele [EMAIL PROTECTED] wrote: hi bruno, Please copy replies to the list so that other people can help and learn from your problem. here's the IF() from psql, although i'm not familiar enough to really know what to look for. do you see anything obviously wrong here? is there a better way to look at the IF() function? .. dmapdb=# \df+ if List of functions Result data type | Schema | Name | Argument data types | Owner | Language | Source code | Description --++--+-+--- +-- +--- ---+- text | public | if | integer, text, text | dmap | plpgsql | BEGIN IF $1 THEN RETURN $2; END IF; IF NOT $1 THEN RETURN $3; END IF; END; In the php code you showed the following fragment: IF(l.last_comment_uid, cu.name, l.last_comment_name) I doubt that l.last_comment_uid is a boolean based on its name. If it isn't this is probably the source of your problem. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Global/persistent variables
Ronnie Meier Ramos wrote: AFAIK PG doesn't have packages - this is not a problem since I can handle it with different schemas or some naming convention, but is there a way to declare persistent variables (that would be visible to any function up to the end of the session) ? You might be able to fake it with some C functions. See: http://www.joeconway.com/myfunc.tgz Look at myfunc_setvar(), myfunc_getvar(), and myfunc_rmvar(). HTH, Joe ---(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] warning: pg_query(): Query failed
hi bruno, turns out that l.last_comment_uid and l.last_comment_name are integer and char var respectively. since i'm using 7.4.1 with strict boolean casting, is there a better alternative to instantiating a different version of pgsql on my server? In the php code you showed the following fragment: IF(l.last_comment_uid, cu.name, l.last_comment_name) I doubt that l.last_comment_uid is a boolean based on its name. If it isn't this is probably the source of your problem. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] disabling OIDs?
On Sunday 02 January 2005 08:24, Martijn van Oosterhout wrote:
On Sat, Jan 01, 2005 at 06:35:30PM -0800, Jeff Davis wrote:
On Sun, 2004-12-12 at 20:25 -0800, Lonni J Friedman wrote:
OK, thanks. So is there any real benefit in doing this in a generic
(non-dspam) sense, or is it just a hack that wouldn't be noticable?
Any risks or potential problems down the line?
I'd just like to add that some 3rd party applications/interfaces make
use of OIDs, as a convenient id to use if there is no primary key (or if
the 3rd party software doesn't take the time to find the primary key).
One might argue that those 3rd party applications/interfaces are broken,
but you still might want to keep OIDs around in case you have a use for
one of those pieces of software.
Yep, especially since an OID is not a unique value and so can't
possibly be a primary key and generally isn't indexed either. Even
Access asks you to identify the primary key...
Of course some 3rd party apps are nice and they look for a primary key first,
then a unique index, then look for an oid. Furthermore the really clueful
ones will check # of affected rows = 1 when modifying by oid, so its pretty
safe.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Editor: Syntax highlighting and stored procedures surrounded by '
Eric, On Jan 6, 2005, at 2:51 PM, Eric Brown wrote: Great! Well, I had to move to 8.0 anyway to get better support for passing around composite types. So where is the $$ quoting stuff documented? How do I use it? In the 8.0 beta html documentation you can find at this path html/sql-syntax.html#SQL-SYNTAX-DOLLAR-QUOTING (or just look in the index under dollar quoting) I thought there was another section some where, but I'm not seeing it at the moment. Basically, you can replace the function start and end single quote with $$ and then not worry about doubling or escaping single quotes in between. For example: -- trigger to fold all domain names to lowercase, ensure both columns are not null create or replace function tg_address_biu() returns trigger as $$ begin if new.domain_name is null and new.ip is null then raise exception 'Both the domain_name and ip columns cannot be null.'; end if; if new.domain_name is not null then new.domain_name = lower(new.domain_name); end if; return new; end; $$ language plpgsql; The previous quoting method still works, so there is no requirement to update everything. If you are using Mac or Windows, pgEdit supports function syntax coloring with both quoting methods and has support for Emacs key bindings. Best, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Serial Foreign Key
Suppose I have two tables: create table t1 ( idserialnot null ); create table t2 ( idserialnot null, t1integernot null, foreign key (t1) references t1 (id) ); Should the t1 integer actually be a bigint? I ask because when I do select * from t1_id_serial the max_value is much larger than 2^32. Does this vary from system to system? Thanks, jonathan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] unsubscribe
unsubscribe attachment: winmail.dat ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] warning: pg_query(): Query failed
On Thu, Jan 06, 2005 at 17:32:30 -0800, Aaron Steele [EMAIL PROTECTED] wrote: hi bruno, turns out that l.last_comment_uid and l.last_comment_name are integer and char var respectively. since i'm using 7.4.1 with strict boolean casting, is there a better alternative to instantiating a different version of pgsql on my server? A better solution is fixing your code. What do you expect it to do anyhow? Once you figure out what you want it to do, you should be able to write a boolean expression that is true, false or null when you want it to be. If fact from what I saw it seems that you could just use a CASE expression and skip the function call altogether. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Serial Foreign Key
On Jan 7, 2005, at 14:45, Jonathan Stafford wrote:
Suppose I have two tables:
create table t1 (
idserialnot null
);
create table t2 (
idserialnot null,
t1integernot null,
foreign key (t1) references t1 (id)
);
Should the t1 integer actually be a bigint? I ask because when I do
select * from t1_id_serial the max_value is much larger than 2^32.
Does this vary from system to system?
While all sequences are 64 bit integers, SERIAL is essentially INTEGER
DEFAULT nextval('foo_seq') (and creating the necessary sequence), so it
will only include 32 bit integers. If you want 64 bit integers, you can
use BIGSERIAL.
Best,
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
