Category: postgresql

PostgreSQL – Operators ”; Previous Next What is an Operator in PostgreSQL? An operator is a reserved word or a character used primarily in a PostgreSQL statement”s WHERE clause to perform operation(s), such as comparisons and arithmetic operations. Operators are used to specify conditions in a PostgreSQL statement and to serve as conjunctions for multiple conditions in a statement. Arithmetic operators Comparison operators Logical operators Bitwise operators PostgreSQL Arithmetic Operators Assume variable a holds 2 and variable b holds 3, then − Example Operator Description Example + Addition – Adds values on either side of the operator a + b will give 5 – Subtraction – Subtracts right hand operand from left hand operand a – b will give -1 * Multiplication – Multiplies values on either side of the operator a * b will give 6 / Division – Divides left hand operand by right hand operand b / a will give 1 % Modulus – Divides left hand operand by right hand operand and returns remainder b % a will give 1 ^ Exponentiation – This gives the exponent value of the right hand operand a ^ b will give 8 |/ square root |/ 25.0 will give 5 ||/ Cube root ||/ 27.0 will give 3 ! factorial 5 ! will give 120 !! factorial (prefix operator) !! 5 will give 120 PostgreSQL Comparison Operators Assume variable a holds 10 and variable b holds 20, then − Show Examples Operator Description Example = Checks if the values of two operands are equal or not, if yes then condition becomes true. (a = b) is not true. != Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. (a != b) is true. <> Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. (a <> b) is true. > Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true. (a > b) is not true. < Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true. (a < b) is true. >= Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true. (a >= b) is not true. <= Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true. (a <= b) is true. PostgreSQL Logical Operators Here is a list of all the logical operators available in PostgresSQL. Show Examples S. No. Operator & Description 1 AND The AND operator allows the existence of multiple conditions in a PostgresSQL statement”s WHERE clause. 2 NOT The NOT operator reverses the meaning of the logical operator with which it is used. Eg. NOT EXISTS, NOT BETWEEN, NOT IN etc. This is negate operator. 3 OR The OR operator is used to combine multiple conditions in a PostgresSQL statement”s WHERE clause. PostgreSQL Bit String Operators Bitwise operator works on bits and performs bit-by-bit operation. The truth table for & and | is as follows − p q p & q p | q 0 0 0 0 0 1 0 1 1 1 1 1 1 0 0 1 Assume if A = 60; and B = 13; now in binary format they will be as follows − A = 0011 1100 B = 0000 1101 —————– A&B = 0000 1100 A|B = 0011 1101 ~A  = 1100 0011 Show Examples The Bitwise operators supported by PostgreSQL are listed in the following table − Operator Description Example & Binary AND Operator copies a bit to the result if it exists in both operands. (A & B) will give 12 which is 0000 1100 | Binary OR Operator copies a bit if it exists in either operand. (A | B) will give 61 which is 0011 1101 ~ Binary Ones Complement Operator is unary and has the effect of ”flipping” bits. (~A ) will give -61 which is 1100 0011 in 2”s complement form due to a signed binary number. << Binary Left Shift Operator. The left operands value is moved left by the number of bits specified by the right operand. A << 2 will give 240 which is 1111 0000 >> Binary Right Shift Operator. The left operands value is moved right by the number of bits specified by the right operand. A >> 2 will give 15 which is 0000 1111 # bitwise XOR. A # B will give 49 which is 00110001 Print Page Previous Next Advertisements ”;

PostgreSQL – CREATE Table ”; Previous Next The PostgreSQL CREATE TABLE statement is used to create a new table in any of the given database. Syntax Basic syntax of CREATE TABLE statement is as follows − CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, PRIMARY KEY( one or more columns ) ); CREATE TABLE is a keyword, telling the database system to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Initially, the empty table in the current database is owned by the user issuing the command. Then, in brackets, comes the list, defining each column in the table and what sort of data type it is. The syntax will become clear with an example given below. Examples The following is an example, which creates a COMPANY table with ID as primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table − CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); Let us create one more table, which we will use in our exercises in subsequent chapters − CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL ); You can verify if your table has been created successfully using d command, which will be used to list down all the tables in an attached database. testdb-# d The above given PostgreSQL statement will produce the following result − List of relations Schema | Name | Type | Owner ——–+————+——-+———- public | company | table | postgres public | department | table | postgres (2 rows) Use d tablename to describe each table as shown below − testdb-# d company The above given PostgreSQL statement will produce the following result − Table “public.company” Column | Type | Modifiers ———–+—————+———– id | integer | not null name | text | not null age | integer | not null address | character(50) | salary | real | join_date | date | Indexes: “company_pkey” PRIMARY KEY, btree (id) Print Page Previous Next Advertisements ”;

