Skip to content

Instantly share code, notes, and snippets.

@siddhant3030

siddhant3030/postgres

Created November 13, 2019 15:21
Show Gist options
  • Save siddhant3030/03544345ea6667f93400ffc300883806 to your computer and use it in GitHub Desktop.
Save siddhant3030/03544345ea6667f93400ffc300883806 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
postgres
Last login: Wed Nov 13 12:59:17 on ttys001
icicle@Icicles-MacBook-Air ~ % cd ~
icicle@Icicles-MacBook-Air ~ % ls -a
. .mkshrc Desktop
.. .mysql_history Documents
.CFUserTextEncoding .profile Downloads
.DS_Store .rvm Library
.Trash .subversion Movies
.bash_profile .vscode Music
.bashrc .zlogin Pictures
.gem .zsh_history Public
.hex .zshrc
.mix Applications
icicle@Icicles-MacBook-Air ~ % vi .zshrc
icicle@Icicles-MacBook-Air ~ % nano .zshrc
icicle@Icicles-MacBook-Air ~ % source .zshrc
icicle@Icicles-MacBook-Air ~ % psql
psql (12.0)
Type "help" for help.
icicle=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
icicle=# \?
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display results in crosstab
\errverbose show most recent error message at maximum verbosity
\g [FILE] or ; execute query (and send results to file or |pipe)
\gdesc describe result of query, without executing it
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store results in psql variables
\gx [FILE] as \g, but forces expanded output mode
\q quit psql
\watch [SEC] execute query every SEC seconds
Help
\? [commands] show help on backslash commands
\? options show help on psql command-line options
\? variables show help on special variables
\h [NAME] help on syntax of SQL commands, * for all commands
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\ev [VIEWNAME [LINE]] edit view definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
Input/Output
\copy ... perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard output
\i FILE execute commands from file
\ir FILE as \i, but relative to location of current script
\o [FILE] send all query results to file or |pipe
\qecho [STRING] write string to query output stream (see \o)
Conditional
\if EXPR begin conditional block
\elif EXPR alternative within current conditional block
\else final alternative within current conditional block
\endif end conditional block
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\dA[+] [PATTERN] list access methods
\db[+] [PATTERN] list tablespaces
\dc[S+] [PATTERN] list conversions
\dC[+] [PATTERN] list casts
\dd[S] [PATTERN] show object descriptions not displayed elsewhere
\dD[S+] [PATTERN] list domains
\ddp [PATTERN] list default privileges
\dE[S+] [PATTERN] list foreign tables
\det[+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\deu[+] [PATTERN] list user mappings
Conditional
\if EXPR begin conditional block
\elif EXPR alternative within current conditional block
\else final alternative within current conditional block
\endif end conditional block
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\dA[+] [PATTERN] list access methods
\db[+] [PATTERN] list tablespaces
\dc[S+] [PATTERN] list conversions
\dC[+] [PATTERN] list casts
\dd[S] [PATTERN] show object descriptions not displayed elsewhere
\dD[S+] [PATTERN] list domains
\ddp [PATTERN] list default privileges
\dE[S+] [PATTERN] list foreign tables
\det[+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[anptw][S+] [PATRN] list [only agg/normal/procedures/trigger/window] functions
\dF[+] [PATTERN] list text search configurations
\dFd[+] [PATTERN] list text search dictionaries
\dFp[+] [PATTERN] list text search parsers
\dFt[+] [PATTERN] list text search templates
\dg[S+] [PATTERN] list roles
\di[S+] [PATTERN] list indexes
\dl list large objects, same as \lo_list
\dL[S+] [PATTERN] list procedural languages
\dm[S+] [PATTERN] list materialized views
\dn[S+] [PATTERN] list schemas
\do[S] [PATTERN] list operators
\dO[S+] [PATTERN] list collations
\dp [PATTERN] list table, view, and sequence access privileges
\dP[itn+] [PATTERN] list [only index/table] partitioned relations [n=nested]
\drds [PATRN1 [PATRN2]] list per-database role settings
\dRp[+] [PATTERN] list replication publications
\dRs[+] [PATTERN] list replication subscriptions
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\du[S+] [PATTERN] list roles
\dv[S+] [PATTERN] list views
\dx[+] [PATTERN] list extensions
\dy [PATTERN] list event triggers
\l[+] [PATTERN] list databases
\sf[+] FUNCNAME show a function's definition
\sv[+] VIEWNAME show a view's definition
\z [PATTERN] same as \dp
Formatting
\a toggle between unaligned and aligned output mode
\C [STRING] set table title, or unset if none
\f [STRING] show or set field separator for unaligned query output
\H toggle HTML output mode (currently off)
\pset [NAME [VALUE]] set table output option
(border|columns|csv_fieldsep|expanded|fieldsep|
fieldsep_zero|footer|format|linestyle|null|
numericlocale|pager|pager_min_lines|recordsep|
recordsep_zero|tableattr|title|tuples_only|
unicode_border_linestyle|unicode_column_linestyle|
unicode_header_linestyle)
\t [on|off] show only rows (currently off)
\T [STRING] set HTML <table> tag attributes, or unset if none
\x [on|off|auto] toggle expanded output (currently off)
Connection
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
connect to new database (currently "icicle")
\conninfo display information about current connection
\encoding [ENCODING] show or set client encoding
\password [USERNAME] securely change the password for a user
Operating System
\cd [DIR] change the current working directory
\setenv NAME [VALUE] set or unset environment variable
\timing [on|off] toggle timing of commands (currently off)
\! [COMMAND] execute command in shell or start interactive shell
Variables
\prompt [TEXT] NAME prompt user to set internal variable
\set [NAME [VALUE]] set internal variable, or list all if no parameters
\unset NAME unset (delete) internal variable
Large Objects
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID large object operations
icicle=# \h
Available help:
ABORT ALTER SYSTEM CREATE FOREIGN DATA WRAPPER CREATE USER MAPPING DROP ROUTINE PREPARE
ALTER AGGREGATE ALTER TABLE CREATE FOREIGN TABLE CREATE VIEW DROP RULE PREPARE TRANSACTION
ALTER COLLATION ALTER TABLESPACE CREATE FUNCTION DEALLOCATE DROP SCHEMA REASSIGN OWNED
ALTER CONVERSION ALTER TEXT SEARCH CONFIGURATION CREATE GROUP DECLARE DROP SEQUENCE REFRESH MATERIALIZED VIEW
ALTER DATABASE ALTER TEXT SEARCH DICTIONARY CREATE INDEX DELETE DROP SERVER REINDEX
ALTER DEFAULT PRIVILEGES ALTER TEXT SEARCH PARSER CREATE LANGUAGE DISCARD DROP STATISTICS RELEASE SAVEPOINT
ALTER DOMAIN ALTER TEXT SEARCH TEMPLATE CREATE MATERIALIZED VIEW DO DROP SUBSCRIPTION RESET
ALTER EVENT TRIGGER ALTER TRIGGER CREATE OPERATOR DROP ACCESS METHOD DROP TABLE REVOKE
ALTER EXTENSION ALTER TYPE CREATE OPERATOR CLASS DROP AGGREGATE DROP TABLESPACE ROLLBACK
ALTER FOREIGN DATA WRAPPER ALTER USER CREATE OPERATOR FAMILY DROP CAST DROP TEXT SEARCH CONFIGURATION ROLLBACK PREPARED
ALTER FOREIGN TABLE ALTER USER MAPPING CREATE POLICY DROP COLLATION DROP TEXT SEARCH DICTIONARY ROLLBACK TO SAVEPOINT
ALTER FUNCTION ALTER VIEW CREATE PROCEDURE DROP CONVERSION DROP TEXT SEARCH PARSER SAVEPOINT
ALTER GROUP ANALYZE CREATE PUBLICATION DROP DATABASE DROP TEXT SEARCH TEMPLATE SECURITY LABEL
ALTER INDEX BEGIN CREATE ROLE DROP DOMAIN DROP TRANSFORM SELECT
ALTER LANGUAGE CALL CREATE RULE DROP EVENT TRIGGER DROP TRIGGER SELECT INTO
ALTER LARGE OBJECT CHECKPOINT CREATE SCHEMA DROP EXTENSION DROP TYPE SET
ALTER MATERIALIZED VIEW CLOSE CREATE SEQUENCE DROP FOREIGN DATA WRAPPER DROP USER SET CONSTRAINTS
ALTER OPERATOR CLUSTER CREATE SERVER DROP FOREIGN TABLE DROP USER MAPPING SET ROLE
ALTER OPERATOR CLASS COMMENT CREATE STATISTICS DROP FUNCTION DROP VIEW SET SESSION AUTHORIZATION
ALTER OPERATOR FAMILY COMMIT CREATE SUBSCRIPTION DROP GROUP END SET TRANSACTION
ALTER POLICY COMMIT PREPARED CREATE TABLE DROP INDEX EXECUTE SHOW
ALTER PROCEDURE COPY CREATE TABLE AS DROP LANGUAGE EXPLAIN START TRANSACTION
ALTER PUBLICATION CREATE ACCESS METHOD CREATE TABLESPACE DROP MATERIALIZED VIEW FETCH TABLE
ALTER ROLE CREATE AGGREGATE CREATE TEXT SEARCH CONFIGURATION DROP OPERATOR GRANT TRUNCATE
ALTER ROUTINE CREATE CAST CREATE TEXT SEARCH DICTIONARY DROP OPERATOR CLASS IMPORT FOREIGN SCHEMA UNLISTEN
ALTER RULE CREATE COLLATION CREATE TEXT SEARCH PARSER DROP OPERATOR FAMILY INSERT UPDATE
ALTER SCHEMA CREATE CONVERSION CREATE TEXT SEARCH TEMPLATE DROP OWNED LISTEN VACUUM
ALTER SEQUENCE CREATE DATABASE CREATE TRANSFORM DROP POLICY LOAD VALUES
ALTER SERVER CREATE DOMAIN CREATE TRIGGER DROP PROCEDURE LOCK WITH
ALTER STATISTICS CREATE EVENT TRIGGER CREATE TYPE DROP PUBLICATION MOVE
ALTER SUBSCRIPTION CREATE EXTENSION CREATE USER DROP ROLE NOTIFY
icicle=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
icicle | icicle | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
icicle=# CREATE DATABASE test;
CREATE DATABASE
icicle=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
icicle | icicle | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | icicle | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
icicle=# psql --help
icicle-# ls
icicle-# quit
Use \q to quit.
icicle-# \q
icicle@Icicles-MacBook-Air ~ % psql -h localhost -p 5432 -U icicle
psql (12.0)
Type "help" for help.
icicle=# \q
icicle@Icicles-MacBook-Air ~ % psql -h localhost -p 5432 -U icicle test
psql (12.0)
Type "help" for help.
test=# \q
icicle@Icicles-MacBook-Air ~ % \l
zsh: command not found: l
icicle@Icicles-MacBook-Air ~ % psql -h localhost -p 5432 -U icicle test
psql (12.0)
Type "help" for help.
test=# CREATE TABLE users (
test(# id INT,
test(# name VARCHAR(40),
test(# first_name VARCHAR(12),
test(# last_name VARCHAR(12),
test(# email VARCHAR(30),
test(# password VARCHAR(40),
test(# birthday DATE,
test(# gender VARCHAR(10),
test(# \q
icicle@Icicles-MacBook-Air ~ % psql -h localhost -p 5432 -U icicle test
psql (12.0)
Type "help" for help.
test=# \l List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
icicle | icicle | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | icicle | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
test=# clear
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-#
test-# Create TABLE users (
test(#
test=# Create TABLE person (
test(# id INT,
test(# first_name VARCHAR(30),
test(# last_name VARCHAR(30),
test(# gender VARCHAR(20),
test(# date_of_birth DATE );
CREATE TABLE
test=# \d
List of relations
Schema | Name | Type | Owner
--------+--------+-------+--------
public | person | table | icicle
(1 row)
test=# \d person
Table "public.person"
Column | Type | Collation | Nullable | Default
---------------+-----------------------+-----------+----------+---------
id | integer | | |
first_name | character varying(30) | | |
last_name | character varying(30) | | |
gender | character varying(20) | | |
date_of_birth | date | | |
test=# DROP TABLE person;
DROP TABLE
test=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
icicle | icicle | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | icicle | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
test=# \d
Did not find any relations.
test=# CREATE TABLE person (
test(# id BIGSERIAL NOT NULL PRIMARY KEY,
test(# first_name VARCHAR(50) NOT NULL,
test(# last_name VARCHAR(50) NOT NULL,
test(# gender VARCHAR(7) NOT NULL,
test(# date_of_birth DATE NOT NULL,
test(# email VARCHAR(150) );
CREATE TABLE
test=# \D
invalid command \D
Try \? for help.
test=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------+----------+--------
public | person | table | icicle
public | person_id_seq | sequence | icicle
(2 rows)
test=# \d person
Table "public.person"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+------------------------------------
id | bigint | | not null | nextval('person_id_seq'::regclass)
first_name | character varying(50) | | not null |
last_name | character varying(50) | | not null |
gender | character varying(7) | | not null |
date_of_birth | date | | not null |
email | character varying(150) | | |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth)
test-# VALUES ('siddhant', 'singh', 'male', date '1995-09-30')
test-# ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('sidd', 'singh', 'male', date '1995-10-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('sid', 'sngh', 'male', date '1995-12-30') ;
INSERT 0 1
test=# SELECT * FROM person;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
(3 rows)
test=# SELECT * FROM person;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
(3 rows)
test=# SELECT FROM person
test-# ;
--
(3 rows)
test=# SELECT first_name FROM person;
first_name
------------
siddhant
sidd
sid
(3 rows)
test=# SELECT * FROM person ORDER BY date_of_birth;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
(3 rows)
test=# SELECT * FROM person ORDER BY first_name;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
3 | sid | sngh | male | 1995-12-30 |
2 | sidd | singh | male | 1995-10-30 |
1 | siddhant | singh | male | 1995-09-30 |
(3 rows)
test=# SELECT * FROM person ORDER BY first_name ASC;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
3 | sid | sngh | male | 1995-12-30 |
2 | sidd | singh | male | 1995-10-30 |
1 | siddhant | singh | male | 1995-09-30 |
(3 rows)
test=# SELECT * FROM person ORDER BY first_name DESC;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
(3 rows)
test=# SELECT * FROM person ORDER BY date_of_birth ASC;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
(3 rows)
test=# SELECT * FROM person ORDER BY date_of_birth DESC;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
3 | sid | sngh | male | 1995-12-30 |
2 | sidd | singh | male | 1995-10-30 |
1 | siddhant | singh | male | 1995-09-30 |
(3 rows)
test=# SELECT * FROM person ORDER BY ID;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
(3 rows)
test=# SELECT * FROM person ORDER BY ID ASC;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
(3 rows)
test=# SELECT * FROM person ORDER BY ID DESC;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
3 | sid | sngh | male | 1995-12-30 |
2 | sidd | singh | male | 1995-10-30 |
1 | siddhant | singh | male | 1995-09-30 |
(3 rows)
test=# SELECT first_name FROM person;
first_name
------------
siddhant
sidd
sid
(3 rows)
test=# SELECT first_name FROM person ORDER BY date_of_birth ASC;
first_name
------------
siddhant
sidd
sid
(3 rows)
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('siddhant', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('siddhant', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('siddhant', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('siddhant', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('siddhant', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('siddhant', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('siddhant', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('siddhant', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# \d person
Table "public.person"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+------------------------------------
id | bigint | | not null | nextval('person_id_seq'::regclass)
first_name | character varying(50) | | not null |
last_name | character varying(50) | | not null |
gender | character varying(7) | | not null |
date_of_birth | date | | not null |
email | character varying(150) | | |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
test=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
icicle | icicle | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | icicle | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
test=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------+----------+--------
public | person | table | icicle
public | person_id_seq | sequence | icicle
(2 rows)
test=# Select * FROM person;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
4 | siddhant | singh | male | 1995-09-30 |
5 | siddhant | singh | male | 1995-09-30 |
6 | siddhant | singh | male | 1995-09-30 |
7 | siddhant | singh | male | 1995-09-30 |
8 | siddhant | singh | male | 1995-09-30 |
9 | siddhant | singh | male | 1995-09-30 |
10 | siddhant | singh | male | 1995-09-30 |
11 | siddhant | singh | male | 1995-09-30 |
(11 rows)
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('karan', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('karan', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('karan', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('karan', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('karan', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('Aakash', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('Aakash', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('Aakash', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('Aakash', 'singh', 'male', date '1995-09-30') ;
INSERT 0 1
test=# Select * FROM person; id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
4 | siddhant | singh | male | 1995-09-30 |
5 | siddhant | singh | male | 1995-09-30 |
6 | siddhant | singh | male | 1995-09-30 |
7 | siddhant | singh | male | 1995-09-30 |
8 | siddhant | singh | male | 1995-09-30 |
9 | siddhant | singh | male | 1995-09-30 |
10 | siddhant | singh | male | 1995-09-30 |
11 | siddhant | singh | male | 1995-09-30 |
12 | karan | singh | male | 1995-09-30 |
13 | karan | singh | male | 1995-09-30 |
14 | karan | singh | male | 1995-09-30 |
15 | karan | singh | male | 1995-09-30 |
16 | karan | singh | male | 1995-09-30 |
17 | Aakash | singh | male | 1995-09-30 |
18 | Aakash | singh | male | 1995-09-30 |
19 | Aakash | singh | male | 1995-09-30 |
20 | Aakash | singh | male | 1995-09-30 |
(20 rows)
test=# SELECT DISTINCT first_name FROM person ORDER BY first_name;
first_name
------------
Aakash
karan
sid
sidd
siddhant
(5 rows)
test=# SELECT DISTINCT first_name FROM person ORDER BY first_name ASC;
first_name
------------
Aakash
karan
sid
sidd
siddhant
(5 rows)
test=# SELECT DISTINCT first_name FROM person ORDER BY first_name DESC;
first_name
------------
siddhant
sidd
sid
karan
Aakash
(5 rows)
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('parul', 'singh', 'female', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('parul', 'singh', 'female', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('parul', 'singh', 'female', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('parul', 'singh', 'female', date '1995-09-30') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('parul', 'singh', 'female', date '1995-09-30') ;
INSERT 0 1
test=# SELECT * FROM person;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
4 | siddhant | singh | male | 1995-09-30 |
5 | siddhant | singh | male | 1995-09-30 |
6 | siddhant | singh | male | 1995-09-30 |
7 | siddhant | singh | male | 1995-09-30 |
8 | siddhant | singh | male | 1995-09-30 |
9 | siddhant | singh | male | 1995-09-30 |
10 | siddhant | singh | male | 1995-09-30 |
11 | siddhant | singh | male | 1995-09-30 |
12 | karan | singh | male | 1995-09-30 |
13 | karan | singh | male | 1995-09-30 |
14 | karan | singh | male | 1995-09-30 |
15 | karan | singh | male | 1995-09-30 |
16 | karan | singh | male | 1995-09-30 |
17 | Aakash | singh | male | 1995-09-30 |
18 | Aakash | singh | male | 1995-09-30 |
19 | Aakash | singh | male | 1995-09-30 |
20 | Aakash | singh | male | 1995-09-30 |
21 | parul | singh | female | 1995-09-30 |
22 | parul | singh | female | 1995-09-30 |
23 | parul | singh | female | 1995-09-30 |
24 | parul | singh | female | 1995-09-30 |
25 | parul | singh | female | 1995-09-30 |
(25 rows)
test=# select * from person where gender = "female";
ERROR: column "female" does not exist
LINE 1: select * from person where gender = "female";
^
HINT: Perhaps you meant to reference the column "person.email".
test=# select * from person where gender = "Female";
ERROR: column "Female" does not exist
LINE 1: select * from person where gender = "Female";
^
HINT: Perhaps you meant to reference the column "person.email".
test=# select * from person where gender = 'female';
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
21 | parul | singh | female | 1995-09-30 |
22 | parul | singh | female | 1995-09-30 |
23 | parul | singh | female | 1995-09-30 |
24 | parul | singh | female | 1995-09-30 |
25 | parul | singh | female | 1995-09-30 |
(5 rows)
test=# select * from person where last_name = 'sngh';
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
3 | sid | sngh | male | 1995-12-30 |
(1 row)
test=# select * from person LIMIT 1;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
(1 row)
test=# select * from person LIMIT 2;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
(2 rows)
test=# select * from person LIMIT 10;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
4 | siddhant | singh | male | 1995-09-30 |
5 | siddhant | singh | male | 1995-09-30 |
6 | siddhant | singh | male | 1995-09-30 |
7 | siddhant | singh | male | 1995-09-30 |
8 | siddhant | singh | male | 1995-09-30 |
9 | siddhant | singh | male | 1995-09-30 |
10 | siddhant | singh | male | 1995-09-30 |
(10 rows)
test=# select * from person LIMIT 12;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
4 | siddhant | singh | male | 1995-09-30 |
5 | siddhant | singh | male | 1995-09-30 |
6 | siddhant | singh | male | 1995-09-30 |
7 | siddhant | singh | male | 1995-09-30 |
8 | siddhant | singh | male | 1995-09-30 |
9 | siddhant | singh | male | 1995-09-30 |
10 | siddhant | singh | male | 1995-09-30 |
11 | siddhant | singh | male | 1995-09-30 |
12 | karan | singh | male | 1995-09-30 |
(12 rows)
test=# select * from person LIMIT 20;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
4 | siddhant | singh | male | 1995-09-30 |
5 | siddhant | singh | male | 1995-09-30 |
6 | siddhant | singh | male | 1995-09-30 |
7 | siddhant | singh | male | 1995-09-30 |
8 | siddhant | singh | male | 1995-09-30 |
9 | siddhant | singh | male | 1995-09-30 |
10 | siddhant | singh | male | 1995-09-30 |
11 | siddhant | singh | male | 1995-09-30 |
12 | karan | singh | male | 1995-09-30 |
13 | karan | singh | male | 1995-09-30 |
14 | karan | singh | male | 1995-09-30 |
15 | karan | singh | male | 1995-09-30 |
16 | karan | singh | male | 1995-09-30 |
17 | Aakash | singh | male | 1995-09-30 |
18 | Aakash | singh | male | 1995-09-30 |
19 | Aakash | singh | male | 1995-09-30 |
20 | Aakash | singh | male | 1995-09-30 |
(20 rows)
test=# select * from person LIMIT 100;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
4 | siddhant | singh | male | 1995-09-30 |
5 | siddhant | singh | male | 1995-09-30 |
6 | siddhant | singh | male | 1995-09-30 |
7 | siddhant | singh | male | 1995-09-30 |
8 | siddhant | singh | male | 1995-09-30 |
9 | siddhant | singh | male | 1995-09-30 |
10 | siddhant | singh | male | 1995-09-30 |
11 | siddhant | singh | male | 1995-09-30 |
12 | karan | singh | male | 1995-09-30 |
13 | karan | singh | male | 1995-09-30 |
14 | karan | singh | male | 1995-09-30 |
15 | karan | singh | male | 1995-09-30 |
16 | karan | singh | male | 1995-09-30 |
17 | Aakash | singh | male | 1995-09-30 |
18 | Aakash | singh | male | 1995-09-30 |
19 | Aakash | singh | male | 1995-09-30 |
20 | Aakash | singh | male | 1995-09-30 |
21 | parul | singh | female | 1995-09-30 |
22 | parul | singh | female | 1995-09-30 |
23 | parul | singh | female | 1995-09-30 |
24 | parul | singh | female | 1995-09-30 |
25 | parul | singh | female | 1995-09-30 |
(25 rows)
test=# select * from person LIMIT 120;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
4 | siddhant | singh | male | 1995-09-30 |
5 | siddhant | singh | male | 1995-09-30 |
6 | siddhant | singh | male | 1995-09-30 |
7 | siddhant | singh | male | 1995-09-30 |
8 | siddhant | singh | male | 1995-09-30 |
9 | siddhant | singh | male | 1995-09-30 |
10 | siddhant | singh | male | 1995-09-30 |
11 | siddhant | singh | male | 1995-09-30 |
12 | karan | singh | male | 1995-09-30 |
13 | karan | singh | male | 1995-09-30 |
14 | karan | singh | male | 1995-09-30 |
15 | karan | singh | male | 1995-09-30 |
16 | karan | singh | male | 1995-09-30 |
17 | Aakash | singh | male | 1995-09-30 |
18 | Aakash | singh | male | 1995-09-30 |
19 | Aakash | singh | male | 1995-09-30 |
20 | Aakash | singh | male | 1995-09-30 |
21 | parul | singh | female | 1995-09-30 |
22 | parul | singh | female | 1995-09-30 |
23 | parul | singh | female | 1995-09-30 |
24 | parul | singh | female | 1995-09-30 |
25 | parul | singh | female | 1995-09-30 |
(25 rows)
test=# select * from person LIMIT 20;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
4 | siddhant | singh | male | 1995-09-30 |
5 | siddhant | singh | male | 1995-09-30 |
6 | siddhant | singh | male | 1995-09-30 |
7 | siddhant | singh | male | 1995-09-30 |
8 | siddhant | singh | male | 1995-09-30 |
9 | siddhant | singh | male | 1995-09-30 |
10 | siddhant | singh | male | 1995-09-30 |
11 | siddhant | singh | male | 1995-09-30 |
12 | karan | singh | male | 1995-09-30 |
13 | karan | singh | male | 1995-09-30 |
14 | karan | singh | male | 1995-09-30 |
15 | karan | singh | male | 1995-09-30 |
16 | karan | singh | male | 1995-09-30 |
17 | Aakash | singh | male | 1995-09-30 |
18 | Aakash | singh | male | 1995-09-30 |
19 | Aakash | singh | male | 1995-09-30 |
20 | Aakash | singh | male | 1995-09-30 |
(20 rows)
test=# select * from person OFFSET 20;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
21 | parul | singh | female | 1995-09-30 |
22 | parul | singh | female | 1995-09-30 |
23 | parul | singh | female | 1995-09-30 |
24 | parul | singh | female | 1995-09-30 |
25 | parul | singh | female | 1995-09-30 |
(5 rows)
test=# select * from person OFFSET 20 LIMIT 2;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
21 | parul | singh | female | 1995-09-30 |
22 | parul | singh | female | 1995-09-30 |
(2 rows)
test=# select * from person OFFSET 20 LIMIT 3;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
21 | parul | singh | female | 1995-09-30 |
22 | parul | singh | female | 1995-09-30 |
23 | parul | singh | female | 1995-09-30 |
(3 rows)
test=# select * from person OFFSET 20 LIMIT 4;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
21 | parul | singh | female | 1995-09-30 |
22 | parul | singh | female | 1995-09-30 |
23 | parul | singh | female | 1995-09-30 |
24 | parul | singh | female | 1995-09-30 |
(4 rows)
test=# select * from person OFFSET 20 LIMIT ;
ERROR: syntax error at or near ";"
LINE 1: select * from person OFFSET 20 LIMIT ;
^
test=# select * from person OFFSET 20 LIMIT 5;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
21 | parul | singh | female | 1995-09-30 |
22 | parul | singh | female | 1995-09-30 |
23 | parul | singh | female | 1995-09-30 |
24 | parul | singh | female | 1995-09-30 |
25 | parul | singh | female | 1995-09-30 |
(5 rows)
test=# select * from person OFFSET 20 LIMIT 7;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
21 | parul | singh | female | 1995-09-30 |
22 | parul | singh | female | 1995-09-30 |
23 | parul | singh | female | 1995-09-30 |
24 | parul | singh | female | 1995-09-30 |
25 | parul | singh | female | 1995-09-30 |
(5 rows)
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('parul', 'singh', 'female', date '1995-10-29') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('parul', 'singh', 'female', date '1995-10-28') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('parul', 'singh', 'female', date '1995-10-27') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('parul', 'singh', 'female', date '1995-10-26') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('parul', 'singh', 'female', date '1995-10-25') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('parul', 'singh', 'female', date '1995-10-24') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('parul', 'singh', 'female', date '1995-10-23') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('parul', 'singh', 'female', date '1995-10-22') ;
INSERT 0 1
test=# INSERT INTO person (first_name, last_name, gender, date_of_birth) VALUES ('parul', 'singh', 'female', date '1995-10-21') ;
INSERT 0 1
test=# select * from person;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
4 | siddhant | singh | male | 1995-09-30 |
5 | siddhant | singh | male | 1995-09-30 |
6 | siddhant | singh | male | 1995-09-30 |
7 | siddhant | singh | male | 1995-09-30 |
8 | siddhant | singh | male | 1995-09-30 |
9 | siddhant | singh | male | 1995-09-30 |
10 | siddhant | singh | male | 1995-09-30 |
11 | siddhant | singh | male | 1995-09-30 |
12 | karan | singh | male | 1995-09-30 |
13 | karan | singh | male | 1995-09-30 |
14 | karan | singh | male | 1995-09-30 |
15 | karan | singh | male | 1995-09-30 |
16 | karan | singh | male | 1995-09-30 |
17 | Aakash | singh | male | 1995-09-30 |
18 | Aakash | singh | male | 1995-09-30 |
19 | Aakash | singh | male | 1995-09-30 |
20 | Aakash | singh | male | 1995-09-30 |
21 | parul | singh | female | 1995-09-30 |
22 | parul | singh | female | 1995-09-30 |
23 | parul | singh | female | 1995-09-30 |
24 | parul | singh | female | 1995-09-30 |
25 | parul | singh | female | 1995-09-30 |
26 | parul | singh | female | 1995-10-29 |
27 | parul | singh | female | 1995-10-28 |
28 | parul | singh | female | 1995-10-27 |
29 | parul | singh | female | 1995-10-26 |
30 | parul | singh | female | 1995-10-25 |
31 | parul | singh | female | 1995-10-24 |
32 | parul | singh | female | 1995-10-23 |
33 | parul | singh | female | 1995-10-22 |
34 | parul | singh | female | 1995-10-21 |
(34 rows)
test=# SELECT * FROM person
test-# WHERE date_of_birth
test-# BETWEEN DATE '1995-10-29' AND '1995-10-21'
test-# ;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
(0 rows)
test=# SELECT * FROM person WHERE gender LIKE '%e'
test-# ;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
4 | siddhant | singh | male | 1995-09-30 |
5 | siddhant | singh | male | 1995-09-30 |
6 | siddhant | singh | male | 1995-09-30 |
7 | siddhant | singh | male | 1995-09-30 |
8 | siddhant | singh | male | 1995-09-30 |
9 | siddhant | singh | male | 1995-09-30 |
10 | siddhant | singh | male | 1995-09-30 |
11 | siddhant | singh | male | 1995-09-30 |
12 | karan | singh | male | 1995-09-30 |
13 | karan | singh | male | 1995-09-30 |
14 | karan | singh | male | 1995-09-30 |
15 | karan | singh | male | 1995-09-30 |
16 | karan | singh | male | 1995-09-30 |
17 | Aakash | singh | male | 1995-09-30 |
18 | Aakash | singh | male | 1995-09-30 |
19 | Aakash | singh | male | 1995-09-30 |
20 | Aakash | singh | male | 1995-09-30 |
21 | parul | singh | female | 1995-09-30 |
22 | parul | singh | female | 1995-09-30 |
23 | parul | singh | female | 1995-09-30 |
24 | parul | singh | female | 1995-09-30 |
25 | parul | singh | female | 1995-09-30 |
26 | parul | singh | female | 1995-10-29 |
27 | parul | singh | female | 1995-10-28 |
28 | parul | singh | female | 1995-10-27 |
29 | parul | singh | female | 1995-10-26 |
30 | parul | singh | female | 1995-10-25 |
31 | parul | singh | female | 1995-10-24 |
32 | parul | singh | female | 1995-10-23 |
33 | parul | singh | female | 1995-10-22 |
34 | parul | singh | female | 1995-10-21 |
(34 rows)
test=# SELECT * FROM person WHERE gender LIKE 'm%' ; id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
4 | siddhant | singh | male | 1995-09-30 |
5 | siddhant | singh | male | 1995-09-30 |
6 | siddhant | singh | male | 1995-09-30 |
7 | siddhant | singh | male | 1995-09-30 |
8 | siddhant | singh | male | 1995-09-30 |
9 | siddhant | singh | male | 1995-09-30 |
10 | siddhant | singh | male | 1995-09-30 |
11 | siddhant | singh | male | 1995-09-30 |
12 | karan | singh | male | 1995-09-30 |
13 | karan | singh | male | 1995-09-30 |
14 | karan | singh | male | 1995-09-30 |
15 | karan | singh | male | 1995-09-30 |
16 | karan | singh | male | 1995-09-30 |
17 | Aakash | singh | male | 1995-09-30 |
18 | Aakash | singh | male | 1995-09-30 |
19 | Aakash | singh | male | 1995-09-30 |
20 | Aakash | singh | male | 1995-09-30 |
(20 rows)
test=# SELECT * FROM person WHERE gender LIKE '%F%' ;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
(0 rows)
test=# SELECT * FROM person WHERE gender LIKE '%female%' ;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
21 | parul | singh | female | 1995-09-30 |
22 | parul | singh | female | 1995-09-30 |
23 | parul | singh | female | 1995-09-30 |
24 | parul | singh | female | 1995-09-30 |
25 | parul | singh | female | 1995-09-30 |
26 | parul | singh | female | 1995-10-29 |
27 | parul | singh | female | 1995-10-28 |
28 | parul | singh | female | 1995-10-27 |
29 | parul | singh | female | 1995-10-26 |
30 | parul | singh | female | 1995-10-25 |
31 | parul | singh | female | 1995-10-24 |
32 | parul | singh | female | 1995-10-23 |
33 | parul | singh | female | 1995-10-22 |
34 | parul | singh | female | 1995-10-21 |
(14 rows)
test=# SELECT * FROM person WHERE gender LIKE '%f%' ;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
21 | parul | singh | female | 1995-09-30 |
22 | parul | singh | female | 1995-09-30 |
23 | parul | singh | female | 1995-09-30 |
24 | parul | singh | female | 1995-09-30 |
25 | parul | singh | female | 1995-09-30 |
26 | parul | singh | female | 1995-10-29 |
27 | parul | singh | female | 1995-10-28 |
28 | parul | singh | female | 1995-10-27 |
29 | parul | singh | female | 1995-10-26 |
30 | parul | singh | female | 1995-10-25 |
31 | parul | singh | female | 1995-10-24 |
32 | parul | singh | female | 1995-10-23 |
33 | parul | singh | female | 1995-10-22 |
34 | parul | singh | female | 1995-10-21 |
(14 rows)
test=# SELECT first_name, count(*) FROM person GROUP BY first_name;
first_name | count
------------+-------
parul | 14
Aakash | 4
karan | 5
sid | 1
siddhant | 9
sidd | 1
(6 rows)
test=# SELECT first_name, last_name, count(*) FROM person GROUP BY first_name AND last_name;
ERROR: argument of AND must be type boolean, not type character varying
LINE 1: ...st_name, last_name, count(*) FROM person GROUP BY first_name...
^
test=# SELECT first_name, last_name, count(*) FROM person GROUP BY first_name, last_name;
first_name | last_name | count
------------+-----------+-------
siddhant | singh | 9
sidd | singh | 1
parul | singh | 14
karan | singh | 5
sid | sngh | 1
Aakash | singh | 4
(6 rows)
test=# SELECT DISTINCT first_name, last_name, count(*) FROM person GROUP BY first_name, last_name;
first_name | last_name | count
------------+-----------+-------
siddhant | singh | 9
sid | sngh | 1
Aakash | singh | 4
parul | singh | 14
sidd | singh | 1
karan | singh | 5
(6 rows)
test=# SELECT DISTINCT first_name, last_name, count(*) FROM person GROUP BY first_name, last_name ORDER BY first_name, last_name;
first_name | last_name | count
------------+-----------+-------
Aakash | singh | 4
karan | singh | 5
parul | singh | 14
sid | sngh | 1
sidd | singh | 1
siddhant | singh | 9
(6 rows)
test=# SELECT DISTINCT first_name, last_name, count(*) FROM person GROUP BY first_name, last_name ORDER BY first_name, last_name ASC;
first_name | last_name | count
------------+-----------+-------
Aakash | singh | 4
karan | singh | 5
parul | singh | 14
sid | sngh | 1
sidd | singh | 1
siddhant | singh | 9
(6 rows)
test=# SELECT DISTINCT first_name, last_name, count(*) FROM person GROUP BY first_name, last_name ORDER BY first_name, last_name DESC;
first_name | last_name | count
------------+-----------+-------
Aakash | singh | 4
karan | singh | 5
parul | singh | 14
sid | sngh | 1
sidd | singh | 1
siddhant | singh | 9
(6 rows)
test=# SELECT DISTINCT first_name, last_name, count(*) FROM person GROUP BY first_name, last_name ORDER BY first_name, last_name DESC;
first_name | last_name | count
------------+-----------+-------
Aakash | singh | 4
karan | singh | 5
parul | singh | 14
sid | sngh | 1
sidd | singh | 1
siddhant | singh | 9
(6 rows)
test=# SELECT DISTINCT first_name, last_name, count(*) FROM person GROUP BY first_name, last_name;
first_name | last_name | count
------------+-----------+-------
siddhant | singh | 9
sid | sngh | 1
Aakash | singh | 4
parul | singh | 14
sidd | singh | 1
karan | singh | 5
(6 rows)
test=# SELECT DISTINCT first_name, count(*) FROM person GROUP BY first_name;
first_name | count
------------+-------
parul | 14
sid | 1
karan | 5
sidd | 1
Aakash | 4
siddhant | 9
(6 rows)
test=# SELECT DISTINCT first_name, count(*) FROM person GROUP BY first_name ORDER BY first_name ASC;
first_name | count
------------+-------
Aakash | 4
karan | 5
parul | 14
sid | 1
sidd | 1
siddhant | 9
(6 rows)
test=# SELECT DISTINCT first_name, count(*) FROM person GROUP BY first_name ORDER BY first_name DESC;
first_name | count
------------+-------
siddhant | 9
sidd | 1
sid | 1
parul | 14
karan | 5
Aakash | 4
(6 rows)
test=# SELECT * FROM person
test-# ;
id | first_name | last_name | gender | date_of_birth | email
----+------------+-----------+--------+---------------+-------
1 | siddhant | singh | male | 1995-09-30 |
2 | sidd | singh | male | 1995-10-30 |
3 | sid | sngh | male | 1995-12-30 |
4 | siddhant | singh | male | 1995-09-30 |
5 | siddhant | singh | male | 1995-09-30 |
6 | siddhant | singh | male | 1995-09-30 |
7 | siddhant | singh | male | 1995-09-30 |
8 | siddhant | singh | male | 1995-09-30 |
9 | siddhant | singh | male | 1995-09-30 |
10 | siddhant | singh | male | 1995-09-30 |
11 | siddhant | singh | male | 1995-09-30 |
12 | karan | singh | male | 1995-09-30 |
13 | karan | singh | male | 1995-09-30 |
14 | karan | singh | male | 1995-09-30 |
15 | karan | singh | male | 1995-09-30 |
16 | karan | singh | male | 1995-09-30 |
17 | Aakash | singh | male | 1995-09-30 |
18 | Aakash | singh | male | 1995-09-30 |
19 | Aakash | singh | male | 1995-09-30 |
20 | Aakash | singh | male | 1995-09-30 |
21 | parul | singh | female | 1995-09-30 |
22 | parul | singh | female | 1995-09-30 |
23 | parul | singh | female | 1995-09-30 |
24 | parul | singh | female | 1995-09-30 |
25 | parul | singh | female | 1995-09-30 |
26 | parul | singh | female | 1995-10-29 |
27 | parul | singh | female | 1995-10-28 |
28 | parul | singh | female | 1995-10-27 |
29 | parul | singh | female | 1995-10-26 |
30 | parul | singh | female | 1995-10-25 |
31 | parul | singh | female | 1995-10-24 |
32 | parul | singh | female | 1995-10-23 |
33 | parul | singh | female | 1995-10-22 |
34 | parul | singh | female | 1995-10-21 |
(34 rows)
test=# SELECT first_name, count(*) FROM person GROUP BY first_name, HAVING count(*) > 9;
ERROR: syntax error at or near "HAVING"
LINE 1: ...t_name, count(*) FROM person GROUP BY first_name, HAVING cou...
^
test=# SELECT first_name, count(*) FROM person GROUP BY first_name HAVING count(*) > 9;
first_name | count
------------+-------
parul | 14
(1 row)
test=# SELECT first_name, count(*) FROM person GROUP BY first_name HAVING count(*) < 9;
first_name | count
------------+-------
Aakash | 4
karan | 5
sid | 1
sidd | 1
(4 rows)
test=# SELECT first_name, count(*) FROM person GROUP BY first_name HAVING count(*) < 9 ORDER BY ASC;
ERROR: syntax error at or near "ASC"
LINE 1: ...person GROUP BY first_name HAVING count(*) < 9 ORDER BY ASC;
^
test=# SELECT first_name, count(*) FROM person GROUP BY first_name HAVING count(*) < 9 ORDER BY first_name ASC;
first_name | count
------------+-------
Aakash | 4
karan | 5
sid | 1
sidd | 1
(4 rows)
test=# SELECT first_name, count(*) FROM person GROUP BY first_name HAVING count(*) < 9 ORDER BY count ASC;
first_name | count
------------+-------
sid | 1
sidd | 1
Aakash | 4
karan | 5
(4 rows)
test=# SELECT first_name, count(*) FROM person GROUP BY first_name HAVING count(*) < 9 ORDER BY count DESC;
first_name | count
------------+-------
karan | 5
Aakash | 4
sid | 1
sidd | 1
(4 rows)
test=# \Q
invalid command \Q
Try \? for help.
test=# \q
icicle@Icicles-MacBook-Air ~ % psql -h localhost -p 5432 -U icicle test
psql (12.0)
Type "help" for help.
test=# \q
icicle@Icicles-MacBook-Air ~ % psql -h localhost -p 5432 -U icicle test
psql (12.0)
Type "help" for help.
test=# \?
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display results in crosstab
\errverbose show most recent error message at maximum verbosity
\g [FILE] or ; execute query (and send results to file or |pipe)
\gdesc describe result of query, without executing it
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store results in psql variables
\gx [FILE] as \g, but forces expanded output mode
\q quit psql
\watch [SEC] execute query every SEC seconds
Help
\? [commands] show help on backslash commands
\? options show help on psql command-line options
\? variables show help on special variables
\h [NAME] help on syntax of SQL commands, * for all commands
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\ev [VIEWNAME [LINE]] edit view definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
Input/Output
\copy ... perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard output
\i FILE execute commands from file
\ir FILE as \i, but relative to location of current script
\o [FILE] send all query results to file or |pipe
\qecho [STRING] write string to query output stream (see \o)
Conditional
\if EXPR begin conditional block
\elif EXPR alternative within current conditional block
\else final alternative within current conditional block
\endif end conditional block
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\dA[+] [PATTERN] list access methods
\db[+] [PATTERN] list tablespaces
\dc[S+] [PATTERN] list conversions
\dC[+] [PATTERN] list casts
\dd[S] [PATTERN] show object descriptions not displayed elsewhere
\dD[S+] [PATTERN] list domains
\ddp [PATTERN] list default privileges
\dE[S+] [PATTERN] list foreign tables
\det[+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[anptw][S+] [PATRN] list [only agg/normal/procedures/trigger/window] functions
\dF[+] [PATTERN] list text search configurations
\dFd[+] [PATTERN] list text search dictionaries
\dFp[+] [PATTERN] list text search parsers
\dFt[+] [PATTERN] list text search templates
\dg[S+] [PATTERN] list roles
\di[S+] [PATTERN] list indexes
\dl list large objects, same as \lo_list
\dL[S+] [PATTERN] list procedural languages
\dm[S+] [PATTERN] list materialized views
\dn[S+] [PATTERN] list schemas
\do[S] [PATTERN] list operators
\dO[S+] [PATTERN] list collations
\dp [PATTERN] list table, view, and sequence access privileges
\dP[itn+] [PATTERN] list [only index/table] partitioned relations [n=nested]
\drds [PATRN1 [PATRN2]] list per-database role settings
\dRp[+] [PATTERN] list replication publications
\dRs[+] [PATTERN] list replication subscriptions
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
test=# \i /Users/icicle/downloads/car.sql
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
test=# SELECT * FROM CAR;
id | make | model | price
-----+---------------+-------------------------+------------
1 | Mitsubishi | Galant | €124492,26
2 | Bentley | Continental Flying Spur | €678776,25
3 | GMC | Yukon | €58968,57
4 | Plymouth | Prowler | €699699,34
5 | Volkswagen | Eos | €978610,01
6 | Cadillac | STS-V | €449027,81
7 | Pontiac | Grand Prix | €338661,61
8 | Pontiac | GTO | €37085,37
9 | Ford | LTD Crown Victoria | €439552,23
10 | Maserati | GranTurismo | €341308,65
11 | Ford | Taurus | €685439,89
12 | Volkswagen | rio | €32844,88
13 | Mercury | Grand Marquis | €663197,17
14 | Subaru | XT | €14521,00
15 | Toyota | MR2 | €911634,08
16 | Audi | A6 | €133278,73
17 | GMC | Yukon Denali | €802508,95
18 | Mercury | Sable | €518042,27
19 | Mercury | Lynx | €371344,14
20 | Volkswagen | New Beetle | €154436,86
21 | Ford | F-Series | €652036,76
22 | Toyota | Camry | €786034,25
23 | Acura | RL | €438670,24
24 | Audi | A3 | €443048,04
25 | Studebaker | Avanti | €17931,17
26 | Lincoln | Navigator | €677536,07
27 | Isuzu | Trooper | €148345,13
28 | Peugeot | 207 | €361400,00
29 | Ford | Windstar | €974189,40
30 | Ford | LTD | €875376,65
31 | Toyota | Avalon | €436195,92
32 | GMC | Vandura 2500 | €269800,04
33 | Ford | Thunderbird | €284099,12
34 | Toyota | Matrix | €846950,75
35 | Mercedes-Benz | S-Class | €628124,71
36 | Chevrolet | Blazer | €818148,19
37 | Porsche | 928 | €863998,40
38 | Chevrolet | Sportvan G20 | €831169,98
39 | Mercedes-Benz | M-Class | €33540,13
40 | Mercedes-Benz | CL-Class | €887137,12
41 | Ford | Bronco | €354104,02
42 | Porsche | 924 S | €28448,09
43 | Toyota | Sienna | €99714,26
44 | BMW | Z4 M Roadster | €236507,14
45 | Suzuki | SX4 | €43375,47
46 | Ford | Taurus | €84540,78
47 | Mercedes-Benz | M-Class | €228338,26
48 | Chevrolet | Tahoe | €868233,71
49 | Suzuki | XL7 | €833039,05
50 | Ford | F-Series Super Duty | €339178,39
51 | Audi | 80 | €661287,60
52 | Ford | E-Series | €619817,92
53 | Hyundai | Accent | €333912,67
54 | Ford | Ranger | €113671,37
55 | GMC | 2500 Club Coupe | €243110,27
56 | Ford | Contour | €877844,99
57 | Acura | TL | €306733,96
58 | Porsche | Cayman | €118112,81
59 | Chevrolet | Caprice Classic | €345037,83
60 | Honda | CR-X | €245420,83
61 | Chevrolet | Cobalt | €859179,21
62 | Ford | LTD Crown Victoria | €525947,64
63 | Isuzu | Trooper | €154329,86
64 | Mercedes-Benz | E-Class | €532884,04
65 | Land Rover | Discovery Series II | €639074,44
66 | Chrysler | Cirrus | €419641,86
67 | Jaguar | S-Type | €110203,84
68 | BMW | X5 | €588025,75
69 | Chevrolet | Express 3500 | €497423,67
70 | Mercury | Topaz | €793657,72
71 | Toyota | Venza | €385804,23
72 | Chevrolet | Impala | €762945,86
73 | Pontiac | LeMans | €428959,38
74 | Audi | A8 | €831748,75
75 | Ford | Th!nk | €54807,38
76 | Kia | Sephia | €388906,13
77 | Oldsmobile | Silhouette | €533170,91
78 | Mercedes-Benz | W201 | €572651,75
79 | BMW | X5 | €607689,70
80 | Jaguar | X-Type | €478822,34
81 | BMW | Z8 | €770229,38
82 | Honda | Civic GX | €353071,23
83 | BMW | Z4 M | €41516,12
84 | Dodge | D350 Club | €596665,66
85 | Volvo | XC90 | €309137,94
86 | Hyundai | Veracruz | €909895,03
87 | Chevrolet | Sportvan G10 | €724576,94
88 | Lamborghini | Gallardo | €308658,66
89 | Eagle | Talon | €556888,52
90 | Mercedes-Benz | G-Class | €258482,28
91 | Scion | xB | €858875,91
92 | Ford | Windstar | €681720,54
93 | Chevrolet | Beretta | €742572,94
94 | Ford | Contour | €308935,72
95 | Pontiac | LeMans | €255571,98
96 | Toyota | Corolla | €260562,36
97 | Lamborghini | Murciélago | €950715,48
98 | Nissan | Sentra | €156607,72
99 | Mitsubishi | Outlander Sport | €950437,85
100 | BMW | M6 | €375099,38
(100 rows)
test=# SELECT MAX(price) from CAR;
max
-----------
€99714,26
(1 row)
test=# SELECT MIN(price) from CAR;
min
------------
€110203,84
(1 row)
test=# SELECT AVG(price) from CAR;
ERROR: function avg(character varying) does not exist
LINE 1: SELECT AVG(price) from CAR;
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
test=# SELECT AVG(price) from CAR;
ERROR: function avg(character varying) does not exist
LINE 1: SELECT AVG(price) from CAR;
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
test=# SELECT AVG(price) from car;
ERROR: function avg(character varying) does not exist
LINE 1: SELECT AVG(price) from car;
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
test=# SELECT AVG(price) FROM car;
ERROR: function avg(character varying) does not exist
LINE 1: SELECT AVG(price) FROM car;
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
test=# SELECT MAX(price) FROM car;
max
-----------
€99714,26
(1 row)
test=# SELECT Min(price) FROM car;
min
------------
€110203,84
(1 row)
test=# SELECT MIN(price) FROM car;
min
------------
€110203,84
(1 row)
test=# SELECT make, model, MAX(price) GROUP BY make, model
test-# ;
ERROR: column "make" does not exist
LINE 1: SELECT make, model, MAX(price) GROUP BY make, model
^
test=# \l List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
icicle | icicle | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | icicle | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
test=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------+----------+--------
public | car | table | icicle
public | car_id_seq | sequence | icicle
public | person | table | icicle
public | person_id_seq | sequence | icicle
(4 rows)
test=# \ car
invalid command \
Try \? for help.
test=# SELECT NOW();
now
----------------------------------
2019-11-13 17:57:24.933572+05:30
(1 row)
test=# SELECT DATE();
ERROR: function date() does not exist
LINE 1: SELECT DATE();
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
test=# SELECT DATE()::DATE;
ERROR: function date() does not exist
LINE 1: SELECT DATE()::DATE;
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
test=# SELECT NOW()::DATE;
now
------------
2019-11-13
(1 row)
test=# SELECT NOW()::MONTH;
ERROR: type "month" does not exist
LINE 1: SELECT NOW()::MONTH;
^
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment