Hanne Moa schrieb am 19.10.2016 um 10:31:
>> You can use the following statement to find the sequences that a table uses:
>>
> Thanks. This assumes that there is only one nextval per table though.
> While this holds for the database we need this on right now, it's not a
> sufficiently generic solut
On 2016-10-19 09:28, Thomas Kellerer wrote:
> You can use the following statement to find the sequences that a table uses:
>
> select sn.nspname as sequence_schema, s.relname as sequence_name
> from pg_class s
> join pg_namespace sn on sn.oid = s.relnamespace
> join pg_depend
Hanne Moa schrieb am 19.10.2016 um 09:06:
>> regression=# create table t1 (f1 serial);
>> CREATE TABLE
>> regression=# select * from pg_depend where objid = 't1_f1_seq'::regclass or
>> refobjid = 't1_f1_seq'::regclass;
>> classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
On 2016-10-18 16:11, Tom Lane wrote:
> regression=# create table t1 (f1 serial);
> CREATE TABLE
> regression=# select * from pg_depend where objid = 't1_f1_seq'::regclass or
> refobjid = 't1_f1_seq'::regclass;
> classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
> ---
Tom Lane schrieb am 18.10.2016 um 16:11:
I thought pg_depend only stores the dependency if the the sequence was assigned
an owning column (through OWNED BY).
No, there will be regular expression dependencies as well.
That 'a' dependency is the one that pg_get_serial_sequence() looks for,
but t
Thomas Kellerer writes:
> Tom Lane schrieb am 18.10.2016 um 15:20:
>> Personally, I'd try looking in pg_depend to see if the column's default
>> expression has a dependency on a relation of type sequence. That avoids
>> all the fun of parsing the expression and turns it into a simple SQL
>> join
Tom Lane schrieb am 18.10.2016 um 15:20:
>> Furthermore, what's stored in the column seems to be a string of the
>> format "nextval('sequencename'::regclass)". Is there a function to
>> parse this, to return just the sequence name, or will the sequence
>> name always be without for instance a schem
Hanne Moa writes:
> Until now we've been using pg_get_serial_sequence() to discover
> which sequence is in use, but can no longer do so due to two tables
> needing to share the same sequence (prior to being properly merged. No
> duplicate values, luckily). For one of the tables,
> pg_get_serial_se
> Is this to be regarded as internal API or is it safe to use this to
> find the correct sequence?
I think it's safe to use.
> Furthermore, what's stored in the column seems to be a string of the
> format "nextval('sequencename'::regclass)". Is there a function to
> parse this, to return just t
