Hello,
I need to find full list of the sequences + the table.column_names.

I know how to find sequences:
SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

I know how to find table + columns:
SELECT c.relname,a.attnum, a.attname AS field, t.typname AS type,
a.attlen AS length, a.atttypmod AS length_var,
a.attnotnull AS not_null, a.atthasdef as has_default
FROM pg_class c, pg_attribute a, pg_type t
WHERE (1=1)
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
ORDER BY a.attnum;

But how to relate them?

Thanks in advance!

I use this query to show me tonnes of stuff including the default values of columns which include sequence names:

select x.nspname || '.' || x.relname as "Table", x.attnum as "#", x.attname as "Column", x."Type", case x.attnotnull when true then 'NOT NULL' else '' end as "NULL?"
, r.conname as "Constraint", r.contype as "C", r.consrc, fn.nspname || '.' || f.relname as "F Key", d.adsrc as "Default"
from (
SELECT c.oid, a.attrelid, a.attnum, n.nspname, c.relname, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type", a.attnotnull
FROM pg_catalog.pg_attribute a, pg_namespace n, pg_class c
WHERE a.attnum > 0
AND NOT a.attisdropped
AND a.attrelid = c.oid
and c.relkind not in ('S','v')
and c.relnamespace = n.oid
and n.nspname not in ('pg_catalog','pg_toast','information_schema')
) x
left join pg_attrdef d on d.adrelid = x.attrelid and d.adnum = x.attnum
left join pg_constraint r on r.conrelid = x.oid and r.conkey[1] = x.attnum
left join pg_class f on r.confrelid = f.oid
left join pg_namespace fn on f.relnamespace = fn.oid
where x.relname = 'TABLE NAME HERE'
order by 1,2

Unfortunately it shows not all sequences. Only few of them

It should list any sequences attached to the tables? If you also want sequences that are not linked in any way the catalog knows about, you're going to have a hard time

What do you mean "attached"? I have table and the sequence and nextval is working. Can it be not attached?

Sorry, by attached I mean when you use a serial column and the sequence is automatically used as the default for it, or you explicitly use a sequence as a columns default value.

If you create a table then a sequence and the sql calls the sequence to get a value, then inserts that value into the table - PostgreSQL knows nothing about the relationship between the two.

ok, thanks a lot. Got it

I know that my solution is very specific, but still it might help somebody. Almost all sequences in my database are named table_name_seq, so I did following:

SELECT a.table_name AS table_name,
a.relname AS sequence_name
FROM
(
SELECT CASE
WHEN UPPER(c.relname) LIKE E'%\\_ID\\_%' -- for the sequence named table_name_ID_seq
THEN SUBSTRING(c.relname from 1 for char_length(c.relname) - 7)
WHEN UPPER(c.relname) LIKE E'%\\_KRKEY\\_%' -- for sequence named table_name_krkey_seq
THEN SUBSTRING(c.relname from 1 for char_length(c.relname) - 10)
ELSE SUBSTRING(c.relname from 1 for char_length(c.relname) - 4) -- all other sequences
END AS table_name,
c.relname
FROM pg_class c WHERE c.relkind = 'S'
) a
JOIN pg_class AS rl -- checks that such table exists
ON rl.relname = a.table_name

The information schema may have a lot of what you need, also. If it does, it is preferable to accessing the system tables, as it is portable to other database vendors as well...

as 'someidiot' (sounds nice ) stated previously that there is no connection of sequence to actual table if it is not stated as default value in DDL

One can "assign" a sequence to a table column after the fact by using the OWNED BY clause in your CREATE SEQUENCE statement.

PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: CREATE SEQUENCE

Whether or not this relationship can be identified/retrieved from the system metadata, however, I have no idea.

This statement lists the table and column that is associated with each sequence: