The vi, vim editor in linux

In this quick tutorial I will share some vim basics. Vim is a powerful text editor used in CLI (command line interface). As there are lots of configuration files in gnu/linux, which are all in clear text format, you’ll often need to edit them using a text editor. vim (in short vi) is a great tool to use.
One of the first things to know about vi is that it typically functions in three different modes:

  1. Command mode
  2. Insert mode
  3. Last line mode

vi command mode

When you first start editing a file with the vi editor you will be in vi command mode. In this mode you can issue many vi commands, including commands like insert, append, and delete, and other search and navigation commands that let you move around your file.

Possibly the most important thing to know is that when you’re in command mode you can’t insert text immediately. You first need to issue an insert, append, or open command to insert text. These commands are actually fairly simple, and I’ve documented them in this

Lets talk about other vim modes.

vi insert mode

Once you issue a vi insert, append, or open command, you will be in vi insert mode. If you’re working with a modern vi or vim implementation, your vi editor is typically configured to show the current mode of operation, so when you go into insert mode, you’ll see a text string like this on the last line of your vi editor window:

-- INSERT --

At this point you can (a) type text into your file and (b) use the arrow keys to navigate around your file just as you would do with any other text editor.

A very important concept to know is that when you’re in vi insert mode, but you want to switch back to vi command mode, you easily move back to command mode by pressing the [Esc] key. This command is so important, I’ll show it again:

[Esc]

This command is very common, and I often see expert vi users press the [Esc] key several times in a row. They usually do this (a) to be sure they hit the key and they’re really back in command mode, and (b) to hear the beep from the computer, which happens when you press the [Esc] key when you’re already in vi command mode. This seems to serve as a form of feedback which assures them that they’re in command mode.

vi last line mode

The last vi mode is known as vi last line mode. You can only get to last line mode from command mode, and you get into last line mode by pressing the colon key, like this:

:

After pressing this key, you’ll see a colon character appear at the beginning of the last line of your vi editor window, and your cursor will be moved to that position. This indicates that vi is ready for you to type in a “last line command”.

From this vi command prompt you can do all sorts of really amazing things. You can do simple things, like quitting your vi session, like this:

:q

or this:

:q!

or this:

:wq

From last line more you can also perform some amazing vi search commands or vim search and replace commands. Another cool thing is that you can issue Linux or Unix commands from within your vi editor session, like this simple ls command:

:!ls

It’s really handy sometimes to be able to stay in your vi editing session but still be able to run Unix or Linux commands.

And finally, you can also issue many vi configuration commands, such as this command that tells vi to show lines numbers in your current editor window:

:set shownumber

There is a ton of power in this vi last line mode, and I’ve tried to share pieces of this power in a variety of different vi tutorials. (Just search this blog for “vi” or “vi editor” and you’ll find a wealth of vi tutorials.)

One last note about the vi last line mode: If you’re in last line mode, and you want to switch back to command mode, there are several different ways to do this. For consistency, one way to do this is to press the [Esc] key twice, like this:

[Esc][Esc]

(This is consistent with the method of moving from insert mode back to command mode, except you have to press the [Esc] key twice.)

A second way is to press the [Backspace] key until anything you typed and the initial “:” character are gone. At this point you’ll be back in command mode.

Finally, if you haven’t typed anything at all, and you’re just looking at the “:” prompt on the last line, you can just press [Enter], and you’ll be placed back in vi command mode.

After opening a new OR existing file in vim editor with vim you can try modes listed here.

Insert mod : lets you insert text in a document. shotcurt : “i” (insert where the cursor is) or “o” (insert at the beginning of the following line).

Visual mod : permits to select the text like you would do with a mouse but using the keyboard instead of the mouse. Useful to copy several lines for example. shotcurt : V

Let’s now speak about the command mode.

A command begins with the symbol “:”.

When you are in another mod you can use the escape key (sometimes you’ll need to hit it twice) to come back to command mod at any time.

