≡ Menu

PostgreSQL

PostgreSQL is an enterprise level open source database. This is one of the most reliable, robust, feature-rich, and powerful relational database system. Most importantly, PostgreSQL is well known for its ability to handle heavy load, and deliver high performance. This supports pretty much all the features that you would ever need from a RDBMS system. [...]

{ 0 comments }

If you have PostgreSQL running in your environment, managing it from command-line might be bit difficult as you have to remember all the db commands and syntax. If you are MySQL DBA, you might notice that there is a slight difference in the syntax for few of the commands in PostgreSQL. Also, some of the [...]

{ 2 comments }

When you install PostgreSQL, by default connection to the database using TCP/IP is not allowed. When you try to connect from a client to a remote PostgreSQL database using psql command, you might get "psql: could not connect to server: Connection refused" error message. In the following example, from a client machine, we are trying [...]

{ 7 comments }

Most of you are already familiar with SQL database, and have a good knowledge on either MySQL, Oracle, or other SQL databases. In the last several years, NoSQL database is getting widely adopted to solve various business problems. It is helpful to understand the difference between SQL and NoSQL database, and some of available NoSQL [...]

{ 31 comments }

PostgreSQL Trigger Tutorial with EMP Table Examples

Question: I have created triggers in Oracle database. But, I'm new to PostgreSQL. Can you explain me how to create a trigger in Postgres with proper examples? Answer: A trigger can be used to execute a function when an event (insert, or delete, or update) occurs in a table. Let us review how to create [...]

{ 17 comments }

8 PostgreSQL Date and Time Function Examples

In this article, let us review following PostgreSQL date and time functions with practical examples. now() now()::date now()::time date_part() age() extract() date_trunc() to_char() to_timestamp() Get current date and time using the following query. dbase=# select now(); now ------------------------------ 2010-06-19 09:28:43.98216-07 (1 row) Use the ::time as shown below to get only the time (without date). [...]

{ 7 comments }

Question: How do I executed PostgreSQL Commands inside a Linux / UNIX shell script? Answer: With the help of the psql interactive terminal, you can execute the psql commands from the shell script. For this purpose, you should enable the password less login by pg_hba.conf, or .pgpass. psql DBNAME USERNAME << EOF statement 1; statement [...]

{ 7 comments }

15 Advanced PostgreSQL Commands with Examples

Some of the open source application comes with postgreSQL database. To maintain those application, companies may not hire a fulltime postgreSQL DBA. Instead they may request the existing Oracle DBA, or Linux system administrator, or programmers to maintain the potgreSQL. In this article let discuss about the 15 practical postgresql database commands which will be [...]

{ 33 comments }