Create table from select
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;
Show table definition
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.
Table creation with Foreign Key
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)
);
Table creation with Primary Key
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.
Sign up or log in
Save edit as a guest
Join Stack Overflow
We recognize you from another Stack Exchange Network site!
Join and Save Draft