0

I'm attempting to make a table for the first time using postgres and the examples I'm seeing are kind of throwing me off. When it comes to creating a schema, I have a schema.sql file that contains my schema as follows:

CREATE TABLE IF NOT EXISTS orders
(
    order_id INTEGER NOT NULL,
    order_amount INTEGER NOT NULL
);

COMMENT ON COLUMN orders.order_id IS 'The order ID';
COMMENT ON COLUMN orders.order_amount IS 'The order amount';

Now I'd upload that schema by doing the following: psql -d mydb -f /usr/share/schema.sql

Now when it comes time to create the table I'm suppose to do something like this:

create table schema.orders(
   order_id   INT       NOT NULL,
   order_amount INT     NOT NULL
 );

The uploading of the schema.sql file is what confuses me. What is all the information inside the file used for. I thought by uploading the schema i'm providing the model to create the table, but running create table schema.orders seems to be doing just that.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Brosef
  • 2,945
  • 6
  • 34
  • 69

1 Answers1

0

What you call "upload" is actually executing a script file (with SQL DDL commands in it).

I thought by uploading the schema i'm providing the model to create the table

You are creating the table by executing that script. The second CREATE TABLE command is almost but not quite doing the same. Crucial difference (besides the missing comments): A schema-qualified table name. And your schema happens to be named "schema", which is a pretty bad idea, but allowed.

Now, the term "schema" is used for two different things:

  1. The general database structure created with SQL DDL commands.
  2. A SCHEMA which is similar to a directory in a file system.

The term just happens to be the same for either, but one has nothing to do with the other.

Depending on the schema search path, the first invocation of CREATE TABLE may or may not have created another table in a different schema. You need to understand the role of the search path in Postgres:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • So I have that schema.sql file and there is the psql CREATE TABLE command. In order to create a table, do I need to execute the script file and run the psql CREATE TABLE command? – Brosef Jan 27 '16 at 04:51
  • @Brosef: `CREATE TABLE` is an SQL command, not a psql command. psql is the name of the command-line terminal. Generally, a table can only be created once. It you can run the second command without raising an exception, you have tables in different schemas. Read the answer. – Erwin Brandstetter Jan 27 '16 at 05:13