PostgreSQL – Perl Interface ”; Previous Next Installation The PostgreSQL can be integrated with Perl using Perl DBI module, which is a database access module for the Perl programming language. It defines a set of methods, variables and conventions that provide a standard database interface. Here are simple steps to install DBI module on your Linux/Unix machine − $ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz $ tar xvfz DBI-1.625.tar.gz $ cd DBI-1.625 $ perl Makefile.PL $ make $ make install If you need to install SQLite driver for DBI, then it can be installed as follows − $ wget http://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/DBD-Pg-2.19.3.tar.gz $ tar xvfz DBD-Pg-2.19.3.tar.gz $ cd DBD-Pg-2.19.3 $ perl Makefile.PL $ make $ make install Before you start using Perl PostgreSQL interface, find the pg_hba.conf file in your PostgreSQL installation directory and add the following line − # IPv4 local connections: host all all 127.0.0.1/32 md5 You can start/restart the postgres server, in case it is not running, using the following command − [root@host]# service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ] DBI Interface APIs Following are the important DBI routines, which can suffice your requirement to work with SQLite database from your Perl program. If you are looking for a more sophisticated application, then you can look into Perl DBI official documentation. S. No. API & Description 1 DBI→connect($data_source, “userid”, “password”, %attr) Establishes a database connection, or session, to the requested $data_source. Returns a database handle object if the connection succeeds. Datasource has the form like : DBI:Pg:dbname=$database;host=127.0.0.1;port=5432 Pg is PostgreSQL driver name and testdb is the name of database. 2 $dbh→do($sql) This routine prepares and executes a single SQL statement. Returns the number of rows affected or undef on error. A return value of -1 means the number of rows is not known, not applicable, or not available. Here $dbh is a handle returned by DBI→connect() call. 3 $dbh→prepare($sql) This routine prepares a statement for later execution by the database engine and returns a reference to a statement handle object. 4 $sth→execute() This routine performs whatever processing is necessary to execute the prepared statement. An undef is returned if an error occurs. A successful execute always returns true regardless of the number of rows affected. Here $sth is a statement handle returned by $dbh→prepare($sql) call. 5 $sth→fetchrow_array() This routine fetches the next row of data and returns it as a list containing the field values. Null fields are returned as undef values in the list. 6 $DBI::err This is equivalent to $h→err, where $h is any of the handle types like $dbh, $sth, or $drh. This returns native database engine error code from the last driver method called. 7 $DBI::errstr This is equivalent to $h→errstr, where $h is any of the handle types like $dbh, $sth, or $drh. This returns the native database engine error message from the last DBI method called. 8 $dbh->disconnect() This routine closes a database connection previously opened by a call to DBI→connect(). Connecting to Database The following Perl code shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned. #!/usr/bin/perl use DBI; use strict; my $driver = “Pg”; my $database = “testdb”; my $dsn = “DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432”; my $userid = “postgres”; my $password = “pass123”; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr; print “Opened database successfullyn”; Now, let us run the above given program to open our database testdb; if the database is successfully opened then it will give the following message − Open database successfully Create a Table The following Perl program will be used to create a table in previously created database − #!/usr/bin/perl use DBI; use strict; my $driver = “Pg”; my $database = “testdb”; my $dsn = “DBI:$driver:dbname=$database;host=127.0.0.1;port=5432”; my $userid = “postgres”; my $password = “pass123”; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr; print “Opened database successfullyn”; my $stmt = qq(CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);); my $rv = $dbh->do($stmt); if($rv < 0) { print $DBI::errstr; } else { print “Table created successfullyn”; } $dbh->disconnect(); When the above given program is executed, it will create COMPANY table in your testdb and it will display the following messages − Opened database successfully Table created successfully INSERT Operation The following Perl program shows how we can create records in our COMPANY table created in above example − #!/usr/bin/perl use DBI; use strict; my $driver = “Pg”; my $database = “testdb”; my $dsn = “DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432”; my $userid = “postgres”; my $password = “pass123”; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr; print “Opened database successfullyn”; my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Paul”, 32, ”California”, 20000.00 )); my $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ”Allen”, 25, ”Texas”, 15000.00 )); $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ”Teddy”, 23, ”Norway”, 20000.00 )); $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, ”Mark”, 25, ”Rich-Mond ”, 65000.00 );); $rv = $dbh->do($stmt) or die $DBI::errstr; print “Records created successfullyn”; $dbh->disconnect(); When the above given program is executed, it will create given records in COMPANY table and will display the following two lines − Opened database successfully Records created successfully SELECT Operation The following Perl program shows how we can fetch and display records from our COMPANY table created in above example − #!/usr/bin/perl use DBI; use strict; my $driver = “Pg”; my $database = “testdb”; my $dsn = “DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432”; my $userid = “postgres”; my $password = “pass123”; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr; print “Opened database successfullyn”; my $stmt

