I have security table with three attributes cusip , isin, sedol with thousands of rows. I wanted to send all cusips to vendor 1, isins to vendor 1 and vendor 2 All sedolsto vendor 3. In my current implementation I have hardcoded cusip, isin, sedols to respective vendors in python code. I wanted to check is there any better solution to this?

One way is to create a column - vendor_name. But in that case I will have to specify vendor_name for all rows. Another way to add comments at attribute level and use sql to extract

SELECT pg_catalog.col_description(c.oid, a.attnum) AS column_comment,a.attname
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
WHERE c.relname = 'security' AND a.attnum > 0 AND NOT a.attisdropped;

or I will have to create a metadata table which is replica of security table with two columns. column name and vendor name.

In future there would be addition of more columns which will have to part of different vendors.

I have security table with three attributes cusip , isin, sedol with thousands of rows. I wanted to send all cusips to vendor 1, isins to vendor 1 and vendor 2 All sedolsto vendor 3. In my current implementation I have hardcoded cusip, isin, sedols to respective vendors in python code. I wanted to check is there any better solution to this?

One way is to create a column - vendor_name. But in that case I will have to specify vendor_name for all rows. Another way to add comments at attribute level and use sql to extract

SELECT pg_catalog.col_description(c.oid, a.attnum) AS column_comment,a.attname
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
WHERE c.relname = 'security' AND a.attnum > 0 AND NOT a.attisdropped;

or I will have to create a metadata table which is replica of security table with two columns. column name and vendor name.

In future there would be addition of more columns which will have to part of different vendors.

• postgresql

• It is not clear what exactly you are asking. Databases store data and retrieve information. They do not send anything anywhere. Sometimes it is necessary to use configuration options, which are placed either in postgresql.conf or in a key/value table, and in both cases a function is used to retrieve the corresponding value in the sql queries. – mr mcwolf Commented Apr 2 at 3:32
• If you're trying to avoid spelling out 'vendor N' because it's wasteful to repeat a string for all rows, you can make it an enum instead. Or add a table mapping smallint identifiers to those strings and use a foreign key to that table. I'm not sure what you have in mind with the SQL comment approach. Please try to add create statements for the tables involved, a few sample/mock rows and what result/end state you're trying to achieve based on those. – Zegarek Commented Apr 2 at 5:40
• Please describe what you need in more detail. Do you want to run a query against the database that returns these values? – Laurenz Albe Commented Apr 2 at 6:04

1 Answer 1

In my current implementation I have hardcoded cusip, isin, sedol to respective vendors in python code.

I think this is absolutely fine, as you have also hardcoded these 3 column names in your database schema. Only you can tell how likely/how often this setup is going to change, how dynamic your application needs to be, and how you handle migrations in your database schema.

If your python application needs to run with different databases that have different columns for different vendors, using dependency injection instead of hardcoded values and making the configuration easy to change (in the code, or in a config file) is the obvious first step.

Using comments on the attributes in the database schema is a fancy way to do this configuration. While allowing you to store everything in the database, and deploying/managing databases independently from your python application, it is rather unusual (but not unheard of). You may be in a situation where you benefit from this, but you may also not; changing configuration in a database schema (migration) may be harder than you'd like. And in any case, your python code probably still has vendor-specific code that does not allow adding arbitrary new vendors; so whether you choose these by column name or by vendor name might not even make much of a difference.

If you want to be completely free in your choice of vendors (and your python application can actually handle that), and need to support any number of vendors without doing a database migration, you probably will need to use the entity-attribute-value pattern.