[SQL] update from another table
dear sir, i would like to know how can i update a table with columns from another table and adding a new column with a secuence, i have try update table set column = (select column from table2), .., set column=secuence.. is it right? thanks ivan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Can I use "UPDATE" sql statement in trigger before or after update ?
Can I use "UPDATE" sql statement in trigger before or after update, without refire the trigger again ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Matching several rows
Hi,This is possibly absolutely trivial but I am lost...A table URIGHTS which stores an ID and the RIGHTs this ID has. One ID may have many rights and accordingly records in table, sample:ID RIGHT -20 120 220 520 1030 230 10Now I need to find out which IDs have, say rights 2 AND 5 AND 10.What would be the simplest query? Thanks!
Re: [SQL] Matching several rows
Thank you to everyone for the great help!I will evaluate all methods in our query (It is actually well complexer then this sample) and choose the best one.Is there any "scientific" name to this kind of "several rows match for one result" data selection? Ivan
[SQL] newbie question
Dear users.. I have fastly created a table in a postgresql database.. some columns where edited by hand (columns A, B, C), and some others (columns D, E, F) have been calculated as a result of mathematical equation (where the factors are the A, B, C columns) now I simply need to change some values in the A, B, C columns and I would like to obtain the correct values in the D, E, F column... I know that this is a tipical problem of a spreadsheet but how can I solve it with a DBMS?? there a way to impose some constrain, also after the table is already filled with values?? I need only some suggestions to start.. then I can go on by myself!! thank you very much Ivan -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] newbie question
Thanks to all... another question... is it possible to copy a table to a view and then back the view to a table??? thank you very much... ivan Il giorno ven, 03/03/2006 alle 11.51 +0100, Andreas Kretschmer ha scritto: > ivan marchesini <[EMAIL PROTECTED]> schrieb: > > > Dear users.. > > I have fastly created a table in a postgresql database.. > > some columns where edited by hand (columns A, B, C), and some others > > (columns D, E, F) have been calculated as a result of mathematical > > equation (where the factors are the A, B, C columns) > > You should create a table with (a,b,c) and a view. Below a example. > > test=# create table foo (a int, b int, c int); > CREATE TABLE > > test=# create view foo_view as (select a,b,c,a*b as ab, a*c as ac, b*c as bc > from foo); > CREATE VIEW > > > > now I simply need to change some values in the A, B, C columns and I > > would like to obtain the correct values in the D, E, F column... > > I know that this is a tipical problem of a spreadsheet but how can I > > solve it with a DBMS?? > > test=# insert into foo values (2,3,4); > INSERT 0 1 > test=# select * from foo_view ; > a | b | c | ab | ac | bc > ---+---+---+++ > 2 | 3 | 4 | 6 | 8 | 12 > (1 row) > > test=# update foo set a=3; > UPDATE 1 > test=# select * from foo_view ; > a | b | c | ab | ac | bc > ---+---+---+++ > 3 | 3 | 4 | 9 | 12 | 12 > (1 row) > > > HTH, Andreas -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] COPY tablename FROM and null values
Dear users, I'm working on a Postgres 7.4 server I have a .txt file, containing some tabular data, where data are delimited by TABs. there are 3 columns: column1 int4, column2 float8, column3 float8 the problem is that column3 contains also null values (i.e. sometimes is empty) so when I try to use COPY tablename FROM 'filename.txt' I obtain an error I have tried also using " WITH NULL AS ' ' " but without good results... can someone explain me how to solve this problem??? thank you very much Ivan -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] how to solve this problem
Dear users, I have this problem I have a table where there are 20 columns named vinc1, vinc2, vinc3, vinc4, etc the values contained into each column are simply 1 or 0 (each column is dichotomic) 1 means presence 0 means absence I would obtain a column (new_column) containg the name of the columns, comma separated, where the value is = 1 for example: vinc1 vinc2 vinc3 vinc4 new_column 1 0 1 0 vinc1,vinc3 0 0 0 1 vinc4 0 1 1 1 vinc2,vinc3,vinc4 can someone help me to find the best way to obtain this result??? thank you very much Ivan -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] how to solve this problem
Sorry, I'm not able to understand how I can use this solution!!! can you better explain me your suggestion??? Thanks very much Il giorno gio, 13/04/2006 alle 06.51 -0700, Richard Broersma Jr ha scritto: > Just a thought, > > Could you achieve that same result using the binary representation of an > integer? > > Regards, > > Richard > > --- ivan marchesini <[EMAIL PROTECTED]> wrote: > > > Dear users, > > I have this problem > > > > I have a table where there are 20 columns named > > vinc1, vinc2, vinc3, vinc4, etc > > > > the values contained into each column are simply 1 or 0 (each column is > > dichotomic) > > 1 means presence > > 0 means absence > > > > I would obtain a column (new_column) containg the name of the columns, > > comma separated, where the value is = 1 > > > > for example: > > > > vinc1 vinc2 vinc3 vinc4 new_column > > 1 0 1 0 vinc1,vinc3 > > 0 0 0 1 vinc4 > > 0 1 1 1 vinc2,vinc3,vinc4 > > > > can someone help me to find the best way to obtain this result??? > > thank you very much > > > > Ivan > > > > > > > > > > > > > > > > -- > > Ivan Marchesini > > Department of Civil and Environmental Engineering > > University of Perugia > > Via G. Duranti 93/a > > 06125 > > Perugia (Italy) > > e-mail: [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > tel: +39(0)755853760 > > fax: +39(0)755853756 > > jabber: [EMAIL PROTECTED] > > > > > > > > > > ---(end of broadcast)--- > > TIP 1: if posting/reading through Usenet, please send an appropriate > >subscribe-nomail command to [EMAIL PROTECTED] so that your > >message can get through to the mailing list cleanly > > > > -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] how to solve this problem
Thank you very much!!! your suggestion seems really usefull!! I will try it very soon!!! ivan Il giorno gio, 13/04/2006 alle 11.34 -0700, Steve Crawford ha scritto: > > I have a table where there are 20 columns named > > vinc1, vinc2, vinc3, vinc4, etc > > > > the values contained into each column are simply 1 or 0 (each column is > > dichotomic) > > 1 means presence > > 0 means absence > > > > I would obtain a column (new_column) containg the name of the columns, > > comma separated, where the value is = 1 > > > > for example: > > > > vinc1 vinc2 vinc3 vinc4 new_column > > 1 0 1 0 vinc1,vinc3 > > 0 0 0 1 vinc4 > > 0 1 1 1 vinc2,vinc3,vinc4 > > > > can someone help me to find the best way to obtain this result??? > > thank you very much > > Here's a brute-force method. Maybe someone else has a more elegant way. > More info on the nature of your data and what you are trying to obtain > from it would help in finding such elegance. > > select >substr( >case when vinc1 = 1 then ',vinc1' else '' || >case when vinc2 = 1 then ',vinc2' else '' || >... >case when vinc20 = 1 then ',vinc20' else '' >,2) as new_column ... > > As to the binary representation mentioned elsewhere, the idea is that > you can view vinc1-vinc20 as bits in a binary number equal to vinc1 + > 2*vinc2 + 4*vinc3 + 8*vinc4...2^19*vinc20. Whether or not this is useful > depends on what you are trying to do. > > Cheers, > Steve > -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] simple problem???
Dear users It's a simple problem I think (and I hope :-) I have a table like this: A|B|C 1|2|20 1|3|10 1|4|21 2|3|12 2|4|22 3|4|23 where the first two column are a combination without repetition (with k=2) of the numbers 1,2,3,4 for each pair I have a value in the column C. I would want a select that can extract these records: 1|3|10 2|3|12 3|4|23 i.e. the select must look into the A field first and: -select all records where A=1, -find, into this selection, the record where there's the minimum value of the field C -print all the fields for this record. then -select all records where A=2 -find, into this selection, the record where there's the minimum value of the field C -print all the fields of this record. and so on... using SELECT a,MIN(c) FROM table GROUP BY a is a partial solution because I can't see the value of B and I obtain: 1|10 2|12 3|23 How can I do for plotting also the value of B??? Thank you very much Ivan -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] 2 tables or two db?
Dear postgres users, Suppose I have two tables of data. suppose the two table are really similar in dimensions suppose they will receive the same number and type of queries. in tems of performance (velocity of answer) it is better to place the two tables in the same db or into two different db? may be it is a stupid question, but many many thanks... Ivan -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] .psql_history": No such file
Dear users, I have installed a postgres db using a datadir different from /var/lib/pgsql/.psql_history. then: su postgres psql postgres All went fine but when I exit from psql from a db I obtain: ___ could not save history to file "/var/lib/pgsql/.psql_history": No such file or directory ___ how can I fix this problem? where can I say psql that it must write .psql_history into the datadir? many thanks... ivan -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] .psql_history": No such file
Thanks to all... in effect it is a very little problem if you consider that probably I will call psql from normal accounts... :-) thanks Il giorno dom, 28/06/2009 alle 01.04 +0930, Shane Ambler ha scritto: > Guillaume Lelarge wrote: > > Hi Ivan, > > > > Le vendredi 26 juin 2009 à 17:53:15, ivan marchesini a écrit : > >> [...] > >> I have installed a postgres db using a datadir different > >> from /var/lib/pgsql/.psql_history. > >> > >> then: > >>su postgres > >>psql postgres > >> > >> All went fine but when I exit from psql from a db I obtain: > >> ___ > >> could not save history to file "/var/lib/pgsql/.psql_history": No such > >> file or directory > >> ___ > >> > >> how can I fix this problem? where can I say psql that it must > >> write .psql_history into the datadir? > >> > > > > psql tries to write there because the home directory of the postgres user > > is > > /var/lib/postgres. Probably better to use HISTFILE to change it (\set > > HISTFILE > > '/path/to/histfile'). > > > > Regards. > > > > > You do know that you don't need to su postgres to start psql? > > Only the server needs to be run with the postgres user account. If you > run psql from your normal user account the history file will be saved > into your home folder not the postgresql install/data file dir. > > > -- > > Shane Ambler > pgSQL (at) Sheeky (dot) Biz > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] LTREE extension and "order by"
Hi, in postgreSQL (with LTREE extension) database I have the following table "comments": id BIGINT /* id */ article_id BIGINT /*article-id */ parent_id BIGINT comment TEXT path LTREE level INTEGER /* level */ with the following rows: id article_id comment parent_id path level 1 1 1 1 2 1 1 1.2 2 3 1 2 1.2.3 3 4 1 2 1.4 2 5 1 4 1.4.5 3 6 1 6 1 7 1 6 6.7 2 8 1 6 6.8 2 9 1 9 1 10 1 10 1 11 1 5 1.4.5.11 4 and I need to select complete tree (with correct order of comments). SELECT * from comments where article_id = 2 order by when I used: SELECT * from comments where article_id = 2 order by path the result is: id comment path 1 1 2 1.2 3 1.2.3 4 1.4 5 1.4.5 11 1.4.5.11 10 10 6 6 7 6.7 8 6.8 9 9 BUT, it is wrong, because comment with id = 10 is after comment with id=11 (i know, this is correct, because ordering by column PATH [as TEXT], and 10 is 'after' 1.4.5.11) , but I need : id comment path 1 1 2 1.2 3 1.2.3 4 1.4 5 1.4.5 11 1.4.5.11 6 6 7 6.7 8 6.8 9 9 10 jjjj 10 thanks Ivan -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LTREE extension and "order by"
Hi, thank you for your answer, please can You send me complete select command how to convert ltree column to integer[] and use it to order by. thanks Ivan 2011/7/21 pasman pasmański : > Hi. > > You should convert path to integer[]. > > 2011/7/20, Ivan Polak : >> Hi, >> >> in postgreSQL (with LTREE extension) database I have the following >> table "comments": >> >> id BIGINT /* id */ >> article_id BIGINT /*article-id */ >> parent_id BIGINT >> comment TEXT >> path LTREE >> level INTEGER /* level */ >> >> with the following rows: >> >> id article_id comment parent_id path level >> 1 1 1 1 >> 2 1 1 1.2 2 >> 3 1 2 1.2.3 3 >> 4 1 2 1.4 2 >> 5 1 4 1.4.5 3 >> 6 1 6 1 >> 7 1 6 6.7 2 >> 8 1 6 6.8 2 >> 9 1 9 1 >> 10 1 10 1 >> 11 1 5 1.4.5.11 4 >> >> and I need to select complete tree (with correct order of comments). >> >> SELECT * from comments where article_id = 2 order by >> >> when I used: >> >> SELECT * from comments where article_id = 2 order by path >> >> the result is: >> >> id comment path >> 1 1 >> 2 1.2 >> 3 1.2.3 >> 4 1.4 >> 5 1.4.5 >> 11 1.4.5.11 >> 10 10 >> 6 6 >> 7 6.7 >> 8 6.8 >> 9 9 >> >> BUT, it is wrong, because comment with id = 10 is after comment with id=11 >> >> (i know, this is correct, because ordering by column PATH [as TEXT], >> and 10 is 'after' 1.4.5.11) >> >> , but I need : >> >> id comment path >> 1 1 >> 2 1.2 >> 3 1.2.3 >> 4 1.4 >> 5 1.4.5 >> 11 1.4.5.11 >> 6 6 >> 7 6.7 >> 8 6.8 >> 9 9 >> 10 10 >> >> thanks >> >> Ivan >> >> -- >> Sent via pgsql-sql mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > > > -- > > pasman > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LTREE extension and "order by"
hi, thank you, but there is error: ERROR: cannot cast type ltree to text LINE 1: ... article_id = 2 order by string_to_array(path::text,'.'):... Ivan On 21 July 2011 17:25, Carla wrote: > Try it: > select * from comments where article_id = 2 order by > string_to_array(path::text,'.')::integer[]; > > 2011/7/21 Ivan Polak >> >> Hi, thank you for your answer, please can You send me complete select >> command how to convert ltree column to integer[] and use it to order >> by. >> >> thanks >> >> Ivan >> >> 2011/7/21 pasman pasmański : >> > Hi. >> > >> > You should convert path to integer[]. >> > >> > 2011/7/20, Ivan Polak : >> >> Hi, >> >> >> >> in postgreSQL (with LTREE extension) database I have the following >> >> table "comments": >> >> >> >> id BIGINT /* id */ >> >> article_id BIGINT /*article-id */ >> >> parent_id BIGINT >> >> comment TEXT >> >> path LTREE >> >> level INTEGER /* level */ >> >> >> >> with the following rows: >> >> >> >> id article_id comment parent_id path level >> >> 1 1 1 1 >> >> 2 1 1 1.2 2 >> >> 3 1 2 1.2.3 3 >> >> 4 1 2 1.4 2 >> >> 5 1 4 1.4.5 3 >> >> 6 1 6 1 >> >> 7 1 6 6.7 2 >> >> 8 1 6 6.8 2 >> >> 9 1 9 1 >> >> 10 1 10 1 >> >> 11 1 5 1.4.5.11 4 >> >> >> >> and I need to select complete tree (with correct order of comments). >> >> >> >> SELECT * from comments where article_id = 2 order by >> >> >> >> when I used: >> >> >> >> SELECT * from comments where article_id = 2 order by path >> >> >> >> the result is: >> >> >> >> id comment path >> >> 1 1 >> >> 2 1.2 >> >> 3 1.2.3 >> >> 4 1.4 >> >> 5 1.4.5 >> >> 11 1.4.5.11 >> >> 10 10 >> >> 6 6 >> >> 7 6.7 >> >> 8 6.8 >> >> 9 9 >> >> >> >> BUT, it is wrong, because comment with id = 10 is after comment with >> >> id=11 >> >> >> >> (i know, this is correct, because ordering by column PATH [as TEXT], >> >> and 10 is 'after' 1.4.5.11) >> >> >> >> , but I need : >> >> >> >> id comment path >> >> 1 1 >> >> 2 1.2 >> >> 3 1.2.3 >> >> 4 1.4 >> >> 5 1.4.5 >> >> 11 1.4.5.11 >> >> 6 6 >> >> 7 6.7 >> >> 8 6.8 >> >> 9 9 >> >> 10 10 >> >> >> >> thanks >> >> >> >> Ivan >> >> >> >> -- >> >> Sent via pgsql-sql mailing list ([email protected]) >> >> To make changes to your subscription: >> >> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> > >> > >> > -- >> > >> > pasman >> > >> > -- >> > Sent via pgsql-sql mailing list ([email protected]) >> > To make changes to your subscription: >> > http://www.postgresql.org/mailpref/pgsql-sql >> > >> >> -- >> Sent via pgsql-sql mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] LTREE extension and "order by"
HI, thanks, thanks Carla, your solution is OK :-) (i`m using PostgreSQL 8.2, so Pavel is right). Ivan On 21 July 2011 18:28, Carla wrote: > Hmm, I'm using PostgreSQL 8.4 and it worked. > Try to use the function ltree2text instead of ::text. > select * from comments where article_id = 2 order by > cast(string_to_array(ltree2text(path),'.') as integer[]); > > 2011/7/21 Ivan Polak >> >> hi, thank you, but there is error: >> >> ERROR: cannot cast type ltree to text >> LINE 1: ... article_id = 2 order by string_to_array(path::text,'.'):... >> >> Ivan >> >> On 21 July 2011 17:25, Carla wrote: >> > Try it: >> > select * from comments where article_id = 2 order by >> > string_to_array(path::text,'.')::integer[]; >> > >> > 2011/7/21 Ivan Polak >> >> >> >> Hi, thank you for your answer, please can You send me complete select >> >> command how to convert ltree column to integer[] and use it to order >> >> by. >> >> >> >> thanks >> >> >> >> Ivan >> >> >> >> 2011/7/21 pasman pasmański : >> >> > Hi. >> >> > >> >> > You should convert path to integer[]. >> >> > >> >> > 2011/7/20, Ivan Polak : >> >> >> Hi, >> >> >> >> >> >> in postgreSQL (with LTREE extension) database I have the following >> >> >> table "comments": >> >> >> >> >> >> id BIGINT /* id */ >> >> >> article_id BIGINT /*article-id */ >> >> >> parent_id BIGINT >> >> >> comment TEXT >> >> >> path LTREE >> >> >> level INTEGER /* level */ >> >> >> >> >> >> with the following rows: >> >> >> >> >> >> id article_id comment parent_id path level >> >> >> 1 1 1 1 >> >> >> 2 1 1 1.2 2 >> >> >> 3 1 2 1.2.3 3 >> >> >> 4 1 2 1.4 2 >> >> >> 5 1 4 1.4.5 3 >> >> >> 6 1 6 1 >> >> >> 7 1 6 6.7 2 >> >> >> 8 1 6 6.8 2 >> >> >> 9 1 9 1 >> >> >> 10 1 10 1 >> >> >> 11 1 5 1.4.5.11 4 >> >> >> >> >> >> and I need to select complete tree (with correct order of comments). >> >> >> >> >> >> SELECT * from comments where article_id = 2 order by >> >> >> >> >> >> when I used: >> >> >> >> >> >> SELECT * from comments where article_id = 2 order by path >> >> >> >> >> >> the result is: >> >> >> >> >> >> id comment path >> >> >> 1 1 >> >> >> 2 1.2 >> >> >> 3 1.2.3 >> >> >> 4 1.4 >> >> >> 5 1.4.5 >> >> >> 11 1.4.5.11 >> >> >> 10 10 >> >> >> 6 6 >> >> >> 7 6.7 >> >> >> 8 6.8 >> >> >> 9 9 >> >> >> >> >> >> BUT, it is wrong, because comment with id = 10 is after comment with >> >> >> id=11 >> >> >> >> >> >> (i know, this is correct, because ordering by column PATH [as TEXT], >> >> >> and 10 is 'after' 1.4.5.11) >> >> >> >> >> >> , but I need : >> >> >> >> >> >> id comment path >> >> >> 1 1 >> >> >> 2 1.2 >> >> >> 3 1.2.3 >> >> >> 4 1.4 >> >> >> 5 1.4.5 >> >> >> 11 1.4.5.11 >> >> >> 6 6 >> >> >> 7 6.7 >> >> >> 8 6.8 >> >> >> 9 9 >> >> >> 10 10 >> >> >> >> >> >> thanks >> >> >> >> >> >> Ivan >> >> >> >> >> >> -- >> >> >> Sent via pgsql-sql mailing list ([email protected]) >> >> >> To make changes to your subscription: >> >> >> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> >> >> > >> >> > >> >> > -- >> >> > >> >> > pasman >> >> > >> >> > -- >> >> > Sent via pgsql-sql mailing list ([email protected]) >> >> > To make changes to your subscription: >> >> > http://www.postgresql.org/mailpref/pgsql-sql >> >> > >> >> >> >> -- >> >> Sent via pgsql-sql mailing list ([email protected]) >> >> To make changes to your subscription: >> >> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> > >> > >> >> -- >> Sent via pgsql-sql mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] could not connect to server
Dear Users, I have this problem: I'm running postgres 7.3 on a linux system... I have started postmaster with -i option, but when I try to connect to the server from another computer I obtain: psql: could not connect to server: Nessuna rotta verso l'host Is the server running on host "IP." and accepting TCP/IP connections on port 5432? when I try to connect from the same computer running the postgres server (using TCP/IP ) psql cave_prova -h IP -p 5432 -U aurora the server answer correctly... and I can connect... so I think the TCP/IP socket is working well!!! can you suggest me some ideas for solving this problem??? thank you Ivan -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] could not connect to server
Dear Andreas, this is my pg_hba.conf file: __ # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD # IPv4-style local connections: hostallall 127.0.0.1 255.255.255.255 trust hostallall "my_netaddres".0 255.255.255.0 trust #hostall # IPv6-style local connections: #hostall all ::1 :::::::trust # Using sockets credentials for improved security. Not available everywhere, # but works on Linux, *BSD (and probably some others) local allall ident sameuser __ I hope is correct... what do you think?? thank you!! On ven, 2006-10-13 at 09:27 +0200, A. Kretschmer wrote: > am Fri, dem 13.10.2006, um 8:52:19 +0200 mailte ivan marchesini folgendes: > > Dear Users, > > I have this problem: > > I'm running postgres 7.3 on a linux system... > > Uhm, a very old version... > > > > > I have started postmaster with -i option, but when I try to connect to > > the server from another computer I obtain: > > > > psql: could not connect to server: Nessuna rotta verso l'host > > Is the server running on host "IP." and accepting > > TCP/IP connections on port 5432? > > > > when I try to connect from the same computer running the postgres server > > (using TCP/IP ) > > psql cave_prova -h IP -p 5432 -U aurora > > > > the server answer correctly... and I can connect... > > so I think the TCP/IP socket is working well!!! > > > > can you suggest me some ideas for solving this problem??? > > thank you > > Ivan > > Take a look in you ph_hba.conf - file and edit this properly. You need > an entry for the other host. > > http://www.postgresql.org/docs/current/static/client-authentication.html > > > > HTH, Andreas -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] drop a check
Dear all... I have created a check constraint without giving it a name.. now I have a check named "$25" in my table that I need to drop or modify!!! How can I do??? with names is simple alter table tablename drop constraint constraintname; but without name?? :-) many thanks... Ivan -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] drop a check
Dear Volkan, thank you for your answer... I have tried to verify the table information_schema.constraint_column_usage, but, and I was really surprised, I have found nothing inside it.. it seems there are not checks!!! I have also seen the manual page you suggested but I can't understand why I don't have checks in this table... but I'm sure I have checks in my table.. because they works! does this problem can be related to the fact that I have created the checks only after that I have created the table... I have used this syntacs: alter table tablename add check (..) however it sounds strange! thanks!!! Ivan On lun, 2006-11-13 at 19:41 +0200, Volkan YAZICI wrote: > On Nov 13 05:32, ivan marchesini wrote: > > I have created a check constraint without giving it a name.. > > now I have a check named "$25" in my table that I need to drop or > > modify!!! > > How can I do??? > > with names is simple > > > > alter table tablename drop constraint constraintname; > > > > but without name?? :-) > > CREATE TABLE cons_test ( > u integer CHECK (u > 10) > ); > > SELECT constraint_name > FROM information_schema.constraint_column_usage > WHERE table_name = 'cons_test' AND >column_name = 'u'; > > See infoschema-constraint-column-usage.html in the manual for further > assistance. > > > Regards. > -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] drop a check
. or it depend on the fact I'm using postgres 7.4.13 many thanks... Ivan On lun, 2006-11-13 at 19:41 +0200, Volkan YAZICI wrote: > On Nov 13 05:32, ivan marchesini wrote: > > I have created a check constraint without giving it a name.. > > now I have a check named "$25" in my table that I need to drop or > > modify!!! > > How can I do??? > > with names is simple > > > > alter table tablename drop constraint constraintname; > > > > but without name?? :-) > > CREATE TABLE cons_test ( > u integer CHECK (u > 10) > ); > > SELECT constraint_name > FROM information_schema.constraint_column_usage > WHERE table_name = 'cons_test' AND >column_name = 'u'; > > See infoschema-constraint-column-usage.html in the manual for further > assistance. > > > Regards. > -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] join a lot of columns of two tables
Dear Postgres Users, I have 2 tables... each one has a column called ID (primary keys of each table) the values into each ID column are exactly the same. each table has a lot of other columns (around 50 for each table) I would like to create a table containing the ID (only one column obviously) and all the other columns of both tables... a simple equi join for each column is simple but how can I join completely the two table on the bases of the column ID??? probably it is a simple question but I don't know how to solve this very simple problem quikly... :-( thanks Ivan -- Ti prego di cercare di non inviarmi files .doc, .xls, .ppt, .dwg. Preferisco formati liberi. Please try to avoid to send me .doc, .xls, .ppt, .dwg files. I prefer free formats. http://it.wikipedia.org/wiki/Formato_aperto http://en.wikipedia.org/wiki/Open_format Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] insert a sequence
Dear postgres Users, I have a simple question I think. I have a table that contains some columns one of these columns (the columns ID) contains distinct integer values ... I need to insert into this table some other records but I only need that values were inserted into the ID column and that they were progressive... (a sequence starting from 100 and ending to 300, step=1) the other columns must remains null can I do this with a sequence and then an "insert into"? many thanks -- Ti prego di cercare di non inviarmi files .doc, .xls, .ppt, .dwg. Preferisco formati liberi. Please try to avoid to send me .doc, .xls, .ppt, .dwg files. I prefer free formats. http://it.wikipedia.org/wiki/Formato_aperto http://en.wikipedia.org/wiki/Open_format Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax (university): +39(0)755853756 fax (home): +39(0)5782830887 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] date problems
Dear PG users, I have an access db and I'm trying to pass all to postgres I have used mdbtools to export the schema and all went quite well. then I exported the single tables... to csv.. a lot of table have some timestamp fields containing data as DD/MM/, and I'm no able to copy this table into postgres... because it needs /MM/DD... I used \copy... Which is the best solution for this problem... many thanks Ivan -- Ti prego di cercare di non inviarmi files .doc, .xls, .ppt, .dwg. Preferisco formati liberi. Please try to avoid to send me .doc, .xls, .ppt, .dwg files. I prefer free formats. http://it.wikipedia.org/wiki/Formato_aperto http://en.wikipedia.org/wiki/Open_format Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax (university): +39(0)755853756 fax (home): +39(0)5782830887 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] foreign key problem
Dear users, I'm trying to create constraints into a db they give me from access... I'm using pgadmin to create a new foreign key for the relationship between two table.. I have a table called "generalita" wich contains a column called "cod_carg": ___ select cod_carg from generalita group by cod_carg; cod_carg -- 2 0 1 (4 righe) ___ I have another table, called "diz_carg" that contain, into a column called "cod_carg" (that is a primary key), the domain values for the "cod_carg" column of "generalita": __ select cod_carg from diz_carg group by cod_carg; cod_carg -- 1 0 2 (3 righe) __ so when I try to create the foreign key I obtain: ALTER TABLE generalita ADD CONSTRAINT fkey_diz_cod_carg FOREIGN KEY (cod_carg) REFERENCES diz_carg (cod_carg) ON UPDATE NO ACTION ON DELETE NO ACTION; ERROR: insert or update on table "generalita" violates foreign key constraint "fkey_diz_cod_carg" DETAIL: Key (cod_carg)=( ) is not present in table "diz_carg". can I allow null values for "generalita.cod_carg" although it is a foreign key for "diz_carg.cod_carg"? I apologize if it is a stupid question :-( many thanks to all -- Ti prego di cercare di non inviarmi files .doc, .xls, .ppt, .dwg. Preferisco formati liberi. Please try to avoid to send me .doc, .xls, .ppt, .dwg files. I prefer free formats. http://it.wikipedia.org/wiki/Formato_aperto http://en.wikipedia.org/wiki/Open_format Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax (university): +39(0)755853756 fax (home): +39(0)5782830887 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] store pdf files
Hi to all... I need to create a db that contain link to some pdf files.. At the moment these are simple links (to the files that are stored into the file system) storing paths into a column of a dbf table... I need to manage this data considering that the db I'm going to create will be moved in some months to another server... so I think that the link to the files positions into the file system isn't a valid solution... can you suggest me a better way to manage this pdf data?? Each pdf is quite small (<100k) should I use BLOB? many thanks Ivan -- Ti prego di cercare di non inviarmi files .dwg, .doc, .xls, .ppt. Preferisco formati liberi. Please try to avoid to send me .dwg, .doc, .xls, .ppt files. I prefer free formats. http://it.wikipedia.org/wiki/Formato_aperto http://en.wikipedia.org/wiki/Open_format Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) Socio fondatore GFOSS "Geospatial Free and Open Source Software" http://www.gfoss.it e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax (university): +39(0)755853756 fax (home): +39(0)5782830887 jabber: [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] plpgsql vs. SQL in stored procedures
Is there a way to declare variables and use IF in plain SQL, not in plpgsql inside stored procedures? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] plpgsql vs. SQL in stored procedures
On Wed, 31 Mar 2004 03:31:01 -0500 Christopher Browne <[EMAIL PROTECTED]> wrote: > The forthcoming support for recursive queries using a WITH clause > might provide, after a fashion, a way to declare variables. I think I'll have to work with pg 7.3 Does this translate to: you won't be able to use variables? I was used to do stuff like this with MS SQL create proc sp_getuid @uid uniqueidentifier as delete from auth where lapsedateadd(mi,-10,getdate()) and [EMAIL PROTECTED]) if (@R_ID is null) begin delete from auth where [EMAIL PROTECTED] select null as R_ID end else begin update auth set lapse=getdate() where [EMAIL PROTECTED] select @R_ID as R_ID end Does it mean that to have variables in SP I'll have to use plpgsql in spite of plain SQL? > As for IF, there is a functional equivalent to it in the form of the > SQL standard CASE statement. TY for the refreshing pointer. I was looking in the wrong place. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Random Unique Id
On Tue, 20 Oct 2009 16:49:17 -0300 Nahuel Alejandro Ramos wrote: > Hi all, > I was searching for a sequence (for serials) that let me use a > random unique number ID on a Primary Key or a simple index. > I have not found a solution so I have done it by myself. I would > like to share it so here it is: http://www.webthatworks.it/d1/node/page/pseudo_random_sequences_postgresql Adapted from Daniel Verite suggestion. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] get distinct + group by then filter
I've a web application and I'm trying to do some reporting on
affiliate commission
create table tracky_hit (
hitid serial,
esid varchar(32), -- related to browser session
track_time timestamp,
aid varchar(32), -- affiliate code
-- some other tracking stuff
);
create table tracky_event (
eventid serial,
esid varchar(32) references tracky_hit (esid)
);
create table tracky_ordergroup_event (
ordergroupid int references ...,
eventid int references tracky_event (eventid)
);
Now I'd like to pick up the first hit for each esid in a given
interval of time for a given aid and relate them with ordergroupid.
aid may change across the same esid.
Getting the first hit for each esid can be done:
select min(hitid) as h
from tracky_hit
group by esid;
or
select distinct on (esid) hitid
from tracky_hit
order by esid, track_time;
If I put a where aid='somestuff' right in the above query... I'm not
picking up the first hit in an esid.
The only way that comes to my mind to solve the problem is applying
the condition later in a subquery, but no conditions means a lot of
data returned.
I've a similar problem with the interval: if I chop in the middle of
a session I may not pick up the beginning of each session.
Furthermore I've to count session just once even if they cross the
boundary of an interval.
I could do something like:
select oe.ordergroupid from
tracky_ordergroup_event oe
join tracky_event e on e.eventid=oe.eventid
join tracky_hit th on th.esid=e.esid
where th.hitid in
(select distinct on (esid) hitid
from tracky_hit
where track_time between
('2009-12-01'::timestamp - interval '1 days')
and
('2009-12-01'::timestamp + interval '1 months' + interval '1
days')
order by esid, track_time
)
and th.aid='someaid'
and th.track_time between
('2009-12-01'::timestamp)
and
('2009-12-01'::timestamp + interval '1 months');
but this looks awful. Any better way?
I'm on 8.3 and no short term plan to move to 8.4
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] get distinct + group by then filter
On Fri, 18 Dec 2009 13:20:00 +0100
Filip Rembiałkowski wrote:
> 2009/12/17 Ivan Sergio Borgonovo
>
> > I've a web application and I'm trying to do some reporting on
> > affiliate commission
> >
> > create table tracky_hit (
> > hitid serial,
> > esid varchar(32), -- related to browser session
> > track_time timestamp,
> > aid varchar(32), -- affiliate code
> > -- some other tracking stuff
> > );
> >
> > create table tracky_event (
> > eventid serial,
> > esid varchar(32) references tracky_hit (esid)
> This imples that tracky_hit.esid is at least UNIQUE.
> );
my fault esid is not unique and that reference was added just to
imply that tracky_hit.esid and tracky_event.esid are related.
I'm still thinking if it's worth for extra safety to have a
create table tracky_esid(
esid varchar(32) primary key
);
but I can't see any use of it other than recording the session
starting time... and I'm not sure it could come handy, since the hit
is recorded later, so I couldn't relate hit time and session time.
> > create table tracky_ordergroup_event (
> > ordergroupid int references ...,
> > eventid int references tracky_event (eventid)
> > );
> >
> > Now I'd like to pick up the first hit for each esid in a given
> > interval of time for a given aid and relate them with
> > ordergroupid.
> >
> > aid may change across the same esid.
> >
>
> If tracky_hit.esid is unique, then why same esid can have many
> aids?
>
> Can you specify more complete schema (at least PKeys would be
> nice)?
I just made a pk out of
(hitid, track_time)
The rest of the schema is the same and I have the query below
working... I was just wondering if there was a "cleaner" way.
> > Getting the first hit for each esid can be done:
> >
> > select min(hitid) as h
> > from tracky_hit
> >group by esid;
> >
> > or
> >
> > select distinct on (esid) hitid
> > from tracky_hit
> >order by esid, track_time;
> >
> DISTINCT ON seems a good aproach tu such queries.
Yeah, they both seems to work... but is there any difference?
These are the 2 plans:
1)
HashAggregate
(cost=10.60..11.10 rows=40 width=72) (actual
time=0.041..0.041 rows=4 loops=1)
-> Seq Scan on tracky_hit (cost=0.00..10.40 rows=40 width=72)
(actual time=0.017..0.019 rows=7 loops=1)
Total runtime: 0.111 ms
(3 rows)
2)
Unique (cost=11.46..11.66 rows=40 width=80) (actual
time=0.061..0.066 rows=4 loops=1)
-> Sort (cost=11.46..11.56 rows=40 width=80) (actual time=0.060..0.063
rows=7 loops=1)
Sort Key: esid, track_time
Sort Method: quicksort Memory: 25kB
-> Seq Scan on tracky_hit (cost=0.00..10.40 rows=40 width=80)
(actual time=0.007..0.010 rows=7 loops=1)
Total runtime: 0.102
ms (6 rows)
But a) I'm not that skilled in reading plans b) there are nearli no
record to test on
But considering the plan is pretty different I'd say one should have
a major performance advantage over the other.
BTW this is working:
> > select oe.ordergroupid from
> > tracky_ordergroup_event oe
> > join tracky_event e on e.eventid=oe.eventid
> > join tracky_hit th on th.esid=e.esid
> > where th.hitid in
> >(select distinct on (esid) hitid
> > from tracky_hit
> > where track_time between
> >('2009-12-01'::timestamp - interval '1 days')
> >and
> >('2009-12-01'::timestamp + interval '1 months' + interval
> > '1 days')
> > order by esid, track_time
> >)
> > and th.aid='someaid'
> > and th.track_time between
> > ('2009-12-01'::timestamp)
> >and
> > ('2009-12-01'::timestamp + interval '1 months');
> >
> > but this looks awful. Any better way?
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] short-cutting if sum()>constant
Hi, I'd like to know if select sum(qty) from t where status=37; is > constant. qty is always >0. Is there a way to skip examining further rows and return a result ASAP? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()>constant
On Tue, 22 Dec 2009 14:48:55 +0100 Filip Rembiałkowski wrote: > With plain SQL, no. > > With a user defined function in PL/PgSQL, yes. thanks to all. I'm on 8.3 so no CTE. I was hoping there was some way to write it in plain SQL. I'm planning to wrap everything in a plpgsql function using cursors and then switch to WITH when I'll move to 8.4. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()>constant
On Tue, 22 Dec 2009 20:47:18 +0100 Pavel Stehule wrote: > Hello > > I found one ugly trick. You can multiply lines and SUM > cons > could be replaced limit clause: The trick is really smart (and fun), kudos, really, it's always a pleasure to read your solutions, thanks. But as expected: In a table with 10 rows with random values [0,9] test=# select sum(x) from (select 1 as x,(a = generate_series(1,a))::int from data limit 10) s; sum 10 (1 row) Time: 71.423 ms test=# create or replace function tano(a int, out b int) returns int as $$ declare row record; begin b :=0; for row in select a as _a from data loop b := row._a + b; if (b>=a) then return; end if; end loop; return; end; $$ language plpgsql; test=# select * from tano(10); b 10 (1 row) Time: 0.187 ms I run both several times to avoid simple caching issues... anyway I didn't really run a serious benchmark, but results were always in the same order of magnitude. I hope I didn't make any mistake. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()>constant
On Wed, 23 Dec 2009 00:00:31 +0100 Ivan Sergio Borgonovo wrote: > On Tue, 22 Dec 2009 20:47:18 +0100 > Pavel Stehule wrote: > > > Hello > > > > I found one ugly trick. You can multiply lines and SUM > cons > > could be replaced limit clause: > > The trick is really smart (and fun), kudos, really, it's always a > pleasure to read your solutions, thanks. > > But as expected: as unexpected... > test=# create or replace function tano(a int, out b int) ^^^ should be anything but a > returns int as > $$ > declare > row record; > begin > b :=0; > for row in select a as _a from data where a>0 > loop > b := row._a + b; > if (b>=a) then > return; > end if; > end loop; > return; > end; > $$ language plpgsql; Making it longer to better appreciate the difference: 1M rows where a [0,2] select * from tano((100)::int); b - 101 (1 row) Time: 1235.243 ms select sum(x) from (select 1 as x,(a = generate_series(1,a))::int from data limit 100) s; sum - 100 (1 row) Time: 1309.441 ms Being fair once you add the where clause to the generate_series version the difference in performance is negligible and saying that the plpgsql version is faster would require some more serious benchmarking. Surprised! If the generate_series can compete with the plpgsql for loop... why is the plpgsql version so "slow"? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()>constant
On Wed, 23 Dec 2009 01:09:40 +0100 Ivan Sergio Borgonovo wrote: > On Wed, 23 Dec 2009 00:00:31 +0100 > Ivan Sergio Borgonovo wrote: > > > On Tue, 22 Dec 2009 20:47:18 +0100 > > Pavel Stehule wrote: > > > > > Hello > > > > > > I found one ugly trick. You can multiply lines and SUM > cons > > > could be replaced limit clause: > > > > The trick is really smart (and fun), kudos, really, it's always a > > pleasure to read your solutions, thanks. > > > > But as expected: > > as unexpected... As even more unexpected... when all row are >0 and most of them are equal to 1 the generate_series performs appreciably better (roughly 15% faster). And I think your version can be further optimised: select count(*) from (select (generate_series(1,a))::int from data limit 9000) s; This perform 30% faster. So what's so slow in the plpgsql version? Fortunately as expected when "enough" rows are >1 the for loop solution perform much better. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()>constant
On Wed, 23 Dec 2009 08:38:52 +0100
Pavel Stehule wrote:
> > As even more unexpected... when all row are >0 and most of them
> > are equal to 1 the generate_series performs appreciably better
> > (roughly 15% faster).
> > And I think your version can be further optimised:
> > select count(*) from (select (generate_series(1,a))::int from
> > data limit 9000) s;
> > This perform 30% faster.
> >
> > So what's so slow in the plpgsql version?
>
> don't forget - plpgsql is interpret - it is best as glue for SQL
Yeah but how many times is it interpreted in a 1M cycle expecially
when you ran it more than one time and the interpreter should be
"hot"? I don't know how much "interpreter" work can be saved from a
previous run of the function... but still there should be something
left [1].
plpgsql isn't duck typed and that function doesn't leave too much
space for "interpretation".
Unless I'm missing something, in a typed language like plpgsql that
function could be easily turned into its C equivalent by the
interpreter.
I really thought the cost of running plpgsql was much lower in such
kind of situation.
Consider that the cost doesn't come from the initial cost of
interpreting the function but it is proportional to the numbers of
cycles.
Even hard coding the LIMIT in the plpgsql version doesn't make it
faster enough. So it is not the planner.
In a 10M rows dataset where 8332885 are =1 and the rest are =2:
If I'm testing for >90
I get 940.580 ms vs 1302.162 ms
If I'm testing for >900
I get 8816.263 ms vs 12306.483 ms
BTW
select sum(a) from data; takes 1999.492 ms.
select count(*) from data; takes 1612.039 ms
While smart your way is definitively more convoluted and should
require much more cpu cycles and memory.
The plperl version:
create or replace function totano(int)
returns int as
$$
my $b = 0;
my $row;
my $sth = spi_query("select a from data");
while(defined($row=spi_fetchrow($sth))) {
$b += $row->{a};
if($b>=$_[0]) {
return $b;
}
}
return $b;
$$ LANGUAGE plperl;
Is 10 times slower than the plpgsql version.
> statement. I don't thing so plpgsql is slow - speed is similar to
> using buildin functionality. But I am sure, rewritening your
> function to C could help. If you need maximal speed.
In my case it is not worth. I was just looking into something that
didn't have to scan all the rows.
With the real distribution of data and no real workload on the box
your solution is faster but the difference while unexpectedly
appreciable is nearly negligible.
Anyway I'm not able to justify the difference in speed between
plpgsql and your solution in such case unless plpgsql is switching
back and forward between binary data and their text representation.
It would be nice if someone that know plpgsql internals explain
where the cost comes from.
> I thing, so there are other trick, I am not sure if it is faster.
> You can create own aggregate. In state function you can calculate
> and check state value. If it is over your limit, then you can raise
> exception. So if your query will be finished with custom exception,
> then sum(c) > n is true.
I may test it later.
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()>constant
On Wed, 23 Dec 2009 12:52:38 +0100 Pavel Stehule wrote: > The problem is massive cycle. Plpgsql really isn't language for > this. This interpret missing own arithmetic unit. So every > expression is translated to SELECT statement > > IF a > c ~ SELECT a > c > a = a + 1 ~ SELECT a + 1 > these queries are executed in some special mode, but still it is > more expensive than C a = a + 1 I didn't get it. I'd expect that since plpgsql should shere SQL data types it could map easily something like if (a>b) then or a := a +b and something like a for in query loop was going to be highly optimised as well. plpgsql should be the most tightly bounded language to the internals of pg SQL. > > select sum(a) from data; takes 1999.492 ms. > > select count(*) from data; takes 1612.039 ms > it is slower, because there is one NULL test more. That didn't came as a surprise. It was there for comparison. > PLpgSQL quietly uses cursors for FOR SELECT, your plperl uses I didn't notice. That was my first plperl function in my life. Anyway that means that that test didn't say anything about interpreter speed and duck typing. > > Anyway I'm not able to justify the difference in speed between > > plpgsql and your solution in such case unless plpgsql is > > switching back and forward between binary data and their text > > representation. It would be nice if someone that know plpgsql > > internals explain where the cost comes from. > I thing so most expensive part in your plpgsql code is two > expression. I didn't understand. What's the reason the interpreter can't translate the if and the b := row._a + b; into something that very resemble compiled C? plpgsql is not doing anything different than: select count(*) from (select case when a>3 then 1 else 2 end, a+a from data limit 900) as f; One of the advantages of using plpgsql (and one annoyance too) is that variables are declared and plpgsql should know how to operate on them with native C code. BTW the best performer considering all constraints and data distribution seems to be the simplest one: select sum(s.a) from (select a from data where a>0 limit 900) s; Time: 2620.677 ms We've no negative int... and they are int, so they have to be no less than 1 if they are not 0. If I know there are no 0, the simplest version become even faster. When I randomly pick up values in [1,3] plpgsql and generate_series start to perform similarly but still the simplest version is leading. When the interval is in [1,6] the plpgsql becomes faster than the generate_series but the simplest version is still leading. Just when the interval is in [1,10] the plpgsql version and the simplest one becomes comparable. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()>constant
On Wed, 23 Dec 2009 14:35:27 +0100 Pavel Stehule wrote: > a) simplicity. PLpgSQL interpret is very simple. > b) mostly types are little bit different behave than natural C > types - domains are different, C types doesn't know NULL value, ... But well... there should be a mapping somewhere between SQL int and C and it can be plainly reused. $b = 5 + "10"; is going to be far more problematic to execute... but I guess that's exactly the problem. create or replace function tano(z int, out b int) returns int as $$ declare x varchar(4) = '12'; y int = 10; begin b := x + y; return; end; $$ language plpgsql; select * from tano(1); ERROR: operator does not exist: character varying + integer LINE 1: SELECT $1 + $2 ^ From what I can guess... the interpreter is sending SQL commands to pg and waiting result back. So actually the "interpreter" isn't actually aware of data types... it just relies on SQL to spot data type mismatch. That means... that everything is converted back and forward to *text*. On the other side once a SQL command is interpreted it actually knows what data types are and can pretty quickly sum int to int just taking care of nulls and overflows that would be anyway problems for any other language trying to sum "SQL ints". Even if it had to sum int and bigint the code could be optimised for just that and the need of a cast should be known in advance before every "loop". Did I get it? That's what you were trying to make me understand with: > It little bit nonsense. On 99% plpgsql use SPI api and work with > variables via query interface. PLpgSQL can do loop statement, if > statement, but for all others use internal query based API. > this query is little but different, than you original request, but > it could work for you. Yep... making clear a is an int simplify the problem quite a lot. But you couldn't use generate_series if a was not an int. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] short-cutting if sum()>constant
On Wed, 23 Dec 2009 11:36:31 -0500
Tom Lane wrote:
> Craig Ringer writes:
> > Pavel Stehule wrote:
> >> these queries are executed in some special mode, but still it
> >> is more expensive than C a = a + 1
>
> > ... and may have different rules, so you can't just write a
> > simple "map expressions to C equivalents" arithmetic evaluator.
> Yeah. As an example, overflow is supposed to be caught in "a + 1",
> unlike what would happen in C.
> In principle you could map some of the builtin operators into
> inline code, but it would be a great deal of work and the results
> would be un-portable.
Tank you all for being so patient...
I really miss how actually procedural languages works internally.
doesn't pg routinely map between SQL and C?
What is the difference between
select a+a from data;
and
a := a + a;
in a plpgsql function?
plpgsql knows that a are eg. int so it could just use the same C
code that it uses when it has to sum a+a in sql.
My guess since I don't even know what to look for to get an idea of
the internal working of plpgsql is that the interpreter translate
the code into SQL (sort of...), it sends it to the parser through
SPI_execute/prepare etc... (so yeah maybe for the "data" it is not
really sending "text" representation of data) but still... the
"code" has to be further interpreted...
So something like:
a := a + a;
turns out to be:
SPI_prepare("SELECT $1 + $2", 2, ...);
and this is going to be called for every loop.
while I thought the SQL engine and plpgsql interpreter were nearer
so that the interpreter could push directly in the SQL engine the
values of a.
Am I getting nearer?
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Postgres function for full text search
On Mon, 24 Jan 2011 10:38:02 +0100 - wrote: > Hello everyone. > My question if any known one postgres function, for full text > search that separates words with "| "? > As plainto_tsquery separating words with "& ". > > For example text "word1 word2 word3 word4" > and conversion rates for full text search postgres "word1 | word2 > | word3 | word4" I wrote a C extension that could help... Currently there are CREATE OR REPLACE FUNCTION tsvector_to_table( IN tsv tsvector, OUT lexeme text, OUT weight smallint[], OUT pos int[]) CREATE OR REPLACE FUNCTION tsvector_to_tsquery( IN tsv tsvector, op IN char(1), weights IN varchar(4), maxpos IN smallint ) RETURNS tsquery op is | or & It will ignore elements in the tsvector whose position is >maxpos. It will ignore elements whose weight is not in weights So it could turn gino:1,3,5A pino:2B into gino:A | pino:B The code has been working in production by several months but: - it is for 8.3 (there are some notes to port it to >8.4, namely supporting prefixes) - I wrote it for my environment so the "build" script are simple but you'll have to adjust them to your environment. The code could be released in GPL or FreeBSD license. I'd be happy if someone could guide me in the process of successfully donating this code to the community. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] \COPY in psql using \e
I'm connecting to a DB with psql -h host DB once in psql I'd like to \e create temp table t1 ...; \copy t1 from 'filename' ... select * from t1; [ESC]:x what I get is: CREATE TABLE ERROR: syntax error at or near "select" LINE 3: select * from t1; ^ \copy: ERROR: syntax error at or near "select" LINE 3: select * from t1; ^ Now if I do: \e create temp table t1 ...; \copy t1 from 'filename' ... [ESC]:x select * from t1; everything works fine. I've a lot of script files that actually contain mixed \COPY and "plain" SQL statement eg. truncate table t1; \copy t1 from 'filename1' insert into d1...; truncate table t2; \copy t2 from 'filename2 insert into d1...; that I execute as: psql -h host DB < myscript.sql and everything is fine. Of course one solution could be to open a file in vi in a xterm and execute it from another xterm. Anything cleaner? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Export Access 97 to PostgreSQL
On Thu, 27 Mar 2008 10:03:57 +0100 "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> wrote: > Thanks. I tried the following command > > COPY REGIONI FROM > 'C:/Inetpub/wwwroot2/FORMWebSite1/database/FormAjax/REGIONI.copy' > WITH DELIMITER '\t'; > > But i get this error > > ERROR: relation "regioni" does not exist You've to create the REGIONI table. You may (or may not) get into trouble with locale. I noticed you're using , as a decimal separator. So if you're still encountering problems... that could be the second thing to look at. > Here is a sample of my file (fields seperated with a tab) > 1360077,00 848351,00 2,00 2,00 4,00 "Trentino-alto adige" > 1842400,00 1132202,00 3,00 3,00 5,00 "Veneto" > 7859928000,00 746082,90 4,00 4,00 6,00 "Friuli-venezia giulia" -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] update with join
I've create table types( typeid int, special boolean not null ); create table methods( methodid int, typeid references types(typeid), ); create table orders( orderid int ); create table order_payments( payid int orderid references order(orderid), methodid references method(methodid), issued boolean not null default false ); orderid payid methodid special 1 1 1t 1 2 2t 1 3 3t 1 4 4f 1 5 4f I'd like to chose one payid If the payid is "special" just set issued to true for that payid, leave the other unchanged. If the payid is not "special" set issued for all the payid in the same order. eg. So if payid=4 I'd have orderid payid methodid special issued 1 1 1t t 1 2 2t t 1 3 3t t 1 4 4f t 1 5 4f t and if payid=2 orderid payid methodid special issued 1 1 1t f 1 2 2t t 1 3 3t f 1 4 4f f 1 5 4f f This stuff below doesn't work: update order_payments set issued=true where payid in ( select p.payid from order_payments p join methods as m on m.methodid=p.methodid join types as t on m.typeid=t.typeid where (p.orderid=%d and not t.special) or p.payid=%d); and I can understand why but I can't rewrite it to make it work. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] update with join
On Wed, 2 Apr 2008 23:54:18 -0300 "Osvaldo Kussama" <[EMAIL PROTECTED]> wrote: > 2008/4/2, Ivan Sergio Borgonovo <[EMAIL PROTECTED]>: > > I've > > > > create table types( > > typeid int, > > special boolean not null > > ); > > > > create table methods( > > methodid int, > > typeid references types(typeid), > > ); > > > > create table orders( > > orderid int > > ); > > > > create table order_payments( > > payid int > > orderid references order(orderid), > > methodid references method(methodid), > > issued boolean not null default false > > ); > > > > orderid payid methodid special > > 1 1 1t > > 1 2 2t > > 1 3 3t > > 1 4 4f > > 1 5 4f > > > > I'd like to chose one payid > > If the payid is "special" just set issued to true for that payid, > > leave the other unchanged. > > If the payid is not "special" set issued for all the payid in the > > same order. > > > > eg. > > So if payid=4 I'd have > > > > orderid payid methodid special issued > > 1 1 1t t > > 1 2 2t t > > 1 3 3t t > > 1 4 4f t > > 1 5 4f t > > > > and if payid=2 > > > > orderid payid methodid special issued > > 1 1 1t f > > 1 2 2t t > > 1 3 3t f > > 1 4 4f f > > 1 5 4f f > > > > This stuff below doesn't work: > > > > update order_payments > > set issued=true where payid in ( > > select p.payid > > from order_payments p > > join methods as m on m.methodid=p.methodid > > join types as t on m.typeid=t.typeid > > where (p.orderid=%d and not t.special) or p.payid=%d); > > > > and I can understand why but I can't rewrite it to make it work. > > > > Try: > UPDATE order_payments > SET issued=true FROM methods m, types t > WHERE m.methodid=p.methodid AND p -> order_payments >m.typeid=t.typeid AND >((order_payments.orderid=%d AND NOT t.special) OR > order_payments.payid=%d)); one less ) Even after correcting the few typos this version obtain the same result of update order_payments set issued=true where payid=%d I ended up in writing a plpgsql function that retrieve special and then have an if block. create or replace function IssuePay(_PayID int, out _OrderGroupID bigint, out _Online boolean) as $$ begin select into _OrderGroupID, _OnLine p.OrderGroupID, t.OnLine from shop_commerce_ordergroup_pay p join shop_commerce_paymethods m on p.PayMethodID=m.MethodID join shop_commerce_paytypes t on m.TypeID=t.TypeID where PayID=_PayID; if(_OnLine) then update shop_commerce_ordergroup_pay set Issued=true where PayID=_PayID; else update shop_commerce_ordergroup_pay set Issued=true where OrderGroupID=_OrderGroupID; end if; return; end; $$ language plpgsql; mutatis mutandis. It may not be the most elegant thing but it is enough encapsulated it won't be a pain to refactor once I become a better DBA or someone else point out a better solution on the list. I'd be curious if it had a performance penalty over a one update statement. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] advocacy: case studies
Can it be one of those things that end up in the "case studies" http://www.postgresql.org/about/casestudies/ http://www.theregister.co.uk/2008/04/03/xtremedata_fpga_xeon_data_warehouse/ "The XtremeDB is built from PostgreSQL – a full-featured, open-source RDBMS, that has been re-engineered by XDI," the company says in a newsletter. "All of the front-end PostgreSQL interfaces have been maintained intact and the back-end execution engine has been transformed to leverage the shared-nothing parallel cluster environment with FPGA acceleration." ? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Protection from SQL injection
On Sat, 26 Apr 2008 23:24:59 -0600 "Scott Marlowe" <[EMAIL PROTECTED]> wrote: > On Sat, Apr 26, 2008 at 9:58 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > > > IIRC there was some discussion recently of providing a mode in > > which the server would reject PQexec strings containing more than > > one query. I didn't care for it much at the time, but I think it > > would provide most of the benefit of these suggestions with far > > less compatibility or performance hit. > > agreed. > And I trust (SQL) code review more than tying the hands of the > programmers. > But I've always had the luxury of working with developers who liked > me as a DBA and were willing to do things my way, as far as the DB > was concerned anyway... what if you're the DBA and the dev and you don't trust yourself even if you'd be willing to do the things your way ;) -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Protection from SQL injection
On Sat, 26 Apr 2008 21:50:10 -0600 "Scott Marlowe" <[EMAIL PROTECTED]> wrote: > Agreed. My point was that to do what the OP wants, wouldn't it make > more sense to just lobotomize libpq so it doesn't understand > anything but prepared queries. Doesn't obviate the need for a > client side language based solution. Just seems to make WAY more > sense than trying to make the change at the server level in pgsql. The problem may be legacy code. You'd like to statically point out places where multiple statements could get injected. All calls to your "query" function get routed to a wrapper that actually call prepare/execute logic. You do a BIG search&replace and see where your code fail cos you actually needed more than one statement in a query. Now you just have to grep your code for direct call to "plain" queries during commit of your rcs. My proposal was to add a switch that force routing to prepared statement logic in libpq. I'm thinking about situation in which you're using a library that already wrap your query call. You don't want to change the wrapper, so you don't want to take the responsibility, sync troubles etc... of the library maintainer but still you'd like to add a safety net to your code. People dealing with your code would still see the familiar library wrapper (you're not wrapping the wrapper) but you'd be able to switch to "single statement mode". Still ALLOW_LITERALS is a nice feature even if I think it won't fix the notorious SQL injection problem forever. Since it is going to make dev nervous because it adds code bloat that's going to cause more bugs than the SQL injections it may prevent. Once you've developers that are so patient to write stuff like: "select a.id, b.name from a join b on b.id=a.id where a.status='pending' and b.id>7 and b.status='logged'" into "select a.id, b.name from a join b on b.id=a.id where a.status=? and b.id>? and b.status=?", 'pending', 7, 'logged' there are high chances they will prefer to spend some of their time actually thinking about what they are writing. I do know that thinking can't be taken for granted and that habits and automatic methods may be generally preferable to "thinking", but automatic methods works when they don't look painful. Prepared statements force you to match input with position and it is definitively error prone. It is a tool... you may have some section of your code where that parameter can't be changed, but most of the time you'll find it useful if its default is set to NONE and dev *can* change it. Now... let's think at the poor programmer... He is writing a SQL statement that is static. He has to disable ALLOW_LITERALS. He is writing dynamic SQL that DON'T take user input. ALLOW_LITERALS may still have some sense as a debugging tool but there are high chances he will disable it to avoid other errors and make coding simpler. He is writing dynamic SQL that does take user input. He should be forced to use ALLOW_LITTERALS NONE. But how can he be forced in the middle of a program? He is writing a "mixed" statement where some input is actually static but not all... he may think it is easier to allow literals. Everything is still in the hands of the dev. Such setting may help you in static code evaluation since you may spot easier the places where there could be breach of policy... but still unless you want to make your dev life a hell... it is not going to solve the SQL injection problem. "mixed" statements that use external input and static input are quite common and writing them avoiding literals may be a pain that your dev won't be willing to suffer. Queued statements in one query are far less common. Still I do think that ALLOW_LITERAL is a valuable tool. Same problems for legacy code apply. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Protection from SQL injection
On Sun, 27 Apr 2008 12:38:48 +0200
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote:
> Once you've developers that are so patient to write stuff like:
>
> "select a.id, b.name from a join b on b.id=a.id where
> a.status='pending' and b.id>7 and b.status='logged'"
>
> into
>
> "select a.id, b.name from a join b on b.id=a.id where
> a.status=? and b.id>? and b.status=?", 'pending', 7, 'logged'
>
> there are high chances they will prefer to spend some of their time
> actually thinking about what they are writing.
[snipped here and there]
> Prepared statements force you to match input with position and it is
> definitively error prone.
It'd be nice to have a wrapper that let you write prepared statements
this way:
"select a.id, b.name from a join b on a.id=b.id where
a.status=$variable1 and b.id>$variable2 etc... but that's a pretty
good change to any language parser.
Maybe it could be obtained by use of macro...
That will turn
new_query("select a.id, b.name from a join b on a.id=b.id where
a.status=$variable1 and b.id>$variable2");
into
pg_prepare('anonymous', 'select a.id, b.name from a join b on
a.id=b.id where a.status=$1 and b.id>$2');
pg_execute('anonymous',array($variable1,$variable2));
but aren't macro evil
Still it would be handy.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Protection from SQL injection
On Sun, 27 Apr 2008 11:55:18 -0400 Joe <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo wrote: > > It'd be nice to have a wrapper that let you write prepared > > statements this way: > > > > "select a.id, b.name from a join b on a.id=b.id where > > a.status=$variable1 and b.id>$variable2 etc... but that's a pretty > > good change to any language parser. > Python already supports something like that. See PEP 249 > (http://www.python.org/dev/peps/pep-0249/), under Module Interface, > the description of the paramstyle parameter. Psycopg2 supports both > the "format" (C printf) and "pyformat" styles. See the last section > on this page for an example using the pyformat style: > http://www.devx.com/opensource/Article/29071/0/page/3. That's better than nothing but it is still a lot of code duplication. You've to write column names in the sql statement and in the array and... column values are not contextual to the statement. That's easy... while what I wrote above does look as requiring a really special parser. Furthermore from the example it looks as if all this is going to miss the scope to prevent sql injection since it doesn't support prepared statements. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] function returning result set of varying column
On Tue, 3 Jun 2008 09:01:02 -0400 "maria s" <[EMAIL PROTECTED]> wrote: > Hi Friends, > Thanks for all your for the reply. > > I tried the function and when I execute it using > select * from myfunction() > it says > ERROR: a column definition list is required for functions > returning "record" > > Could you please help me to fix this error? > > Thanks so much for your help. you can specify the returned types in each statement that call your function or you can specify the returned type in the function itself. CREATE OR REPLACE FUNCTION myfunction(out col1 int, out col2 varchar(32), out ...) RETURNS SETOF RECORD AS $body$ DECLARE rec record; BEGIN FOR rec IN ( SELECT * FROM sometable) LOOP col1:=rec.col1; col2:=rec.col2; --col3:=...; RETURN NEXT; END LOOP; RETURN; END; $body$ > > CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS > > $body$ > > DECLARE > > rec record; > > BEGIN > > FOR rec IN ( > > SELECT * FROM sometable) > > LOOP > > RETURN NEXT rec; > > END LOOP; > > RETURN; > > END; > > $body$ -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] function returning result set of varying column
On Tue, 3 Jun 2008 09:41:27 -0400 "maria s" <[EMAIL PROTECTED]> wrote: > Thanks for all your replies. > > Actually I don't know the number of columns that I am going to > return. > > I have 2 tables. For a single entry E1 in one table(t1), I have > to fetch all the matching entries for E1 from the other > table(t2), K1,..Kn. and finally the function should return E1, > K1..Kn. So I don't know the number of columns that I am going to > get. > > Is it possible to write a function that returns this kind of > result? Up to my knowledge as Bart wrote in pl/pgsql you'll have to specify somewhere the return type: in the function or in the select calling the function. If you use sql (not pl/pgsql) function you shouldn't be obliged to specify the return type. But I haven't written enough sql function to actually remember how it works. If you post your tentative sql it could give us more clue. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] function returning result set of varying column
On Tue, 3 Jun 2008 10:06:45 -0400 "maria s" <[EMAIL PROTECTED]> wrote: > Hi Ivan, > If I have to know the column names then I can't use the Functions. > As I said before, the columns will vary. or As Pavel Stehule said > I will use arrays. > > Is anyone can show an example of returning a record with string > and array? What I meant was that with "pure" sql functions you shouldn't have that constraint... wrapping a select in a function create or replace function fsql() as $$ begin select * from mytable; end; $$ language sql; should work. SQL functions are less flexible than pg/plsql but maybe you can live with it. SQL functions still support conditionals etc... you'd have to see if what SQL functions offer is enough for your need. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] selecting N record for each group
There are many ways to achieve this: http://rickosborne.org/blog/index.php/2008/01/07/sql-getting-top-n-rows-for-a-grouped-query/ http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/More-SQL-Server-2005-Solutions.aspx I was wondering if any of the available methods perform better on postgresql: The actual situation is: create table type( id int primary key, name varchar(32) ); create table list( id int primary key, name varchar(32), type int references type (id), inserted timestamp ); I'd like to get the type.name, list.name of the N newest items for a set of types (eg. where type.id in (1,2,4)). the list of types I'm interested in is composed of 20-100 types on 10K distinct types. list table has 1M record N is in the order of 5-20. I could use plpgsqql too but I'm not sure it could make any better compared to plain sql. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] COPY equivalent for updates
Is there a COPY equivalent for updates? eg I've create table t1 ( id int primary key, col1 int, col2 int, col3 varchar(32) ); and a CSV file 10,4,5,"abc" 13,7,3,"def" 18,12,77,"ghi" I'd like to UPDATE t1 (col1, col2, col3) from file with @1 as primary key; or UPDATE t1 (col1, col2, col3) from file where @1=id; sort of... Otherwise what is the fastest approach? I can think of 2 approaches: 1)load a temp table with COPY update t1 set col1=temp_t1.col1, col2=temp_t1.col2 where t1.id=temp_t1.id; 2) use awk to generate update statements. Supposing I could neglect the awk execution time, will COPY + UPDATE be faster than executing a list of UPDATE? Considering I've to deal with a where clauses anyway... when (and if) should I create an index on the id of temp_t1? t1 will contain 700-1M records while I may update a maximum of 20K a time. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] integrity check and visibility was: COPY equivalent for updates
On Tue, 15 Jul 2008 22:13:42 -0500 Decibel! <[EMAIL PROTECTED]> wrote: > Depending on what you're doing, it might well be fastest to... > BEGIN; > COPY temp_table FROM 'file'; > DELETE FROM real_table WHERE real_table_id IN (SELECT > real_table_id FROM temp_table); > INSERT INTO real_table SELECT * FROM temp_table; > COMMIT; Thank you for making me reconsider this approach. I gave up since I have a lot of ON DELETE CASCADE... but you made me remind that there should be a way to delay the checks at the end of the transaction (I still couldn't find the syntax on the manual). Actually from my understanding an UPDATE in PostgreSQL is a DELETE + INSERT. But wouldn't the subquery DELETE FROM real_table WHERE real_table_id IN (SELECT real_table_id FROM temp_table); take longer to execute than the UPDATE version: update t1 set col1=temp_t1.col1, col2=temp_t1.col2 where t1.id=temp_t1.id; mutatis mutandis? I'd say that pg should be enough smart to take advantage of a unique key on real_table.real_table_id and stop searching as soon as it deleted the first occurrence of an ID found in real_table from temp_table. Is it? Would adding an unique index on temp_table make the process faster or slower? I know that temp_table.real_table_id will be unique so I'm not interested in the integrity check (that anyway should take place later). At first glance the unique index on real_table.real_table_id may be useful, the one on temp_table should not and will actually make the process slower adding index creation time. So the "ON DELETE CASCADE/SET NULL" problem should be solved once I find the syntax to delay checks Can it be solved this way? I'm going to check as soon as I find the syntax on the manual. What about visibility of the "going to be deleted rows"? If another transaction start between the DELETE and the INSERT statement are executed... what is it going to see? What if I don't have just a single INSERT statement since some columns will be moved to another table? Does this make necessary the UPDATE approach in spite of the DELETE/INSERT approach? If I had temp_table split into 2 tables eg. create temp_table( id int, col11 varchar(32), col21 varchar(32) ); create table table1 ( id int primary key, col11 varchar(32) ); create table table2 ( id int references table1(id) on delete cascade, col21 varchar(32) ); I wouldn't be concerned that data in table1 and table2 contains data updated in different times and "not coherent" rather I'd be concerned there is a row in table1 but there isn't in table2. Furthermore... if I delay checks and ON DELETE triggers rows in table2 won't be deleted when I delete rows in table1 unless I do it "manually". At this point I'm more concerned of maintainability over speed. Actually if I follow the UPDATE approach and temp_table is still split across several tables I'll have to write an update for each tableN anyway... so there shouldn't be too much difference between the UPDATE and the DELETE/INSERT approach. What about the effect of a longer transaction compared to a list of updates on speed? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Dynamic SQL Syntax help please
On Thu, 4 Sep 2008 13:41:16 -0700 "Ruben Gouveia" <[EMAIL PROTECTED]> wrote: > I created a simple function that i want to use another function > (in the same schema) that will be used to create a sql statement. > I get the following error when i try this: > > *ERROR: syntax error at or near "EXEC" at character 1 > QUERY: EXEC SQL EXECUTE $1 INTO $2 > CONTEXT: SQL statement in PL/PgSQL function "fcn_population" near > line 10 > EXEC SQL EXECUTE v_stmt INTO v_total_pop; Could it be you're mixing MS SQL (Oracle?) syntax with pgplsql syntax EXECUTE v_stmt INTO v_total_pop; http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] some howto/theory book/tutorial on practical problem solving in SQL
I'm looking to some book/tutorial/sample code that will teach me how to use SQL to solve some standard problem that goes a bit beyond using group by and aggregates. Something like "SQL problem solving" or "SQL design strategies for selected problems". My current problem is how to manage discounts in SQL, inside transactions. Specifically how to "delete" promotions if they are overlapping, considering I have to display discounted prices on a 1M article DB and I may have hundreds of promotions running and they may involve even 10% of the catalogue. But this is just the beginning. I bet I'll have different set of problems later. Online tutorial/howto or reference to Open Source programs that really exploit SQL (and not python, php, ruby) to deal with discounts would be very welcome. eg. I did find very educational to look in the code of GNUMed in the past. Actually a reference to some Open Source software that does discount management at the DB level would be a very good pointer. And of course I'm using postgresql and even if DB agnostic techniques will be welcome I don't need to be DB neutral, I'm working on postgresql. I skimmed through Celko books and at the moment they seems the nearest thing to what I'd like to learn even if too much "theoretical" at the moment. O'Reilly "SQL cookbook" is another example of the kind of stuff I'm looking for... but the examples are more like ingredients then recipes. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] some howto/theory book/tutorial on practical problem solving in SQL
On Sun, 11 Jan 2009 11:19:19 -0700 "Scott Marlowe" wrote: > On Sun, Jan 11, 2009 at 9:32 AM, Ivan Sergio Borgonovo > wrote: > > My current problem is how to manage discounts in SQL, inside > > transactions. Specifically how to "delete" promotions if they are > > overlapping, considering I have to display discounted prices on > > a 1M article DB and I may have hundreds of promotions running > > and they may involve even 10% of the catalogue. But this is just > > the beginning. > > I skimmed through Celko books and at the moment they seems the > > nearest thing to what I'd like to learn even if too much > > "theoretical" at the moment. O'Reilly "SQL cookbook" is another > > example of the kind of stuff I'm looking for... but the examples > > are more like ingredients then recipes. > I highly recommend Celko's SQL books. They may seem too > theoretical, but they have a lot of good information I found > myself reusing all the time when I first started out. I was a OK... let's be more clear... they seems the books I'd surely read if I had 1 month to find a proper solution to my problems and become a better programmers for the years to come. Actually from a quick glance they are definitively more practical than a book on sets algebra ;) and among all the books I had a chance to skim they really look the ones that could be useful and they have a good chance to find a permanent place in my bookshelf. Just not to misguide people. If I had other 3 months I'd pick up Date's book. I just remember now I gave a look to a .*postgres.*ecommerce book but while it seems to me it could be a good book for learning some practical postgresql and start to know the tool it was not the kind of book that I'd title "how to solve this in SQL". I just saw there is a "Sql Puzzles" by Celko that looks promising. I was able to see the first 2 puzzles on Google books. Now I'm downloading all the Open Source ecommerce apps I could find... but having done this before I think none of them will really exploit the DB and will rely mostly on php. I think Open Source accounting [1] programs may have more chances to teach me something about the problem I'd like to solve since they tend to be less mysqlish. If anyone know an accounting Open Source program that really exploit postgresql as an engine I'd be glad to look at the source. > little disconcerted by his resemblence to Anton Lavie (sp) at > first though. http://images.google.com/images?q=Joe+Celko http://en.wikipedia.org/wiki/Anton_Szandor_LaVey I think I'm going to read some Teach yourself SQL in 1fs by Laura Lemay if I want to keep sleeping at night or I may enrol myself in Milingo's army. If I had eternity I wouldn't be so worried about performances so I think I could finish War and Peace. I just found an answer by Celko to a problem similar to mine here: http://www.eggheadcafe.com/software/aspnet/31256828/calculate-discount-by-cat.aspx [1] accounting? just a tool that deals with discounts and an inventory. What would be the right term to search for in google? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] some howto/theory book/tutorial on practical problem solving in SQL
On Tue, 13 Jan 2009 22:18:32 -0800
Erik Jones wrote:
>
> On Jan 11, 2009, at 8:32 AM, Ivan Sergio Borgonovo wrote:
>
> > I'm looking to some book/tutorial/sample code that will teach me
> > how to use SQL to solve some standard problem that goes a bit
> > beyond using group by and aggregates.
> >
> > Something like "SQL problem solving" or "SQL design strategies
> > for selected problems".
> O'Reilly's SQL Hacks is a good one that fits the bill you describe.
Thanks.
I think it is complementary to Celko's SQL puzzles.
O'Reilly's book seems techniques on the field.
Celko's book seems design on the field.
Elsevier seems to make it difficult to buy ebooks in a convenient
way.
I think I'm going to buy 'SQL hacks'. It still seems a good
investment.
Meanwhile I downloaded all the ecommerce, billing and ERP Open Source
programs I know (over 60) and started to examine the code to see
how they manage discounts in SQL.
The one that seems more promising to learn from seems:
ofbiz
webERP
promogest
You can find the most flexible discount management in the ecommerce
programs but they do it in the client language (php) rather than in
sql and most (all?) the time in a way that's not compatible with a
1M items catalog and that is not atomic when you close the order.
$result=db_query("select [some items from catalogue]");
while($row=db_fetch_array($result1) {
//some logic
$result2=db_query("select [some extra data about item]");
//compute discount
The billing/ERP programs have a more B2B approach so it seems that
marketing is not among their priorities.
They end up in having stuff like
create table prices(
itemid int references items (itemsid),
discountclass int references discountclient (classid),
price numeric
)
But no way to mass apply/revoke overlapping discounts.
I'll come back asking comments on a possible design to solve my
problem later.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dynamic OUT parameters?
On Sun, 01 Feb 2009 12:42:12 +0900 Craig Ringer wrote: > [email protected] wrote: > > > Well, aparenty my problem remains, because the boss want that the > > programmers just need to call > > select * from report('table_name'). > > Then, AFAIK, you're not going to have much luck, as Pg needs to > know the columns that'll be output before the function is called. > Applications and DB access interfaces also usually also need to > know the column list beforehand. > > If you return a refcursor you can at least use: > > SELECT report('table_name'); > FETCH ALL IN 'cursorname'; > > The documentation has a more full example: > > http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html#AEN40465 > > Personally, I find it difficult to imagine what could be wrong > with that. I've followed this thread with interest. I'm starting to manage larger and larger code base of plpsql. What I find hard to achieve is code reuse and implication of some techniques to performances. I didn't see many examples that exploit cursors around. Does anyone have some pointer to some more enlightening use of cursors for code reuse and OOP? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] row not deleted but updated (trigger?)
I've:
create or replace function FT1IDX_catalog_brands_update() returns
trigger as $$
begin
if(TG_OP='DELETE') then
update catalog_items set
FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name,
Authors, '')
where BrandID=old.BrandID;
else
if(coalesce(new.Name,'')<>coalesce(old.Name,'')) then
update catalog_items set
FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN,
Name, Authors, new.Name)
where BrandID=new.BrandID;
end if;
end if;
return new;
end $$ language plpgsql volatile;
create trigger FT1IDX_catalog_brands_update_trigger before update or
delete on catalog_brands for each row execute procedure
FT1IDX_catalog_brands_update();
I do something
update catalog_brands set name='zzz' where brandid=1234;
1 row get updated.
When I do
delete from catalog_brands where brandid=1234;
no row get deleted and no error get reported.
what did I miss?
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] row not deleted but updated (trigger?)
On Fri, 27 Feb 2009 12:56:06 +0100
Ivan Sergio Borgonovo wrote:
> I've:
>
> create or replace function FT1IDX_catalog_brands_update() returns
> trigger as $$
> begin
> if(TG_OP='DELETE') then
> update catalog_items set
> FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN,
> Name, Authors, '')
> where BrandID=old.BrandID;
> else
> if(coalesce(new.Name,'')<>coalesce(old.Name,'')) then
> update catalog_items set
> FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN,
> Name, Authors, new.Name)
> where BrandID=new.BrandID;
> end if;
> end if;
> return new;
> end $$ language plpgsql volatile;
umpfs...
on delete new is null... so no action take place.
modified to return old on delete and new for the rest.
Sorry for the noise.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL]
On Thu, 5 Mar 2009 11:48:17 -0400
Jhonny Velasquez c. wrote:
>$result=pg_query( $linkacademica,"SELECT personas.nombres,
> personas.paterno, s.direccion, s.telefono, FROM personas INNER
^
> JOIN dblink('dbname=bdsiap port=5432 host=localhost user=usuario
> password=clave,'SELECT idpersona, direccion, telefono FROM
> personasdatosactualizables') AS s(idpersona character(15),
> direccion character varying(50), telefono character varying(25))
> ON personas.idpersona = s.idpersona");
Please don't cross post and this is supposed to be an English list
(am I right?).
Most of the people here will see your post as noise here.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] join
I'm trying to get rid of the side effect of the left join catalog_promosimple That means I'm expecting at least one row no matter if there is an entry in left join catalog_promosimpleitem but I don't want "duplicates" from catalog_promosimple create table catalog_promosimple ( PromoSimpleID int primary key, IsPromo boolean not null default true, Discount numeric(19,4) not null default 0 ); create table catalog_promosimpleitem ( PromoSimpleID int references catalog_promosimple (PromoSimpleID), ItemID int references catalog_items (ItemID) ); so eg. I've 1,true 2,false 1,1,10 1,1,20 2,1,0 2,1,5 when I'm looking for ItemID=1 the query should return: ItemID,Discount 1,20 and skip the entries with IsPromo=false If I've just 2,false 2,1,0 2,1,5 the query should return: ItemID,Discount 2,null If there are no items at all in catalog_promosimpleitem (but there is a corresponding one in catalog_items eg. ItemID=5) the query should return: ItemID,Discount 5,null select i.ItemID as _ItemID, i.Code as _Code, i.ISBN as _ISBN, i.CodeAlt as _CodeAlt, i.Name as _Name, i.ListPrice as _ListPrice, DiscountedPrice( i.qty, i.StatusID, max(pi.Discount), p.Percent, p.OnListPrice, p.Vendible, p.OnStock,i.ListPrice, ip.Price ) as _DiscountedPrice, i.valIva as _Tax, i.StatusID as _StatusID, i.qty as _qty, b.Name as _Brands, i.BrandID as _BrandID, i.Authors as _Authors, b.Delivery as _Delivery, extract(year from i.dataPub) as _YearPub, s.FamID as _FamID, st.Name as _SName from catalog_items i join catalog_categoryitem s on i.ItemID=s.ItemID join catalog_item_status st on st.StatusID=i.StatusID left join catalog_brands b on b.BrandID=i.BrandID left join catalog_itemprice ip on ip.ItemID=i.ItemID and ip.DiscountClass=10 left join catalog_promosimpleitem pi on pi.ItemID=i.ItemID left join catalog_promosimple p on pi.PromoSimpleID=p.PromoSimpleID and p.PromoStart=now() and p.IsPromo=true where i.ItemID=102020 group by i.ItemID, i.Code, i.ISBN, i.CodeAlt, i.Name, i.ListPrice, i.qty, i.StatusID, p.Percent, p.OnListPrice, p.Vendible, p.OnStock, ip.Price, i.valIva, b.Name, i.BrandID, i.Authors, i.dataPub, s.FamID, st.Name, b.Delivery -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] 2 left join taking in too many records, 1 join and 1 left join too few was: join
first, sorry for the subject. I started editing the body and I forgot the subject looked nearly meaningless. I solved this using a view on catalog_promosimpleitem and catalog_promosimple that just take out the record I'm interested into. If no record, the left join on the view should make it work anyway as expected. I still don't like the overall solution, but at least it makes a more reasonable starting point to clean some mess and having a list of IsPromo around ready will come handy in other places. On Tue, 17 Mar 2009 22:40:08 +0100 Ivan Sergio Borgonovo wrote: > I'm trying to get rid of the side effect of the > > left join catalog_promosimple > > That means I'm expecting at least one row no matter if there is an > entry in > left join catalog_promosimpleitem > but I don't want "duplicates" from > catalog_promosimple > > create table catalog_promosimple ( > PromoSimpleID int primary key, > IsPromo boolean not null default true, > Discount numeric(19,4) not null default 0 > ); > create table catalog_promosimpleitem ( > PromoSimpleID int references catalog_promosimple (PromoSimpleID), > ItemID int references catalog_items (ItemID) > ); > > so eg. I've > > 1,true > 2,false > > 1,1,10 > 1,1,20 > 2,1,0 > 2,1,5 > > when I'm looking for ItemID=1 the query should return: > ItemID,Discount > 1,20 > and skip the entries with IsPromo=false > > If I've just > > 2,false > > 2,1,0 > 2,1,5 > > the query should return: > ItemID,Discount > 2,null > > If there are no items at all in catalog_promosimpleitem (but there > is a corresponding one in catalog_items eg. ItemID=5) > the query should return: > ItemID,Discount > 5,null > > > select i.ItemID as _ItemID, i.Code as _Code, i.ISBN as _ISBN, > i.CodeAlt as _CodeAlt, i.Name as _Name, > i.ListPrice as _ListPrice, > DiscountedPrice( > i.qty, i.StatusID, max(pi.Discount), > p.Percent, p.OnListPrice, p.Vendible, p.OnStock,i.ListPrice, > ip.Price ) > as _DiscountedPrice, > i.valIva as _Tax, > i.StatusID as _StatusID, i.qty as _qty, b.Name as _Brands, > i.BrandID as _BrandID, i.Authors as _Authors, > b.Delivery as _Delivery, > extract(year from i.dataPub) as _YearPub, > s.FamID as _FamID, st.Name as _SName from catalog_items i > join catalog_categoryitem s on i.ItemID=s.ItemID > join catalog_item_status st on st.StatusID=i.StatusID > left join catalog_brands b on b.BrandID=i.BrandID > left join catalog_itemprice ip on ip.ItemID=i.ItemID > and ip.DiscountClass=10 > left join catalog_promosimpleitem pi on pi.ItemID=i.ItemID > left join catalog_promosimple p on > pi.PromoSimpleID=p.PromoSimpleID and p.PromoStart p.PromoEnd>=now() and p.IsPromo=true where i.ItemID=102020 > group by i.ItemID, i.Code, i.ISBN, i.CodeAlt, > i.Name, i.ListPrice, i.qty, i.StatusID, p.Percent, p.OnListPrice, > p.Vendible, p.OnStock, ip.Price, i.valIva, b.Name, i.BrandID, > i.Authors, i.dataPub, s.FamID, st.Name, b.Delivery > -- Ivan Sergio Borgonovo http://www.webthatworks.it - Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Obtaining a limited number of records from a long query
On Mon, 25 May 2009 14:55:54 +0100 "Oliveiros Cristina" wrote: > > > Thanks a million, Andy. > > It's precisely what I needed, Indeed! Beware: unless you specify an ORDER BY (and you expect no other record is inserted between one query and the other... or you just don't care) you may incur in unexpected results. A query without an order by is not granted to return the result in the same order. If you can (same session) you may use cursors. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Obtaining a limited number of records from a long query
On Mon, 25 May 2009 15:20:57 +0100 "Oliveiros Cristina" wrote: > Thank you for pointing it out, Ivan. > The query I am trying to restrict output for happens to be an > ORDER BY query. > Actually I wasn't aware of this detail. > I'll leave a mental note for myself that results might be > unexpected for not ORDER BY queries a bit more about "unexpected": - if people insert other rows... and by chance they happen to fall before the ones you already retrieved... no matter of the ORDER BY, you may "miss" them in the "paginated result". - without an order by there is no "granted" order. This is not a deficiency of PostgreSQL, it is not mandated by sql standard and it offers chances for optimisation The fact that without an order by clause they may return in the same order... is implementation dependent and Postgresql don't behave that way or it happens just by chance. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
