pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedstrange system columns
3+ messages / 3 participants
[nested] [flat]
* strange system columns
@ 2006-01-05 20:17 Colton A Smith <smith@cs.utk.edu>
0 siblings, 2 replies; 3+ messages in thread
From: Colton A Smith @ 2006-01-05 20:17 UTC (permalink / raw)
To: pgsql-admin
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!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: strange system columns
@ 2006-01-12 05:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Colton A Smith <smith@cs.utk.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-01-12 05:11 UTC (permalink / raw)
To: Colton A Smith <smith@cs.utk.edu>; +Cc: pgsql-admin
Colton A Smith <smith@cs.utk.edu> writes:
> 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;
I think you meant to join to pg_type.oid, not pg_type.typelem.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: strange system columns
@ 2006-01-12 05:16 Michael Fuhr <mike@fuhr.org>
parent: Colton A Smith <smith@cs.utk.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2006-01-12 05:16 UTC (permalink / raw)
To: Colton A Smith <smith@cs.utk.edu>; +Cc: pgsql-admin
On Thu, Jan 05, 2006 at 03:17:02PM -0500, Colton A Smith wrote:
> 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;
The join condition for pg_type should be against pg_type.oid, not
pg_type.typelem. A simplified version of your two queries is:
SELECT attname, atttypid::regtype, attnum
FROM pg_attribute
WHERE attrelid = 'organic_matter'::regclass AND NOT attisdropped;
--
Michael Fuhr
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-01-12 05:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-05 20:17 strange system columns Colton A Smith <smith@cs.utk.edu>
2006-01-12 05:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-12 05:16 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox