Postgresql:如何只在表不存在的情况下创建表?[英] Postgresql: how to create table only if it does not already exist?

本文是小编为大家收集整理的关于Postgresql:如何只在表不存在的情况下创建表?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

在PostgreSQL中,只有在尚不存在的情况下才能创建一个条件?

代码示例赞赏.

推荐答案

我不确定何时添加它,但是为了完整的目的,我想指出的是,在版本9.1(也许之前)IF NOT EXISTS可以使用. IF NOT EXISTS仅在不存在的情况下才能创建该表.

示例:

CREATE TABLE IF NOT EXISTS users.vip
(
  id integer
)

这将在架构中创建一个名为vip的表users如果表不存在.

source

其他推荐答案

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();

其他推荐答案

只需创建表即可,不必担心它是否存在.如果不存在,它将被创建;如果确实存在,则表将不会修改.您始终可以检查SQL查询的返回值,以查看执行Create语句时是否存在该表是否存在.

本文地址:https://www.itbaoku.cn/post/597575.html

问题描述

In Postgresql, how can I do a condition to create a table only if it does not already exist?

Code example appreciated.

推荐答案

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

其他推荐答案

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();

其他推荐答案

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.