CODEWITHSUNDEEP
sql-serverpostgresql
Helen
Helen

Reputation: 23

PostgreSQL ms_description equivalent?

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.

Upvotes: 1

Views: 393

Answers (2)

Helen
Helen

Reputation: 23

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';

Upvotes: 1

klin
klin

Reputation: 121654

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)

Upvotes: 2

Related Questions