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

[SQL] Select clause in JOIN statement

Is it valid to specify a SELECT statement as part of a JOIN clause? For example: SELECT table1.f1, table1.f2 FROM table1 INNER JOIN (SELECT table2.f1, table2.f2 FROM table2) table_aux ON table1.f1 = table_aux.f1 Respectfully, Jorge Maldonado

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

[SQL] Select statement with except clause

I have one SELECT statement as follows: SELECT lpt_titulo as tmt_titulo, tmd_nombre as tmt_nombre, tmd_album as tmt_album, SUM(lpt_puntos) AS tmt_puntos FROM listas_pre_titulos INNER JOIN cat_tit_media ON lpt_titulo = tmd_clave " WHERE condition The above statement must have an EXCEPT clause whic

Re: [SQL] Data Loss from SQL SELECT (vs. COPY/pg_dump)

This seems to answer my question. I completely forgot about the behavior of NULL value in the text concatenation. http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE Because quote_literal is labelled STRICT, it will always return null when calle

[SQL] Data Loss from SQL SELECT (vs. COPY/pg_dump)

I am troubled to find out that a SELECT statement produces fewer rows than the actual row count and have not been able to answer myself as to why. I hope someone could help shedding some light to this. I attempted to generate a set of INSERT statements, using a the following SELECT statement,

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_

[SQL] select on many-to-many relationship

Dear list, assuming I have the following n:n relationship: t1: id_project 1 2 t2: id_product 1 2 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 -- View this message in context: http://postgre

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

[SQL] Select row cells as new columns

Hello. I hope you can help me with this or at least guide me into the right direction: I have 2 tables: CREATE TABLE infos ( id integer NOT NULL DEFAULT nextval('info_id_seq'::regclass), name text NOT NULL, id_member integer NOT NULL, title text, min_length integer NOT NULL DEFAULT 0,

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,  

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

Hi, suppose a table that has records with some ID and a timestamp. id,ts 3,2012/01/03 5,2012/01/05 7,2012/01/07 3,2012/02/03 3,2012/01/05 5,2012/03/01 7,2012/04/04 to fetch every last row of those IDs I do: select id, ts from ( select id, ts, row_number(

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

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

[SQL] SELECT 1st field

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 view that selects from the function. What I need is the ability to name the column in the view, ie. create function func(i int) returns

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

[SQL] select xpath ...

hi all, I've inserted xml file : xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";> zz .. to a table: CREATE TABLE "temp".tempxml ( record xml ) I can get it using: select * from temp.tempxml but, I can't get any values using xpath. ex:

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

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

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 probability distribution. I want to use this procedure to construct a test group for a

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?

f1 ON f1.id = ids.id AND f1.order = 1 LEFT JOIN foo f2 ON f2.id = ids.id AND f2.order = 2 LEFT JOIN foo f3 ON f3.id = ids.id AND f3.order = 3 ORDER BY ids.id; - Reply message - From: "Claudio Adriano Guarracino" Date: Thu, May 5, 2011 5:18 pm Subject: [SQL] Select and

[SQL] Select and merge rows?

Excuse me, The original table is: order    ID    value    -- 1    1000    3 2    1000    5 3    1000    6 1    1001    1 2    1001    2 1    1002    4 2    1002    4 The result of table should be: id    value1    value2    value3 ---

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

[SQL] Select and merge rows?

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 rows: order    ID    value    -- 1    1000    3 2    1000    5 3    1000    6 1    1001    1 2    1001    2 1    1002    4 2    1002    4 I need to get this

[SQL] Select For Update and Left Outer Join

This is a follow-up to an old message by Tom Lane: http://archives.postgresql.org/pgsql-sql/2006-10/msg00080.php In ORMs like NHibernate, there are a few strategies for mapping inheritance to SQL. One of these is "Joined Subclass," which allows for the elimination of duplicate data and clean

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

NY 10013 >> 212-625-5307 (Work) >> 201-660-3221 (Cell) >> AIM&  Skype : RolandoLogicWorx >> redwa...@logicworks.net >> http://www.linkedin.com/in/rolandoedwards >> >> >> -Original Message- >> From: pgsql-sql-ow...@postgresql.org >&g

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

IM& Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Emi Lu Sent: Tuesday, February 08, 2011 2:36 PM To: pgsql-sql@postgresql.org

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

landoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Emi Lu Sent: Tuesday, February 08, 2011 2:36 PM To: pgsql-sql@postgresql.org Subject: [SQL] "s

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(

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

Good afternoon, Is there a method to retrieve the following results: T1(c1 int, c2 varchar(128) ) - (1, val1); (1, val2); (1, val3); (2, val1); (3, val5); (3, val6); select c1, method(c2) group by c1 returns: 1, "val1, val2, val3" 2, "val1" 3,

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

[SQL] select points of polygons

Hi there, 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 in an readable manner other than: e.g. SELECT replace(split_part(p::text,',(',1),'((','(')::point as point FROM

Re: [SQL] combine SQL SELECT statements into one

msi77 wrote: > Hi, > > SELECT > (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01') > as count1, > (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20') > as count2, > (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01') >

Re: [SQL] combine SQL SELECT statements into one

Hi, SELECT (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01') as count1, (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20') as count2, (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01') as count3 Serge > Good Evenin

Re: [SQL] combine SQL SELECT statements into one

Hi, SELECT (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01') as count1, (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20') as count2, (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01') as count3 Serge > Good Evenin

Re: [SQL] [NOVICE] combine SQL SELECT statements into one

In response to Neil Stlyz : > Good Evening, Good Morning Wherever you are whenever you may be reading this. > > I am new to this email group and have some good experience with SQL and > PostgreSQL database. > > > I am currently working on a PHP / PostgreSQL project and I came upon something > I

[SQL] combine SQL SELECT statements into one

Good Evening, Good Morning Wherever you are whenever you may be reading this. I am new to this email group and have some good experience with SQL and PostgreSQL database. I am currently working on a PHP / PostgreSQL project and I came upon something I could not figure out in SQL. I was wonderi

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

[SQL] select result into string's array

Hi, I need to store the result of select into an array of string: /create or replace function search_engine.test/ /(/ /)/ /returns integer as $$/ /declare/ /m_array text[];/ /begin/ /for m_array in select * from my_table loop/ /raise notice

[SQL] SELECT max() group by problem

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, timest) ) „id“ have foreign key with table1 and when I t

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

[SQL] SELECT max(time) group by problem

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, timest) ) „id“ have foreign key with table1 and when I t

[SQL] select regexp_matches('a a a', '([a-z]) a','g');

First: Please don't reply to an existing message to create a new thread. Your mail client copies the replied-to message ID into the References: header, and well-implemented mail clients will thread your message under a now-unrelated thread. Compose a new message instead. Marc Mamin wrote: > I ha

[SQL] select regexp_matches('a a a', '([a-z]) a','g');

Hello, I wonder if someone has an idea for this problem: I have a string that contains a serie of chars, separated by single spaces. e.g 'a b x n r a b c b' Having such a string, I d'like to get a list of all predecessors of a given character. In the example, the predecessors of b are a,a,c.

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

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

Hello all, I have 2 tables locations and user_tracker: locations has 2 columns location_name location_geometry user_tracker has 3 columns user_name user_geometry user_timestamp locations table is coordinates and names of areas of interest. user_tracker basically is an archive of a user's moveme

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 >

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

[SQL] SELECT multiple MAX(id)s ?

Hello list, table diary_entry entry_id SERIAL PK d_entry_date_time timestamp without time zone d_entry_company_id integer d_entry_location_id integer d_entry_shift_id integer d_user_id integer d_entry_header text ... Get the last entries from companies and their locations? The last, i.e. the bi

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

[SQL] Select function with set return type

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 Thanks to all Nacef

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

[SQL] Select default values

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 values for each column, but being able to select them in one go wou

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

[SQL] select across two database

hi guys. I want know if it's possible create a select from 2 database or create a view in one of them. -- Jorge Andrés Medina Oliva. Systems Manager and Developer. BSDCHiLE. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql

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.

  1   2   3   4   5   6   >