473,846 Members | 1,972 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

determine sequence name for a serial

I am trying to track down a method of determining what a sequence name
is for a SERIAL is in postgresql.

For example,

CREATE TABLE foo (id SERIAL PRIMARY KEY NOT NULL, bar TEXT);

\d foo
Table "public.foo "
Column | Type | Modifiers
--------+---------+-----------------------------------------------------
id | integer | not null default nextval('public .foo_id_seq'::t ext)
bar | text |
Indexes:
"foo_pkey" primary key, btree (id)

Now, I have figured out how to get a list of all the sequences with:

foo=> SELECT relname FROM pg_class WHERE relkind='S' AND relname !~ '^pg_';
relname
------------
foo_id_seq
(1 row)

I can find public.foo in pg_tables, but I am not sure how to relate pg_tables and pg_class in order to find the sequence for a specific field in public.foo.

Can anyone point me in the right direction? I am trying to get out of the habit of hard-coding the sequence names in my code.

Now that I think of it, I am lacking 'public.' as well from my query.

Ok, so how would I go about getting the sequence name for a SERIAL field onany given schema.table? I would like to build a function that would returnthis value if I pass it the schema and table (and fieldname is necessary)

Thanks,

Robby
--
/*************** *************** *********
* Robby Russell | Owner.Developer .Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | ro***@planetarg on.com
* 503.351.4730 | blog.planetargo n.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
*************** *************** **********/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBBgHA30Qa QZBaqXgwRAkcMAJ 4vTrjsPBA+UWsbO R/sy4KHofIOEgCeNS 3X
WQk4+eh0LUgeeIo sTGKISVM=
=3fZd
-----END PGP SIGNATURE-----

Nov 23 '05 #1
15 9664
On Wed, 2004-10-27 at 21:06 -0700, Robby Russell wrote:
I am trying to track down a method of determining what a sequence name
is for a SERIAL is in postgresql.

For example,

CREATE TABLE foo (id SERIAL PRIMARY KEY NOT NULL, bar TEXT);

\d foo
Table "public.foo "
Column | Type | Modifiers
--------+---------+-----------------------------------------------------
id | integer | not null default nextval('public .foo_id_seq'::t ext)
bar | text |
Indexes:
"foo_pkey" primary key, btree (id)

Now, I have figured out how to get a list of all the sequences with:

foo=> SELECT relname FROM pg_class WHERE relkind='S' AND relname !~ '^pg_';
relname
------------
foo_id_seq
(1 row)

I can find public.foo in pg_tables, but I am not sure how to relate pg_tables and pg_class in order to find the sequence for a specific field in public.foo.

Can anyone point me in the right direction? I am trying to get out of thehabit of hard-coding the sequence names in my code.

Now that I think of it, I am lacking 'public.' as well from my query.

Ok, so how would I go about getting the sequence name for a SERIAL field on any given schema.table? I would like to build a function that would return this value if I pass it the schema and table (and fieldname is necessary)

Thanks,

I figured out how to get this:

foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
pg_class WHERE relname = 'foo');
adsrc
------------------------------------
nextval('public .foo_id_seq'::t ext)
(1 row)

However, this will break as soon as I do this:

foo=> CREATE SCHEMA x;
CREATE SCHEMA
foo=> CREATE TABLE x.foo (id SERIAL PRIMARY KEY NOT NULL, x TEXT);
NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq " for
"serial" column "foo.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
pg_class WHERE relname = 'foo');
ERROR: more than one row returned by a subquery used as an expression

So, it was a nice attempt, but I am back to the need to of determining
the sequence name using a schema and a table.

Help. :-)

Thanks again,

-Robby

--
/*************** *************** *********
* Robby Russell | Owner.Developer .Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | ro***@planetarg on.com
* 503.351.4730 | blog.planetargo n.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
*************** *************** **********/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBBgHaR0Qa QZBaqXgwRAn1hAJ 9ZnPngtDrOw1kP/fbklTXhY/j5QACeMA2w
pZlgGypzY9L53C3 LU4lrwm0=
=cpsO
-----END PGP SIGNATURE-----

Nov 23 '05 #2
On Wed, 2004-10-27 at 21:33 -0700, Robby Russell wrote:

I figured out how to get this:

foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
pg_class WHERE relname = 'foo');
adsrc
------------------------------------
nextval('public .foo_id_seq'::t ext)
(1 row)

However, this will break as soon as I do this:

foo=> CREATE SCHEMA x;
CREATE SCHEMA
foo=> CREATE TABLE x.foo (id SERIAL PRIMARY KEY NOT NULL, x TEXT);
NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq " for
"serial" column "foo.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
pg_class WHERE relname = 'foo');
ERROR: more than one row returned by a subquery used as an expression

So, it was a nice attempt, but I am back to the need to of determining
the sequence name using a schema and a table.


Also, I am trying to avoid assuming that the sequence name will be:
schema.table_id _seq

The goal is to determine the sequence name for any schema.table that has
a SERIAL sequence (because you can create a sequence with a different
name) ... and if the column name isn't 'id'

for example:
foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
pg_class WHERE relname = 'bar');
adsrc
-------------------------------------
nextval('public .bar_nid_seq':: text)
(1 row)

The schema.table_id _seq wouldn't work under this scenario.

any thoughts or pointers?

Thanks,

Robby

--
/*************** *************** *********
* Robby Russell | Owner.Developer .Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | ro***@planetarg on.com
* 503.351.4730 | blog.planetargo n.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
*************** *************** **********/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBBgHvC0Qa QZBaqXgwRAks/AJ9HBpA7l/N0z3x/nBOcyPFX0/BdJgCghPqL
lC5+82mJUmyUclp pDcyedD8=
=EikY
-----END PGP SIGNATURE-----

Nov 23 '05 #3
On Wed, Oct 27, 2004 at 09:33:21PM -0700, Robby Russell wrote:
So, it was a nice attempt, but I am back to the need to of determining
the sequence name using a schema and a table.


The schema of a table is stored in pg_class.relnam espace, which is an
Oid of the pg_namespace catalog. With that and your previous query you
should be set.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La felicidad no es mañana. La felicidad es ahora"
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #4
Robby Russell <ro***@planetar gon.com> writes:
Ok, so how would I go about getting the sequence name for a SERIAL
field on any given schema.table?


8.0 will have a function pg_get_serial_s equence to do this for you.
If you can't wait, the secret is to look in pg_depend for the dependency
link from the serial sequence to its column.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 23 '05 #5
# I figured out how to get this:
#
# foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
# pg_class WHERE relname = 'foo');
# adsrc
# ------------------------------------
# nextval('public .foo_id_seq'::t ext)
# (1 row)
#
# However, this will break as soon as I do this:
#
# foo=> CREATE SCHEMA x;
# CREATE SCHEMA
# foo=> CREATE TABLE x.foo (id SERIAL PRIMARY KEY NOT NULL, x TEXT);
# NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq " for
# "serial" column "foo.id"
# NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
# "foo_pkey" for table "foo"
# CREATE TABLE
# foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
# pg_class WHERE relname = 'foo');
# ERROR: more than one row returned by a subquery used as an
# expression

This should suffice to get you a string you can regex. Other than the
default value setting for the serial, I don't see another link that
binds the serial to its sequence.

CREATE OR REPLACE FUNCTION get_default_val ue (text, text, text) RETURNS text AS '
SELECT adsrc
FROM pg_attrdef, pg_class, pg_namespace, pg_attribute
WHERE
adrelid = pg_class.oid AND
pg_class.relnam espace = pg_namespace.oi d AND
pg_attribute.at tnum = pg_attrdef.adnu m AND
pg_attribute.at trelid = pg_class.oid AND
pg_namespace.ns pname = $1 AND
pg_class.relnam e = $2 AND
pg_attribute.at tname = $3;
' language sql;

--
Jonathan Daugherty
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, (503) 667-4564

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #6
On Wed, Oct 27, 2004 at 09:06:15PM -0700, Robby Russell wrote:

Ok, so how would I go about getting the sequence name for a SERIAL
field on any given schema.table? I would like to build a function
that would return this value if I pass it the schema and table (and
fieldname is necessary)


PostgreSQL 8.0 (still in beta) has pg_get_serial_s equence():

test=> SELECT pg_get_serial_s equence('foo', 'id');
pg_get_serial_s equence
------------------------
public.foo_id_s eq

