David M Kerr

Here is a little perl script that I use to give me a list of tables in a schema, a rowcount and the pg_relation_size of a table.

The usage is just:

pg_rowcount <database> <schema>

The output looks like this:

./pg_rowcount.pl david.kerr pg_catalog
Table                                Rows                 Size
--------------------------------------------------------------
pg_statistic                         1853              1672 kB
pg_type                               910               168 kB
pg_attribute                         9569              1488 kB
pg_class                             1371               416 kB
pg_authid                              52                16 kB
pg_index                              766               112 kB
pg_operator                           705               104 kB
pg_database                            44                16 kB
[...]

You can download the script here

I use a lot of "serial" data type fields, sometimes the nextval of my sequence doesn't reflect the max() of my table.

Since I do a lot of moving data around these can get messed up.

The basic command that you want to use to reset a sequence is:

select setval("<sequence name>", <value>);

However, the cool thing about Postgres is that <value> can be a query too so I use:

select setval("<sequence name>", max(<column name>) from <table>);

So I wrote a little perl script to look through all of the columns that have a default like "nextval" (which if you look in the database is how a serial column actually looks) and sets the sequence to the max of the value in that table.

You can get it here

Usage: reset_requences.pl -d <database> [-s <schema>] [-u <user>] [-p <password>]

If you specify a database it will reset all of the columns in the database
If you specify a schema it will reset all of the columns within a specific schema.
If you specify a user it will reset all of the columns for a specific user.

PostgreSQL provides the ability to return a value on insert. so for example:

create table foo (id serial, name text);
insert into foo('dave') returning id;

Will return the auto-generated value for "id" assigned to 'dave'

You can leverage this from within Perl by doing something like this:

my $foo_insert = << "EOH";
insert into foo(?) returning id
EOH

my $foo_cur = $dbh->prepare($foo_insert);
$foo_cur->execute('Dave');
my $foo_rec = $foo_cur->fetchrow_hashref();
print $$foo_rec{"id"};

Of course, you can use whichever fetch method you're comfortable with.

If you want to dummy up a bunch of data in PostgreSQL you can use this neat little trick

create table test (a int);
insert into test (select generate_series(0,999,1));
INSERT 0 1000

I frequently find the need to have single quoted output when i generate dynamic SQL.

It's always a pain to remember the exact number of ticks needed to get the quoted output.

Here is a reminder on how to do it:

select ''''||schemaname||'.'||tablename||'''' from pg_tables

4 quotes when there's no text, or 3 quotes on the outside and one on the inside when there is text.

select '''public.'||tablename||''';' from pg_tables

select 'select pg_size_pretty(pg_relation_size('''||tablename||'''));' from pg_tables where schemaname = 'bla';