Re: [SQL] select ... for update
> > Hi, > > How can I use select ... for update to update limit to update what I > select?? First thing - the SELECT FOR UPDATE is not merge of SELECT and UPDATE but transaction option. The PostgreSQL use row-locking for UPDATEed rows. Standard SELECT ignore this lock, but SELECT FOR UPDATE wait until *other* transaction with UPDATE will commited. > somewhat like: > select url,id from mytable for update order by priority,id limit 5; ^^^ see the SELECT's syntax, ORDER BY must be before FOR UPDATE. > I want update the id in above return like: > update mytable set allocatedto='whatever' where id in above return set. Can't you in UPDATE's WHERE define some 'id' as in above SELECT? An example (via subselect): UPDATE mytable SET allocatedto='whatever' WHERE id IN ( SELECT id FROM mytable ORDER BY priority,id LIMIT 5 ); But it not will too much fast... better is define relevant 'id' inside UPDATE's WHERE without sub-select, but if you need define it via ORDER+LIMIT it's impossible. Karel
[SQL] Postgres closing the connection too fast with php4+apache
Hello, in old days i was used to open the connection to my DB at the begin of the script, make lots of stuff inside the script and eventually close the connection at the end (even if php is supposed to do it for me...). This stopped to work now each time i make a call to the DB i connect, make immediately after the request, and close immediately despite this i still get sometimes the dreaded: Warning: 45 is not a valid PostgreSQL link resource in /home/bboett/www/fibu/includes/global.php on line 344 Erreur durant la requete conn = Resource id #45 and most of what i do not understand is : displaying the connection shows me a var type conform to what i may expect?? so why does this error appear? if i open the connection at the begin of my script as i was used, i get for nearly all exec that type of response as this way of doing is real DB intensive (i hear it due to my HD) is there anything i could do, something i do wrong? i use postgres, php4, apache on a debian woody x86 system of latest breed... -- ciao bboett == [EMAIL PROTECTED] http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett === the total amount of intelligence on earth is constant. human population is growing
[SQL] Great thanks (JDBC). Another probem.
Hello.
Thank you for your answer (really big thanks). But I have another problem.
I try to connect to my linux postgresql server and I have some error. I
have linux machine on my local network (e.g. 192.168.1.100), on this
machine I have postgresql 7.0.2. In my "pg_hba.conf" file is line like that:
hostall 0.0.0.0 0.0.0.0 password
so I think I could connect to psql from all network. I write Java class on
second computer (w2k) but in the same network (e.g. 192.168.1.101). The
code looks like that:
"
import java.sql.*;
class Javasql
{
public static void main(String[] args)
{
try
{
Class.forName("org.postgresql.Driver");
Connection db =
DriverManager.getConnection("jdbc:postgresql://192.168.1.100/menu","javaus",
"testpwd");
}
catch(Exception e)
{
System.out.println("Exception !!!");
}
System.out.println("After Exception");
}
}
"
where:
javaus user in pg_shadow
menu database
testpwd correct password for javaus
Problem is that I cant connect to server. Appear exception. Exception is
caused by getConnection() method not Class.for.Name(). I try also write
line like that
DriverManager.getConnection("jdbc:postgresql://192.168.1.100:5432/menu","jav
aus,"testpwd");
The same problem. Postgres looks fine, because he accept connection from
php script (localhost). I can't test Java localhost connection (ther is no
sDK or JVM on linux machine).
I don't know I do wrong.
If any one can help me. Please.
Mateusz.
[SQL] Date Format
In MS Access is: SELECT FORMAT([field],'DD/MM/') AS new name; How I can make in pgaccess? Daniel Hentges Automação Fockink In. Eletricas Ltda. Panambi - RS - Brasil
RE: [SQL] Date Format
Hi Daniel, Try this as your query: SELECT to_char(field, 'DD/MM/') AS "new name"; Hope this helps Francis Solomon > > In MS Access is: > SELECT FORMAT([field],'DD/MM/') AS new name; > How I can make in pgaccess? > > Daniel Hentges > Automação > Fockink In. Eletricas Ltda. > Panambi - RS - Brasil > >
Re: [SQL] SQL parse error
On Tue, 12 Dec 2000, Tom Lane wrote:
TL> play=> create table out2cp(cp varchar(6));
TL> CREATE
TL> play=> insert into out2cp values('3182.1');
TL> INSERT 405833 1
TL> play=> insert into out2cp values('3182.2');
TL> INSERT 405834 1
TL> play=> select substring(cp from 1 for 4)::int4 from out2cp;
TL> ?column?
TL> --
TL> 3182
TL> 3182
TL> (2 rows)
When I do this, it works for me, too. But when I do it on my current
database, something breaks. The table I work on is:
swports=# \d out2cp
Table "out2cp"
Attribute |Type | Modifier
---+-+--
cp| varchar(10) |
outlet| varchar(10) |
location | varchar(32) |
Index: o2c_cp
Values in cp field look like this:
swports=# select cp from out2cp ;
cp
-
3182.4
3182.7
3182.2
3182.5
3182.6
3182.8
3169.1
3169.3
3169.4
3169.7
3165.1
Bye Borek
--
=
BOREK LUPOMESKYUsti nad Labem, Czech Republic, Europe
WWW: http://www.volny.cz/borekl/ PGP keyid: B6A06AEB
==[ MIME/ISO-8859-2 & PGP encrypted mail welcome ]===
Re: [SQL] Postgres closing the connection too fast with php4+apache
On Wed, Dec 13, 2000 at 10:55:03AM +0100, Bruno Boettcher wrote: > > despite this i still get sometimes the dreaded: > Warning: 45 is not a valid PostgreSQL link resource in > /home/bboett/www/fibu/includes/global.php on line 344 >Erreur durant la requete conn = Resource id #45 Pardon me, bout wouldn't this be more appropriate on a PHP list? -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
Re: [SQL] SQL parse error
=?ISO-8859-2?Q?Borek_Lupom=ECsk=FD?= <[EMAIL PROTECTED]> writes: >When I do this, it works for me, too. But when I do it on my current > database, something breaks. Hm. The obvious question is: are you *sure* all the entries in your table look like ".N"? regards, tom lane
[SQL] How to represent a tree-structure in a relational database
I am just thinking about the data model for a little content management system that I am currently planning. Individual articles are sorted under different categories which branch into subcategories, sub-subcategories etc. up to a depth of about 6 or 7 levels. The structure should be extensible, i.e. it must be possible to add levels. What I am thinking now is that you would keep the index in a separate index table (linked with the primary key in the articles table), which would have 6 or 7 fields initially, and that you'd add columns with the alter table command, if need be, to make the structure deeper. Is this the recommended way to go about it? It feels pretty 'right' to me now but since the problem should be fairly common, there must be other people who have thought and written about it and there might even be a recognized 'optimal' solution to the problem. Comments? - Frank
[SQL] Strange slow behavior in backend
I'm using 7.0.1 with a TCL frontend.
I have a schema that splits large files into tuple-sized bites and stores
them in a table. This was done before TOAST in order to store large
files.
I have a backend TCL function that re-assembles the file like this:
-- Fetch the specified document data, reassembling the bits back
together
-- in the right order.
-- Calling sequence: cont_doc_fetch(crt_by,crt_date,ctype)
create function cont_doc_fetch(int4,timestamp,varchar) returns
text as '
set odata {}
spi_exec -array d "select data from cont_doc_data
where crt_by = \'$1\' and crt_date = \'$2\' and ctype = \'[quote $3]\'
order by seq" {
append odata $d(data)
}
return $odata
' LANGUAGE 'pltcl';
This worked great until I put a real big file in (about 5M). Then,
when I tried to fetch the file, it seemed really slow (about 60 seconds).
I tried reassembling the file in the frontend instead and my time dropped
to about 6 seconds using this TCL fragment (mpg::qlist is an interface
to pg_exec that returns a list of tuples):
set data {}
set tuple_list [mpg::qlist "select data from
$ca(prefix)_doc_data where crt_by = $crt_by and crt_date = '$crt_date'
and ctype = '$ctype' order by seq"]
foreach rec $tuple_list {
append data [lindex
$rec 0]
}
The only difference I can identify is whether the re-assembly TCL code
is running as a procedural language (backend) or in the frontend.
Anyone have any idea why the difference is so dramatic?
Jan:
Is this the difference between old TCL and new TCL (with multi-port
objects)? Or is there something else about the way the backend handles
large chunks of data that would mark the difference?
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:President
x-mozilla-cpt:;0
fn:Kyle Bateman
end:vcard
Re: [SQL] How to represent a tree-structure in a relational database
Frank, Please look in the list archives. About 2 months ago this topic came up and was discussed extensively (including a creative solution by yours truly). -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] How to represent a tree-structure in a relational database
On Wed, Dec 13, 2000 at 04:48:47PM +0100, Frank Joerdens allegedly wrote:
> I am just thinking about the data model for a little content management system that
>I am
> currently planning. Individual articles are sorted under different categories which
>branch
> into subcategories, sub-subcategories etc. up to a depth of about 6 or 7 levels. The
> structure should be extensible, i.e. it must be possible to add levels. What I am
>thinking
> now is that you would keep the index in a separate index table (linked with the
>primary
> key in the articles table), which would have 6 or 7 fields initially, and that you'd
>add
> columns with the alter table command, if need be, to make the structure deeper. Is
>this
> the recommended way to go about it? It feels pretty 'right' to me now but since the
> problem should be fairly common, there must be other people who have thought and
>written
> about it and there might even be a recognized 'optimal' solution to the problem.
>
> Comments?
Yeah. I've built something similar.
The way I've done it:
Give each record a unique ID (generated with a sequence) and store
the records in a table. Create a second table in which you store
parent id-child id combinations.
So:
1 - Automotive transport
2 - Cars
3 - Motorcycles
Store in the table:
1-2
1-3
There's one main category (Automotive transport) which has two sub-categories:
Cars & Motorcyles
The way I'd do it if I had to do it again:
Give each record a unique id, generated by the application. Denote levels with
extra letters.
So:
AA - Automotive transport
- Cars
AAAB - Motorcycles
The structures has the added bonus of making it very easy to determine all the
sub-categories of a category, no matter how deep the tree is below the category
you're looking at. With the first approach it is not possible to do this in a
single SQL query. You could do this with a function, I guess.
I hope this is of some use to you.
Cheers,
Mathijs
--
"Borrowers of books -- those mutilators of collections, spoilers of the
symmetry of shelves, and creators of odd volumes."
Charles Lamb (1775-1834)
[SQL] Selecting Most Recent Row
Ok here is the problem. Table: Widgets Fields: Unique_Key, DateStamp, Batch_Number, Instructions. Basic Select Statement: select Unique_Key from Widgets where Batch_Number>='inputedvalue' Problem: Some Batch_Numbers might be duplicated over a period of time. I want to select the most recent of these Batch Numbers. -- Steve Meynell Candata Systems
Re: [SQL] SQL parse error
On Wed, 13 Dec 2000, Tom Lane wrote: TL> =?ISO-8859-2?Q?Borek_Lupom=ECsk=FD?= <[EMAIL PROTECTED]> writes: TL> >When I do this, it works for me, too. But when I do it on my current TL> > database, something breaks. TL> TL> Hm. The obvious question is: are you *sure* all the entries in your TL> table look like ".N"? You're right -- not all of them do, there are some anomalous ones. Thanks for your help and pardon my ignorance. Bye Borek -- = BOREK LUPOMESKYUsti nad Labem, Czech Republic, Europe WWW: http://www.volny.cz/borekl/ PGP keyid: B6A06AEB ==[ MIME/ISO-8859-2 & PGP encrypted mail welcome ]===
Re: [SQL] How to represent a tree-structure in a relationaldatabase
I once started writing a small paper on this subject; it is still in a rather preliminary state. You can download the draft (and some ill documented code, 53kB) from http://www.utdt.edu/~mig/sql-trees Miguel >> Original Message << On 12/13/00, 12:48:47 PM, Frank Joerdens <[EMAIL PROTECTED]> wrote regarding [SQL] How to represent a tree-structure in a relational database: > I am just thinking about the data model for a little content management system that I am > currently planning. Individual articles are sorted under different categories which branch > into subcategories, sub-subcategories etc. up to a depth of about 6 or 7 levels. The > structure should be extensible, i.e. it must be possible to add levels. What I am thinking > now is that you would keep the index in a separate index table (linked with the primary > key in the articles table), which would have 6 or 7 fields initially, and that you'd add > columns with the alter table command, if need be, to make the structure deeper. Is this > the recommended way to go about it? It feels pretty 'right' to me now but since the > problem should be fairly common, there must be other people who have thought and written > about it and there might even be a recognized 'optimal' solution to the problem. > Comments? > - Frank
Re: [SQL] Decimal vs.Numeric vs. Int & type for OID
Nikhil G. Daddikar writes: > It is unclear to me what the real difference is and when to use what. > Any pointers would be greatly appreaciated. Integers are integers (uh...) Numeric is for exact fixed-point decimal numbers. Monetary amounts are a good example. Decimal is equivalent to numeric. The oid type should only be used as a foreign key to an oid column. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Selecting Most Recent Row
> Ok here is the problem. > Table: Widgets > Fields: Unique_Key, DateStamp, Batch_Number, Instructions. > > Basic Select Statement: > select Unique_Key from Widgets where Batch_Number>='inputedvalue' > > Problem: > Some Batch_Numbers might be duplicated over a period of time. I want > to select the most recent of these Batch Numbers. Will DateStamp being the date of insertion? If so, is it that you want the record for the most recent (largest) datestamp for each batch_number? something like SELECT DISTINCT ON (batch_number) unique_key, datestamp, batch_number, instructions FROM widgets ORDER BY batch_number, datestamp desc; (sort by batch then by date (last first) and show the first (aka 'distinct') row, considering only the batch_number for distinctness) HTH. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [SQL] How to represent a tree-structure in a relational database
On Wed, Dec 13, 2000 at 11:38:18AM -0800, Stuart Statman wrote: [ . . . ] > I would suggest, instead, to create a table that represents your hierarchy > without adding columns. For example : > > create table Category ( > CategoryID int4 not null primary key, > ParentCategoryID int4 not null REFERENCES Category (CategoryID), > CategoryName varchar(100) > ); > > Add a CategoryID with an FK reference to this table, and your work is done. > > Then adding, inserting, removing, or moving layers in the hierarchy becomes > quite simple. This also preserves hierarchical integrity, where subcategory > a of subcategory b will also remain a subcategory of category c if > subcategory b is a subcategory of subcategory c, where I'm not sure your > model will preserve or guarantee that. (Does that sentence deserve a prize?) Cool. That looks like my solution. I had actually seen it someplace before, but didn't make the connection with my problem. Ta, Frank
Re: [SQL] Selecting Most Recent Row
Joel, Thank you very much. I gave that a try and it worked perfectly. It definately was the distinct keyword I was missing. Thanks Again, Steve Joel Burton wrote: > > Will DateStamp being the date of insertion? If so, is it that you want > the record for the most recent (largest) datestamp for each > batch_number? > > something like > > SELECT DISTINCT ON (batch_number) unique_key, datestamp, > batch_number, instructions FROM widgets ORDER BY batch_number, > datestamp desc; > > (sort by batch then by date (last first) and show the first (aka > 'distinct') row, considering only the batch_number for distinctness)
Re: [SQL] How to represent a tree-structure in a relational database
On Wed, Dec 13, 2000 at 11:04:13AM -0800, Josh Berkus wrote: > Frank, > > Please look in the list archives. About 2 months ago this topic came > up and was discussed extensively (including a creative solution by yours > truly). Hm, neither my archives nor a search on the postgresql.org page turned up the thread you mention. Do you recall which list it was and what the title of the thread was? Thanks, Frank
Re: [SQL] How to represent a tree-structure in a relational database
Frank Joerdens wrote: > > On Wed, Dec 13, 2000 at 11:04:13AM -0800, Josh Berkus wrote: > > Frank, > > > > Please look in the list archives. About 2 months ago this topic came > > up and was discussed extensively (including a creative solution by yours > > truly). > > Hm, neither my archives nor a search on the postgresql.org page turned > up the thread you mention. Do you recall which list it was and what the > title of the thread was? > > Thanks, Frank yes i recall!! i managed to implement something to that effect a lot was done using postgres and perl anyone need code fragements?
[SQL] Null comparison
I am migrating to postgress from msql and am encountering numerous problems in the differences in NULL usage and comparison. 1. Why are 2 fields not equal if they are both NULL? 2. Is there a way to easily modify my sql select statements to account for either column being NULL and having them return true if they both are NULL? select a.name, b.cost from a, b where a.type=b.type I'd like to make this as easy as possible so I can put it into a "translation" function. Currently I have a regsub that handles <> and NULLs, since <> doesn't work on a NULL field. thanks, Al pls cc me on your response.
[SQL] Query by sresultset.getArray(index)
I wanted to get array data from Java ResultSet object but error was reported: This method is not yet implemented. at org.postgresql.Driver.notImplemented(Driver.java:368) at org.postgresql.jdbc2.ResultSet.getArray(ResultSet.java:836) at PersistentObject.populateDomainModel(PersistentObject.java:356) at PersistentObject.query(PersistentObject.java:286) at TestFormatter.main(TestFormatter.java:88) java.lang.NullPointerException at TestFormatter.main(TestFormatter.java:91) I am using postgresql 7.0.2. How to get array data ? Please help me. Thanks in advance. Frank
RE: [SQL] How to represent a tree-structure in a relational database
> The way I'd do it if I had to do it again: > Give each record a unique id, generated by the application. > Denote levels with extra letters. > > So: > >AA - Automotive transport > - Cars >AAAB - Motorcycles > > The structures has the added bonus of making it very easy to > determine all the > sub-categories of a category, no matter how deep the tree is > below the category > you're looking at. With the first approach it is not possible > to do this in a > single SQL query. You could do this with a function, I guess. The problem with this method is if you need to insert a category, or move a category. You'll need to re-id a bunch of categories, and bubble those changes out to every table that refers to this table. Stuart Statman Director of Software Development Slam Media, Inc. BEGIN:VCARD VERSION:2.1 N:Statman;Stuart FN:Stuart Statman ORG:Slam Media, Inc. TITLE:Director of Software Development TEL;WORK;VOICE:(206) 391-0187 ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;Slam Media, Inc.=0D=0A800 5th Ave. #101-296;Seattle;WA;98104;United States= of America LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Slam Media, Inc.=0D=0A800 5th Ave. #101-296=0D=0ASeattle, WA 98104=0D=0AUnit= ed States of America EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:2910T063546Z END:VCARD
[SQL] plpgsql
Hi, How can I declare an array in plpgsql?? when I use declare url text[10]; ERROR: parse error at or near "[" if I use _text; declare is OK, however, when I assgin a value after BEGIN url[i]:=whatever; get same ERROR, Is it possible to return an array from a plpgsql function?? Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
RE: [SQL] How to represent a tree-structure in a relational database
> What I am thinking now is that you would keep the index > in a separate index table linked with the primary > key in the articles table), which would have 6 or 7 fields > initially, and that you'd add columns with the alter table > command, if need be, to make the structure deeper. I would suggest, instead, to create a table that represents your hierarchy without adding columns. For example : create table Category ( CategoryID int4 not null primary key, ParentCategoryID int4 not null REFERENCES Category (CategoryID), CategoryName varchar(100) ); Add a CategoryID with an FK reference to this table, and your work is done. Then adding, inserting, removing, or moving layers in the hierarchy becomes quite simple. This also preserves hierarchical integrity, where subcategory a of subcategory b will also remain a subcategory of category c if subcategory b is a subcategory of subcategory c, where I'm not sure your model will preserve or guarantee that. (Does that sentence deserve a prize?) In general, if you know that you will need to periodically alter a table to add columns, you should come up with a different model that doesn't require adding columns. Stuart Statman Director of Software Development Slam Media, Inc. BEGIN:VCARD VERSION:2.1 N:Statman;Stuart FN:Stuart Statman ORG:Slam Media, Inc. TITLE:Director of Software Development TEL;WORK;VOICE:(206) 391-0187 ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;Slam Media, Inc.=0D=0A800 5th Ave. #101-296;Seattle;WA;98104;United States= of America LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Slam Media, Inc.=0D=0A800 5th Ave. #101-296=0D=0ASeattle, WA 98104=0D=0AUnit= ed States of America EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:2910T063546Z END:VCARD
Re: [SQL] How to represent a tree-structure in a relational database
Frank, etc: > > create table Category ( > > CategoryID int4 not null primary key, > > ParentCategoryID int4 not null REFERENCES Category (CategoryID), > > CategoryName varchar(100) > > ); That was it. I also gave an example of a UNION query that would display the whole category tree in ASCII format: I've done this before for one project. Here's what you do: CREATE TABLE sample_heirarchy ( unique_id SERIAL CONSTRAINT PRIMARY KEY, node_linkup INT4, node_level INT2, label VARCHAR(30) datawhatever ); Then you use the unique_id and node_linkup fields to create a heirarchy of data nodes, with an indefinite number of levels, where the node_linkup of each lower level equals the id of its parent record. For example: id linkup level label data 3 0 1 Node1 Node1 4 3 2 Node1.1 Node1.1 6 3 2 Node1.2 Node1.2 7 6 3 Node1.2.1 Node1.2.1 5 0 1 Node2 Node2 etc. You can then access the whole heirarchy through moderately complex, but very fast-executing UNION queries. The one drawback is that you need to know in advance the maximum number of levels (3 in this example), but I'm sure someone on this list can find a way around that: SELECT n1.unique_id, n1.label, n1.data, n1.node_level, n1.unique_id AS level1, 0 AS level2, 0 AS level3 FROM sample_heirarchy n1 WHERE n1.node_level = 1 UNION ALL SELECT n2.unique_id, n2.label, n2.data, n2.node_level, n1.unique_id, n2.unique_id, 0 FROM sample_heirarchy n2, sample_heirarchy n1 WHERE n1.unique_id = n2.node_linkup AND n2.node_level = 2 UNION ALL SELECT n3.unique_id, n3.label, n3.data, n3.node_level, n1.unique_id, n2.unique_id, n3.unique_id FROM sample_heirarchy n1, sample_heirarchy n2, sample_heirarchy n3 WHERE n1.unique_id = n2.node_linkup AND n2.unique_id = n3.node_linkup AND n3.node_level = 3 ORDER BY level1, level2, level3 Should produce this output (pardon any parsing errors; I'm not at a PGSQL terminal right now): unique_id label datalevel level1 level2 level3 3 Node1 Node1 1 3 0 0 4 Node1.1 Node1.1 2 3 4 0 6 Node1.2 Node1.2 2 3 6 0 7 Node1.2.1 Node1.2.1 3 3 6 7 5 Node2 Node2 1 7 0 0 etc. This sorts them in numerical (id) order, but one could just as easily substitute the labels or data for the various levels and sort them alphabetically (although you do need to allow for NULL sort order on your database, and any label duplicates). The advantages of this structure are: 1. It allows you to create, assign, and re-assign nodes freely all over the heirarchy ... just change the level and/or linkup. 2. Aside from the Union query above, the table structure allows for any number of levels, unlike a set or relationally linked tables. 3. Because the display query is entirely once table linking to itself on (hopefully) indexed fields, in my expreience it runs very, very fast. 4. My PHP developer has reprogrammed the easily available PHP Tree Control to uses this table structure (I don't know if he's giving it out, but he said it wasn't very difficult). -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Null comparison
On Wed, 13 Dec 2000, Al Lewis wrote: > I am migrating to postgress from msql and am encountering numerous problems > in the differences in NULL usage and comparison. > > 1. Why are 2 fields not equal if they are both NULL? Because that's what the SQL spec says. If either value is NULL the result is unknown (because NULL is not a value really, is this unknown value equal to some other unknown value...). > 2. Is there a way to easily modify my sql select statements to account for > either column being NULL and having them return true if they both are NULL? > select a.name, b.cost from a, b where a.type=b.type where a.type=b.type or (a.type is null and b.type is null) should do it. > I'd like to make this as easy as possible so I can put it into a > "translation" function. Currently I have a regsub that handles <> and > NULLs, since <> doesn't work on a NULL field.
[SQL] postgres
Hi, I in the search for a DB that would work with our product and have been told to have a look at postgres. Would you be able to foward me any information on your product and or point me to where I might be able to find some. Thank you in advance for your help! Marc Get your own FREE, personal Netscape WebMail account today at http://home.netscape.com/webmail
RE: [SQL] How to represent a tree-structure in a relational database
[Josh Berkus] > I've done this before for one project. Here's what you do: > > CREATE TABLE sample_heirarchy ( > unique_id SERIAL CONSTRAINT PRIMARY KEY, > node_linkup INT4, > node_level INT2, > label VARCHAR(30) > datawhatever > ); > > Then you use the unique_id and node_linkup fields to create a heirarchy > of data nodes, with an indefinite number of levels, where the > node_linkup of each lower level equals the id of its parent record. For > example: > > id linkup level label data > 3 0 1 Node1 Node1 > 4 3 2 Node1.1 Node1.1 > 6 3 2 Node1.2 Node1.2 > 7 6 3 Node1.2.1 Node1.2.1 > 5 0 1 Node2 Node2 I don't think I'd be comfortable with having the node_level column in the table structure. First, because you can derive that value using a function, it's duplicate data. Second, if you decide to take an entire segment of your hierarchy and move it under another node (by changing the value of node_linkup/ParentCategoryID), you'll need to recalculate all of those node_level values. And all the node_level values underneath it. > You can then access the whole heirarchy through moderately complex, but > very fast-executing UNION queries. The one drawback is that you need to > know in advance the maximum number of levels (3 in this example), but > I'm sure someone on this list can find a way around that: I can think of another way to do this, though it would be a little complex and would involve temp tables. Select all of your top level nodes into a temp table. Create a new table with a new column for the new level. Select the children of the top level nodes into the temp table, followed by those top level nodes themselves, with a 0 in the new column and a flag indicating not to expand again. Create a new temp table just like the last but with another column for the new level, and repeat the above process from the first temp table to the second, only expanding the latest children, but copying all records over. Keep doing it until there are no more new children. Alternately, if you didn't need each level to have it's own column, but didn't mind an x.x.x.x kind of notation, you could use one temp table, and just append '.0' to the end of every copied-over parent node. Basically, both methods are simulations of recursing the tree, but you get to do each level all at once using an insert ... select. If you wanted, you could even use a counter, to identify which level each node appeared in. Clearly, this could also be done with cursors and recursive > 4. My PHP developer has reprogrammed the easily available PHP Tree > Control to uses this table structure (I don't know if he's giving it > out, but he said it wasn't very difficult). We've done a similar thing for Java. It was ridiculously easy to create a TreeModel wrapped around this data. Almost too easy; it made me feel dirty. Stuart Statman Director of Software Development Slam Media, Inc. BEGIN:VCARD VERSION:2.1 N:Statman;Stuart FN:Stuart Statman ORG:Slam Media, Inc. TITLE:Director of Software Development TEL;WORK;VOICE:(206) 391-0187 ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;Slam Media, Inc.=0D=0A800 5th Ave. #101-296;Seattle;WA;98104;United States= of America LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Slam Media, Inc.=0D=0A800 5th Ave. #101-296=0D=0ASeattle, WA 98104=0D=0AUnit= ed States of America EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:2910T063546Z END:VCARD
Re: [SQL] postgres
Mr. Daoust, You have reached the PostgreSQL SQL developers mailing list. We are not PostgreSQL sales people, and we have no marketing information to sell you. Please have a clue. I suggest that you try http://www.postgresql.org/ and http://www.pgsql.com/ for more information. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] postgres
On 13 Dec 2000, Marc Daoust wrote: > I in the search for a DB that would work with our product and have been told > to have a look at postgres. Would you be able to foward me any information on > your product and or point me to where I might be able to find some. You should start with www.postgresql.org -- Brett http://www.chapelperilous.net/~bmccoy/ --- So, is the glass half empty, half full, or just twice as large as it needs to be?
Re: [SQL] How to represent a tree-structure in a relational database
Stuart, > I don't think I'd be comfortable with having the node_level column in the > table structure. First, because you can derive that value using a function, > it's duplicate data. Second, if you decide to take an entire segment of your > hierarchy and move it under another node (by changing the value of > node_linkup/ParentCategoryID), you'll need to recalculate all of those > node_level values. And all the node_level values underneath it. I can see that. I suppose it depends on the data you're storing. The project I was working on tracked grocery inventory for a delivery service, and thus each item had a fixed "level" in the heirarcy (Food Class, Food Type, Manufacturer, and Item) and thus while items might get reassigned *across* the heirarcy, they did not get re-assigned *up and down* the heirarcy. Also, I can't think of a way to represent the tree in pure SQL without having the level identifiers (and a fixed number of levels). > We've done a similar thing for Java. It was ridiculously easy to create a > TreeModel wrapped around this data. Almost too easy; it made me feel dirty. Great. Maybe I'll buy it from you if I ever need to use Java :-) -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] How to represent a tree-structure in a relational database
On Wed, Dec 13, 2000 at 12:09:06PM -0800, Stuart Statman allegedly wrote: > > The way I'd do it if I had to do it again: > > Give each record a unique id, generated by the application. > > Denote levels with extra letters. > > > > So: > > > >AA - Automotive transport > > - Cars > >AAAB - Motorcycles > > > > The structures has the added bonus of making it very easy to > > determine all the > > sub-categories of a category, no matter how deep the tree is > > below the category > > you're looking at. With the first approach it is not possible > > to do this in a > > single SQL query. You could do this with a function, I guess. > > The problem with this method is if you need to insert a category, or move a > category. You'll need to re-id a bunch of categories, and bubble those > changes out to every table that refers to this table. You can solve the last problem by using an extra table that maps unique record id's (numerical) to hierarchical id's (text). Inserting a category, in my case, does not require me to start updating lots of records, since I would only use these strings to store hierarchical information. I'm sorting categories based on alphabet, which you can overrule by increasing the 'weight' of a category, which is a numerical value attached to every category and which normally has a vaue of 1. However, changing the level of a category would require me to modify all categories below that. In my case, this wouldn't be a problem. We're using this stuff for a Yahoo style directory which (atm) has about 2500 different categories. I'm generating a complete tree of all categories and the websites in them once a day, storing them in a souped up DBM style database. For each record I store the children, not the parent. If changing the underlying structure takes a couple of minutes, than this is acceptable. As you can see my number of categories is rather small. If you're going to use this for a forum or something similar, you may run into problems. However, how often do you want to move a thread... Cheers, Mathijs -- "Where is human nature so weak as in a bookstore!" Henry Ward Beecher (1813-1887)
Re: [SQL] How to represent a tree-structure in a relational database
On Wed, Dec 13, 2000 at 04:49:51PM -0800, Josh Berkus allegedly wrote: > Stuart, > > > I don't think I'd be comfortable with having the node_level column in the > > table structure. First, because you can derive that value using a function, > > it's duplicate data. Second, if you decide to take an entire segment of your > > hierarchy and move it under another node (by changing the value of > > node_linkup/ParentCategoryID), you'll need to recalculate all of those > > node_level values. And all the node_level values underneath it. > > I can see that. I suppose it depends on the data you're storing. The > project I was working on tracked grocery inventory for a delivery > service, and thus each item had a fixed "level" in the heirarcy (Food > Class, Food Type, Manufacturer, and Item) and thus while items might get > reassigned *across* the heirarcy, they did not get re-assigned *up and > down* the heirarcy. Indeed. If the structure 'rarely' changes, having to do an expensive update may be acceptable, if it increase the overall performance significantly. > Also, I can't think of a way to represent the tree in pure SQL without > having the level identifiers (and a fixed number of levels). Storing only the parent for a record doesn't require you to keep track of levels, since this information can be reconstructed by following the chain of parent id's until you reach the top of your tree. Storing the children for each record (like I'm doing) works exactly the same. Just follow the 'path' (for instance 'Automotive Transport/Cars') to find the category you're looking for. Cheers, Mathijs -- "A book is a fragile creature. It suffers the wear of time, it fears rodents, the elements, clumsy hands." Umberto Eco
RE: [SQL] How to represent a tree-structure in a relational database
Don't if this will help but there is a really good book that discuss this problem in details. The book is called "SQL for Smarties" by Joe Celko. It covers lots of advance topics (tree being one of them). Very good book. Check out on Amazon: http://www.amazon.com/exec/obidos/ASIN/1558605762/qid=976755796/sr=1-1/106-0241434-0557209 Just me $0.02 > -Original Message- > From: [EMAIL PROTECTED] > [mailto: Behalf Of Josh Berkus > Sent: Thursday, December 14, 2000 10:50 AM > To: sqllist > Subject: Re: [SQL] How to represent a tree-structure in a relational > database > > > Stuart, > > > I don't think I'd be comfortable with having the node_level column in the > > table structure. First, because you can derive that value using a function, > > it's duplicate data. Second, if you decide to take an entire segment of your > > hierarchy and move it under another node (by changing the value of > > node_linkup/ParentCategoryID), you'll need to recalculate all of those > > node_level values. And all the node_level values underneath it. > > I can see that. I suppose it depends on the data you're storing. The > project I was working on tracked grocery inventory for a delivery > service, and thus each item had a fixed "level" in the heirarcy (Food > Class, Food Type, Manufacturer, and Item) and thus while items might get > reassigned *across* the heirarcy, they did not get re-assigned *up and > down* the heirarcy. > > Also, I can't think of a way to represent the tree in pure SQL without > having the level identifiers (and a fixed number of levels). > > > We've done a similar thing for Java. It was ridiculously easy to create a > > TreeModel wrapped around this data. Almost too easy; it made me feel dirty. > > Great. Maybe I'll buy it from you if I ever need to use Java :-) > > -Josh > > -- > __AGLIO DATABASE SOLUTIONS___ > Josh Berkus >Complete information technology [EMAIL PROTECTED] > and data management solutions (415) 436-9166 >for law firms, small businesses fax 436-0137 > and non-profit organizations. pager 338-4078 > San Francisco
Re: [SQL] postgres
"Brett W. McCoy" wrote: > > On 13 Dec 2000, Marc Daoust wrote: > > > I in the search for a DB that would work with our product and have been told > > to have a look at postgres. Would you be able to foward me any information on > > your product and or point me to where I might be able to find some. > > You should start with www.postgresql.org > I've been wondering for a long time how people manage to find the mailing list without finding the web site. On the blackdown mailing list (blackdown.org ports the jdk to linux) people ask where they can get a jdk for linux. How did they find the list without knowing about blackdown? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [SQL] How to represent a tree-structure in a relational database
Mathijs Brands wrote: > > On Wed, Dec 13, 2000 at 04:49:51PM -0800, Josh Berkus allegedly wrote: > > Stuart, > > > > > I don't think I'd be comfortable with having the node_level column in the > > > table structure. First, because you can derive that value using a function, > > > it's duplicate data. Second, if you decide to take an entire segment of your > > > hierarchy and move it under another node (by changing the value of > > > node_linkup/ParentCategoryID), you'll need to recalculate all of those > > > node_level values. And all the node_level values underneath it. > > > > I can see that. I suppose it depends on the data you're storing. The > > project I was working on tracked grocery inventory for a delivery > > service, and thus each item had a fixed "level" in the heirarcy (Food > > Class, Food Type, Manufacturer, and Item) and thus while items might get > > reassigned *across* the heirarcy, they did not get re-assigned *up and > > down* the heirarcy. > > Indeed. If the structure 'rarely' changes, having to do an expensive > update may be acceptable, if it increase the overall performance > significantly. > > > Also, I can't think of a way to represent the tree in pure SQL without > > having the level identifiers (and a fixed number of levels). > > Storing only the parent for a record doesn't require you to keep track > of levels, since this information can be reconstructed by following the > chain of parent id's until you reach the top of your tree. > > Storing the children for each record (like I'm doing) works exactly the > same. Just follow the 'path' (for instance 'Automotive Transport/Cars') > to find the category you're looking for. > > Cheers, > > Mathijs > -- > "A book is a fragile creature. It suffers the wear of time, > it fears rodents, the elements, clumsy hands." > Umberto Eco this is the way i implemented too! i used perl and modperl/apache to deploy so i did use a perl module to store the top level of categories so they where always avail to the starting page mostly it ended up looking like the bidders edge horizontal line type thing again if anyone needs the code lemme know!
Re: [SQL] postgres
Joseph Shraibman writes: > I've been wondering for a long time how people manage to find the mailing > list without finding the web site. They do a Web search on 'postgres' and get a zillion hits on articles in the list archive. They then look at the first article and pull the address out of that. They never notice where the article came from. -- John Hasler [EMAIL PROTECTED] Dancing Horse Hill Elmwood, Wisconsin
Re: [SQL] Strange slow behavior in backend
Kyle <[EMAIL PROTECTED]> writes:
> This worked great until I put a real big file in (about 5M). Then, when
> I tried to fetch the file, it seemed really slow (about 60 seconds). I
> tried reassembling the file in the frontend instead and my time dropped
> to about 6 seconds using this TCL fragment (mpg::qlist is an interface
> to pg_exec that returns a list of tuples):
> The only difference I can identify is whether the re-assembly TCL code
> is running as a procedural language (backend) or in the frontend.
> Anyone have any idea why the difference is so dramatic?
I happened to have handy a 7.1 backend compiled for profiling, so I
looked into this a little. I confirm that this seems unreasonably slow.
As near as I can tell, 98% of the backend runtime is being spent in
strlen() and strcpy() invoked from Tcl_SetResult invoked from Tcl_Eval
invoked from the per-result-tuple loop in pltcl_SPI_exec. Apparently,
all this is happening because Tcl_Eval thinks it needs to make the
result of the append command available for its caller. I changed the
inner loop to
spi_exec -array d "select data from pg_largeobject where
loid = $1 order by pageno" {
append odata $d(data);
set z z
}
and voila, the runtime dropped to something reasonable.
So, yes, it would seem that some care in the inner loop of
pltcl_SPI_exec would help a lot. It'd be worth if'defing the Tcl_Eval
call there to use a new-style call when using Tcl 8. (This could also
avoid repetitive parsing of the loop body.) Might want to think about
the same for the Tcl function as a whole, too.
I was also distressed to notice that pltcl_set_tuple_values does a LOT
of repetitive work --- it should be fixed so that the syscache and
function lookups are done only once, not once per tuple.
regards, tom lane
Re: [SQL] Null comparison
I wrote some notes on three-valued logic (true,false,unknown) at my website: http://www.comptechnews.com/~reaster/dbdesign.html#three-valued-logic If anyone finds errors with it, please let me know. On Wednesday 13 December 2000 19:03, Stephan Szabo wrote: > On Wed, 13 Dec 2000, Al Lewis wrote: > > I am migrating to postgress from msql and am encountering numerous > > problems in the differences in NULL usage and comparison. > > > > 1. Why are 2 fields not equal if they are both NULL? > > Because that's what the SQL spec says. If either value is NULL > the result is unknown (because NULL is not a value really, > is this unknown value equal to some other unknown value...). > > > 2. Is there a way to easily modify my sql select statements to account > > for either column being NULL and having them return true if they both are > > NULL? select a.name, b.cost from a, b where a.type=b.type > > where a.type=b.type or (a.type is null and b.type is null) > should do it. > > > I'd like to make this as easy as possible so I can put it into a > > "translation" function. Currently I have a regsub that handles <> and > > NULLs, since <> doesn't work on a NULL field. -- Robert B. Easter [EMAIL PROTECTED] - - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - -- http://www.comptechnews.com/~reaster/
Re: [SQL] How to represent a tree-structure in a relational database
On Wednesday 13 December 2000 18:05, Josh Berkus wrote: > Frank, etc: > > > create table Category ( > > > CategoryID int4 not null primary key, > > > ParentCategoryID int4 not null REFERENCES Category (CategoryID), > > > CategoryName varchar(100) > > > ); I made a message board with a hierarchy for topics/boards under which messages go into a post/reply hierarchy at www.comptechnews.com. I used the parent_id idea like in the table above. It's working OK and, yes, I can easily move nodes around without problems. I can move/reparent Topic/boards and posts/replies (posts and replies are treated nearly the same, just that a post has a parent-post-id of 0 while a reply message's parent-post-id is non-zero). This arrangement combined with the use of PL/pgSQL trigger functions can go a long way. I did not use any foreign keys but just had the PL/pgSQL triggers do any checks I wanted myself. So, its easy to make triggers that do things like automatically delete an entire hierarchy when a node(message or topic) is deleted. Like, if you delete a post, it deletes all its replies automatically. If you delete a reply, it deletes any child/replies that it might have etc. If you delete a topic/board, it deletes all messages that were in that topic and any subtopics and messages in those subtopics recursively. The PL/pgSQL can be used to RAISE EXCEPTION when something you don't want to happen is attempted (like deleting the root topic!), which then automatically ABORTs the transaction. You will want to make heavy use of procedures in the database to ensure integrity. You might be able to use FOREIGN KEY triggers also but with careful use of BEFORE and AFTER PL/pgSQL triggers. In my case, I had somekind of conflict between what my PL/pgSQL triggers where doing and what the foreign key triggers were doing. The stuff my PL/pgSQL were doing caused referential integrity violations sometimes. You have to be real careful what goes into a BEFORE trigger and what goes into an AFTER trigger. Trying to make a BEFORE trigger or an AFTER trigger do too much will end up in trouble so you have to split what you want to do into two triggers for a table. The CONSTRAINT TRIGGERs that get created automatically by FOREIGN KEYs are called AFTER INSERT OR UPDATE|DELETE and are NOT DEFERRABLE and INITIALLY IMMEDIATE. You have to keep that in mind in writing your procedures. In the end, I judged the contraint triggers to interfere with what I wanted to do and removed them. > > That was it. I also gave an example of a UNION query that would display > the whole category tree in ASCII format: > > I've done this before for one project. Here's what you do: > > CREATE TABLE sample_heirarchy ( > unique_id SERIAL CONSTRAINT PRIMARY KEY, > node_linkup INT4, > node_level INT2, > label VARCHAR(30) > datawhatever > ); > > Then you use the unique_id and node_linkup fields to create a heirarchy > of data nodes, with an indefinite number of levels, where the > node_linkup of each lower level equals the id of its parent record. For > example: > > id linkup level label data > 3 0 1 Node1 Node1 > 4 3 2 Node1.1 Node1.1 > 6 3 2 Node1.2 Node1.2 > 7 6 3 Node1.2.1 Node1.2.1 > 5 0 1 Node2 Node2 > > etc. > > You can then access the whole heirarchy through moderately complex, but > very fast-executing UNION queries. The one drawback is that you need to > know in advance the maximum number of levels (3 in this example), but > I'm sure someone on this list can find a way around that: > > SELECT n1.unique_id, n1.label, n1.data, n1.node_level, n1.unique_id AS > level1, > 0 AS level2, 0 AS level3 > FROM sample_heirarchy n1 > WHERE n1.node_level = 1 > UNION ALL > SELECT n2.unique_id, n2.label, n2.data, n2.node_level, n1.unique_id, > n2.unique_id, 0 > FROM sample_heirarchy n2, sample_heirarchy n1 > WHERE n1.unique_id = n2.node_linkup > AND n2.node_level = 2 > UNION ALL > SELECT n3.unique_id, n3.label, n3.data, n3.node_level, n1.unique_id, > n2.unique_id, n3.unique_id > FROM sample_heirarchy n1, sample_heirarchy n2, sample_heirarchy n3 > WHERE n1.unique_id = n2.node_linkup AND > n2.unique_id = n3.node_linkup > AND n3.node_level = 3 > ORDER BY level1, level2, level3 > > Should produce this output (pardon any parsing errors; I'm not at a > PGSQL terminal right now): > > unique_id label datalevel level1 level2 level3 > 3 Node1 Node1 1 3 0 0 > 4 Node1.1 Node1.1 2 3 4 0 > 6 Node1.2 Node1.2 2 3 6 0 > 7 Node1.2.1 Node1.2.1 3
[SQL] How to insert/select date fields in a particular format
Hello everyone, Please help me in knowing how to insert/select date fields in a particular format in postgres. Thanks in advance Ramesh
Re: [SQL] postgres
On Wed, Dec 13, 2000 at 04:44:55PM -0800, Josh Berkus wrote: > Mr. Daoust, > > You have reached the PostgreSQL SQL developers mailing list. We are > not PostgreSQL sales people, and we have no marketing information to > sell you. Please have a clue. Errr... forgive me, but maybe we could be help the PostgreSQL team by kindly directing people to their corporate website, so they can purchase services that are going to fund the project. As long as the project is happy I am happy. Just some thoughts. -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
