pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpopulate value of column
5+ messages / 3 participants
[nested] [flat]
* populate value of column
@ 2007-08-08 05:30 novice <user.postgresql@gmail.com>
2007-08-08 05:37 ` Re: populate value of column Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-08-08 05:40 ` Re: populate value of column Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 2 replies; 5+ messages in thread
From: novice @ 2007-08-08 05:30 UTC (permalink / raw)
To: pgsql-sql
I have a table
CREATE TABLE meter
(
meter_id integer NOT NULL,
area_no integer NOT NULL,
CONSTRAINT meter_pkey PRIMARY KEY (meter_id)
) ;
INSERT INTO meter(meter_id, no_of_bays) VALUES (1001, 4);
INSERT INTO meter(meter_id, no_of_bays) VALUES (1012, 6);
select meter_id, area_no from meter;
meter_id | no_of_bays
----------+------------
1001 | 4
1012 | 6
How can I generate the following result?
meter_id | bay
----------+------------
1001 | 01
1001 | 02
1001 | 03
1001 | 04
1012 | 01
1012 | 02
1012 | 03
1012 | 04
1012 | 05
1012 | 06
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: populate value of column
2007-08-08 05:30 populate value of column novice <user.postgresql@gmail.com>
@ 2007-08-08 05:37 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-08-08 05:50 ` Re: populate value of column novice <user.postgresql@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Phillip Smith @ 2007-08-08 05:37 UTC (permalink / raw)
To: pgsql-sql
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of novice
Sent: Wednesday, 8 August 2007 15:31
To: pgsql-sql@postgresql.org
Subject: [SQL] populate value of column
> How can I generate the following result?
>
> meter_id | bay
> ----------+------------
> 1001 | 01
> 1001 | 02
> 1001 | 03
> 1001 | 04
> 1012 | 01
> 1012 | 02
> 1012 | 03
> 1012 | 04
> 1012 | 05
> 1012 | 06
I even tested this one too ;)
SELECT meter_id,
LPAD(GENERATE_SERIES(1,area_no),2,'0')
FROM meter;
Works on 8.2.4 - you didn't say what version you were using.
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
e-mail.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: populate value of column
2007-08-08 05:30 populate value of column novice <user.postgresql@gmail.com>
2007-08-08 05:37 ` Re: populate value of column Phillip Smith <phillip.smith@weatherbeeta.com.au>
@ 2007-08-08 05:50 ` novice <user.postgresql@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: novice @ 2007-08-08 05:50 UTC (permalink / raw)
To: Phillip Smith <phillip.smith@weatherbeeta.com.au>; +Cc: pgsql-sql
Thank you :-))
That's so quick! Apologies for the error on my DDL statement
On 08/08/07, Phillip Smith <phillip.smith@weatherbeeta.com.au> wrote:
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
> On Behalf Of novice
> Sent: Wednesday, 8 August 2007 15:31
> To: pgsql-sql@postgresql.org
> Subject: [SQL] populate value of column
>
> > How can I generate the following result?
> >
> > meter_id | bay
> > ----------+------------
> > 1001 | 01
> > 1001 | 02
> > 1001 | 03
> > 1001 | 04
> > 1012 | 01
> > 1012 | 02
> > 1012 | 03
> > 1012 | 04
> > 1012 | 05
> > 1012 | 06
>
> I even tested this one too ;)
> SELECT meter_id,
> LPAD(GENERATE_SERIES(1,area_no),2,'0')
> FROM meter;
>
> Works on 8.2.4 - you didn't say what version you were using.
>
>
> *******************Confidentiality and Privilege Notice*******************
>
> The material contained in this message is privileged and confidential to
> the addressee. If you are not the addressee indicated in this message or
> responsible for delivery of the message to such person, you may not copy
> or deliver this message to anyone, and you should destroy it and kindly
> notify the sender by reply email.
>
> Information in this message that does not relate to the official business
> of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
> Weatherbeeta, its employees, contractors or associates shall not be liable
> for direct, indirect or consequential loss arising from transmission of this
> message or any attachments
>
> THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
> e-mail.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: populate value of column
2007-08-08 05:30 populate value of column novice <user.postgresql@gmail.com>
@ 2007-08-08 05:40 ` Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Glaesemann @ 2007-08-08 05:40 UTC (permalink / raw)
To: novice <user.postgresql@gmail.com>; +Cc: pgsql-sql
On Aug 8, 2007, at 0:30 , novice wrote:
> CREATE TABLE meter
> (
> meter_id integer NOT NULL,
> area_no integer NOT NULL,
> CONSTRAINT meter_pkey PRIMARY KEY (meter_id)
> ) ;
>
> INSERT INTO meter(meter_id, no_of_bays) VALUES (1001, 4);
> INSERT INTO meter(meter_id, no_of_bays) VALUES (1012, 6);
Your insert columns don't match your table.
> How can I generate the following result?
>
> meter_id | bay
> ----------+------------
> 1001 | 01
Your column headers don't match your table definition *or* your
insert statements. I'm not sure what you're trying to do. Can you
explain the result you want? (And in the future, please post actual
DDL and statements.)
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: populate value of column
@ 2007-08-15 03:15 Phillip Smith <phillip.smith@weatherbeeta.com.au>
0 siblings, 0 replies; 5+ messages in thread
From: Phillip Smith @ 2007-08-15 03:15 UTC (permalink / raw)
To: 'Ronald Rojas' <ronald.rojas@gmail.com>; +Cc: pgsql-sql
I believe they are standard PostgreSQL functions – not sure what version
they appeared in though.
I learnt about them by RTM
Cheers,
~p
-----Original Message-----
From: Ronald Rojas [mailto:ronald.rojas@gmail.com]
Sent: Tuesday, 14 August 2007 18:00
To: Phillip Smith
Subject: Re: [SQL] populate value of column
Hi philipp,
How do i get those built-in function like LPAD, GENERATE_SERIES in
postgresql? is there a query where I can list down those?
Thanks in advance.
Ronald
On Wed, 2007-08-08 at 15:37 +1000, Phillip Smith wrote:
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of novice
Sent: Wednesday, 8 August 2007 15:31
To: pgsql-sql@postgresql.org
Subject: [SQL] populate value of column
> How can I generate the following result?
>
> meter_id | bay
> ----------+------------
> 1001 | 01
> 1001 | 02
> 1001 | 03
> 1001 | 04
> 1012 | 01
> 1012 | 02
> 1012 | 03
> 1012 | 04
> 1012 | 05
> 1012 | 06
I even tested this one too ;)
SELECT meter_id,
LPAD(GENERATE_SERIES(1,area_no),2,'0')
FROM meter;
Works on 8.2.4 - you didn't say what version you were using.
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by
Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
e-mail.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
============================================================================
======================
Ronald Rojas
Systems Administrator
Linux Registered User #427229
============================================================================
======================
MSDOS didn't get as bad as it is overnight -- it took over ten years
of careful development.
-- dmeggins@aix1.uottawa.ca
THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-08-15 03:15 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-08 05:30 populate value of column novice <user.postgresql@gmail.com>
2007-08-08 05:37 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-08-08 05:50 ` novice <user.postgresql@gmail.com>
2007-08-08 05:40 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-15 03:15 Re: populate value of column Phillip Smith <phillip.smith@weatherbeeta.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox