Sometimes it’s useful to remove all punctuation from a field.
Take the sentence
It's done - and dusted, finally. & that's that.. We’ll use regexp_replace on this.
select regexp_replace(lower($$It's done - and dusted, finally. & that's that. $$),$$\sand\s|[&\.,\s'-]$$,$$$$,$$g$$)
That results in
itsdonedustedfinallythatsthat. We’ve also removed the word
If we use
It's done--and dusted, finally. & that's that. then the
\sand\s matcher would fail. It’s an exercise to the reader to work a way around that.
We’ve also not dealt with
$ and the like, but that’s also facile to remove using the above.
If you don’t want to enter your password in everytime you use
psql, you can use a .pgpass file.
The file itself–
.pgpass–is in the format
Importantly, you need to give the password file the
600 permission, or psql will just ignore it, so:
chmod 600 .pgpass
Then psql should, normally, just pick that up. If not do something like
PGPASSFILE=/home/dave/.pgpass psql -h localhost -U dave -d my_db
If you want to dump all the functions in your schema, public in the case, do this:
SELECT pg_get_functiondef(f.oid) FROM pg_catalog.pg_proc f INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid) WHERE n.nspname = 'public';
If you issue
\o somefile.sql and then issue the above you’ll save the functions to a file.
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+ 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.
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.