When http://localhost:8080 isn't working, you may want to try starting the tomcat server.
I'm going to assume that Tomcat was installed at /Library/Tomcat/apache-tomcat-6.0.18/
cd /Library/Tomcat/apache-tomcat-6.0.18/bin
./startup.sh
p.s. During setup, make sure CATALINA_HOME points to /Library/Tomcat/apache-tomcat-6.0.18. If it is not, edit your .profile file and redirect the CATALINA_HOME environment variable.
iPhone: Localization
Should have done my research before trying to do my own localization on the phone... the full article can be found here. Here are the basic steps to localizing your iphone application. Trust me, this is the way to do it...
Step 1: Create 2 directories called en.lproj (English) and it.lproj (Italian)
Step 2: Add new string file by going to File -> New File -> Other -> Strings File, name the file as "Localizable.strings" and place it in en.lproj.
Step 3: Repeat step 2, this time saving "Localizable.strings" inside it.lproj.
Step 4: Add the following lines to the strings files...
Step 5: To grab WelcomeKey you just do: NSLocalizedString(@"WelcomeKey", @"") like so:
That's all there is to it... Here are some of Apple's conventional language codes:
English: en
Japanese: ja
French: fr
Deutsch: de
Netherlands: nl
Italian: it
Spanish: es
Portugues: pt
Korean: ko
Simplified Chinese: zh-Hans
Traditional Chinese: zh-Hant
The full list can be accessed through the following call:
Step 1: Create 2 directories called en.lproj (English) and it.lproj (Italian)
Step 2: Add new string file by going to File -> New File -> Other -> Strings File, name the file as "Localizable.strings" and place it in en.lproj.
Step 3: Repeat step 2, this time saving "Localizable.strings" inside it.lproj.
Step 4: Add the following lines to the strings files...
"WelcomeKey" = "Welcome!!!"; // put into english version
"WelcomeKey" = "Benvenuto!!!"; // put into italian versionStep 5: To grab WelcomeKey you just do: NSLocalizedString(@"WelcomeKey", @"") like so:
NSLog(@"Welcome Text: %@", NSLocalizedString(@"WelcomeKey", @""));
That's all there is to it... Here are some of Apple's conventional language codes:
English: en
Japanese: ja
French: fr
Deutsch: de
Netherlands: nl
Italian: it
Spanish: es
Portugues: pt
Korean: ko
Simplified Chinese: zh-Hans
Traditional Chinese: zh-Hant
The full list can be accessed through the following call:
NSArray *languages = [defaults objectForKey:@"AppleLanguages"];
// print each NSString in languages
PostgreSQL: Advanced PostgreSQL
Transactions:
begin work;
// do query
commit work;
begin work;
// do query
rollback work; // undo
begin work;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; // serialize, data doesn't change
// do query
commit work;
begin work;
// do query. End with FOR UPDATE; this serializes & locks this transaction until I update or transaction ends
// do update
commit work;
INDEX: Speeds up searches
create index [indexname] on [tbl] ( [col1], [col2] );
drop index [indexname];
create unique index [indexname] on [tbl] ( [col1], [col2] );
CLUSTER: Speeds up searches by clustering large search results
VACUUM: Speeds up searches by cleaning up expired rows
vacuum [tbl];
vacuum analyze [tbl];
EXPLAIN: Shows speed & statistic on queries
explain select * from [tbl];
LIMIT:
select * from [tbl] limit #;
select * from [tbl] limit # offset #;
CURSOR: must take place inside a transaction
begin work;
declare [cursor name] cursor for select [col] from [tbl];
fetch # from [cursor name];
move #from [cursor name];
close [cursor name];
commit work;
Temporary tables
select * into temporary [tbl] from [tbl2] where [condition];
Alter table content:
alter table [tbl] rename to [tbl'];
alter table [tbl] rename column [col] to [col'];
alter table [tbl] add column [col] [type];
alter table [tbl] alter column [col] [type];
alter table [tbl] alter column [col] set default [value];
alter table [tbl] alter column [col] drop default;
Grant permission:
grant select on [tbl] to [user];
grant all on [tbl] to public;
Inheritance:
create table [tbl] ( ... );
create table [tb2] ( ... ) inherits ( [tbl1] );
select * from [tbl1]*; // access shared columns in tbl1 & tbl2
Views: Views cannot be directly modified, just queried
create view [viewname] as [select ...];
drop view [viewname];
Rules:
create rule [rulename] as on select to [tbl/view] do instead nothing;
create rule [rulename] as on insert to [tbl/view] do instead [what to do];
create rule [rulename] as on update to [tbl/view] do instead [what to do];
create rule [rulename] as on deleete to [tbl/view] do instead [what to do];
Constraints: NOT NULL, UNIQUE, PRIMARY KEY
create table [tbl] ( col1 INTEGER NOT NULL DEFAULT [value] );
create table [tbl] ( col1 INTEGER UNIQUE );
create table [tbl] ( col1 INTEGER PRIMARY KEY ); // UNIQUE+NOT NULL
create table [tbl] (
col1 INTEGER,
col2 INTEGER,
UNIQUE(col1, col2) // must be on its own line
);
create table [tbl] (
col1 INTEGER,
col2 INTEGER,
PRIMARY KEY(col1, col2) // must be on its own line
);
Foreign Key:
create table [tbl1] ( col INTEGER PRIMARY KEY );
create table [tbl2] ( col INTEGER REFERENCES [tbl1] );
Foreign Key Options:
create table [tbl2] ( col INTEGER REFERENCES [tbl1] on update CASCADE on delete SET NULL );
where:
NO ACTION = UPDATEs and DELETEs to the primary key are prohibited if referenced by a foreign key row. (Default)
CASCADE = UPDATEs to the primary key update all foreign key columns that reference it. DELETEs on the primary key cause the deletion of all foreign key rows that reference it.
SET NULL = UPDATEs and DELETEs to the primary key row cause the foreign key to be set to NULL.
CHECK: validity checking during insertion
create table [tbl] ( age INTEGER CHECK (age > 18) );
create table [tbl] ( gender CHAR(1) CHECK (gender IN ('M', 'F') );
create table [tbl] ( age INTEGER, gender CHAR(1), CHECK ( age > 18 and gender in ('M', 'F') ) );
begin work;
// do query
commit work;
begin work;
// do query
rollback work; // undo
begin work;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; // serialize, data doesn't change
// do query
commit work;
begin work;
// do query. End with FOR UPDATE; this serializes & locks this transaction until I update or transaction ends
// do update
commit work;
INDEX: Speeds up searches
create index [indexname] on [tbl] ( [col1], [col2] );
drop index [indexname];
create unique index [indexname] on [tbl] ( [col1], [col2] );
CLUSTER: Speeds up searches by clustering large search results
VACUUM: Speeds up searches by cleaning up expired rows
vacuum [tbl];
vacuum analyze [tbl];
EXPLAIN: Shows speed & statistic on queries
explain select * from [tbl];
LIMIT:
select * from [tbl] limit #;
select * from [tbl] limit # offset #;
CURSOR: must take place inside a transaction
begin work;
declare [cursor name] cursor for select [col] from [tbl];
fetch # from [cursor name];
move #from [cursor name];
close [cursor name];
commit work;
Temporary tables
select * into temporary [tbl] from [tbl2] where [condition];
Alter table content:
alter table [tbl] rename to [tbl'];
alter table [tbl] rename column [col] to [col'];
alter table [tbl] add column [col] [type];
alter table [tbl] alter column [col] [type];
alter table [tbl] alter column [col] set default [value];
alter table [tbl] alter column [col] drop default;
Grant permission:
grant select on [tbl] to [user];
grant all on [tbl] to public;
Inheritance:
create table [tbl] ( ... );
create table [tb2] ( ... ) inherits ( [tbl1] );
select * from [tbl1]*; // access shared columns in tbl1 & tbl2
Views: Views cannot be directly modified, just queried
create view [viewname] as [select ...];
drop view [viewname];
Rules:
create rule [rulename] as on select to [tbl/view] do instead nothing;
create rule [rulename] as on insert to [tbl/view] do instead [what to do];
create rule [rulename] as on update to [tbl/view] do instead [what to do];
create rule [rulename] as on deleete to [tbl/view] do instead [what to do];
Constraints: NOT NULL, UNIQUE, PRIMARY KEY
create table [tbl] ( col1 INTEGER NOT NULL DEFAULT [value] );
create table [tbl] ( col1 INTEGER UNIQUE );
create table [tbl] ( col1 INTEGER PRIMARY KEY ); // UNIQUE+NOT NULL
create table [tbl] (
col1 INTEGER,
col2 INTEGER,
UNIQUE(col1, col2) // must be on its own line
);
create table [tbl] (
col1 INTEGER,
col2 INTEGER,
PRIMARY KEY(col1, col2) // must be on its own line
);
Foreign Key:
create table [tbl1] ( col INTEGER PRIMARY KEY );
create table [tbl2] ( col INTEGER REFERENCES [tbl1] );
Foreign Key Options:
create table [tbl2] ( col INTEGER REFERENCES [tbl1] on update CASCADE on delete SET NULL );
where:
NO ACTION = UPDATEs and DELETEs to the primary key are prohibited if referenced by a foreign key row. (Default)
CASCADE = UPDATEs to the primary key update all foreign key columns that reference it. DELETEs on the primary key cause the deletion of all foreign key rows that reference it.
SET NULL = UPDATEs and DELETEs to the primary key row cause the foreign key to be set to NULL.
CHECK: validity checking during insertion
create table [tbl] ( age INTEGER CHECK (age > 18) );
create table [tbl] ( gender CHAR(1) CHECK (gender IN ('M', 'F') );
create table [tbl] ( age INTEGER, gender CHAR(1), CHECK ( age > 18 and gender in ('M', 'F') ) );
PostgreSQL: Data Types, Functions
display all data types: \dT
Data Types: (Page 108)
TEXT, VARCHAR(length), CHAR(length)
INTEGER, INT2, INT8, OID, NUMERIC(precision, decimal), FLOAT, FLOAT4
DATE, TIME, TIMESTAMP, INTERVAL
BOOLEAN
POINT, LSEG, PATH, BOX, CIRCLE, POLYGON
INET, CIDR, MACADDR
Array Example:
create table [tbl] ( col INTEGER[5] );
insert into [tbl] values ( '{1, 2, 3, 4, 5}' );
Functions: (Page 113)
length(), character_length(), trim(), upper(), lower(), substr(), to_number()
round(), trunc(), abs(), factorial(), sqrt(), cbrt(), exp(), ln(), log(), to_char()
date_part(), now(), timeofday()
Variables:
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER
Casts:
CAST('5/8/1982' AS DATE)
CAST('5/8/1982' AS TIMESTAMP)
Data Types: (Page 108)
TEXT, VARCHAR(length), CHAR(length)
INTEGER, INT2, INT8, OID, NUMERIC(precision, decimal), FLOAT, FLOAT4
DATE, TIME, TIMESTAMP, INTERVAL
BOOLEAN
POINT, LSEG, PATH, BOX, CIRCLE, POLYGON
INET, CIDR, MACADDR
Array Example:
create table [tbl] ( col INTEGER[5] );
insert into [tbl] values ( '{1, 2, 3, 4, 5}' );
Functions: (Page 113)
length(), character_length(), trim(), upper(), lower(), substr(), to_number()
round(), trunc(), abs(), factorial(), sqrt(), cbrt(), exp(), ln(), log(), to_char()
date_part(), now(), timeofday()
Variables:
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER
Casts:
CAST('5/8/1982' AS DATE)
CAST('5/8/1982' AS TIMESTAMP)
SVN: Troubleshooting
Rule: DO NOT EVER EDIT SVN DB FILES DIRECTLY!
The problem: Wanted to fix the log message for a specific revision.
My (bad) solution: Edit db/revprops/### directly.
This causes the following error:
svn: General svn error from server
To fix this, copy another ### file and overwrite the bad one you altered. After that you should be back in business, just with a bad log.
Now to change the log the PROPER way, go on the server computer and type:
echo "my message goes here" >> msg
svnadmin setlog /Path/to/repo -r #REV# msg
The problem: Wanted to fix the log message for a specific revision.
My (bad) solution: Edit db/revprops/### directly.
This causes the following error:
svn: General svn error from server
To fix this, copy another ### file and overwrite the bad one you altered. After that you should be back in business, just with a bad log.
Now to change the log the PROPER way, go on the server computer and type:
echo "my message goes here" >> msg
svnadmin setlog /Path/to/repo -r #REV# msg
PostgreSQL: Intermediate PostgreSQL
Aggregates:
Return # of rows: COUNT(*)
Return count of NON-NULL values in a column: COUNT(colname)
SUM(colname)
MAX(colname)
MIN(colname)
AVG(colname)
Grouping aggregates: GROUP BY
select [col], aggregate#1, aggregate#2 from [tbl] group by [col], [aggregate#1];
Aggregate conditionals: HAVING
select [col], aggregate#1 from [tbl] group by [col] having [aggregate condition];
Alias:
select [col] from [tbl] [alias] where [alias].[col?] = [condition];
select [col] from [tbl] [alias1], [tbl] [alias2] where [condition];
Joining:
select [tbl].[col] from [tbl1], [tbl2] where [tbl1].[col] = [tb2].[col];
Distinct: Return distinct col1+col2 pairs only
select distinct [col1], [col2], count(*) from [tbl] where [condition];
Unjoined table: Cartesian product
select * from [tbl1], [tb2];
Nonequijoin: (Could be any of <, <>, !=, >)
select [col] from [tbl] [alias1], [tbl] [alias2] where [alias1].[col1] <> [alias2].[col2];
OIDs: Automatically incremented & always distinct
select oid, [col] from [tbl];
create table [tbl] ( customer_oid OID );
AUTO-Numbering / Sequences:
create sequence [sequence name];
create table [tbl] ( customer_id INTEGER DEFAULT nextval('[sequence name]') );
insert into [tbl] values ( nextval('[sequence name]') );
currval('[sequence name]');
setval('[sequence name]', #);
create table [tbl] ( customer_id SERIAL ); // alternative
UNION: Combined results, A and B
select [col1] from [tbl1] union select [col2] from [tbl2];
select [col1] from [tbl1] union all select [col2] from [tbl2]; // with duplicates
EXCEPT: A but not in B
select [col1] from [tbl1] except select [col2] from [tbl2];
INTERSECT: A and in B
select [col1] from [tbl1] intersect select [col2] from [tbl2];
SUBQUERIES: Select within a select, bypass joins
select [col2] from [tbl2] where [col2] <> ( select [col1] from [tbl1] where [condition] );
SUBQUERIES W/ LISTS: Using IN/NOT IN/ANY/ALL/EXISTS/NOT EXISTS
select [col2] from [tbl2] where [col2] in ( select [col1] from [tbl1] where [condition] );
select [col2] from [tbl2] where [col2] not in ( select [col1] from [tbl1] where [condition] );
select [col2] from [tbl2] where [col2] any ( select [col1] from [tbl1] where [condition] );
select [col2] from [tbl2] where [col2] all ( select [col1] from [tbl1] where [condition] );
select [col2] from [tbl2] where [col2] exists ( select [col1] from [tbl1] where [condition] );
select [col2] from [tbl2] where [col2] not exists ( select [col1] from [tbl1] where [condition] );
UPDATE + FROM:
update [tbl1] set [col]=[val] from [tbl2] where [condition w/ tbl1 + tbl2];
INSERT USING SELECTS: transfer data from table 1 to 2
insert into [tbl1] (name) select 'danny' from [tbl2];
SELECT INTO: copy table 1 to 2
select [col1], [col2] into [tbl2] from [tbl1];
Text concatenation: ||
'Danny' || 'Cool' = 'DannyCool'
Return # of rows: COUNT(*)
Return count of NON-NULL values in a column: COUNT(colname)
SUM(colname)
MAX(colname)
MIN(colname)
AVG(colname)
Grouping aggregates: GROUP BY
select [col], aggregate#1, aggregate#2 from [tbl] group by [col], [aggregate#1];
Aggregate conditionals: HAVING
select [col], aggregate#1 from [tbl] group by [col] having [aggregate condition];
Alias:
select [col] from [tbl] [alias] where [alias].[col?] = [condition];
select [col] from [tbl] [alias1], [tbl] [alias2] where [condition];
Joining:
select [tbl].[col] from [tbl1], [tbl2] where [tbl1].[col] = [tb2].[col];
Distinct: Return distinct col1+col2 pairs only
select distinct [col1], [col2], count(*) from [tbl] where [condition];
Unjoined table: Cartesian product
select * from [tbl1], [tb2];
Nonequijoin: (Could be any of <, <>, !=, >)
select [col] from [tbl] [alias1], [tbl] [alias2] where [alias1].[col1] <> [alias2].[col2];
OIDs: Automatically incremented & always distinct
select oid, [col] from [tbl];
create table [tbl] ( customer_oid OID );
AUTO-Numbering / Sequences:
create sequence [sequence name];
create table [tbl] ( customer_id INTEGER DEFAULT nextval('[sequence name]') );
insert into [tbl] values ( nextval('[sequence name]') );
currval('[sequence name]');
setval('[sequence name]', #);
create table [tbl] ( customer_id SERIAL ); // alternative
UNION: Combined results, A and B
select [col1] from [tbl1] union select [col2] from [tbl2];
select [col1] from [tbl1] union all select [col2] from [tbl2]; // with duplicates
EXCEPT: A but not in B
select [col1] from [tbl1] except select [col2] from [tbl2];
INTERSECT: A and in B
select [col1] from [tbl1] intersect select [col2] from [tbl2];
SUBQUERIES: Select within a select, bypass joins
select [col2] from [tbl2] where [col2] <> ( select [col1] from [tbl1] where [condition] );
SUBQUERIES W/ LISTS: Using IN/NOT IN/ANY/ALL/EXISTS/NOT EXISTS
select [col2] from [tbl2] where [col2] in ( select [col1] from [tbl1] where [condition] );
select [col2] from [tbl2] where [col2] not in ( select [col1] from [tbl1] where [condition] );
select [col2] from [tbl2] where [col2] any ( select [col1] from [tbl1] where [condition] );
select [col2] from [tbl2] where [col2] all ( select [col1] from [tbl1] where [condition] );
select [col2] from [tbl2] where [col2] exists ( select [col1] from [tbl1] where [condition] );
select [col2] from [tbl2] where [col2] not exists ( select [col1] from [tbl1] where [condition] );
UPDATE + FROM:
update [tbl1] set [col]=[val] from [tbl2] where [condition w/ tbl1 + tbl2];
INSERT USING SELECTS: transfer data from table 1 to 2
insert into [tbl1] (name) select 'danny' from [tbl2];
SELECT INTO: copy table 1 to 2
select [col1], [col2] into [tbl2] from [tbl1];
Text concatenation: ||
'Danny' || 'Cool' = 'DannyCool'
PostgreSQL: Regular Expression Query
Taken from PostgreSQL Introduction & Concepts
regular expression: ~
regular expression (case-insensitive): ~*
not equal to regular expression: !~
not equal to regular expression (case-insensitive): !~*
Basic Rules:
start: ^
end: $
any single character: .
set of characters: [abc]
set of characters not equal: [^abc]
range of characters: [a-z]
range of characters not equal: [^a-z]
zero or 1: ?
zero or more: *
one or more: +
OR: |
Examples:
begins with D: ~ '^D'
contains D: ~ 'D'
D in second position: ~ '^.D'
begins with D and contains e: ~ 'D.*e'
begins with D, contains e, then f: ~ '^D.*e.*f'
contains A, B, C, or D: ~ '[A-D]' or ~ '[ABCD]'
contains A or a: ~ * 'a' or ~ '[Aa]'
does not contain D: !~ 'D'
does not begin with D: !~ '^D' or ~ '^[^D]'
begin with D, one optional space in front: ~ '^ ?D'
begin with D, with optional leading spaces: ~ '^ *D'
begin with D, with at least 1 space: ~ '^ +D'
end with G, with optional trailing spaces: ~ 'G *$'
regular expression: ~
regular expression (case-insensitive): ~*
not equal to regular expression: !~
not equal to regular expression (case-insensitive): !~*
Basic Rules:
start: ^
end: $
any single character: .
set of characters: [abc]
set of characters not equal: [^abc]
range of characters: [a-z]
range of characters not equal: [^a-z]
zero or 1: ?
zero or more: *
one or more: +
OR: |
Examples:
begins with D: ~ '^D'
contains D: ~ 'D'
D in second position: ~ '^.D'
begins with D and contains e: ~ 'D.*e'
begins with D, contains e, then f: ~ '^D.*e.*f'
contains A, B, C, or D: ~ '[A-D]' or ~ '[ABCD]'
contains A or a: ~ * 'a' or ~ '[Aa]'
does not contain D: !~ 'D'
does not begin with D: !~ '^D' or ~ '^[^D]'
begin with D, one optional space in front: ~ '^ ?D'
begin with D, with optional leading spaces: ~ '^ *D'
begin with D, with at least 1 space: ~ '^ +D'
end with G, with optional trailing spaces: ~ 'G *$'
PostgreSQL: Basic PostgreSQL & SQL
After years of messing with MySQL, I am finally getting into PostgreSQL. The transition was not as easy as I had hoped.
To enter a database:
psql [dbname] -U [username]
Get list of SQL commands: \h
Get help: \?
To exit a database: \q
To clear the buffer: \r
To print the current query: \p
List all tables: \d
Expanded display (instead of columns): \x
List all functions: \df
List all functions named X: \df [function name]
Describe a function: \dd [function name]
Describe an operator: \do [operator ie. ! ^ * + -]
Commentting:
-- a single line comment
/* */ multi-line comment
Create new user:
create user [username] with password '[password]' [createdb/nocreatedb] in group [groupname] valid until 'Jan 1 2005';
Create database:
create database [dbname] with owner [1], [2] encoding = 'UTF8';
Common data types:
CHAR(length), VARCHAR(length)
INTEGER, FLOAT, NUMERIC(precision/decimal)
DATE, TIME, TIMESTAMP
Create table:
create table [tblname] (firstname CHAR(15), lastname CHAR(20), age INTEGER);
create table [tblname] (firstname CHAR(15) default 'no name');
Delete table:
drop table [tblname];
Describe specific table:
\d [tblname];
Insert table content:
insert into [tblname] values ('firstname', 'lastname', 20);
Delete table content:
delete from [tblname]; (deletes all)
delete from [tblname] where [condition];
Update table content:
update [tblname] set [colname]=[new value] where [condition];
Display table content:
select * from [tblname] where age<=20; select * from [tblname] where age>=18 and firstname='danny';
Display table content w/ specified column name:
select [colname] as [tmp-alt colname] from [tblname] order by [tmp-alt colname];
select 1+3 as total;
Display table content w/ specific ordering:
select * from [tblname] where [condition] order by [col1] DESC, [col2], [col3] DESC;
Conditional query:
select [colname] case when [condition w/ colname] then [conditional string value for yes] else [conditional string value for no] end as [casename] from [tblname];
select [colname] case when [condition 1 w/ colname] then [case string value 1 for yes] when [condition 2 w/ colname] then [case string value 2 for yes] else [string value for no] end as [casename] from [tblname];
Grabbing distinct column values from table:
select distinct [colname] from [tblname];
select distinct [col1], [col2] from [tblname];
Example Conditions:
age <> 99
age IS NULL
age != 22 and (firstname='jack' or firstname='bob')
age between 33 and 35
Like Conditions:
like 'D%' (begins with D)
like '%D%' (contains a D)
like '_D%' (has D in second position)
like 'D_nny' (Donny, Danny, Denny)
like 'D%e%' (begins with D contains e)
like 'D%e%f%' (begins with D contains e, then f)
not like 'D%' (begins with non-D)
Common vars:
select current_user;
select current_timestamp;
To enter a database:
psql [dbname] -U [username]
Get list of SQL commands: \h
Get help: \?
To exit a database: \q
To clear the buffer: \r
To print the current query: \p
List all tables: \d
Expanded display (instead of columns): \x
List all functions: \df
List all functions named X: \df [function name]
Describe a function: \dd [function name]
Describe an operator: \do [operator ie. ! ^ * + -]
Commentting:
-- a single line comment
/* */ multi-line comment
Create new user:
create user [username] with password '[password]' [createdb/nocreatedb] in group [groupname] valid until 'Jan 1 2005';
Create database:
create database [dbname] with owner [1], [2] encoding = 'UTF8';
Common data types:
CHAR(length), VARCHAR(length)
INTEGER, FLOAT, NUMERIC(precision/decimal)
DATE, TIME, TIMESTAMP
Create table:
create table [tblname] (firstname CHAR(15), lastname CHAR(20), age INTEGER);
create table [tblname] (firstname CHAR(15) default 'no name');
Delete table:
drop table [tblname];
Describe specific table:
\d [tblname];
Insert table content:
insert into [tblname] values ('firstname', 'lastname', 20);
Delete table content:
delete from [tblname]; (deletes all)
delete from [tblname] where [condition];
Update table content:
update [tblname] set [colname]=[new value] where [condition];
Display table content:
select * from [tblname] where age<=20; select * from [tblname] where age>=18 and firstname='danny';
Display table content w/ specified column name:
select [colname] as [tmp-alt colname] from [tblname] order by [tmp-alt colname];
select 1+3 as total;
Display table content w/ specific ordering:
select * from [tblname] where [condition] order by [col1] DESC, [col2], [col3] DESC;
Conditional query:
select [colname] case when [condition w/ colname] then [conditional string value for yes] else [conditional string value for no] end as [casename] from [tblname];
select [colname] case when [condition 1 w/ colname] then [case string value 1 for yes] when [condition 2 w/ colname] then [case string value 2 for yes] else [string value for no] end as [casename] from [tblname];
Grabbing distinct column values from table:
select distinct [colname] from [tblname];
select distinct [col1], [col2] from [tblname];
Example Conditions:
age <> 99
age IS NULL
age != 22 and (firstname='jack' or firstname='bob')
age between 33 and 35
Like Conditions:
like 'D%' (begins with D)
like '%D%' (contains a D)
like '_D%' (has D in second position)
like 'D_nny' (Donny, Danny, Denny)
like 'D%e%' (begins with D contains e)
like 'D%e%f%' (begins with D contains e, then f)
not like 'D%' (begins with non-D)
Common vars:
select current_user;
select current_timestamp;
PostgreSQL: Setting up PostgreSQL in Mac OSX
NOTE: Do NOT mix the 2 methods. Once you install using the dmg, you will lose access to the server data. However you will gain the use of pgAdmin, which is very similar to SQLServer (from what I remember anyway...).
One method is to download the .dmg from their website, which automatically sets up everything for you, including database URL, port, username & password.
The other method is to do this yourself via command-line:
1. mkdir /DB/data
2. chmod 0700 /DB/data
3. initdb -D /DB/data
4. pg_ctl start -D /DB/data
5. pg_ctl stop -D /DB/data
You can use a simple shell script to start/stop/restart the server for you (taken from Will Green)
#!/bin/bash
export PGDATA=/DB/data
start() {
echo -n "Starting Postgres: "
pg_ctl start -l $PGDATA/postgres.log
return
}
stop() {
echo -n "Shutting down Postgres: "
pg_ctl stop -l $PGDATA/postgres.log
return
}
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
*)
echo "Usage: {start|stop|restart}"
exit 1
;;
esac
exit $?
One method is to download the .dmg from their website, which automatically sets up everything for you, including database URL, port, username & password.
The other method is to do this yourself via command-line:
1. mkdir /DB/data
2. chmod 0700 /DB/data
3. initdb -D /DB/data
4. pg_ctl start -D /DB/data
5. pg_ctl stop -D /DB/data
You can use a simple shell script to start/stop/restart the server for you (taken from Will Green)
#!/bin/bash
export PGDATA=/DB/data
start() {
echo -n "Starting Postgres: "
pg_ctl start -l $PGDATA/postgres.log
return
}
stop() {
echo -n "Shutting down Postgres: "
pg_ctl stop -l $PGDATA/postgres.log
return
}
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
*)
echo "Usage: {start|stop|restart}"
exit 1
;;
esac
exit $?
Subscribe to:
Posts (Atom)