Today I Learned

5 posts about #sql

PosgreSQL "DROP DATABASE.." RTFM

I have had over 200 databases to move from server to a new one. When a testing “import” succeeded I was ready for a final-final production move.

But I had to clean up the server first and remove all existing databases from “the testing” stage, I even created a SQL function:

DO $$ DECLARE
    db RECORD;
BEGIN
    FOR db IN (SELECT datname FROM pg_database where datdba != 10 AND datdba != 16385 LOOP
        EXECUTE 'DROP DATABASE IF EXISTS ' || quote_ident(db.tablename) ;
    END LOOP;
END $$;

but it did not work as PostgreSQL does not allow to drop a database from a function 😂

Finally, I’ve used a shell script to trigger DROP DATABASE.

integer limit is adjustable in activerecord migration

create_table 'example' do |t|
  t.integer :int                 # int (4 bytes, max 2,147,483,647)
  t.integer :int1, :limit => 1   # tinyint (1 byte, -128 to 127)
  t.integer :int2, :limit => 2   # smallint (2 bytes, max 32,767)
  t.integer :int3, :limit => 3   # mediumint (3 bytes, max 8,388,607)
  t.integer :int4, :limit => 4   # int (4 bytes)
  t.integer :int5, :limit => 5   # bigint (8 bytes, max 9,223,372,036,854,775,807)
  t.integer :int8, :limit => 8   # bigint (8 bytes)
  t.integer :int11, :limit => 11 # int (4 bytes)
end