[SQL] Distinct Values
Hi, I'm trying to retreive DISTINCT Values from a two colomn table called "Books". The colomns are named "Author" and "URL". DISTINCT values should be retieved from the "Author" Colomn , and then I should be able to retrieve the corresponding URL. How do I build the SQL Query ? I tried to use: "SELECT DISTINCT Author FROM Books" But this does not allow me to fetch the URL value on the other colomn. Thanks Nicolas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Protection of tables by group and not by users
Hello, Is it possible to protect a table of Postgresql by a group of persons instead of giving a list of persons ? Thanks for your answers Regards nicolas michaud
[SQL] Let join syntax
Hi, I've a problem with the following query. I want to left join table sec (x5) with x0, and x4. I try the following query : select x0.coh_agent ,x0.bra_id ,x0.dpr_id ,x0.usr_id ,x0.csc_id ,x0.spp_id ,x0.csc_id_inv ,x0.coh_doc_inv ,x0.coh_d_inv , x0.coh_process ,x0.coh_doc_main ,x0.coh_status ,x0.coh_total_local ,x0.coh_basis_local ,x0.coh_cost_local ,x0.coh_profit_local ,x0.coh_over_local ,x0.coh_com_earned ,x0.coh_com_adjust ,x0.coh_com_held ,x0.coh_com_cancel ,x0.coh_com_topay ,x0.coh_d_paid ,x0.coh_matchname ,x0.coh_cscmatch ,x0.coh_com_paid ,x3.cur_dec_nb ,x3.cur_location ,x3.cur_negative ,x3.cur_dec_char ,x3.cur_group_char ,x5.sec_id ,x1.cpy_id ,x1.cpy_cr_tr ,x1.cpy_cr_tg ,x1.cpy_cr_tb ,x2.bra_screen ,x2.bra_id ,x2.lng_id ,x4.tad_cpy_name from coh x0 , cpy x1 ,bra x2 , cur x3 ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND x5.usr_id=x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, dpr x6 where ((x0.cpy_id = x1.cpy_id ) AND (x0.bra_id = x2.bra_id ) ) AND (x0.cur_id = x3.cur_id ) ) AND (x0.dpr_id = x6.dpr_id ) ) AND (x2.tad_id = x4.tad_id ) ) AND (x5.thr_id = x4.thr_id ) ) AND (x2.bra_id = x6.bra_id ) ) AND (x5.usr_id = x0.usr_id ) ) AND (x5.bra_id = x0.bra_id ) ) AND (x5.dpr_id = x0.dpr_id ) Unfortunatelly, postgres returns me the following error : Error: ERROR: Relation "x0" does not exist (State:S1000, Native Code: 7) I tried to modify the FROM sentence by: FROM (coh x0 , cpy x1 ,bra x2 , cur x3 ,tad x4 ) LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND x5.usr_id=x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, but I get a syntax error , may be due to parentheses. Does anyone knows how to write this query correctly ? Regards, Nicolas. ------- Nicolas JOUANIN - SA REGIE FRANCE Village Informatique BP 3002 17030 La Rochelle CEDEX Tel: 05 46 44 75 76 Fax: 05 46 45 34 17 email: [EMAIL PROTECTED] Web : www.regie-france.com --- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Let join syntax
Hi, select x0.coh_agent ,x0.bra_id ,x0.dpr_id ,x0.usr_id ,x0.csc_id ,x0.spp_id ,x0.csc_id_inv ,x0.coh_doc_inv ,x0.coh_d_inv , x0.coh_process ,x0.coh_doc_main ,x0.coh_status ,x0.coh_total_local ,x0.coh_basis_local ,x0.coh_cost_local ,x0.coh_profit_local ,x0.coh_over_local ,x0.coh_com_earned ,x0.coh_com_adjust ,x0.coh_com_held ,x0.coh_com_cancel ,x0.coh_com_topay ,x0.coh_d_paid ,x0.coh_matchname ,x0.coh_cscmatch ,x0.coh_com_paid ,x3.cur_dec_nb ,x3.cur_location ,x3.cur_negative ,x3.cur_dec_char ,x3.cur_group_char ,x5.sec_id ,x1.cpy_id ,x1.cpy_cr_tr ,x1.cpy_cr_tg ,x1.cpy_cr_tb ,x2.bra_screen ,x2.bra_id ,x2.lng_id ,x4.tad_cpy_name from coh x0 INNER JOIN bra x2 ON x0.bra_id = x2.bra_id INNER JOIN tad x4 ON x2.tad_id = x4.tad_id LEFT JOIN sec x5 ON x5.thr_id = x4.thr_id AND x5.usr_id = x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, cpy x1 , cur x3 ,dpr x6 where x0.cpy_id = x1.cpy_id AND x0.cur_id = x3.cur_id AND x0.dpr_id = x6.dpr_id AND x2.bra_id = x6.bra_id worked correctly Thanks. > -Message d'origine- > De : Manfred Koizar [mailto:[EMAIL PROTECTED] > Envoye : lundi 16 juin 2003 16:40 > A : Nicolas JOUANIN > Cc : [EMAIL PROTECTED] > Objet : Re: [SQL] Let join syntax > > > On Mon, 16 Jun 2003 12:34:34 +0200, "Nicolas JOUANIN" > <[EMAIL PROTECTED]> wrote: > >from > >coh x0 , cpy x1 ,bra x2 , > >cur x3 ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND > >x5.usr_id=x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, > >dpr x6 where ((x0.cpy_id = x1.cpy_id ) AND (x0.bra_id > >= x2.bra_id ) ) AND (x0.cur_id = x3.cur_id ) ) AND (x0.dpr_id > >= x6.dpr_id ) ) AND (x2.tad_id = x4.tad_id ) ) AND (x5.thr_id > >= x4.thr_id ) ) AND (x2.bra_id = x6.bra_id ) ) AND (x5.usr_id > >= x0.usr_id ) ) AND (x5.bra_id = x0.bra_id ) ) AND (x5.dpr_id > >= x0.dpr_id ) > > Nicolas, sometimes reformatting a query helps a lot: > > FROM > coh x0 , cpy x1 ,bra x2 , cur x3 , > tad x4 LEFT OUTER JOIN sec x5 ON > x5.thr_id = x4.thr_id AND > x5.usr_id = x0.usr_id AND > x5.bra_id = x0.bra_id AND > x5.dpr_id = x0.dpr_id, > dpr x6 > WHERE > x0.cpy_id = x1.cpy_id AND > x0.bra_id = x2.bra_id AND > x0.cur_id = x3.cur_id AND > x0.dpr_id = x6.dpr_id AND > x2.tad_id = x4.tad_id AND > x2.bra_id = x6.bra_id AND > x5.thr_id = x4.thr_id AND > x5.usr_id = x0.usr_id AND > x5.bra_id = x0.bra_id AND > x5.dpr_id = x0.dpr_id > > First note that the last four lines duplicate the ON conditions thus > effectively turning the OUTER JOIN into an INNER JOIN. As I suspect > that that was not your intention, simply omit those four conditions > from the WHERE clause. > > Now inserting INNER JOIN where the syntax forces us to do so leads to > (completely untested): > > FROM > coh x0 > INNER JOIN bra x2 ON x0.bra_id = x2.bra_id > INNER JOIN tad x4 ON x2.tad_id = x4.tad_id > LEFT JOIN sec x5 ON x5.thr_id = x4.thr_id AND > x5.usr_id = x0.usr_id AND > x5.bra_id = x0.bra_id AND > x5.dpr_id = x0.dpr_id, > cpy x1, cur x3, dpr x6 > WHERE > x0.cpy_id = x1.cpy_id AND > x0.cur_id = x3.cur_id AND > x0.dpr_id = x6.dpr_id AND > x2.bra_id = x6.bra_id > > You might feel like replacing the remaining commas in the FROM clause > and the corresponding WHERE conditions with semantically equivalent > INNER JOINs. But this limits the freedom of the planner which may be > a good or a bad thing... > > Servus > Manfred ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CREATE table1 FROM table2
Hi,
Documentation says:
CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [,
...] ) ]
AS query
example : create table table1 as select * from table2;
> -Message d'origine-
> De : [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] la part de Rado Petrik
> Envoyé : mardi 17 juin 2003 17:21
> À : [EMAIL PROTECTED]
> Objet : [SQL] CREATE table1 FROM table2
>
>
> Hi,
>
> How I create table1 from other table2 .
>
> "cp table1 table2"
>
> Thanks.
>
> --
> Rado Petrik <[EMAIL PROTECTED]>
>
>
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[SQL] TR: Like and =
Hi, I've got a table , pdi, with a field pro_id defined as char(25). One fied og this table contains the string '100058' plus spaces to fill the 25 length (ie pro_id = '100058 '). When I run: select * from pdi where pdi = '100058' the row is returned. When I run: select * from pdi where pdi like '100058' the row is NOT returned. select length(pro_id) where pdi = '100058' returns: length --- 25 2 Row(s) affected 1) In PostgreSQL documentation, it's said that without % wildcards like operates the same as = , it seems not. 2) Why does the = operator return the row ? it shouldn't because of the trailing spaces. 3) The row was inserted from the COPY command: COPY pdi FROM STDIN NULL as '' DELIMITER as '|'; VOL|100058|0|PART||PART \. Why does my field contain trailing spaces ? Regards and thanks again for your useful help. PS: create table pdi ( pmf_id char(4) not null , pro_id char(25) not null , lng_id char(3) not null , pdi_desc char(50) not null , pdi_instr text, pdi_matchdesc char(50), CONSTRAINT pk_pdi PRIMARY KEY (pro_id,pmf_id,lng_id) ); Nicolas. --- Nicolas JOUANIN - SA REGIE FRANCE Village Informatique BP 3002 17030 La Rochelle CEDEX Tel: 05 46 44 75 76 Fax: 05 46 45 34 17 email: [EMAIL PROTECTED] Web : www.regie-france.com --- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] TR: Like and =
Hi, Thanks for your help. In fact that means 2 solutions for this: 1) select * from pdi where rtrim(pdi) = '100058' or 2) Use VARCHAR instead of CHAR I don't which is the best , but both are working. Nicolas. > -Message d'origine- > De : Randall Lucas [mailto:[EMAIL PROTECTED] > Envoye : lundi 23 juin 2003 18:54 > A : Nicolas JOUANIN > Cc : [EMAIL PROTECTED] > Objet : Re: [SQL] TR: Like and = > > > > Hi Nicholas, > > CHAR fields, as opposed to VARCHAR, are blank-padded to the set length. > > Therefore, when you inserted a < 25 character string, it got padded > with spaces until the end. > > Likewise, when you cast '100058' to a CHAR(25) in the = below, it > gets padded, so it matches. > > The LIKE operator takes a pattern, and since your pattern did not > specify a wildcard at the end, it didn't exactly match the padded > string. > > This behavior does seem kind of confusing; in any case, it probably > argues for using varchar. > > Best, > > Randall > > > On Monday, June 23, 2003, at 12:29 PM, Nicolas JOUANIN wrote: > > > > > Hi, > > > > I've got a table , pdi, with a field pro_id defined as char(25). One > > fied > > og this table contains the string '100058' plus spaces to fill the > > 25 > > length (ie pro_id = '100058 '). > > When I run: > >select * from pdi where pdi = '100058' the row is returned. > > When I run: > >select * from pdi where pdi like '100058' the row is NOT > > returned. > > > > select length(pro_id) where pdi = '100058' returns: > > length > > --- > > 25 > > > > 2 Row(s) affected > > > > 1) In PostgreSQL documentation, it's said that without % wildcards like > > operates the same as = , it seems not. > > 2) Why does the = operator return the row ? it shouldn't because of the > > trailing spaces. > > 3) The row was inserted from the COPY command: > > COPY pdi FROM STDIN NULL as '' DELIMITER as '|'; > > VOL|100058|0|PART||PART > > \. > > Why does my field contain trailing spaces ? > > > > Regards and thanks again for your useful help. > > > > > > PS: > > create table pdi > > ( > > pmf_id char(4) not null , > > pro_id char(25) not null , > > lng_id char(3) not null , > > pdi_desc char(50) not null , > > pdi_instr text, > > pdi_matchdesc char(50), > > CONSTRAINT pk_pdi PRIMARY KEY (pro_id,pmf_id,lng_id) > > ); > > > > Nicolas. > > > > --- > > Nicolas JOUANIN - SA REGIE FRANCE > > Village Informatique BP 3002 > > 17030 La Rochelle CEDEX > > Tel: 05 46 44 75 76 > > Fax: 05 46 45 34 17 > > email: [EMAIL PROTECTED] > > Web : www.regie-france.com > > --- > > > > > > > > ---(end of > > broadcast)--- > > TIP 9: the planner will ignore your desire to choose an index scan if > > your > > joining column's datatypes do not match > > > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] TR: Like and =
In fact I'm trying to migrate a database from Informix IDS to Postgres. This IDS database uses CHAR so I just let the same. Now I'm conviced that I sould convert CHAR to VARCHAR. Thanks. > -Message d'origine- > De : [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] la part de Dani Oderbolz > Envoye : mercredi 25 juin 2003 14:19 > A : [EMAIL PROTECTED] > Objet : Re: [SQL] TR: Like and = > > > Nicolas JOUANIN wrote: > > >Hi, > > > > Thanks for your help. In fact that means 2 solutions for this: > > > >1) select * from pdi where rtrim(pdi) = '100058' > > > >or > > > >2) Use VARCHAR instead of CHAR > > > >I don't which is the best , but both are working. > > > >Nicolas. > > > > > > Do you have a specific reason why to use CHAR? > I use CHAR only for certain one-byte flags, and even there its use is > debatable. > I would use VARCHAR; if I was you. > > Cheers, > Dani > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Caracter é
Hello, I compiled Postgresql 8 on my server and installed it I am trying to insert a string using php and PEAR::DB The string contain: é and this cause $db-query to fail with an Unkown error as error message. If i insert the data using pgAdminIII the string is inserted into the table. I don't think it is Postgres related, do i need to ask to PEAR::DB team ? Thanks in adavance, CN ---(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
[SQL] unsuscribe
unsuscribe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] unsuscribe
unsuscribe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Special grouping on sorted data.
Hi, I was looking for a simple solution to this problem. I can't find a way to group on b and n by just collapsing sequential n's (identical n's right next to each other) the sorting condition is the timestamp. b| n | stamp A| 1 | 2008-09-20 06:07:47.981445 A| 1 | 2008-09-20 06:08:13.294306 A| 1 | 2008-09-20 06:12:02.046596 A| 2 | 2008-09-20 06:12:26.267786 A| 2 | 2008-09-20 06:12:47.750429 A| 1 | 2008-09-20 06:13:12.152512 A| 2 | 2008-09-20 06:13:39.052528 A| 2 | 2008-09-20 06:14:12.875389 B| 1 | 2008-09-20 06:14:29.963352 B| 1 | 2008-09-20 06:14:52.247307 B| 3 | 2008-09-20 06:15:13.358151 B| 3 | 2008-09-20 06:15:44.307792 B| 3 | 2008-09-20 06:16:17.32131 B| 2 | 2008-09-20 06:16:44.030435 B| 2 | 2008-09-20 06:17:00.140907 C| 1 | 2008-09-20 06:17:50.067258 C| 1 | 2008-09-20 06:18:22.280218 C| 1 | 2008-09-20 06:18:41.661213 C| 1 | 2008-09-20 06:19:07.920327 C| 3 | 2008-09-20 06:19:26.166675 C| 2 | 2008-09-20 06:19:46.459439 C| 2 | 2008-09-20 06:20:04.634328 Here I give an example of the output I'm looking for, And I can find a way to do that in crystal report, but I would like postgresql to send it that way. If the next n is different create a new row. b| n |min_stamp | max_stamp - A| 1 | 2008-09-20 06:07:47.981445 | 2008-09-20 06:12:02.046596 A| 2 | 2008-09-20 06:12:26.267786 | 2008-09-20 06:12:47.750429 A| 1 | 2008-09-20 06:13:12.152512 | 2008-09-20 06:13:12.152512 A| 2 | 2008-09-20 06:13:39.052528 | 2008-09-20 06:14:12.875389 B| 1 | 2008-09-20 06:14:29.963352 | 2008-09-20 06:14:52.247307 B| 3 | 2008-09-20 06:15:13.358151 | 2008-09-20 06:16:17.32131 B| 2 | 2008-09-20 06:16:44.030435 | 2008-09-20 06:17:00.140907 C| 1 | 2008-09-20 06:17:50.067258 | 2008-09-20 06:19:07.920327 C| 3 | 2008-09-20 06:19:26.166675 | 2008-09-20 06:19:26.166675 C| 2 | 2008-09-20 06:19:46.459439 | 2008-09-20 06:20:04.634328 If I write a GROUP BY b,n there is going to be only two rows for b = 'A', if I use distinct on i get the same thing is there an easy way to do that ? Nicolas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Special grouping on sorted data.
Craig Ringer wrote: b| n | stamp A| 1 | 2008-09-20 06:07:47.981445 [1] A| 1 | 2008-09-20 06:08:13.294306 [1] A| 1 | 2008-09-20 06:12:02.046596 [1] A| 2 | 2008-09-20 06:12:26.267786 [2] A| 2 | 2008-09-20 06:12:47.750429 [2] A| 1 | 2008-09-20 06:13:12.152512 [3] A| 2 | 2008-09-20 06:13:39.052528 [4] A| 2 | 2008-09-20 06:14:12.875389 [4] I'd be tempted to use a set-returning PL/PgSQL function to process an input set ordered by stamp and return a result whenever the (b,n) pair changed. I'm sure there's a cleverer set-oriented approach, but it's eluding me at present. You need a way to express the notion of "contiguous runs of (b,n)" which doesn't really exist in (set-oriented) SQL. The numbers you have next to each row is exactly what I'm looking for. You mention PL/PgSQL, I'm familiar with creating triggered procedures so I'll look into that I suspect that Crystal Reports may be pulling the whole data set from PostgreSQL then doing its processing client-side. Crystal report is running a simple pass through query that I wrote, select b.n.stamp from table where stamp order by stamp Then I use its grouping features, I group by b, then n but when I group by n I don't specify ascending or descending order but "in original order" And it ends up doing what I'm looking for. I which distinct on was more flexible, it's not happy when the order by set is different than the distinct on set. I would like to be able to write select distinct on (b,n) b,n,stamp from table where ... order by stamp; Nicolas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Special grouping on sorted data.
I which distinct on ... I wish distinct on ... -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
