Thread: Formatting Function..
Hi.. I am newbe in postgresql so please help me though the question may be very easy to answer.. Is there any formatting function to get output with fix lengths..for example my query is.. schema is: Student (name Varchar, age integer); select name, age from student; the output is like this.. Name | Age xyz | 22 I want this length of name to be of my choice...is it possible..& how.. thanks in advance Regards Vinay -- Vinay Jain Dissertation Project Trainee DAKE Division C-DAC Mumbai
Perhaps, select cast(name as varchar(desired length)) as name, age from student; Mike On Mon, 2004-07-05 at 00:19, Vinay Jain wrote: > Hi.. > I am newbe in postgresql so please help me though the question may be > very easy to answer.. > Is there any formatting function to get output with fix lengths..for > example my query is.. > schema is: > > Student > (name Varchar, > age integer); > > select name, age from student; > the output is like this.. > Name | Age > xyz | 22 > > I want this length of name to be of my choice...is it possible..& how.. > > thanks in advance > Regards > Vinay >
Then again varchar might not keep the padded blanks by default. Perhaps casting to a char data type would be better. On Mon, 2004-07-05 at 00:44, mike g wrote: > Perhaps, > > select cast(name as varchar(desired length)) as name, age from student; > > Mike > On Mon, 2004-07-05 at 00:19, Vinay Jain wrote: > > Hi.. > > I am newbe in postgresql so please help me though the question may be > > very easy to answer.. > > Is there any formatting function to get output with fix lengths..for > > example my query is.. > > schema is: > > > > Student > > (name Varchar, > > age integer); > > > > select name, age from student; > > the output is like this.. > > Name | Age > > xyz | 22 > > > > I want this length of name to be of my choice...is it possible..& how.. > > > > thanks in advance > > Regards > > Vinay > >
To format the column names you could try
select cast(name as char(desired length)) as "Name", age as "Age" from
student;
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of mike g
Sent: Monday, 5 July 2004 3:44 PM
To: Vinay Jain
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Formatting Function..
Perhaps,
select cast(name as varchar(desired length)) as name, age from student;
Mike
On Mon, 2004-07-05 at 00:19, Vinay Jain wrote:
> Hi..
> I am newbe in postgresql so please help me though the question may be
> very easy to answer..
> Is there any formatting function to get output with fix lengths..for
> example my query is..
> schema is:
>
> Student
> (name Varchar,
> age integer);
>
> select name, age from student;
> the output is like this..
> Name | Age
> xyz | 22
>
> I want this length of name to be of my choice...is it possible..& how..
>
> thanks in advance
> Regards
> Vinay
>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Hi... Thanks for response I don't want casting... ...is this the only solution.. Regards Vinay On Mon, 5 Jul 2004 16:17:53 +1000, Andrew Bartley <abartley@evolvosystems.com> wrote: > To format the column names you could try > > select cast(name as char(desired length)) as "Name", age as "Age" from > student; > > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of mike g > Sent: Monday, 5 July 2004 3:44 PM > To: Vinay Jain > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Formatting Function.. > > Perhaps, > > select cast(name as varchar(desired length)) as name, age from student; > > Mike > On Mon, 2004-07-05 at 00:19, Vinay Jain wrote: > > Hi.. > > I am newbe in postgresql so please help me though the question may be > > very easy to answer.. > > Is there any formatting function to get output with fix lengths..for > > example my query is.. > > schema is: > > > > Student > > (name Varchar, > > age integer); > > > > select name, age from student; > > the output is like this.. > > Name | Age > > xyz | 22 > > > > I want this length of name to be of my choice...is it possible..& how.. > > > > thanks in advance > > Regards > > Vinay > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- Vinay Jain Dissertation Project Trainee DAKE Division C-DAC Mumbai
Hi Vinay Using cast will pad the output with blanks. Do you want to dynamically change the length of the name column? Thanks Andrew -----Original Message----- From: Vinay Jain [mailto:vinayjain@gmail.com] Sent: Monday, 5 July 2004 4:53 PM To: abartley@evolvosystems.com Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Formatting Function.. Hi... Thanks for response I don't want casting... ...is this the only solution.. Regards Vinay On Mon, 5 Jul 2004 16:17:53 +1000, Andrew Bartley <abartley@evolvosystems.com> wrote: > To format the column names you could try > > select cast(name as char(desired length)) as "Name", age as "Age" from > student; > > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of mike g > Sent: Monday, 5 July 2004 3:44 PM > To: Vinay Jain > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Formatting Function.. > > Perhaps, > > select cast(name as varchar(desired length)) as name, age from student; > > Mike > On Mon, 2004-07-05 at 00:19, Vinay Jain wrote: > > Hi.. > > I am newbe in postgresql so please help me though the question may be > > very easy to answer.. > > Is there any formatting function to get output with fix lengths..for > > example my query is.. > > schema is: > > > > Student > > (name Varchar, > > age integer); > > > > select name, age from student; > > the output is like this.. > > Name | Age > > xyz | 22 > > > > I want this length of name to be of my choice...is it possible..& how.. > > > > thanks in advance > > Regards > > Vinay > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- Vinay Jain Dissertation Project Trainee DAKE Division C-DAC Mumbai
Hi.. This is not the case...Actually I am not using VARCHAR...I am using my own data type named INDCHAR...Now i want to display it according to user supplied length..For casting I will have to add more functionalities which involves a lot of coding... I was looking for some function (if one exists) which just reserves a perticular length in terms of either pixels or characters... Thanks On Mon, 5 Jul 2004 17:05:26 +1000, Andrew Bartley <abartley@evolvosystems.com> wrote: > Hi Vinay > > Using cast will pad the output with blanks. > > Do you want to dynamically change the length of the name column? > > Thanks > > Andrew > > > > > -----Original Message----- > From: Vinay Jain [mailto:vinayjain@gmail.com] > Sent: Monday, 5 July 2004 4:53 PM > To: abartley@evolvosystems.com > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Formatting Function.. > > Hi... > Thanks for response > I don't want casting... ...is this the only solution.. > > Regards > Vinay > > On Mon, 5 Jul 2004 16:17:53 +1000, Andrew Bartley > <abartley@evolvosystems.com> wrote: > > To format the column names you could try > > > > select cast(name as char(desired length)) as "Name", age as "Age" from > > student; > > > > > > > > -----Original Message----- > > From: pgsql-general-owner@postgresql.org > > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of mike g > > Sent: Monday, 5 July 2004 3:44 PM > > To: Vinay Jain > > Cc: pgsql-general@postgresql.org > > Subject: Re: [GENERAL] Formatting Function.. > > > > Perhaps, > > > > select cast(name as varchar(desired length)) as name, age from student; > > > > Mike > > On Mon, 2004-07-05 at 00:19, Vinay Jain wrote: > > > Hi.. > > > I am newbe in postgresql so please help me though the question may be > > > very easy to answer.. > > > Is there any formatting function to get output with fix lengths..for > > > example my query is.. > > > schema is: > > > > > > Student > > > (name Varchar, > > > age integer); > > > > > > select name, age from student; > > > the output is like this.. > > > Name | Age > > > xyz | 22 > > > > > > I want this length of name to be of my choice...is it possible..& how.. > > > > > > thanks in advance > > > Regards > > > Vinay > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 7: don't forget to increase your free space map settings > > > > -- > Vinay Jain > Dissertation Project Trainee > DAKE Division > C-DAC Mumbai > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- Vinay Jain Dissertation Project Trainee DAKE Division C-DAC Mumbai
you should try with rpad and lpad functions:
select rpad('hello world', 20, '*'); ---> hello world*********
select lpad('hello world', 20, '*'); ---> *********hello world
hope this is what you were looking for.
On Mon, 2004-07-05 at 02:19, Vinay Jain wrote:
select rpad('hello world', 20, '*'); ---> hello world*********
select lpad('hello world', 20, '*'); ---> *********hello world
hope this is what you were looking for.
On Mon, 2004-07-05 at 02:19, Vinay Jain wrote:
Hi.. I am newbe in postgresql so please help me though the question may be very easy to answer.. Is there any formatting function to get output with fix lengths..for example my query is.. schema is: Student (name Varchar, age integer); select name, age from student; the output is like this.. Name | Age xyz | 22 I want this length of name to be of my choice...is it possible..& how.. thanks in advance Regards Vinay
Attachment
You could always use SUBSTR(Name,x,y) AS xxx to control the size of the output and the heading.
Duane
-----Original Message-----
From: Vinay Jain [mailto:vinayjain@gmail.com]
Sent: Sunday, July 04, 2004 10:20 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Formatting Function..
Hi..
I am newbe in postgresql so please help me though the question may be
very easy to answer..
Is there any formatting function to get output with fix lengths..for
example my query is..
schema is:
Student
(name Varchar,
age integer);
select name, age from student;
the output is like this..
Name | Age
xyz | 22
I want this length of name to be of my choice...is it possible..& how..
thanks in advance
Regards
Vinay
--
Vinay Jain
Dissertation Project Trainee
DAKE Division
C-DAC Mumbai
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
vinayjain@gmail.com (Vinay Jain) wrote in
news:c729bfe0040704221910989de8@mail.gmail.com:
> Hi..
> I am newbe in postgresql so please help me though the question may be
> very easy to answer..
> Is there any formatting function to get output with fix lengths..for
> example my query is..
> schema is:
>
> Student
> (name Varchar,
> age integer);
>
> select name, age from student;
> the output is like this..
> Name | Age
> xyz | 22
I am a little curious about where you want your data to be presented. If it
is only output in psql or other places where text is presented with a fixed
with font then my suggestions will help.
(If the data is to be outputted in html format then there are other
methods.)
One option is to store text data in char coloumns, it will fill the field
with spaces to the left. This i will only recommend in special cases.
Use formatting functions instead
for text use lpad and rpad
for numbers use to_char
example:
select rpad(name,20) as 'Name', to_char(age,'9999') as 'Age' from student;
read more about it in the manual (Chapter 9 in the 7.4. documentation)
--
Rolf