PostgreSQL
March 08, 2013
Update one table from another
-- UG only update dss_chapuza.proposal set proposal_title = tc.new_title from dss_marking.ug_title_change_feeder tc where tc.proposal_id = proposal.proposal_id
Posted by pj at 01:06 PM | Comments (0)
March 07, 2013
Get BST dates in PostgreSQL
select
date_trunc('month', (extract('year' from now()::timestamp)::text||'-03-01 00:00:00 GMT'::text)::timestamp + '1 month'::interval) - '1 day'::interval + (((1 - 7 - to_char(date_trunc('month', (extract('year' from now()::timestamp)::text||'-03-01 00:00:00 GMT'::text)::timestamp + '1 month'::interval) - '1 day'::interval,'D')::int) %7)||' days')::interval
as last_sunday_march,
date_trunc('month', (extract('year' from now()::timestamp)::text||'-10-01 00:00:00 GMT'::text)::timestamp + '1 month'::interval) - '1 day'::interval + (((1 - 7 - to_char(date_trunc('month', (extract('year' from now()::timestamp)::text||'-10-01 00:00:00 GMT'::text)::timestamp + '1 month'::interval) - '1 day'::interval,'D')::int) %7)||' days')::interval
as last_sunday_october
Posted by pj at 03:52 PM | Comments (0)
February 08, 2013
Sort case where name includes more than one word
update scratch.student set forename=initcap(lower(forename))
Posted by pj at 01:49 PM | Comments (0)
January 07, 2013
Postgres Array Functions
PostgreSQL: Documentation: 8.4: Array Functions and OperatorsPosted by pj at 12:28 PM | Comments (0)
December 13, 2012
Select options for this and next year
$yopts = just::options("
(select
to_char(to_timestamp( now()::abstime::int4), 'YYYY')::integer as labello,
'year_'||to_char(to_timestamp( now()::abstime::int4), 'YYYY')::integer::text as year)
union
(select (to_char(to_timestamp( now()::abstime::int4), 'YYYY')::integer) + 1 as labello,
'year_'||((to_char(to_timestamp( now()::abstime::int4), 'YYYY')::integer) + 1)::text as year)
"
, 'year', 'labello');
Posted by pj at 12:49 PM | Comments (0)
September 10, 2012
Casting and formatting dates in PostgreSQL
select to_char(to_timestamp(now()::abstime::int4), 'Day DD Month YYYY HH24:MI:SS') as "The Day Today"
Posted by pj at 09:04 PM | Comments (0)
July 27, 2012
Appending to an array column in PostgreSQL
update dss_change_request.change_request set approved_notes = approved_notes||ARRAY['Blah blah blah'] where change_request_id = 13
Posted by pj at 03:05 PM | Comments (0)
December 07, 2011
Cast the results of a subquery as an array then join as a string
select p.*,
array_to_string(array(select first_name||' '||last_name from publications.authors a where a.publication_id = p.id), ',') as aus ,
array_to_string(array(select first_name||' '||last_name from publications.editors e where e.publication_id = p.id), ',') as eds
from publications.publication_as_ris3 p
where p.av in ('Forthcoming','Published') and p.id in ((select publication_id from publications.authors where first_name = 'Tony' and last_name = 'Kinder')union(select publication_id from publications.editors where first_name = 'Tony' and last_name = 'Kinder'))
Posted by pj at 12:31 PM | Comments (0)
November 26, 2011
Server replication in Postgres 9.1
Postgres slave server in five minutesFiles: Postgres_9.1_replication_standby_files.tar.gz
Binary Replication in 6 Steps
This 6-step guide, and all of the examples in this tutorial, assume that you have a master server at 192.168.0.1 and a standby server at 192.168.0.2 and that your database and its configuration files are installed at /var/lib/postgresql/data. Replace those with whatever your actual server addresses and directories are.
1. Edit postgresql.conf on the master to turn on streaming replication. Change these settings:
listen_address = '*' wal_level = hot_standby max_wal_senders = 3
2. Edit pg_hba.conf on the master in order to let the standby connect.
host replication all 192.168.0.2/32 trust
3. Edit recovery.conf and postgresql.conf on the standby to start up replication and hot standby. First, in postgresql.conf, change this line:
hot_standby = on
Then create a file in the standby's data directory (which is often the same directory as postgresql.conf and pg_hba.conf, except on some Linux distributions such as Debian and Ubuntu), called recovery.conf, with the following lines:
standby_mode = 'on' primary_conninfo = 'host=192.168.0.1'
4. Shutdown the master and copy the files. You want to copy most but not all files between the two servers, excluding the configuration files and the pg_xlog directory.
tar cvzhf /Users/paulhollands/Desktop/data.tgz --exclude 'pg_xlog' --exclude 'postgresql.conf' data/
5. Start the standby first, so that they can't get out of sync.
6. Start the master.
Posted by pj at 12:10 PM | Comments (0)
November 01, 2011
Cast a date to Unix timestamp in Postgres
'2012-04-01'::timestamp::abstime::int4
Posted by pj at 11:31 AM | Comments (0)
October 10, 2011
Kill runaway Postgres threads
kill `ps -ef | grep [p]ostgres | awk '{print $2}'`
Posted by pj at 05:05 PM | Comments (0)
September 28, 2011
Casting Unix timestamps to PostgreSQL timestamps
select *, to_timestamp(insert_date) as inserted from test where 1 limit 1;
Data Type Formatting Functions
Posted by pj at 12:55 PM | Comments (0)
May 20, 2010
Overlapping dates revisited
select distinct s1.resident_slot_id from eclub.resident_slot s1, eclub.resident_slot s2 where s1.start_time < s2.end_time and s2.start_time < s1.end_time and s1.resident_id = s2.resident_id and s1.resident_slot_id != s2.resident_slot_id and s2.expired_date is null and s1.expired_date is null
Posted by pj at 04:21 PM | Comments (0)
October 19, 2009
PostgreSQL 7.4: Mathematical Functions and Operators
PostgreSQL: Documentation: Manuals: PostgreSQL 7.4: Mathematical Functions and Operators
-43
Posted by pj at 04:55 PM | Comments (0)
October 01, 2009
De-dupe but leave one of each with one SQL query
Using sick sub-queries:
delete from ug_abstract where ug_abstract_id in
-- Get a list of duplicated rows based upon a comparison of the title
(
select ug.ug_abstract_id
from ug_abstract ug, ug_abstract ag
where ug.title = ag.title and ug.ug_abstract_id != ag.ug_abstract_id and ug.ug_abstract_id not in
-- Get the PK from one of each of the rows that have been duplicated
-- then exclude them from the delete run with a "not in"
(select max(ug.ug_abstract_id) as ug_abstract_id
from ug_abstract ug, ug_abstract ag
where ug.title = ag.title and ug.ug_abstract_id != ag.ug_abstract_id group by ug.title)
group by ug.title, ug.ug_abstract_id order by ug.title
)
Posted by pj at 04:23 PM | Comments (0)
September 25, 2009
Mental BST events SQL with cluster tags included
-- All dates are plus one hour to accomodate BST '1970-01-01 01:00:00 GMT'
select distinct e.event_id, e.event_name, e.event_start_date, e.event_start_time, t.asset_id, '1970-01-01 01:00:00 GMT'::timestamp +
((e.event_start_date::bigint)::text)::interval as ma_date,
-- Get the day from the date and pad it with a leading zero if less than 10
case when extract('day' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval) < 10 then
'0'||extract('day' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval)::text else
extract('day' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval)::text end
as ma_day,
-- Get the month from the date and pad it with a leading zero if less than 10
case when extract('month' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval) < 10 then
'0'||extract('month' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval)::text else
extract('month' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval)::text end
as ma_month,
-- Get the year from the date
extract('year' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval) as ma_year,
-- Get the hours and minutes from the date + the start time and pad them with a leading zeros if less than 10
case when extract('hour' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint + (e.event_start_time * 60 * 60))::text)::interval) < 10 then
'0'||extract('hour' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint + (e.event_start_time * 60 * 60))::text)::interval)::text
else extract('hour' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint + (e.event_start_time * 60 * 60))::text)::interval)::text end
as start_hour,
case when extract('minute' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint + (e.event_start_time * 60 * 60))::text)::interval) < 10 then
'0'||extract('minute' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint + (e.event_start_time * 60 * 60))::text)::interval)::text
else extract('minute' from '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint + (e.event_start_time * 60 * 60))::text)::interval)::text end as start_min,
-- Get two tags
max(rc.research_cluster_name) as tag_one,
min(rc.research_cluster_name) as tag_two
from event e
-- left join in the research cluster (tag) table
left join event_research_cluster_link ecl on (ecl.event_id = e.event_id and ecl.expired_date is null)
left join research_cluster rc on (ecl.research_cluster_id = rc.research_cluster_id and ecl.expired_date is null),
-- Join on the heading and transaction table (latter for the asset_id)
event_heading_link l, heading h, transaction t where l.event_id = e.event_id and l.heading_id = h.heading_id and l.heading_id in (2)
and t.event_id = e.event_id and t.asset_id is not null
-- Check that the event_start_date is in British Summer Time
and ('1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval between
-- Get the last Sunday in March
date_trunc('month', (extract('year' from now()::timestamp)::text||'-03-01 00:00:00 GMT'::text)::timestamp + '1 month'::interval) - '1 day'::interval + (((1 - 7 - to_char(date_trunc('month', (extract('year' from now()::timestamp)::text||'-03-01 00:00:00 GMT'::text)::timestamp + '1 month'::interval) - '1 day'::interval,'D')::int) %7)||' days')::interval
and
-- Get the last Sunday in October
date_trunc('month', (extract('year' from now()::timestamp)::text||'-10-01 00:00:00 GMT'::text)::timestamp + '1 month'::interval) - '1 day'::interval + (((1 - 7 - to_char(date_trunc('month', (extract('year' from now()::timestamp)::text||'-10-01 00:00:00 GMT'::text)::timestamp + '1 month'::interval) - '1 day'::interval,'D')::int) %7)||' days')::interval )
-- This is an archive so the start date must be before now
and '1970-01-01 01:00:00 GMT'::timestamp + ((e.event_start_date::bigint)::text)::interval < current_timestamp
group by e.event_start_date, e.event_start_time, e.event_name, t.asset_id, e.event_id
order by e.event_start_date desc, e.event_start_time desc
Posted by pj at 02:32 PM | Comments (0)
May 21, 2009
Example: Loading binary image files into a PostgreSQL OID (blob)
Posted by pj at 02:22 PM | Comments (0)
Adding MySQL-like date functions with custom functions in PostgreSQL
How to add unix_timestamp() and from_unixtime() to PostgreSQL?
Posted by pj at 10:17 AM | Comments (0)