Table Creation All Versions

Examples

• 0

  Create table from select

  edit move example delete share

  0

  Let's say you have a table called person:

  CREATE TABLE person (
      person_id BIGINT NOT NULL,
      last_name VARCHAR(255) NOT NULL,
      first_name VARCHAR(255),
      age INT NOT NULL,
      PRIMARY KEY (person_id)
  );     
  

  You can create a new table of people over 30 like this:

  CREATE TABLE people_over_30 AS SELECT * FROM person WHERE age > 30;
  

  created Jul 22 at 16:11

  
• 0

  Show table definition

  edit move example delete share

  0

  Open the psql command line tool connected to the database where your table is. Then type the following command:

  \d tablename
  

  To get extended information type

  \d+ tablename
  

  If you have forgotten the name of the table, just type \d into psql to obtain a list of tables and views in the current database.

  edited Jul 23 at 17:56

  

  
• 0

  Table creation with Foreign Key

  edit move example delete share

  0

  CREATE TABLE order (
      order_id BIGINT NOT NULL,
      person_id BIGINT references person(person_id),
      order_date TIMESTAMP(6),
      PRIMARY KEY (order_id)
  );  
  
  CREATE TABLE person (
      person_id BIGINT NOT NULL,
      last_name VARCHAR(255) NOT NULL,
      first_name VARCHAR(255),
      address VARCHAR(255),
      city VARCHAR(255),
      PRIMARY KEY (person_id)
  );
  

  Alternatively, you can also add a foreign key as a constraint:

  CREATE TABLE order (
      order_id BIGINT NOT NULL,
      person_id BIGINT,
      order_date TIMESTAMP(6),
      PRIMARY KEY (order_id),
      CONSTRAINT person_id_fkey FOREIGN KEY (person_id) REFERENCES person (person_id) 
  );  
  
  CREATE TABLE person (
      person_id BIGINT NOT NULL,
      last_name VARCHAR(255) NOT NULL,
      first_name VARCHAR(255),
      address VARCHAR(255),
      city VARCHAR(255),
      PRIMARY KEY (person_id)
  );
  

  created Jul 22 at 16:11

  
• 0

  Table creation with Primary Key

  edit move example delete share

  0

  CREATE TABLE person (
      person_id BIGINT NOT NULL,
      last_name VARCHAR(255) NOT NULL,
      first_name VARCHAR(255),
      address VARCHAR(255),
      city VARCHAR(255),
      PRIMARY KEY (person_id)
  );
  

  Alternatively, you can place the PRIMARY KEY constraint directly in the column definition:

  CREATE TABLE person (
      person_id BIGINT NOT NULL PRIMARY KEY,
      last_name VARCHAR(255) NOT NULL,
      first_name VARCHAR(255),
      address VARCHAR(255),
      city VARCHAR(255)
  );
  

  It is recommended that you use lower case names for the table and as well as all the columns. If you use upper case names such as Person you would have to wrap that name in double quotes ("Person") in each and every query because PostgreSQL enforces case folding.

  edited Jul 22 at 14:41