Hello:

I continue to be working in my .NET Data provider for postgres 7.4, i 
want to know if there are a simple way for identify autoincrement 
fields, i see that for serial and big serial fields the default value is 
'nextval(...)' this can be a way for know it but i want to know if there 
are a better way :)


Thanks in advance.





-- 
Best regards

Carlos Guzmï¿½n ï¿½lvarez
Vigo-Spain


Sat, 02 Aug 2003 23:19:31 +0200
Carlos Guzman Alvarez <carlosga@telefonica.net>
Identification of serial fields
On Sat, 2003-08-02 at 17:19, Carlos Guzman Alvarez wrote:
> Hello:
> 
> I continue to be working in my .NET Data provider for postgres 7.4, i 
> want to know if there are a simple way for identify autoincrement 
> fields, i see that for serial and big serial fields the default value is 
> 'nextval(...)' this can be a way for know it but i want to know if there 
> are a better way :)

In SERIAL columns the sequence depends on the column, so the association
can be gathered from pg_depend.

Check for a relation between a sequence and column of a table where the
deptype = 'i'. (Taken from pg_dump)

SELECT c.oid, relname, relacl, relkind,
relnamespace,
(select usename from pg_user where relowner = usesysid) as usename,
relchecks, reltriggers,
relhasindex, relhasrules, relhasoids,
d.refobjid as owning_tab,
d.refobjsubid as owning_col
from pg_class c
join pg_depend d on (c.relkind = 'S' and
   d.classid = c.tableoid and d.objid = c.oid and
   d.objsubid = 0 and
   d.refclassid = c.tableoid and d.deptype = 'i')
where relkind in ('t', 'S', 'v')
order by c.oid
;
02 Aug 2003 17:39:24 -0400
Rod Taylor <rbt@rbt.ca>
Re: Identification of serial fields
Hello:

> In SERIAL columns the sequence depends on the column, so the association
> can be gathered from pg_depend.
> 
> Check for a relation between a sequence and column of a table where the
> deptype = 'i'. (Taken from pg_dump)


Thanks very much i will try it and comment the results here :)



-- 
Best regards

Carlos Guzmï¿½n ï¿½lvarez
Vigo-Spain


Sun, 03 Aug 2003 00:20:51 +0200
Carlos Guzman Alvarez <carlosga@telefonica.net>
Re: Identification of serial fields
Hello:

> Thanks very much i will try it and comment the results here :)

This is was i'm finally using for retirve column information:

SELECT pg_namespace.nspname AS TABLE_SCHEMA,
pg_class.relname AS TABLE_NAME,
pg_attribute.attname AS COLUMN_NAME,
pg_attribute.atttypid AS DATA_TYPE,
pg_attribute.attlen AS COLUMN_SIZE,
pg_attribute.attndims AS COLUMN_DIMENSIONS,
pg_attribute.attnum AS ORDINAL_POSITION,
pg_attribute.atthasdef AS HAS_DEFAULT,
pg_attrdef.adsrc AS COLUMN_DEFAULT,
pg_attribute.attnotnull AS IS_NOT_NULL,
(pg_depend.objid is not null) AS IS_AUTOINCREMENT,
case pg_attribute.attstorage  when 'p' THEN 'PLAIN' when 'e' THEN 
'EXTERNAL' when 'm' THEN 'MAIN' when 'x' THEN 'EXTENDED' END  AS STORAGE,
pg_description.description AS DESCRIPTION
FROM pg_attribute
left join pg_class ON pg_attribute.attrelid = pg_class.oid
left join pg_namespace ON pg_class.relnamespace = pg_namespace.oid
left join pg_attrdef ON (pg_class.oid = pg_attrdef.adrelid AND 
pg_attribute.attnum = pg_attrdef.adnum)
left join pg_description ON (pg_attribute.attrelid = 
pg_description.objoid AND pg_attribute.attnum = pg_description.objsubid) 
left join pg_depend ON (pg_attribute.attrelid = pg_depend.objid AND 
pg_depend.deptype = 'i')
WHERE pg_attribute.attisdropped = false AND pg_attribute.attnum > 0 
ORDER BY pg_namespace.nspname, pg_class.relname, pg_attribute.attnum

I'm not sure now if it's 100% correct but the first tests seems to work, 
i will try to make more tests tomorrow :)





-- 
Best regards

Carlos Guzmï¿½n ï¿½lvarez
Vigo-Spain


Sun, 03 Aug 2003 01:42:49 +0200
Carlos Guzman Alvarez <carlosga@telefonica.net>
Re: Identification of serial fields
Hello:

Finally after some tests i'm doing this:

SELECT pg_namespace.nspname AS TABLE_SCHEMA,
pg_class.relname AS TABLE_NAME,
pg_attribute.attname AS COLUMN_NAME,
pg_attribute.atttypid AS DATA_TYPE,
pg_attribute.attlen AS COLUMN_SIZE,
pg_attribute.attndims AS COLUMN_DIMENSIONS,
pg_attribute.attnum AS ORDINAL_POSITION,
pg_attribute.atthasdef AS HAS_DEFAULT,
pg_attrdef.adsrc AS COLUMN_DEFAULT,
pg_attribute.attnotnull AS IS_NOT_NULL,
(pg_depend.objid is not null) AS IS_AUTOINCREMENT,
case pg_attribute.attstorage  when 'p' THEN 'PLAIN' when 'e' THEN
'EXTERNAL' when 'm' THEN 'MAIN' when 'x' THEN 'EXTENDED' END  AS STORAGE,
pg_description.description AS DESCRIPTION
FROM pg_attribute
left join pg_class ON pg_attribute.attrelid = pg_class.oid
left join pg_namespace ON pg_class.relnamespace = pg_namespace.oid
left join pg_attrdef ON (pg_class.oid = pg_attrdef.adrelid AND 
pg_attribute.attnum = pg_attrdef.adnum)
left join pg_description ON (pg_attribute.attrelid = 
pg_description.objoid AND pg_attribute.attnum = pg_description.objsubid)
left join pg_depend ON (pg_attribute.attrelid = pg_depend.refobjid AND 
pg_attribute.attnum = pg_depend.refobjsubid  AND pg_depend.deptype = 'i')
WHERE pg_attribute.attisdropped = false AND pg_attribute.attnum > 0
ORDER BY pg_namespace.nspname, pg_class.relname, pg_attribute.attnum



-- 
Best regards

Carlos Guzmï¿½n ï¿½lvarez
Vigo-Spain


Sun, 03 Aug 2003 12:22:34 +0200
Carlos Guzman Alvarez <carlosga@telefonica.net>
Re: Identification of serial fields