Thread: How can I see IDENTITY columns?
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.
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.
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.
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 runselect 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'sDave CramerOn 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.
Greetings, * Dave Cramer (davecramer@gmail.com) wrote: > On 8 August 2018 at 15:52, Rob Richardson <interrobang@yahoo.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. > that seems like a bug, at least a feature that should be there.. Yeah, I tend to agree.. Seems like pgAdmin4 really should be realizing that it's an identity column and creating a CREATE TABLE statement as such, though it'll need code specific to v10 and above to do that. I'm not at all surprised that pgAdmin3 has this issue and I wouldn't get your hopes up about seeing that fixed. This should get fixed for pgAdmin4 though. Thanks! Stephen
Attachment
Greetings,
* Dave Cramer (davecramer@gmail.com) wrote:
> On 8 August 2018 at 15:52, Rob Richardson <interrobang@yahoo.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.
> that seems like a bug, at least a feature that should be there..
Yeah, I tend to agree.. Seems like pgAdmin4 really should be realizing
that it's an identity column and creating a CREATE TABLE statement as
such, though it'll need code specific to v10 and above to do that.
I'm not at all surprised that pgAdmin3 has this issue and I wouldn't get
your hopes up about seeing that fixed. This should get fixed for
pgAdmin4 though.
Thanks!
Stephen