I'm new to PostgreSQL. I am working on a project to convert our MSSQL database to PostgreSQL. Right now all our tables and columns are documented using extended properties (MS_Description). I would like to be able to keep all of these descriptions, but can not seem to figure out if there is a way to convert these to PostgreSQL. Any ideas on how I might do it? I could always dump the info to a data dictionary table but would prefer not having the info separate from the actual tables/columns.
2 Answers
Use COMMENT command, e.g.:
comment on table test is 'My favotite table';
comment on column test.id is 'Primary key of my favorite table';
To retrieve these comments in SQL use the functions (see Table 9-60. Comment Information Functions):
select obj_description('test'::regclass, 'pg_class');
obj_description
-------------------
My favotite table
(1 row)
select col_description('test'::regclass, 1);
col_description
----------------------------------
Primary key of my favorite table
(1 row)
2 Comments
Helen
Thanks so much! This is very helpful.
For anyone else needing to do this, here is the script I wrote based on @klin's answer to get the descriptions from MS SQL and create the required PostgreSQL commands. The first select builds the table descriptions and the second builds the column descriptions:
SELECT N'comment on table '+ Lower(o1.[name])
+ N' is ''' + CAST(s1.value AS NVARCHAR(4000)) + ''';'
FROM sys.objects o1
INNER JOIN sys.extended_properties s1 on o1.object_id = s1.major_id
WHERE o1.type = 'U'
AND s1.name = 'MS_Description'
UNION
SELECT N'comment on column '+ Lower(o2.[name]) + '.' + Lower(c.[name])
+ N' is ''' + CAST(s2.value AS NVARCHAR(4000)) + ''';'
FROM sys.objects o2
INNER JOIN sys.columns c ON o2.object_id = c.object_id
INNER JOIN sys.extended_properties s2 on o2.object_id = s2.major_id
WHERE o2.type = 'U'
AND c.column_id = s2.minor_id
AND s2.name = 'MS_Description';