PostgreSQL – Overview ”; Previous Next PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development phase and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. This tutorial will give you a quick start with PostgreSQL and make you comfortable with PostgreSQL programming. What is PostgreSQL? PostgreSQL (pronounced as post-gress-Q-L) is an open source relational database management system (DBMS) developed by a worldwide team of volunteers. PostgreSQL is not controlled by any corporation or other private entity and the source code is available free of charge. A Brief History of PostgreSQL PostgreSQL, originally called Postgres, was created at UCB by a computer science professor named Michael Stonebraker. Stonebraker started Postgres in 1986 as a follow-up project to its predecessor, Ingres, now owned by Computer Associates. 1977-1985 − A project called INGRES was developed. Proof-of-concept for relational databases Established the company Ingres in 1980 Bought by Computer Associates in 1994 1986-1994 − POSTGRES Development of the concepts in INGRES with a focus on object orientation and the query language – Quel The code base of INGRES was not used as a basis for POSTGRES Commercialized as Illustra (bought by Informix, bought by IBM) 1994-1995 − Postgres95 Support for SQL was added in 1994 Released as Postgres95 in 1995 Re-released as PostgreSQL 6.0 in 1996 Establishment of the PostgreSQL Global Development Team Key Features of PostgreSQL PostgreSQL runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It supports text, images, sounds, and video, and includes programming interfaces for C / C++, Java, Perl, Python, Ruby, Tcl and Open Database Connectivity (ODBC). PostgreSQL supports a large part of the SQL standard and offers many modern features including the following − Complex SQL queries SQL Sub-selects Foreign keys Trigger Views Transactions Multiversion concurrency control (MVCC) Streaming Replication (as of 9.0) Hot Standby (as of 9.0) You can check official documentation of PostgreSQL to understand the above-mentioned features. PostgreSQL can be extended by the user in many ways. For example by adding new − Data types Functions Operators Aggregate functions Index methods Procedural Languages Support PostgreSQL supports four standard procedural languages, which allows the users to write their own code in any of the languages and it can be executed by PostgreSQL database server. These procedural languages are – PL/pgSQL, PL/Tcl, PL/Perl and PL/Python. Besides, other non-standard procedural languages like PL/PHP, PL/V8, PL/Ruby, PL/Java, etc., are also supported. Print Page Previous Next Advertisements ”;

PostgreSQL Tutorial PDF Version Quick Guide Resources Job Search Discussion PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. PostgreSQL runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. This tutorial will give you quick start with PostgreSQL and make you comfortable with PostgreSQL programming. Audience This tutorial has been prepared for the beginners to help them understand the basic to advanced concepts related to PostgreSQL Database. Prerequisites Before you start practicing with various types of examples given in this reference, I”m making an assumption that you are already aware of what a database is, especially RDBMS and what a computer language is. Print Page Previous Next Advertisements ”;