save : :w
save and exit : :wq
exit : :q
force : ! (example :w! :q!)
vertical split : open a document and then type :vsplit /path-to-document/document and this will open the specified document and split the screen so you can see both documents.
copy : y
copy a line : yy
paste : p
cut : d
cut a line : dd

I repeat these are very basic commands for vim, but they are very useful, and I hope this will help you configuring your Linux.

How to change date and time in linux? date-time

Today I wanted to change date and time on one of my server quickly from command line. I am aware with command line options but every time I want to do it, I need to read man page or help to get perfect combination so i decided to write this small how to which can help me and you every time we want to change date and time in linux from command line.

Remember, CLI (command line interface) is most powerful for users / administrators like us.
To change system date in linux, type:-

#date MMDDhhmmYYYY.ss

For example: i want to change my system date to Dec 25 2009, 5.30pm, I will type:-
#date 122517302009.00

It’s simple, Isn’t it ?

Note:
MM – month
DD – day
YYYY – year
hh – hour is based on 24 hour
mm – minutes
ss – seconds

Hope this will help you.

How to disable users in linux/unix?

This how-to will show how to disable a user account under linux. This might be useful in the situation where you don’t want to permanently remove the user, but you just want it disabled and no longer able to use the system. The user will still receive emails for example, but he will not be able to login and check them out.

In latest linux systems /etc/shadow stores the encrypted user passwords. The quickest way to disable a user is to alter is password stored in /etc/shadow. Normally an active user account will have one line in /etc/shadow that will look like:

user:$1$eFd7EIOg$EeCk6XgKktWSUgi2pGUpk.:13852:0:99999:7:::

where the second field is the encrypted password. Note: Fields seperated by :

If we replace the password with “*” or “!” this will make the account unusable, and that means the user will not able to login on system any more:

user:*:13852:0:99999:7:::

This method has the disadvantage that the user password will be lost (unless saved somewhere, etc.) in the case we will want to re-enable it again later. From this point of view a much better method is to use the passwd command to lock the account:

passwd -l

and the output of the successful change will be “Password changed.”. This actually just changes the shadow file and adds “!” in front of the user password:

user:!$1$eFd7EIOg$EeCk6XgKktWSUgi2pGUpk.:13852:0:99999:7:::

Of course we could do this manually ourselves also if we want but its good to do through commands. There is a chance of human error if you try and edit shadow file by yourself.

If you will ever need to re-enable the account just unlock it:

passwd -u

or just remove manually the “!” character from the user’s password line in /etc/shadow.

Of course if you don’t need all this stuff and you just want to permanently remove the user just run:

userdel

this will keep user’s old files (home directory, mails, etc.) or to delete all his files on the system:

userdel -r

just be careful what is the home of the user before running this command as personally I have seen someone do this and erasing all the system… the user had set as home “/” .

Hope this helps.

How to search file(s) in linux / unix?

find is very famous and regularly used command to find files in the Linux/UNIX filesystem based on various different conditions. Let us review some practical examples of find command. All system administrators love this command, sometimes a life saver..
Syntax:

find [pathnames] [conditions]

How to find files containing a particular / specific word in its name?
The following command looks for all the files under /etc directory with cron
in the filename.

# find /etc -name "*cron*"

How to find all the files greater than certain size?
The following command will list all the files in the system greater than
10MB.

# find / -type f -size +10M

How to find files that are not modified in the last x number of days?
The following command will list all the files that were modified more than 30
days ago under the current directory.

# find . -mtime +30

How to find files that are modified in the last x number of days?
The following command will list all the files that were modified in the last
five days under the current directory.

# find . –mtime -5

How to delete all the archive files with extension *.tar.gz and greater than 50MB?
Please be careful while executing the following command as you don’t want
to delete the files by mistake. The best practice is to execute the same
command with ls –l to make sure you know which files will get deleted when
you execute the command with rm.

