PostgreSQL newbie – how to create objects in a database

I’ve just finished installing PostgreSQL and pgadmin3 on Ubuntu. Using pgadmin, I’ve been able to create a new user called ‘test’ and also a new database with ‘test’ as the owner.
Now I’m trying to create tables using SQL statements… but I can’t seem to be able to figure out how to do that in pgAdmin. I don’t’ want to create each column individually using the GUI.
Is there any way I can run a SQL statement like:

create table test 
( 
  id serial primary key, 
  name varchar(64), 
  handler varchar(16), 
  desc varchar(255) 
);

I figured it out. I can create an empty table using the “new table..” menu item. Then I can edit the table once created by right clicking on it and choosing “scripts”.

You should have a button, in pgAdmin3, for opening a textbox where you can enter any sql code. Or, you may just use command line client like psql.

Best way is to use files, which can be version-controlled in git or hg.

Here’s my approach:

1. Create a schema_UP.sql file with the following content:

   begin;

   create table test

   (
   id serial primary key,

   name varchar,

   handler varchar,

   desc varchar

   );

   — Grant privileges ON ‘test’ table TO ‘test’ user

   GRANT select, insert, update, delete on test to test ;

   end;

2. Create a schema_DOWN.sql file with the following content:

   begin;

   drop table IF EXISTS test;

   end;

3. Execute either of the following as required as you evolve your initial/early stage schema.

   • $ psql -f schema_UP.sql -U postgres test OR
   • $ psql -f schema_DOWN.sql -U postgres test

As your database grow you may need to refactor the schema. To such end you may need to ALTER the database schema objects (tables, indexes, constraints, columns, triggers, etc). What I do is create chronologically named UP and DOWN scripts to apply (UP) or revert (DOWN) the changes.

In the case you want to constraint the name column size to 100 char long, you might create and execute the following scripts:

• 201304152246_UP.sql file with the following content:

  begin;

  ALTER table test ALTER COLUMN name SET TYPE varchar(100);

  end;

• 201304152246_DOWN.sql file with the following content:

  begin;

  ALTER table test ALTER COLUMN name SET TYPE varchar;

  end;

With these scripts ready, you can can apply the changes via $ psql -f <scriptname> -U postgres <databasename>.

Notice that to guarantee that every schema change is applied transactionally, I enclose all the instructions with a begin; and end;. This way the script is atomic and it’s either COMMITed or ROLLedBACK.

Let me know if you need further assistance. =D