Re: [SQL] Select clause in JOIN statement

På fredag 14. juni 2013 kl. 01:10:51, skrev Luca Vernini : It works. Also consider views. Just used this on a my db: SELECT * FROM tblcus_customer INNER JOIN ( SELECT * FROM tblcus_customer_status WHERE status_id > 0) AS b ON tblcus_customer.status = b.status_id   This que

Re: [SQL] Select clause in JOIN statement

It works. Also consider views. Just used this on a my db: SELECT * FROM tblcus_customer INNER JOIN ( SELECT * FROM tblcus_customer_status WHERE status_id > 0) AS b ON tblcus_customer.status = b.status_id You can even join with a function result. Regards, Luca. 2013/6/14 JORGE MALDONADO : > I

Re: [SQL] Select statement with except clause

Reply-all is acceptable; but standard list protocol is to respond at the end of the message after performing "quote editing". JORGE MALDONADO wrote > Firstly, I want to thank you for responding. > Secondly, I wonder if I should only reply to the mailing list (I clicked > Reply All); if this is th

Re: [SQL] Select statement with except clause

Firstly, I want to thank you for responding. Secondly, I wonder if I should only reply to the mailing list (I clicked Reply All); if this is the case, I apologize for any inconvenience. Please let me know so I reply correctly next time. I will describe my issue with more detail. I need to perform

Re: [SQL] Select statement with except clause

JORGE MALDONADO wrote > How does the EXCEPT work? Do fields should be identical? > I need the difference to be on the first 3 fields. Except operates over the entire tuple so yes all fields are evaluated and, if they all match, the row from the "left/upper" query is excluded. If you need somethin

Re: [SQL] select on many-to-many relationship

On Tue, Nov 27, 2012 at 2:13 AM, ssylla wrote: > id_project|id_product > 1|1 > 1|2 > 2|1 > > How can I create an output like this: > id_project|id_product1|id_product2 > 1|1|2 > 2|1|NULL You can use the crostab() function from the tablefunc module (http://www.postgresql.org/docs/9.2/static/tablef

Re: [SQL] select on many-to-many relationship

2012/11/27 ssylla : > assuming I have the following n:n relationship: > > intermediary table: > t3 > id_project|id_product > 1|1 > 1|2 > 2|1 > > How can I create an output like this: > id_project|id_product1|id_product2 > 1|1|2 > 2|1|NULL I'd said the sample is too simplified — not clear which id_

Re: [SQL] Select row cells as new columns

danycxxx wrote: > Yes, I've looked at it, but id doesn't create the desired output. After more > research I've found that I my design is similar to Entity, Attribute and > Value(EAV) design and I think I have to redesign. Any suggestion regarding > EAV? Is there any other approach? EAV is controve

Re: [SQL] Select row cells as new columns

Yes, I've looked at it, but id doesn't create the desired output. After more research I've found that I my design is similar to Entity, Attribute and Value(EAV) design and I think I have to redesign. Any suggestion regarding EAV? Is there any other approach? -- View this message in context: http:

Re: [SQL] Select row cells as new columns

On Fri, 25 May 2012 02:03:41 -0700 (PDT), danycxxx wrote: [...] The question: How can I achive this output? nume, prenume, cnp, nume anterior, stare civila, ... (as columns - built from unique rows from infos) a , a, ... b , b, ... (as rows) Did you look at crosstab functio

Re: [SQL] Select every first/last record of a partition?

I think this would work: select distinct on (id) id, ts --and whatever other columns you want from mytab order by id, timestamp desc; On Mon, May 21, 2012 at 12:04 PM, Andreas wrote: > Hi, > > suppose a table that has records with some ID and a timestamp. > > id,    ts > 3,    2012/01/03 > 5,  

Re: [SQL] SELECT 1st field

Hi Misa, Tom & msi77, On 16/05/12 00:21, Misa Simic wrote: > SELECT id FROM generate_series(1,5) AS foo(id); Thanks for the suggestions - all sorted! cheers, Jan smime.p7s Description: S/MIME Cryptographic Signature

Re: [SQL] SELECT 1st field

Both works fine: SELECT generate_series AS id FROM generate_series(1,5); and SELECT id FROM generate_series(1,5) AS foo(id); Technically dont know is there any differenece... Thanks, Misa 2012/5/15 Tom Lane > Jan Bakuwel writes: > > What I need is the ability to name the column in the vie

Re: [SQL] SELECT 1st field

Jan Bakuwel writes: > What I need is the ability to name the column in the view, ie. > create view v as select 1 as "id" from func(5); I think what you're looking for is the ability to re-alias a column name, for example select id from func(5) as foo(id); regard

Re: [SQL] SELECT 1st field

When you select from function I think column name is the same as function name. So if function name is func query would be: SELECT func AS id FROM func(5); Sent from my Windows Phone From: Jan Bakuwel Sent: 15/05/2012 08:02 To: pgsql-sql@postgresql.org Subject: [SQL] SELECT 1st field Hi, I've

Re: [SQL] SELECT 1st field

Try this create view v(id) as select * from func(5); if your function returns one column. 15.05.2012, 10:01, "Jan Bakuwel" : > Hi, > > I've spend some time checking the documentation but haven't been able to > find what I'm looking for. > I've got a function that returns a set of integers and a

Re: [SQL] select xpath ...

(Note: catching up on a severe list backlog, thought I'd complete this thread for the archives) Brian - In case Boris never sent anything directly, I'll extend his example and show a solution. The usual problem w/ namespaces is getting your head wrapped around the fact that they're local aliases:

Re: [SQL] select xpath ...

Boris, Can you send me your final solution? I am trying to do something similar and I think I am stuck at the namespace. Thanks On Mon, Sep 19, 2011 at 11:49 AM, boris wrote: > On 09/19/2011 10:49 AM, Rob Sargent wrote: >> >> Having a name space in the doc requires it's usage in the query. > >

Re: [SQL] select xpath ...

On 09/19/2011 10:49 AM, Rob Sargent wrote: Having a name space in the doc requires it's usage in the query. yeah, I got it... I was using wrong one... thanks. On 09/17/2011 11:48 AM, boris wrote: hi all, I've inserted xml file : http://www.w3.org/2001/XMLSchema-instance";> zz

Re: [SQL] select xpath ...

Having a name space in the doc requires it's usage in the query. On 09/17/2011 11:48 AM, boris wrote: > hi all, > I've inserted xml file : > > > xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";> > > zz > .. > > > to a table: > > CREATE TABLE "temp".tempxml

Re: [SQL] Select random lines of a table using a probability distribution

On Wed, Jul 13, 2011 at 03:27:10PM +0200, Jira, Marcel wrote: > Hi! > > Let's consider I have a table like this > > idqualificationgenderageincome > > I'd like to select (for example 100) lines of this table by random, but the > random mechanism has to follow a certain probabili

Re: [SQL] Select For Update and Left Outer Join

On Apr 28, 2:00 am, pate...@patearl.net (Patrick Earl) wrote: > This is a follow-up to an old message by Tom Lane: > >    http://archives.postgresql.org/pgsql-sql/2006-10/msg00080.php > [...] > > select * from Pet > left join Dog on Dog.Id = Pet.Id > left join Cat on Cat.Id = Pet.Id > > Now suppo

Re: [SQL] Select and merge rows?

/current/interactive/tablefunc.html In this case, i use: F.36.1.4. - crosstab(text, text). Thanks to Osvaldo Kussama for this help! --- On Thu, 5/5/11, Claudio Adriano Guarracino wrote: From: Claudio Adriano Guarracino Subject: Re: [SQL] Select and merge rows? To: pgsql-sql@postgresql.org, &qu

Re: [SQL] Select and merge rows?

Thank you very much! Your example help me a lot! The original query is more complex, but I can continue with this example. Thanks again! --- On Thu, 5/5/11, scorpda...@hotmail.com wrote: From: scorpda...@hotmail.com Subject: Re: [SQL] Select and merge rows? To: "Claudio Adriano Guarr

[SQL] Re: [SQL] Select and merge rows?

While there is insufficient information provided (a couple of table snippets), you may consider and experiment with the snippet below to get you started. SELECT ids.id, f1.value AS value1, f2.value AS value2, f3.value AS value3 FROM ( SELECT DISTINCT id FROM foo ) AS ids LEFT JOIN foo

Re: [SQL] Select and merge rows?

Howdy! Is there a maximum ceilling of three values per order ID? or an ID can have an arbitrary number of values? Best, Oliveiros 2011/5/5 Claudio Adriano Guarracino > Hello! > I have a doubt about a query that I tried to do, but I cant.. > This is the scenario: > I have a table, with this

Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1

2011/2/8 Emi Lu : > On 02/08/2011 02:51 PM, Rolando Edwards wrote: >> >> SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where >> A.c1=B.c1),', ') from T1 A order by c1; >> >> Give it a Try !!! > > > Thanks a lot! Very helpful! > > array_to_string() + array() is exactly what I am looki

Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1

