Home   Notes   Contact Me

SQL Notes

Note: Database specific commands on this page are typically postgres specific

Disambigation

Local

External

INSERT

INSERT INTO {table_name} ( {column_name1}, {column_name2} ) VALUES ( {value for column1}, {value for column2} ); INSERT INTO PeopleTable ( firstName, lastName ) VALUES ( 'John', 'Doe' );

Coalesce

Returns the first value that is not NULL.
So in the following if (SELECT true FROM ... WHERE boolcol LIMIT 1) is not null, its value is returned, otherwise FALSE is returned. 
SELECT COALESCE( (SELECT true FROM ... WHERE boolcol LIMIT 1), FALSE);

Boolean, Max

 Odd question:  I have a query that returns a set of none to several rows. One
column in these rows is BOOLEAN.   I want the query to return:
1) TRUE if *any* of the BOOLEAN values is TRUE;
2) FALSE if *all* of the BOOLEAN values are FALSE;
3) FALSE or NULL if no rows are returned.

I thought that I could do this through a MAX(boolean), but as it turns out,
MAX(boolean) has not been defined in the standard 7.2.3. distro.  I could
define MAX(boolean), but I'm wondering if I'm missing something obvious.

Well, you might be better off making a max(bool), but a not
super-efficient version might be:
 max(case when col then 1 else 0 end)=1

Random Row, Selecting

To SELECT a random row, use: (at least with PostGres) 
    SELECT col
    FROM tab
    ORDER BY random()
    LIMIT 1;

Quirks

QuirkDesc
Quotes, SingleIf you insert like this 'John''s car' java cannot understand when you select it out. Instead insert like this 'John\'s car'

Date Manipulation

Postgres time manip page
WhatHowOutput
epoch to date SELECT timestamptz(1047574080) 
      timestamptz
------------------------
 2003-03-13 11:48:00-05
(1 row)
current time as a float SELECT date_part( 'epoch', now()); 
     date_part
------------------
 1048540609.94646
(1 row)
current time as an int SELECT int4(date_part( 'epoch', now())); 
    int4
------------
 1048540750
(1 row)
A Date as an int SELECT int4(date_part( 'epoch', timestamptz'2003-03-19 18:25:00-05')); 
    int4
------------
 1048116300
(1 row)

Null

Testing to see if a value is NULL: 

{colname} IS NULL

Explain how much computing a query will take

Explain {Query}

i.e:

EXPLAIN SELECT * FROM table_1 ;

Left Outer Join

table_A

nameid
alpha{null}
beta1
gamma2
delta3

table_B

idattrib
1red
2green

desired output

nameattrib
alpha{null}
betared
gammagreen
delta{null}
SELECT S1.name, S2.attrib 
FROM 
  (SELECT name, id FROM table_A) AS S1
LEFT OUTER JOIN
  (SELECT id, attrib FROM table_B) AS S2
ON ( S1.id = S2.id ) ;

Comments

Line must start with '--'

Drop Function

When dropping a function you must use its name and the types of its parameters.

Emptying a Table of all Data

DELETE FROM table_name WHERE 0 = 0 ;

Example:

    DELETE FROM alarms WHERE 0 = 0 ;

Adding Columns to an existing Table

ALTER TABLE tablename ADD COLUMN new_column_name data_type ;

Example:
 
    ALTER TABLE addresses ADD COLUMN phoneNumber text ;

DELETE

DELETE FROM [Table]
{WHERE [Column]=[Value to Identify Row to Update]}

SELECT (get)

SELECT [Column to Get]{,[Another Column]}
FROM [Table]
{WHERE [Column]="[Value]"}
{WHERE [Column] LIKE [{%}string{%}]}

SELECT [ ALL [ DISTINCT [ ON (expression [,...]) ] expression
    [ AS name] [,...]
    [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
    [ FROM [ ONLY ] fromitem [ alias ] [,...] ]
        [ ON JOIN joincondition | USING (joinlist) ] [,...] ]
    [ WHERE wherecondition ]
    [ GROUP BY column [,...] ]
    [ HAVING wherecondition [,...] ]
    [ {UNION [ALL] | INTERSECT | EXCEPT} secselect ]
    [ ORDER BY column [ ASC | DESC | USING operator ] [,...]
    [ FOR UPDATE [ OF tablename [,...] ] ]
    [ LIMIT {count | ALL} [ {OFFSET | , } start ] ]
Notes:

UPDATE (put)

UPDATE [Table]
SET
{[Column]='[StringVal]'{,}}
{[Column]=NumVal}
{WHERE [Column]=[Value to Identify Row to Update]}

GRANT

GRANT privilege [,...] ON object TO { PUBLIC | GROUP groupname | username }

where privilege is one of:
SELECT INSERT UPDATE DELETE RULE or ALL

Create Table

CREATE TABLE employee (Name char(20),Dept char(20),jobTitle char(20)) ;

Delete Table

DROP TABLE tablename

Insert

INSERT INTO table VALUES ( 'Fred Flinstone','Quarry Worker','Rock Digger' ) ;

Time Intervals

SELECT now() ;
SELECT now() - INTERVAL '1 day' ;
SELECT now() - INTERVAL '5 secs' ;