[SQL] \COPY in psql using \e

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 (pgsql-sql@postgresql.org) 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_i

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:

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, >

[SQL] LTREE extension and "order by"

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 v

Re: [SQL] Postgres function for full text search

ave 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 (

Re: [SQL] short-cutting if sum()>constant

s 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 ht

Re: [SQL] short-cutting if sum()>constant

s 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://w

Re: [SQL] short-cutting if sum()>constant

y 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 bu

Re: [SQL] short-cutting if sum()>constant

ir 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

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

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: > >

Re: [SQL] short-cutting if sum()>constant

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 Bor

Re: [SQL] short-cutting if sum()>constant

gsql 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 (pgsql-sql@postgresql.org) 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 (pgsql-sql@postgre

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

[SQL] get distinct + group by then filter

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

Re: [SQL] Random Unique Id

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 (pgsql-sql@postgresql.org) To make changes to your sub

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

[SQL] .psql_history": No such file

h 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 (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] 2 tables or two db?

db? may be it is a stupid question, but many many thanks... Ivan -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) 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 no

Re: [SQL] Obtaining a limited number of records from a long query

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 (pgsql-sql@post

[SQL] 2 left join taking in too many records, 1 join and 1 left join too few was: join

ke 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&#x

[SQL] join

, 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 (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL]

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 (pgsql-sql@postgresql.org) 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_c

[SQL] row not deleted but updated (trigger?)

elete 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 (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] dynamic OUT parameters?

ong > 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 aroun

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 > > beyo

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 a

[SQL] some howto/theory book/tutorial on practical problem solving in SQL

. 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 lik

[SQL] store pdf files

ed 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 c

Re: [SQL] Dynamic SQL Syntax help please

) 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 (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] integrity check and visibility was: COPY equivalent for updates

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 (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] COPY equivalent for updates

ents. 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 ma

[SQL] selecting N record for each group

#x27;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 (pgsql-sql@postgresql.org) 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 ca

Re: [SQL] function returning result set of varying column

lly 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 (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] function returning result set of varying column

> 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; > >

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 >

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.sta

Re: [SQL] Protection from SQL injection

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 o

Re: [SQL] Protection from SQL injection

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 t

[SQL] advocacy: case studies

e-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." ?

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 > >

[SQL] update with join

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 (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Export Access 97 to PostgreSQL

t 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 "

[SQL] foreign key problem

iberi. 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 Perugi

[SQL] date problems

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, .dw

[SQL] insert a sequence

ri. 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 (Ita

[SQL] join a lot of columns of two tables

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 -

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 &q

Re: [SQL] drop a 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 t

[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 thank

Re: [SQL] could not connect to server

d 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 +020

[SQL] could not connect to server

cket 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)

[SQL] simple problem???

2 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

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 va

Re: [SQL] how to solve this problem

resentation 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

[SQL] how to solve this problem

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

[SQL] COPY tablename FROM and null values

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 E

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: > > >

[SQL] newbie question

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

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] 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 n

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

[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

[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] 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