PostgreSQL cheat sheet for beginners

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 a table

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 a table

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.

Alter a table

/* 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 );

Manage data

/*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!!

Comments

[nove1398]

Short and sweet, i like the tips.

[sabrinasuarezarrieta]

Thanks for that sweet comment😊😊

[webstuff]

Sooo, you can create types? Like in oracle? Cool !!!

[sabrinasuarezarrieta]

Yes you can create them, but I'm not an expert because I've never used it, but I found this documentation, I hope will be helpful for you ✌️ ... doc