Tech notes for sql


Postgresql: Window functions intro

The group by SQL statement winds up all rows based on a column. Then you use aggregate functions to output something based on the wound up rows.

Sometimes you don't want to wind up all the rows. You may want them to display as normal. But calculate a value on a load of rows (say, a 'window' of rows...).

This is where Window functions come into play. They allow you to specify a load of (or window of) rows on which to use a aggregate function.

Let's say we have this table.

 name  | department | salary 
-------+------------+--------
 chris | IT         |  30000
 jason | IT         |  35000
 kate  | IT         |  22000
 david | sales      |  40000
 matt  | sales      |  45000
 james | sales      |  50000

We could use this group by SQL statement to output salary averages of the departments.

select department, avg(salary) from employees group by department;
 department |        avg
------------+--------------------
 IT         | 29000.000000000000
 sales      | 45000.000000000000

But we may not want to wind all the columns up, just diplay an average column after each row. In that case:

   select name, department, salary, avg(salary) over (partition by department) from employees;
    name  | department | salary |        avg         
   -------+------------+--------+--------------------
    chris | IT         |  30000 | 29000.000000000000
    jason | IT         |  35000 | 29000.000000000000
    kate  | IT         |  22000 | 29000.000000000000
    david | sales      |  40000 | 45000.000000000000
    matt  | sales      |  45000 | 45000.000000000000
    james | sales      |  50000 | 45000.000000000000

We use the 'over' keyword to specify the column we want to partition our window by.

And, voila.

sql postgresql

Alter a Postgresql constraint on a table

If you have a constraint you wish to alter, you need to drop it and re-add it.

First let's look at the constraint with \d+ yourtable

\d+ youtable
...
Foreign-key constraints:
"constraint_name" FOREIGN KEY (some_id) REFERENCES yourtable(some_id)

We can we see the, in this case, foreign key constraint we want to alter. We'll use text above later.

Now let's drop the constraint.

alter table yourtable drop constraint constraint_name;

Now let's copy the text we saw above, and paste it after the text alter table yourtable add constraint.

alter table yourtable add constraint "constraint_name" FOREIGN KEY (some_id) REFERENCES yourtable(some_id) on delete cascade;

In our case, we altered the constraint by adding on delete cascade.

postgresql sql

Metadata about tables in postgresql (or in information_schema in general)

SELECT tables.table_name
FROM information_schema.tables
WHERE tables.table_schema = 'public' 
AND tables.table_name != 'schema_version' 
AND tables.table_type = 'BASE TABLE';

The above lists all the tables, excluding schema_version since we don't care about migration info and including only those in the public schema, the ones we created, excluding views by specifying the base table table type.

SELECT columns.table_name,
  columns.column_name,
  columns.data_type,
  columns.column_default,
  columns.is_nullable
 FROM information_schema.columns;

The above lists all the column names in your database, their type, the column default (you can work out if it's got a sequence etc) and if they're nullable

SELECT kcu.constraint_name,
    kcu.table_name,
    kcu.column_name 
  FROM information_schema.key_column_usage kcu
     LEFT JOIN information_schema.table_constraints tc ON tc.constraint_name = kcu.constraint_name
  WHERE tc.constraint_type = 'FOREIGN KEY';

The above lists all the foreign key constraints in your database, with the table name and column name which they belong to (not what they reference)

SELECT constraint_name, table_name, column_name 
FROM information_schema.constraint_column_usage;

The above lists all the constraints (foreign keys for example), along with the table name and column name which they reference.

postgresql sql

Setting up Postgresql 9.1 on Debian Squeeze

First add the squeeze backports to /etc/apt/sources.list:

deb http://backports.debian.org/debian-backports squeeze-backports main

Then issue:

apt-get update
apt-get -t squeeze-backports install postgresql-9.1

Then login as the postgres user and start up psql:

su - postgres
psql

Now create a new user with a new password and create a database as that user:

create user myuser password 'mypassword'
create database mydatabase owner myuser;
\q

Now you've exited psql, log out of the postgres unix user, and attach the the postgres program remotely:

<ctrl-d>
psql -h localhost -U myuser -d mydatabase

Now it'll ask you to enter in the password 'myuser'. You can now start issuing sql commands. Or ? to see help for psql commands.

unix postgresql sql

SQL: Renaming columns in sqlite

You can't easily do this in sqlite. Say you have a table with a schema:

CREATE TABLE ANOTHERTHING (ID NUMBER(10) NOT NULL, TEXT VARCHAR, PRIMARY KEY (ID));

And you want to rename the column 'text' to 'sometext', you need to make a new table with that schema, rename the current table, copy all the data over into the new table and then drop the renamed table:

alter table anotherthing rename to anotherthing_1;
create table anotherthing (id number(10) not null, sometext varchar, primary key (id));
insert into anotherthing(id, sometext) select id, text from anotherthing_1;
drop table anotherthing_1;
sql-sqlite sql sql-renamecolumns

Page 2 of 3
prev next
Click me