joplin-notes/joplin notion extension/Web Development/sql/postgresql syntax.md

postgresql syntax

• Creating table

    DROP TABLE IF EXISTS COMPANY;
    CREATE TABLE COMPANY(
       ID INT PRIMARY KEY      NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL
    );
  

  For auto increment

  CREATE TABLE COMPANY(
     ID SERIAL PRIMARY KEY      NOT NULL,
     NAME           TEXT    NOT NULL,
     AGE            INT     NOT NULL,
     ADDRESS        CHAR(50),
     SALARY         REAL
  );
  

  SERIAL for auto increment

• to load postgresql database

  psql -U postgres smart-brain
  

  Here postgres is owner name and smart-brain is name of data base. It will prompt for password which is U2FsdGVkX1/k+uOTLs4gSYVmxYAJEQybNVGnxIQVHyg=. Refer to

  Web development

  for decrypting it.

  To load postgresql without database:

  psql -U postgres

  To create database:

  createdb mydb -U postgres

  or

  psql -U postgres
  create database mydb;
  

  To get list of database:

  \l

  To connect to database when login as user

  \connect dbname

  \q: To quit

  • List tables, views and sequences \d
  • Describe tables \d <table_name>