PostgreSQL Notes

How to drop all tables sharing the same prefix in PostgreSQL?

DO
$do$
DECLARE
   _tbl text;
BEGIN
FOR _tbl  IN
    SELECT quote_ident(table_schema) || '.'
        || quote_ident(table_name)
    FROM   information_schema.tables
    WHERE  table_name LIKE '[prefix]' || '%'
    AND    table_schema NOT LIKE 'pg\_%'
LOOP
-- RAISE NOTICE '%',
  EXECUTE 'DROP TABLE ' || _tbl || ' CASCADE';
END LOOP;
END
$do$;
  • Non-Cascade version:
EXECUTE 'DROP TABLE ' || _tbl;

How to drop all functions in PostgreSQL?

DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          string_agg(format('DROP %s %s;'
                          , CASE WHEN proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END
                          , oid::regprocedure)
                   , E'\n')
   FROM   pg_proc
   WHERE  pronamespace = 'public'::regnamespace;

   IF _sql IS NOT NULL THEN
      RAISE NOTICE '%', _sql;
      -- EXECUTE _sql;
   ELSE 
      RAISE NOTICE 'No fuctions found in schema %', quote_ident(_schema);
   END IF;
END
$do$  LANGUAGE plpgsql;

Running PostgreSQL in Docker on my Mac

It’s extremely convenient to run your local tools, like RDMS, development environments and so on on Docker containers on Mac. To be honest with you, I hate installing a bunch of software on my Mac, and that’s the main reason why do I use containers for my local development.

Before doing that manual, make sure your Docker is installed on Mac. To do so, you can go to https://www.docker.com/products/docker-desktop and download the latest version of Docker for your Mac.

So, how do I can use PostgreSQL on Mac in a Docker container? Let’s create a new Project folder, or you may use your own where docker-compose files.

mkdir ~/Projects
cd ~/Projects

And creating a docker-compose.yml file with the following code:

#File: docker-compose.yml 
version: "3"
services:
  db:
    image: "postgres:11"
    container_name: "local_postgres"
    ports:
      - "54320:5432"
    volumes:
      - dbdata:/var/lib/postgresql/data
    environment:
      - POSTGRES_HOST_AUTH_METHOD=trust
      - POSTGRES_PASSWORD=somesecretpassword
volumes:
  dbdata:

So, a quick clarification on what we do here. We use postgres:11 image, naming containers as local_postgres, expose port 54320, store data on dbdata volume, use passwordless access to database (as you we do everything locally), and use somesecretpassword as password. Pretty straight forward, ha?

Ok, let’s build it and run it:

docker-compose up -d

You shouldn’t see any errors, and let’s see logs with the following command:

docker logs -f local_postgres

And if we can connect to database, like this:

docker exec -it local_postgres psql -U postgres

Hit CTRL+D to exit from the prompt.

Create a database

So, to create a database, you can run the following command, where new_database is database new you want to create.

docker exec -it my_postgres psql -U postgres -c "create database new_database"

You can use a database connection now with the following login details:

host='localhost',
port=54320,
dbname='hew_database',
user='postgres',
password='somesecretpassword'

That’s pretty much it. Easy, right?

Please comment below if you have any issues or questions!