Hi:
I use v8.1.0, and have a table named pressure:
Table "public.pressure"
Column | Type |
Modifiers
------------------+-----------------------------+---------------------------------------------------------------
pressure_id | integer | not null default
nextval(('pressure_id_seq'::text)::regclass)
row_entry_date | timestamp with time zone |
sensor_id | integer | not null
measurement_date | timestamp without time zone |
pressure | double precision |
Indexes:
"pressure_pkey" PRIMARY KEY, btree (pressure_id)
Foreign-key constraints:
"sensor_id_exists" FOREIGN KEY (sensor_id) REFERENCES
sensor(sensor_id) ON DELETE RESTRICT
Tablespace: "diskvol2"
When I query the system tables to get a more complete listing, I get:
attname | typname | attnum
------------------+--------------+--------
oid | oidvector | -2
tableoid | oidvector | -7
pressure | point | 5
pressure | line | 5
sensor_id | _int4 | 3
pressure_id | _int4 | 1
oid | _oid | -2
tableoid | _oid | -7
ctid | _tid | -1
xmin | _xid | -3
xmax | _xid | -5
cmin | _cid | -4
cmax | _cid | -6
pressure | _float8 | 5
measurement_date | _timestamp | 4
row_entry_date | _timestamptz | 2
(16 rows)
My question: where did entries 3 and 4 come from?
I certainly didn't add them.
Looking at the rest of my tables, wherever I have a column datatyped as
float, I get the same result, i.e., two 'shadow' columns typed point and
line. Take my organic_matter table, for example:
Table "public.organic_matter"
Column | Type |
Modifiers
-------------------+-----------------------------+---------------------------------------------------------------------
organic_matter_id | integer | not null default
nextval(('organic_matter_id_seq'::text)::regclass)
row_entry_date | timestamp with time zone |
sensor_id | integer | not null
measurement_date | timestamp without time zone |
dom | double precision |
year | integer |
month | integer |
day | integer |
Indexes:
"organic_matter_pkey" PRIMARY KEY, btree (organic_matter_id)
"organic_matter_measurement_date_index" btree (measurement_date)
Foreign-key constraints:
"sensor_id_exists" FOREIGN KEY (sensor_id) REFERENCES
sensor(sensor_id) ON DELETE RESTRICT
Triggers:
converter AFTER INSERT ON organic_matter FOR EACH ROW EXECUTE
PROCEDURE organic_time_converter()
And then:
attname | typname | attnum
-------------------+--------------+--------
oid | oidvector | -2
tableoid | oidvector | -7
dom | point | 5
dom | line | 5
day | _int4 | 8
month | _int4 | 7
year | _int4 | 6
sensor_id | _int4 | 3
organic_matter_id | _int4 | 1
oid | _oid | -2
tableoid | _oid | -7
ctid | _tid | -1
xmin | _xid | -3
xmax | _xid | -5
cmin | _cid | -4
cmax | _cid | -6
dom | _float8 | 5
measurement_date | _timestamp | 4
row_entry_date | _timestamptz | 2
(19 rows)
The latter was derived using a series
of two queries:
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.pg_table_is_visible(c.oid)
AND c.relname = '$table_name'
ORDER BY 2, 3;
SELECT attname, typname, attnum
FROM pg_attribute INNER JOIN pg_type
ON pg_attribute.atttypid = pg_type.typelem
WHERE pg_attribute.attrelid = $oid (derived above)
AND NOT attisdropped;
Can anyone give me a clue? For what it's worth, I've recently upgraded
from 8.0.4 to 8.1.0.
Thanks!