# find / -type f -name *.tar.gz -size +50M -exec ls -l {} \;
# find / -type f -name *.tar.gz -size +50M -exec rm -f {} \;

How to archive all the files that are not modified in the last x
number of days?

The following command finds all the files not modified in the last 30 days
under /home/nilesh directory and creates an archive files under /tmp in the
format of ddmmyyyy_archive.tar.

# find /home/nilesh -type f -mtime +30 | xargs tar -cvf
/tmp/`date '+%d%m%Y'_archive.tar`

On a side note, you can perform lot of file related activities (including finding
files) us

How to change MySQL root password? mySQL-server

How do I change MySQL root password under Linux, Debian, centos, fedora, suse, mandrake, linux-mint, Ubuntu, FreeBSD, OpenBSD and UNIX like operating system over ssh / telnet session?
Setting up mysql password is one of the essential tasks. By default root user is MySQL admin account. Please note that the Linux / UNIX login root account for your operating system and MySQL root are different. They are separate and nothing to do with each other (indeed some admin removes root account and setup admin as mysql super user).

mysqladmin command to change root password

If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. To setup root password for first time, use mysqladmin command at shell prompt as follows:

$ mysqladmin -u root password NEWPASSWORD

However, if you want to change (or update) a root password, then you need to use following command

$ mysqladmin -u root -p’oldpassword’ password newpass

For example, If old password is abc, and set new password to 123456, enter:

$ mysqladmin -u root -p’abc’ password ’123456′

Change MySQL password for other user
To change a normal user password you need to type (let us assume you would like to change password for nilesh):

$ mysqladmin -u nilesh -p oldpassword password newpass

Changing MySQL root user password using MySQL sql command
This is another method. MySQL stores username and passwords in user table inside MySQL database. You can directly update password using the following method to update or change password for user nilesh:

1) Login to mysql server, type following command at shell prompt:

$ mysql -u root -p

2) Use mysql database (type command at mysql> prompt):

mysql> use mysql;

3) Change password for user nilesh:

mysql> update user set password=PASSWORD("NEWPASSWORD") where User='nilesh';

4) Reload privileges:

mysql> flush privileges;
mysql> quit

This method you need to use while using PHP or Perl scripting.

How to Increase max_connections in mysql without restarting mysqld service? global-variable

By default in mysql database server max_connections is set to 100. This value indicates how many maximum concurrent connections mysql server can handle. If mysql reaches to it maximum (max) limit then you can see errors like “too many connections“. I assume that you have enough hardware resources (Mainly RAM )to handle more connections, here with this article I will share a TIP to increase max_connections in mysql.

As we know my.cnf is default configuration file for mysqld service and by default it is located in /etc directory unless and until you have changed it.

To find out how many max_connections are allowed currently on your mysql server use following command from mysql prompt.

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
| 100                |
+-------------------+
1 row in set (0.00 sec)

max_connections is a GLOBAL variable. we can increase it on the fly without restarting mysqld service.
To do so use following command.

mysql> set global max_connections = 200;
Query OK, 0 rows affected (0.00 sec)

Now, If you check again you will see that limit of max_connections is increased.

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
| 200               |
+-------------------+
1 row in set (0.00 sec)

Note:
It is important that you edit your /etc/my.cnf to add max_connections = 200 otherwise when you restart mysqld service in future, It will complain again after it hits the old max_connections limit.
Mysql server is throwing “too many connections” error ?
How to increase number of concurrent mysql connections without restarting mysqld service?
errrr… mysql-server not allowing any more connections ?
Quick how to on increasing max_connections in mysql. restart not required?

 

How to find File-Directory creation date ?

$stat <file name> command basically displays file or file-system status. stat command is used to find Access , Modify, Change date and time for any file or directory in Unix like operating systems. e.g

