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
Group Query Results by Month and Year in Postgresql
Identity_Insert Is Set to Off - How to Turn It On
Select Newest Records That Have Distinct Name Column
SQL Server: How to Get All Child Records Given a Parent Id in a Self Referencing Table
The Best Way to Use a Db Table as a Job Queue (A.K.A Batch Queue or Message Queue)
How to List Records with Date from the Last 10 Days
Use Tnsnames.Ora in Oracle SQL Developer
SQL Server: Get Data for Only the Past Year
SQL Statement Indentation Good Practice
How to Index a Database Column
If-Then-Else Statements in Postgresql
How to Find the Size of a Table in SQL
Get Month and Year from a Datetime in SQL Server 2005
Difference Between Drop Table and Truncate Table
Getting the Number of Rows with a Group by Query