New answers tagged

4

If those are always at the same position you can use string_to_array and then access the array position, e.g. select (string_to_array('/userfiles/account/5084/attachment/26607871/file/2771022/main.with_name/photo.jpg', '/'))[4] for the account ID. Array indexes are 1-based in Postgres, but the leading / leads to an empty entry for the index 1, that's ...


-2

for all changes in table structure of two databases : SELECT table_schema, table_name, column_name,ordinal_position,data_type,column_type FROM ( SELECT table_schema, table_name, column_name,ordinal_position, data_type,column_type,COUNT(1) rowcount FROM information_schema.columns WHERE table_schema IN ('database1', 'database2') ...


2

if any errors, roll the whole thing back; It's simpler than you seem to think. Any exception in a transaction (that is not trapped somehow) triggers a ROLLBACK for the whole transaction automatically. You don't have to do anything extra. BEGIN; CREATE TABLE IF NOT EXISTS blah ( ... ); -- much more DDL COMMIT; You cannot start, commit or roll back ...



Top 50 recent answers are included