Back Home

Datatypes

PostgreSQL supports the following data types:

  • Boolean
  • Character types such as char, varchar, and text.
  • Numeric types such as integer and floating-point number.
  • Temporal types such as date, time, timestamp, and interval
  • UUID for storing Universally Unique Identifiers
  • Array for storing array strings, numbers, etc.
  • JSON stores JSON data
  • hstore stores key-value pair
  • Special types such as network address and geometric data.

Enums

Postgres supports enums

CREATE TYPE STATUS AS ENUM ('sad', 'ok', 'happy');

Id's Sequences and Primary Keys

Sequences are a common id strategy and Postgres supports it with a special SERIAL keyword

CREATE TABLE TABLE_NAME(
    id SERIAL
);

this is equivalent to

CREATE SEQUENCE table_name_id_seq;
 
CREATE TABLE TABLE_NAME (
    id INTEGER NOT NULL DEFAULT NEXTVAL('table_name_id_seq')
);
 
ALTER SEQUENCE table_name_id_seq
OWNED BY TABLE_NAME.id;

Note to insert into the serial values:

INSERT INTO fruits(name) VALUES('orange');

and for the insert you can use the DEFAULT keyword when inserting named values

INSERT INTO fruits(id,name) VALUES(DEFAULT,'apple');

And to get the id of the value inserted:

INSERT INTO fruits(name) VALUES('banana')
RETURNING id;

Table Management

With regards to table data we may want to

  • alter it's name and attributes, including
  • adding, dropping or renaming a column

To rename a table

ALTER TABLE public.jobstatus RENAME TO job_status;

To drop/delete a column

ALTER TABLE job_status
DROP COLUMN id
 
postgres_data_modelling.txt · Last modified: 2018/12/19 07:56 by root
 
RSS - 200 © CrosswireDigitialMedia Ltd