Using `nextval` and `currval` inside one CREATE TABLE Let's say that we would like to model a team of people that can have subteams like so:

CREATE SEQUENCE seq_teams;
CREATE TABLE teams (
    tid INTEGER PRIMARY KEY DEFAULT nextval('seq_teams'),
    sid INTEGER REFERENCES teams(tid) DEFAULT currval('seq_teams'),

    name VARCHAR(40) NOT NULL
);


So, if the team is not a "subteam" of any other team, then tid and sid have the same value. Otherwise, sid is the id of the superteam for team tid.

So, if I want to insert a superteam, I can do just:

INSERT INTO teams (name) VALUES ('superteam 1')


Is there a guarantee that tid and sid will be the same in this case? Is there some better (well known) design that can deal with recursive data structures?
The code you have posted works OK, and is guaranteed to insert the same IDs. 

The other way of designing this is to have a separate table that stores the hierarchical relationships, but you don't really need to do so unless you have many:1 or many:many relationships.