StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>With the constraint you added (not being superuser), I do not find a pure-SQL solution. But doing it in your favorite language is quite simple. You open a connection to the "old" database, another one to the new database, you SELECT in one and INSERT in the other. Here is a tested-and-working solution in Python.</p> <pre><code> #!/usr/bin/python """ Copy a *part* of a database to another one. See <http://stackoverflow.com/questions/414849/whats-the-best-way-to-copy-a-subset-of-a-tables-rows-from-one-database-to-anoth> With PostgreSQL, the only pure-SQL solution is to use COPY, which is not available to the ordinary user. Stephane Bortzmeyer <bortzmeyer@nic.fr> """ table_name = "Tests" # List here the columns you want to copy. Yes, "*" would be simpler # but also more brittle. names = ["id", "uuid", "date", "domain", "broken", "spf"] constraint = "date > '2009-01-01'" import psycopg2 old_db = psycopg2.connect("dbname=dnswitness-spf") new_db = psycopg2.connect("dbname=essais") old_cursor = old_db.cursor() old_cursor.execute("""SET TRANSACTION READ ONLY""") # Security new_cursor = new_db.cursor() old_cursor.execute("""SELECT %s FROM %s WHERE %s """ % \ (",".join(names), table_name, constraint)) print "%i rows retrieved" % old_cursor.rowcount new_cursor.execute("""BEGIN""") placeholders = [] namesandvalues = {} for name in names: placeholders.append("%%(%s)s" % name) for row in old_cursor.fetchall(): i = 0 for name in names: namesandvalues[name] = row[i] i = i + 1 command = "INSERT INTO %s (%s) VALUES (%s)" % \ (table_name, ",".join(names), ",".join(placeholders)) new_cursor.execute(command, namesandvalues) new_cursor.execute("""COMMIT""") old_cursor.close() new_cursor.close() old_db.close() new_db.close() </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload