Query To know if a table field exists or does not exist in PostgreSQL

Question

Query To know if a table field exists or does not exist in PostgreSQL

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

2

I can not find the way to do the query to create a field in a table and a foreign key in a PostgreSQL database, but previously verifying that it does not exist. In SQL server I use the following but they do not work in PostgreSQL 9.1.

--- para un campo nuevo ------
BEGIN TRANSACTION;
    IF NOT EXISTS(
        SELECT *
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'workflowarea' AND COLUMN_NAME = 'wrko_area'
    )
    BEGIN
        ALTER TABLE workflowarea ADD wrko_area bigint NOT NULL;
    END
COMMIT TRANSACTION;




---- para llave foranea.
BEGIN TRANSACTION;
IF EXISTS
(
SELECT * FROM sys.foreign_keys WHERE name = 'workflowarea_ra_id_fk'
)
BEGIN
  ALTER TABLE workflowarea DROP CONSTRAINT workflowarea_ra_id_fk; 
END
COMMIT TRANSACTION;

What would be the equivalent in PostgreSQL?

sql postgresql

asked by Manuel Mosquera 08.09.2016 в 22:26

source

3 answers

Python: Scipy.optimize Levenberg-marquardt method Set the active window in a QmdiArea

score 1 · Answer 1

The following query will return true/false , using the EXISTS() function.

Free translation made by me:

EXISTS () :
The EXISTS argument is a SELECT statement, or a sub-query. The sub-query is evaluated to determine if a row returns. If you return at least one row, the result of EXISTS is true ; if the sub-query does not return any row, the result is false

SELECT EXISTS(
SELECT column_name 
FROM information_schema.columns 
WHERE table_schema='public' 
  and table_name='x' 
  and column_name='y')

Having said that, you can use a dynamic SQL statement to modify your table, for example:

DO
$$
BEGIN
  IF not EXISTS (SELECT column_name 
                 FROM information_schema.columns 
                 WHERE table_schema='public' 
                   and table_name='workflowarea' 
                   and column_name='wrko_area') THEN
    alter table workflowarea add column wrko_area bigint not null;
  else
    raise NOTICE 'El campo ya existe';
  END IF;
END
$$

You can use this same technique to determine if the foreign key does not exist and add it.

score 0 · Answer 2

Greetings, try the following SQL statements:

-- Para saber si existe una columna en una tabla
SELECT column_name 
FROM information_schema.columns 
WHERE table_name='nombre_table' and column_name='nombre_columna';

-- Para saber si existe una llave en la tabla
SELECT * FROM pg_constraint WHERE conname = 'fk_modulo_controlador'

I hope it's helpful :)

score 0 · Answer 3

From postgres 9.6 it is possible to use "IF NOT EXISTS" for the creation of columns:

ALTER TABLE workflowarea ADD COLUMN IF NOT EXISTS wrko_area bigint not null;

For FK there is no such comfort, but you can remove it IF EXISTS and create it again

ALTER TABLE workflowarea DROP CONSTRAINT IF EXISTS fk_modulo_controlador;
ALTER TABLE workflowarea ADD CONSTRAINT fk_modulo_controlador ...;