On 02/08/2011 02:51 PM, Rolando Edwards wrote: SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where A.c1=B.c1),', ') from T1 A order by c1; Give it a Try !!! Thanks a lot! Very helpful! array_to_string() + array() is exactly what I am looking for! I just wonder that array_to_

Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1

I'm not saying this is good or best but: select distinct a.c1, array_to_string(array(select c2 from t1 as b where b.c1 = a.c1),',') from t1 as a; c1 | array_to_string +- 1 | val1,val2,val3 2 | val1 3 | val5,val6 (3 rows) On Tue, Feb 8, 2011 at 2:35 PM, Emi Lu wro

Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1

SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where A.c1=B.c1),', ') from T1 A order by c1; Give it a Try !!! Rolando A. Edwards MySQL DBA (SCMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM & Skype : RolandoLogicWorx re

Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1

Hello you can use a string%agg function if you have a 9.0. On older version there is a array_agg function select c1, array_to_string(array_agg(c2),',') from T1 group by c1 regards Pavel Stehule 2011/2/8 Emi Lu : > Good afternoon, > > Is there a method to retrieve the following results: > > T1(

Re: [SQL] select points of polygons

Andreas Gaab writes: > I have polygons with 5 points (left, center, right, top, bottom) > Now I would like to select an individual point out of the polygon. Are > there any functions to provide this Doesn't look like it :-(. Seems like rather an oversight. regards, tom

Re: [SQL] select result into string's array

Dmitriy Igrishin wrote: > Hello. > > You should use an array constructor: > > DECLARE > m_array text[]; > [..] > BEGIN > FOR m_array IN SELECT ARRAY[col_1, col_2, col_N] FROM my_table LOOP > [..] > END LOOP; > > Regards, > Igrishin Dmitriy. > > 2009/10/9 Alberto Asuero Arroyo

Re: [SQL] select result into string's array

Hello. You should use an array constructor: DECLARE m_array text[]; [..] BEGIN FOR m_array IN SELECT ARRAY[col_1, col_2, col_N] FROM my_table LOOP [..] END LOOP; Regards, Igrishin Dmitriy. 2009/10/9 Alberto Asuero Arroyo > Hi, > > I need to store the result of select into an array of

Re: [SQL] select result into string's array

In response to Alberto Asuero Arroyo : > Hi, > > I need to store the result of select into an array of string: test=*# select * from foo; t -- foo bar batz (3 rows) test=*# select array_agg(t) from foo; array_agg {foo,bar,batz} (1 row) Helps that? Andreas -- And

Re: [SQL] SELECT max(time) group by problem

Hello, Le 30/07/09 11:38, Heigo Niilop a écrit : > hi, > > I have table > > CREATE TABLE table > ( > id integer NOT NULL, > timest timestamp with time zone NOT NULL, > db_time timestamp with time zone NOT NULL DEFAULT now(), > "values" text[], > CONSTRAINT table_pkey PRIMARY KEY (id, t

Re: [SQL] select count of all overlapping geometries and return 0 if none.

On Thursday 12 March 2009 19:28:19 Duffer Do wrote: > I want to return the following: > locations    |  number_visits > Frankfurt    |  6 > Manhattan  |  3 > Talahassee |  0 > > My query only returns: > Frankfurt    |  6 > Manhattan  | 3 > My query: > SELECT count(user_name) as number_visits, loca

Re: [SQL] SELECT multiple MAX(id)s ?

On Tuesday 14 October 2008 18:27:01 Fernando Hevia wrote: > > -Mensaje original- > > De: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] En nombre de Aarni Ruuhimäki > > Enviado el: Viernes, 10 de Octubre de 2008 07:56 > > Para: pgsql-sql@postgresql.org > > Asunto: [SQL] SELECT multiple MAX

Re: [SQL] SELECT multiple MAX(id)s ?

> -Mensaje original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Aarni Ruuhimäki > Enviado el: Viernes, 10 de Octubre de 2008 07:56 > Para: pgsql-sql@postgresql.org > Asunto: [SQL] SELECT multiple MAX(id)s ? > > Hello list, > > table diary_entry > > entry_id SERI

Re: [SQL] Select function with set return type

Nacef LABIDI <[EMAIL PROTECTED]> schrieb: > Hi all, > > I am writing some functions with retrun type as a SETOF of a datatype that I > have defined. How can I test them with a select statement. > Doing select my_function(); return set valued function called in context that > cannot accept a set

Re: [SQL] Select default values

On 24/lug/08, at 23:15, Richard Broersma wrote: On Thu, Jul 24, 2008 at 12:35 PM, Giorgio Valoti <[EMAIL PROTECTED]> wrote: Um - there is no default value for a function. Without this "feature" you have to overload the function arguments. You could pass a casted null to the function. T

Re: [SQL] Select default values

On Thu, Jul 24, 2008 at 12:35 PM, Giorgio Valoti <[EMAIL PROTECTED]> wrote: >> Um - there is no default value for a function. > > Without this "feature" you have to overload > the function arguments. You could pass a casted null to the function. The would eliminate function overloading. Then in

Re: [SQL] Select default values

On 24/lug/08, at 12:42, Richard Huxton wrote: Giorgio Valoti wrote: postgres=# insert into f(a,b) values(default, default) returning *; It seems that you can’t use the same syntax with function calls: select function(default,default); gives a syntax error. Is it expected? Um - there is no d

Re: [SQL] Select default values

Giorgio Valoti wrote: postgres=# insert into f(a,b) values(default, default) returning *; It seems that you can’t use the same syntax with function calls: select function(default,default); gives a syntax error. Is it expected? Um - there is no default value for a function. -- Richard Hux

Re: [SQL] Select default values

On 23/lug/08, at 11:28, Pavel Stehule wrote: Hello 2008/7/23 Maximilian Tyrtania <[EMAIL PROTECTED]>: Hi there, just a quickie: Is there a way to select all default values of a given table? Something like "Select Default values from sometable" ? Unfortunately this syntax doesn't seem to be

Re: [SQL] Select default values

On Wed, Jul 23, 2008 at 3:57 AM, Maximilian Tyrtania <[EMAIL PROTECTED]> wrote: > Hi, > >> am Wed, dem 23.07.2008, um 10:32:58 +0200 mailte Maximilian Tyrtania >> folgendes: >>> Hi there, just a quickie: Is there a way to select all default values of a >>> given table? Something like "Select Defau

Re: [SQL] Select default values

Pavel, fantastic, that's exactly what I wanted, thank you very much! Maximilian Tyrtania > Von: Pavel Stehule <[EMAIL PROTECTED]> >> Okay, here is one final (i hope) obstacle. My db has >200 tables and I'd >> love to be able to write some function that would just take a tablename and >> return

Re: [SQL] Select default values

2008/7/23 Maximilian Tyrtania <[EMAIL PROTECTED]>: > Hi, > >> begin >> insert ... >> rollback; >> >> it's not best solution, but it just works. > > Ah, yes, of course, haven't thought of that. > > Okay, here is one final (i hope) obstacle. My db has >200 tables and I'd > love to be able to write

Re: [SQL] Select default values

On Wed, Jul 23, 2008 at 12:35:08PM +0200, Maximilian Tyrtania wrote: > With your way (insert into f(a,b) values(default, default) returning *) i > need to know everything about the given table. > > Hmm. Any ideas? Do look at the information schema. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.n

Re: [SQL] Select default values

Hi, > begin > insert ... > rollback; > > it's not best solution, but it just works. Ah, yes, of course, haven't thought of that. Okay, here is one final (i hope) obstacle. My db has >200 tables and I'd love to be able to write some function that would just take a tablename and return the de

Re: [SQL] Select default values

2008/7/23 Maximilian Tyrtania <[EMAIL PROTECTED]>: > Hi again, > >>> Hi there, just a quickie: Is there a way to select all default values of a >>> given table? Something like "Select Default values from sometable" ? > >> it's not possible directly, you can find expressions used as default >> in sy

Re: [SQL] Select default values

Hi, > am Wed, dem 23.07.2008, um 10:32:58 +0200 mailte Maximilian Tyrtania > folgendes: >> Hi there, just a quickie: Is there a way to select all default values of a >> given table? Something like "Select Default values from sometable" ? > > test=# create table t_with_defaults( s1 int default 1

Re: [SQL] Select default values

Hi again, >> Hi there, just a quickie: Is there a way to select all default values of a >> given table? Something like "Select Default values from sometable" ? > it's not possible directly, you can find expressions used as default > in system tables or > postgres=# create table f(a integer defaul

Re: [SQL] Select default values

Hello 2008/7/23 Maximilian Tyrtania <[EMAIL PROTECTED]>: > Hi there, just a quickie: Is there a way to select all default values of a > given table? Something like "Select Default values from sometable" ? > Unfortunately this syntax doesn't seem to be supported. I know i can select > the default v

Re: [SQL] Select default values

am Wed, dem 23.07.2008, um 10:32:58 +0200 mailte Maximilian Tyrtania folgendes: > Hi there, just a quickie: Is there a way to select all default values of a > given table? Something like "Select Default values from sometable" ? > Unfortunately this syntax doesn't seem to be supported. I know i can

Re: [SQL] select across two database

Helo it is possible if you don't mind some work :) We are doing it with plproxy. Simple scenario would be 1. install plproxy 2. create sql functon with needed sql in remote db 3. create plproxy function in current db 4. create sql or function that combines the results from data in current db and p

Re: [SQL] select across two database

On 17/06/2008, Jorge Medina <[EMAIL PROTECTED]> wrote: > hi guys. > I want know if it's possible create a select from 2 database or create > a view in one of them. The short answer is no. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://

Re: [SQL] Select into

On Mar 20, 2008, at 7:10 AM, Joe wrote: Gavin 'Beau' Baumanis wrote: The copy is inside the same table, so I don't understand why it (the required query ) would require any joins. Ie. I want to copy the contents of a row (but for the id column - of course) into a record in the same tabl

Re: [SQL] Select into

On Thu, Mar 20, 2008 at 7:08 PM, Joe <[EMAIL PROTECTED]> wrote: > Gurjeet Singh wrote: > > Except that it doesn't work... Did you try to execute that query; I am > > assuming not. > Of course I did, My bad... I did not run your query either, and based my assumption on my previous attempt that ha

Re: [SQL] Select into

Gurjeet Singh wrote: Except that it doesn't work... Did you try to execute that query; I am assuming not. Of course I did, do you think I create results by editing them into my email? The script: delete from t1; insert into t1 values (1, 123, 'first record'); insert into t1 values (2, 456, 's

Re: [SQL] Select into

Gurjeet Singh wrote: Except that it doesn't work... Did you try to execute that query; I am assuming not. It does, or at least a query written to work the same way works fine for me. Not only that, but at least in the presence of a unique index the query planner optimises it to the same quer

Re: [SQL] Select into

On Thu, Mar 20, 2008 at 5:40 PM, Joe <[EMAIL PROTECTED]> wrote: > Gavin 'Beau' Baumanis wrote: > > > > The copy is inside the same table, so I don't understand why it (the > > required query ) would require any joins. > > > > Ie. I want to copy the contents of a row (but for the id column - of > >

Re: [SQL] Select into

Gavin 'Beau' Baumanis wrote: The copy is inside the same table, so I don't understand why it (the required query ) would require any joins. Ie. I want to copy the contents of a row (but for the id column - of course) into a record in the same table. I think what you want is something like

Re: [SQL] Select into

craig=# update x set val = foundrow.val from ( select val from x where id = 2 ) as foundrow where id = 1 ; UPDATE 1 Thinking about it, it'd actually be better written as: UPDATE x SET val = foundrow.val FROM ( SELECT val FROM x AS x2 WHERE x2.id = 2 ) AS foundrow WHERE id = 1; ... be

Re: [SQL] Select into

Hi Everyone, I want to thank everyone for their help / suggestions... I really appreciate it. Though I think I have found a winner. craig=# update x set val = foundrow.val from ( select val from x where id = 2 ) as foundrow where id = 1 ; UPDATE 1 Very elegant, very clean... Very nice! T

Re: [SQL] Select into

On Thu, Mar 20, 2008 at 5:35 PM, Craig Ringer <[EMAIL PROTECTED]> wrote: > > craig=# explain update x set val = foundrow.val from ( select val from x > where id = 4123 ) as foundrow where id = 5912 ; > Thats nifty. -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.

Re: [SQL] Select into

Gavin 'Beau' Baumanis wrote: The copy is inside the same table, so I don't understand why it (the required query ) would require any joins. Maybe you should use FROM clause in the update that references a row-valued subquery? craig=# create table x ( id serial, val integer ); NOTICE: CREATE

Re: [SQL] Select into

On Thu, Mar 20, 2008 at 4:39 PM, Gavin 'Beau' Baumanis < [EMAIL PROTECTED]> wrote: > HI Gurjeet, > You're right. > > But what information do you need to know? > > The copy is inside the same table, so I don't understand why it (the > required query ) would require any joins. > > Ie. I want to copy

Re: [SQL] Select into

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Ie. I want to copy the contents of a row (but for the id > column - of course) into a record in the same table. BEGIN; CREATE TEMP TABLE tempfoo AS SELECT * FROM foo WHERE id = 123; UPDATE tempfoo SET id = 456; DELETE FROM foo WHERE id = 456;

Re: [SQL] Select into

HI Gurjeet, You're right. But what information do you need to know? The copy is inside the same table, so I don't understand why it (the required query ) would require any joins. Ie. I want to copy the contents of a row (but for the id column - of course) into a record in the same table.

Re: [SQL] Select into

On Thu, Mar 20, 2008 at 4:19 PM, A. Kretschmer < [EMAIL PROTECTED]> wrote: > am Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin 'Beau' Baumanis > folgendes: > > Hi Everyone, > > > > I have asked our DBA at work and h is not too sure either... so I > > thought it best to on the list. > > > > B

Re: [SQL] Select into

am Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin 'Beau' Baumanis folgendes: > Hi Everyone, > > I have asked our DBA at work and h is not too sure either... so I > thought it best to on the list. > > Basically, what I am after is a way to copy the contents of one record > into another.

Re: [SQL] SELECT DISTINCT

Shavonne Marietta Wijesinghe wrote: Hello I have 2 records with the same value. Even when i do a select with DISTINCT, it returns me both the records instead of one. SELECT DISTINCT ON (TE_COGNOME) TE_COGNOME, TE_NOME, N_GEN_TEST, TE_SESSO, TE_ATTNASC, TE_LUONASC, TE_INDI, TE_DTNASC, TE_PROVSTA

Re: [SQL] SELECT MAX returns wrong value

On Dec 13, 2007 5:09 PM, Gavin Baumanis <[EMAIL PROTECTED]> wrote: > Hi Everyone, > > Sorry if I am missing something obvious but I think I have found a bug. > If I perform the following SQL > > SELECT MAX(column) FROM table WHERE expression > > and there is no match, Postgres returns a record coun

Re: [SQL] SELECT MAX returns wrong value

Gavin Baumanis <[EMAIL PROTECTED]> writes: > If I perform the following SQL > SELECT MAX(column) FROM table WHERE expression > and there is no match, Postgres returns a record count of 1. > There is no value in max, it is NULL. This is the behavior required by the SQL standard. > Thus trying to

Re: [SQL] Select in From clause

Consider this: CREATE TABLE "public"."test" ( "id" INTEGER NOT NULL, "tbl" TEXT ) WITHOUT OIDS; INSERT INTO "public"."test" ("id", "tbl") VALUES (1, 'status'); INSERT INTO "public"."test" ("id", "tbl") VALUES (2, 'yearplan'); Following two statements will return one record. select tbl from

Re: [SQL] Select in From clause

Ray Madigan wrote: > I have never seen this done before, but it seems like it is supposed to work > from reading the manual. > > I want to be able to get a table name from another table and use it in the > from clause of a select. > > Something like > > SELECT * FROM (SELECT name FROM bar WHERE

Re: [SQL] SELECT DISTINCT ... ORDER BY UPPER(column_name) fails

Aymeric Levaux <[EMAIL PROTECTED]> writes: > SELECT DISTINCT tag FROM logs ORDER BY UPPER(tag); > You get the following error message : > for SELECT DISTINCT, ORDER BY expressions must appear in select list. > It is weird as the order by column appears in the select. No it doesn't. The ORDER BY

Re: [SQL] Select into with dynamic criteria in a plpgsql function

Paul Lambert wrote: I've got a function defined in PL/PgSQL to update some fields in a record where the criteria for pulling out some other values from a table is dynamic. I define a string called account_criteria to which I assign a normal SQL WHERE clause based on some work done earlier in

Re: [SQL] select sum within transaction problem

"Bart Degryse" <[EMAIL PROTECTED]> writes: > I have a function like below (simplified). Everything works the way I want = > it to except for one thing. > After the select statement sum_revenues is NULL instead of the real sum. > Has this something to do with the earlier deletes and inserts not bein

Re: [SQL] SELECT syntax synopsis: column_definition?

Gregory Stark <[EMAIL PROTECTED]> writes: > I haven't quite figured out how this is useful though. It probably makes more > sense if you use plpgsql but I still don't quite see what the use case is. IIRC, the case that actually convinced people to allow it was dblink. You want to be able to do som

Re: [SQL] SELECT syntax synopsis: column_definition?

--- Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Any function declared as returning SETOF RECORD needs it, when you don't > use OUT params. Before OUT params existed, it was the only way to use > those functions. Thanks everyone for the exposition! It makes sense. Regards, Richard Broersma Jr.

Re: [SQL] SELECT syntax synopsis: column_definition?

Gregory Stark wrote: > "Michael Glaesemann" <[EMAIL PROTECTED]> writes: > > > ERROR: a column definition list is only allowed for functions returning > > "record" > > > > So the *form* is right, but I don't know of an example that works. > > postgres=# create function testf() returns record as

Re: [SQL] SELECT syntax synopsis: column_definition?

On 8/21/07, Gregory Stark <[EMAIL PROTECTED]> wrote: > "Michael Glaesemann" <[EMAIL PROTECTED]> writes: > > > ERROR: a column definition list is only allowed for functions returning > > "record" > > > > So the *form* is right, but I don't know of an example that works. > > postgres=# create funct

Re: [SQL] SELECT syntax synopsis: column_definition?

"Michael Glaesemann" <[EMAIL PROTECTED]> writes: > ERROR: a column definition list is only allowed for functions returning > "record" > > So the *form* is right, but I don't know of an example that works. postgres=# create function testf() returns record as 'select 1' language sql; CREATE FUNCT

Re: [SQL] SELECT syntax synopsis: column_definition?

On Aug 21, 2007, at 18:04 , Michael Glaesemann wrote: So the *form* is right, but I don't know of an example that works. CREATE TABLE foos ( foo text PRIMARY KEY , title text NOT NULL ); INSERT INTO foos (foo, title) values ('foo', 'the great') , ('bar', 'the extravagant') , ('baz',

Re: [SQL] SELECT syntax synopsis: column_definition?

On Aug 21, 2007, at 14:34 , Richard Broersma Jr wrote: Can any one give an example of the difference between a column_alias and a column_definition when using a function in the FROM clause? from the manual: http://www.postgresql.org/docs/8.2/interactive/sql-select.html "function_name ( [ ar

Re: [SQL] select from table and add rows.

On Fri, Jul 06, 2007 at 05:35:39PM +0100, Gary Stainburn wrote: > > I want to be able to do away with the first line of the code, and create a > select statement that would generate the three rows first, followed by the > rows from the table I'm using. I want to be able to do the same job withou

Re: [SQL] select from table and add rows.

Gary Stainburn wrote: On Friday 06 July 2007 16:02, Andrew Sullivan wrote: On Fri, Jul 06, 2007 at 02:25:08PM +0100, Gary Stainburn wrote: This sets up an array with some pseudo values and then populates it with *proper* values from a table. How's the best way to do this all within SQL. I'm o

Re: [SQL] select from table and add rows.

On Friday 06 July 2007 16:02, Andrew Sullivan wrote: > On Fri, Jul 06, 2007 at 02:25:08PM +0100, Gary Stainburn wrote: > > This sets up an array with some pseudo values and then populates it with > > *proper* values from a table. > > > > How's the best way to do this all within SQL. > > I'm obvious

Re: [SQL] select from table and add rows.

On Fri, Jul 06, 2007 at 02:25:08PM +0100, Gary Stainburn wrote: > This sets up an array with some pseudo values and then populates it with > *proper* values from a table. > > How's the best way to do this all within SQL. I'm obviously not understanding the question properly, because I think you

Re: [SQL] Select last there dates

I think there might be a small typo though. The left-join is to: (select uid, phone_numer, datum from t2 order by 2 ) Probably want to order by "datum DESC" too, to ensure you get the latest dates for each telnum. yes, I also observed this, so I have add to my query. Thank you for your ex

Re: [SQL] Select last there dates

am Thu, dem 21.06.2007, um 16:00:05 +0300 mailte Loredana Curugiu folgendes: > So Andreas, would you please give some more explanations > on your solution? I didn't work with functions and aggregate till > now. I will try it, but i'm not a nativ english speaker and thats why i have some problems.

Re: [SQL] Select last there dates

Loredana Curugiu wrote: Richard, Andreas, thank you very much for your solutions. I took a look on both solutions, but I choosed Andreas's solution because is shorter :) Not to mention clever, exploiting the fact that we know the length of a text-representation of three comma-separated dates.

Re: [SQL] Select last there dates

Richard, Andreas, thank you very much for your solutions. I took a look on both solutions, but I choosed Andreas's solution because is shorter :) So Andreas, would you please give some more explanations on your solution? I didn't work with functions and aggregate till now. I don't understand ho

Re: [SQL] Select last there dates

am Thu, dem 21.06.2007, um 11:18:13 +0300 mailte Loredana Curugiu folgendes: > Hello again, > > I have the following two tables: > > Table 1: > uid | phone_number | > -+--- >8 | +40741775621 | >8 | +40741775622 | >8 | +40741775623 | >9 | +407417756

Re: [SQL] Select last there dates

Loredana Curugiu wrote: My task is to create a query which for a given uid returns all values for phone_number column from table1 and last three values of date column from table2. For example, if uid=8 the query should return: phone_number |date ---+ +4074177

Re: [SQL] select ..... not in .....

Gary Stainburn <[EMAIL PROTECTED]> writes: > So, who does this select not return the row? > select v_d_code, v_o_number, v_vin, v_status from vista_details where v_vin > not in ( > goole(# select substring(w_vin from '(.{11}$)') from walon); NOT IN with a sub-select that returns any NULL valu

Re: [SQL] select slows from 3 seconds to 30 seconds

On Saturday 05 May 2007 01:57, Tom Lane wrote: > Gary Stainburn <[EMAIL PROTECTED]> writes: > > I have the query below which when run takes approx 3 seconds. However > > when I add the condition 'and w_ws_id = 10' onto the end changes to 30+ > > seconds. Can anyone see why? I've included the expla

Re: [SQL] select slows from 3 seconds to 30 seconds

Gary Stainburn <[EMAIL PROTECTED]> writes: > I have the query below which when run takes approx 3 seconds. However when I > add the condition 'and w_ws_id = 10' onto the end changes to 30+ seconds. > Can anyone see why? I've included the explain for the long select. You really ought to provide EX

  1   2   3   4   5   >