Creating Multiple Tables in PostgreSQL

Question

I want to create multiple tables (more than 100 tables) in one go. I am trying to write a script using loop such as for loop for generating as many tables as I want. I am new in PostgreSQL. I would be grateful if someone could give me some tips.

I'd reconsider why you'd want to create more than 100 tables in a database...especially in a loop. Are you trying to create an identical table for each user or each customer? Adding a column called 'customer_name' or an customer_id field is a much better choice. — Twelfth
– Twelfth, Commented Sep 4, 2013 at 22:51
@Twelfth, you are right. I am trying to create table for each customer and yes, the tables are identical. — user2711722
– user2711722, Commented Sep 4, 2013 at 23:29
I usually use spreadsheet with simple text concatenation formulas to do such one-time tasks, e.g.: ="create table "&A1&" (a int, b int);" where in column A you have table names and then you just copy/paste this formula. — Tomas Greif
– Tomas Greif, Commented Sep 5, 2013 at 9:31
just write a quick python script with a for loop and each iteration of the loop using the PSQL driver to create a table. done! — fIwJlxSzApHEZIl
– fIwJlxSzApHEZIl, Commented Jul 28, 2023 at 13:28

wildplasser · Accepted Answer · 2013-09-04 23:01:16Z

3

#!/bin/sh

(
for i in 0 1 2 3 4 5 6 7 8 9; do
for j in 0 1 2 3 4 5 6 7 8 9; do
        echo "SET search_path=tmp;"
        echo "CREATE TABLE barf${i}${j}"
        echo " ( id SERIAL NOT NULL PRIMARY KEY );"
done
done) | psql -U lutsername databasename

answered Sep 4, 2013 at 23:01

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

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

1 Comment

wildplasser Over a year ago

IIRC that would be a bash extension. UPDATE: no, it is not. (but hey! I like to be explicit) But at least it would work on systems where ARG_MAX < 100 ;-)

Twelfth · Accepted Answer · 2013-09-05 15:42:26Z

1

My answer here has to be 'dont do it!' and change your architechture. Create a table called customer and have a customer_id along with some other info. Create a second table with the columns you want here and a 'customer_id' column to refer to the customer table. This format will allow you to store in two tables what you are trying to store in 100+ tables. You want to stardardize your data base design at this stage...otherwise you are setting yourself up for a nightmare in the not too distant future. Databases are not spreadsheets...

answered Sep 5, 2013 at 15:42

Twelfth

7,2083 gold badges30 silver badges35 bronze badges

5 Comments

user2711722 Over a year ago

I do appreciate your suggestion but it is my client's demand. The organization wants to breakdown their tables into per customer.

user330315 Over a year ago

@user2711722: that is the most stupid requirement I have ever heard of. If I were you I would refuse to implement such a nonsense. You can still create a single table and create a view that "fakes" the customer specific tables using Twelth' approach

Twelfth Over a year ago

I'd still make sure they understand the nightmare they are creating here...future administration costs and the bulkiness of this set up will cripple their business later on (this is why your question has a -2 now). That said, the customer is always right and if they feel like jumping off a cliff tis our duty to throw them off. Someone will get a contract to clean this up eventually. You can loop this with dynamic SQL (security loophole, but I get the feeling your client won't care). My preference though will be as Tomas Greif said, use a spreadsheet to mass make these table creates.

Twelfth Over a year ago

To repeat horse with no name...i'd rethink my last comment and refuse to implement, even walk off the job if needed...having my name associated with an implement of a database like this isn't worth it.

Yumi Koizumi Over a year ago

As strict/elitist as SO is with users, I'm surprised so many answers are allowed to stay that do not answer the question. If an OP wants know whether it is a good idea, that is a different question, no? An answer that serves nobody is left to stand while "what is the best way to..." questions are tossed after a quick scan. If the goal of SO is low signal to noise, then...

Collectives™ on Stack Overflow

Creating Multiple Tables in PostgreSQL

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related