Post date: Jan 24, 2014 3:16:24 PM
Notes from my first day looking at PostgreSql. Some interesting features to be found.
-- generate range of numbers, with optional stepselect * from generate_series(1, 500, 5);-- generate range of dates, with optional stepSELECT * FROM generate_series('2014-01-01 00:00'::timestamp, '2014-03-04 12:00', '10 minutes');--use enum for querying, from http://gurjeet.singh.im/blog/2014/01/07/understanding-postgres-parameter-context/create type guc_context as enum ( 'internal', 'postmaster', 'sighup', 'backend', 'superuser', 'user');select name as parameter, context_enum > 'internal' as can_be_changed, context_enum = 'postmaster' as change_requires_restart, context_enum >= 'sighup' as can_be_changed_by_reload from (select name, context::guc_context as context_enum from pg_settings) as v;-- arraysSELECT ARRAY[2001, 2002, 2003] As yrs;-- put all years from a set in a new arraySELECT array(SELECT DISTINCT date_part('year', daDate) FROM generate_series('2011-01-01 00:00'::timestamp, '2014-03-04 12:00', '10 minutes') as daDate);-- make array from a string, splitting by dot. Retreive values using regular [],-- indexed from 1 and up (Visual basic style huh?)SELECT x[1] from (select string_to_array('Peter.Henell.was.here.recently', '.') As x) as arr;-- Slice array using :SELECT x[1:3] from (select string_to_array('Peter.Henell.was.here.recently', '.') As x) as arr; -- Concat arrays using double pipe ||SELECT x[1:3] || x[5:6] || x[4] from (select string_to_array('Peter.Henell.was.here.recently', '.') As x) as arr; -- create tables if they do not existcreate table IF NOT EXISTS Car (id serial primary key, brand varchar(50)); -- use array of cars in personcreate table IF NOT EXISTS Person (personId serial primary key, firstname varchar(50), cars car[]); -- create a single object of a car using the row constructorselect ROW(86,'Volvo')::car;truncate table Person;Insert into Person(personId, Cars)select personId, ARRAY[ROW(personId % 10, 'Volvo')::Car,ROW(personId % 5, 'Saab')::Car]FROM generate_series(1, 100) as personId;-- observe that all persons have some cars...select * from Person; -- ... but there are no cars in the Car tableselect * from Car;-- give someone another carupdate Person set cars = cars || ROW(55, 'Mazda')::Car where personId = 1;select * from Person where personId = 1;-- Inherited tables?! Sweetcreate table IF NOT EXISTS Employee (primary key(personId), EmployeeNumber int not null) INHERITS (Person);-- Create some employeesInsert into Employee(personId, Cars, EmployeeNumber)select personId, null, (date_part('year', now())::integer * 1000 ) + personId FROM generate_series(1, 100) as personId;-- observe themSelect * from Employee;-- They ALSO exist as Person, note the duplicate primary key?!?! The same personid exist twice.select * from Person where cars is null;select * from Person where cars is not null;-- Create unlogged table, supposedly up to 15 times faster than regular tables. -- These are not "in-memory only", they are written to disk at checkpoint. At server startup, they will be truncated.create UNLOGGED table If not exists rental_log (log_id bigserial primary key, info text not null);insert into rental_log (info)select a::textfrom generate_series(1, 50000) as a;-- Query returned successfully: 50000 rows affected, 161 ms execution time.-- now to compare with logged tablecreate table If not exists rental_log_logged (log_id bigserial primary key, info text not null);insert into rental_log_logged (info)select a::textfrom generate_series(1, 50000) as a;-- Query returned successfully: 50000 rows affected, 301 ms execution time.-- About double the speed for this stupid example...