jrrojasg

The standard names for indexes in PostgreSQL are:

{tablename}_{columnname(s)}_{suffix}

where the suffix is one of the following:

• pkey for a Primary Key constraint;
• key for a Unique constraint;
• excl for an Exclusion constraint;
• idx for any other kind of index;

jrrojasg

-- Create tables
CREATE TABLE groups (id serial NOT NULL, name character varying NOT NULL);
CREATE TABLE network_group_members (pid integer, cid integer NOT NULL);

-- Insert the groups
INSERT INTO groups (name) VALUES ('GROUP 1'),('GROUP 2'),('GROUP 3'),('GROUP 4'),('GROUP 5'),('GROUP 6'),('GROUP 7'),('GROUP 8'),('GROUP 9'),('GROUP 10'),('GROUP 11'),('GROUP 12'),('GROUP 13');
-- Build the "Network"
INSERT INTO network_group_members(pid,cid) VALUES (1,2),(1,3),(1,4),(2,5),(5,6),(5,7),(7,8),(3,9),(4,10),(4,11),(11,12),(12,13);

jrrojasg

CREATE OR REPLACE FUNCTION random_int_array(dim integer, min integer, max integer) RETURNS integer[] AS $BODY$
begin
	return (select array_agg(round(random() * (max - min)) + min) from generate_series (0, dim));
end
$BODY$ LANGUAGE plpgsql;

-- usage example
select random_int_array(15, 6, 40);

-- return example

jrrojasg

SchemaSpy is a neat tool to produce visual diagrams for most relational databases.

Here's how to use it to generate schema relationship diagrams for PostgreSQL databases:

1. Download the jar file from here (the current version is schemaSpy_5.0.0.jar)

2. Get the PostgreSQL JDBC driver (either the JDBC3 or JDBC4 jar file is fine)

3. Run the command against an existing database. For most databases, the schema (-s option) we are interested in is the public one:

jrrojasg

Usage

$ rake pg:query_time
total time (min) ▾ | average execution (ms) |            query
--------------------+------------------------+------------------------------
       2.0379       |       1153.5163        | SELECT COUNT(*) FROM "users"  WHERE ("users"."username" ILIKE ?)
       1.3497       |        809.8202        | INSERT INTO users (email, username, auth_token) VALUES (?, ?, ?), ...
       0.0056       |        111.416         | SELECT COUNT(*) FROM "users"  WHERE ("users"."avatar_file_name" ILIKE ?)
(3 rows)