From: | Rob Richardson <interrobang(at)yahoo(dot)com> |
---|---|
To: | Dave Cramer <davecramer(at)gmail(dot)com> |
Cc: | "pgadmin-support(at)lists(dot)postgresql(dot)org" <pgadmin-support(at)lists(dot)postgresql(dot)org> |
Subject: | Re: How can I see IDENTITY columns? |
Date: | 2018-08-08 19:52:27 |
Message-ID: | 567957550.47899.1533757947058@mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
I have now downloaded pgAdmin 4 v3.1. It has the same problem.I run this command:
CREATE TABLE public.identity_sample ( identity_sample_key bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ) WITH ( OIDS = FALSE )
Then, I run
select attrelid, attname, attidentity from pg_attribute where attname = 'identity_sample_key'
and get two records, one of which has an attidentity column that contains 'd'. The other record's attidentity column is null.
Then, in pgAdmin, I refresh the schema and select the identity_sample table. In the SQL tab, I see this:
-- DROP TABLE public.identity_sample; CREATE TABLE public.identity_sample ( identity_sample_key bigint NOT NULL, CONSTRAINT identity_sample_pkey PRIMARY KEY (identity_sample_key) ) WITH ( OIDS = FALSE ) TABLESPACE pg_default;
I copy that into a new SQL window, uncomment the DROP TABLE line, and execute it. Then, I repeat the select query on pg_attribute. Again, I get two records, but this time the attidentity column is null for both records.
On Wednesday, August 8, 2018, 2:39:00 PM EDT, Dave Cramer <davecramer(at)gmail(dot)com> wrote:
The attribute table now has an attidentity column. https://www.postgresql.org/docs/10/static/catalog-pg-attribute.html
Curious why are you using with oid's
Dave Cramer
On 8 August 2018 at 14:16, Rob Richardson <interrobang(at)yahoo(dot)com> wrote:
I just learned about IDENTITY columns in PostgreSQL 10. I am working on upgrading a database for an upcoming major revision of my company's software package, and I want to make all of the columns defined as "serial" or "bigserial" IDENTITY columns. I found a nice web page with a function that will do that. The problem I am running into is that I can't see IDENTITY columns in pgAdmin. I've tried both in pgAdmin 4 and in BigSQL's version as shipped with its implementation of PostgreSQL 10.3. After running the function, pgAdmin shows me the following CREATE script for my table:
CREATE TABLE public.alarm_comments
(
key bigint NOT NULL,
alarm_key smallint,
alarm_comment character varying(256) COLLATE pg_catalog."default",
updated_by character varying(16) COLLATE pg_catalog."default",
updated_date timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone,
CONSTRAINT alarm_comments_pkey PRIMARY KEY (key)
)
WITH (
OIDS = TRUE
)
TABLESPACE pg_default;
ALTER TABLE public.alarm_comments
OWNER to postgres;
psql, on the other hand, shows me this:
Stripco for Conversion=# \d alarm_comments
Table "public.alarm_comments"
Column | Type | Collation | Nullable |
Default
---------------+-------------- ------------+-----------+----- -----+--------------
------------------------------
key | bigint | | not null | generated by
default as identity
alarm_key | smallint | | |
alarm_comment | character varying(256) | | |
updated_by | character varying(16) | | |
updated_date | timestamp with time zone | | | ('now'::text)
::timestamp(6) with time zone
Indexes:
"alarm_comments_pkey" PRIMARY KEY, btree (key)
I need to be able to see the IDENTITY in pgAdmin, since that's what we use for all of our database administration. If I can't see that a column is an IDENTITY column, then I may not be able to use IDENTITY columns at all.
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2018-08-08 20:11:40 | Re: How can I see IDENTITY columns? |
Previous Message | Dave Cramer | 2018-08-08 18:38:26 | Re: How can I see IDENTITY columns? |