Re: How can I see IDENTITY columns?

Oh, ok,

that seems like a bug, at least a feature that should be there..

Dave Cramer

On 8 August 2018 at 15:52, Rob Richardson <interrobang(at)yahoo(dot)com> wrote:

> 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.
>
>
>