Re: PROBLEM SOLVED RE: [SQL] please help with converting a view in oracle into postgresql readably code
On Tue, Oct 01, 2002 at 11:55:14PM -0700, mgeddert wrote: > Robert, > > Thanks for the help, I kept on playing with what you gave me, and after > removing one () pair and adding the ENDs to the CASE WHENs it works! > Thank you so much for the help, I have been very frustrated with this > for a number of days now. Argh, that's right, I forgot the ENDs. Ugh! Shame on me. Glad that's working for you now. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Q: Heard about the who couldn't spell? A: He spent the night in a warehouse. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] indexing on char vs varchar
Beth, > SorryI don't understand. The length is at the front of what? In some RDBMSs, the VARCHAR data type has a 2 or 4-byte indicator of the length of the stored string before the data itself, while CHAR does not require this information because it is fixed-length. This makes the CHAR datatype marginally smaller, and thus faster, than the VARCHAR data type on those databases. This difference goes back to much older databases and computers, where every byte of a row counted in terms of performance. In my experience, even though MS SQL Server still functions this way, the performance difference between CHAR and VARCHAR is not measurable unless you are getting close to the 8K data page limit that MSSQL imposes. YMMV. Postgres does not materially differentiate between CHAR, VARCHAR, and TEXT, except that CHAR is padded by spaces and VARCHAR often has a length limit. However, in terms of storage efficiency (and indexing efficiency), they are identical. In Postgres, the character count is included in all string data types. Thus, you should use the data type most appropriate to the data you are storing, ignoring performance issues. If the data is a fixed-length string (such as a required zip code) use CHAR; if it's variable but limited, use varchar; if it's a long description, use TEXT. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Stored Procedures
Ok, if this does not apply to versions prior to 7.3beta then what do I need to do if I am running 7.2.1? When I try to use the SETOF to retrun a row set, I only get one column. Do I need to update Postgres to get things to work? Ben > david williams wrote: > > Also, > > > > the table definition MUST be in the Public Schema. I use my own schema > > names but in order for the table to be found by the function it ( the > > table ) must be in the public schema. Although it can be empty. > > (Note: > this discussion does not apply to PostgreSQL releases prior to 7.3 beta) > > Not true. You need to be sure the schema the table is in is in your search > path, or you need to fully qualify the table reference. See below for an > example: > > -- create a new schema > CREATE SCHEMA s1; > CREATE SCHEMA > -- change to the new schema > SET search_path='s1','$user','public'; > SET > select current_schema(); > current_schema > > s1 > (1 row) > > -- create the table > CREATE TABLE foo (fooid int, foosubid int, fooname text); > CREATE TABLE > INSERT INTO foo VALUES(1,1,'Joe'); > INSERT 794076 1 > -- change back to public schema, but leave s1 in the search path > SET search_path='$user','public','s1'; > SET > select current_schema(); > current_schema > > public > (1 row) > > \dt > List of relations > Schema | Name | Type | Owner > +--+---+-- > s1 | foo | table | postgres > (1 row) > > CREATE FUNCTION getfoo(int) RETURNS foo AS ' >SELECT * FROM foo WHERE fooid = $1; > ' LANGUAGE SQL; > CREATE FUNCTION > \df getfoo > List of functions > Result data type | Schema | Name | Argument data types > --+++- > foo | public | getfoo | integer > (1 row) > > -- this will work > SELECT *, upper(fooname) FROM getfoo(1) AS t1; > fooid | foosubid | fooname | upper > ---+--+-+--- > 1 |1 | Joe | JOE > (1 row) > > -- now try again with table name qualified in the function > DROP FUNCTION getfoo(int); > DROP FUNCTION > -- remove s1 from the search path > SET search_path='$user','public'; > SET > select current_schema(); > current_schema > > public > (1 row) > > \dt > No relations found. > CREATE FUNCTION getfoo(int) RETURNS s1.foo AS ' >SELECT * FROM s1.foo WHERE fooid = $1; > ' LANGUAGE SQL; > CREATE FUNCTION > \df getfoo > List of functions > Result data type | Schema | Name | Argument data types > --+++- > s1.foo | public | getfoo | integer > (1 row) > > -- this will work > SELECT *, upper(fooname) FROM getfoo(1) AS t1; > fooid | foosubid | fooname | upper > ---+--+-+--- > 1 |1 | Joe | JOE > (1 row) > > 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: [SQL] split function for pl/pgsql
Frederic Logier wrote: > Great ! have you some example for call a pl/perl function from a > pl/pgsql function ? I don't use PL/Perl, but I think you should be able to do: SELECT INTO var my_plperl_func(text_to_split); from within your PL/pgSQL function. > And could i use an int array in pl/pgsql returned by the pl/perl > function ? I don't know if it is possible to construct a PostgreSQL array in pl/perl, but I would imagine that should work. Any pl/perl users out there? Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Stored Procedures
Stored procedures returning more than one row up through odbc does not work in 7.2.1 To return more than one column you must spec is column in the returns area of the function. Dave - Original Message - From: [EMAIL PROTECTED] Sent: Wednesday, October 02, 2002 1:53 PM To: Joe Conway Cc: david williams; [EMAIL PROTECTED] Subject: Re: [SQL] Stored Procedures Ok, if this does not apply to versions prior to 7.3beta then what do I need to do if I am running 7.2.1? When I try to use the SETOF to retrun a row set, I only get one column.Do I need to update Postgres to get things to work?Ben> david williams wrote:> > Also,> > > > the table definition MUST be in the Public Schema. I use my own schema > > names but in order for the table to be found by the function it ( the > > table ) must be in the public schema. Although it can be empty.> > (Note:> this discussion does not apply to PostgreSQL releases prior to 7.3 beta)> > Not true. You need to be sure the schema the table is in is in your search > path, or you need to fully qualify the table reference. See below for an > example:> > -- create a new schema> CREATE SCHEMA s1;> CREATE SCHEMA> -- change to the new schema> SET search_path='s1','$user','public';> SET> select current_schema();> current_schema> > s1> (1 row)> > -- create the table> CREATE TABLE foo (fooid int, foosubid int, fooname text);> CREATE TABLE> INSERT INTO foo VALUES(1,1,'Joe');> INSERT 794076 1> -- change back to public schema, but leave s1 in the search path> SET search_path='$user','public','s1';> SET> select current_schema();> current_schema> > public> (1 row)> > \dt> List of relations> Schema | Name | Type | Owner> +--+---+--> s1 | foo | table | postgres> (1 row)> > CREATE FUNCTION getfoo(int) RETURNS foo AS '> SELECT * FROM foo WHERE fooid = $1;> ' LANGUAGE SQL;> CREATE FUNCTION> \df getfoo> List of functions> Result data type | Schema | Name | Argument data types> --+++-> foo | public | getfoo | integer> (1 row)> > -- this will work> SELECT *, upper(fooname) FROM getfoo(1) AS t1;> fooid | foosubid | fooname | upper> ---+--+-+---> 1 | 1 | Joe | JOE> (1 row)> > -- now try again with table name qualified in the function> DROP FUNCTION getfoo(int);> DROP FUNCTION> -- remove s1 from the search path> SET search_path='$user','public';> SET> select current_schema();> current_schema> > public> (1 row)> > \dt> No relations found.> CREATE FUNCTION getfoo(int) RETURNS s1.foo AS '> SELECT * FROM s1.foo WHERE fooid = $1;> ' LANGUAGE SQL;> CREATE FUNCTION> \df getfoo> List of functions> Result data type | Schema | Name | Argument data types> --+++-> s1.foo | public | getfoo | integer> (1 row)> > -- this will work> SELECT *, upper(fooname) FROM getfoo(1) AS t1;> fooid | foosubid | fooname | upper> ---+--+-+---> 1 | 1 | Joe | JOE> (1 row)> > HTH,> > Joe> Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com
Re: [SQL] indexing on char vs varchar
Thanks for the excellent description, Josh. And the reminder of the performance list (it's existence somehow slipped from my mind). -Beth > -Original Message- > From: Josh Berkus [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 02, 2002 10:56 AM > To: Beth Gatewood; 'Bruce Momjian' > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] indexing on char vs varchar > > > Beth, > > Oh, and you should take this sort of question to the new > performance list: > [EMAIL PROTECTED] > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Stored Procedures
[EMAIL PROTECTED] wrote: > Ok, if this does not apply to versions prior to 7.3beta > then what do I need to do if I am running 7.2.1? When I > try to use the SETOF to retrun a row set, I only get > one column. First, prior to 7.3 there is no SCHEMA support in Postgres. Everything lives in essentially one and the same schema. In 7.2.x and before, returning a composite type (i.e. multiple columns) gives you back one column of pointers (large integer values) to the actual row of data. You can access the individual columns, but it's ugly: test=# CREATE TABLE foo (fooid int, foosubid int, fooname text); CREATE test=# INSERT INTO foo VALUES(1,1,'Joe'); INSERT 304822 1 test=# CREATE FUNCTION getfoo(int) RETURNS foo AS ' test'# SELECT * FROM foo WHERE fooid = $1; test'# ' LANGUAGE SQL; CREATE test=# select fooid(getfoo(1)), foosubid(getfoo(1)), fooname(getfoo(1)); fooid | foosubid | fooname ---+--+- 1 |1 | Joe (1 row) 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
[SQL] schedule of v7.3
Bruce, What is the schedule for releasing v7.3 stable? Oct? Thanks. Jie Liang ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] celko nested set functions
I'm wondering if anyone has written code that enables you to move entities between parents in a nested set model. Specifically something that can do it without deleting any of the children involved in the process. I looked in the postgresql cookbook which had adding and building tree's, but not moving. I'm hoping to find something postgresql specific but if not that's ok. Thanks in advance, Robert Treat ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] schedule of v7.3
Jie Liang wrote: > Bruce, > What is the schedule for releasing v7.3 stable? Oct? We think late October or early November. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] celko nested set functions
Robert, > I'm wondering if anyone has written code that enables you to move > entities between parents in a nested set model. Specifically something > that can do it without deleting any of the children involved in the > process. I looked in the postgresql cookbook which had adding and > building tree's, but not moving. I'm hoping to find something > postgresql specific but if not that's ok. Thanks in advance, I've been planning on doing this as an article for a while. I'll send you an answer off-list because I don't want to publicize it until I can add some commentary. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] split function for pl/pgsql
hi,
i'm looking for a split function, like perl or php.
I need doing a pl/pgsql function with a split return an array.
I must do some loop with this array for mass inserting.
I think of doing it with pl / perl but I need to do insert and I don't
find example with pl / perl and sql.
Here my function (not tested of course) :
CREATE FUNCTION candidat_valid (varchar ,varchar ,varchar, varchar,
varchar, varchar, varchar, varchar, varchar, varchar, text, oid,
varchar, varchar, varchar) RETURNS boolean AS '
DECLARE
func_nomALIAS FOR $1;
func_prenom ALIAS FOR $2;
func_adresse1 ALIAS FOR $3;
func_adresse2 ALIAS FOR $4;
func_ville ALIAS FOR $5;
func_cp ALIAS FOR $6;
func_pays ALIAS FOR $7;
func_email ALIAS FOR $8;
func_telephone ALIAS FOR $9;
func_gsmALIAS FOR $10;
func_commentaireALIAS FOR $11;
func_cv ALIAS FOR $12;
func_nom_fichierALIAS FOR $13;
func_iddiplome ALIAS FOR $14;
func_idqualificationALIAS FOR $15;
new_id int4;
indice int;
tableau_diplome int[];
tableau_qualification int[];
BEGIN
new_id := output of "SELECT nextval('candidat_id_seq')";
IF (func_nom_fichier == NULL) THEN
INSERT INTO candidat (id, nom, prenom, adresse1, adresse2, ville, cp,
pays, email, telephone, gsm, commentaire) VALUES (new_id, func_nom,
func_prenom, func_adresse1, func_adresse2, func_ville, func_cp,
func_pays, func_email, func_telephone, func_gsm, func_commentaire);
ELSE
INSERT INTO candidat (id, nom, prenom, adresse1, adresse2, ville, cp,
pays, email, telephone, gsm, commentaire, cv, nom_fichier) VALUES
(new_id, func_nom, func_prenom, func_adresse1, func_adresse2,
func_ville, func_cp, func_pays, func_email, func_telephone, func_gsm,
func_commentaire, func_cv, func_nom_fichier);
END IF;
indice := 0;
tableau_diplome := split(',',func_iddiplome);
tableau_qualification := split(',',func_idqualification);
while (tableau_diplome[indice]) {
INSERT INTO candidat_diplome (id_candidat, id_diplome) VALUES
(new_id,tableau_diplome[indice]);
indice := indice + 1;
}
indice := 0;
while (tableau_qualification[indice]) {
INSERT INTO candidat_qualif (id_candidat, id_qualification)
VALUES (new_id,tableau_qualification[indice]);
indice := indice + 1;
}
RETURN TRUE;
END;
' LANGUAGE 'plpgsql';
--
AZ informatique
68 avenue Felix Faure 69003 Lyon
Tel : +33 (0) 472 844 949 direct : +33 (0) 472 844 956
Fax : 04 78 60 40 70
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Stored Procedures
http://developer.postgresql.org/docs/postgres/xfunc-sql.html#AEN30400 See section 9.2.4. SQL Table Functions - Original Message - From: [EMAIL PROTECTED] Sent: Tuesday, October 01, 2002 4:25 PM To: [EMAIL PROTECTED] Subject: [SQL] Stored Procedures Hi all. I'm looking for a little help here. I have a project where I have to write some stored proceedures and am having some problems. My main issue is, I cannot figure out how to return a record set containing multipule columns. I am looking for a few examples on how I can do this. Most of what I have to do is fairly simple SQL queries based on a pramater sent to the function. I tried to use the SETOF option, but only get back one column.Any help will be would be greatly appricated. Simple examples would be of a great help.Thanks,Ben---(end of broadcast)---TIP 6: Have you searched our list archives?http://archives.postgresql.orgGet more from the Web. FREE MSN Explorer download : http://explorer.msn.com
Re: [SQL] Stored Procedures
Also, the table definition MUST be in the Public Schema. I use my own schema names but in order for the table to be found by the function it ( the table ) must be in the public schema. Although it can be empty. DaveGet more from the Web. FREE MSN Explorer download : http://explorer.msn.com
Re: [SQL] split function for pl/pgsql
Frederic Logier wrote:
> hi,
>
> i'm looking for a split function, like perl or php.
> I need doing a pl/pgsql function with a split return an array.
> I must do some loop with this array for mass inserting.
>
> I think of doing it with pl / perl but I need to do insert and I don't
> find example with pl / perl and sql.
There is no split function built in to PostgreSQL currently. You could write
it yourself in PL/Perl and use it in the PL/pgSQL function.
In 7.3 (currently in beta) there is a split_part() function which returns just
one element. I will most likely write a split function for 7.4 to return an
array, similar to perl and php. In 7.3, the following will do what you want (I
think):
CREATE OR REPLACE FUNCTION split(text, text)
RETURNS text[] AS '
DECLARE
i int := 0;
word text;
result text := ''{'';
result_arr text[];
BEGIN
LOOP
i := i + 1;
SELECT INTO word split_part($1, $2, i);
IF word = THEN
EXIT;
END IF;
IF i > 1 THEN
result := result || '',"'' || word || ''"'';
ELSE
result := result || ''"'' || word || ''"'';
END IF;
END LOOP;
result := result || ''}'';
result_arr := result;
RETURN result_arr;
END
' LANGUAGE 'plpgsql';
test=# select split('a,b,c',',');
split
-
{a,b,c}
(1 row)
test=# select a[1] from (select split('a,b,c',',') as a) as t;
a
---
a
(1 row)
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: [SQL] Stored Procedures
david williams wrote: > Also, > > the table definition MUST be in the Public Schema. I use my own schema > names but in order for the table to be found by the function it ( the > table ) must be in the public schema. Although it can be empty. (Note: this discussion does not apply to PostgreSQL releases prior to 7.3 beta) Not true. You need to be sure the schema the table is in is in your search path, or you need to fully qualify the table reference. See below for an example: -- create a new schema CREATE SCHEMA s1; CREATE SCHEMA -- change to the new schema SET search_path='s1','$user','public'; SET select current_schema(); current_schema s1 (1 row) -- create the table CREATE TABLE foo (fooid int, foosubid int, fooname text); CREATE TABLE INSERT INTO foo VALUES(1,1,'Joe'); INSERT 794076 1 -- change back to public schema, but leave s1 in the search path SET search_path='$user','public','s1'; SET select current_schema(); current_schema public (1 row) \dt List of relations Schema | Name | Type | Owner +--+---+-- s1 | foo | table | postgres (1 row) CREATE FUNCTION getfoo(int) RETURNS foo AS ' SELECT * FROM foo WHERE fooid = $1; ' LANGUAGE SQL; CREATE FUNCTION \df getfoo List of functions Result data type | Schema | Name | Argument data types --+++- foo | public | getfoo | integer (1 row) -- this will work SELECT *, upper(fooname) FROM getfoo(1) AS t1; fooid | foosubid | fooname | upper ---+--+-+--- 1 |1 | Joe | JOE (1 row) -- now try again with table name qualified in the function DROP FUNCTION getfoo(int); DROP FUNCTION -- remove s1 from the search path SET search_path='$user','public'; SET select current_schema(); current_schema public (1 row) \dt No relations found. CREATE FUNCTION getfoo(int) RETURNS s1.foo AS ' SELECT * FROM s1.foo WHERE fooid = $1; ' LANGUAGE SQL; CREATE FUNCTION \df getfoo List of functions Result data type | Schema | Name | Argument data types --+++- s1.foo | public | getfoo | integer (1 row) -- this will work SELECT *, upper(fooname) FROM getfoo(1) AS t1; fooid | foosubid | fooname | upper ---+--+-+--- 1 |1 | Joe | JOE (1 row) HTH, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] indexing on char vs varchar
Hi- This is more just trying to understand what is going on under the hood of pgsql. I have read through the archives that there is no difference between index on char, varchar or text. I am wondering why? I understand all the arguments about saving space but I am specifically asking about index performance and wondering about the underworkings of indices based on char and varchar. Othe RDBMS have clear leanings that indexing on chars are a better way to go. In MySQL this is due to a static table characteristics (http://www.mysql.com/doc/en/Static_format.html) and speed for an index look-up (row number X row length). and the ease to read a constant number of records with each disk. In the case of Oracle, the suggestion for char is based on if using varchar2 that takes 5 char and then there is a subsequent update to this field to now take 20 char, but now the record can not grow physically...so they essentially mark the old one as deleted and create a new record at the top (in an entirely new block) but the problem is that the index points to the deleted block...so the index has to query the old block and then the new(info from: http://groups.google.com/groups?q=oracle+char+vs+varchar+index&hl=en&lr=&ie= UTF-8&oe=UTF-8&selm=3a791aa3%40news.iprimus.com.au&rnum=1) Thanks for explaining this to me -Beth ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] split function for pl/pgsql
Le mer 02/10/2002 à 17:44, Joe Conway a écrit :
> There is no split function built in to PostgreSQL currently. You could write
> it yourself in PL/Perl and use it in the PL/pgSQL function.
Great ! have you some example for call a pl/perl function from a
pl/pgsql function ?
And could i use an int array in pl/pgsql returned by the pl/perl
function ?
> In 7.3 (currently in beta) there is a split_part() function which returns just
> one element. I will most likely write a split function for 7.4 to return an
> array, similar to perl and php. In 7.3, the following will do what you want (I
> think):
Thanks for your function but i can't use a beta version of postgresql in
production :(
maybe i should use a int array like this example :
create function foo(_int4) returns int2 as'
declare
a alias for $1;
i int:=1;
begin
while a[i] loop
i:=i+1;
end loop;
return i-1;
end;
' language 'plpgsql';
you can call it by:
select foo('{1232131,12312321,3424234}');
--
AZ informatique
68 avenue Felix Faure 69003 Lyon
Tel : +33 (0) 472 844 949 direct : +33 (0) 472 844 956
Fax : 04 78 60 40 70
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] indexing on char vs varchar
We store all the text/char/varchar types with the length at the front so we don't have such optimizations. We do have "char", in quotes, which is a single character, but that's about it. --- Beth Gatewood wrote: > Hi- > > This is more just trying to understand what is going on under the hood of > pgsql. I have read through the archives that there is no difference between > index on char, varchar or text. I am wondering why? I understand all the > arguments about saving space but I am specifically asking about index > performance and wondering about the underworkings of indices based on char > and varchar. > > Othe RDBMS have clear leanings that indexing on chars are a better way to > go. > > In MySQL this is due to a static table characteristics > (http://www.mysql.com/doc/en/Static_format.html) and speed for an index > look-up (row number X row length). and the ease to read a constant number of > records with each disk. > > In the case of Oracle, the suggestion for char is based on if using > varchar2 that takes 5 char and then there is a subsequent update to this > field to now take 20 char, but now the record can not grow physically...so > they essentially mark the old one as deleted and create a new record at the > top (in an entirely new block) but the problem is that the index points to > the deleted block...so the index has to query the old block and then the > new(info from: > http://groups.google.com/groups?q=oracle+char+vs+varchar+index&hl=en&lr=&ie= > UTF-8&oe=UTF-8&selm=3a791aa3%40news.iprimus.com.au&rnum=1) > > Thanks for explaining this to me > -Beth > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Updating from select
Hi all I have two related tables: CREATE TABLE trans_log ( id serial, datetimestamp not null, costnumeric(12,5) NULL ); CREATE TABLE products_log ( id serial, trans integer not null references trans_log(id), costnumeric(12,5) NOT NULL ); So, a transaction can have from zero to a lot of products (1:N) Ok. I have data in the tables. The transaction tables have their id and date, but no costs, so costs = 0.0 for all transactions. The products do have their cost set. I can do a SELECT trans, SUM(cost) FROM products_log GROUP BY trans; to get the total amount of each transactions. What I would like is to update the transactions table with the sum of its products cost, some kind of UPDATE trans_log t SET t.cost = (SELECT SUM(p.cost) FROM products_log p WHERE p.trans = t.id) But I'm not able. Thanks in advance Thrasher ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] indexing on char vs varchar
SorryI don't understand. The length is at the front of what? -Beth > -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 02, 2002 10:06 AM > To: Beth Gatewood > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] indexing on char vs varchar > > > > We store all the text/char/varchar types with the length at > the front so > we don't have such optimizations. We do have "char", in quotes, which > is a single character, but that's about it. > > -- > - > > Beth Gatewood wrote: > > Hi- > > > > This is more just trying to understand what is going on > under the hood of > > pgsql. I have read through the archives that there is no > difference between > > index on char, varchar or text. I am wondering why? I > understand all the > > arguments about saving space but I am specifically asking > about index > > performance and wondering about the underworkings of > indices based on char > > and varchar. > > > > Othe RDBMS have clear leanings that indexing on chars are a > better way to > > go. > > > > In MySQL this is due to a static table characteristics > > (http://www.mysql.com/doc/en/Static_format.html) and speed > for an index > > look-up (row number X row length). and the ease to read a > constant number of > > records with each disk. > > > > In the case of Oracle, the suggestion for char is based on if using > > varchar2 that takes 5 char and then there is a subsequent > update to this > > field to now take 20 char, but now the record can not grow > physically...so > > they essentially mark the old one as deleted and create a > new record at the > > top (in an entirely new block) but the problem is that the > index points to > > the deleted block...so the index has to query the old block > and then the > > new(info from: > > > http://groups.google.com/groups?q=oracle+char+vs+varchar+index &hl=en&lr=&ie= > UTF-8&oe=UTF-8&selm=3a791aa3%40news.iprimus.com.au&rnum=1) > > Thanks for explaining this to me > -Beth > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Updating from select
On Wed, 02 Oct 2002 19:11:19 +0200, Thrasher <[EMAIL PROTECTED]> wrote: >UPDATE trans_log t SET t.cost = > (SELECT SUM(p.cost) FROM products_log p WHERE p.trans = t.id) Thrasher, try it without the table alias t: UPDATE trans_log SET cost = (SELECT SUM(p.cost) FROM products_log p WHERE p.trans = trans_log.id) Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] indexing on char vs varchar
Beth, Oh, and you should take this sort of question to the new performance list: [EMAIL PROTECTED] -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
