How to CREATE TABLE copying "Default" and "Constraints" of the previous table in PostgreSql using PHP?

Question

I am not able to create table like/as with constraints and default values from another table that already exists.

I have tried following

$sql = "CREATE TABLE schemas.a AS TABLE schemas.b";

This creates the table but no constraint or default value is copied.

$sql = "CREATE TABLE schemas.a (LIKE schemas.b INCLUDING CONSTRAINTS)";

This creates the table with not null constraint only. Still the primary key constraint and the default value is not being transferred.

Coding used in php

$sql = "CREATE TABLE schemas.a (LIKE schemas.b INCLUDING CONSTRAINTS)";
pg_query($con, $sql);  //This is in the PHP

I want to create the table with all the constraints (primary, unique, not null) and default value that are set.

The code works outside of PHP? Not a postgresql user but seems strange that the behavior is related to PHP. — user3783243
– user3783243, Commented Oct 3, 2019 at 23:46
This has nothing to do with php, it is just about the PostgreSQL CREATE TABLE command. — Islingre
– Islingre, Commented Oct 4, 2019 at 2:23
CREATE TABLE Database.NewTableName SELECT * FROM Database.ExistingTable This will create a copy of your table including structure (minus the Primary Key link which you will want to set yourself after you copy it. But this is a FAST way to duplicate a table that is similar to the new one you want to make, and then you can just make the edits to that table. — easleyfixed
– easleyfixed, Commented Oct 25, 2022 at 19:05

Islingre · Accepted Answer · 2019-10-04 02:28:16Z

1

Try the following sql:

CREATE TABLE schemas.a (
    LIKE schemas.b
        INCLUDING ALL
);

For further information about the CREATE TABLE command have a look into the documentation: https://www.postgresql.org/docs/current/sql-createtable.html (Perhaps you need to switch to your version. Search for like_option to find the relevant information on the page.)

answered Oct 4, 2019 at 2:28

Islingre

2,37810 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rock123A Over a year ago

Don't know the problem but this work. Earlier i tried something like this but hit with an error. INCLUDING ALL works perfectly.

Collectives™ on Stack Overflow

How to CREATE TABLE copying "Default" and "Constraints" of the previous table in PostgreSql using PHP?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related