Here's a query that you might find useful:

SELECT s1.nspname || '.' || t1.relname AS tablename,
a.attname,
s2.nspname || '.' || t2.relname AS sequencename
FROM pg_depend AS d
JOIN pg_class AS t1 ON t1.oid = d.refobjid
JOIN pg_class AS t2 ON t2.oid = d.objid
JOIN pg_namespace AS s1 ON s1.oid = t1.relnamespace
JOIN pg_namespace AS s2 ON s2.oid = t2.relnamespace
JOIN pg_attribute AS a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE t1.relkind = 'r'
AND t2.relkind = 'S';

I posted a somewhat different query in a recent thread about
automatically updating all sequences after importing data:

http://archives.postgresql.org/pgsql...0/msg00673.php

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #7
# CREATE OR REPLACE FUNCTION get_default_val ue (text, text, text) RETURNS text AS '
# SELECT adsrc
# FROM pg_attrdef, pg_class, pg_namespace, pg_attribute
# WHERE
# adrelid = pg_class.oid AND
# pg_class.relnam espace = pg_namespace.oi d AND
# pg_attribute.at tnum = pg_attrdef.adnu m AND
# pg_attribute.at trelid = pg_class.oid AND
# pg_namespace.ns pname = $1 AND
# pg_class.relnam e = $2 AND
# pg_attribute.at tname = $3;
# ' language sql;

As per Tom's mention of pg_depend, here's something that seems to do
the trick for the time being, assuming the column is a serial:

-- get_sequence(sc hema_name, table_name, column_name)

CREATE OR REPLACE FUNCTION get_sequence (text, text, text) RETURNS
text AS '
SELECT seq.relname::te xt
FROM pg_class src, pg_class seq, pg_namespace, pg_attribute,
pg_depend
WHERE
pg_depend.refob jsubid = pg_attribute.at tnum AND
pg_depend.refob jid = src.oid AND
seq.oid = pg_depend.objid AND
src.relnamespac e = pg_namespace.oi d AND
pg_attribute.at trelid = src.oid AND
pg_namespace.ns pname = $1 AND
src.relname = $2 AND
pg_attribute.at tname = $3;
' language sql;

--
Jonathan Daugherty
http://www.cprogrammer.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 23 '05 #8
On Wed, 2004-10-27 at 22:45 -0700, Jonathan Daugherty wrote:
# CREATE OR REPLACE FUNCTION get_default_val ue (text, text, text) RETURNStext AS '
# SELECT adsrc
# FROM pg_attrdef, pg_class, pg_namespace, pg_attribute
# WHERE
# adrelid = pg_class.oid AND
# pg_class.relnam espace = pg_namespace.oi d AND
# pg_attribute.at tnum = pg_attrdef.adnu m AND
# pg_attribute.at trelid = pg_class.oid AND
# pg_namespace.ns pname = $1 AND
# pg_class.relnam e = $2 AND
# pg_attribute.at tname = $3;
# ' language sql;

As per Tom's mention of pg_depend, here's something that seems to do
the trick for the time being, assuming the column is a serial:

-- get_sequence(sc hema_name, table_name, column_name)

CREATE OR REPLACE FUNCTION get_sequence (text, text, text) RETURNS
text AS '
SELECT seq.relname::te xt
FROM pg_class src, pg_class seq, pg_namespace, pg_attribute,
pg_depend
WHERE
pg_depend.refob jsubid = pg_attribute.at tnum AND
pg_depend.refob jid = src.oid AND
seq.oid = pg_depend.objid AND
src.relnamespac e = pg_namespace.oi d AND
pg_attribute.at trelid = src.oid AND
pg_namespace.ns pname = $1 AND
src.relname = $2 AND
pg_attribute.at tname = $3;
' language sql;


Thanks, this seems to work well. My goal is to actually create a php
function that takes a result and returns the insert_id like
mysql_insert_id () does, but without needing to know the sequence names
and such. I would make a psql function, but I don't always have that
option with some clients existing systems.

-Robby

