![]() We can't all be power psql users like you and not all people want to source control everything in their life. This is not necessarily one I feel one tool is greatly stronger than another. (not everything) I like psql for a lot of things like automated testing and so forth. For most day to day use I prefer PgAdminIII Now getting back to your comment about bashing my favorite tool of choice and how I treat "my customers". I have taken your advice and included the single row ddl way, but I still prefer my way better for my general usage. I take it I must have offended you with my windows/PgAdmin III centric lifestyle to deserve such a backlash or you are just a person who likes to start debates. generates something like this ALTER TABLE public. WHERE t.table_schema NOT IN ( ' information_schema ', ' pg_catalog ' ) AND t.table_name lower (t.table_name ) ORDER BY t.table_schema, t.table_name ' | | quote_ident (t.table_name ) | | ' RENAME TO ' | | quote_ident ( lower (t.table_name ) ) | | ' ' As ddlsql ![]() lower case table names - the psql friendly and more reader-friendly way SELECT ' ALTER TABLE ' | | quote_ident (t.table_schema ) | | '. ' | | quote_ident (t.table_name ) | | ' RENAME TO ' | | quote_ident ( lower (t.table_name ) ) | | ' ' FROM information_schema.tables As t lower case table names - the PgAdmin centric way, single EXECUTE way SELECT array_to_string ( ARRAY ( SELECT ' ALTER TABLE ' | | quote_ident (t.table_schema ) | | '. It goes without saying, that you may run into the same issue with table names. WHERE c.table_schema NOT IN ( ' information_schema ', ' pg_catalog ' ) AND c.column_name lower (c.column_name ) ORDER BY c.table_schema, c.table_name, c.column_name ' | | quote_ident (c.table_name ) | | ' RENAME " ' | | c.column_name | | ' " TO ' | | quote_ident ( lower (c.column_name ) ) | | ' ' As ddlsql As David Fetter kindly pointed out, this looks cleaner, returns one record per ddl and is more psql friendly SELECT ' ALTER TABLE ' | | quote_ident (c.table_schema ) | | '. ' | | quote_ident (c.table_name ) | | ' RENAME " ' | | c.column_name | | ' " TO ' | | quote_ident ( lower (c.column_name ) ) | | ' ' FROM information_lumns As c This generates SQL you can then run - lower case column names - this puts the sql in a single record - suitable for running in a single EXECUTE statement or cut and paste from PgAdmin query window SELECT array_to_string ( ARRAY ( SELECT ' ALTER TABLE ' | | quote_ident (c.table_schema ) | | '. ![]() We are really big on self-documenting structures. It also gives you history about what will be changed so in a sense is self-documenting. This is our preferred way, because it doesn't assume anything about the underlying structure of the pg_catalog tables and therefore less likely to cause damage.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |