PostgreSQL

In the 1970's, the Ingres database project began at Berkeley. Ingres originally stood for Interactive graphics retrieval system, though shortly thereafter the "graphics" part became much less important.

After Ingres (which does still exist) came Postgres, for "post-ingres".

Later, to emphasize its support for SQL, Postgres became PostgreSQL.

As it is virtually impossible to pronounce PostgreSQL using the "sequel" form for SQL; most people pronounce it "postgres".

A decade ago, MySQL was the "fast one" and Postgres was the one that dutifully implemented the SQL standard. Since 2010, MySQL has become a little more "mainstream" (though it is still missing the intersection operator), but Postgres has become a lot faster. Postgres is also much more similar to Oracle (which is a little ironic because Oracle now owns MySQL, though the usual assumption is that Oracle doesn't want MySQL to compete with the family jewels).

Postgres can be found at postgresql.org.

After you install the postgres server software, as with pretty much any database, you need to create a user, and a database for that user. The postgres account is the only account installed by default. 

Here's what I had to do on linux (similar to macs; Windows is below). You will need the -p 5433 option, if your PostgreSQL installation uses that alternative port.

1. From the shell:

    bash> createdb -U postgres pld

You will need to enter the postgres password.

2. Run the psql command for user postgres. The "bash>" prompt below is the shell prompt (as user postgres); the "post=> " prompt is the psql SQL prompt.

bash> psql -U postgres
postgres=# create user pld with password 'zanzibar';    

The system response here should be CREATE ROLE. If you don't get that, something went wrong (make sure you typed the final semicolon, though).

You now need to give permissions to your new user:

postgres=# grant all privileges on database pld to pld;
postgres=# grant all on schema public to pld;
postgres=# alter database pld owner to pld;

Then, as user pld, the command "psql" works. If you created several databases above, eg "pldoffice", then the command to connect to postgres using the pldoffice database would be psql pldoffice. What Postgres is doing here is noticing that my linux username is "pld", and assumes (unless I request otherwise) that I will be Postgres user "pld". Postgres also assumes that system user "pld" is authorized to use the Postgres account with the same name; this is called peer authentication.

Note that I did not actually give myself the right to create new databases (though I don't need it).

When I first installed Postgres on linux, it set itself up to accept connections on port 5435, instead of the standard 5432. This was insufferably annoying as I had to specify -p 5435 in every command. I eventually discovered that I could change the port by setting port = 5432 in the postgresql.conf file, in (under linux) /etc/postgresql/9.5/main. The port number is more of an issue, though, when installing under MacOS.

Installing on Windows

Go to postgresql.org, and click the Download button, and then the Windows button. Pick the release you want (16, the most current as of 2024, should be the best choice.) Click the Download link.

Select "Download the ... installer". You can use either the installer from EnterpriseDB or the "graphical" installer from BigSQL. I used the former here. Be sure to choose 32-bit or 64-bit to match your system (you can check your system with Control Panel → System; it should be 64 bit).

After downloading (~162 MB in 2018), run the installer. It starts with "Welcome to the PostgreSQL Setup Wizard". At some point, you may see an alert telling you it is installing "MS Visual C++ Redistributable".

Eventually you get to the PostgreSQL installer itself. The default locations for everything should be fine. You will be asked

    Please provide a password for the database superuser (postgres).

Don't forget the password you provide here! Though for classroom use it doesn't have to be very secure.

You will also be asked a couple other questions; the default answers should be fine (though if the default network port is not 5432, that probably means you have a Mac, and the default will probably then be 5433). The installation begins when you click "next" on the screen that tells you Postgres is ready to install.

You can skip the offer to launch Stack Builder, though it's fine if you want to try it. It won't be used in Comp 305/405 though.

Postgres "peer" authentication means that if you have logged into your computer with username, say, pld, and you want to get into your PostgreSQL account with the same name, postgres just lets you, on the theory that you've already provided your password to get logged in. Windows sometimes has trouble with "peer" authentication, as in the Linux discussion above, because system usernames under Windows tend to be unwieldy. In particular they often have spaces, which do not work well as Postgres usernames. If your system username and postgres username do not match, you cannot use peer authentication. Just go ahead and use password authentication. The -h localhost option to the psql usually forces password authentication. If the port is not 5432, you will also need to add -p 543x to every psql and createdb command, where 543x is the port used by your installation.

As of 2018, it looks like the Windows installation has abandoned "peer" authentication, which is a good thing. (At least there were no "peer" authentication methods listed in the authentication-configuration file pg_hba.conf.)

Installing on a Mac

You can also use the Postgres.app, which is convenient but which does not run "continuously". You'll have to verify that the app has started Postgres whenever you use it (normally this is not at all difficult).

You are asked to provide a database superuser password; that is, the password to the "postgres" account. Don't forget the password you supplied! For class use it does not have to be especially secure.

Check to be sure that "PostgreSQL Server" and "Command Line Tools" are selected for installation. The other tools are optional.

The README file accompanying the installation suggests increasing the shared-memory allotment. You should definitely do that if you're running a production service, but I did not.

Note that Apple may already have a Postgres installation on your mac, as part of the mac system. You can verify this by entering the following command in a terminal window and seeing if there's any significant output: ps axuww | grep postgres. It's fine to have two separate, unrelated installations, but if this is the case then your new installation will definitely have a new port number other than 5432 (it will probably be 5433). You need to use this new port in every command!

Postgres Accounts

You need to create your own non-superuser (non-postgres) account, and then create a database. It's most convenient to have the database account name be the same name as your computer login name, though I'm never sure about spaces, and on Windows this is sometimes pointless. To find out your windows login name, run psql with no options and it will tell you, after prompting for a password (even if it won't actually "connect").

On the Loyola computer I tested this on in 2020, I was "pdordal", my Loyola ID. I also tried creating a username/databasename of "pld"; the only difference was that with Windows username "pdordal" and Postgres username "pld" I had to use the -U pld option to psql.

• type the full path name: "c:\program files\postgresql\16\bin\psql" --username=postgres -p 5433 (with quotes. The 16 here is the version number).
• cd to the appropriate bin directory, and run commands from there (on macs you'll need "./psql" rather than "psql"
• On Windows, type "psql" into the search box. You will be prompted for a couple things (like the port number), and then you should connect.
• Likewise on Macs, but there may be more than one psql command; you want the version-16 one.

Whichever you do, here are the steps. The option "--username=postgres" (or "-U postgres", without the equals sign) means you will be running the command as the PostgreSQL superuser, and will need to supply the initial password above.

If you got a network port number other than 5432 during the installation,

    (a) You probably have a Mac
    (b) You will need to use that new port number in every psql and createdb command:

        psql --username=postgres -p 5433

What is going on is that you have two entirely independent installations of Postgres, with separate data. One is reachable via port 5432 and one via 5433. They don't talk to one another; they don't even know about one another.

In the example below the account name and database name will be "mydb". You can use whatever name you want, and should probably use some form of your username, first name or initials. On Windows I've tried both "pdordal" and "pld". On Linux and Macs, it is popular to have your Postgres account name match your login name.

The first step is actually to run createdb, rather than psql. Again using the full path name, as above, or going to the bin directory, or setting up PATH. You will also need to add -p 5433 if your port is 5433; I will show this below, but you can leave it out if it's 5432. The createdb command is typed at the Windows command shell, not within Postgres:

1. createdb --username=postgres -p 5433 mydb    (Supply the postgres password. This might take several seconds.)

This creates a database for userid "mydb". On Windows in 2024, typing "createdb" in the Windows search box failed; I had to open up a cmd window and navigate to \Program Files\PostgreSQL\16\bin.

Next, create the matching account within PostgreSQL, using the postgres administrative account. (Here, on Windows, you can just type "psql" into the Windows search box; you'll be prompted for the username and port, and maybe host (use localhost)).

2. psql --username=postgres -p 5433      (supply the postgres password)

postgres=# create user mydb with password 'mypassword';    (choose your own password for mypassword; the response should be CREATE ROLE)
postgres=# grant all privileges on database mydb to mydb;
postgres=# grant all on schema public to mydb;
postgres=# alter database mydb owner to mydb;   

Finally, you can log in as you:

3. psql --username=mydb -p 5433        ;; the "--username mydb" isn't necessary if mydb = your windows account name; supply mypassword above

On Windows, if you're starting psql from the Windows search box, enter your user name at the appropriate prompt.

Here are a few quick commands:

psql=> \d    ;; \d gives  list of tables
psql=> create table demo (
        name varchar(200),
        idnum integer primary key
    );
psql=> insert into demo values('alice', 37);
psql=> insert into demo values('bob',   61);
psql=> select * from demo;

Setting your PATH

Login Troubles

Command Line

    psql -U username -d databasename -c 'select * from employee'

You may have to add -W (or --password) to get psql to prompt for a password, if applicable (this is usually not necessary if your postgres username matches your operating-system username). On unix-like systems (linux and macs), the command can be either in single quotes, as above, or double quotes. The latter form allows shell variable expansion:

for table in employee works_on project department
do
    echo $table
    psql -U pld  -c "select count(*) from $table"
done

    select fname, lname from employee where lname = 'wong';

you will get nothing (printed as "(0 rows)"). The correct query is

    select fname, lname from employee where lname = 'Wong';

One way to resolve this is with

    select fname, lname from employee where lower(lname) = 'wong';

However, applying lower() to a primary-key attribute (or any indexed attribute) will mean that the attribute's index will likely fail to be used. Another alternative is to use ilike instead of =:

    select fname, lname from employee where lname ilike 'wong';

Some postgres commands

Your psql prompt should show you the current database.

SHOW ALL -- shows status variables only

\d                   same as MySQL show tables;

\d employee    similar to as MySQL describe employee; gives all indexes and FK constraints with names

\d [NAME]      describe table, index, sequence, or view
\d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
                 list tables/indexes/sequences/views/system tables
\da [PATTERN]  list aggregate functions
\db [PATTERN]  list tablespaces (add "+" for more detail)
\dc [PATTERN]  list conversions
\dC            list casts
\dd [PATTERN]  show comment for object
\dD [PATTERN]  list domains
\df [PATTERN]  list functions (add "+" for more detail)
\dg [PATTERN]  list groups
\dn [PATTERN]  list schemas (add "+" for more detail); \dnS includes system schemas
\do [NAME]     list operators
\dl            list large objects, same as \lo_list
\dp [PATTERN]  list table, view, and sequence access privileges
\dt [PATTERN]    list tables in a schema; eg \dt pg_catalog.*
\dT [PATTERN]  list data types (add "+" for more detail)
\du [PATTERN]  list users
\l             list all databases (add "+" for more detail)
\z [PATTERN]   list table, view, and sequence access privileges (same as \dp)

If you start psql with the -E option, postgres will tell you the underlying query it uses for each of the above commands.

Here's another way to list your tables:

select table_name from information_schema.tables where table_schema='public' and table_type='BASE TABLE';

Intersect example

This works, under postgres. Here is the example (the select attributes must match in the two queries being intersected):

(select e.fname, e.lname from employee e, works_on w where e.ssn = w.essn and w.pno = 2)
intersect
(select e.fname, e.lname from employee e, works_on w where e.ssn = w.essn and w.pno = 3);

Postgres file storage

Your postgres files are in a designated directory (at least if you are using the standard "tablespace"). On my linux installation it is /var/lib/postgresql/9.5/main/base/. There is a directory for each database, and, within these directories, a file for each table. Databases and tables are usually named using their so-called "oid" numbers. My "pld" database on my laptop (2015) has oid 16385. I can find this with the oid2name command, or with the query

    select oid, datname from pg_database;

To find the oid of a table, say 'employee', you can use

    select pg_relation_filepath('employee');            -- note quotation marks!

or

select tableoid from employee limit 1;

The first returns

    base/16385/16386

The second returns 16386. For my password database on ulam.cs.luc.edu (a different installation from above!), the first is  base/16385/16387. The file size itself is just under half a gigabyte.

Very large tables are split over multiple files (by default 1 GB per file). Tables with very wide columns (blobs and long strings, for example) have an associated "TOAST" table; the primary table file then in effect has pointers to another location where the large field is stored. TOAST stands for The Oversized-Attribute Storage Technique.

Record layout

Postgres "tuples" are immutable. When a record needs to be updated, the old tuple is left in place, but marked as not current. The new tuple is created in a new location. This design decision does sometimes create some write bottlenecks, but we will address that later.

Each tuple has a system attribute called ctid, which is a pair of numbers that, informally, represents (block number, record number). Blocks are 8KB. What is Mr Wong's ctid?

select ctid, lname, ssn, salary from employee;

Now let's update Mr Wong's salary:

update employee set salary = salary+10 where lname = 'Wong';

What is Wong's ctid now? What happens if we repeat this a few times?

Actually, each block begins with an array of record pointers; the ctid record number points to one of these record pointers. That pointer then points to the actual block; this way, records within a block can be compacted without changing any ctids.

Postgres tuples also have xmin and xmax system attributes, reflecting the minimum transaction id that can view a record and (for deletions) the maximum. These play a role in concurrency control; we will come back to them later. Every transaction is numbered sequentially; the current transaction id is available via

select txid_current();

As soon as there are no remaining transactions in progress with txid <= xmax, that particular tuple can be deleted completely. This is done by the autovacuum process, which is sort of the garbage collector for Postgres.

Other system columns include cmin and cmax.

More at rachbelaid.com/introduction-to-postgres-physical-storage/,  interdb.jp/pg/, and, of course, postgresql.org/docs/9.6/static/storage.html.

Postgres schemas

As a general database term, a "schema" is a set of tables together with table definitions. The University and Company databases would be good examples of separate schemas, except I did not set them up that way. Postgres allows schemas -- as a kind of namespace -- to be first-class objects within the database.

If, as user postgres, I enter \l (list databases), I get these:

• dell
• pld
• usda
• postgres
• template0
• template1

The first three were created by me. The last three have no top-level tables! If I do the following,

    \c postgres
    \d

I get

    No relations found

Where are the tables for the postgres database? They are all within non-default schemas. These schemas can be listed with

    \dnS

(the S asks to include even the system schemas.) Here is a partial list:

• information_schema
• pg_catalog
• pg_temp_1
• pg_toast
• pg_toast_temp_1
• public

Databases are, in effect, divided into schemas. Every database has a schema named public (the last one above) which is (initially at least) the default schema for that database. Every database also has a schema pg_catalog; this particular schema is the same across all databases. Every database also has a schema information_schema as part of the SQL standard; this schema contains a more "standardized" (that is, non-Postgres-specific) version of the pg_catalog information.

Schemas can be thought of as directories within a database, where tables are actually stored, although you cannot have nested schemas. A table foo in schema bar can be thought of as having fully-qualified name bar.foo.

Schemas have owners. The "postgres" role owns the public schema, and all the system schemas. The owner of a table in a schema is not necessarily the same as the owner of the schema.

We can examine the tables in a schema with, eg

   \dt pg_catalog.*

We can the look in detail at the pg_database table with

    \d pg_catalog.pg_database
    \d pg_catalog.pg_index

We can read the table with

    select * from pg_catalog.pg_database;

Tables are described in the information_schema schema; for example we can run this:

select table_name from information_schema.tables where table_schema='public' and table_type='BASE TABLE';

But there is no table 'tables' in information_schema! (We can check this with \dt information_schema.*). It turns out that tables is a view. We can list all views with

    \dv information_schema.*

Or

\connect pld
select table_schema, table_name, table_type from information_schema.tables
where table_catalog='pld';

Why is this a view? Most of these views are of Postgres-specific tables (and Postgres-version-specific tables) in the pg_catalog schema; the information_schema views are more generic. The underlying tables are subject to change, but the view is not.

The information_schema schema is documented in http://www.postgresql.org/docs/current/static/information-schema.html.

Recall that if you run psql with the -E option, you get to see the underlying queries generated. This would be a good time to give that a try.

We can also run the following:

select table_name from information_schema.tables
where table_catalog='pld'
and table_schema='public'
and table_type = 'BASE TABLE';

This is like the previous query, but with more where clauses. It also has similarities to the listindexes example of the previous section.

The public schema is the default. We can run the following to find all tables in this schema:

SELECT * FROM information_schema.tables WHERE table_schema = 'public'

Alternatively, we can use \dt public.* However, this will only list tables in the current database.

To list all the tables in the current database, we can use \dt *.*

Schemas and organization

Here are a few options for organizing a database with schemas.

1. Use only the public schema. There may be multiple users and multiple separate table ownerships.

2. Each user can be given his or her own schema, corresponding to their username. Each user's tables are potentially visible to other users (if permissions allow it), but this visibility is not "obvious" (the other schema tables won't show in a \dt, for example). Users can choose to restrict access by other users, or not. (If each user gets his or her own database, then no sharing between users is possible at all.)

With this strategy, it might be appropriate to deny everyone access to the public schema, to force them to use their individual schemas.

3. Create one schema for each company "grouping", eg for each store or each project. Then most queries within one store's schema will refer only to that store, but it is possible to write queries that collect data from different stores. If we create a separate database for each store, on the other hand, we can not write queries that involve data from different stores.

Schema search path

To avoid having to type "fully qualified" schema.table names, one can define a schema search path.

show search_path;

If we create a new schema (in database "pld")

Postgres arrays

We can have arrays in postgres data fields; see http://www.postgresql.org/docs/9.5/static/arrays.html. This is often done as denormalization, though it definitely breaks the 1NF rule.

create table courses (name varchar, courses integer[]);

insert into courses values ('Dordal', '{ 305, 388, 417, 443 }');  -- note quote marks!
insert into courses values('Harrington', array[150, 170, 310]);

We can then access the array components using []:

select courses[2] from courses where name = 'Dordal';
update courses set courses[2]=488 where name='Dordal';

Searching is primitive: we can do this

select name from courses where 305 = any(courses);

but not this:

select name from courses where any(courses) = 305;

• wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.5
• www.postgresql.org/about/news/1636/

MySQL

Some notes on installing mysql

MySQL configuration

"Raw" installation

• install the MySQL executables
• create the initial system tables

The latter is done with a script mysql_install_db. It creates, among other things, the root account. Some options are possible, but rare.

Where and how are MySQL files stored?

• MyISAM: once upon a time the MySQL default
• memory: all tables are kept in RAM
• csv: comma-separated values, usually used only for data export
• Archive: non-indexed, used for archival data
• various cluster-oriented engines such as NDB/NDBCLUSTER and Federated
• Blackhole: very fast -- ahem -- write performance, but no persistence.

• course.ibd
• grade_report.ibd
• prerequisite.ibd
• section.ibd
• student.ibd

• create table foocopy ( /* exactly like foo */ ) data directory '/ssd/mysql/data'; -- same table structure as foo
• insert into foocopy select * from foo;                -- copy the data from foo to foocopy
• drop table foo;  
• rename table foocopy to foo;   

Alternatively, one can just move the original foo.ibd to the new location, and create a symlink from the old location to the new. This requires turning on symlink support, however; there are also some warnings in the MySQL manual about such symlink use.

MySQL data dictionary

MySQL Password hashes

Oracle

Compared to MySQL, Oracle has more proprietary extensions. A few are there more to lock you into Oracle than because they are a good idea. That said, Oracle does do well with big, transactional DBs.

Note that, with Oracle's purchase of Sun Microsystems, Oracle now owns MySQL. Some people think this is why they bought Sun. (Others think it was for Java.)

Oracle tends to support more features than MySQL. Transactions are one, though this is now beginning to appear in MySQL. Another is key constraints, and foreign key constraints in particular. However, consider the following:

• Application programs generally have to explicitly check for foreign key values, anyway; otherwise, it is difficult to respond naturally to a user error.
• Now that Oracle has acquired the innodb database engine for MySQL, foreign key constraints are now implemented whenever the DB administrator chooses the innodb engine (which is now the default).

• bandwidth to download 2+ GB
• disk space

Some notes on the Oracle Developer Days virtual machine

Demo of Oracle

Oracle administrative accounts

(See Oracle Database Administrator's Guide.)

Oracle Data Dictionary

• user_catalog
• user_tab_columns
• user_views
• user_constraints
• user_objects
• user_users
• ...

The user_catalog (or just cat) is a table of the current user's tables; additional information about these (mostly storage-related) is found in user_tables. The user_views table contains information about views (below); views are a sort of pseudo-table.

select * from cat;

An alias for cat is tab. The "official" way to get all tables is

select table_name from user_tables;
select object_name from user_objects where object_type = 'TABLE';

You can use describe employee to get information about the employee table. What is different from the MySQL version?

Earlier we looked at some information from user_constraints. I had: used this information to find the existing foreign-key constraints and re-create them with sensible names:

select table_name, constraint_name from user_constraints where constraint_type = 'R';
select table_name, constraint_name, constraint_type from user_constraints;

Oracle Schemas

Oracle Materialized Views