Psql show data in table

Continue

Psql show data in table

el@defiant$ /bin/psql -h localhost --username=pgadmin --list Or the command stated more simply: psql -U pgadmin -l Those commands print this on the terminal: List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- kurz_prod | pgadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | pgadmin | pgadmin | 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 (5 rows) These are the available databases. In PSQL these commands list the tables available You have to specify a database before you can list the tables in that database. el@defiant$ psql -U pgadmin -d kurz_prod This brings you to a psql terminal: kurz_prod=# Use the command \d meaning show all tables, views, and sequences kurz_prod=# \d This prints: List of relations Schema | Name | Type | Owner --------+---------+----------+--------- public | mytable | table | pgadmin public | testing | sequence | pgadmin (2 rows) Then, to exit the psql terminal, type \q and press enter. Or Ctrl-D does the same thing. These are the tables in that database. NEWBEDEVPythonJavascriptLinuxCheat sheet I realize I'm a bit late to this party, but this was the first result to my Google Search so I figured I'd answer with what I came up with. You can get pretty far toward a solution with this query to get the columns: SELECT * FROM information_schema.columns WHERE table_schema = 'YOURSCHEMA' AND table_name = 'YOURTABLE' ORDER BY ordinal_position; And then this query for most common indexes: SELECT c.oid, c.relname, a.attname, a.attnum, i.indisprimary, i.indisunique FROM pg_index AS i, pg_class AS c, pg_attribute AS a WHERE i.indexrelid = c.oid AND i.indexrelid = a.attrelid AND i.indrelid = 'YOURSCHEMA.YOURTABLE'::regclass ORDER BY" => "c.oid, a.attnum Then it is a matter of building out the query string(s) in the right format. Is there a way to show all the content inside a table by using psql command line interface? I can use \list to show all the databases, \d to show all the tables, but how can I show all the data in a table? 1 We use cookies to personalize the website for you and to analyze the use of our website. You consent to this by clicking on "I consent" or by continuing your use of this website. Further information about cookies can be found in our Privacy Policy. EDB Team Sep 30, 2020 In this article we are going to look at what an SQL query is and how to use it with PostgreSQL databases in different situations. We provide 50 examples of types of SQL, queries along with descriptions of their functions and how to use them in PostgreSQL. SQL is short for Structured Query Language. Originally, it used to be called SEQUEL (Structured English Query Language) and was used for storing and manipulating data in databases. Today SQL is used to perform all types of data operations in relational database management systems (RDBMS). SQL is a powerful language where we can perform a wide range of operations: execute queries fetch data insert, update, and delete records in a database (DML operations) create new objects in a database (DDL operations) set permissions on tables, procedures, functions, and views and much, much more... The American National Standards Institute (ANSI) created a standard for SQL in 1986, and it was adopted by the International Organization for Standardization (ISO) in 1987. For each RDBMS to be compliant with the ANSI standard, they all have to support the major commands, like DML, in a similar manner as closely as possible. More information about the ANSI standard can be found on the SQL Wikipedia page. SQL follows ANSI/ISO standards, but there are different versions of the SQL language used by different database systems. For example, in PostgreSQL we can perform an INSERT operation using RETURNING clauses, which not all other databases can do. [centos@tushar-ldap-docker bin]$ ./psql postgres psql.bin (11.9.17) Type "help" for help. postgres=# create table tyu(n int); CREATE TABLE postgres=# insert into tyu values(1),(2) returning *; n --- 1 2 (2 rows) INSERT 0 2 But to be compliant with the ANSI standard, all databases support commands (like DELETE, UPDATE, SELECT, INSERT) in the same way--that is, the syntax should work anywhere. If we check the PostgreSQL documentation of the INSERT statement, its conformity to the SQL standard is discussed in the page's Compatibility section: INSERT conforms to the SQL standard, except that the RETURNING clause is a PostgreSQL extension, as is the ability to use WITH with INSERT, and the ability to specify an alternative action with ON CONFLICT. Also, the case in which a column name list is omitted, but not all the columns are filled from the VALUES clause or query, is disallowed by the standard. The SQL standard specifies that OVERRIDING SYSTEM VALUE can only be specified if an identity column that is generated always exists. PostgreSQL allows the clause in any case and ignores it if it is not applicable. The PostgreSQL documentation includes clear descriptions of any differences from the SQL standard for any command, including: DML (INSERT, SELECT, DELETE) DDL (CREATE, ALTER, TRUNCATE, DROP, RENAME) TCL (COMMIT, ROLLBACK, SAVEPOINT) DCL (GRANT, REVOKE) under each command's Compatibility section. Now let's explore some examples of common and useful PostgreSQL queries that can be used in various situations. CREATE TABLE is a keyword that will create a new, initially empty table in the database. The table will be owned by the user who has issued this command. postgres=# create table dummy_table(name varchar(20),address text,age int); CREATE TABLE The INSERT command is used to insert data into a table: postgres=# insert into dummy_table values('XYZ','location-A',25); INSERT 0 1 postgres=# insert into dummy_table values('ABC','location-B',35); INSERT 0 1 postgres=# insert into dummy_table values('DEF','locationC',40); INSERT 0 1 postgres=# insert into dummy_table values('PQR','location-D',54); INSERT 0 1 The SELECT command (when used without the optional WHERE condition) is used to fetch all data from a database table: postgres=# select * from dummy_table; name | address | age ---------+--------------+ ----- XYZ | location-A | 25 ABC | location-B | 35 DEF | location-C | 40 PQR | location-D | 54 (4 rows) UPDATE is used to make updates to the data or row(s) of a database table. In the example below we use UPDATE to change the age of a person whose name is `PQR': postgres=# update dummy_table set age=50 where name='PQR'; UPDATE 1 postgres=# select * from dummy_table; name | address | age --------+--------------+------- XYZ | location-A | 25 ABC | location-B | 35 DEF | location-C | 40 PQR | location-D | 50 (4 rows) Next, we'll use the UPDATE command to change the name and age of a person whose address is `location-D': postgres=# update dummy_table set name='GHI',age=54 where address='location-D'; UPDATE 1 postgres=# select * from dummy_table; name | address | age ------+------------+----- XYZ | location-A | 25 ABC | location-B | 35 DEF | location-C | 40 GHI | location-D | 54 (4 rows) postgres=# If we want to modify all the values in the address and age columns in dummy_table, then we do not need to use the WHERE clause. The UPDATE query would look like this: postgres=# update dummy_table set age=54,address='location-X'; UPDATE 4 postgres=# select * from dummy_table ; name | address | age ------+------------+-------- XYZ | location-X | 54 ABC | location-X | 54 DEF | location-X | 54 GHI | location-X | 54 (4 rows) postgres=# A RETURNING clause returns the updated rows. This is optional in UPDATE: postgres=# update dummy_table set age=30 where name='XYZ' returning age as age_no; age_no --------- 30 (1 row) UPDATE 1 It is always recommended to perform such operations under transaction blocks (i.e., BEGIN...COMMIT/ROLLBACK;), so we have the option to roll back the operation. 5. DELETE query in PostgreSQL The DELETE command is used to delete row(s). It can be used with or without the optional WHERE condition, but take note: if the WHERE condition is missing, the command will delete all rows, leaving you with an empty table. In this example, we are deleting one row whose age column has the value 65: postgres=# delete from dummy_table where age=65; DELETE 1 postgres=# In PostgreSQL, with the help of comparison operators we can find results where the value in a column is not equal to the specified condition or value. Less than or equal to query: postgres=# select * from dummy_table where age =50; name | address | age ------+------------+----- PQR | location-D | 50 (1 row) Not equal to query: postgres=# select * from dummy_table where age50; name | address | age ------+------------+----- XYZ | location-A | 25 ABC | location-B | 35 DEF | location-C | 40 (3 rows) Equal to query: postgres=# select * from dummy_table where age=50; name | address | age ------+------------+----- PQR | location-D | 50 (1 row) The SELECT DISTINCT statement is used to return only distinct values from the table. It removes any duplicate values. SELECT query without DISTINCT clause postgres=# select age from dummy_table order by 1; age ----- 1 1 2 2 3 (5 rows) SELECT query with DISTINCT clause postgres=# select distinct age from dummy_table order by 1; age ----- 1 2 3 (3 rows) The TRUNCATE command is used to empty a table: postgres=# truncate table dummy_table; TRUNCATE TABLE 9. DROP TABLE query in PostgreSQL This DROP TABLE command is used to drop a table from the database: postgresql=# drop table if exists dummy; NOTICE: table "dummy" does not exist, skipping DROP TABLE This command has removed the full table, including any associated data, indexes, rules, triggers, and constraints for that table. 10. CREATE VIEW query in PostgreSQL The CREATE VIEW command is used to generate views. Views are pseudo-tables, which are used to present a full table, subset, or select columns from the underlying table: postgres=# create or replace view vi as select * from dummy_table where age is NULL; CREATE VIEW Using the syntax in the example below, we can create a table using a SELECT statement: postgres=# select 'My name is X' as col1 , 10 as col2, 'Address is -XYZ location' as col3 into new_table; SELECT 1 postgres=# select * from new_table ; col1 | col2 | col3 ---------------+------+-------------------------- My name is X | 10 | Address is -XYZ location (1 row) We can command a query to timeout after a certain period with the help of GUC parameters (short for grand unified configuration) like statement_timeout, which aborts any statement that takes more than the specified number of milliseconds: postgresql=# set statement_timeout=10; SET postgresql=# select pg_sleep(20); ERROR: canceling statement due to statement timeout The CREATE SEQUENCE command is a sequential number generator. Once the sequence is created, we can use the sequence's nextval and currval functions to insert values into a table: postgres=# create sequence seq; CREATE SEQUENCE postgres=# create table tab(n int); CREATE TABLE postgres=# insert into tab values (nextval('seq')); INSERT 0 1 postgres=# insert into tab values (currval('seq')); INSERT 0 1 postgres=# insert into tab values (nextval('seq')); INSERT 0 1 postgres=# select * from tab; n --- 1 1 2 (3 rows) PostgreSQL doesn't directly support BLOBs (binary large objects), but we can work with them using the following methods: Let's assume you have an image (in png format) downloaded in the /home/edb/ folder: [edb@localhost]$ ls /home/edb/mypic.png /home/edb/mypic.png We want to store this image in the PostgreSQL database. Go to the bin folder of your PostgreSQL installation and connect to the psql terminal: postgres=# Create table testing(n int,n1 oid); CREATE TABLE postgres=# insert into testing values (1,lo_import('/home/edb/mypic.png')); INSERT 0 1 The lo_import() function loads the named file into pg_largeobject and returns an OID (object identifier) value that will refer to the large object. Selecting the testing table will show just the OID and not the bits that have made up this photo. The ILIKE operator is a matching function similar to the LIKE operator, but with the advantage that it matches valus case-insensitively. postgres=# select * from ted; n ----- TAR TaR Tar tar (4 rows) Using ILIKE in a WHERE condition postgres=# select * from ted where n ilike 'TAR%'; n ----- TAR TaR Tar tar (4 rows) Hierarchical queries are ones where the results have a structured or parent-child relationship and are displayed in a tree structure. To see how hierarchical queries work, create a dummy table: create table test_table( emp_no int, ename char(5), job char(9), manager_no int ); Insert data into `test_table': insert into test_table values(10,'A1','CEO',null); insert into test_table values(11, 'B1', 'VP', 10); insert into test_table values(12, 'B2', 'VP', 10); insert into test_table values(13, 'B3', 'VP', 10); insert into test_table values(14, 'C1', 'DIRECTOR', 13); insert into test_table values(15, `C2', `DIRECTOR', 13); insert into test_table values(16, 'D1', 'MANAGER', 15); insert into test_table values(17 ,'E1', 'ENGINEER', 11); insert into test_table values(18, 'E2', 'ENGINEER', 11); We can perform hierarchical queries on this table using the methods below. The length function returns the number of characters or number of bytes in a specified string variable. Basic SELECT query postgres=# select name,age from dummy_table; name | age ------+----- XYZ | 25 ABC | 35 DEF | 40 PQR | 54 PQR | (5 rows) Query with length function for column name and age postgres=# select length(name),length(age) from dummy_table; length | length --------+-------- 3 | 2 3 | 2 3 | 2 3 | 2 3 | (5 rows) Say that while selecting a given function, we receive the error message below: postgresql=# create or replace function f(n int) returns int as $$ begin select now(); return 1; end; $$ language 'plpgsql'; CREATE FUNCTION postgres=# select f(9); ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. To avoid such errors, we can either use PERFORM or declare a variable and use it in a SELECT INTO statement: Using PERFORM postgres=# create or replace function f(n int) returns int as $$ begin perform now(); return 1; end; $$ language 'plpgsql'; CREATE FUNCTION postgresql=# select f(9); f --- 1 (1 row) Declaring a variable and using it in a SELECT INTO statement postgres=# create or replace function f(n int) returns int as $$ declare a date; begin select now() into a; raise notice `%s',a; return 1; end; $$ language 'plpgsql'; CREATE FUNCTION postgresql=# select f(9); NOTICE: 24-SEP-20 13:15:46.23388s f --- 1 (1 row) With the help of the COPY command, we can export data from a table to an outside text file as well as import data from a text file into a table. Exporting data from a table to a text file postgres=# copy dummy_table to '/tmp/abc.txt'; COPY 5 postgres=# \! cat /tmp/abc.txt XYZ location-A 25 ABC location-B 35 DEF location-C 40 PQR location-D 50 CXC 1 50 Importing data from a text file into a table postgres=# copy dummy_table from '/tmp/abc.txt'; COPY 5 postgres=#WITH RECURSIVE cte AS ( SELECT emp_no, ename, manager_no, 1 AS level FROM test_table where manager_no is null UNION ALL SELECT e.emp_no, e.ename, e.manager_no, c.level + 1 FROM cte c JOIN test_table e ON e.manager_no = c.emp_no ) SELECT * FROM cte; emp_no | ename | manager_no | level -----------+----------+------------------+------- 10 | A1 | | 1 11 | B1 | 10 | 2 12 | B2 | 10 | 2 13 | B3 | 10 | 2 14 | C1 | 13 | 3 17 | E1 | 11 | 3 18 | E2 | 11 | 3 15 | C2 | 13 | 3 16 | D1 | 15 | 4 (9 rows) postgres=# The tablefunc extension is a contrib module that resides in the contrib/ folder in PostgreSQL sources. First, create the tablefunc extension: postgres=# CREATE EXTENSION tablefunc; CREATE EXTENSION postgres=# Then, use tablefunc's connectby function to display results hierarchically: postgres=# SELECT * FROM connectby('dummy_table', 'emp_no', 'manager_no', '10', 0, '->') AS t(emp_no int, manager_no int, level int, ord text) order by emp_no; emp_no | manager_no | level | ord --------+------------+-------+---------------- 10 | | 0 | 10 11 | 10 | 1 | 10->11 12 | 10 | 1 | 10->12 13 | 10 | 1 | 10->13 14 | 13 | 2 | 10->13->14 15 | 13 | 2 | 10->13->15 16 | 15 | 3 | 10->13->15->16 17 | 11 | 2 | 10->11->17 18 | 11 | 2 | 10->11->18 (9 rows) postgres=# The following query can be used to show all the databases created: postgres=# select oid,datname from pg_database; oid | datname -----------+----------- 13743 | postgres 1 | template1 13742 | template0 (3 rows) We can also list out all the database names using the \l command at the psql prompt. We can check the time needed for a query to execute by enabling \timing at the psql prompt: postgres=# \timing Timing is on. The SELECT query will now show the execution time: postgres=# select * from dummy_table; name | address | age ------+------------+-------- XYZ | location-A | 25 ABC | location-B | 35 DEF | location-C | 40 PQR | location-D | 50 CXC | 1 | 50 (5 rows) Time: 0.440 ms postgres=# Dynamic SQL is used to reduce repetitive tasks when it comes to querying. Dynamic SQL queries are not cached in memory. postgres=# do postgres-# $$ postgres$# begin postgres$# execute 'select * from dummy_table'; postgres$# end; postgres$# $$; DO The COUNT query returns the number of rows in a table. If we use (*) this will include null values; otherwise null values will be excluded. postgres=# select count(*) from dummy_table; count ------- 5 (1 row) postgres=# select count(avg) from dummy_table; count ------- 4 (1 row) The LIMIT clause is used to limit the data amount returned by the SELECT statement. The query below will display only 1 row: postgres=# select * from dummy_table limit 1; name | address | age ------+------------+----- XYZ | location-A | 25 (1 row) OFFSET is used when we want to skip a particular number of rows: postgres=# select * from dummy_table offset 4; name | address | age ------+--------+----- cxc | 1 | 50 (1 row) We can use conditional statements like IF ... ELSE in an anonymous block. The example below checks if the values of variables abc and xyz are matching and prints the result-- i.e., 150: postgres=# Do $$ Declare abc int; xyz int; begin abc:=100; xyz:=abc; if abc=xyz then xyz=150; raise notice '%',xyz; else end if; end; $$ ; NOTICE: 150 DO We can use UPDATE with a JOIN and WHERE clause when we want to update the values from one table (table X) based on values from another table (table Y): postgres=# create table X(n int, n1 char(10)); CREATE TABLE postgres=# insert into X values (1,'abc'); INSERT 0 1 postgres=# insert into X values (2,'xyz'); INSERT 0 1 postgres=# insert into X values (3,'pqr'); INSERT 0 1 postgres=# create table Y(n int, n1 char(10)); CREATE TABLE postgres=# insert into Y values (1,''); INSERT 0 1 postgres=# insert into Y values (2,''); INSERT 0 1 postgres=# insert into Y values (5,'axyz'); INSERT 0 1 postgres=# update Y set n1=X.n1 from X where X.n=Y.n; UPDATE 2 postgres=# select * from Y; n | n1 ---+------------ 5 | axyz 1 | abc 2 | xyz (3 rows) postgres=# The INNER JOIN command will find rows from two (or more) tables where the specified columns data in the tables match: postgres=# select * from x inner join y on x.n1 = y.n1; n | n1 | n | n1 ---+------------+---+------------ 1 | abc | 1 | abc 2 | xyz | 2 | xyz (2 rows) postgres=# The CASE expression is a generic conditional expression, similar to the IF ... ELSE statement. postgres=# SELECT age, CASE age WHEN 25 THEN 'one' WHEN 50 THEN 'two' ELSE 'other' END FROM dummy_table; age | case -----+------- 25 | one 35 | other 40 | other 50 | two 50 | two (5 rows) Recursive queries are used to deal with hierarchical queries or tree-structured data. The structure of a WITH RECURSIVE query is always: a) Non-recursive term b) UNION (or UNION ALL), then a recursive term where the recursive term includes a reference to the query's output. CREATE TABLE emp_test ( id int, ename varchar(255), emanager int ); INSERT INTO emp_test VALUES (1, 'abc', null); INSERT INTO emp_test VALUES (2, 'xyz', 1); INSERT INTO emp_test VALUES (3, 'def', 2); INSERT INTO emp_test VALUES (4, 'cde', 1); INSERT INTO emp_test VALUES (5, 'qrs', 2); INSERT INTO emp_test VALUES (9, 'iop', 3); INSERT INTO emp_test VALUES (10, 'klm', 4); The recursive query below will give all the reports in a certain order: postgres=#WITH RECURSIVE emp_testnew AS ( SELECT id, ename, emanager FROM emp_test WHERE id = 2 UNION ALL SELECT e.id, e.ename, e.emanager FROM emp_test e INNER JOIN emp_testnew e1 ON e1.id = e.emanager ) SELECT * FROM emp_testnew; id | ename | emanager ----+-------+---------- 2 | xyz | 1 3 | def | 2 5 | qrs | 2 9 | iop | 3 (4 rows) postgres=# Using the pg_stat_statements module, we can track execution statistics for all SQL statements. To do this, we need to create an extension and add in shared_preload_libraries inside the postgresql.conf file: postgres=# create extension pg_stat_statements; CREATE EXTENSION postgres=# show shared_preload_libraries ; shared_preload_libraries ------------------------------------------------- $libdir/pg_stat_statements (1 row) postgres=# select query from pg_stat_statements where query like 'create table%'; -[ RECORD 1 ]------------------- query | create table test(n int) We can also configure PostgreSQL to generate log output by enabling these parameters in the postgresql.conf file: logging_collector = on log_directory = 'log' log_filename = `postgresql-%Y-%m-%d_%H%M%S.log' log_destination = `stderr' Log file will be created under the pg_log directory which resides under the data folder. [centos@tushar-ldap-docker bin]$ ls data/log postgresql-2020-09-17_150932.log postgresql-2020-0919_000000.log [centos@tushar-ldap-docker bin]$ Queries will be recorded in these files. We can declare a variable in PostgreSQL at the psql prompt: postgres=# \set cond 50 Using a variable in a WHERE condition postgres=# select * from dummy_table where age=:cond; name | address | age ------+------------+----- PQR | location-D | 50 (1 row) OR postgres=# \set cond 50 postgres=# select :cond+100 ; ?column? ---------- 150 (1 row) PostgreSQL offers functions for date and time that can be used in queries. postgres=# select now(); now ---------------------------------- 22-SEP-20 03:08:42.636385 +05:30 (1 row) postgres=# select current_date; current_date -------------- 22-SEP-20 (1 row) postgres=# select current_time; current_time ----------------------- 03:08:53.648466+05:30 (1 row) We can also perform a date range query to find rows with values between two time stamps: postgres=# create table datetable(n int,n1 date); CREATE TABLE postgres=# insert into datetable values (1,'12-01-1980'); INSERT 0 1 postgres=# insert into datetable values (2,'12-01-2020'); INSERT 0 1 postgres=# insert into datetable values (3,'12-01-2000'); INSERT 0 1 postgres=# select * from datetable where n1 between '12-01-1980' and '12-01-2000'; n | n1 ---+-------------------- 1 | 12-JAN-80 00:00:00 3 | 12-JAN-00 00:00:00 (2 rows) When a PL/pgSQL function is declared to return a SETOF some data type, the return is specified by a RETURN QUERY command: postgres=# CREATE FUNCTION get(int) RETURNS SETOF integer AS $BODY$ BEGIN RETURN QUERY SELECT age FROM dummy_table WHERE age >= $1 ; RETURN; END $BODY$ LANGUAGE plpgsql; postgres=# select * from get(9); get ----- 25 35 40 50 (4 rows) Parallel queries in PostgreSQL allow you to finish queries faster by utilizing many CPUs. These GUCs parameters are set in postgresql.conf file: #max_parallel_maintenance_workers = 2 # taken from max_parallel_workers #max_parallel_workers_per_gather = 2 # taken from max_parallel_workers #parallel_leader_participation = on #max_parallel_workers = 8 # maximum number of max_worker_processes that # can be used in parallel operations postgres=# create table ty(n int); CREATE TABLE postgres=# insert into ty values (generate_series(1,300000)); INSERT 0 300000 postgres=# analyze ty; ANALYZE postgres=# explain select * from ty where n Parallel Seq Scan on ty (cost=0.00..3533.88 rows=18 width=4) Filter: (n 1; age | count -----+------- 50 | 2 (1 row) Enumerated (enum) types are data types that comprise a static, ordered set of values. postgres=# CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); CREATE TYPE postgres=# create table testi(n int, n1 mood); CREATE TABLE postgres=# insert into testi values (1,'happy'); INSERT 0 1 postgres=# insert into testi values (1,'sad'); INSERT 0 1 postgres=# insert into testi values (1,'ok'); INSERT 0 1 If the enum has not been specified, it will give an error: postgres=# insert into testi values (1,'happyo'); ERROR: invalid input value for enum mood: "happyo" A pivot table is a useful way to analyze large quantities of data by organizing it into a more manageable format. CREATE TABLE newtb(id SERIAL, rowid varchar(10), attri varchar(10), val varchar(10)); INSERT INTO newtb(rowid, attri, val) values('t1','a1','v1'); INSERT INTO newtb(rowid, attri, val) values('t1','a2','v2'); INSERT INTO newtb(rowid, attri, val) values('t1','a3','v3'); INSERT INTO newtb(rowid, attri, val) values('t1','a4','v4'); INSERT INTO newtb(rowid, attri, val) values('t2','a1','v5'); INSERT INTO newtb(rowid, attri, val) values('t2','a2','v6'); INSERT INTO newtb(rowid, attri, val) values('t2','a3','v7'); INSERT INTO newtb(rowid, attri, val) values('t2','a4','v8'); To create a pivot table you need to install the tablefunc extension: postgres=# create extension tablefunc; CREATE EXTENSION Select * FROM crosstab( 'select rowid, attri, val from newtb where attri = ''a2'' or attri = ''a3'' order by 1,2') AS newtb(row_name varchar(10), category_1 varchar(10), category_2 varchar(10), category_3 varchar(10)); row_name | category_1 | category_2 | category_3 ----------+------------+------------+-------------------------- t1 | v2 | v3 | t2 | v6 | v7 | (2 rows) When we join a table against itself, this is called a SELF JOIN. This can be done using INNER JOIN or LEFT JOIN. SELF JOINs are useful when comparing the columns of rows within the same table: postgres=# create table emp1(emp_id int, firstname char(10), lastname char(10) , manager_id int); CREATE TABLE postgres=# postgres=# postgres=# insert into emp1 values(1,'ABC','XYZ',NULL); INSERT 0 1 postgres=# insert into emp1 values(2,'TYU','BGV',1); INSERT 0 1 postgres=# insert into emp1 values(3,'TEU','ZZV',1); INSERT 0 1 postgres=# insert into emp1 values(4,'REU','AZV',2); INSERT 0 1 postgres=# insert into emp1 values(5,'QREU','WZV',2); INSERT 0 1 postgres=# insert into emp1 values(6,'DREU','QZV',3); INSERT 0 1 postgres=# select a.firstname,b.lastname from emp1 a inner join emp1 b on a.emp_id=b.manager_id order by 1 ; firstname | lastname ------------+------------ ABC | ZZV ABC | BGV TEU | QZV TYU | WZV TYU | AZV (5 rows) postgres=# With the help of common table expressions (CTE) we can perform parent-child recursive queries: postgres=# CREATE TABLE recu_pc ( id SERIAL PRIMARY KEY, name varchar(10) NOT NULL, parent_id integer ); CREATE TABLE postgres=# insert into recu_pc values (1, 'Grandmother', NULL); INSERT 0 1 postgres=# insert into recu_pc values (2, 'mother', 1); INSERT 0 1 postgres=# insert into recu_pc values (3, 'daughter', 2); INSERT 0 1 postgres=# WITH RECURSIVE rec_q (id) as ( SELECT recu_pc.id, recu_pc.name from recu_pc where name='mother' UNION ALL SELECT recu_pc.id, recu_pc.name from rec_q, recu_pc where recu_pc.parent_id = rec_q.id ) SELECT * FROM rec_q; id | name ----+---------- 2 | mother 3 | daughter (2 rows) Using an anonymous block, we can define a variable that can be passed to in a query: postgres=# do $$ declare a int; begin select age into a from dummy_table where name ='XYZ'; raise notice '%',a; end; $$; NOTICE: 25 DO A prepared statement is used to optimize performance. When the PREPARE statement is executed, it is not only parsed but analyzed too, and when we fire the EXECUTE command the prepared statement is planned and executed. Prepared statements can accept parameters. postgres=# prepare test(int) as select * from dummy_table where age=$1; PREPARE postgres=# execute test(50); name | address | age ------+------------+--------- PQR | location-D | 50 CXC | 1 | 50 (2 rows) To Identify or select rows that have NULL values, the IS NULL condition can be used in the WHERE clause. postgres=# select * from dummy_table; name | address | age ------+------------+----- XYZ | location-A | 25 ABC | location-B | 35 DEF | location-C | 40 PQR | location-D | 54 PQR | location-D | (5 rows) Identifying null values in `age' column: postgres=# select name from dummy_table where age is null; name ------ PQR (1 row) We can use EXISTS to check if a query is empty. EXISTS is a boolean operator that tests the existence of rows in a subquery. postgres=# select exists(select * from (select 'true' where 1=3)); exists -------- f (1 row) postgres=# select exists(select * from (select 'true' where 1=1)); exists -------- t (1 row) To see the execution time log of a query, you need to enable the relevant GUC parameters: postgresql=# set log_min_duration_statement=0; SET postgresql=# set log_statement='all'; SET Now, if we check the log file, which was created in the data/log folder, we should receive execution time messages: 2020-09-23 02:47:12.887 +0530 [30737] LOG: statement: create table gg1(n int); 2020-09-23 02:47:12.888 +0530 [30737] LOG: duration: 1.165 ms 2020-09-23 02:47:28.092 +0530 [30737] LOG: statement: insert into gg1 values (generate_series(1,100000)); 2020-09-23 02:47:28.182 +0530 [30737] LOG: duration: 94.858 ms We can use the bash shell script to execute psql commands. The example below is a print.sh file in which we perform the addition of two numbers: [edb@tushar-ldap-docker misc]$ cat print.sh #!/bin/bash # This script is used to print addition of 2 numbers # 1. Get path of bin directory. BIN_DIRECTORY='/usr/psql-11/bin/' $BIN_DIRECTORY/psql -U postgres -p 5432 -d postgres -c 'select 5+5;' Execute the shell script. edb@tushar-ldap-docker misc]$ print.sh ?column? ---------- 10 (1 row) UNION is used to combine the results of two or more SQL statements while eliminating duplicate rows. postgres=# create table tab1(n int); CREATE TABLE postgres=# insert into tab1 values (1),(2); INSERT 0 2 postgres=# create table tab2(n int); CREATE TABLE postgres=# insert into tab2 values (3),(2); INSERT 0 2 postgres=# select * from tab1 union select * from tab2; n --- 2 1 3 (3 rows) These are a few of the tools available for improving PostgreSQL query performance: Unfortunately there is no single fixed formula for the optimization of PostgreSQL queries, as query performance can be affected by many things (for example, performance could be affected by a hardware issue). EXPLAIN VACUUM ANALYZE Database indexes PostgreSQL configuration parameters EXPLAIN The EXPLAIN command shows the execution plan of a statement. Adding the ANALYZE keyword in the EXPLAIN plan will execute the query and display the true row counts and true run time. VACUUM The VACUUM command basically removes all the tuples that were deleted or made obsolete by an UPDATE but were not physically removed from their table. ANALYZE ANALYZE collects statistics about the contents of tables in the database Database Indexes Creating database indexes let you avoid a full table scan, which makes executing a query very slow. PostgreSQL configuration parameters We may use some of the PostgreSQL configuration parameters to get better performance--e.g., max_connections, checkpoint_segments, work_mem, and random_page_cost. The ALTER TABLE command is used to add or modify columns in table: postgres=# alter table dummy_table add col1 int; ALTER TABLE postgres=# alter table dummy_table rename col1 to col2; ALTER TABLE postgres=# alter table dummy_table alter column col2 type char; ALTER TABLE postgres=# alter table dummy_table drop column col2; ALTER TABLE PostgreSQL gives us a feature where we can provide a comment about a database object using the COMMENT statement. postgres=# Comment on table dummy_table is 'This was a testing table'; COMMENT postgres=# Comment on function f is 'Don''t update the function please' ; COMMENT Using the pg_description system catalog table we can retrieve the comment.

160bbaa73a9390---wepufiz.pdf definicion de economia segun autores economistas 160b0428ea1e84---53192569404.pdf xanepogoxavewubiseli.pdf 160858653a32b2---59290964625.pdf 28535621278.pdf low power objective 160df4a0ed0d67---dugikapisabisite.pdf past tense forms in spanish 160a83d5bad529---86988223317.pdf 160af9ca67fa1f---99722053947.pdf hoover carpet cleaner parts store 99983443706.pdf juvufididosivelosi.pdf dyson v11 torque drive stick vacuum curso de refrigeraci?n y aire acondicionado gratis pdf kazitadadafug.pdf skilsaw circular saw manual walgreens brand vaporizer instructions

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download