$stat /root/install.log
File: `/root/install.log'
Size: 17154           Blocks: 48         IO Block: 4096   regular file
Device: fd00h/64768d    Inode: 1177346     Links: 1
Access: (0644/-rw-r--r--)  Uid: (    0/    root)   Gid: (    0/    root)
Access: 2011-09-30 16:24:39.000000000 +0100
Modify: 2011-09-30 16:28:59.000000000 +0100
Change: 2011-09-30 16:29:04.000000000 +0100


As show in above example, It also displays owner and group associated with particular file. permissions on particular file. etc
There is no creation date available as far as I know unless you log the file creation date in a script. You can use the stat command as mentioned above to get the time stamp for last access, last change and last modification time.

$stat --version
stat (GNU coreutils) 5.97
Copyright (C) 2006 Free Software Foundation, Inc.
This is free software.  You may redistribute copies of it under the terms of
the GNU General Public License <http://www.gnu.org/licenses/gpl.html>.
There is NO WARRANTY, to the extent permitted by law.

Written by Michael Meskes.

In linux, centos, fedora, redhat, suse, ubuntu, mint, debian, mandrake, caldera, yolinux, gnulinux

How to find directory last change / modification date and time?
How to find file / filesystem last change / modification date and time?
How to find last “access” date and time for file / directory?

As we are talking about time stamp (access, change, modify) for files / directories in linux, we much consider talking about “touch” command.
“touch” [options] <file name(s)>  :
Mainly touch command is used to change time stamp for files. If file does not exist then touch command creates file with current time stamp. e.g

$touch file1 file2 file3

will create 3 files in your present working directory with current time stamp.
Several of touch’s options are specifically designed to allow the user to change the timestamps for files. For example, the -a option changes only the access time, while the -m option changes only the modification time. The use of both of these options together changes both the access and modification times to the current time, for example:

$touch -am file11

The -r (i.e., reference) option followed directly by a space and then by a file name tells touch to use that file’s time stamps instead of current time. For example, the following would tell it to use the times of file4 for file5:

$touch -r myfile yourfile

The -B option modifies the timestamps by going back the specified number of seconds, and the -F option modifies the time by going forward the specified number of seconds. For example, the following command would make file7 30 seconds older than file6.

$touch -r myfile -B 30 yourfile

Importance of touch command.
Ah, beware before you use touch command on production system.
How to change times tamp using touch command?

What is PHP? server-side-scripting

What is PHP? <?php … ?>

PHP (Hypertext Preprocessor) is a widely-used general-purpose scripting language that is especially suited for Web development and can be embedded into HTML. PHP is a scripting language designed to fill the gap between SSI (Server Side Includes) and Perl, intended largely for the web environment. PHP has gained quite a following in recent times, and it is one of the forerunners in the Open Source software movement. Its popularity derives from its C-like syntax, and its simplicity. PHP is currently divided into two major versions: PHP 4 and PHP 5, although PHP 4 is deprecated and is no longer developed or supplied with critical bug fixes. PHP 6 is currently under development.

PHP started out as a small open source project that evolved as more and more people found out how useful it was. Rasmus Lerdorf unleashed the first version of PHP way back in 1994.

  • PHP is a server side scripting language that is embedded in HTML. It is used to manage dynamic content, databases, session tracking, even build entire e-commerce sites.
  • It is integrated with a number of popular databases, including MySQL, PostgreSQL, Oracle, Sybase, Informix, and Microsoft SQL Server.
  • PHP is pleasingly zippy in its execution, especially when compiled as an Apache module on the Unix side. The MySQL server, once started, executes even very complex queries with huge result sets in record-setting time.
  • PHP supports a large number of major protocols such as POP3, IMAP, and LDAP.

Common uses of PHP:

  • Access control on websites / web applications using php. you can restrict users to access some pages of your website.
  • PHP performs system functions, i.e. from files on a system it can create, open, read, write, and close them.
  • PHP can handle forms, i.e. gather data from files, save data to a file, through email you can send data, return data to the user.
  • You add, delete, modify elements within your database through PHP.
  • Access cookies variables and set cookies.
  • It can encrypt data.

“Hello Web World” Script in PHP:

To get a taste of PHP, first start with simple PHP scripts. Since “Hello, Web World!” is an essential example, first we will create a friendly little “Hello, World!” script.

As mentioned earlier, PHP is embedded in HTML. That means that in amongst your normal HTML (or XHTML if you’re cutting-edge) you’ll have PHP statements like this:

<html>
<head>
<title>Hello Web World</title>
<body>
    <?php echo "Hello, Web World!";?>
</body>
</html>

It will produce following result:

Hello, Web World!

If you examine the HTML output of the above example, you’ll notice that the PHP code is not present in the file sent from the server to your Web browser. All of the PHP present in the Web page is processed and stripped from the page; the only thing returned to the client from the Web server is pure HTML output.

All PHP code must be included inside one of the three special markup tags ate are recognised by the PHP Parser.

<?php PHP code goes here ?>

<?    PHP code goes here ?>

<script language="php"> PHP code goes here </script>

Most common tag is the <?php…?> and we will also use same tag in our tutorial.

From the next chapter we will start with PHP Environment Setup on your machine and then we will dig out almost all concepts related to PHP to make you comfortable with the PHP language.
Is PHP a buzz word ?
Why php is so popular ?
PHP uh.. never heard of it ? what is php?
php a hypertext preprocessor?
A powerful server-side-scripting language, PHP.

What is sed? linux/unix

sed is stream editor for filtering and transforming text.
Sed  is a stream editor.  A stream editor is used to perform basic text transformations on an input stream (a file or input from  a  pipeline). While  in  some  ways similar to an editor which permits scripted edits (such as ed), sed works by making only one pass over the input(s),  and is consequently more efficient.  But it is sed’s ability to filter text in a pipeline which particularly distinguishes it from other  types  of  editors.

sed is very useful when you are making same changes on multiple files or multiples times in same file. Imagine using regular text editor for such scenario. you will feel rest-less when you do such changes manually. once you start using sed, those changes will be done easily.. quickly..How to find version of sed on your system ?

$sed --version
GNU sed version 4.2.1
Copyright (C) 2009 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.
$man sed
--- Read this man page for more details.

Example 1 :

$sed 's/wrong pattern/correct pattern/' original.file

substitutes in EVERY line of a file called wrong.file the first and only the first occurrence of “wrong pattern” by “good pattern” and displays the corrected text at standard output. Check the output until everything is ok, then redirect the output (you can not overwrite the old file)

$sed 's/wrong pattern/correct pattern/' original.file > updated.file

Another way to redirect the output:

$cat wrong_file.txt | sed 's/wrong pattern/good pattern/g' > correct_file.txt

If you want to replace the “wrong pattern” globally (whole file), e.g. several times a line, then enter

$sed 's/wrong pattern/good pattern/g' wrong.file > correct.file

To replace the “wrong pattern” only once at the very first occurrence we need to adress the substitution. Either we know at which line the “wrong pattern” is located, i.e. line 16, then type

$sed '16s/wrong pattern/good pattern/' original.file > correct_new.file

If we don’t know the position of the “wrong pattern”, we need to give an address range where sed should do the substitution, i.e.

$sed '1,16s/wrong pattern/correct pattern/' original.file > correct_new.file

but in an general case we won’t know if the “wrong pattern” occurs exactly once in the first 20 lines… But we can define the range with the pattern itself, so we expect

$sed '1,/wrong pattern/s/wrong pattern/correct pattern/' correct_new.file

to do the job: start at line 1 and substitute until the first occurrence is found. Unfortunately this doesn’t work IF the “wrong pattern” appears in the first AND second line! Because the 1 activates the first line the second address of the range is at least the second line. In this case the “wrong pattern” will be substituted in both lines. What we need is

$sed '0,/wrong pattern/s/wrong pattern/good pattern/' wrong.file

to prevent the activation of the second line where the “wrong pattern”
could appear again.
How to perform different append operations using sed?
Append content of file year.txt at line 3 of data.txt.

$sed '3 r file1.txt' file2.txt > file1_2.txt

Append after each line with the string “pattern” a line with text “new”.

$sed '/pattern/anew' original.file

Append after each line a new line with text “new”.

$sed 'a/new' original.file

 Why “sed” is system administrators friend ?
How do is edit multiple files in 1 go ?
Make your edit/update/delete/replace easier with sed?
Sed “The King” of stream / patter edditing / formating and transforming

Getting started with PostgreSQL. ubuntu debian mint

PostgreSQL database server installation and configuration in debian / ubuntu

Introduction :
PostgreSQL is a fully featured object-relational database management system. It supports a large part of the SQL standard and is designed to be extensible by users in many aspects. Some of the features are: ACID transactions, foreign keys, views, sequences, subqueries, triggers, user-defined types and functions, outer joins, multiversion concurrency control. Graphical user interfaces and bindings for many programming languages
are available as well.

Postgresql Database Installation in Debian

If you want to install the PostgreSQL base system as well as a textual clientrun the following command from your shell

apt-get update
apt-get install postgresql-8.3 postgresql-client-8.3 postgresql-contrib-8.3 postgresql-common

This will install a lot of package dependencies as well. The version of PostgreSQL installed should be 8.3.7 on a debian lenny (5.0.1 in my case) system. If you want to check the official documentation click here. http://www.postgresql.org/docs/8.3/interactive/index.html

NOTE: After installation if you will try to start postgresql then it may not start if you have not created database cluster.
it is simple to initialize database cluster [It is being used to store databases].

#pg_createcluster 8.3 main –start That will display some information on your screen. once it completes successfully, you will able to login to postgresql database using default way to login as mentioned below.

The only (default) way to log into your newly created database system is to log as root, then to log as user postgres, by issuing

#su
#su postgres
#psql template1
#template1=# \q
#exit

However, you would probably like to be able to login as your unix user, or any other user you choose. There are several ways to set up these accesses, so let’s decomposeAlowing local users to login is done by editing the /etc/postgresql/pg_hba.conf file (a complete documentation of this file is available here). There, you have to retrieve a series of lines like the following ones

local all postgres ident sameuser
#
# All other connections by UNIX sockets
local all all ident sameuser
#
# All IPv4 connections from localhost
host all all 127.0.0.1 255.255.255.255 md5
# All IPv6 localhost connections
host all all ::1 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff ident sameuser
host all all ::ffff:127.0.0.1/128 ident sameuser
#
# reject all other connection attempts
host all all 0.0.0.0 0.0.0.0 reject

This file has limited accesses but we will come back here later.Now what we have told PostgreSQL is that all users on the local machine should be able to access all databases, using their UNIX login and passwords. You need to restart your database to take new changes effect

Restart postgresql using the following command

#/etc/init.d/postgresql restart

Now the problem is that you have authorized users to log into the database, but they will only be able to log in if they also exist in the database. So you need to create accounts with their usernames. This is done by issuing the createuser command as user postgres as follows

#su postgres
#createuser firstuser
#exit

The createuser command will ask you if the user should be able to create databases or create new users. Answer truly.

Now you have a new user in the database, and you should be able to connect as this user (let’s call him firstuser) like this:

#su – firstuser
#firstuser@localhost:~$ psql -W template1
#template1=# \q
#exit

The -W parameter is to ask for a password prompt. You should enter your system password when asked.
So that’s it, we got the first part of this installation running. Now let’s prepare this for the web.

If some of your web scripts are going to need a connection to the database, you will need to grant them some access via a new user, reserved for this web usage.

Let’s first create this new user (as user postgres). Let’s call him webuser.

#su postgres
#createuser webuser
#exit

Now you want to give him access to one or several databases (let’s say you will let him access the databases which name is web). To do this, you need to edit once more the pg_hba.conf file. Retrieve the first line below and write the second one just beneath the first.

host all all 127.0.0.1 255.255.255.255 md5
host web webuser 127.0.0.1 255.255.255.255 md5

Would you like to give this user access from any computer on the sub-network 192.168.0.xxx, you would have to add the following line

host web webuser 192.168.1.1 255.255.255.0 md5

You have to grant him access to the host as it will probably use the TCP/IP protocol to get a connection to the database, and that’s what host is for. But as you have given him an md5 authentication type, you need to give him a password as well. In order to do this, you need to connect to the database and issue a special command, all as postgres user:

#su postgres
#postgres@localhost:~$ psql template1
#template1=# alter user webuser password ‘some_password’;
#ALTER USER
#template1=# \q
#exit

Now the user should have a nice ‘some_password’ password that you can use after restarting PostgreSQL to make it remember your changes to pg_hba.conf.

Restart postgresql using the following command

#/etc/init.d/postgresql restart

And you should be able to create his own database if you gave him the permission

#createdb -h localhost -U webuser -W web

And connect to this newly-created database using the following command

#psql -h localhost -U webuser -W web

As you might have noticed, we use -h localhost here. This is to force the PostgreSQL client to use TCP/IP instead of UNIX sockets. If you don’t want is, you need to add a new line into the “local” category of pg_hba.conf for the user webuser to connect via sockets.The line would be something like

local web webuser md5

Using SQL

The first important command you might want to know is

#psql -h localhost -U webuser -W -l

This will give you a list of available databases.

Now connect to the PostgreSQL database using the psql client, and let’s create a small table, just after a few tests

web=> SELECT version();
web=> SELECT current_date;
web=> SELECT 2+2;
web=> \h
web=> \d
web=> \dt
web=> CREATE TABLE weather(
web=> city varchar(80),
web=> temp_lo int, — low temperature
web=> temp_hi int, — high temperature
web=> prcp real, — precipitation
web=> date date
web=> );
web=> INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
web=> VALUES (‘London’, 12, 13, 0.0, ’2005-05-29′);
web=> INSERT INTO weather (date, city, temp_hi, temp_lo)
web=> VALUES (’2005-05-19′, ‘Miltons’, 10, 25);
web=> SELECT city, temp_lo as ‘Lowest Temperature’, temp_hi as ‘Highest Temperature’ FROM weather;
web=> SELECT city FROM weather WHERE temp_lo < 30;
web=> SELECT max(temp_lo) FROM weather;
web=> UPDATE weather SET temp_lo = 18 WHERE city = ‘London’;
web=> SELECT * FROM weather;
web=> DELETE FROM weather WHERE prcp IS NULL;
web=> SELECT * FROM weather;
web=> \q

Now you’ve had a nice overview of several SQL statements and how to use them in PostgreSQL.

Later, you might want to create a table with a numerical sequence as an index. This link will help you get through this step which I find counter-intuitive and user-unfriendly at will. You might as well know of a shortcut. If you want to create a default auto-incremental key that will be used by your table, you need to define (for every table) a SEQUENCE. This id done, for the weather table above, like this (with the help of user comments here):

web=> CREATE SEQUENCE my_table_pk_sequence;
web=> CREATE TABLE weather(
web=> id int not null default nextval(‘my_table_pk_sequence’) PRIMARY KEY,
web=> city varchar(80),
web=> temp_lo int, — low temperature
web=> temp_hi int, — high temperature
web=> prcp real, — precipitation
web=> date date
web=> );

And then insert elements as before, ignoring the id field

web=> INSERT INTO weather (date, city, temp_hi, temp_lo)
web=> VALUES (’2009-06-20′, ‘London’, 14, 27);
web=> SELECT * FROM weather;

This should have inserted an auto-incremented id.