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) 
  • 4
    Just a side note: there is no advantage having column lengths that are powers of two. And there is nothing special about 255. No magical "optimization" will happen in the background. A column defined as varchar(123) as the same performance and storage characteristics as one defined as varchar(371) (and if both store the e.g. 31 characters, their actual storage requirements are identical as well).
    – user1822
    Apr 10, 2013 at 16:39

3 Answers 3


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:


    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 ;


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


    drop table IF EXISTS test;


  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:


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


  • 201304152246_DOWN.sql file with the following content:


    ALTER table test ALTER COLUMN name SET TYPE varchar;


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.