--
/*************** *************** *********
* Robby Russell | Owner.Developer .Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | ro***@planetarg on.com
* 503.351.4730 | blog.planetargo n.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
*************** *************** **********/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBBgS970Qa QZBaqXgwRAmwIAJ 9xGhi8cQ3p2oTA9 Ih+XodcnCHajgCg ktnT
eT0GO3tjolD2k7K 2jRbDOX8=
=3Nd8
-----END PGP SIGNATURE-----

Nov 23 '05 #9
On Thursday October 28 2004 11:42, Robby Russell wrote:

Thanks, this seems to work well. My goal is to actually create a php
function that takes a result and returns the insert_id like
mysql_insert_id () does, but without needing to know the sequence names
and such. I would make a psql function, but I don't always have that
option with some clients existing systems.


An alternative is to simply select nextval() from a separately-created
sequence object to get the serial value, then insert with that value. No
need to have a serial column then, but you do need to explicitly create the
sequence object, as opposed to SERIAL.

But I didn't understand why you care to get rid of the explicit reference to
the sequence object in your code in the first place. In PostgreSQL, at
least for the past 5 years if not longer, if you create a SERIAL column for
(schemaname, tablename, columnname), then your sequence will *always* be
"schemaname.tab lename_columnna me_seq". If that naming convention changes,
there will be a whole lotta breakage world-wide.

Ed
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
2640
by: Anthony Best | last post by:
I'm working on an idea that uses sequences. I'm going to create a table like this: id serial, sequence int, keyword varchar(32), text text for every keyword there will be a uniq sequence for it eg:
5
3041
by: Marek Lewczuk | last post by:
I'm curious if the default scheme for sequence name (which is created with SERIAL data type) can be changed -- currently all sequences are named like this: <table_name>_<field_name>_seq -- can it be changed for e.g. <table_name>__<field_name>__seq ??? Thanks. ML
0
1138
by: KathyB | last post by:
Hi, sorry if this is the wrong group...but I don't see an xpath group and hoping someone here can help me. How do you assign variable to different elements within a single node set? I have a simple xml structure that I'm using to determine a workflow process. I've read all the xpath documentation, but can't quite get my brain around the precise methods I need. When a user enters a serial number (sn), I need to find the last board with...
3
2130
by: David Garamond | last post by:
Am I correct to assume that SERIAL does not guarantee that a sequence won't skip (e.g. one successful INSERT gets 32 and the next might be 34)? Sometimes a business requirement is that a serial sequence never skips, e.g. when generating invoice/ticket/formal letter numbers. Would an INSERT INTO t (id, ...) VALUES (SELECT MAX(col)+1 FROM t, ...) suffice, or must I install a trigger too to do additional checking? -- dave
1
1637
by: Miles Keaton | last post by:
Here's an interesting problem! When a sequence clashes with data already in that table: CREATE TABLE clients ( id serial NOT NULL PRIMARY KEY UNIQUE, name varchar(64)); -- import OLD clients, with their original ID#... INSERT INTO clients VALUES (3, 'Dave');
4
4507
by: Sim Zacks | last post by:
I am in the process of converting an existing database to PostGreSQL and wrote a generic script to update all of the sequences as they default at 1. I thought it would be useful to other people who are converting their databases. If anyone can write this script in using plpythonu, I would love to see how it is done. create or replace function UpdateSequences() returns varchar(50) as $$
4
538
by: Eric E | last post by:
Hi, I have a question about sequences. I need a field to have values with no holes in the sequence. However, the values do not need to be in order. My users will draw a number or numbers from the sequence and write to the field. Sometimes, however, these sequence numbers will be discarded (after a transaction is complete), and thus available for use. During the transaction, however, any drawn numbers need to be unavailable. I would...
8
1708
by: Ole | last post by:
If I define a class and create a instant of it like e.g.: UserClass instantName = new UserClass(); how do I then determine the defined name "instantName" in the UserClass e.g. in a method (or a property) like this: public void userFunction() { string Var = new string();
5
9509
by: LongBow | last post by:
Hello, Is there a way, in .NET, to determine what are the avialable Serial (Communications) Ports on a Windows OS and is there a way to determine that port isn't being use other than attempting to opening the Serial Port and catching the associated exception? Thanks If there isn't a way to determine the Serial Port within .NET I would be willing, I guess, to use a WinAPI is that was the only way.
0
9879
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9725
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10640
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9477
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7877
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7050
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5714
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5906
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4111
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.