Postgres basic command

a month ago

This is some basic command that you use a lot when working with postgres.

Create table

CREATE TABLE accounts (
	_id text PRIMARY KEY,
	username text UNIQUE NOT NULL,
	password text NOT NULL,
	email text UNIQUE NOT NULL,
	created_at TIMESTAMP with time zone DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP 
);

Create table with foreign key

Syntax

[CONSTRAINT fk_name] The CONSTRAINT clause is optional. If you omit it, PostgreSQL will assign an auto-generated name.
   FOREIGN KEY(fk_columns) 
   REFERENCES parent_table(parent_key_columns)
   [ON DELETE delete_action]
   [ON UPDATE update_action]

First, specify the name for the foreign key constraint after the CONSTRAINT keyword. The CONSTRAINT clause is optional. If you omit it, PostgreSQL will assign an auto-generated name.

Second, specify one or more foreign key columns in parentheses after the FOREIGN KEY keywords.

Third, specify the parent table and parent key columns referenced by the foreign key columns in the REFERENCES clause.

Finally, specify the delete and update actions in the ON DELETE and ON UPDATE clauses.

 

DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS contacts;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer # ptional.
      FOREIGN KEY(customer_id) 
	  REFERENCES customers(customer_id)
      ON DELETE SET NULL
);

 1 custom

What is constrain

a limitation or restriction.
The following constraints are commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Prevents actions that would destroy links between tables
CHECK - Ensures that the values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column if no value is specified
CREATE INDEX - Used to create and retrieve data from the database very quickly

 

Update table schame

# add column
ALTER TABLE users ADD COLUMN IF NOT EXISTS imageurl text;
# drop column
ALTER TABLE users DROP COLUMN IF EXISTS imageurl;

Delete table

DROP TABLE IF EXISTS users;

 Insert data to table

Insert into users(id, "name") values(1, 'Abstract');

Update data to table

UPDATE users SET challenges = FALSE;

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;

Inner join

select p.id, title, user_id, p.created_at, p.updated_at, tags, u.name
from posts p
inner join users u
on p.user_id = u.id