List All Sequences in a Postgres Db 8.1 with SQL

List all sequences in a Postgres db 8.1 with SQL

The following query gives names of all sequences.

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

Typically a sequence is named as ${table}_id_seq. Simple regex pattern matching will give you the table name.

To get last value of a sequence use the following query:

SELECT last_value FROM test_id_seq;

Is there any way to list all the views related to a table in the existing postgres schema

Use this query:

select
u.view_schema schema_name,
u.view_name,
u.table_schema referenced_table_schema,
u.table_name referenced_table_name,
v.view_definition
from information_schema.view_table_usage u
join information_schema.views v on u.view_schema = v.table_schema
and u.view_name = v.table_name
where u.table_schema not in ('information_schema', 'pg_catalog')
order by u.view_schema, u.view_name

Credit: Dataedo.com's article List tables used by a view in PostgreSQL database

How to list all indexes of a table with their corresponding size in PostgreSQL?

Use pg_indexes.

select indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) as size
from pg_indexes
where tablename = 'my_table';

SQL | List all all tuples(a, b, c) if there exists another tuple with equal (b,c)

Use the aggregate function string_agg() to get the full list of foods for each restaurant:

with cte as (
select restaurant_ID,
string_agg(food_ID::varchar(10),',' order by food_ID) foods
from identifier
group by restaurant_ID
)
select r.*
from Restaurants r inner join cte c
on c.restaurant_ID = r.restaurant_ID
where exists (select 1 from cte where restaurant_ID <> c.restaurant_ID and foods = c.foods)

But I would prefer to group restaurants based on matching foods:

with cte as (
select restaurant_ID,
string_agg(food_ID::varchar(10),',' order by food_ID) foods
from identifier
group by restaurant_ID
)
select string_agg(r.name, ',') restaurants
from Restaurants r inner join cte c
on c.restaurant_ID = r.restaurant_ID
group by foods
having count(*) > 1

See the demo.

How to specify list of values for a postgresql sequence

Could work like this:

-- DROP SCHEMA x CASCADE;
CREATE SCHEMA x;
CREATE TABLE x.priv_id(seq_id int primary key, id int);

INSERT INTO x.priv_id
SELECT generate_series(1,100,1), (random() * 1000)::int;

CREATE SEQUENCE x.priv_seq;

SELECT id
FROM x.priv_id
WHERE seq_id = (SELECT nextval('x.priv_seq'));

Major points:

1) Create a lookup table with two numbers

- seq_id is counting from 1 and your primary key.

- id is your numbers in sequence (I substituted random numbers here).

2) Create a helper sequence.

3) Get your numbers with a SELECT like above.

You need the subselect, or all values will be returned at once.

This solution gives all the security nextval() has to offer for concurrency.

Create a unique index on priv_id(id) if you want to make sure your custom id's are unique.

how to conditional sequences

You might create the name of the sequence depending on the name of new.serie:

CREATE OR REPLACE FUNCTION get_webf_serial() RETURNS trigger AS
$BODY$
declare
seq_name TEXT := FORMAT('seq_%s', new.serie);
begin
PERFORM * FROM information_schema.sequences
WHERE sequence_schema = 'public' AND sequence_name = seq_name;
IF NOT FOUND THEN
seq_name := 'seq_any';
END IF;
new.folio: = nextval(seq_name);
return new;
end;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;


Related Topics



Leave a reply



Submit