Thread: Finding column using SQL query.
Hi, Is there any way to find whether a particular column exists in the table or not based on the column name using sql query? I have a requirement where I need to find whether a column exists in the table or not using SQL query. Thanks, Rajan
I tried finding relation between pg_attribute and pg_class but seems there is no common key between these two table. select * from pg_attribute where attrelid = (select reltype from pg_class where relname = 'mytablename'; attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | at tcacheoff | atttypmod | attbyval | attstorage | attisset | attalign | attnotnull | atthasdef ----------+---------+----------+---------------+--------+--------+------ ----+--- ----------+-----------+----------+------------+----------+----------+--- -------- -+----------- (0 rows) So this is not solving my problem. Am I missing somethg or there is some other way to find out? Plz comment. Thanks, Rajan -----Original Message----- From: email@juergen-cappel.de [mailto:email@juergen-cappel.de] Sent: Wednesday, February 09, 2005 3:38 PM To: Rajan Bhide Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Finding column using SQL query. Take a look at the system catalogs: http://www.postgresql.org/docs/8.0/interactive/catalogs.html HTH Rajan Bhide <rbhide@starentnetworks.com> schrieb am 09.02.2005, 10:56:20: > Hi, > > Is there any way to find whether a particular column exists in the > table or not based on the column name using sql query? I have a > requirement where I need to find whether a column exists in the table > or not using SQL query. > > Thanks, > Rajan > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match
>>pg_attribute.attrelid=pg_class.oid
I am not able to find any attribute name 'oid' for pg_class.
Is this new in 8.0?
Can you construct a query to explain this?
Thanks,
Rajan
-----Original Message-----
From: email@juergen-cappel.de [mailto:email@juergen-cappel.de]
Sent: Wednesday, February 09, 2005 5:02 PM
To: Rajan Bhide
Cc: pgsql-novice@postgresql.org
Subject: Re: RE: [NOVICE] Finding column using SQL query.
You have to link like this:
pg_attribute.attrelid=pg_class.oid
and to find the datatye of an attribute:
pg_attribute.atttypid=pg_type.oid
HTH, Jürgen
Rajan Bhide <rbhide@starentnetworks.com> schrieb am 09.02.2005,
12:10:54:
> I tried finding relation between pg_attribute and pg_class but seems
> there is no common key between these two table.
>
> select * from pg_attribute where attrelid = (select reltype from
> pg_class where relname = 'mytablename'; attrelid | attname |
> atttypid | attstattarget | attlen | attnum | attndims | at
> tcacheoff | atttypmod | attbyval | attstorage | attisset | attalign |
> attnotnull
> | atthasdef
> ----------+---------+----------+---------------+--------+--------+----
> ----------+---------+----------+---------------+--------+--------+--
> ----+---
> ----------+-----------+----------+------------+----------+----------+-
> ----------+-----------+----------+------------+----------+----------+-
> ----------+-----------+----------+------------+----------+----------+-
> --------
> -+-----------
> (0 rows)
>
>
> So this is not solving my problem.
> Am I missing somethg or there is some other way to find out? Plz
> comment.
>
> Thanks,
> Rajan
>
> -----Original Message-----
> From: email@juergen-cappel.de [mailto:email@juergen-cappel.de]
> Sent: Wednesday, February 09, 2005 3:38 PM
> To: Rajan Bhide
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Finding column using SQL query.
>
>
>
> Take a look at the system catalogs:
>
> http://www.postgresql.org/docs/8.0/interactive/catalogs.html
>
> HTH
>
>
>
> Rajan Bhide schrieb am 09.02.2005,
> 10:56:20:
> > Hi,
> >
> > Is there any way to find whether a particular column exists in the
> > table or not based on the column name using sql query? I have a
> > requirement where I need to find whether a column exists in the table
> > or not using SQL query.
> >
> > Thanks,
> > Rajan
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if
> your
> > joining column's datatypes do not match
Buddy. This is what I was looking out.
Thanks,
Rajan
-----Original Message-----
From: email@juergen-cappel.de [mailto:email@juergen-cappel.de]
Sent: Wednesday, February 09, 2005 5:26 PM
To: Rajan Bhide
Cc: pgsql-novice@postgresql.org
Subject: RE: [NOVICE] Finding column using SQL query.
Here is what I do in my application (slightly reduced for clarity):
select pg_class.relname,pg_type.typname,pg_attribute.attname
from pg_class,pg_type,pg_attribute
where pg_attribute.attrelid=pg_class.oid
and pg_attribute.atttypid=pg_type.oid
and pg_class.relname = 'yourtable'
Also you may look at the reference of attrelid on this page:
http://www.postgresql.org/docs/8.0/interactive/catalog-pg-attribute.html
HTH, Jürgen
Rajan Bhide <rbhide@starentnetworks.com> schrieb am 09.02.2005,
12:43:28:
> >>pg_attribute.attrelid=pg_class.oid
> I am not able to find any attribute name 'oid' for pg_class. Is this
> new in 8.0? Can you construct a query to explain this?
>
> Thanks,
> Rajan
>
>
> -----Original Message-----
> From: email@juergen-cappel.de [mailto:email@juergen-cappel.de]
> Sent: Wednesday, February 09, 2005 5:02 PM
> To: Rajan Bhide
> Cc: pgsql-novice@postgresql.org
> Subject: Re: RE: [NOVICE] Finding column using SQL query.
>
>
>
> You have to link like this:
>
> pg_attribute.attrelid=pg_class.oid
>
> and to find the datatye of an attribute:
>
> pg_attribute.atttypid=pg_type.oid
>
>
>
> HTH, Jürgen
>
>
>
>
> Rajan Bhide schrieb am 09.02.2005,
> 12:10:54:
> > I tried finding relation between pg_attribute and pg_class but seems
> > there is no common key between these two table.
> >
> > select * from pg_attribute where attrelid = (select reltype from
> > pg_class where relname = 'mytablename'; attrelid | attname |
> > atttypid | attstattarget | attlen | attnum | attndims | at
> > tcacheoff | atttypmod | attbyval | attstorage | attisset | attalign |
> > attnotnull
> > | atthasdef
> > ----------+---------+----------+---------------+--------+--------+--
> > ----------+---------+----------+---------------+--------+--------+--
> > ----------+---------+----------+---------------+--------+--------+--
> > ----+---
> > ----------+-----------+----------+------------+----------+----------+-
> > ----------+-----------+----------+------------+----------+----------+-
> > ----------+-----------+----------+------------+----------+----------+-
> > --------
> > -+-----------
> > (0 rows)
> >
> >
> > So this is not solving my problem.
> > Am I missing somethg or there is some other way to find out? Plz
> > comment.
> >
> > Thanks,
> > Rajan
> >
> > -----Original Message-----
> > From: email@juergen-cappel.de [mailto:email@juergen-cappel.de]
> > Sent: Wednesday, February 09, 2005 3:38 PM
> > To: Rajan Bhide
> > Cc: pgsql-novice@postgresql.org
> > Subject: Re: [NOVICE] Finding column using SQL query.
> >
> >
> >
> > Take a look at the system catalogs:
> >
> > http://www.postgresql.org/docs/8.0/interactive/catalogs.html
> >
> > HTH
> >
> >
> >
> > Rajan Bhide schrieb am 09.02.2005,
> > 10:56:20:
> > > Hi,
> > >
> > > Is there any way to find whether a particular column exists in the
> > > table or not based on the column name using sql query? I have a
> > > requirement where I need to find whether a column exists in the
> > > table or not using SQL query.
> > >
> > > Thanks,
> > > Rajan
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 9: the planner will ignore your desire to choose an index scan
> > > if
> > your
> > > joining column's datatypes do not match
On Wed, Feb 09, 2005 at 05:13:28PM +0530, Rajan Bhide wrote: > > > > pg_attribute.attrelid=pg_class.oid > > I am not able to find any attribute name 'oid' for pg_class. oid is a system column: http://www.postgresql.org/docs/8.0/static/ddl-system-columns.html > Is this new in 8.0? No, earlier versions PostgreSQL also had oid system columns. The documentation discourages their use as primary keys in user tables and says that future versions might disable their creation by default. http://www.postgresql.org/docs/8.0/static/datatype-oid.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/
I have a function that I am using to provide results back to a program.
I want/need to pass a long string approx. 400chars back from this.
I am getting cut off at 256...
Any way around this. I would be greatful for any help. Below is a dummy
function showing the error.It should return a long list of 'aaaaaa'with the
number of a's appended to the end.
select * from zfunc_test(7);
'aaaaaaa7'
select * from zfunc_test(254);
Gets chopped off to '...aaaa25'
-- Function: zfunc_test(int4)
-- DROP FUNCTION zfunc_test(int4);
CREATE OR REPLACE FUNCTION zfunc_test(int4)
RETURNS "varchar" AS
$BODY$DECLARE
v_length integer;
v_retval varchar;
v_counter integer;
BEGIN
v_length = $1;
v_counter =0;
v_retval :='';
WHILE v_counter < v_length LOOP
v_retval := v_retval || 'a';
v_counter:=v_counter +1;
END LOOP;
v_retval :=v_retval || CAST(v_length as VARCHAR);
return v_retval;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION zfunc_test(int4) OWNER TO postgres;
On Wed, Feb 09, 2005 at 12:30:51PM -0500, Oisin Glynn wrote: > > select * from zfunc_test(254); > Gets chopped off to '...aaaa25' I couldn't duplicate this problem -- I get the entire string. Maybe your client is truncating the value -- how are you communicating with the database? Regarding what your function does, are you familiar with the repeat() function described in the "String Functions and Operators" documentation? http://www.postgresql.org/docs/8.0/static/functions-string.html SELECT repeat('a', 10); repeat ------------ aaaaaaaaaa (1 row) -- Michael Fuhr http://www.fuhr.org/~mfuhr/
My application was using ODBC from Windows server to Postgres 8.0.0 beta5 on Windows I used the pgAdminIII SQL tool for my tests. Which failed and still fail. I just ran a test in psql and it works fine!!! Is this a possible issue in the odbc driver? How does the SQL tool with pgAdminIII work? I just found a max varchar len 254 in odbc driver settings I set this to 1000 but it did not make any difference. I then discovered a similar restriction in my app and have fixed it. Is there any downside to doing this? Should the sql tool in pgAdminIII return the whole thing? I was not aware of the string function you pointed out, thank you very much. Oisin ----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> To: "Oisin Glynn" <me@oisinglynn.com> Cc: <pgsql-novice@postgresql.org> Sent: Wednesday, February 09, 2005 12:51 Subject: Re: [NOVICE] Returning a long string (varchar from a function) > On Wed, Feb 09, 2005 at 12:30:51PM -0500, Oisin Glynn wrote: > > > > select * from zfunc_test(254); > > Gets chopped off to '...aaaa25' > > I couldn't duplicate this problem -- I get the entire string. Maybe > your client is truncating the value -- how are you communicating > with the database? > > Regarding what your function does, are you familiar with the repeat() > function described in the "String Functions and Operators" > documentation? > > http://www.postgresql.org/docs/8.0/static/functions-string.html > > SELECT repeat('a', 10); > repeat > ------------ > aaaaaaaaaa > (1 row) > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > >