How to make a table share a sequence with another table in postgresql?

user_6213353 0
Viewed 4659

Basically I have a table1 with unique ids of features for a city, now I got a table2 for features for that country.

I need to create new ids for the country table (these need to share the same sequence as the city, so that the ids match when cross referencing tables)

How to make table2 have the same ids as table1 within that city and then new ids for features elsewhere? Essentially sharing the sequence

Edit: the tables are already created, how can i update table2

postgresql sql
4 Answers

If you manually create a sequence and assign that as the default value to the ID columns, then it works. But to reuse an existing value that would mean we'd have to create a trigger that assign an existing value or obtains a new from the shared sequence.

create sequence baz;

create table foo(id bigint default nextval('baz'), value text);

create table bar(id bigint default nextval('baz'), value date);

insert into foo (value) values ('Hello');
insert into bar (value) values (now());
insert into foo (value) values ('World');
insert into bar (value) values (now());

select 'foo', id, value::text from foo 
union all
select 'bar', id, value::text from bar

And the result is:

foo 1   Hello
bar 2   2018-10-29
foo 3   World
bar 4   2018-10-29

And as a bonus:

drop sequence baz

ERROR: cannot drop sequence baz because other objects depend on it
  Detail: 
    default for table foo column id depends on sequence baz
    default for table bar column id depends on sequence baz
  Hint: Use DROP ... CASCADE to drop the dependent objects too.

Edit: If we can do post processing then this approach could be usedto assign values for the missing ID columns:

update bar
SET id = coalesce((select id from foo where bar.city_name = foo.city_name),nextval('baz'))
WHERE id is null
user_15761960

if your tables are already created you must create a sequence 

create sequence seq_city_country;

and then add the sequence to your ids with the following code

ALTER TABLE city ALTER COLUMN id_city SET DEFAULT nextval('seq_city_country');
ALTER TABLE country ALTER COLUMN id_country SET DEFAULT nextval('seq_city_country');

if your sequence is already created for table city by (sequence_c) you can use 

  ALTER TABLE country ALTER COLUMN id_country SET DEFAULT nextval('sequence_c');
user_87527330 
CREATE SEQUENCE shared_seq;

CREATE TABLE a (
   col1 bigint  DEFAULT nextval('shared_seq'),
   ...
);

CREATE TABLE b (
   col1 bigint  DEFAULT nextval('shared_seq'),
   ...
);
user_46583120

This doesn't sound like very good (or even possible) database design. Instead, I suggest creating a junction table which relates cities to their respective countries. So, your three tables might look like this:

city (PK id, name, ...)
country (PK id, name, ...)
country_city (city_id, country_id)  PK (city_id -> city(id), country_id -> country(id))

With this design, you don't need to worry about the auto increment sequences in the city and country table. Just let Postgres assign those values, and then just maintain the junction table using the correct values.

user_18632290
Related