Useful Postgres SQL Example Codes

1. create basic demo table in postgres sql with an ENUM column and an auto-increment one


-- we create an enum type
CREATE TYPE demo_enum AS ENUM ('item1', 'item2', 'item3');

-- we create a sequence
CREATE SEQUENCE demotable_id_seq;

-- we create the actual table and we'll use the sequence for autoincrement column
-- and the type for an enum-type column
CREATE TABLE demo_table
("id" integer NOT NULL DEFAULT nextval('demotable_id_seq'),
start_date timestamp,
end_date timestamp,
demo_choice demo_enum,
demo_extern integer,
status smallint DEFAULT 1,
PRIMARY KEY ("id"));

-- we alter the sequence
ALTER SEQUENCE demotable_id_seq OWNED BY demo_table.id;

-- we add a foreign key constraint
ALTER TABLE demo_table ADD FOREIGN KEY (demo_extern) REFERENCES demo_other_table(demo_extern_column);

Leave a Reply

Your email address will not be published. All fields are mandatory.

This site uses Akismet to reduce spam. Learn how your comment data is processed.