Postgres Create Table

Databases
1

Hi,

i’m just testing Postgres as a possible database-solution for my apps. I use this code to connect to my Postgres-Server:

  PostgresDB = new PostgreSQLDatabase
  
  PostgresDB.Host = "localhost"
  PostgresDB.Port = 5432
  PostgresDB.DatabaseName = "myDatabase"
  PostgresDB.UserName = "myUsername"
  PostgresDB.Password = "myPassword"
  
  if PostgresDB.Connect then
    MsgBox("PostgresDB connected!")
  Else
    MsgBox("PostgresDB not connected!")
  end if

This works fine. But when i try to create a table, i cannot see this table in pgadmin.

  PostgresDB.SQLExecute("CREATE TABLE customers (id INTEGER, customernumber TEXT, customername TEXT)")
  
  PostgresDB.SQLExecute("COMMIT")
  
  if PostgresDB.Error then
    MsgBox(PostgresDB.ErrorMessage)
  else
    //do whatever you need to do...
  end if

I don’t get any Error-Message.

2

Ups. My fault. Code works. Have splitted the code to a Open- and a Create-Method and forgot to call my CreateTable-Method…

3

Hi Michael,

For DDL you don’t have to do a “COMMIT”. Commits are only for committing actual transactions (INSERT, UPDATE, and DELETE)

4

DDL?

5

Sorry…DDL = Data Definition Language. Those are statements that impact the structure of the database.
CREATE TABLE…
ALTER TABLE…
ALTER SYSTEM…
CREATE INDEX…
DROP TABLE…

As opposed to DML = Data Manipulation Language
INSERT
UPDATE
DELETE

Make sense? :slight_smile:

6

[quote=174197:@Craig Boyd]
Make sense? :)[/quote]

Absolutely. Thanks.

7

Well, differently than some other DBs, PostgreSQL have Transactional DDL.

What happens is that WITHOUT a BEGIN, PostgreSQL auto-commits the atomic statements, even a DML.

So, when you do something like:

CREATE TABLE foo (bar int); INSERT INTO foo VALUES (1);

Internally is like:

BEGIN; CREATE TABLE foo (bar int); COMMIT; BEGIN; INSERT INTO foo VALUES (1); COMMIT;

– Pg Transactional DDL example :

[code]DROP TABLE IF EXISTS foo;
– NOTICE: table “foo” does not exist

BEGIN;
CREATE TABLE foo (bar int);
INSERT INTO foo VALUES (1);
ROLLBACK;

SELECT * FROM foo;
– ERROR: relation “foo” does not exist[/code]

8

That is fantastic! I was not aware of that! I have been wanting something like that for years. Being able to wrap schema changes within a transaction would allow for much safer database upgrades.

Thanks for the info!

9

A very readable (and free) introduction to Postgres: Postgres Succinctly.

And if you develop on a Mac there is a very easy installation: Postgres App.

10

Postgres App is what i already use for my testings. Your link to the free Postgres e-book is also helpful.

Thanks James.