Constellation Identification

pg_orrery identifies which of the 88 IAU constellations contains a given sky position. You pass an equatorial coordinate (or raw RA and Dec values) and get back a three-letter IAU abbreviation. A companion function expands abbreviations to full names. The boundary lookup uses the Roman (1987) definitive boundary table, with internal precession from J2000 to the B1875.0 epoch in which the boundaries were originally defined.

How you do it today

Determining which constellation an object is in usually involves:

• Stellarium: Click on an object, read the constellation from the info panel. One object at a time, not scriptable.
• Astropy + regions: Load the IAU constellation boundary polygons, precess coordinates to B1875.0, and run a point-in-polygon test. Correct, but requires assembling coordinate transforms and boundary data yourself.
• SIMBAD/CDS: Query by object name and the constellation is in the metadata. Only works for cataloged objects, not arbitrary coordinates.
• Manual lookup: Find the RA/Dec on a star chart and visually identify the constellation. Error-prone near boundaries.

The constraint is always the same: the boundary data and the precession step live outside your database. If your star catalog, observation log, or scheduling system is in PostgreSQL, you export coordinates, look up constellations externally, and import the labels.

What changes with pg_orrery

Three functions handle constellation identification:

Function	Returns	What it does
constellation(equatorial)	text (3-letter IAU code)	Identifies constellation from an equatorial coordinate
constellation(ra_hours, dec_deg)	text (3-letter IAU code)	Convenience overload for raw J2000 RA (hours) and Dec (degrees)
constellation_full_name(abbrev)	text (full name or NULL)	Expands a 3-letter abbreviation to the full IAU name

The first overload accepts the equatorial type returned by any of pg_orrery’s equatorial functions — planet_equatorial(), sun_equatorial(), star_equatorial_pm(), and so on. This makes the constellation lookup composable with the rest of the observation pipeline.

The second overload takes raw RA in hours [0, 24) and Dec in degrees [-90, 90]. Use it for catalog data stored as individual columns.

Both constellation() overloads precess the input J2000 coordinates to B1875.0 internally before testing against the ~357 boundary segments from the Roman (1987) table (CDS catalog VI/42). This precession is necessary because the IAU constellation boundaries were defined at epoch B1875.0.

All three functions are IMMUTABLE — the Roman boundary data is compiled into the extension, so there are no external dependencies. This means they are safe for use in indexes, generated columns, and materialized views.

What pg_orrery does not replace

Boundary precision

The Roman boundary table defines constellation boundaries as line segments in RA and Dec at epoch B1875.0. For positions very close to a boundary (within a few arcseconds), the linear scan may disagree with higher-precision boundary implementations that account for the curved nature of precession over short segments.

• Not a substitute for detailed boundary analysis. Objects within a few arcseconds of a constellation boundary may land on different sides depending on the precession model used. For critical applications (e.g., variable star designations), consult the CDS boundary data directly.
• No constellation figures or asterisms. The function returns the IAU region that contains the coordinate, not any information about the traditional figure or its stars.
• 88 constellations only. The function returns the standard IAU three-letter abbreviation. Historical constellations (Argo Navis, Quadrans Muralis) are not represented.

Try it

Which constellation is Jupiter in?

The simplest case — combine planet_equatorial() with constellation():

SELECT constellation(planet_equatorial(5, '2025-06-15 04:00:00+00')) AS jupiter_constellation;

This returns a three-letter IAU abbreviation like Tau or Gem, depending on where Jupiter is at the specified time.

Full composability chain

Chain the equatorial, constellation, and full-name functions together to produce a readable result:

SELECT constellation_full_name(
           constellation(
               planet_equatorial(5, '2025-06-15 04:00:00+00')
           )
       ) AS jupiter_in;

This returns the full name — something like Taurus or Gemini. The three functions nest cleanly because each takes the output of the previous one.

All planets and their constellations

Sweep all seven visible planets at a given time:

SELECT CASE body_id
         WHEN 1 THEN 'Mercury' WHEN 2 THEN 'Venus'
         WHEN 4 THEN 'Mars'    WHEN 5 THEN 'Jupiter'
         WHEN 6 THEN 'Saturn'  WHEN 7 THEN 'Uranus'
         WHEN 8 THEN 'Neptune'
       END AS planet,
       constellation(planet_equatorial(body_id, '2025-06-15 04:00:00+00')) AS abbrev,
       constellation_full_name(
           constellation(planet_equatorial(body_id, '2025-06-15 04:00:00+00'))
       ) AS constellation
FROM generate_series(1, 8) AS body_id
WHERE body_id != 3  -- cannot observe Earth from Earth
ORDER BY body_id;

Seven rows, one query. Each planet gets its constellation abbreviation and full name.

Star catalog enrichment

Add a constellation column to a catalog table using the raw-coordinate overload:

-- Existing star catalog
CREATE TABLE star_catalog (
    hip_id     integer PRIMARY KEY,
    name       text,
    ra_hours   float8 NOT NULL,
    dec_deg    float8 NOT NULL,
    vmag       float8
);

INSERT INTO star_catalog VALUES
    (11767,  'Polaris',    2.5303,   89.264,  1.98),
    (32349,  'Sirius',     6.7525,  -16.716, -1.46),
    (27989,  'Betelgeuse', 5.9195,    7.407,  0.42),
    (91262,  'Vega',      18.6156,   38.784,  0.03);

-- Query with constellation identification
SELECT name,
       vmag,
       constellation(ra_hours, dec_deg) AS iau_abbrev,
       constellation_full_name(constellation(ra_hours, dec_deg)) AS constellation
FROM star_catalog
ORDER BY vmag;

Sirius returns CMa (Canis Major), Betelgeuse returns Ori (Orion), Vega returns Lyr (Lyra), and Polaris returns UMi (Ursa Minor). Because constellation() is IMMUTABLE, you could also store the result in a generated column:

ALTER TABLE star_catalog
    ADD COLUMN iau_constellation text
    GENERATED ALWAYS AS (constellation(ra_hours, dec_deg)) STORED;

Sun through the zodiac

The Sun’s constellation changes roughly once a month as it moves along the ecliptic. Sample at monthly intervals to see the progression:

SELECT t::date AS date,
       constellation(sun_equatorial(t)) AS abbrev,
       constellation_full_name(constellation(sun_equatorial(t))) AS constellation
FROM generate_series(
    '2025-01-15'::timestamptz,
    '2025-12-15'::timestamptz,
    interval '1 month'
) AS t;

The Sun passes through 13 constellations over the course of a year — the 12 traditional zodiac constellations plus Ophiuchus, which the ecliptic crosses between Scorpius and Sagittarius. The IAU boundaries do not match the astrological 30-degree divisions, so the Sun spends significantly different amounts of time in each constellation.

What constellation is Polaris in?

Using the (ra_hours, dec_deg) overload directly with known coordinates:

SELECT constellation(2.5303, 89.264) AS polaris_abbrev,
       constellation_full_name(constellation(2.5303, 89.264)) AS polaris_constellation;

This returns UMi and Ursa Minor. The raw-coordinate overload is useful when you have RA and Dec values from an external source and do not need to construct an equatorial type first.

Full name display for UI

A common pattern for user-facing output: show the abbreviation alongside the full name.

WITH stars(name, ra_h, dec_d) AS (VALUES
    ('Polaris',    2.5303,   89.264),
    ('Sirius',     6.7525,  -16.716),
    ('Betelgeuse', 5.9195,    7.407),
    ('Vega',      18.6156,   38.784)
)
SELECT name,
       constellation(ra_h, dec_d)
           || ' (' || constellation_full_name(constellation(ra_h, dec_d)) || ')'
       AS constellation_display
FROM stars;

This produces strings like CMa (Canis Major) and Ori (Orion). The constellation_full_name() function returns NULL for unrecognized abbreviations, so if you are working with external data, wrap the concatenation in a COALESCE or check for NULL:

SELECT COALESCE(
    constellation_full_name('XYZ'),
    'Unknown'
) AS result;
-- Returns: Unknown

Moon and Sun constellations over a week

Track where the Moon and Sun are relative to the constellations:

SELECT t::date AS date,
       constellation(sun_equatorial(t)) AS sun_in,
       constellation(moon_equatorial(t)) AS moon_in
FROM generate_series(
    '2025-03-01'::timestamptz,
    '2025-03-07'::timestamptz,
    interval '1 day'
) AS t;

The Moon moves roughly 13 degrees per day, crossing a constellation boundary every two to three days. The Sun barely moves — it stays in the same constellation for the entire week.