Learn postgres

[minaorangina]

From @minaorangina on July 5, 2016 11:23

Resources

• pgexercises
• CS50 explanation
• Udacity Intro to Relational Databases
• Codecademy Learn SQL
  • List of SQL commands (codecademy)
• pgadmin
• Tutorials Point
• Blog: realtime web apps w/ postgres and node
• sqlzoo -http://becausejavascript.com/generating-json-in-postgresql/

  33

[minaorangina]

From @sohilpandya on July 6, 2016 15:8

IN

SQL IN command returns items in table that match any of the arguments.

SELECT * FROM cd.facilities WHERE facid IN (1,5)

[minaorangina]

Joins

Use joins to get data from multiple tables.

Inner join

Combines rows from different tables if the join condition is true.

Example

This returns all columns from all tables, including duplicates of module_id

SELECT * FROM modules 
JOIN medals 
ON modules.module_id = medals.module_id

[minaorangina]

From @sohilpandya on August 16, 2016 12:33

Case

Case is a conditional (i.e. an if statement).

Example

Simplest example to use with our database is to list number of responses for a given question.

select 
count(case when response = 'A' then 1 end) as a_response, 
count(case when response = 'B' then 1 end) as b_response, 
count(case when response = 'C' then 1 end) as c_response, 
count(case when response = 'D' then 1 end) as d_response  
from responses 
where quiz_id=1 and question_id=1

This returns a new table with 4 columns and 1 row containing the accumulation of responses.

Complex Example.

We can also use multiple When # Then #. view #85 for the query we've actually used.

[minaorangina]

"Upserting"

This references when you want to either insert a value or, if it exists already, update it.

Postgres 9.5 makes this possible with the ON CONFLICT DO UPDATE clause. Used this to solve issue #76, specific implementation may be found here

Useful links

• Postgres wiki entry on upserting
• Postgres docs for ON CONFLICT clause
• Really great blog post example on upserting in Postgres

[minaorangina]

Composite primary key

Usually you'll uniquely identify a row by a primary key - perhaps a self-incrementing id. But you might have a table where there's no column with completely unique values. Instead, a particular combination of values are what make a row unique.

You can actually combine multiple keys into one primary key.

CREATE TABLE IF NOT EXISTS module_members (
    module_id CHAR(4) NOT NULL,
    user_id INTEGER NOT NULL,
    PRIMARY KEY (module_id, user_id)
);

Alternative could be:

CREATE TABLE IF NOT EXISTS module_members (
    module_id CHAR(4) NOT NULL,
    user_id INTEGER NOT NULL,
    UNIQUE (module_id, user_id)
);

Used this to solve issue #76, specific implementation may be found here

Useful links

• A stack overflow question on handling a unique combo of keys
• Another stack overflow question on doing composite keys
• Tutorials Point article on constraints in general

[minaorangina]

Lifted from #47, here

Distinct

Let's say we want a list of students who have taken part in a quiz

SELECT DISTINCT(user_id) FROM responses WHERE quiz_id = $1;

[minaorangina]

Lifted from #47, here

Count

If you need to count rows, you can do it like this:

SELECT COUNT(user_id) FROM module_members WHERE module_id = $1;

[minaorangina]

Lifted from #47, here

For each of this category, do some maths

SELECT arbitrary_category, SUM(my_column) FROM table GROUP BY arbitrary_category

SELECT arbitrary_category, COUNT(my_column) FROM table GROUP BY arbitrary_category

COUNT/SUM ... ... GROUP BY This says: group things up in this arbitrary category, then reduce the members of that category down to one value

For each of this category, do some maths, then do some more maths

SELECT arbitrary_category, COUNT(my_column) FROM table GROUP BY arbitrary_category

[minaorangina]

Comparison operators

Not equal to

<> or <!=>

SELECT medal_name, condition FROM medals WHERE module_id = 'TEST' AND medal_name <> 'gold';

[minaorangina]

From @sohilpandya on August 22, 2016 17:30

Delete Multiple rows from a table

 DELETE FROM table WHERE id IN (?,?,?,?,?,?,?,?)

[minaorangina]

From @sohilpandya on August 26, 2016 10:4

Converting integers to floats.

when you calculate 3/4 it should return 0.75, but because sql is strongly typed, it needs to know that you are after floats.

Hence use:

CAST (3 AS FLOAT) / CAST (4 AS FLOAT)

shorthand version of CAST

3::float / 4::float

to get the return value of 0.75

[heron2014]

Convert a null value to other value in the following example is 0:

COALESCE(field, 0)