PostgreSQL – Environment Setup ”; Previous Next To start understanding the PostgreSQL basics, first let us install the PostgreSQL. This chapter explains about installing the PostgreSQL on Linux, Windows and Mac OS platforms. Installing PostgreSQL on Linux/Unix Follow the given steps to install PostgreSQL on your Linux machine. Make sure you are logged in as root before you proceed for the installation. Pick the version number of PostgreSQL you want and, as exactly as possible, the platform you want from EnterpriseDB I downloaded postgresql-9.2.4-1-linux-x64.run for my 64 bit CentOS-6 machine. Now, let us execute it as follows − [root@host]# chmod +x postgresql-9.2.4-1-linux-x64.run [root@host]# ./postgresql-9.2.4-1-linux-x64.run ———————————————————————— Welcome to the PostgreSQL Setup Wizard. ———————————————————————— Please specify the directory where PostgreSQL will be installed. Installation Directory [/opt/PostgreSQL/9.2]: Once you launch the installer, it asks you a few basic questions like location of the installation, password of the user who will use database, port number, etc. So keep all of them at their default values except password, which you can provide password as per your choice. It will install PostgreSQL at your Linux machine and will display the following message − Please wait while Setup installs PostgreSQL on your computer. Installing 0% ______________ 50% ______________ 100% ######################################### ———————————————————————– Setup has finished installing PostgreSQL on your computer. Follow the following post-installation steps to create your database − [root@host]# su – postgres Password: bash-4.1$ createdb testdb bash-4.1$ psql testdb psql (8.4.13, server 9.2.4) test=# You can start/restart postgres server in case it is not running using the following command − [root@host]# service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ] If your installation was correct, you will have PotsgreSQL prompt test=# as shown above. Installing PostgreSQL on Windows Follow the given steps to install PostgreSQL on your Windows machine. Make sure you have turned Third Party Antivirus off while installing. Pick the version number of PostgreSQL you want and, as exactly as possible, the platform you want from EnterpriseDB I downloaded postgresql-9.2.4-1-windows.exe for my Windows PC running in 32bit mode, so let us run postgresql-9.2.4-1-windows.exe as administrator to install PostgreSQL. Select the location where you want to install it. By default, it is installed within Program Files folder. The next step of the installation process would be to select the directory where your data would be stored. By default, it is stored under the “data” directory. Next, the setup asks for password, so you can use your favorite password. The next step; keep the port as default. In the next step, when asked for “Locale”, I selected “English, United States”. It takes a while to install PostgreSQL on your system. On completion of the installation process, you will get the following screen. Uncheck the checkbox and click the Finish button. After the installation process is completed, you can access pgAdmin III, StackBuilder and PostgreSQL shell from your Program Menu under PostgreSQL 9.2. Installing PostgreSQL on Mac Follow the given steps to install PostgreSQL on your Mac machine. Make sure you are logged in as administrator before you proceed for the installation. Pick the latest version number of PostgreSQL for Mac OS available at EnterpriseDB I downloaded postgresql-9.2.4-1-osx.dmg for my Mac OS running with OS X version 10.8.3. Now, let us open the dmg image in finder and just double click it which will give you PostgreSQL installer in the following window − Next, click the postgres-9.2.4-1-osx icon, which will give a warning message. Accept the warning and proceed for further installation. It will ask for the administrator password as seen in the following window − Enter the password, proceed for the installation, and after this step, restart your Mac machine. If you do not see the following window, start your installation once again. Once you launch the installer, it asks you a few basic questions like location of the installation, password of the user who will use database, port number etc. Therefore, keep all of them at their default values except the password, which you can provide as per your choice. It will install PostgreSQL in your Mac machine in the Application folder which you can check − Now, you can launch any of the program to start with. Let us start with SQL Shell. When you launch SQL Shell, just use all the default values it displays except, enter your password, which you had selected at the time of installation. If everything goes fine, then you will be inside postgres database and a postgress# prompt will be displayed as shown below − Congratulations!!! Now you have your environment ready to start with PostgreSQL database programming. Print Page Previous Next Advertisements ”;

