AS TABLE statement: CREATE TABLE pets4 AS TABLE pets AS TABLE StatementĪnother way to copy the table and its data is to use the CREATE TABLE. This option doesn’t copy the data but it does copy any NOT NULL constraints associated with the table. LIKE option: CREATE TABLE pets3 (LIKE pets) For example: CREATE TABLE pets2 ASĪs mentioned, this method doesn’t copy any indexes or constraints associated with the table (such as primary keys, foreign keys or NOT NULL constraints). If we don’t want to insert the data, we can use the WITH NO DATA clause: CREATE TABLE pets2 ASĪlternatively, we can modify the query to return no results. I also copied all data from the original table to its clone.
Here, I copied a table called pets and named the new table pets2. We can use the following statement to copy that table, along with its data: CREATE TABLE pets2 AS AS SELECT statement to copy the table and its data, but not its indexes. The method we use will depend on things like, whether or not we want to transfer the data, indexes, etc. Below are six methods we can use to clone a table in Postgres. NOTICE: CREATE TABLE will create implicit sequence "stupid_table_with_a_very_very_very_very_very_very_ver_my_id_seq" for "serial" column "stupid_table_with_a_very_very_very_very_very_very_very_very_ver.PostgreSQL provides us with several options when it comes to cloning a table. NOTICE: identifier "stupid_table_with_a_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_longname" will be truncated to "stupid_table_with_a_very_very_very_very_very_very_very_very_ver" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "stupid_table_pkey" for table "stupid_table"Īutotest=# create table stupid_table_with_a_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_longname(my_id serial, primary key(my_id)) NOTICE: CREATE TABLE will create implicit sequence "stupid_table_my_id_seq" for "serial" column "stupid_table.my_id" \g or terminate with semicolon to execute queryĪutotest=# create table stupid_table(my_id serial, primary key(my_id)) Welcome to psql 7.4.29, the PostgreSQL interactive terminal. $ /opt/PostgreSQL/7.4/bin/psql -p 5436 -d autotest -U postgres This works even on archaic PostgreSQL 7.4 (and on my PostgreSQL 8.4 too), so should be safe backporting :
Omitting the primary key name and letting PostgreSQL decide over it succeeds as pgsql truncates the name to take into account the suffix (it adds _pkey as a suffix). "ERROR 1: CREATE TABLE \"public\".\"sql_filename_manutenzione2009_q_select_from_manutenzione2009_fo\" ( \"cartodb_id\" SERIAL, CONSTRAINT \"sql_filename_manutenzione2009_q_select_from_manutenzione2009_fo_pk\" PRIMARY KEY (\"cartodb_id\") )ĮRROR: relation \"sql_filename_manutenzione2009_q_select_from_manutenzione2009_fo\" already existsĮRROR 1: Terminating translation prematurely after failed\ntranslation of layer sql_filename_manutenzione2009_q_select_from_manutenzione2009_fo20130620-29277-1vedg2r (use -skipfailures to skip errors) PostgreSQL truncates those 3 characters from the constraint name, to get it within limits, and the result is an error due to the fact that a relation with the given name already exists (a primary key creates an index which is a relation): When asked to create a table with a name of 63 characters (postgresql name limit) ogr2ogr adds a PRIMARY KEY constraint with a name that is derived from the table name with 3 characters appended ("_pk").
0 kommentar(er)
