3 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

For a table created like this:

CREATE TABLE public.delete_key_bigserial (id bigserial PRIMARY KEY NOT NULL);

... both my queries in the previous answerboth my queries in the previous answer (as well as pgAdmin, psql or any other decent client) would find the PK constraint. If it's not there, you removed it somehow.
Note that my first query only returns the column if it is the PK and a serial type - which is the case for the example.

Another possible cause for the confusion: Maybe you have more than one table named delete_key_bigserial in your database? Table names are only unique inside a single schema. Test with:

SELECT * FROM pg_class WHERE relname = 'delete_key_bigserial';

To make your query unambiguous, schema-qualify the table name:

WHERE  a.attrelid = 'public.delete_key_bigserial'::regclass

There are ways to make the constraint "disappear" without leaving a DROP CONSTRAINT in your logs.

  • Drop and recreate the table.
  • Drop and recreate the schema or database.
  • (Temporarily) set log_statement or other relevant settings so the statement is not logged.
  • Manipulate the system catalogs directly (as superuser) Internally, the primary key is set with contype = 'p' in the table pg_constraint.
  • Edit the log files.

  • etc.

For a table created like this:

CREATE TABLE public.delete_key_bigserial (id bigserial PRIMARY KEY NOT NULL);

... both my queries in the previous answer (as well as pgAdmin, psql or any other decent client) would find the PK constraint. If it's not there, you removed it somehow.
Note that my first query only returns the column if it is the PK and a serial type - which is the case for the example.

Another possible cause for the confusion: Maybe you have more than one table named delete_key_bigserial in your database? Table names are only unique inside a single schema. Test with:

SELECT * FROM pg_class WHERE relname = 'delete_key_bigserial';

To make your query unambiguous, schema-qualify the table name:

WHERE  a.attrelid = 'public.delete_key_bigserial'::regclass

There are ways to make the constraint "disappear" without leaving a DROP CONSTRAINT in your logs.

  • Drop and recreate the table.
  • Drop and recreate the schema or database.
  • (Temporarily) set log_statement or other relevant settings so the statement is not logged.
  • Manipulate the system catalogs directly (as superuser) Internally, the primary key is set with contype = 'p' in the table pg_constraint.
  • Edit the log files.

  • etc.

For a table created like this:

CREATE TABLE public.delete_key_bigserial (id bigserial PRIMARY KEY NOT NULL);

... both my queries in the previous answer (as well as pgAdmin, psql or any other decent client) would find the PK constraint. If it's not there, you removed it somehow.
Note that my first query only returns the column if it is the PK and a serial type - which is the case for the example.

Another possible cause for the confusion: Maybe you have more than one table named delete_key_bigserial in your database? Table names are only unique inside a single schema. Test with:

SELECT * FROM pg_class WHERE relname = 'delete_key_bigserial';

To make your query unambiguous, schema-qualify the table name:

WHERE  a.attrelid = 'public.delete_key_bigserial'::regclass

There are ways to make the constraint "disappear" without leaving a DROP CONSTRAINT in your logs.

  • Drop and recreate the table.
  • Drop and recreate the schema or database.
  • (Temporarily) set log_statement or other relevant settings so the statement is not logged.
  • Manipulate the system catalogs directly (as superuser) Internally, the primary key is set with contype = 'p' in the table pg_constraint.
  • Edit the log files.

  • etc.

2 clarify, add more
source | link

For a table created like this:

CREATE TABLE public.delete_key_bigserial (id bigserial PRIMARY KEY NOT NULL);

... both my queries in the previous answer (as well as pgAdmin, psql or any other decent client) would find the PK constraint. If it's not there, you removed it somehow.
Note that my first query in the previous answer only returns the column if it is the PK and a serial type - which is the case for the example.

Another possible cause for the confusion: Maybe you have more than one table named delete_key_bigserial in your database? Table names are only unique inside a single schema. Test with:

SELECT * FROM pg_class WHERE relname = 'delete_key_bigserial';

To make your query unambiguous, schema-qualify the table name:

WHERE  a.attrelid = 'public.delete_key_bigserial'::regclass

There are ways to make the constraint "disappear" without leaving a DROP CONSTRAINT in your logs.

  • Drop and recreate the table.
  • Drop and recreate the schema or database.
  • (Temporarily) set log_statement or other relevant settings so the statement is not logged.
  • Manipulate the system catalogs directly (as superuser) Internally, the primary key is set with contype = 'p' in the table pg_constraint.
  • Edit the log files.

  • etc.

For a table created like this:

CREATE TABLE public.delete_key_bigserial (id bigserial PRIMARY KEY NOT NULL);

... both my queries (as well as pgAdmin, psql or any other decent client) would find the PK constraint. If it's not there, you removed it somehow.
Note that my first query in the previous answer only returns the column it is the PK and a serial type - which is the case for the example.

There are ways to make the constraint "disappear" without leaving a DROP CONSTRAINT in your logs.

  • Drop and recreate the table.
  • Drop and recreate the schema or database.
  • (Temporarily) set log_statement or other relevant settings so the statement is not logged.
  • Manipulate the system catalogs directly (as superuser) Internally, the primary key is set with contype = 'p' in the table pg_constraint.
  • Edit the log files.

  • etc.

For a table created like this:

CREATE TABLE public.delete_key_bigserial (id bigserial PRIMARY KEY NOT NULL);

... both my queries in the previous answer (as well as pgAdmin, psql or any other decent client) would find the PK constraint. If it's not there, you removed it somehow.
Note that my first query only returns the column if it is the PK and a serial type - which is the case for the example.

Another possible cause for the confusion: Maybe you have more than one table named delete_key_bigserial in your database? Table names are only unique inside a single schema. Test with:

SELECT * FROM pg_class WHERE relname = 'delete_key_bigserial';

To make your query unambiguous, schema-qualify the table name:

WHERE  a.attrelid = 'public.delete_key_bigserial'::regclass

There are ways to make the constraint "disappear" without leaving a DROP CONSTRAINT in your logs.

  • Drop and recreate the table.
  • Drop and recreate the schema or database.
  • (Temporarily) set log_statement or other relevant settings so the statement is not logged.
  • Manipulate the system catalogs directly (as superuser) Internally, the primary key is set with contype = 'p' in the table pg_constraint.
  • Edit the log files.

  • etc.

1
source | link

For a table created like this:

CREATE TABLE public.delete_key_bigserial (id bigserial PRIMARY KEY NOT NULL);

... both my queries (as well as pgAdmin, psql or any other decent client) would find the PK constraint. If it's not there, you removed it somehow.
Note that my first query in the previous answer only returns the column it is the PK and a serial type - which is the case for the example.

There are ways to make the constraint "disappear" without leaving a DROP CONSTRAINT in your logs.

  • Drop and recreate the table.
  • Drop and recreate the schema or database.
  • (Temporarily) set log_statement or other relevant settings so the statement is not logged.
  • Manipulate the system catalogs directly (as superuser) Internally, the primary key is set with contype = 'p' in the table pg_constraint.
  • Edit the log files.

  • etc.