# - - # # - - # # - - # # - DESCRIBE TABLE - PostgreSQL - # https://stackoverflow.com/questions/109325/postgresql-describe-table select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = 'ChartOfAccounts'; # - - # # - 9.17. Conditional Expressions - # https://www.postgresql.org/docs/9.6/static/functions-conditional.html SELECT * FROM test; a --- 1 2 3 SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two' ELSE 'other' END FROM test; a | case ---+------- 1 | one 2 | two 3 | other SELECT a, CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'other' END FROM test; a | case ---+------- 1 | one 2 | two 3 | other SELECT SUBSTR("Accounting"."ChartOfAccounts".Account, 7, 3) as "Act", CASE SUBSTR("Accounting"."ChartOfAccounts".Account, 9, 1) WHEN '0' THEN 'cero' WHEN '1' THEN 'one' WHEN '2' THEN 'two' WHEN '3' THEN 'three' WHEN '4' THEN 'four' ELSE 'other' END AS "Ending" FROM "ChartOfAccounts" LIMIT 20; DELETE FROM "Accounting"."ChartOfAccounts" WHERE "Accounting"."ChartOfAccounts".Cia = 'IS'; SELECT CASE WHEN EXISTS (SELECT * FROM "Accounting"."ChartOfAccounts" WHERE "Accounting"."ChartOfAccounts".Cia = 'IS' LIMIT 5) THEN 1 ELSE 0 END; # - - # # - PostgreSQL Quick Guide - # https://www.tutorialspoint.com/postgresql/postgresql_quick_guide.htm # - - # # - ADO/PHP Connection - # # - Very important - # # - Persistent connections - # http://php.net/manual/en/pdo.connections.php Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application. Example #4 Persistent connections true)); ?> # - Very important - # # - Look for "Transactions" - # http://php.net/manual/en/pdo.begintransaction.php http://php.net/manual/en/pdo.transactions.php http://www.w3resource.com/php/pdo/php-pdo.php http://w3resource.com/PostgreSQL/PostgreSQL-with-php-a-through-introduction.php # - - # # - PostgreSQL - Basic Functions - # http://www.sqlines.com/postgresql/stored_procedures_functions http://www.postgresqltutorial.com/postgresql-stored-procedures/ # - PL/pgSQL Function Parameters - # http://www.postgresqltutorial.com/plpgsql-function-parameters/ http://www.postgresonline.com/journal/archives/58-Quick-Guide-to-writing-PLPGSQL-Functions-Part-1.html http://www.joeconway.com/presentations/function_basics.pdf http://www.onlamp.com/pub/a/onlamp/2006/05/11/postgresql-plpgsql.html?page=1 # - - # # - iMac - # # - - # # - [PostgreSQL] Help with BEGIN/COMMIT within a transaction - # http://grokbase.com/t/postgresql/pgsql-general/0381k6ct4v/help-with-begin-commit-within-a-transaction # - - # # - How to search a specific value in all tables (PostgreSQL)? - # https://stackoverflow.com/questions/5350088/how-to-search-a-specific-value-in-all-tables-postgresql # - - # # - How do I do large non-blocking updates in PostgreSQL? - # https://stackoverflow.com/questions/1113277/how-do-i-do-large-non-blocking-updates-in-postgresql # - - # # - Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL? - # https://stackoverflow.com/questions/18389124/simulate-create-database-if-not-exists-for-postgresql psql -U postgres -tc "SELECT 1 AS Cold FROM pg_database WHERE datname = 'my_db'" | grep -q 1 || psql -U postgres -c "CREATE DATABASE my_db" # - - # # - psql: no relations found despite existing relations - # https://stackoverflow.com/questions/17578077/psql-no-relations-found-despite-existing-relations # - - # # - Create table - PostgreSQL - # https://www.postgresql.org/docs/9.6/static/sql-createtable.html # - - # # - Install Atom Text Editor in Ubuntu 16.04 (both 32&64bit) - # http://tipsonubuntu.com/2016/08/05/install-atom-text-editor-ubuntu-16-04/ # - - # # - Install Sublime Text 3 in Ubuntu 16.04 & Higher The Official Way - # http://tipsonubuntu.com/2017/05/30/install-sublime-text-3-ubuntu-16-04-official-way/ # - - # # - The byte-order mark (BOM) in HTML - # https://www.w3.org/International/questions/qa-byte-order-mark # - - # # - Run a PostgreSQL .sql file using command line arguments - # # - Look for answer 142 - # https://stackoverflow.com/questions/9736085/run-a-postgresql-sql-file-using-command-line-arguments \i path_to_sql_file # - - # # - Introducción a la base de datos relacional PostgreSQL - world database - # https://picodotdev.github.io/blog-bitix/2017/05/introduccion-a-la-base-de-datos-relacional-postgresql/ \l \c world \connect world \dt \d+ city; \d+ country; \d+ countrylanguage; SELECT Co.name, COUNT(Ci.id) as n FROM country Co INNER JOIN city Ci ON Ci.countrycode = Co.code GROUP BY Co.code ORDER BY Co.name; SELECT Co.name, COUNT(Ci.id) as n FROM country Co INNER JOIN city Ci ON Ci.countrycode = Co.code GROUP BY Co.code ORDER BY n DESC; create database companies; \connect companies create table company( ID BIGSERIAL PRIMARY KEY NOT NULL, NAME CHAR(50) UNIQUE NOT NULL, FOUNDATION DATE NOT NULL, ADDRESS TEXT, EMPLOYEES INTEGER ); INSERT INTO company (name, foundation, address, employees) VALUES ('Oracle', '1977-06-16', 'Redwood Shores, California, Estados Unidos', 105000), ('Apple', '1976-04-01', '1 Infinite Loop, Cupertino, California, Estados Unidos', 80000), ('Microsoft', '1975-04-04', 'Redmond, Washington, Estados Unidos', 120584), ('RedHat', '1993-01-01', 'Raleigh, Carolina del Norte 100 East Davie Street', 6100), ('Sun Microsystems', '1982-02-24', '4150 Network Circle, Santa Clara, California, Estados Unidos', 35000); SELECT * FROM company; INSERT INTO company (name, foundation, address, employees) VALUES ('Canonical', '2004-03-05', 'London, United Kingdom', '700') ON CONFLICT (name) DO UPDATE SET foundation = EXCLUDED.foundation, address = EXCLUDED.address, employees = EXCLUDED.employees; SELECT * FROM company; INSERT INTO company (name, foundation, address, employees) VALUES ('Canonical', '2004-03-05', 'London, United Kingdom_', '750') ON CONFLICT (name) DO UPDATE SET foundation = EXCLUDED.foundation, address = EXCLUDED.address, employees = EXCLUDED.employees; WITH city_rank AS ( SELECT c.*, rank() OVER w AS rank, sum(population) OVER w AS country_cities_population FROM city c WHERE countrycode in ('DEU', 'ESP', 'FRA', 'ITA') WINDOW w AS (PARTITION BY countrycode ORDER BY population DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ) SELECT countrycode, name, CAST(population * 100 AS FLOAT) / country_cities_population AS percentage FROM city_rank WHERE rank <= 3 ORDER BY countrycode, percentage DESC; CREATE TYPE company_type AS ENUM ('startup', 'pyme', 'multinational'); CREATE TABLE company_2 ( ID BIGSERIAL PRIMARY KEY NOT NULL, NAME VARCHAR(50) UNIQUE NOT NULL, TYPE COMPANY_TYPE NOT NULL, FOUNDATION DATE NOT NULL, ADDRESS TEXT, EMPLOYEES INTEGER CONSTRAINT employees_positive CHECK (EMPLOYEES > 0) ); CREATE TABLE company_3 ( ID BIGSERIAL PRIMARY KEY NOT NULL, NAME VARCHAR(50) UNIQUE NOT NULL, FOUNDATION DATE NOT NULL, ADDRESS TEXT, EMPLOYEES INTEGER CONSTRAINT employees_positive CHECK (EMPLOYEES > 0) ); INSERT INTO company_3 (name, foundation, address, employees) VALUES ('Dummy-2', '1970-01-01', 'Unknow', -10); ERROR: new row for relation "company_3" violates check constraint "employees_positive" DETAIL: Failing row contains (1, Dummy-2, 1970-01-01, Unknow, -10). # - - # # - - #