PostgreSQL – Syntax ”; Previous Next This chapter provides a list of the PostgreSQL SQL commands, followed by the precise syntax rules for each of these commands. This set of commands is taken from the psql command-line tool. Now that you have Postgres installed, open the psql as − Program Files → PostgreSQL 9.2 → SQL Shell(psql). Using psql, you can generate a complete list of commands by using the help command. For the syntax of a specific command, use the following command − postgres-# help <command_name> The SQL Statement An SQL statement is comprised of tokens where each token can represent either a keyword, identifier, quoted identifier, constant, or special character symbol. The table given below uses a simple SELECT statement to illustrate a basic, but complete, SQL statement and its components. SELECT id, name FROM states Token Type Keyword Identifiers Keyword Identifier Description Command Id and name columns Clause Table name PostgreSQL SQL commands ABORT Abort the current transaction. ABORT [ WORK | TRANSACTION ] ALTER AGGREGATE Change the definition of an aggregate function. ALTER AGGREGATE name ( type ) RENAME TO new_name ALTER AGGREGATE name ( type ) OWNER TO new_owner ALTER CONVERSION Change the definition of a conversion. ALTER CONVERSION name RENAME TO new_name ALTER CONVERSION name OWNER TO new_owner ALTER DATABASE Change a database specific parameter. ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT } ALTER DATABASE name RESET parameter ALTER DATABASE name RENAME TO new_name ALTER DATABASE name OWNER TO new_owner ALTER DOMAIN Change the definition of a domain specific parameter. ALTER DOMAIN name { SET DEFAULT expression | DROP DEFAULT } ALTER DOMAIN name { SET | DROP } NOT NULL ALTER DOMAIN name ADD domain_constraint ALTER DOMAIN name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ] ALTER DOMAIN name OWNER TO new_owner ALTER FUNCTION Change the definition of a function. ALTER FUNCTION name ( [ type [, …] ] ) RENAME TO new_name ALTER FUNCTION name ( [ type [, …] ] ) OWNER TO new_owner ALTER GROUP Change a user group. ALTER GROUP groupname ADD USER username [, … ] ALTER GROUP groupname DROP USER username [, … ] ALTER GROUP groupname RENAME TO new_name ALTER INDEX Change the definition of an index. ALTER INDEX name OWNER TO new_owner ALTER INDEX name SET TABLESPACE indexspace_name ALTER INDEX name RENAME TO new_name ALTER LANGUAGE Change the definition of a procedural language. ALTER LANGUAGE name RENAME TO new_name ALTER OPERATOR Change the definition of an operator. ALTER OPERATOR name ( { lefttype | NONE }, { righttype | NONE } ) OWNER TO new_owner ALTER OPERATOR CLASS Change the definition of an operator class. ALTER OPERATOR CLASS name USING index_method RENAME TO new_name ALTER OPERATOR CLASS name USING index_method OWNER TO new_owner ALTER SCHEMA Change the definition of a schema. ALTER SCHEMA name RENAME TO new_name ALTER SCHEMA name OWNER TO new_owner ALTER SEQUENCE Change the definition of a sequence generator. ALTER SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] ALTER TABLE Change the definition of a table. ALTER TABLE [ ONLY ] name [ * ] action [, … ] ALTER TABLE [ ONLY ] name [ * ] RENAME [ COLUMN ] column TO new_column ALTER TABLE name RENAME TO new_name Where action is one of the following lines − ADD [ COLUMN ] column_type [ column_constraint [ … ] ] DROP [ COLUMN ] column [ RESTRICT | CASCADE ] ALTER [ COLUMN ] column TYPE type [ USING expression ] ALTER [ COLUMN ] column SET DEFAULT expression ALTER [ COLUMN ] column DROP DEFAULT ALTER [ COLUMN ] column { SET | DROP } NOT NULL ALTER [ COLUMN ] column SET STATISTICS integer ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } ADD table_constraint DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ] CLUSTER ON index_name SET WITHOUT CLUSTER SET WITHOUT OIDS OWNER TO new_owner SET TABLESPACE tablespace_name ALTER TABLESPACE Change the definition of a tablespace. ALTER TABLESPACE name RENAME TO new_name ALTER TABLESPACE name OWNER TO new_owner ALTER TRIGGER Change the definition of a trigger. ALTER TRIGGER name ON table RENAME TO new_name ALTER TYPE Change the definition of a type. ALTER TYPE name OWNER TO new_owner ALTER USER Change a database user account. ALTER USER name [ [ WITH ] option [ … ] ] ALTER USER name RENAME TO new_name ALTER USER name SET parameter { TO | = } { value | DEFAULT } ALTER USER name RESET parameter Where option can be − [ ENCRYPTED | UNENCRYPTED ] PASSWORD ”password” | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | VALID UNTIL ”abstime” ANALYZE Collect statistics about a database. ANALYZE [ VERBOSE ] [ table [ (column [, …] ) ] ] BEGIN Start a transaction block. BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, …] ] Where transaction_mode is one of − ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } READ WRITE | READ ONLY CHECKPOINT Force a transaction log checkpoint. CHECKPOINT CLOSE Close a cursor. CLOSE name CLUSTER Cluster a table according to an index. CLUSTER index_name ON table_name CLUSTER table_name CLUSTER COMMENT Define or change the comment of an object. COMMENT ON { TABLE object_name | COLUMN table_name.column_name | AGGREGATE agg_name (agg_type) | CAST (source_type AS target_type) | CONSTRAINT constraint_name ON table_name | CONVERSION object_name | DATABASE object_name | DOMAIN object_name | FUNCTION func_name (arg1_type, arg2_type, …) | INDEX object_name | LARGE OBJECT large_object_oid | OPERATOR op (left_operand_type, right_operand_type) | OPERATOR CLASS object_name USING index_method | [ PROCEDURAL ] LANGUAGE object_name | RULE rule_name ON table_name | SCHEMA object_name | SEQUENCE object_name | TRIGGER trigger_name ON table_name | TYPE object_name | VIEW object_name } IS

