PostgreSQL basics 2: SQL

In the previous post, , we installed PostgreSQL on our openSUSE 12.2 system and did the absolute minimum necessary to get it running. We also learned how to run psql, the "terminal-based front-end to PostgreSQL".

Create a table, get list of tables in database:

# su - postgres
postgres@pantograf:~> psql
psql (9.1.6)
Type "help" for help.

postgres=# \d
No relations found.
postgres=# \h create table
postgres=# CREATE TABLE foobar (foo integer);
CREATE TABLE
postgres=# INSERT INTO foobar VALUES (257);
INSERT 0 1
postgres=# \d
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | foobar | table | postgres
(1 row)

postgres=# \d foobar
    Table "public.foobar"
 Column |  Type   | Modifiers 
--------+---------+-----------
 foo    | integer | 

postgres=#

Put things back the way they were:

postgres=# drop table foobar;
DROP TABLE
postgres=# \d
No relations found.
postgres=#

Create a new database and connect to it:

postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".
testdb=#

PostgreSQL recognizes certain constants (strings, bit strings, and numbers) as "implicitly typed". In the previous example, 257 was recognized as an integer. In this example, we introduce a string constant. Note the single quotes:

testdb=# CREATE TABLE foobar (foo text);
CREATE TABLE
testdb=# \d foobar
   Table "public.foobar"
 Column | Type | Modifiers 
--------+------+-----------
 foo    | text | 

testdb=# INSERT INTO foobar VALUES ("Carpe diem");
ERROR:  column "Carpe diem" does not exist
LINE 1: INSERT INTO foobar VALUES ("Carpe diem");
              
testdb=# INSERT INTO foobar VALUES ('Carpe diem');
INSERT 0 1
testdb=# SELECT * FROM foobar;
    foo     
------------
 Carpe diem
(1 row)

testdb=#

More information on "character types" (i.e. strings) in PostgreSQL:
PostgreSQL 9.1 manual, section 8.3. Character types

Including a single-quote in a string constant, Example No. 1 - obviously wrong but note how psql responds:

testdb=# INSERT INTO foobar VALUES ('Now's the time');
testdb'# ^C
testdb=# 

Including a single-quote in a string constant, Example No. 2 - escaping with backslash doesn't work either (!):

testdb=# INSERT INTO foobar VALUES ('Now\'s the time');
testdb'# ^C
testdb=#

N.B. Apparently, this behavior is specific to newer versions of PostgreSQL. Section 4.1.2.1. (String Constants) of the PostgreSQL 9.1 manual makes no mention of backslashing the single-quote character. A full explanation can be found in the PostgreSQL 9.1 manual, section 18.13. Version and Platform Compatibility:

backslash_quote (enum)

    This controls whether a quote mark can be represented by \' in a 
    string literal. The preferred, SQL-standard way to represent a quote 
    mark is by doubling it ('') but PostgreSQL has historically also 
    accepted \'. However, use of \' creates security risks because in some 
    client character set encodings, there are multibyte characters in which 
    the last byte is numerically equivalent to ASCII \. If client-side code 
    does escaping incorrectly then a SQL-injection attack is possible. This 
    risk can be prevented by making the server reject queries in which a 
    quote mark appears to be escaped by a backslash.

Including a single-quote in a string constant, The Right Way: two consecutive single-quotes:

testdb=# INSERT INTO foobar VALUES ('Now''s the time');
INSERT 0 1
testdb=# SELECT * FROM foobar WHERE foo = 'Now''s the time';
      foo       
----------------
 Now's the time
(1 row)

The LIKE keyword is used for pattern matching. '%' matches any number of any character, while '_' matches any single character:

testdb=# SELECT * FROM foobar WHERE foo LIKE 'Now''s%';
      foo       
----------------
 Now's the time
(1 row)

testdb=# SELECT * FROM foobar WHERE foo LIKE 'Now_s the time';
      foo       
----------------
 Now's the time
(1 row)

testdb=#

More about pattern matching here:
PostgreSQL 9.1 manual, section 9.7. Pattern Matching

Anyway, the PostgreSQL 9.1 manual does a much better job at this than I could ever do, so for further instruction I refer the kind reader there.

A very quick and useful way to search the PostgreSQL 9.1 manual. Type the following into Google Search:

YOUR_SEARCH_TERMS site:http://www.postgresql.org/docs/9.1