pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: checking if sequence exists
12+ messages / 5 participants
[nested] [flat]
* Re: checking if sequence exists
@ 2013-11-15 19:30 Thara Vadakkeveedu <tharagv@yahoo.com>
0 siblings, 3 replies; 12+ messages in thread
From: Thara Vadakkeveedu @ 2013-11-15 19:30 UTC (permalink / raw)
To: pgsql-admin
How can we find out if a particular sequence exists ? The idea is to check if sequence first and if it does not exist then create it...the goal is to do this when we deploy the application war...
thanks
tg
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: checking if sequence exists
@ 2013-11-15 19:36 Thara Vadakkeveedu <tharagv@yahoo.com>
parent: Thara Vadakkeveedu <tharagv@yahoo.com>
2 siblings, 1 reply; 12+ messages in thread
From: Thara Vadakkeveedu @ 2013-11-15 19:36 UTC (permalink / raw)
To: Thara Vadakkeveedu <tharagv@yahoo.com>; pgsql-admin
I tried this from pg_admin, but I get a syntax error (unexpected character)
IF EXISTS (SELECT 0 FROM pg_class
WHERE relkind = 'S'
AND oid::regclass::text = 'public.' || quote_ident('hibernate_sequence'))
THEN
RAISE EXCEPTION 'sequence public.% already exists!', 'hibernate_sequence
ENF IF;
Thanks.
From: Thara Vadakkeveedu <tharagv@yahoo.com>
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 2:30 PM
Subject: Re: [ADMIN] checking if sequence exists
How can we find out if a particular sequence exists ? The idea is to check if sequence first and if it does not exist then create it...the goal is to do this when we deploy the application war...
thanks
tg
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: checking if sequence exists
@ 2013-11-15 19:36 Elliot <yields.falsehood@gmail.com>
parent: Thara Vadakkeveedu <tharagv@yahoo.com>
2 siblings, 0 replies; 12+ messages in thread
From: Elliot @ 2013-11-15 19:36 UTC (permalink / raw)
To: Thara Vadakkeveedu <tharagv@yahoo.com>; pgsql-admin
On 2013-11-15 14:30, Thara Vadakkeveedu wrote:
> How can we find out if a particular sequence exists ? The idea is to
> check if sequence first and if it does not exist then create it...the
> goal is to do this when we deploy the application war...
> thanks
> tg
>
In psql if you set ECHO_HIDDEN you can get it to dump out its
introspection queries, like the one for \ds, which gives you a list of
sequences.
For instance,
These steps:
\set ECHO_HIDDEN 1
\ds
Yield a query like this:
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm'
THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','s','')
AND n.nspname !~ '^pg_toast'
AND n.nspname = '<schemaname>'
and c.relname = '<sequencename>'
ORDER BY 1,2
;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: checking if sequence exists
@ 2013-11-15 19:38 Payal Singh <payal@omniti.com>
parent: Thara Vadakkeveedu <tharagv@yahoo.com>
2 siblings, 1 reply; 12+ messages in thread
From: Payal Singh @ 2013-11-15 19:38 UTC (permalink / raw)
To: Thara Vadakkeveedu <tharagv@yahoo.com>; +Cc: pgsql-admin
\d <schema_name>.<table_name> will show you columns, their types and the
modifiers. If you look in the modifiers section, you will be able to see
existing sequences.
Payal Singh,
OmniTi Computer Consulting Inc.
Junior Database Architect,
Phone: 240.646.0770 x 253
On Fri, Nov 15, 2013 at 2:30 PM, Thara Vadakkeveedu <tharagv@yahoo.com>wrote:
> How can we find out if a particular sequence exists ? The idea is to check
> if sequence first and if it does not exist then create it...the goal is to
> do this when we deploy the application war...
> thanks
> tg
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: checking if sequence exists
@ 2013-11-15 19:46 Thara Vadakkeveedu <tharagv@yahoo.com>
parent: Payal Singh <payal@omniti.com>
0 siblings, 0 replies; 12+ messages in thread
From: Thara Vadakkeveedu @ 2013-11-15 19:46 UTC (permalink / raw)
To: Payal Singh <payal@omniti.com>; +Cc: pgsql-admin
The name of the sequence is hibernate_sequence. I already checked in the pg_class table under catalogs..
Thanks,
________________________________
From: Payal Singh <payal@omniti.com>
To: Thara Vadakkeveedu <tharagv@yahoo.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 2:38 PM
Subject: Re: [ADMIN] checking if sequence exists
\d <schema_name>.<table_name> will show you columns, their types and the modifiers. If you look in the modifiers section, you will be able to see existing sequences.
Payal Singh,
OmniTi Computer Consulting Inc.
Junior Database Architect,
Phone: 240.646.0770 x 253
On Fri, Nov 15, 2013 at 2:30 PM, Thara Vadakkeveedu <tharagv@yahoo.com> wrote:
How can we find out if a particular sequence exists ? The idea is to check if sequence first and if it does not exist then create it...the goal is to do this when we deploy the application war...
>thanks
>tg
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: checking if sequence exists
@ 2013-11-15 20:06 Kevin Grittner <kgrittn@ymail.com>
parent: Thara Vadakkeveedu <tharagv@yahoo.com>
0 siblings, 1 reply; 12+ messages in thread
From: Kevin Grittner @ 2013-11-15 20:06 UTC (permalink / raw)
To: Thara Vadakkeveedu <tharagv@yahoo.com>; pgsql-admin
Thara Vadakkeveedu <tharagv@yahoo.com> wrote:
> I tried this from pg_admin, but I get a syntax error (unexpected
> character)
>
> IF EXISTS (SELECT 0 FROM pg_class
> WHERE relkind = 'S'
> AND oid::regclass::text = 'public.' || quote_ident('hibernate_sequence'))
> THEN
> RAISE EXCEPTION 'sequence public.% already exists!', 'hibernate_sequence
> ENF IF;
First, is this code in a plpgsql context (like a function
definition or a DO command)? If not, that IF is not going to work.
If it *is*, you seem to have a typo where you meant END IF.
Also note that if you force the oid to text for the comparison, it
is both more fragile and slower than if you convert the text to
regclass for the comparison.
explain analyze
SELECT 0 FROM pg_class
WHERE relkind = 'S'
AND oid::regclass::text = 'public.' || quote_ident('hibernate_sequence');
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=0.00..13.86 rows=1 width=0) (actual time=0.197..0.197 rows=0 loops=1)
Filter: ((relkind = 'S'::"char") AND (((oid)::regclass)::text = 'public.hibernate_sequence'::text))
Rows Removed by Filter: 295
Total runtime: 0.221 ms
(4 rows)
explain analyze
SELECT 0 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using pg_class_oid_index on pg_class (cost=0.28..8.29 rows=1 width=0) (actual time=0.031..0.032 rows=1 loops=1)
Index Cond: (oid = (('public.hibernate_sequence'::text)::regclass)::oid)
Filter: (relkind = 'S'::"char")
Total runtime: 0.062 ms
(4 rows)
Notice that there was a table scan, which was slower, and the
record was not found because the cast of the oid to regclass and
then text did not include the 'public." part. The other way used
an index and matched as you probably intended.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: checking if sequence exists
@ 2013-11-15 21:09 Thara Vadakkeveedu <tharagv@yahoo.com>
parent: Kevin Grittner <kgrittn@ymail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Thara Vadakkeveedu @ 2013-11-15 21:09 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; pgsql-admin
"First, is this code in a plpgsql contex"?
No, that is my problem.
Does it have to be inside a Create function block or can justwrapping the if with a BEGIN END; suffice ?
Thanks.
From: Kevin Grittner <kgrittn@ymail.com>
To: Thara Vadakkeveedu <tharagv@yahoo.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 3:06 PM
Subject: Re: [ADMIN] checking if sequence exists
Thara Vadakkeveedu <tharagv@yahoo.com> wrote:
> I tried this from pg_admin, but I get a syntax error (unexpected
> character)
>
> IF EXISTS (SELECT 0 FROM pg_class
> WHERE relkind = 'S'
> AND oid::regclass::text = 'public.' || quote_ident('hibernate_sequence'))
> THEN
> RAISE EXCEPTION 'sequence public.% already exists!', 'hibernate_sequence
> ENF IF;
First, is this code in a plpgsql context (like a function
definition or a DO command)? If not, that IF is not going to work.
If it *is*, you seem to have a typo where you meant END IF.
Also note that if you force the oid to text for the comparison, it
is both more fragile and slower than if you convert the text to
regclass for the comparison.
explain analyze
SELECT 0 FROM pg_class
WHERE relkind = 'S'
AND oid::regclass::text = 'public.' || quote_ident('hibernate_sequence');
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=0.00..13.86 rows=1 width=0) (actual time=0.197..0.197 rows=0 loops=1)
Filter: ((relkind = 'S'::"char") AND (((oid)::regclass)::text = 'public.hibernate_sequence'::text))
Rows Removed by Filter: 295
Total runtime: 0.221 ms
(4 rows)
explain analyze
SELECT 0 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using pg_class_oid_index on pg_class (cost=0.28..8.29 rows=1 width=0) (actual time=0.031..0.032 rows=1 loops=1)
Index Cond: (oid = (('public.hibernate_sequence'::text)::regclass)::oid)
Filter: (relkind = 'S'::"char")
Total runtime: 0.062 ms
(4 rows)
Notice that there was a table scan, which was slower, and the
record was not found because the cast of the oid to regclass and
then text did not include the 'public." part. The other way used
an index and matched as you probably intended.
--
Kevin Grittner
EDB: http://www.enterprisedb.com/
The Enterprise PostgreSQL Company
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: checking if sequence exists
@ 2013-11-15 21:13 Elliot <yields.falsehood@gmail.com>
parent: Thara Vadakkeveedu <tharagv@yahoo.com>
0 siblings, 1 reply; 12+ messages in thread
From: Elliot @ 2013-11-15 21:13 UTC (permalink / raw)
To: Thara Vadakkeveedu <tharagv@yahoo.com>; Kevin Grittner <kgrittn@ymail.com>; pgsql-admin
On 2013-11-15 16:09, Thara Vadakkeveedu wrote:
> "First, is this code in a plpgsql contex"?
> No, that is my problem.
> Does it have to be inside a Create function block or can justwrapping
> the if with a BEGIN END; suffice ?
>
Control structures like if statements don't exist in straight sql - you
need a procedural language like pl/pgsql for that. Wrapping sql
statements in begin/end only affects the transactional context of the
statements, it does not cause them to be interpreted as pl/pgsql. You
can either create a function or you can use a "DO" block, which is
sometimes what I use for deploy scripts
[http://www.postgresql.org/docs/9.3/static/sql-do.html].
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: checking if sequence exists
@ 2013-11-15 22:56 Thara Vadakkeveedu <tharagv@yahoo.com>
parent: Elliot <yields.falsehood@gmail.com>
0 siblings, 3 replies; 12+ messages in thread
From: Thara Vadakkeveedu @ 2013-11-15 22:56 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; pgsql-admin
By itself this sql works:
SELECT 0 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;
However when I create a function for it and run it I see an error
create function chk_sequence() returns integer as $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass
THEN
return 1;
ELSE
return 0;
END IF;
END;
$$ language plpgsql;
select chk_sequence();
ERROR: operator does not exist: oid = text
LINE 3: AND oid = ('public.' || quote_ident('hibernate_...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: SELECT EXISTS (SELECT 1 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass
Thanks.
From: Elliot <yields.falsehood@gmail.com>
To: Thara Vadakkeveedu <tharagv@yahoo.com>; Kevin Grittner <kgrittn@ymail.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 4:13 PM
Subject: Re: [ADMIN] checking if sequence exists
On 2013-11-15 16:09, Thara Vadakkeveedu wrote:
"First, is this code in a plpgsql contex"?
>
>No, that is my problem.
>
>Does it have to be inside a Create function block or can justwrapping the if with a BEGIN END; suffice ?
>
>Control structures like if statements don't exist in straight sql - you need a procedural language like pl/pgsql for that. Wrapping sql statements in begin/end only affects the transactional context of the statements, it does not cause them to be interpreted as pl/pgsql. You can either create a function or you can use a "DO" block, which is sometimes what I use for deploy scripts [http://www.postgresql.org/docs/9.3/static/sql-do.html
].
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: checking if sequence exists
@ 2013-11-16 01:36 Bob Lunney <bob_lunney@yahoo.com>
parent: Thara Vadakkeveedu <tharagv@yahoo.com>
2 siblings, 0 replies; 12+ messages in thread
From: Bob Lunney @ 2013-11-16 01:36 UTC (permalink / raw)
To: Thara Vadakkeveedu <tharagv@yahoo.com>; Kevin Grittner <kgrittn@ymail.com>; pgsql-admin
Thara,
Here are your functions:
bob=> create sequence foo;
CREATE SEQUENCE
bob=> create or replace function public.chk_sequence(namespace name, seq_name name)
bob-> returns int
bob-> language sql
bob-> as $$
bob$> select count(*)::int as sequence_exists
bob$> from pg_class c, pg_namespace n
bob$> where c.relnamespace = n.oid
bob$> and c.relkind in ('S', 's', '')
bob$> and pg_table_is_visible(c.oid)
bob$> and n.nspname = $1
bob$> and c.relname = $2;
bob$> $$;
CREATE FUNCTION
bob=> select chk_sequence('public', 'foo');
chk_sequence
--------------
1
(1 row)
bob=> select chk_sequence('public', 'bar');
chk_sequence
--------------
0
(1 row)
bob=> ----
bob=> -- when you get tired of typing 'public' use this version
bob=> ----
bob=> create or replace function chk_sequence(seq_name name)
bob-> returns int
bob-> language sql
bob-> as $$
bob$> select chk_sequence('public', $1);
bob$> $$;
CREATE FUNCTION
bob=> select chk_sequence('foo');
chk_sequence
--------------
1
(1 row)
bob=> select chk_sequence('bar');
chk_sequence
--------------
0
(1 row)
On Friday, November 15, 2013 5:39 PM, Thara Vadakkeveedu <tharagv@yahoo.com> wrote:
By itself this sql works:
SELECT 0 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;
However when I create a function for it and run it I see an error
create function chk_sequence() returns integer as $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_class
WHERE relkind = 'S'
AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass
THEN
return 1;
ELSE
return 0;
END IF;
END;
$$ language plpgsql;
select chk_sequence();
ERROR: operator does not exist: oid = text
LINE 3: AND oid = ('public.' || quote_ident('hibernate_...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: SELECT EXISTS (SELECT 1 FROM pg_class
WHERE relkind = 'S'
AND oid =
('public.' || quote_ident('hibernate_sequence')))::regclass
Thanks.
From: Elliot <yields.falsehood@gmail.com>
To: Thara Vadakkeveedu <tharagv@yahoo.com>; Kevin Grittner <kgrittn@ymail.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 4:13 PM
Subject: Re: [ADMIN] checking if sequence exists
On 2013-11-15 16:09, Thara Vadakkeveedu wrote:
"First, is this code in a plpgsql contex"?
>
>No, that is my problem.
>
>Does it have to be inside a Create function block or can justwrapping the if with a BEGIN END; suffice ?
>
>Control structures like if statements don't exist in straight sql - you need a procedural language like pl/pgsql for that. Wrapping sql statements in begin/end only affects the transactional context of the statements, it does not cause them to be interpreted as pl/pgsql. You can either create a function or you can use a "DO" block, which is sometimes what I use for deploy scripts [http://www.postgresql.org/docs/9.3/static/sql-do.html
].
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: checking if sequence exists
@ 2013-11-16 02:43 Kevin Grittner <kgrittn@ymail.com>
parent: Thara Vadakkeveedu <tharagv@yahoo.com>
2 siblings, 0 replies; 12+ messages in thread
From: Kevin Grittner @ 2013-11-16 02:43 UTC (permalink / raw)
To: Thara Vadakkeveedu <tharagv@yahoo.com>; pgsql-admin
Thara Vadakkeveedu <tharagv@yahoo.com> wrote:
> By itself this sql works:
> SELECT 0 FROM pg_class
> WHERE relkind = 'S'
> AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;
>
> However when I create a function for it and run it I see an error
>
> create function chk_sequence() returns integer as $$
> BEGIN
> IF EXISTS (SELECT 1 FROM pg_class
> WHERE relkind = 'S'
> AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass
> THEN
> return 1;
> ELSE
> return 0;
> END IF;
> END;
> $$ language plpgsql;
Move the cast to regclass inside one level of parentheses.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: checking if sequence exists
@ 2013-11-18 14:54 Elliot <yields.falsehood@gmail.com>
parent: Thara Vadakkeveedu <tharagv@yahoo.com>
2 siblings, 0 replies; 12+ messages in thread
From: Elliot @ 2013-11-18 14:54 UTC (permalink / raw)
To: Thara Vadakkeveedu <tharagv@yahoo.com>; Kevin Grittner <kgrittn@ymail.com>; pgsql-admin
On 2013-11-15 17:56, Thara Vadakkeveedu wrote:
> By itself this sql works:
> SELECT 0 FROM pg_class
> WHERE relkind = 'S'
> AND oid = ('public.' ||
> quote_ident('hibernate_sequence'))::regclass;
> However when I create a function for it and run it I see an error
> create function chk_sequence() returns integer as $$
> BEGIN
> IF EXISTS (SELECT 1 FROM pg_class
> WHERE relkind = 'S'
> AND oid = ('public.' ||
> quote_ident('hibernate_sequence')))::regclass
> THEN
> return 1;
> ELSE
> return 0;
> END IF;
> END;
> $$ language plpgsql;
> select chk_sequence();
> ERROR: operator does not exist: oid = text
> LINE 3: AND oid = ('public.' || quote_ident('hibernate_...
> ^
> HINT: No operator matches the given name and argument type(s). You
> might need to add explicit type casts.
> QUERY: SELECT EXISTS (SELECT 1 FROM pg_class
> WHERE relkind = 'S'
> AND oid = ('public.' ||
> quote_ident('hibernate_sequence')))::regclass
> Thanks.
>
You've got two different queries there. In the first example you're
casting the string public.hibernate_sequence to regclass. In the second
you've got the parentheses capturing the regclass cast around the entire
query.
However, you probably don't want to use this query anyway as the
regclass cast will fail with an exception if the sequence doesn't exist
(meaning you'd have to wrap it in an exception catching block instead of
an in-else block). This uses the query I sent out in an early response:
create function chk_sequence() returns integer as $$
BEGIN
IF EXISTS (SELECT 1
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace
WHERE c.relkind IN ('S','s','')
AND n.nspname !~ '^pg_toast'
AND n.nspname = 'public'
and c.relname = 'hibernate_sequence')
THEN
return 1;
ELSE
return 0;
END IF;
END;
$$ language plpgsql;
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2013-11-18 14:54 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-11-15 19:30 Re: checking if sequence exists Thara Vadakkeveedu <tharagv@yahoo.com>
2013-11-15 19:36 ` Thara Vadakkeveedu <tharagv@yahoo.com>
2013-11-15 20:06 ` Kevin Grittner <kgrittn@ymail.com>
2013-11-15 21:09 ` Thara Vadakkeveedu <tharagv@yahoo.com>
2013-11-15 21:13 ` Elliot <yields.falsehood@gmail.com>
2013-11-15 22:56 ` Thara Vadakkeveedu <tharagv@yahoo.com>
2013-11-16 01:36 ` Bob Lunney <bob_lunney@yahoo.com>
2013-11-16 02:43 ` Kevin Grittner <kgrittn@ymail.com>
2013-11-18 14:54 ` Elliot <yields.falsehood@gmail.com>
2013-11-15 19:36 ` Elliot <yields.falsehood@gmail.com>
2013-11-15 19:38 ` Payal Singh <payal@omniti.com>
2013-11-15 19:46 ` Thara Vadakkeveedu <tharagv@yahoo.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