How to create a table in PostgreSQL

CREATE TABLE Syntax:

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)

Data Types:

Text types:

Data type	Description
CHAR(size)	Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size)	Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
TINYTEXT	Holds a string with a maximum length of 255 characters
TEXT	Holds a string with a maximum length of 65,535 characters
BLOB	For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT	Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB	For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT	Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB	For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
ENUM(x,y,z,etc.)	Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. Note: The values are sorted in the order you enter them. You enter the possible values in this format: ENUM('X','Y','Z')
SET	Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice

Number types:

Data type	Description
TINYINT(size)	-128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis
SMALLINT(size)	-32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis
MEDIUMINT(size)	-8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis
INT(size)	-2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
BIGINT(size)	-9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis
FLOAT(size,d)	A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DOUBLE(size,d)	A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DECIMAL(size,d)	A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

Constraints：

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

NOT NULL

CREATE TABLE table_name
(
column_name1 data_type NOT NULL,
column_name2 data_type NULL,
column_name3 data_type,
....
)

UNIQUE

CREATE TABLE table_name
(Method 1: 
column_name1 data_type UNIQUE,
Method 2: 
column_name2 data_type CONSTRAINT unique_constraint_name UNIQUE,
column_name3 data_type,
....
Method 3:
UNIQUE ([column_name]s...)
 Method 4:
CONSTRAINT [unique_constraint_name] UNIQUE ([column_name]s...)
)

Add a UNIQUE constraint to a PostgreSQL table.

Method 1:

>> CREATE UNIQUE INDEX [constraint_name] 

      ON [table_name] ([column_name1], [column_name2], ..., [column_nameN]);

Method 2:

>> ALTER TABLE [table_name] 

      AND CONSTRAINT [constraint_name]

      UNIQUE ([column_name1], [column_name2], ..., [column_nameN]);

To drop a UNIQUE constraint.

>> ALTER TABLE [table_name]

      DROP CONSTRAINT [constraint_name];

PRIMARY KEY

To create a PRIMARY KEY on the "column_name1".

 
CREATE TABLE table_name
(
column_name1 data_type PRIMARY KEY,
column_name2 data_type,
column_name3 data_type,
....
)

or, 
 
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
PRIMARY KEY(column_name1)
)

To create a PRIMARY KEY using the "column_name1" and "column_name2" .

 
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
PRIMARY KEY(column_name1, column_name2)
)

To allow naming of a PRIMARY KEY constraint.

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
CONSTRAINT [pk_contraint_name] PRIMARY KEY([column_name]s...)
)

Add a PRIMARY KEY constraint to a PostgreSQL table.

Method 1:
>> ALTER TABLE [table_name] 
     ADD PRIMARY KEY ([column_name]s...);

Method 2:
>> ALTER TABLE [table_name]
     ADD CONSTRAINT [pk_contraint_name] PRIMARY KEY ([column_name]s...);

To drop a PRIMARY KEY constraint.

>> ALTER table [table_name]
      DROP CONSTRAINT [pk_constraint_name];

FOREIGN KEY

To create a FOREIGN KEY on the "column_name1".

 
CREATE TABLE table_nameA
(
column_nameA1 data_type PRIMARY KEY,
column_nameA2 data_type,
column_nameA3 data_type,
....
) 

CREATE TABLE table_nameB
(
column_nameB1 data_type PRIMARY KEY,
column_nameB2 data_type REFERENCES table_nameA (column_nameA1),
column_nameB3 data_type,
....
)

or,


CREATE TABLE table_nameB
(
column_nameB1 data_type PRIMARY KEY,
column_nameB2 data_type,
column_nameB3 data_type,
....
FOREIGN KEY(column_nameB2) REFERENCES table_nameA (column_nameA1)
)
  

To create a FOREIGN KEY using more than one "column_name"

 
CREATE TABLE table_name
(
column_name1 data_type PRIMARY KEY,
column_name2 data_type,
column_name3 data_type,
....
FOREIGN KEY(column_name2, column_name3) 
REFERENCES [other_table](column_C1, column_C2)
)

To allow naming of a FOREIGN KEY constraint.

CREATE TABLE table_name
(
column_name1 data_type PRIMARY KEY,
column_name2 data_type,
column_name3 data_type,
....
CONSTRAINT [fk_contraint_name] FOREIGN KEY ([column_name]s...)
)

Add a FOREIGN KEY constraint to a PostgreSQL table.

Method 1:
>> ALTER TABLE [table_name] 
     ADD FOREIGN KEY ([column_name]s...);

Method 2:
>> ALTER TABLE [table_name]
     ADD CONSTRAINT [fk_contraint_name] FOREIGN KEY ([column_name]s...);

To drop a FOREIGN KEY constraint.

>> ALTER table [table_name]
      DROP CONSTRAINT [fk_constraint_name];

CHECK

CREATE TABLE table_name
(
column_name1 data_type,
Method 1:
column_name2 data_type CHECK ( column_name2 <operator> [value]),
Method 2:
column_name3 data_type CONSTRAINT new_column_name CHECK ( column_name2 <operator> [value]),
Method 3:
CHECK ( column_nameM <operator> [value]),
Method 4:
CONSTRAINT check_constraint_name CHECK ( column_nameN <operator> [value]),
....
)

DEFAULT

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type DEFAULT value,
....
)

How to retrieve data from postgreSQL table

To select all data from a table.
>> SELECT * FROM [table_name];

To select some contents of the columns in a table.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name];

To select some contents of the columns in a table.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name];

To list only distinct (different) values in a column from a table.
>> SELECT DISTINCT [column_name] FROM [table_name];

To extract only those data that fulfil specified criterion(s).
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name]
      WHERE [column_name_K] <operator> [value_1] 
      AND|OR [column_name_L] <operator> [value_2];
List of operators:

=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

 IS                Same to

List of values:
NULL
NOT NULL

To search for a specified pattern in a column.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name]
      WHERE [column_name_K] LIKE <pattern>;

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for exactly one character
[characters_list]	Any single character in characters_list
[^characters_list]or [!characters_list]	Any single character not in characters_list

 
 Example:
 's%'                - String starts with character 's'
'%love%'        - String contents substring 'love'
's_n'                - String which has 3 characters only which starts with 's' and end with 'n'
'[a-z][A-Z]%' - String that starts with character a-z or A-Z.
'[^0-9]%'        - String that not starts with character 0-9.

To list rows in a table where the specified column in a multiple values.
>>  SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name]
      WHERE [column_name_k] IN (value_1, value_2, ...., value_N);
 example:
>> SELECT * FROM "children" WHERE "fatherName" IN (SELECT "name" FROM "parents");

To sort the result-set by a specified column.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name]
      ORDER BY [column_name_K] ASC|DESC;

To specific the number of records to return.
>>  SELECT [column_name_1], [column_name_2], ..., [column_name_N]
      FROM [table_name]
      LIMIT [number_of_records];

To select data from one table and inserts into a different table.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
     INTO [new_table_name] IN [external_database]
     FROM [old_table_name];

To combine result-set of two or more SELECT statement.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
     FROM [table_name_1]
     UNION 
     SELECT [column_name_1], [column_name_2], ..., [column_name_N]
     FROM [table_name_2];

To give an alias name to a table.
>> SELECT [column_name_1], [column_name_2], ..., [column_name_N]
     FROM [table_name] AS [alias_name];

To give an alias name to a column.
>> SELECT [column_name_1], 
     [column_name_2] AS [alias_name_1], ..., 
     [column_name_N] AS [alias_name_2]
     FROM [table_name];

 *************************************************************
 Different PostgreSQL JOIN

JOIN               : Return rows when there is at least one match in both tables (INNER JOIN)
LEFT JOIN   : Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN  : Return rows when there is a match in one of the tables
To join 2 tables.
>> SELECT [table_x].[column_name_1],..., [table_y].[column_name_K]
     FROM [table_x]
     JOIN|INNER JOIN|LEFT JOIN|RIGHT JOIN|FULL JOIN [table_y]
     ON [table_x].[column_name_A]=[table_y].[column_name_B];



 select  1 from ...， sql语句中的1代表什么意思？查出来是个什么结果？

        select 1 from table;与select anycol(目的表集合中的任意一行） from table;与select * from table 从作用上来说是没有差别的，都是查看是否有记录，一般是作条件查询用的。select 1 from 中的1是一常量（可以为任意数值），查到的所有行的值都是它，但从效率上来说，1>anycol>*，因为不用查字典表。

 

测试场景：（转自网络文献）
table表是一个数据表，假设表的行数为10行。
1：select  1 from table       增加临时列，每行的列值是写在select后的数，这条sql语句中是1
2：select count(1)  from table   管count(a)的a值如何变化，得出的值总是table表的行数
3：select sum(1) from table   计算临时列的和

 在SQL SERVER中用 1 测试了一下，发现结果如下：
1：测试结果，得出一个行数和table表行数一样的临时列（暂且这么叫，我也不知道该叫什么），每行的列值是1；
2：得出一个数，该数是table表的行数；
3：得出一个数，该数是table表的行数；
然后我又用“2”测试，结果如下：
1：得出一个行数和table表行数一样的临时列，每行的列值是2；
2：得出一个数，该数是table表的行数；
3：得出一个数，该数是table表的行数×2的数
然后我又用更大的数测试：
1：得出一个行数和table表行数一样的临时列，每行的列值是我写在select后的数；
2：还是得出一个数，该数是table表的行数；
3：得出一个数，该数是table表的行数×写在select后的数
    综上所述：第一种的写法是增加临时列，每行的列值是写在select后的数；第二种是不管count(a)的a值如何变化，得出的值总是table表的行数；第三种是计算临时列的和。

XML Schema Tutorial

An XML Schema describes the structure of an XML document.
XML Schema is more powerful than DTDs.
The XML Schema Language is also referred to as XML schema Definition (XSD).

An XML Schema:

• defines elements that can appear in a document
• defines attributes that can appear in a document
• defines which elements are child elements
• defines the order of child elements
• defines the number of child elements
• defines whether an element is empty or can include text
• defines data types for elements and attributes
• defines default and fixed values for elements and attributes.

Some benefits of that XML Schemas are written in XML:

• You don't have to learn a new language
• You can use your XML editor to edit your Schema files
• You can use your XML parser to parse your Schema files
• You can manipulate your Schema with the XML DOM
• You can transform your Schema with XSLT

 Software: Stylus Studio XML Development Environment

pom.xml

In your pom.xml file, add :
in "repositories" section :

<repository>
     <id>smartgwt</id>
     <url>http://www.smartclient.com/maven2</url>
</repository>

in "dependencies" section :

<dependency>
     <groupId>com.smartgwt</groupId>
     <artifactId>smartgwt</artifactId>
     <version>3.0</version>
</dependency>

PostgreSQL : Part 2

Login to the PostgresSQL command-line interface

    psql -U [username] [database_name]

***********************************************************************
To list all databases
> \l

To list all tables
> \dt

To list all indexs
> \di

To list all sequences
> \ds

To list all views
> \dv

To list all privileges
> \dp

To list all large objects
> \dl

To list all aggregates
> \da

To list all functions
> \df

To list all operators
> \do

To list all types
> \dT

To show all tables, views and sequences.
> \dS+

To show all schema.
> \dn

To show all tables for all schema.
> \dt *.

To show all tables for a schema.
> \dt [schema_name].

To show a table definition under a schema.
> \dt [schema_name].[table_name]

To exit from psql command prompt
> \q

To get the list of the available tables of a database in PostgreSQL.

> SELECT tablename FROM pg_tables
     WHERE tablename NOT LIKE ‘pg\\_%’
     AND tablename NOT LIKE ‘sql\\_%’;

To get the list of columns for a particular table in that database.
> SELECT attname FROM pg_attribute, pg_type
      WHERE typname = ‘table_name‘
      AND attrelid = typrelid
      AND attname NOT IN (‘cmin’, ‘cmax’, ‘ctid’, ‘oid’, ‘tableoid’, ‘xmin’, ‘xmax’);

To view table/row locks in PostgreSQL
> SELECT t.relname,l.locktype,page,virtualtransaction,pid,mode,granted 
      FROM pg_locks l, pg_stat_all_tables t WHERE l.relation=t.relid order by relation asc;

To show all enum label for an enum table.
> SELECT e.enumlabel FROM pg_enum e JOIN pg_type t ON e.enumtypid = t.oid WHERE t.typname = '[enum_table_name]';

How to find tables without primary keys.
> SELECT table_catalog, table_schema, table_name
     FROM information_schema.tables
     WHERE (table_catalog, table_schema, table_name) NOT IN
     (SELECT table_catalog, table_schema, table_name
     FROM information_schema.table_constraints
     WHERE constraint_type = 'PRIMARY KEY')
     AND table_schema NOT IN ('information_schema', 'pg_catalog');

How to find postgreSQL transaction in idle.

> SELECT procpid, datname, usename, client_addr,  current_query 
     FROM pg_stat_activity WHERE current_query!='<IDLE>';
> SELECT pg_cancel_backend (procpid);

To find how long locks have been active by executing the following query:

select
pg_stat_activity.datname,
pg_class.relname,
pg_locks.transactionid,
pg_locks.mode,
pg_locks.granted,
pg_stat_activity.usename,
substr(pg_stat_activity.current_query,1,40) as current_query,
pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age",
pg_stat_activity.procpid
from pg_stat_activity,pg_locks
left outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid and usename<>'postgres' order by query_start;

How to check postgreSQL errors.
Important: All postgresql errors are logged to /var/lib/pgsql/9.1/data/pg_log/ .

Save Chinese / Japanese character in Excel

response.setContentType("application/vnd.ms-excel:UTF-8");
response.setHeader("Content-Disposition", "attachment; filename=myOutputFile.csv");

// Excel does not recongize the UTF-8, add additional header (BOM) for excel
OutputStream outputStream = response.getOutputStream();
outputStream.write(0xEF);  
outputStream.write(0xBB);
outputStream.write(0xBF);  

BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(outputStream, "UTF-8"));

