SQL Commands

Administration

Command line

createdb cs01test8122
dropdb cs01test8122

SQL

CREATE DATABASE tractime WITH owner patrick;
DROP DATABASE tractime;

To create a new database user - run psql

CREATE USER patrick;

You can then log in as that user and manipulate the database - but it does not work - I get permission denied:

DROP USER patrick

The following creates a database user who is allowed to create databases..

CREATE USER patrick CREATEDB
CREATE USER patrick WITH PASSWORD 'password' CREATEDB;

The following creates a database superuser!!

CREATE USER patrick CREATEUSER

To give permission for a user to access a table:

GRANT ALL ON "table_name" TO "postgres";

To change a users password:

ALTER USER davide WITH PASSWORD 'hu8jmn3';

To add a foreign key constraint:

alter table task add constraint task_usercode foreign key(usercode) references developer(usercode);

To remove the constraint:

alter table task drop constraint task_usercode;

To find field information (equivalent to DDF):

select column_name from information_schema.columns where table_name ilike 'test_pat';

To find table information (equivalent to DDF):

select table_name from information_schema.tables where table_type = 'BASE TABLE' and table_schema = 'public'

or

select table_name from information_schema.tables where table_type = 'BASE TABLE' and table_schema = 'public' and table_name ilike 'VIEW%'

Can be found by using psql

SELECT version();

To do a case sensitive wildcard match on a table:

like 'VIEW%'

(This is not in the SQL standard but is a PostgreSQL extension):

ilike 'VIEW%'

cast('123' as text)
cast('1964-09-22' as date)
cast(40 as integer)
cast(1 as smallint)
cast('12:34:55' as time)

CASE WHEN 2 > 0 THEN True ELSE False END

cast(null as integer)
cast(null as date)

case when de.TimePeriod = 'Y'
then dc.timestart
else cast(null as time)
end

To select the current date and time:

current_date
current_time

To specify a date in a query:

select * from timebank where timedate >= '2005-4-01' and timedate <= '2005-4-30'

or:

select * from timebank tb where tb.timedate between nl.startdate and nl.enddate;

SELECT isbn FROM stock WHERE cost BETWEEN 10 AND 17;

You can achieve the same output using the less-than-or-equal-to operator (<=) in conjunction with the greater-than-or-equal-to (>=) operator:

SELECT isbn FROM stock WHERE cost >= 10 AND cost <= 17;

'a' || 'b'

This command gets the first 100 characters of the notes field:

Substring(notes from 0 for 100)