Very simple query decomposition
by Admin | Apr 1, 2015 | PL/pqSQL | 0 comments
This is just small simple hint but maybe someone could need it. If you have some not complicated query you can decompose it to parts using this query: with srcquery as (select replace(lower( --this is some "normalization of the query string 'SELECT...Find dependecies for some object
by Admin | Apr 1, 2015 | Database, PL/pqSQL | 0 comments
PostgreSQL can show you directly only these dependencies: table – view table column – view view – view view column – view … You cannot check directly dependencies like: table (view) -> function table (view) column -> function...How to alter table column which is used in views
by Admin | Apr 1, 2015 | Tables, Views | 0 comments
Sometimes you need to alter data type or length of some table column which is used in views.This of course means to drop and recreate all those views and this also in right order. You can find some workaround in internet. But if you want to be really on the safe side...Tags
aggregate functions
array functions
BSD
CAST command
COPY command
CREATE FUNCTION
CTID
current_query
data file
date time
dblink
deadlock
dependencies
DOMAIN command
EXISTS command
FOR cycle
freeze
IF...ELSE...END IF
information_schema
installation
JSON
operation system
parser
performance
pg_bulkload
pg_catalog
pg_class
pg_constraint
pg_database
pg_locks
pg_stat_activity
pg_stat_bgwriter
plv8
psql
recursive select
server configuration
string functions
system columns
system functions
TODO
vacuum
VALUES command
WAL log
window functions
xmin transaction ID
Recent Posts
- Problems with autovacuum during high load on server
- How to get information about partitions for new native partitioning in PostgreSQL
- Check / terminate postgresql processes
- How to check blocked queries
- Bash script – dump structures of parent tables one by one and upload them to Google storage
- Bash script – dump functions one by one and upload them to Google storage
- Bash script – dump tables one by one and upload dumps to Google storage
- Transaction ID wraparound error
- Block some user from connecting into PostgreSQL
- Setting logical replication is not entirely straight forward…
- Show source code of trigger function
- Default “postgres” database – how to re-create it
- PostgreSQL point in time recovery – experiences
- Experiences with PostgreSQL Google Cloud SQL
- Similarity of two strings – experiences
- Bash script for emergency stop of PostgreSQL
- Build latest pgloader from sources (Debian 9)
- Temporary tablespace – small notes
- Simple plpgsql script to set select only privileges for new user on a database
- pg_basebackup – bash script for backup and archiving on Google storage
- pg_basebackup – experiences
- pg_basebackup / pg-barman – restore tar backup
- pg-barman – check HW config of a server / instance
- Streaming replication – increase of max_connections on master can shutdown all your replicas
- Streaming replication / pg-barman – archiving WAL logs using script
- Add new disk and tablespace to PostgreSQL master and replica
- Hanging PostgreSQL session when called from GO lang or node.js program
- error: duplicate key value violates unique constraint pg_type_typname_nsp_index
- Dump and restore postgresql users (roles)
- Bash script – clone PostgreSQL replica
- Ansible – simple playbook for installing PostgreSQL on Ubuntu/ Debian
- SQL injection in PostgreSQL
- Problem with “drowsy” sessions in PostgreSQL 9.6 on Debian 8
- Debug / trace PostgreSQL with perf
- Bash script – sync table from PostgreSQL to Bigquery
- How to rename database with active sessions
- All objects in database
- About this site
- Simple query to find object in all databases on your server
- Server process was terminated by signal 9: Killed
- Useful regular expressions
- Hierarchy of tables based on foreign keys
- Use cases for GROUP BY aggregate functions
- psql quick help
- Use cases for WINDOW functions
- pg-barman - check HW config of a server / instance
- Small PostgreSQL performance tricks
- Problems with casts?
- First tests of AGG extension
- Transaction ID wraparound
- Install PostgreSQL 9.5 on Ubuntu 14.04
- Parallel run of queries / functions / tasks using dblink
- PostgreSQL packages for Debian and Ubuntu
- Alias for value in RETURN
- System column ctid as row identifier
- Playing with JSON data in PostgreSQL
- Tests of cstore_fdw extension
- pg_dump & pg_restore
- Move PostgreSQL tablespace / data directory to another disk
- Experiencies with table inheritance / partitioning in PostgreSQL
- List all databases with size
- Install pgAdmin3 1.22 on CentOS 7
- Find data file for table
- GROUP BY CUBE - experiences
- PostgreSQL Timestamp vs. UNIX timestamp
- Show cstore_fdw foreign table files
- Parse tree in messages output
- PostgreSQL candies
- Install javascript pl/v8 extension on PostgreSQL on Ubuntu
- pgbouncer - small hints
- pg_basebackup / pg-barman - restore tar backup
- Install PostgreSQL 9.3 on PcBSD 10
- Check available / installed extensions
- Check PostgreSQL version
- PostgreSQL and MySQL on cloud instances - few small hints
- Log all queries into server log
- Where database stores data / PostgreSQL data directory
- Linux commands for simple analysis of PostgreSQL logs
- Javascript in PostgreSQL pl/v8 - tutorial 1 - overview
- Select * from VALUES / Using VALUES in SELECT
- Simple bash script for quick copying of the tables between two servers / databases
- pglogical on PostgreSQL 9.6 - small hints (on Debian)
- Wish to have
- JavaScript in PostgreSQL pl/v8 - tutorial 2 - operations with tables - plv8.execute
- System information functions
- Simple patch / update script
- Domain - very useful constraint standardization
- Useful linux commands for PostgreSQL administrator
- Parallelism in PostgreSQL 9.6.1 does not work withing CREATE TABLE / CREATE MATERIALIZED VIEW statement
- Settings for WAL
- Export data into CSV format
- pg_basebackup - experiences
- JavaScript in PostgreSQL pl/v8 – tutorial 3 - small JavaScript cheat sheet
- psql - ask for value or set a value from command line
- Arrays in tables and selects
- pg_basebackup - bash script for backup and archiving on Google storage
- Changes in streaming replication in PostgreSQL 9.6
- Simple plpgsql script to set select only privileges for new user on a database
- Find database creation date
- Streaming replication - installation notes
- pl/sh language - small hints
- Temporary tablespace - small notes
- Show all sessions / connections on PostgreSQL server
- Build latest pgloader from sources (Debian 9)
- Lock monitoring
- JavaScript in PostgreSQL pl/v8 – error return_next function calls in context that cannot accept a set
- Show all foreign keys for table with DDL constraint definition
- Locks on objects with relationships
- JavaScript in PostgreSQL pl/v8 – tutorial 4 - trigger functions
- file_fdw extension small hints
- Bash script for emergency stop of PostgreSQL
- Analyze data file
- How to find column names of variable type record in PL/pgSQL
- List of values - bank Swift codes
- Which indexes are really used
- bgwriter statistic in PostgreSQL 8.4
- Better query statistics with extension pg_stat_statements
- Bash script - synchronize daily partitions between 2 postgresql servers
- Server statistics
- Average time interval between checkpoints
- Very simple bash script for monitoring waits on database
- How to compare database objects between two databases - program in python
- Count according to condition
- How to easily find row count for tables (and nearly kill server in the process)
- Use EXISTS in IF condition
- Usage of arrays in aggregations
- Use SELECT count in IF command
- Similarity of two strings - experiences
- Experiences with PostgreSQL Google Cloud SQL
- Convert ARRAY to rows
- Display / find / determine size of data files for tables / objects
- Show current session / process / connection ID
- PostgreSQL: No space left on device
- Find newly inserted records / Identify changed tables
- A little bit about pgstattuple extension
- Add indexes to partitions
- PostgreSQL point in time recovery - experiences
- Kill / end connection / session in PostgreSQL
- Import really big export files into PostgreSQL + change tablespace during import
- Install PostgreSQL 10 beta from sources - on Debian 8
- Default "postgres" database - how to re-create it
- Find biggest tables / database objects over all databases based on data file size
- PostgreSQL 10 - table partitioning - first impressions
- Reload server configuration
- Show source code of trigger function
- Processes waiting for lock - PostgreSQL 8.4
- Useful SQLs to check contents of PostgreSQL9.4 shared_buffer
- PostgreSQL 10 - table partitioning - how to check partitions and manipulate with them
- Setting logical replication is not entirely straight forward...
- Very useful PostgreSQL links
- Processes waiting for lock - PostgreSQL 9.3
- Check real sizes of columns in tables
- PostgreSQL 10 - other highlights
- Install PostgreSQL of desired version on Ubuntu using Ansible
- Reconfigure PostgreSQL to listen to all addresses and trust local connections using Ansible
- Bash script - sync table from PostgreSQL to Bigquery
- PostgreSQL on Ubuntu without swap
- Debug / trace PostgreSQL with perf
- All pg_catalog objects in PostgreSQL 9.3 with explanation
- Drop all versions of a function
- Problem with "drowsy" sessions in PostgreSQL 9.6 on Debian 8
- Small useful Linux commands with watch
- How to alter table column which is used in views
- Script for VACUUM FULL on all tables
- Triggers - some useful queries
- Find dependecies for some object
- For loop over VALUES
- How to get DDL for table from PostgreSQL
- Out of shared memory error
- Find comments on database objects
- mysql_fdw
- Event triggers in PostgreSQL 9.3 / 9.4
- SQL injection in PostgreSQL
- Block some user from connecting into PostgreSQL
- PostgreSQL 9.3 - find all dependent objects for materialized view
- Very simple query decomposition
- Error "write failed" during SELECT command - on Linux machine
- Ansible - simple playbook for installing PostgreSQL on Ubuntu/ Debian
- Transaction ID wraparound error
- How to simulate deadlock in PostgreSQL
- Use stacked diagnostics to check error details (+ check of deadlock details)
- PostgreSQL and time zones
- Bash script - clone PostgreSQL replica
- Install PostgreSQL 10 on Ubuntu
- Bash script - dump tables one by one and upload dumps to Google storage
- Dump and restore postgresql users (roles)
- Donations?
- Bash script - dump functions one by one and upload them to Google storage
- Bash script - dump structures of parent tables one by one and upload them to Google storage
- How to check PostgreSQL processes from OS
- error: duplicate key value violates unique constraint pg_type_typname_nsp_index
- How to check blocked queries
- Hanging PostgreSQL session when called from GO lang or node.js program
- Check / terminate postgresql processes
- Check of missing data files
- How to get information about partitions for new native partitioning in PostgreSQL
- Add new disk and tablespace to PostgreSQL master and replica
- Autonomous transaction in PostgreSQL
- Streaming replication / pg-barman - archiving WAL logs using script
- Problems with autovacuum during high load on server
- Parallel run of queries / functions using dblink - example 1
- PostgreSQL and high data load application
- Streaming replication - increase of max_connections on master can shutdown all your replicas
- List of columns and list of column definitions for dblink