pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSequences
14+ messages / 10 participants
[nested] [flat]
* Sequences
@ 2002-10-28 18:56 Danny Morgan <dmorgan@corpmedia.com>
2002-10-30 13:12 ` Re: Sequences Gareth Kirwan <gbjk@thermeoneurope.com>
0 siblings, 1 reply; 14+ messages in thread
From: dmorgan@corpmedia.com @ 2002-10-28 18:56 UTC (permalink / raw)
To: pgsql-admin
I have a strange dilemma. I've created a couple sequences I can't
remove.
Here is an example
I can
CREATE SEQUENCE "count1" ..........;
DROP SEQUENCE count1;
I can't
CREATE SEQUENCE "count1ListItems1" ..........;
DROP SEQUENCE count1ListItems1;
It states sequence xx does not exist.
I can \ds and it is there.
I'm using v7.2.3, is there a way to manually remove a sequence or best
yet why can't I remove a sequence that has only 16 chars?
Thanks,
Danny L. Morgan
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Sequences
2002-10-28 18:56 Sequences Danny Morgan <dmorgan@corpmedia.com>
@ 2002-10-30 13:12 ` Gareth Kirwan <gbjk@thermeoneurope.com>
0 siblings, 0 replies; 14+ messages in thread
From: Gareth Kirwan @ 2002-10-30 13:12 UTC (permalink / raw)
To: Danny Morgan <dmorgan@corpmedia.com>; pgsql-admin
* smacks your hand for using uppercase letters *
Another big no no is not keeping your create sequences for sequences ( what
a pun ) in a file somewhere.
Otherwise you can't drop them because you don't remember what parameters
they took ( but you can always, as you say, look at system tables )
Check the \ds.
If not there try \dS, which leads you to do:
select * from pg_statio_all_sequences;
if it's still not there - give up - it's not there :-p
Gareth
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Danny Morgan
Sent: 28 October 2002 18:57
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Sequences
I have a strange dilemma. I've created a couple sequences I can't
remove.
Here is an example
I can
CREATE SEQUENCE "count1" ..........;
DROP SEQUENCE count1;
I can't
CREATE SEQUENCE "count1ListItems1" ..........;
DROP SEQUENCE count1ListItems1;
It states sequence xx does not exist.
I can \ds and it is there.
I'm using v7.2.3, is there a way to manually remove a sequence or best
yet why can't I remove a sequence that has only 16 chars?
Thanks,
Danny L. Morgan
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 14+ messages in thread
* Sequences
@ 2002-10-28 19:00 Danny Morgan <dmorgan@corpmedia.com>
2002-10-28 21:42 ` Re: Sequences Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 14+ messages in thread
From: dmorgan@corpmedia.com @ 2002-10-28 19:00 UTC (permalink / raw)
To: pgsql-admin
I have a strange dilemma. I've created a couple sequences I can't
remove.
Here is an example
I can
CREATE SEQUENCE "count1" ..........;
DROP SEQUENCE count1;
I can't
CREATE SEQUENCE "count1ListItems1" ..........;
DROP SEQUENCE count1ListItems1;
It states sequence xx does not exist.
I can \ds and it is there.
I'm using v7.2.3, is there a way to manually remove a sequence or best
yet why can't I remove a sequence that has only 16 chars?
Thanks,
Danny L. Morgan
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Sequences
2002-10-28 19:00 Sequences Danny Morgan <dmorgan@corpmedia.com>
@ 2002-10-28 21:42 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 14+ messages in thread
From: Bruno Wolff III @ 2002-10-28 21:42 UTC (permalink / raw)
To: Danny Morgan <dmorgan@corpmedia.com>; +Cc: pgsql-admin
On Mon, Oct 28, 2002 at 14:00:44 -0500,
Danny Morgan <dmorgan@corpmedia.com> wrote:
> I have a strange dilemma. I've created a couple sequences I can't
> remove.
>
> Here is an example
>
> I can
> CREATE SEQUENCE "count1" ..........;
> DROP SEQUENCE count1;
>
> I can't
> CREATE SEQUENCE "count1ListItems1" ..........;
> DROP SEQUENCE count1ListItems1;
Try:
DROP SEQUENCE "count1ListItems1";
(Quoting preserves capitalization.)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Sequences
@ 2002-10-28 21:51 Danny Morgan <dmorgan@corpmedia.com>
0 siblings, 0 replies; 14+ messages in thread
From: dmorgan@corpmedia.com @ 2002-10-28 21:51 UTC (permalink / raw)
To: bruno@wolff.to; +Cc: pgsql-admin
Thanks, worked like a charm.
Danny L. Morgan
-----Original Message-----
From: bruno [mailto:bruno@wolff.to]
Sent: Monday, October 28, 2002 4:42 PM
To: dmorgan
Cc: pgsql-admin
Subject: Re: [ADMIN] Sequences
On Mon, Oct 28, 2002 at 14:00:44 -0500,
Danny Morgan <dmorgan@corpmedia.com> wrote:
> I have a strange dilemma. I've created a couple sequences I can't
> remove.
>
> Here is an example
>
> I can
> CREATE SEQUENCE "count1" ..........;
> DROP SEQUENCE count1;
>
> I can't
> CREATE SEQUENCE "count1ListItems1" ..........;
> DROP SEQUENCE count1ListItems1;
Try:
DROP SEQUENCE "count1ListItems1";
(Quoting preserves capitalization.)
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 14+ messages in thread
* sequences
@ 2007-04-04 19:19 Alexander B. <burbello3000@yahoo.com.br>
2007-04-04 19:49 ` Re: sequences Jeff Frost <jeff@frostconsultingllc.com>
2007-04-04 19:55 ` Re: sequences Bricklen Anderson <banderson@presinet.com>
0 siblings, 2 replies; 14+ messages in thread
From: Alexander B. @ 2007-04-04 19:19 UTC (permalink / raw)
To: pgsql-admin
Hi,
I tried to find, but I didn't, I would like to know what's the view to
list all sequences!
Thank you
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: sequences
2007-04-04 19:19 sequences Alexander B. <burbello3000@yahoo.com.br>
@ 2007-04-04 19:49 ` Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 0 replies; 14+ messages in thread
From: Jeff Frost @ 2007-04-04 19:49 UTC (permalink / raw)
To: Alexander B. <burbello3000@yahoo.com.br>; +Cc: pgsql-admin
On Wed, 4 Apr 2007, Alexander B. wrote:
> Hi,
>
> I tried to find, but I didn't, I would like to know what's the view to
> list all sequences!
>
You can get the info from pg_class like so:
SELECT n.nspname
, c.relname
FROM pg_class c
, pg_namespace n
WHERE c.relnamespace = n.OID
AND c.relkind = 'S'
;
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: sequences
2007-04-04 19:19 sequences Alexander B. <burbello3000@yahoo.com.br>
@ 2007-04-04 19:55 ` Bricklen Anderson <banderson@presinet.com>
1 sibling, 0 replies; 14+ messages in thread
From: Bricklen Anderson @ 2007-04-04 19:55 UTC (permalink / raw)
To: Alexander B. <burbello3000@yahoo.com.br>; +Cc: pgsql-admin
Alexander B. wrote:
> Hi,
>
> I tried to find, but I didn't, I would like to know what's the view to
> list all sequences!
>
> Thank you
>
\ds
or
select * from pg_class where relkind = 'S';
^ permalink raw reply [nested|flat] 14+ messages in thread
* Sequences
@ 2018-12-04 19:03 Campbell, Lance <lance@illinois.edu>
2018-12-04 19:17 ` Re: Sequences Alex Balashov <abalashov@evaristesys.com>
0 siblings, 1 reply; 14+ messages in thread
From: Campbell, Lance @ 2018-12-04 19:03 UTC (permalink / raw)
To: pgsql-admin
PostgreSQL 10.x
What query will give the name of all sequences in a database and the current or next value for each sequence?
This will give me everything except for the next value in the sequence.
SELECT * FROM information_schema.sequences;
Thanks,
Lance
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Sequences
2018-12-04 19:03 Sequences Campbell, Lance <lance@illinois.edu>
@ 2018-12-04 19:17 ` Alex Balashov <abalashov@evaristesys.com>
2018-12-04 19:26 ` Re: Sequences Alex Balashov <abalashov@evaristesys.com>
2018-12-04 19:47 ` Re: Sequences Campbell, Lance <lance@illinois.edu>
0 siblings, 2 replies; 14+ messages in thread
From: Alex Balashov @ 2018-12-04 19:17 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Constructing dynamic SQL is always a bit tricky. Try define this
function:
---
CREATE OR REPLACE FUNCTION seqs_last_val()
RETURNS SETOF record
AS $$
DECLARE
_seqname varchar;
_r record;
BEGIN
SELECT INTO _r null::varchar AS seqname, -1::integer AS lastval;
FOR _seqname IN
SELECT sequence_name
FROM information_schema.sequences
WHERE sequence_schema = 'public'
LOOP
_r.seqname = _seqname;
EXECUTE format('SELECT last_value FROM %s', quote_ident(_seqname)) INTO _r.lastval;
RETURN NEXT _r;
END LOOP;
RETURN;
END
$$ LANGUAGE 'plpgsql';
---
Then run:
SELECT * FROM seqs_last_val() AS (seqname varchar, last_value integer);
-- Alex
On Tue, Dec 04, 2018 at 07:03:11PM +0000, Campbell, Lance wrote:
> PostgreSQL 10.x
>
> What query will give the name of all sequences in a database and the current or next value for each sequence?
>
> This will give me everything except for the next value in the sequence.
>
> SELECT * FROM information_schema.sequences;
>
> Thanks,
>
> Lance
--
Alex Balashov | Principal | Evariste Systems LLC
Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free)
Web: http://www.evaristesys.com/, http://www.csrpswitch.com/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Sequences
2018-12-04 19:03 Sequences Campbell, Lance <lance@illinois.edu>
2018-12-04 19:17 ` Re: Sequences Alex Balashov <abalashov@evaristesys.com>
@ 2018-12-04 19:26 ` Alex Balashov <abalashov@evaristesys.com>
1 sibling, 0 replies; 14+ messages in thread
From: Alex Balashov @ 2018-12-04 19:26 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
If you have no permission or inclination to actually define something in
the database, here is a shell-based alternative:
---
# psql -Aqt -U evaristesys_adm -d evaristesys \
-c "SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public' ORDER BY sequence_name;" \
| while read SEQ;
do
echo -n "$SEQ: ";
psql -Aqt -U evaristesys_adm -d evaristesys -c "SELECT last_value FROM $SEQ;";
done
---
-- Alex
--
Alex Balashov | Principal | Evariste Systems LLC
Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free)
Web: http://www.evaristesys.com/, http://www.csrpswitch.com/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Sequences
2018-12-04 19:03 Sequences Campbell, Lance <lance@illinois.edu>
2018-12-04 19:17 ` Re: Sequences Alex Balashov <abalashov@evaristesys.com>
@ 2018-12-04 19:47 ` Campbell, Lance <lance@illinois.edu>
2018-12-05 06:11 ` Re: Sequences Rui DeSousa <rui@crazybean.net>
1 sibling, 1 reply; 14+ messages in thread
From: Campbell, Lance @ 2018-12-04 19:47 UTC (permalink / raw)
To: Alex Balashov <abalashov@evaristesys.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thanks so much. This was very helpful! Thanks.
Lance
On 12/4/18, 1:18 PM, "Alex Balashov" <abalashov@evaristesys.com> wrote:
Constructing dynamic SQL is always a bit tricky. Try define this
function:
---
CREATE OR REPLACE FUNCTION seqs_last_val()
RETURNS SETOF record
AS $$
DECLARE
_seqname varchar;
_r record;
BEGIN
SELECT INTO _r null::varchar AS seqname, -1::integer AS lastval;
FOR _seqname IN
SELECT sequence_name
FROM information_schema.sequences
WHERE sequence_schema = 'public'
LOOP
_r.seqname = _seqname;
EXECUTE format('SELECT last_value FROM %s', quote_ident(_seqname)) INTO _r.lastval;
RETURN NEXT _r;
END LOOP;
RETURN;
END
$$ LANGUAGE 'plpgsql';
---
Then run:
SELECT * FROM seqs_last_val() AS (seqname varchar, last_value integer);
-- Alex
On Tue, Dec 04, 2018 at 07:03:11PM +0000, Campbell, Lance wrote:
> PostgreSQL 10.x
>
> What query will give the name of all sequences in a database and the current or next value for each sequence?
>
> This will give me everything except for the next value in the sequence.
>
> SELECT * FROM information_schema.sequences;
>
> Thanks,
>
> Lance
--
Alex Balashov | Principal | Evariste Systems LLC
Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free)
Web: http://www.evaristesys.com/, http://www.csrpswitch.com/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Sequences
2018-12-04 19:03 Sequences Campbell, Lance <lance@illinois.edu>
2018-12-04 19:17 ` Re: Sequences Alex Balashov <abalashov@evaristesys.com>
2018-12-04 19:47 ` Re: Sequences Campbell, Lance <lance@illinois.edu>
@ 2018-12-05 06:11 ` Rui DeSousa <rui@crazybean.net>
2018-12-05 07:37 ` Re: Sequences Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Rui DeSousa @ 2018-12-05 06:11 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: Alex Balashov <abalashov@evaristesys.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Why not just do this:
select schemaname
, sequencename
, last_value
, increment_by
from pg_sequences
;
> On Dec 4, 2018, at 2:47 PM, Campbell, Lance <lance@illinois.edu> wrote:
>
> Thanks so much. This was very helpful! Thanks.
>
> Lance
>
> On 12/4/18, 1:18 PM, "Alex Balashov" <abalashov@evaristesys.com> wrote:
>
> Constructing dynamic SQL is always a bit tricky. Try define this
> function:
>
> ---
> CREATE OR REPLACE FUNCTION seqs_last_val()
> RETURNS SETOF record
> AS $$
> DECLARE
> _seqname varchar;
> _r record;
> BEGIN
> SELECT INTO _r null::varchar AS seqname, -1::integer AS lastval;
>
> FOR _seqname IN
> SELECT sequence_name
> FROM information_schema.sequences
> WHERE sequence_schema = 'public'
> LOOP
> _r.seqname = _seqname;
> EXECUTE format('SELECT last_value FROM %s', quote_ident(_seqname)) INTO _r.lastval;
> RETURN NEXT _r;
> END LOOP;
>
> RETURN;
> END
> $$ LANGUAGE 'plpgsql';
> ---
>
> Then run:
>
> SELECT * FROM seqs_last_val() AS (seqname varchar, last_value integer);
>
> -- Alex
>
> On Tue, Dec 04, 2018 at 07:03:11PM +0000, Campbell, Lance wrote:
>> PostgreSQL 10.x
>>
>> What query will give the name of all sequences in a database and the current or next value for each sequence?
>>
>> This will give me everything except for the next value in the sequence.
>>
>> SELECT * FROM information_schema.sequences;
>>
>> Thanks,
>>
>> Lance
>
> --
> Alex Balashov | Principal | Evariste Systems LLC
>
> Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free)
> Web: http://www.evaristesys.com/, http://www.csrpswitch.com/
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Sequences
2018-12-04 19:03 Sequences Campbell, Lance <lance@illinois.edu>
2018-12-04 19:17 ` Re: Sequences Alex Balashov <abalashov@evaristesys.com>
2018-12-04 19:47 ` Re: Sequences Campbell, Lance <lance@illinois.edu>
2018-12-05 06:11 ` Re: Sequences Rui DeSousa <rui@crazybean.net>
@ 2018-12-05 07:37 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Shreeyansh Dba @ 2018-12-05 07:37 UTC (permalink / raw)
To: rui@crazybean.net; +Cc: lance@illinois.edu; abalashov@evaristesys.com; pgsql-admin@lists.postgresql.org
Hi Lance,
Please look into this and go through the below link..
http://www.postgresqltutorial.com/postgresql-serial/
Hope this help..
<http://www.shreeyansh.com;
On Wed, Dec 5, 2018 at 11:42 AM Rui DeSousa <rui@crazybean.net> wrote:
>
> Why not just do this:
>
> select schemaname
> , sequencename
> , last_value
> , increment_by
> from pg_sequences
> ;
>
>
> > On Dec 4, 2018, at 2:47 PM, Campbell, Lance <lance@illinois.edu> wrote:
> >
> > Thanks so much. This was very helpful! Thanks.
> >
> > Lance
> >
> > On 12/4/18, 1:18 PM, "Alex Balashov" <abalashov@evaristesys.com> wrote:
> >
> > Constructing dynamic SQL is always a bit tricky. Try define this
> > function:
> >
> > ---
> > CREATE OR REPLACE FUNCTION seqs_last_val()
> > RETURNS SETOF record
> > AS $$
> > DECLARE
> > _seqname varchar;
> > _r record;
> > BEGIN
> > SELECT INTO _r null::varchar AS seqname, -1::integer AS lastval;
> >
> > FOR _seqname IN
> > SELECT sequence_name
> > FROM information_schema.sequences
> > WHERE sequence_schema = 'public'
> > LOOP
> > _r.seqname = _seqname;
> > EXECUTE format('SELECT last_value FROM %s',
> quote_ident(_seqname)) INTO _r.lastval;
> > RETURN NEXT _r;
> > END LOOP;
> >
> > RETURN;
> > END
> > $$ LANGUAGE 'plpgsql';
> > ---
> >
> > Then run:
> >
> > SELECT * FROM seqs_last_val() AS (seqname varchar, last_value
> integer);
> >
> > -- Alex
> >
> > On Tue, Dec 04, 2018 at 07:03:11PM +0000, Campbell, Lance wrote:
> >> PostgreSQL 10.x
> >>
> >> What query will give the name of all sequences in a database and the
> current or next value for each sequence?
> >>
> >> This will give me everything except for the next value in the sequence.
> >>
> >> SELECT * FROM information_schema.sequences;
> >>
> >> Thanks,
> >>
> >> Lance
> >
> > --
> > Alex Balashov | Principal | Evariste Systems LLC
> >
> > Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free)
> > Web: http://www.evaristesys.com/, http://www.csrpswitch.com/
> >
> >
> >
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2018-12-05 07:37 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-28 18:56 Sequences Danny Morgan <dmorgan@corpmedia.com>
2002-10-30 13:12 ` Gareth Kirwan <gbjk@thermeoneurope.com>
2002-10-28 19:00 Sequences Danny Morgan <dmorgan@corpmedia.com>
2002-10-28 21:42 ` Bruno Wolff III <bruno@wolff.to>
2002-10-28 21:51 Re: Sequences Danny Morgan <dmorgan@corpmedia.com>
2007-04-04 19:19 sequences Alexander B. <burbello3000@yahoo.com.br>
2007-04-04 19:49 ` Re: sequences Jeff Frost <jeff@frostconsultingllc.com>
2007-04-04 19:55 ` Re: sequences Bricklen Anderson <banderson@presinet.com>
2018-12-04 19:03 Sequences Campbell, Lance <lance@illinois.edu>
2018-12-04 19:17 ` Alex Balashov <abalashov@evaristesys.com>
2018-12-04 19:26 ` Alex Balashov <abalashov@evaristesys.com>
2018-12-04 19:47 ` Campbell, Lance <lance@illinois.edu>
2018-12-05 06:11 ` Rui DeSousa <rui@crazybean.net>
2018-12-05 07:37 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox