current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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) 
);
share|improve this question
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). –  a_horse_with_no_name Apr 10 '13 at 16:39
add comment

3 Answers

up vote 2 down vote

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".

share|improve this answer
add comment
up vote 0 down vote

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.

share|improve this answer
add comment
up vote 0 down vote

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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