PostgreSQL – Data Type ”; Previous Next In this chapter, we will discuss about the data types used in PostgreSQL. While creating table, for each column, you specify a data type, i.e., what kind of data you want to store in the table fields. This enables several benefits − Consistency − Operations against columns of same data type give consistent results and are usually the fastest. Validation − Proper use of data types implies format validation of data and rejection of data outside the scope of data type. Compactness − As a column can store a single type of value, it is stored in a compact way. Performance − Proper use of data types gives the most efficient storage of data. The values stored can be processed quickly, which enhances the performance. PostgreSQL supports a wide set of Data Types. Besides, users can create their own custom data type using CREATE TYPE SQL command. There are different categories of data types in PostgreSQL. They are discussed below. Numeric Types Numeric types consist of two-byte, four-byte, and eight-byte integers, four-byte and eight-byte floating-point numbers, and selectable-precision decimals. The following table lists the available types. Name Storage Size Description Range smallint 2 bytes small-range integer -32768 to +32767 integer 4 bytes typical choice for integer -2147483648 to +2147483647 bigint 8 bytes large-range integer -9223372036854775808 to 9223372036854775807 decimal variable user-specified precision,exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point numeric variable user-specified precision,exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point real 4 bytes variable-precision,inexact 6 decimal digits precision double precision 8 bytes variable-precision,inexact 15 decimal digits precision smallserial 2 bytes small autoincrementing integer 1 to 32767 serial 4 bytes autoincrementing integer 1 to 2147483647 bigserial 8 bytes large autoincrementing integer 1 to 9223372036854775807 Monetary Types The money type stores a currency amount with a fixed fractional precision. Values of the numeric, int, and bigint data types can be cast to money. Using Floating point numbers is not recommended to handle money due to the potential for rounding errors. Name Storage Size Description Range money 8 bytes currency amount -92233720368547758.08 to +92233720368547758.07 Character Types The table given below lists the general-purpose character types available in PostgreSQL. S. No. Name & Description 1 character varying(n), varchar(n) variable-length with limit 2 character(n), char(n) fixed-length, blank padded 3 text variable unlimited length Binary Data Types The bytea data type allows storage of binary strings as in the table given below. Name Storage Size Description bytea 1 or 4 bytes plus the actual binary string variable-length binary string Date/Time Types PostgreSQL supports a full set of SQL date and time types, as shown in table below. Dates are counted according to the Gregorian calendar. Here, all the types have resolution of 1 microsecond / 14 digits except date type, whose resolution is day. Name Storage Size Description Low Value High Value timestamp [(p)] [without time zone ] 8 bytes both date and time (no time zone) 4713 BC 294276 AD TIMESTAMPTZ 8 bytes both date and time, with time zone 4713 BC 294276 AD date 4 bytes date (no time of day) 4713 BC 5874897 AD time [ (p)] [ without time zone ] 8 bytes time of day (no date) 00:00:00 24:00:00 time [ (p)] with time zone 12 bytes times of day only, with time zone 00:00:00+1459 24:00:00-1459 interval [fields ] [(p) ] 12 bytes time interval -178000000 years 178000000 years Boolean Type PostgreSQL provides the standard SQL type Boolean. The Boolean data type can have the states true, false, and a third state, unknown, which is represented by the SQL null value. Name Storage Size Description boolean 1 byte state of true or false Enumerated Type Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in a number of programming languages. Unlike other types, Enumerated Types need to be created using CREATE TYPE command. This type is used to store a static, ordered set of values. For example compass directions, i.e., NORTH, SOUTH, EAST, and WEST or days of the week as shown below − CREATE TYPE week AS ENUM (”Mon”, ”Tue”, ”Wed”, ”Thu”, ”Fri”, ”Sat”, ”Sun”); Enumerated, once created, can be used like any other types. Geometric Type Geometric data types represent two-dimensional spatial objects. The most fundamental type, the point, forms the basis for all of the other types. Name Storage Size Representation Description point 16 bytes Point on a plane (x,y) line 32 bytes Infinite line (not fully implemented) ((x1,y1),(x2,y2)) lseg 32 bytes Finite line segment ((x1,y1),(x2,y2)) box 32 bytes Rectangular box ((x1,y1),(x2,y2)) path 16+16n bytes Closed path (similar to polygon) ((x1,y1),…) path 16+16n bytes Open path [(x1,y1),…] polygon 40+16n Polygon (similar to closed path) ((x1,y1),…) circle 24 bytes Circle <(x,y),r> (center point and radius) Network Address Type PostgreSQL offers data types to store IPv4, IPv6, and MAC addresses. It is better to use these types instead of plain text types to store network addresses, because these types offer input error checking and specialized operators and functions. Name Storage Size Description cidr 7 or 19 bytes IPv4 and IPv6 networks inet 7 or 19 bytes IPv4 and IPv6 hosts and networks macaddr 6 bytes MAC addresses Bit String Type Bit String Types are used to store bit masks. They are either 0 or 1. There are two SQL bit types: bit(n) and bit varying(n), where n is a positive integer. Text Search Type This type supports full text search, which is the activity of searching through a collection of natural-language documents to locate those that best match a query. There are two Data Types for this − S. No. Name & Description 1 tsvector This is a sorted list of distinct words that have been normalized to merge different variants of the same word, called as “lexemes”. 2 tsquery This stores lexemes that are to be searched for,

PostgreSQL – DROP Table ”; Previous Next The PostgreSQL DROP TABLE statement is used to remove a table definition and all associated data, indexes, rules, triggers, and constraints for that table. You have to be careful while using this command because once a table is deleted then all the information available in the table would also be lost forever. Syntax Basic syntax of DROP TABLE statement is as follows − DROP TABLE table_name; Example We had created the tables DEPARTMENT and COMPANY in the previous chapter. First, verify these tables (use d to list the tables) − testdb-# d This would produce the following result − List of relations Schema | Name | Type | Owner ——–+————+——-+———- public | company | table | postgres public | department | table | postgres (2 rows) This means DEPARTMENT and COMPANY tables are present. So let us drop them as follows − testdb=# drop table department, company; This would produce the following result − DROP TABLE testdb=# d relations found. testdb=# The message returned DROP TABLE indicates that drop command is executed successfully. Print Page Previous Next Advertisements ”;