nyuuzyou/wikis huggingface dataset:
Events/2013

This is a historical listing of Postgres events that occurred in 2013. Please see the events page for the current listings.
 
Event 	Web Page 	Date 	Country 	City 	Activities
PgDay Argentina 2013 	4th National PostgreSQL One-Day Conference 2013	November 14, 2013 	Argentina	Buenos Aires	Talks & Tutorials
German-Speaking PostgreSQL Conference 2013	PGConf.DE 2013 	Nov 08, 2013 	Germany 	Oberhausen 	Talks
PG Conference Europe 2013 	PostgreSQL Conference 2013 	October 29 - November 1, 2013 	Ireland 	Dublin 	Training, Talks
PGDay Campinas 2013 	PGDay Campinas 2013 	September 18, 2013 	Brazil 	Campinas/SP 	Talks
PostgresOpen 2013 	PostgresOpen 2013 	Sept 16-18, 2013 	USA 	Chicago 	Training, Talks
PGBR 2013 	PGBR 2013 	August 15-17, 2013 	Brazil 	Porto Velho 	Talks, Workshops
PGday UK 	PGday UK 	July 12, 2013 	UK 	London 	Talks, Workshops
CHAR 13 	CHAR13 	July 11, 2013 	UK 	London 	Talks, Workshops
PG Day France 2013 	PG Day France 	June 18, 2013 	France 	Nantes 	Talks
PGCon 2013 	PGCon 2013 	May 21-25, 2012 	Canada 	Ottawa, ON 	Training, Talks
PG Day Ceará 2013 	PG Day Ceará 2013 	May 3, 2013 	Brazil 	Fortaleza 	Talks
Inaugural Indian PUG Meetup 	Indian PUG Pune 2013 	April 27th, 2013 	India 	Pune 	Talks
PGDay NYC 2013 	PGDay NYC 2013 	March 22nd, 2013 	USA 	New York 	Talks
ConFoo 2013: Web Techno Conference 	ConFoo 2013: Web Techno Conference 	February 25th - March 1st, 2013 	Canada 	Montreal 	Training, Talks, Booth
PGDay FOSDEM 2013 	FOSDEM PGDay 2013 	Feb 01, 2013 	Belgium 	Brussels 	Talks
Campus Party Brasil 2013 	Campus Party Brasil 2013 	January 28th - February 03rd, 2013	Brazil 	São Paulo 	Talks, Workshops
Category:PostgreSQL Events
Events/2014

This is a historical listing of Postgres events that occurred in 2014. Please see the events page for the current listings.
 
Event 	Web Page 	Date 	Country 	City 	Activities
PGDay.IT 	2014.pgday.it 	November 7, 2014 	Italy 	Prato 	Tutorials, Talks, Interactive sessions
PgDay Argentina 	pgday.com.ar 	November 28, 2014 	Argentina	Santa Fe 	Talks, Tutorials, Panels, Keynotes
PG Conference China 2014 	ChinaCon2014 	December 12 - 13, 2014 	China 	Shenzhen 	Training, Talks
PG Conference Europe 2014	PostgreSQL Conference 2014 	October 21 - 24, 2014 	Spain 	Madrid 	Training, Talks
PGDay Ecuador 2014 	In progress 	October 7, 2014 	Ecuador 	Quito 	Talks
PGDay Curitiba 2014 	PGDay Curitiba 2014 	September 19, 2014 	Brazil 	Curitiba/PR	Talks
PostgresOpen 2014 	PostgresOpen 2014 	September 17-19, 2014 	USA 	Chicago 	Training, Talks
PGDay Campinas 2014 	PGDay Campinas 2014 	September 10, 2014 	Brazil 	Campinas/SP	Talks
PDXPUGDay2014 	PDXPUGDay2014 	September 6, 2014 	USA 	Portland 	Talks
PostgreSQL @ INNOVATHENS 	(Greek) PostgreSQL.gr - INNOVATHENS	June 6, 2014 	Greece 	Athens 	Talks for professional audience
PGCon 2014 	PGCon 2014 	May 20-24 	Canada 	Ottawa 	Tutorials, Talks, Unconference
PGDay Caribbean 2014 	PGDay Caribbean 2014 (deactivated) 	August 1st, 2014 (announced: April 1st, 2014)	Caribbean	St. Martin 	Talks, Social Event
PGConf NYC 2014 	PGConf NYC 2014 	April 3-4, 2014 	USA 	New York 	Talks
Nordic PGDay 2014 	Nordic PGDay 2014 	March 20, 2014 	Sweden 	Stockholm 	Talks
FOSDEM PGDay 2014 	FOSDEM PGDay 2014 	January 31, 2014 	Belgium 	Brussels 	Talks
Category:PostgreSQL Events
Excel Add-in for PostgreSQL

== About ==
Excel Add-in for PostgreSQL by Devart allows you to quickly and easily connect Microsoft Excel to PostgreSQL, load data from PostgreSQL to Excel, instantly refresh data in an Excel workbook from the database, edit these data, and save them back to PostgreSQL. It enables you to work with PostgreSQL data like with usual Excel worksheets, easily perform data cleansing and de-duplication, and apply all the Excel's powerful data processing and analysis capabilities to these data.
== Key Features: ==
* Connect to PostgreSQL Directly from Excel. To connect Excel to PostgreSQL you just need to specify its host, port, and your credentials. As for security, Devart Excel Add-in for PostgreSQL supports secure SSL connections, secure authentication with single sign-on, etc. Connection information may optionally be stored in the Excel file, or you may reenter it whenever necessary.
* Use Query Builder or SQL to Import Data You Need. Visual Query Builder allows you to choose a table to import data from, select the columns from this table and related tables, and set filter conditions and data ordering options visually. If you need to select data more precisely, you can use SQL to select data you need. Before finally importing data you can preview data and verify the query in a Data Preview window.
* Instantly Refresh Data Whenever You NeedIt's very convenient to always have fresh data from the database right in your workbook. Imagine, you have performed data analysis in your Excel workbook, built some charts for your PostgreSQL data, and you can re-read data from the source and update all this to the current state by just clicking a button on the ribbon whenever you need.
 
* Edit PostgreSQL Data Like Usual Excel Spreadsheets To edit PostgreSQL data in Excel you need just to click the Edit Mode button on the ribbon before editing, and then you can edit data just like you usually do it - add or delete rows, modify cell values, etc. All the changes are highlighted so you can easily see which data is changed. After you finish editing, simply click Commit to write changes to the database or Rollback to cancel changes.
* Instant Data Validation Devart Excel Add-in for PostgreSQL instantly validates entered data and highlights errors, such as incorrect dates, duplicate primary key values, etc.
Category:Tool
Excel Add-in for PostgreSQL by Devart

== About ==
Excel Add-in for PostgreSQL by Devart allows you to quickly and easily connect Microsoft Excel to PostgreSQL, load data from PostgreSQL to Excel, instantly refresh data in an Excel workbook from the database, edit these data, and save them back to PostgreSQL. It enables you to work with PostgreSQL data like with usual Excel worksheets, easily perform data cleansing and de-duplication, and apply all the Excel's powerful data processing and analysis capabilities to these data.
== Key Features: ==
* Connect to PostgreSQL Directly from Excel. To connect Excel to PostgreSQL you just need to specify its host, port, and your credentials. As for security, Devart Excel Add-in for PostgreSQL supports secure SSL connections, secure authentication with single sign-on, etc. Connection information may optionally be stored in the Excel file, or you may reenter it whenever necessary.
* Use Query Builder or SQL to Import Data You Need. Visual Query Builder allows you to choose a table to import data from, select the columns from this table and related tables, and set filter conditions and data ordering options visually. If you need to select data more precisely, you can use SQL to select data you need. Before finally importing data you can preview data and verify the query in a Data Preview window.
* Instantly Refresh Data Whenever You NeedIt's very convenient to always have fresh data from the database right in your workbook. Imagine, you have performed data analysis in your Excel workbook, built some charts for your PostgreSQL data, and you can re-read data from the source and update all this to the current state by just clicking a button on the ribbon whenever you need.
 
* Edit PostgreSQL Data Like Usual Excel Spreadsheets To edit PostgreSQL data in Excel you need just to click the Edit Mode button on the ribbon before editing, and then you can edit data just like you usually do it - add or delete rows, modify cell values, etc. All the changes are highlighted so you can easily see which data is changed. After you finish editing, simply click Commit to write changes to the database or Rollback to cancel changes.
* Instant Data Validation Devart Excel Add-in for PostgreSQL instantly validates entered data and highlights errors, such as incorrect dates, duplicate primary key values, etc.
Category:Tool
Extension build troubleshooting

Usually all that's necessary to build an extension is the following commands:
 make
 make install
 make installcheck
Followed by a SQL command to create the extension in a database (replace foobar with the name of the extension):
 CREATE EXTENSION foobar;
== Troubleshooting ==
If you encounter an error such as:
 "Makefile", line 8: Need an operator
You need to use GNU make, which may well be installed on your system as gmake:
 gmake
 gmake install
 gmake installcheck
If you encounter an error such as:
 make: pg_config: Command not found
Be sure that you have pg_config installed and in your path. If you used a package management system such as RPM to install PostgreSQL, be sure that the -devel package is also installed. If necessary tell the build process where to find it:
 env PG_CONFIG=/path/to/pg_config make && make installcheck && make install
If you encounter an error such as:
 ERROR: must be owner of database regression
You need to run the test suite using a super user, such as the default "postgres" super user:
 make installcheck PGUSER=postgres
Once the extension is installed, you can add it to a database. If you're running PostgreSQL 9.1.0 or greater, it's a simple as connecting to a database as a super user and running (replace foobar with the name of the extension):
 CREATE EXTENSION foobar;
If you've upgraded your cluster to PostgreSQL 9.1 and already had the extension installed, you can upgrade it to a properly packaged extension with:
 CREATE EXTENSION foobar FROM unpackaged;
For versions of PostgreSQL less than 9.1.0, you'll need to run the installation script (replace foobar with the name of the extension):
 psql -d mydb -f /path/to/pgsql/share/contrib/foobar.sql
If you want to install the extension and all of its supporting objects into a specific schema, use the PGOPTIONS environment variable to specify the schema, like so:
 PGOPTIONS=--search_path=extensions psql -d mydb -f foobar.sql
== Credits ==
This troubleshooting guide was originally written by David E. Wheeler. Copied from his hostname extension README.
Category:Extensions
Extract days from range type

== Extract Days or Interval from a Range type value ==
To determine the the interval between the lower and upper bound of a timestamp(tz) range, or to return the number of days between the lower and lower bounds.
== Functions ==
Two sets of overloaded functions, the first to extract the interval, the second set to extract the days.
Postgresql docs about Range types: http://www.postgresql.org/docs/current/static/rangetypes.html
CREATE OR REPLACE FUNCTION extract_interval(TSTZRANGE) RETURNS interval AS
$func$
 select upper($1) - lower($1);
$func$ LANGUAGE sql STABLE;
CREATE OR REPLACE FUNCTION extract_interval(TSRANGE) RETURNS interval AS
$func$
 select upper($1) - lower($1);
$func$ LANGUAGE sql STABLE;
CREATE OR REPLACE FUNCTION extract_days(TSTZRANGE) RETURNS integer AS
$func$
 select (date_trunc('day',upper($1))::DATE - date_trunc('day',lower($1))::DATE) + 1;
$func$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION extract_days(TSRANGE) RETURNS integer AS
$func$
 select (date_trunc('day',upper($1))::DATE - date_trunc('day',lower($1))::DATE) + 1;
$func$ LANGUAGE sql;
== Example ==
WITH tzr AS (SELECT tstzrange('2013-10-01 10:00-07', '2013-10-03 05:15-07') AS dttz, tstzrange('2013-10-01 10:00', '2013-10-03 05:15') AS dt)
SELECT extract_interval(dttz) as interval_with_tz,
 extract_interval(dt) as interval_no_tz,
 extract_days(dttz) as days_with_tz,
 extract_days(dt) as days_no_tz
FROM tzr;
 interval_with_tz | interval_no_tz | days_with_tz | days_no_tz 
------------------+----------------+--------------+------------
 1 day 19:15:00 | 1 day 19:15:00 | 3 | 3
FDW v2

== NoSQL Database Wrappers ==
{| align="center" border="1" cellspacing="0" 
|-
! |Data Source
! |Type
! |Licence
! |Code
! |PGXN
! |Doc
! |Notes
|-
|CouchDB
|Native
|PostgreSQL
| Github
| PGXN
|
| Original version
|-
|CouchDB
|Native
|PostgreSQL
|Github
|
|
| golgauth version (9.1 - 9.2+ compatible)
|-
|Hive
|Multicorn
|?
|Github
|
|
|
|-
|Kyoto Tycoon 
|Native
|MIT
| Github
|
|
|
|-
|MongoDB
|Native
|GPL3+
|Github 
| PGXN
|README
|CitusDB version
|-
|MongoDB
|Native
|GPL3+
|Github
|
|
|EDB version
|-
|Neo4j
|Native
|?
|Github
|
|
|
|-
|Redis
|Native
|PostgreSQL
|Github
|
|
|
|-
Fibonacci Numbers

Create a fibonnaci sequence to the limiting number given
CREATE OR REPLACE FUNCTION fib(f integer) 
RETURNS SETOF integer
LANGUAGE SQL
AS $$
WITH RECURSIVE t(a,b) AS (
 VALUES(0,1)
 UNION ALL
 SELECT greatest(a,b), a + b as a from t
 WHERE b < $1
 )
SELECT a FROM t;
$$;
Figures & Pics in Docs

An action item from this session in PGCon 2016 user unconference was that I (Heikki) would try to draw an illustration of the GIN index using ASCII art, to see how well ASCII art works in practice for diagrams like that. Here's what I came up with:
 ********************************************************************************************
 * *
 * +----------+ +-----------------+ *
 * | Metapage | | | *
 * +----+-----+ +---------------------------------+ | +---+ | *
 * | | | | -->| | | *
 * | | +---+ | | +---+/ +---+ | *
 * | | -->| +----------->| + | *
 * | | +---+/ +---+ | | +---+\ +---+ | *
 * | | -->| + | | -->| | | *
 * | | / +---+\ +---+ | | +---+ | *
 * | | / -->| | | | | *
 * | | / +---+ | | Posting tree | *
 * | | / | +-----------------+ *
 * | | / +---+ | *
 * | | / -->| | | *
 * | | +---+/ +---+/ +---+ | *
 * | | | +--------->| + | *
 * | | +---+\ +---+\ +---+ | *
 * | | \ -->| +-------> *
 * | | \ +---+ | *
 * | | \ | *
 * | | \ +---+ | *
 * | | \ -->| | | *
 * | | \ +---+/ +---+ | *
 * | | -->| + | *
 * | | +---+\ +---+ | *
 * | | -->| | | *
 * | | +---+ | *
 * | | | *
 * | | Entry tree | *
 * | +---------------------------------+ *
 * | *
 * +---|------------------+ *
 * | V | *
 * | +---+ +---+ +---+ | *
 * | | |->| |->| | | *
 * | +---+ +---+ +---+ | *
 * | | *
 * | Fast update list | *
 * +----------------------+ *
 * *
 ********************************************************************************************
Category:Unconference
Finding useless columns

This query finds columns in the whole database that have no more than 1 distinct value in its table, using planner's estimates.
This is useful for finding redundant and unused columns.
select nspname, relname, attname, typname,
 (stanullfrac*100)::int as null_percent,
 case when stadistinct >= 0 then stadistinct else abs(stadistinct)*reltuples end as "distinct",
 case 1 when stakind1 then stavalues1 when stakind2 then stavalues2 end as "values"
from pg_class c
join pg_namespace ns on (ns.oid=relnamespace)
join pg_attribute on (c.oid=attrelid)
join pg_type t on (t.oid=atttypid)
join pg_statistic on (c.oid=starelid and staattnum=attnum)
where nspname not like E'pg\\_%' and nspname != 'information_schema'
 and relkind='r' and not attisdropped and attstattarget != 0
 and reltuples >= 100 -- ignore tables with fewer than 100 rows
 and stadistinct between 0 and 1 -- 0 to 1 distinct values
order by nspname, relname, attname
;
See also: pg_statistic catalog documentation
Find Locks

SELECT
 l1.*,
 l2.virtualtransaction,
 l2.pid,
 l2.mode,
 l2.granted
FROM
 pg_locks l1
JOIN
 pg_locks l2 on (
 (
 l1.locktype,
 l1.database,
 l1.relation,
 l1.page,
 l1.tuple,
 l1.virtualxid,
 l1.transactionid,
 l1.classid,
 l1.objid,
 l1.objsubid
 )
 IS NOT DISTINCT FROM
 (
 l2.locktype,
 l2.database,
 l2.relation,
 l2.page,
 l2.tuple,
 l2.virtualxid,
 l2.transactionid,
 l2.classid,
 l2.objid,
 l2.objsubid
 )
 )
WHERE
 NOT l1.granted
AND
 l2.granted;
Category:SQL
First/last (aggregate)

This aggregate function returns the value from the first or last input row in each group, ignoring NULL rows. (NULLs are ignored automatically by the STRICT declaration, documented here)
The result depends on the sort order of input rows, which is arbitrary for unsorted input.</br>
Function and aggregate function can be PARALLEL SAFE in Postgres 9.6 or later. Parallelism is never used when an aggregate function is used with ORDER BY. The manual:
Partial (including parallel) aggregation is currently not supported for ordered-set aggregates. Also, it will never be used for aggregate calls that include DISTINCT or ORDER BY clauses, since those semantics cannot be supported during partial aggregation.
The aggregate function returns an arbitrary result (possibly using parallelism) for unsorted input, and a deterministic result (not using parallelism) for sorted input.
==Example==
test=# select first(column1), last(column1) from (values (null),(1),(3),(null)) as x;
 first | last 
-------+------
 1 | 3
==SQL version==
This is an SQL-language implementation with no external dependencies. 
-- Create a function that always returns the first non-NULL value:
CREATE OR REPLACE FUNCTION public.first_agg (anyelement, anyelement)
 RETURNS anyelement
 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT $1';
-- Then wrap an aggregate around it:
CREATE AGGREGATE public.first (anyelement) (
 SFUNC = public.first_agg
, STYPE = anyelement
, PARALLEL = safe
);
-- Create a function that always returns the last non-NULL value:
CREATE OR REPLACE FUNCTION public.last_agg (anyelement, anyelement)
 RETURNS anyelement
 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT $2';
-- Then wrap an aggregate around it:
CREATE AGGREGATE public.last (anyelement) (
 SFUNC = public.last_agg
, STYPE = anyelement
, PARALLEL = safe
);
Initial idea: http://archives.postgresql.org/pgsql-hackers/2006-03/msg01324.php with a couple of corrections.
==Fast C version==
A faster version written in C is available in PGXN, called first_last_agg: http://pgxn.org/dist/first_last_agg/
Category:SQL
First steps

Hello,
please add info or links:
== install postgres ==
Follow steps for your OS.
== initdb ==
Typically installing postgres to your OS creates an "initial DB" and starts the postgres server daemon running. If not then you'll need to run initdb 
ex:
 # typically initdb creates a table named "postgres" owned by user "current logged in user name"
 # however, when you use pg_ctl it defaults to connecting as user "current user" to database "current user" so specify user postgres here 
 # so that it avoids confusion of having them named differently.
 $ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data -U postgres
and start the postgres daemon through some means, then proceed ref.
== First steps after installation ==
First connect/login as root
 # su - postgres
 $ psql
 psql (9.6.0)
 Type "help" for help.
 # or in windows, current user doesn't matter
 c:\path\to\psql.exe -U postgres
 psql (9.6.0)
 Type "help" for help.
 postgres=# 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
Create a schema called test in the default database called postgres
 postgres=# CREATE SCHEMA test;
Create a role (user) with password
 postgres=# CREATE USER xxx PASSWORD 'yyy';
Grant privileges (like the ability to create tables) on new schema to new role
 postgres=# GRANT ALL ON SCHEMA test TO xxx;
Grant privileges (like the ability to insert) to tables in the new schema to the new role
 postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;
Disconnect
 postgres=# \q
Became a standard user. 
The default authentication mode is set to 'ident' which means a given Linux user xxx can only connect as the postgres user xxx.
 # su - xxx
Login from xxx user in shell to default postgres db
 xxx$ psql -d postgres
 psql (9.2.4)
 Type "help" for help.
 # in windows
 # c:\path\to\psql.exe -U xxx -d postgres # again, windows doesn't care what your current user is, by default
 psql (9.6.0)
 Type "help" for help.
Create a table test in schema test
 postgres=>CREATE TABLE test.test (coltest varchar(20));
 CREATE TABLE
Insert a single record into new table
 postgres=>insert into test.test (coltest) values ('It works!');
 INSERT 0 1
First SELECT from a table
 postgres=>SELECT * from test.test;
 coltest 
 -----------
 It works!
 (1 row)
Drop test table
 postgresql=>DROP TABLE test.test;
 DROP TABLE
Fixing Sequences/ja

データベースのコピーや再作成時によくある問題として、データベースシーケンスがそのシーケンスを使用するテーブルに挿入する時のように更新されないことがあります。
シーケンスを既存の値の直後から始めさせたければ、以下のような最も一般的な設定を行うことで実現できます。
SELECT 'SELECT SETVAL(\ ||S.relname|| '\', MAX(' ||C.attname|| ') ) FROM ' ||T.relname|| ' ;'
FROM pg_class AS S, pg_depend AS D, pg_class AS T, pg_attribute AS C
WHERE S.relkind = 'S'
 AND S.oid = D.objid
 AND D.refobjid = T.oid
 AND D.refobjid = C.attrelid
 AND D.refobjsubid = C.attnum
ORDER BY S.relname;
使用方法は通常以下のようになります。
* これを例えば'reset.sql'という名前でファイルに保存します。
* このファイルを実行し、その出力を通常のヘッダを含まないようにして保存し、それから、その出力を実行します。以下に例を示します。
psql -Atq -f reset.sql -o temp
psql -f temp
rm temp
この小さなコードには注意しなければならない制限がいくつかあります。
* テーブルで所有されているシーケンスのみに動作します。
Category:FAQCategory:Administration
Category:Japanese
Fixing Sequences/ru

==Обновление значения последовательности по данным таблицы==
Общей проблемой при копировании и повторном создании базы является то, что при вставке записей в таблицы, значения связанных с ними последовательностей не обновляются. Если необходимо установить начальные значения последовательностей, начиная со следующего после последнего существующего в таблице, то для большинства конфигураций это можно достичь следующим путем:
SELECT 'SELECT SETVAL(' ||
 quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
 ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
 quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
 pg_depend AS D,
 pg_class AS T,
 pg_attribute AS C,
 pg_tables AS PGT
WHERE S.relkind = 'S'
 AND S.oid = D.objid
 AND D.refobjid = T.oid
 AND D.refobjid = C.attrelid
 AND D.refobjsubid = C.attnum
 AND T.relname = PGT.tablename
ORDER BY S.relname;
----
Сценарий использования метода следующий:
* Сохраните запрос в файл, например, 'reset.sql'
* Выполните полученный скрипт и сохраните результат его выполнения, исключив заголовки
* Выполните полученный скрипт
Пример:
----
 $ psql -Atq -f reset.sql -o temp
 $ psql -f temp
 $ rm temp
----
Существует несколько важных моментов при таком подходе:
* Это работает лишь для последовательностей, принадлежащих таблицам.
* Если последовательности не принадлежат таблицам, первоначально потребуется выполнить другой скрипт:
==Как исправить принадлежность последовательности==
Данный скрипт заменяет последовательности с OWNED BY на таблицы, колонки которых ссылаются на них.
Внимание! Последовательности, на которые ссылаются несколько таблиц одновременно, игнорируются.
(Запрос частично позаимствован с OmniTI Tasty Treats repository Роберта Трита (Robert Treat))
select 'ALTER SEQUENCE '|| quote_ident(min(schema_name)) ||'.'|| quote_ident(min(seq_name))
 ||' OWNED BY '|| quote_ident(min(table_name)) ||'.'|| quote_ident(min(column_name)) ||';'
from (
 select 
 n.nspname as schema_name,
 c.relname as table_name,
 a.attname as column_name,
 substring(d.adsrc from E'^nextval\\(([^]*)(?:::text|::regclass)?\\)') as seq_name 
 from pg_class c 
 join pg_attribute a on (c.oid=a.attrelid) 
 join pg_attrdef d on (a.attrelid=d.adrelid and a.attnum=d.adnum) 
 join pg_namespace n on (c.relnamespace=n.oid)
 where has_schema_privilege(n.oid,'USAGE')
 and n.nspname not like 'pg!_%' escape '!'
 and has_table_privilege(c.oid,'SELECT')
 and (not a.attisdropped)
 and d.adsrc ~ '^nextval'
) seq
group by seq_name having count(*)=1;
----
Данный запрос ищет последовательности, на которые нет ни одной ссылки. Дважды стоит проверить полученный вывод при выполнении скрипта - будет ли достигнут ожидаемый результат?
select ns.nspname as schema_name, seq.relname as seq_name
from pg_class as seq
join pg_namespace ns on (seq.relnamespace=ns.oid)
where seq.relkind = 'S'
 and not exists (select * from pg_depend where objid=seq.oid and deptype='a')
order by seq.relname;
Category:FAQ
Category:Administration
Category:Russian