[SQL] Concat two fields into one at runtime
Is there a way using built-in PostgreSQL functions to combine two data fields into a single field at runtime when querying data? For example, the query now returns: idfirstlast --- --- -- 1 Goerge Handin 2 Joe Rachin I'd like it to return: idname --- --- 1 George Handin 2 Joe Rachin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Query to return modified results at runtime?
Richard Broersma Jr wrote: IDColor --- --- 1 Blue 2 Red 3 Green 4 Orange How would I rewrite the query to return results where the colors are replaced by letters to give the following results? IDColor --- --- 1 A 2 D 3 B 4 C http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html this is probably the easiest to implement but hard to mangage over time. Another solution would be to create color_code table that is referenced by your test table. Then when you can create a query as: select a.ID, b.code from test as a join color_code as b on a.color = b.color; There are additional solutions to this also. But these two are probably the easiest. Thanks! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Query to return modified results at runtime?
On Wed, 2006-06-07 at 19:29 -0500, George Handin wrote: > I have a query: > > SELECT * FROM testtable; > > Where the results are: > > IDColor > --- --- > 1 Blue > 2 Red > 3 Green > 4 Orange > > How would I rewrite the query to return results where the colors are > replaced by letters to give the following results? > > IDColor > --- --- > 1 A > 2 D > 3 B > 4 C > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq It looks like you may want to use a validation table: Name: color_validate ID Color Code --- --- --- 1 BlueA 2 Red D 3 Green B 4 Orange C Then try the following query: SELECT table_a.ID, color_validate.Code FROM table_a, color_validate WHERE (table_a.ID = color_validate.ID); -Ken ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Concat two fields into one at runtime
select id, first || ' ' || lastfrom mytable;On 6/8/06, George Handin <[EMAIL PROTECTED]> wrote: Is there a way using built-in PostgreSQL functions to combine two datafields into a single field at runtime when querying data? For example, the query now returns:idfirstlast--- --- --1 Goerge Handin2 Joe RachinI'd like it to return:idname--- --- 1 George Handin2 Joe Rachin---(end of broadcast)---TIP 5: don't forget to increase your free space map settings
Re: [SQL] Concat two fields into one at runtime
George Handin wrote: Is there a way using built-in PostgreSQL functions to combine two data fields into a single field at runtime when querying data? For example, the query now returns: idfirstlast --- --- -- 1 Goerge Handin 2 Joe Rachin I'd like it to return: idname --- --- 1 George Handin 2 Joe Rachin select id,first||' '||last from your_table ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Concat two fields into one at runtime
George Handin wrote: Is there a way using built-in PostgreSQL functions to combine two data fields into a single field at runtime when querying data? For example, the query now returns: idfirstlast --- --- -- 1 Goerge Handin 2 Joe Rachin I'd like it to return: idname --- --- 1 George Handin 2 Joe Rachin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings if you want the 2 fields with a space between them select first ||' '||last as name from foo; should do it. Oisin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] pgxml & xpath_table
Hi,
I'm playing with the contrib/pgxml library under PG 8.1.4, and I'm not sure if
what I found with pgxml is a feature of a bug:
I've got the following table:
CREATE TABLE test
(
id int4 NOT NULL,
xml varchar(200),
CONSTRAINT pk PRIMARY KEY (id)
)
WITHOUT OIDS;
INSERT INTO test VALUES (1, '123112233');
INSERT INTO test VALUES (2, '111222333111222333');
If I launch this query:
select * from
xpath_table('id','xml','test',
'/doc/@num|/doc/line/@num|/doc/line/a|/doc/line/b|/doc/line/c','1=1') AS t(id
int4, doc_num varchar(10), line_num varchar(10), val1 int4, val2 int4, val3
int4)
where id = 1
order by doc_num, line_num
I get:
iddoc_num line_numval1val2val3
1 C1 L1 1 2 3
1 L2 11 22 33
I was expecting doc_num would receive twice the C1 value, just like with a
normal sql join.
Regards,
--
Philippe Lang, Ing. Dipl. EPFL
Attik System
rte de la Fonderie 2
1700 Fribourg
Switzerland
http://www.attiksystem.ch
Tel: +41 (26) 422 13 75
Fax: +41 (26) 422 13 76
smime.p7s
Description: S/MIME cryptographic signature
Re: [SQL] Concat two fields into one at runtime
On Thu, 2006-06-08 at 10:50 -0500, George Handin wrote: > Is there a way using built-in PostgreSQL functions to combine two data > fields into a single field at runtime when querying data? > > For example, the query now returns: > > idfirstlast > --- --- -- > 1 Goerge Handin > 2 Joe Rachin > > I'd like it to return: > > idname > --- --- > 1 George Handin > 2 Joe Rachin > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings Try this: SELECT id, first || || last AS name FROM table; -Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Query to return modified results at runtime?
Hi, You can use the construct Case When but You have to have Your information structured (even if only in Your mind) in order to achive the results You want. So, suposse You have for the color Blue the letter A, for the color Red the letter D, for the color Green the letter B and finally for the color Orange the letter C. For the following data: create table dcosta.colors (id numeric(3), Color varchar(12)); insert into dcosta.colors values(1, 'Blue'); insert into dcosta.colors values(2, 'Red'); insert into dcosta.colors values(3, 'Green'); insert into dcosta.colors values(4, 'Orange'); You can use the following instruction: SELECT ID, Color, CASE WHEN color = 'Blue' THEN 'A' WHEN color = 'Red'THEN 'D' WHEN color = 'Green' THEN 'B' WHEN color = 'Orange' THEN 'C' ELSE 'other' END FROM dcosta.colors; Obviously You can ommit the column Color from the select clause. Hope I helped Dias Costa George Handin wrote: Richard Broersma Jr wrote: ID Color --- --- 1 Blue 2 Red 3 Green 4 Orange How would I rewrite the query to return results where the colors are replaced by letters to give the following results? ID Color --- --- 1 A 2 D 3 B 4 C http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html this is probably the easiest to implement but hard to mangage over time. Another solution would be to create color_code table that is referenced by your test table. Then when you can create a query as: select a.ID, b.code from test as a join color_code as b on a.color = b.color; There are additional solutions to this also. But these two are probably the easiest. Thanks! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] empty set
PostgreSQL 8.1 I've been trying to write a SQL prepare routine. One of the challenging elements I'm running into is an empty set ... "select foo from bar where foo in ? ;" What if "?" is an set with zero elements? What is the proper value to use to replace "?" indicating an empty set? CG __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] empty set
On Thu, 2006-06-08 at 16:40, CG wrote: > PostgreSQL 8.1 > > I've been trying to write a SQL prepare routine. One of the challenging > elements I'm running into is an empty set ... > > "select foo from bar where foo in ? ;" > > What if "?" is an set with zero elements? What is the proper value to use to > replace "?" indicating an empty set? NULL? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] pgxml & xpath_table
There's a good chance the author isn't on this list. You'd be better off
on pgsql-general, or just emailing the author of pgxml directly.
On Thu, Jun 08, 2006 at 06:00:42PM +0200, Philippe Lang wrote:
> Hi,
>
> I'm playing with the contrib/pgxml library under PG 8.1.4, and I'm not sure
> if what I found with pgxml is a feature of a bug:
>
> I've got the following table:
>
>
> CREATE TABLE test
> (
> id int4 NOT NULL,
> xml varchar(200),
> CONSTRAINT pk PRIMARY KEY (id)
> )
> WITHOUT OIDS;
>
> INSERT INTO test VALUES (1, ' num="L1">123 num="L2">112233');
>
> INSERT INTO test VALUES (2, ' num="L1">111222333 num="L2">111222333');
>
>
>
> If I launch this query:
>
>
> select * from
>
> xpath_table('id','xml','test',
> '/doc/@num|/doc/line/@num|/doc/line/a|/doc/line/b|/doc/line/c','1=1') AS t(id
> int4, doc_num varchar(10), line_num varchar(10), val1 int4, val2 int4, val3
> int4)
>
> where id = 1
>
> order by doc_num, line_num
>
>
> I get:
>
>
> iddoc_num line_numval1val2val3
> 1 C1 L1 1 2 3
> 1 L2 11 22 33
>
>
> I was expecting doc_num would receive twice the C1 value, just like with a
> normal sql join.
>
> Regards,
>
> --
> Philippe Lang, Ing. Dipl. EPFL
> Attik System
> rte de la Fonderie 2
> 1700 Fribourg
> Switzerland
> http://www.attiksystem.ch
>
> Tel: +41 (26) 422 13 75
> Fax: +41 (26) 422 13 76
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] empty set
That will work if foo is never NULL. If foo takes on a NULL value you will get those records where it is NULL.Is there a reason you cannot do an if/else statement on the list size? That is what I do on my queries. -AaronOn 6/8/06, Scott Marlowe <[EMAIL PROTECTED]> wrote: On Thu, 2006-06-08 at 16:40, CG wrote:> PostgreSQL 8.1>> I've been trying to write a SQL prepare routine. One of the challenging> elements I'm running into is an empty set ...>> "select foo from bar where foo in ? ;" >> What if "?" is an set with zero elements? What is the proper value to use to> replace "?" indicating an empty set?NULL?---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
