Postgres Pro
Downloads
Home   >   mailing lists

Re: How can I see IDENTITY columns? - Mailing list pgadmin-support

From Rob Richardson
Subject Re: How can I see IDENTITY columns?
Date
Msg-id 567957550.47899.1533757947058@mail.yahoo.com
Whole thread Raw
In response to Re: How can I see IDENTITY columns?  (Dave Cramer <davecramer@gmail.com>)
Responses Re: How can I see IDENTITY columns?
List pgadmin-support
Tree view
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@gmail.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@yahoo.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 identityalarm_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.


pgadmin-support by date:

Previous
From: Dave Cramer
Date:
Subject: Re: How can I see IDENTITY columns?
Next
From: Dave Cramer
Date:
Subject: Re: How can I see IDENTITY columns?