Question:
I have simple table creating script in Postgres 9.1. I need it to create the table with 2-attributes PK only if it does not exist.
CREATE TABLE IF NOT EXISTS "mail_app_recipients"
(
"id_draft" Integer NOT NULL,
"id_person" Integer NOT NULL
) WITH (OIDS=FALSE); -- this is OK
ALTER TABLE "mail_app_recipients" ADD PRIMARY KEY IF NOT EXISTS ("id_draft","id_person");
-- this is problem since "IF NOT EXISTS" is not allowed.
Any solution how to solve this problem? Thanks in advance.
Solution 1:
You could do something like the following, however it is better to include it in the create table as a_horse_with_no_name suggests.
if NOT exists (select constraint_name from information_schema.table_constraints where table_name = 'table_name' and constraint_type = 'PRIMARY KEY') then
ALTER TABLE table_name
ADD PRIMARY KEY (id);
end if;
Solution 2:
Why not include the PK definition inside the CREATE TABLE:
CREATE TABLE IF NOT EXISTS mail_app_recipients
(
id_draft Integer NOT NULL,
id_person Integer NOT NULL,
constraint pk_mail_app_recipients primary key (id_draft, id_person)
)
Solution 3:
You can try to DROP it before creating it (DROP has the IF EXISTS clause):
ALTER TABLE mail_app_recipients DROP CONSTRAINT IF EXISTS mail_app_recipients_pkey;
ALTER TABLE mail_app_recipients ADD CONSTRAINT mail_app_recipients_pkey PRIMARY KEY ("id_draft","id_person");
Note that this require that you give a name to the primary key constraint — in this example mail_app_recipients_pkey.
sql — Postgresql: how to create table only if it does not already exist?
Question:
In Postgresql, how can I do a condition to create a table only if it does not already exist?
Code example appreciated.
Solution 1:
I’m not sure when it was added, but for the sake of completeness I’d like to point out that in version 9.1 (maybe before) IF NOT EXISTS can be used. IF NOT EXISTS will only create the table if it doesn't exist already.
Example:
CREATE TABLE IF NOT EXISTS users.vip
(
id integer
)
This will create a table named vip in the schema users if the table doesn't exist.
Source
Solution 2:
create or replace function update_the_db() returns void as
$$
begin
if not exists(select * from information_schema.tables
where
table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA
and table_name = 'your_table_name_here') then
create table your_table_name_here
(
the_id int not null,
name text
);
end if;
end;
$$
language 'plpgsql';
select update_the_db();
drop function update_the_db();
Solution 3:
Just create the table and don’t worry about whether it exists. If it doesn’t exist it will be created; if it does exist the table won’t be modified. You can always check the return value of your SQL query to see whether the table existed or not when you executed the create statement.
sql — Postgresql tables exists, but getting “relation does not exist” when querying
Question:
I have a postgresql db with a number of tables. If I query:
SELECT column_name
FROM information_schema.columns
WHERE table_name="my_table";
I will get a list of the columns returned properly.
However, when I query:
SELECT *
FROM "my_table";
I get the error:
(ProgrammingError) relation "my_table" does not exist
'SELECT *\n FROM "my_table"\n' {}
Any thoughts on why I can get the columns, but can’t query the table? Goal is to be able to query the table.
Solution 1:
You have to include the schema if isnt a public one
SELECT *
FROM ."my_table"
Or you can change your default schema
SHOW search_path;
SET search_path TO my_schema;
Check your table schema here
SELECT *
FROM information_schema.columns
For example if a table is on the default schema public both this will works ok
SELECT * FROM parroquias_region
SELECT * FROM public.parroquias_region
But sectors need specify the schema
SELECT * FROM map_update.sectores_point
Solution 2:
create or replace function update_the_db() returns void as
$$
begin
if not exists(select * from information_schema.tables
where
table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA
and table_name = 'your_table_name_here') then
create table your_table_name_here
(
the_id int not null,
name text
);
end if;
end;
$$
language 'plpgsql';
select update_the_db();
drop function update_the_db();
Solution 3:
I had to include double quotes with the table name.
db=> \d
List of relations
Schema | Name | Type | Owner
--------+-----------------------------------------------+-------+-------
public | COMMONDATA_NWCG_AGENCIES | table | dan
...
db=> \d COMMONDATA_NWCG_AGENCIES
Did not find any relation named "COMMONDATA_NWCG_AGENCIES".
???
Double quotes:
db=> \d "COMMONDATA_NWCG_AGENCIES"
Table "public.COMMONDATA_NWCG_AGENCIES"
Column | Type | Collation | Nullable | Default
--------------------------+-----------------------------+-----------+----------+---------
ID | integer | | not null |
...
Lots and lots of double quotes:
db=> select ID from COMMONDATA_NWCG_AGENCIES limit 1;
ERROR: relation "commondata_nwcg_agencies" does not exist
LINE 1: select ID from COMMONDATA_NWCG_AGENCIES limit 1;
^
db=> select ID from "COMMONDATA_NWCG_AGENCIES" limit 1;
ERROR: column "id" does not exist
LINE 1: select ID from "COMMONDATA_NWCG_AGENCIES" limit 1;
^
db=> select "ID" from "COMMONDATA_NWCG_AGENCIES" limit 1;
ID
1
(1 row)
This is postgres 11. The CREATE TABLE statements from this dump had double quotes as well:
DROP TABLE IF EXISTS "COMMONDATA_NWCG_AGENCIES";
CREATE TABLE "COMMONDATA_NWCG_AGENCIES" (
...
sql — drop primary key constraint in postgresql by knowing schema and table name only
Question:
As far I know the only way of dropping primary key in postgresql is:
ALTER TABLE schema.tableName DROP CONSTRAINT constraint_name;
the constraint name by default is tableName_pkey. However sometimes if table is already renamed I can’t get the original table name to construct right constraint name.
For example, for a table created as A then renamed to B the constraint remains A_pkey but I only have the table name B.
Do you know right way to drop the pkey constraint by knowing only the schema name and table name ?
I am writing program for doing this so I need to use only SQL queries. Solutions like “open pgAdmin and see the constraint name” will not work.
Solution 1:
You can use information from the catalog tables like so:
Create a table with id as the primary key
create table test1 (id int primary key, name text);
Create the SQL to drop the key
select concat('alter table public.test1 drop constraint ', constraint_name) as my_query
from information_schema.table_constraints
where table_schema = 'public'
and table_name = 'test1'
and constraint_type = 'PRIMARY KEY';
The result will be:
alter table public.test1 drop constraint test1_pkey
You can create a stored function to extract this query and then execute it.
Solution 2:
login to the database using psql, the command line tool.
Then type:
\d
for example:
\d claim
Table "public.claim"
Column | Type | Collation | Nullable | Default
--------------------------------+-----------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('claim_id_seq'::regclass)
policy_id | integer | | |
person_id | integer | | |
incident_id | integer | | |
first_notification_of_loss | timestamp without time zone | | |
police_reference | character varying(40) | | |
photos_to_follow | boolean | | |
sketch_to_follow | boolean | | |
description_of_weather | character varying(2000) | | |
description_of_property_damage | character varying(2000) | | |
created_at | timestamp without time zone | | not null | now()
updated_at | timestamp without time zone | | not null |
Indexes:
"primary_key_claim" PRIMARY KEY, btree (id)
Foreign-key constraints:
"foreign_key_claim_incident" FOREIGN KEY (incident_id) REFERENCES incident(id)
"foreign_key_claim_person" FOREIGN KEY (person_id) REFERENCES person(id)
"foreign_key_claim_policy" FOREIGN KEY (policy_id) REFERENCES policy(id)
Referenced by:
TABLE "claimant" CONSTRAINT "foreign_key_claimant_claim" FOREIGN KEY (claim_id) REFERENCES claim(id)
TABLE "damage" CONSTRAINT "foreign_key_damage_claim" FOREIGN KEY (claim_id) REFERENCES claim(id)
TABLE "witness" CONSTRAINT "foreign_key_witness_claim" FOREIGN KEY (claim_id) REFERENCES claim(id)
This shows you the primary key name (as well as other stuff).
If you want to do this programmatically and you are using Java or another language that uses the JDBC interface, you can use the class DatabaseMetaData, method getPrimaryKeys.
Otherwise, the other answer, selecting from the system catalogs, is the way to go.
sql — Adding column with primary key in existing table
Question:
I am trying to add primary key to newly added column in existing table name Product_Details.
New Column added: Product_Detail_ID (int and not null)
I am trying add primary key to Product_Detail_ID (please note: there are no other primary or foreign key assigned to this table)
I am trying with this query but getting error.
ALTER TABLE Product_Details
ADD CONSTRAINT pk_Product_Detils_Product_Detail_ID PRIMARY KEY(Product_Detail_ID)
GO
Error:
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Product_Details' and the index name 'pk_Product_Detils'. The duplicate key value is (0).
Am I missing something here? I am using SQL Server 2008 R2. I would appreciate any help.
Solution 1:
If you want SQL Server to automatically provide values for the new column, make it an identity.
ALTER TABLE Product_Details DROP COLUMN Product_Detail_ID
GO
ALTER TABLE Product_Details ADD Product_Detail_ID int identity(1,1) not null
GO
ALTER TABLE Product_Details
add CONSTRAINT pk_Product_Detils_Product_Detail_ID primary key(Product_Detail_ID)
GO
Solution 2:
In mysql, I was able to achieve with following query
ALTER TABLE table_name ADD new_column int NOT NULL AUTO_INCREMENT primary key
Solution 3:
You are getting the error because you have existing data that does not fullfill the constraint.
There are 2 ways to fix it:
clean up the existing data before adding the constraint
add the constraint with the “WITH NOCHECK” option, this will stop sql server checking existing data, only new data will be checked
Top comments (0)