Just Code‎ > ‎

PostgreSQL - Playing with postgresql, day 1

posted Jan 24, 2014, 7:16 AM by Peter Henell   [ updated Jan 24, 2014, 7:16 AM ]
Notes from my first day looking at PostgreSql. Some interesting features to be found.
-- generate range of numbers, with optional step
select * from generate_series(1, 500, 5);

-- generate range of dates, with optional step
SELECT * 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;


-- arrays
SELECT ARRAY[2001, 2002, 2003] As yrs;

-- put all years from a set in a new array
SELECT 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 exist
create table IF NOT EXISTS Car  (id serial primary key, brand varchar(50));	
-- use array of cars in person
create table IF NOT EXISTS Person  (personId serial primary key, firstname varchar(50), cars car[]);	

-- create a single object of a car using the row constructor
select 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 table
select * from Car;

-- give someone another car
update Person set cars = cars || ROW(55, 'Mazda')::Car where personId = 1;
select * from Person where personId = 1;

-- Inherited tables?! Sweet
create table 
IF NOT EXISTS 
Employee  (primary key(personId), EmployeeNumber int not null) INHERITS (Person);

-- Create some employees
Insert into Employee(personId, Cars, EmployeeNumber)
select personId, null, (date_part('year', now())::integer * 1000 ) + personId 
FROM 
	generate_series(1, 100) as personId;

-- observe them
Select * 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::text
from generate_series(1, 50000) as a;
-- Query returned successfully: 50000 rows affected, 161 ms execution time.

-- now to compare with logged table
create table 
If not exists
rental_log_logged (log_id bigserial primary key, info text not null);


insert into rental_log_logged (info)
select 
	a::text
from generate_series(1, 50000) as a;
-- Query returned successfully: 50000 rows affected, 301 ms execution time.

-- About double the speed for this stupid example...
Comments