Smathermather's Weblog

Remote Sensing, GIS, Ecology, and Oddball Techniques

PostgreSQL table drop — a little fun with ouroboros

Posted by smathermather on February 11, 2016

My latests posts on PDAL have been fun. For the moment, a quick bit of code for dropping all the tables in your PostgreSQL database. BTW, the following is a bad idea. Many bad ideas are really useful. This is a really useful but bad idea:

echo "\dt" | psql | grep layer | awk '{print "DROP TABLE \"" $3"\";"}' | psql

How does this work? Let’s do a quick step through.

First we echo “\dt”.

$ echo "\dt"
\dt

This just prints literally (the literally kind of literally) “\dt”. \dt is a psql command for listing all the tables in your database. We “pipe” (this symbol is a pipe: |) that into psql. Essentially what this does is run this command within psql as follows:

$ echo "\dt" | psql
               List of relations
 Schema |        Name        | Type  |  Owner  
--------+--------------------+-------+---------
 public | layer_0-1.5        | table | user
 public | layer_1.5-3        | table | user
 public | layer_105-150      | table | user
 public | layer_15-30        | table | user
 public | layer_150-200      | table | user
 public | layer_3-6          | table | user
 public | layer_30-45        | table | user
 public | layer_45-60        | table | user
 public | layer_6-15         | table | user
 public | layer_60-105       | table | user
 public | paw_points         | table | user
 public | pointcloud_formats | table | user
 public | spatial_ref_sys    | table | user
(13 rows)

Now that we have a list of tables, we can manipulate that list in order to manipulate our tables. We’ll use the grep command to go line by line and only return the ones with the word “layer” in them. These are the table names that we are interested in dropping.

$ echo "\dt" | psql | grep layer
 public | layer_0-1.5        | table | user
 public | layer_1.5-3        | table | user
 public | layer_105-150      | table | user
 public | layer_15-30        | table | user
 public | layer_150-200      | table | user
 public | layer_3-6          | table | user
 public | layer_30-45        | table | user
 public | layer_45-60        | table | user
 public | layer_6-15         | table | user
 public | layer_60-105       | table | user

Now we use the awk command to just grab the column we want. By default, awk assumes spaces are our column delimiter, so we’ll grab the third column, however we could also use the pipes as our delimiter with the -F flag:

$ echo "\dt" | psql | grep layer | awk '{print $3}'
layer_0-1.5
layer_1.5-3
layer_105-150
layer_15-30
layer_150-200
layer_3-6
layer_30-45
layer_45-60
layer_6-15
layer_60-105

Let us next extend the awk command to print the commands we want to run against our database. Note I had to escape my double quotes with a “\”:

$ echo "\dt" | psql | grep layer | awk '{print "DROP TABLE \"" $3"\";"}' 
DROP TABLE "layer_0-1.5";
DROP TABLE "layer_1.5-3";
DROP TABLE "layer_105-150";
DROP TABLE "layer_15-30";
DROP TABLE "layer_150-200";
DROP TABLE "layer_3-6";
DROP TABLE "layer_30-45";
DROP TABLE "layer_45-60";
DROP TABLE "layer_6-15";
DROP TABLE "layer_60-105";

Now we have the commands we need to feed back into psql to run against the database. Here is where the dragon eats its own tail:

echo "\dt" | psql | grep layer | awk '{print "DROP TABLE \"" $3"\";"}' | psql
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE

3 Responses to “PostgreSQL table drop — a little fun with ouroboros”

  1. Toby Speight said

    You can pass the ‘\dt’ command directly to psql, and you can get ‘awk’ to do the filtering for you:

    psql -c ‘\dt’ -tA | awk -F ‘|’ ‘$2 ~ /^layer/ {print “DROP TABLE \”” $2″\”;”}’

  2. Aweome Toby. What does the ~ do?

  3. Toby Speight said

    The ~ is a match operator – it makes awk match the regexp “/^layer/” against the field $2. I’m not proficient in awk; I just copied that from an example I found…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: