PostgreSQL also called Postgres, is an open-source, object-oriented relational database management system released under the PostgreSQL license. In this post, I wanted to do a little cheat sheet on how to use Postgres with examples, because if you are like me, sometimes I need a little quick glance to the instructions and I thought will be helpful for someone have this info in one place.
CREATE TABLE [IF NOT EXISTS] person( document INTEGER NOT NULL, documentType SMALLINT NOT NULL name VARCHAR ( 50 ) NOT NULL, bithDate TIMESTAMP NOT NULL, PRIMARY KEY (document , documentType ), FOREIGN KEY (documentType) REFERENCES type(code), );
DROP TABLE [IF EXISTS] person [CASCADE | RESTRICT];
If you remove a table that does not exist, PostgreSQL issues an error. To avoid this situation, you can use the IF EXISTS, I always added just in case.
On the other hand, the CASCADE option allows you to remove the table and its dependent objects meanwhile the RESTRICT option rejects the removal if there is any object depends on the table. The RESTRICT option is the default if you don’t explicitly specify it in the DROP TABLE statement.
/* Add a column */ ALTER TABLE person ADD COLUMN passportNumber INTEGER; /* Delete a column */ ALTER TABLE person DROP COLUMN passportNumber ; /* Rename the column*/ ALTER TABLE person RENAME COLUMN document TO documentNumber; /* Add a constraint at the table */ ALTER TABLE person ADD CONSTRAINT unique_passport UNIQUE ( passportNumber );
/*Insert to the table*/ INSERT INTO person(document, documentType, name, bithDate ) VALUES (1020754, 2, 'Jane Doe', '13/02/1998'); /*Delete*/ DELETE FROM person WHERE document = 1020754; /*Update*/ UPDATE person SET bithDate = '1998-02-14' WHERE document = 1020754;
To finish I want to give you a little bonus if you need to insert a lot of registers you could use COPY, It's a convenient way to transfer data between files and tables, but it's also much faster than INSERT when more than a few thousand rows are added at a time.
COPY copy_test FROM '/path/sample_data.csv' DELIMITER ',';
I hope you find this helpful and thank you for reading!!