// Your method that generate the CSV content
writeSth(writer);

writer.flush();
writer.close();

Issue: virgo-web-server crashed

Issue 3:

when failed to run the virgo-web-server & see the error log as below

    8080 can be the JMX port too (9875?) if virgo's crashed after it's shut down on 8080.

Solutions:

kills virgo if it's crashed.

  

   #kill -9 `lsof -iTCP:8080 -Fp | perl -p -e "s/p//g"`

 or,

   #ps -A

get the processID for java (e.g:12345)

   #kill -9 12345

Issue: Failed to startup virgo-web-server

Issue 2:

[abc@localhost ~]$ /home/abc/virgo-web-server-2.1.1.RELEASE/bin/startup.sh –clear
 
Error: Could not find or load main class org.eclipse.virgo.osgi.launcher.JavaVersionChecker

Solution:

redownload virgo :P

OSGi bundle template.mf issue - can't use bundles in Virgo.

Issue 1:

:

:

        An Import-Package could not be resolved. Caused by missing constraint in bundle <com.springsource.javax.servlet.jsp.jstl_1.2.0>

             constraint: <Import-Package: com.sun.org.apache.xalan.internal.res; version="0.0.0">

 :

 :

Solution:

Go to the /home/wcs/virgo-web-server-2.1.1.RELEASE/lib directory.

Open the java6-server.profile

 org.xml.sax.ext,\
 org.xml.sax.helpers,\
 com.sun.org.apache.xalan.internal.res,\
 com.sun.org.apache.xml.internal.utils,\
 com.sun.org.apache.xpath.internal,\
 com.sun.org.apache.xpath.internal.jaxp,\
 com.sun.org.apache.xpath.internal.objects
org.osgi.framework.bootdelegation = \
 org.eclipse.virgo.osgi.extensions.*,\

 

Maven

To force dependency redownloads

# mvn package -U

If you get it to compile in mvn, do

#mvn install

else
nuke .../.m2/repository/com/...

To do the maven compile
#mvn clean package -f <.../pom.xml>

set a environment variable in CentOS

Example: set $JAVA_HOME on CentOS

# vi ~/.bash_profile

Add the following line to .bash_profile

export JAVA_HOME=/usr/local/jdk1.7.0_2 (your actual location of the jdk)

log out then log back in.

Enter this command on the terminal to test:

echo $JAVA_HOME