SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

nbv4: Aug 21, 2002; by Duchess Gummybuns

OK, so you have databases which exist within a mysql installation. Within each database, you have tables. In each table you have fields. Is all that correct?

For a website project, should I have ALL of it in one database, each user in their own table, and then user details as a field in the tables, or do I have all this messed up?

I'm having trouble wrapping my brain around how data is represented in mysql. Most beginner tutorials focus on specific commands (which I can just learn just fine by reading code examples) and have nothing on the kind of stuff I'm having trouble with.

Basically I'm wanting to make a site that has a ton of users, and each user has a ton of data that is stored in the database (with no data shared between any other users). I'm got phpmyadmin installed, and when I create a new table, it wants me to give a name for the table, and I don't know if I should put "bob", "user_name", "users", or what-the-gently caress.

nbv4 fucked around with this message at 10:07 on Nov 12, 2007

# ¿ Nov 12, 2007 10:04

Adbot: ADBOT LOVES YOU

# ¿ Apr 29, 2024 11:43

nbv4: Aug 21, 2002; by Duchess Gummybuns

minato posted:

Edit: To elaborate, a table consists of a list of 0-many rows. Each row represents all the information about some "thing". A row consists of various fields that describe that single thing. So examples of tables would be "users", "projects", "products", etc. Each row in those tables describes a single user, project or product. The fields depend on the thing you're trying to describe. A user might have an ID, name, DOB, etc. A product might have an ID, name, quantity available, etc.

OK I think I'm starting to get it now.

One more question. Is there a guide somewhere that helps you determine which data type is the best for certain kinds of data? For instance, whats the best type to use to store a users email address? What if you have a value that only has a small number of values? Such as, for example, car manufacturer. Lets say there are only 5 possible manufacturers. Would it be best to store that data as either "1,2,3,4,5", then have each number correlate to a manufacturer, or would it be best to just store it as a string? Or what about setting up 5 different boolean rows for each manufacturer? Would it matter which way you did it?

# ¿ Nov 12, 2007 11:19

nbv4: Aug 21, 2002; by Duchess Gummybuns

code:

<?
// connection information
$hostName = "localhost";
$userName = "root";
$password = "sex";
$dbName = "logbook";

// make connection to database
mysql_connect($hostName, $userName, $password) or die("Unable to connect to host $hostName");

mysql_select_db($dbName) or die( "Unable to select database $dbName");
?>

<form action="register-script.php" method="post">

username: <input type="text" name="username"><p>
email: <input type="text" name="email"><p>
password: <input type="text" name="password"><p>

<input type="submit" name="submit" value="submit">


<?
// Close the database connection
mysql_close();
?>

register-script.php:

code:

<?
// make connection
$hostName = "localhost";
$userName = "root";
$password = "sex";
$dbName = "logbook";

mysql_connect($hostName, $userName, $password) or die("Unable to connect to host $hostName");

mysql_select_db($dbName) or die( "Unable to select database $dbName");
//////////


//$username = $_GET['username'];
//$password = $_POST['password'];

//$query = "INSERT INTO pilots (username) VALUES('$username')";
//$result = mysql_query($query);

print 'hi<br>';
print $username;
print $query;

// Close the database connection
mysql_close();
?>

what am i missing? even if i uncomment out the lines i have commented out, it still doesn't work. I even change the _POST's to _GET's and still no go

# ¿ Nov 14, 2007 07:06

nbv4: Aug 21, 2002; by Duchess Gummybuns

minato posted:

Not closing the </form>?

that was what it was, I feel stupid.

Now I've got another problem that is stumping me. Here is my query:

$result = mysql_query("SELECT * FROM pilots WHERE email=$email and password=" . MD5($password);

then below that, I have:

$row = mysql_fetch_row($result);

print $row[0];

which should print out something, but it's giving me this error:

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /var/www/logbook/login-script.php on line 20

makes me believe the problem is with my query string. I copied the string exactly as how it comes from php:

SELECT * FROM users WHERE email=nbvfour@gmail.com and password=a4f9767401ed74edc2321b7b1bc7790e

into phpmyadmin, and it says it's an invalid query string. I'm thinking I'm supoosed to escape the '@' but I can't seem to find anything on google.

# ¿ Nov 19, 2007 05:52

nbv4: Aug 21, 2002; by Duchess Gummybuns

code:

id   date    instructor  
----------------------------
1    12/7            Bob      
2    12/7            Bob
3    12/8            Tom
4    12/8            Harry
5    12/9            Bob
6    12/10           Tom
7    12/10           William
8    12/11           Tom
9    12/12           Fred
10   12/13           Bob

How can I form a query that returns a list of each instructor and the date latest date associated with that instructor?

In other words, I want this to be returned:

code:

   date    instructor  
----------------------------
    12/8            Harry
    12/10           William
    12/11           Tom
    12/12           Fred
    12/13           Bob

each instructor is returned ONCE, along with the date that is the latest date associated with that instructor. The closest I can come up with is:

SELECT DISTINCT `instructor`, `date`
FROM `flights`
WHERE `pilot_id` = '50'
ORDER BY `date` DESC
LIMIT 12

but thats not quite there. Any help?

# ¿ Dec 17, 2007 06:00

nbv4: Aug 21, 2002; by Duchess Gummybuns

I have a PHP script that, at the very top of the script, makes a MySQL query that grabs a bit of data from the database, and makes that data global. So it looks like this:

php:

<?
$query = "some query";
global $data;
$data = mysql_query($query);
?>

Then the rest of the script has a bunch of functions that use that global mysql data. I have it done this way because I don't want the script to hit the database a bunch of times for the exact same data.

When the functions use the data, they do it with mysql_fetch_array() and a while() loop. At the end of each of those loops, I have "mysql_data_seek($data, 0);", but it still thinks each one, except for the first, is empty. What am I missing?

# ¿ Jan 15, 2008 18:37

nbv4: Aug 21, 2002; by Duchess Gummybuns

quick question, this is my query:

code:

SELECT DISTINCT manufacturer
FROM planes
WHERE pilot_id='5'
ORDER BY manufacturer

If the field is NULL, it has that at the top. How can I change it so that NULL to be sorted at the bottom? Adding DESC at the end will do this, but I want the rest of the items to be alphabetically sorted.

# ¿ Feb 1, 2008 06:39

nbv4: Aug 21, 2002; by Duchess Gummybuns

Victor posted:

code:
order by coalesce(manufacturer, chr(127))
Use whatever function generates a character from its code. Increase the number if you're using Unicode.

cool that worked. I couldn't get that char() thing to work, so I just used

order by coalesce(manufacturer, 'ZZZZZZZZZZZ')

and it worked fine.

# ¿ Feb 1, 2008 21:34

nbv4: Aug 21, 2002; by Duchess Gummybuns

I have a PHP script that makes a few hundred mysql queries, then executes them one by one. All of these queries are INSERT queries. The way I way it is now, if one of those queries fails, it's a pain in the rear end to go through and fix whatever problem that may have occurred. I want to have it so the system does a dry run through each query, and only if every single query is valid, it executes them all. Is there any way to test a query against the database? It's probably very simple, but Googling gives me nothing.

# ¿ Feb 7, 2008 10:46

nbv4: Aug 21, 2002; by Duchess Gummybuns

minato posted:

Why would a query fail? If a query fails, that's a sign that either your data or code is broken.

Do you mean "Don't fail if the record is already there?". Because to do that, you can always do:
code:
INSERT INTO foo (foo_id, bar)
SELECT $foo_id, $bar
WHERE NOT EXISTS (SELECT 1
   FROM foo
   WHERE foo_id = $foo_id
   )

the user uploads a file that is transformed into about 600 queries. If one line of that data is not right for whatever reason, only 599 lines will get entered, and it'll be absolute hell to get things right. I need some way to ensure 100% of the queries are correct, or else it tells the user his data needs looking over.

# ¿ Feb 7, 2008 11:31

nbv4: Aug 21, 2002; by Duchess Gummybuns

minato posted:

But really, you should be validating all data before it gets anywhere near the database before beginning any inserts.

I am, I just would like one last line of defence against total query entry, just for piece of mind against anything unforeseen... I tried running through each query once with "LIMIT 0" attached, but that didn't work. I'll look into the transaction thing.

# ¿ Feb 7, 2008 11:55

nbv4: Aug 21, 2002; by Duchess Gummybuns

kalleboo posted:

This is the whole point of transactions - to be able to roll back changes if anything goes wrong (well, along with making things atomic and the side effect of certain things being optimizable). With MySQL you need to be using InnoDB and a fairly recent version (5.0 IIRC) for support.

I just spent the last hour looking up transactions, and have now got it working with my site. The only problem is that my webhost is using an old version of MySQL so it may not work there

edit: basically i have this:

code:

mysql_query("BEGIN");

for($k=0; $k<sizeof($fixed_file); $k++)
{
        $sql="blah query"

        if(!(mysql_query($sql)))
	{
                mysql_query("ROLLBACK");
		die("There was an error in entering data on line <b>{$k+1}</b>");
		
	}
}

mysql_query("COMMIT");

this works in mysql 5, will it work the same way in 4.1.22? Googling around leads me to believe they changed the way it works in mysql 5...

edit2: nevermind, it works

nbv4 fucked around with this message at 13:49 on Feb 7, 2008

# ¿ Feb 7, 2008 13:20

nbv4: Aug 21, 2002; by Duchess Gummybuns

I have a TEXT field that contains comma separated tags. Is it even possible to create a query that returns each unique tag used?

# ¿ Feb 11, 2008 15:02

nbv4: Aug 21, 2002; by Duchess Gummybuns

No Safe Word posted:

If changing your schema to actually have a table of separate tags and setting up a many to many relationship is an option, then do so.

So you basically want to SELECT DISTINCT across essentially the entire list of tags which can only currently be found by splitting a TEXT field on commas? I'd be surprised if there was a way of doing it in one query. You might be able to build a temp table and then do a SELECT DISTINCT on that, but that's the only way I can think of that you'd be able to do that.

using another table for tags seems like too much work for such a simple tagging system that my project requires. Basically each user owns a bunch of items, and the tags are used to organize and create statistics within that user's items. There will never be a full-table tag search. I'm pretty sure a non-normalized solution is the way to go. I guess if worse comes to worse, I could just do something like:

SELECT DISTINCT tags FROM `planes` WHERE user_id='7'

which would probably return 5 or 6 rows, normally. Then use PHP to separate out the fat and create the list that way...

# ¿ Feb 11, 2008 15:40

nbv4: Aug 21, 2002; by Duchess Gummybuns

m5 posted:

Well, that's the way you do things in SQL.

Then how would it be done?

The user types in their tags. PHP splits the string up at the commas. A recursive function goes through each tag, and checks to see if it's already in the database. If it's not, it adds it, then gets the auto_incrimented value and sticks it into the item's 'tags' row. Thats like 5 or 6 queries. Why do it that way when you can just stuff it all into a TEXT column and be done with it? I'm not writing an enterprise level project here where the database has to fulltext through millions of rows. Each tag exists within each user. None of these tags need to exist "globally".

# ¿ Feb 11, 2008 16:25

nbv4: Aug 21, 2002; by Duchess Gummybuns

Whats the easiest way to get a total of a column by calendar months?

For instance, since today is 3-14-2008, I need the total from the last 6 calendar months, so that would be all items from 9-01-2007 to 3-14-2008.

# ¿ Mar 14, 2008 20:45

nbv4: Aug 21, 2002; by Duchess Gummybuns

IsaacNewton posted:

What DBA are you using?

In MySQL, you could GROUP BY year(date), month(date) (e.g. the two fields) example:
code:
SELECT sum(column) FROM sometable WHERE date >= date_add(now(), INTERVAL -6 month) GROUP BY year(date), month(date)

yeah, I'm using mysql and that query you provided works wonderfully. Is there any way to get it to return it it all on one row, though?

edit:" nevermind, i figured it out. remove the GROUP BY part...

nbv4 fucked around with this message at 00:37 on Mar 15, 2008

# ¿ Mar 14, 2008 22:58

nbv4: Aug 21, 2002; by Duchess Gummybuns

I have a SQL problem that I can't even figure out where to begin. I need to get the date of the third from last occurance of a record. Say this is the table:

code:

date       |    items
--------------------------
3-11-2008  |     2
3-12-2008  |     0
3-13-2008  |     1
3-14-2008  |     1
3-15-2008  |     2

I want 3-14-2008 to be returned because thats where the third from last occurrence of the item was. If 0 items existed on 3-15-2008, 3-11-2008 would be returned. And if 3 were on 3-15-2008, then 3-15-2008 would be returned.

# ¿ Mar 17, 2008 07:34

nbv4: Aug 21, 2002; by Duchess Gummybuns

minato posted:

Bingo.

so if "item" is "approaches", and the name of the table is "flights" would the query look like this:

code:

SELECT `date`, `approaches`
FROM flights
WHERE (SELECT SUM(approaches)
                   FROM flights AS flights2
                   WHERE flights2.date >= flight.date) >= 3
ORDER BY total
LIMIT 1

...because its giving me a weird result where the date doesn't even contain an instance of 'approaches'.

# ¿ Mar 17, 2008 08:30

nbv4: Aug 21, 2002; by Duchess Gummybuns

How can I sort a table like this:

code:

item_id    |   value1     |  value2
1                36
2                26
3                              69
4                12
5                              9
6                78
7                              436
8                2
9                2

So it returns like this:

code:

item_id    |   value1     |  value2
7                              436
6                78
3                              69
1                36
2                26
4                12
5                              9
8                2
9                2

See how I did that? The sorting is based on two different columns at the same time. An easy solution would be to make a third column that is just the two columns added together, but I suck at SQL so hard I can't even figure out how to do that :shobon:

nbv4 fucked around with this message at 06:37 on Mar 24, 2008

# ¿ Mar 22, 2008 13:05

nbv4: Aug 21, 2002; by Duchess Gummybuns

Boogeyman posted:

If you're using MSSQL, this will work (just tested it).
code:
SELECT
  item_id,
  value1,
  value2
FROM
  whatever
ORDER BY
  COALESCE(value1, value2) DESC
COALESCE returns the first non-null argument that was passed to it. If either value1 or value2 is null for each row, this will work.

That actually doesn't work because value1 is actually "SUM(another_value) AS value1" so it gives me this error: "#1247 - Reference 'value1' not supported (reference to group function)"

I think making a third column would be best because some instances there may be a value in both value1 and value2.

# ¿ Mar 24, 2008 23:03

nbv4: Aug 21, 2002; by Duchess Gummybuns

Alex007 posted:

You can't sort on grouped fileds, but you can do it (sort) in an outer select:
code:
SELECT * FROM (SELECT SUM(field1) AS f1, SUM(field2) AS f2 FROM mytable) AS mydata ORDER BY f1+f2

hmm, thats not working either. Here is my exact query:

code:

SELECT *
FROM (
   SELECT planes.tail_number, SUM(total) AS total, SUM(flights.simulator) AS simulator
   FROM planes, flights
   WHERE planes.pilot_id = 21 AND planes.plane_id = flights.plane_id AND planes.tail_number != ""
   GROUP BY planes.tail_number) AS mytable
ORDER BY (mytable.total + mytable.simulator)

The inner SELECT works perfectly, minus the ordering. Here is an example of my output with the query above:

code:

tail_number    total    simulator
N1600U  	7.5  	NULL
N378AM  	21.5 	NULL
N53797  	1.3 	NULL
N757NJ  	39.6 	NULL
FRASCA142 	NULL 	31.3
N366AM  	27.8 	NULL
N48581  	36.0 	NULL
N69254          45.3  	NULL

as you can see, it seems to think by "ORDER BY" I really meant "RANDOMIZE BY" :rolleyes:

If I order it by either total or simulator alone, it works as expected, but adding that plus sign screwes it all up.

nbv4 fucked around with this message at 00:40 on Mar 26, 2008

# ¿ Mar 26, 2008 00:26

nbv4: Aug 21, 2002; by Duchess Gummybuns

Could someone please help me come up with a WHERE clause that returns a row if it's NOT in the form of "XXX-XXX"

for instance, "abc-abc" is not a hit, but "abc-xyx" is. The column can be written as "abc-abc-asd-fdffsdfrekl kjfdg", but I'm only interested in the first seven characters. I imagine regular expressions could play a role in this, but I don't know how to do those :shobon:

edit: changed requirements

nbv4 fucked around with this message at 10:32 on Apr 2, 2008

# ¿ Apr 2, 2008 10:22

nbv4: Aug 21, 2002; by Duchess Gummybuns

Zombywuf posted:

code:

WHERE substring(colname, 1, 3) <> substring(colname, 5, 3)

in SQL Server 2005.

ah, good job. I didn't realize sql had substring functions. I was looking around for a fulltext search solution...

# ¿ Apr 2, 2008 11:37

nbv4: Aug 21, 2002; by Duchess Gummybuns

I have a table thats about to be populated with a few hundred thousand records. The table has about 34 columns. Some of these columns are boolean columns that could be combined with one another. For instance one column called "is_car" and another "is_motorcycle", which could be combined as "vehicle_type" with a value of either NULL, "m", or "c".

The way it is now works fine, I'm just wondering if it's worth the trouble to get a little more performance (if any performance at all) by restructuring the table. Will it matter at all in the long run?

# ¿ May 6, 2008 11:21

nbv4: Aug 21, 2002; by Duchess Gummybuns

I'm working on a tagging scheme for my website. Basically I have one table full of blog entries, and another table of tags. Each row in the tag table has an blog_id that connects it to the blog it is tagging. Everything is working fine except for one thing. I can't figure out how I should go about edit/deleting/adding new tags to an blog. Lets say I have one blog that is tagged with three tags. Thats three rows in the tag table each with the corresponding blog_id. If I were to add an additional tag, I could just add a new row to that tags table, but what about if I want to delete one? Do I just delete the row? Wouldn't that hurt performance?

Also, how do I even determine if a its a new tag or not? I'm thinking of, whenever the user edits tags, to just delete all existing tags for that blog, then add the new ones, ensuring no duplicates exist. Is this a dumb idea? I can't think of any other way to do it.

nbv4 fucked around with this message at 07:47 on May 8, 2008

# ¿ May 8, 2008 01:06

nbv4: Aug 21, 2002; by Duchess Gummybuns

I've got a pretty simple problem:

code:

SELECT DISTINCT route FROM flights, planes LEFT JOIN tags ON planes.plane_id = tags.plane_id
WHERE flights.plane_id = planes.plane_id
AND ((planes.category_class = 1 OR planes.category_class = 3))
AND flights.pilot_id = 22
AND flights.route IS NOT NULL
AND tags.tag != "Turbine"

Basically I have a table called "flights" which is linked to one plane, which is then linked to multiple tags via a tag table. If I run this query, it will return no results. If I remove the last line, it'll work perfectly. Even if I change the "!=" to "=", I still get zero results. I need to exclude any flights where the plane has that certain "Turbine" tag.

# ¿ May 28, 2008 17:54

nbv4: Aug 21, 2002; by Duchess Gummybuns

Jethro posted:

code:

       AND planes.plane_id not in
          (SELECT plane_id
             FROM tags
            WHERE tag = 'Turbine')

this is exactly what I needed, thanks

# ¿ May 30, 2008 19:35

nbv4: Aug 21, 2002; by Duchess Gummybuns

I have a table that has a primary key that is auto incremented. Over time many entries have been deleted, but as you know, the auto_increment value just keeps going. There are like 2200 total rows, yet the auto_increment value is currently on like 3800. I want to reset the auto_increment value so it will begin going through and "filling in the holes".

How do you reset the auto_increment value? Googling bring up this query:

ALTER TABLE [table name] AUTO_INCREMENT = 1

But whenever I run it, I get the "this query was successfully executed!" message in phpmyadmin, but nothing gets reset. New records get placed with ~3800 values. Even if I go to the "Operations" tab and enter "1" into the text box, it gives me the same success message, but when the page refreshes, the old value of ~3800 is displayed. If I put in a larger value, like 9000, it will work fine.

First off, will setting the value to 1 actually do what I want, or will it just overwrite over the old values and screw up all my data? And secondly, how I actually set the darn value?

# ¿ Aug 29, 2008 20:56

nbv4: Aug 21, 2002; by Duchess Gummybuns

SQL nub here. I have a varchar field in my table that the user can have in many different formats, such as "abc def", "ABD-DEF", "ABC; DEF", "ABC - DEF", etc etc.

So far I've used PHP to do all the "cleaning up" of this data to get into a uniform format to work with, but now I realize the best way is probably to do it with a MySQL function. Something like "SELECT DISTINCT clean_up(`column`) FROM ..." instead of a bunch of PHP loops and crap following it.

Doing a google search for "user defined functions mysql" tells me that there are about 3 or 4 ways to create functions, and they all, as far as I can tell, require compiling C++ code. Is there an easy way to define a simple function without going balls out? All I need is a few REPLACE()'s, and a SUBSTRING() or two...

# ¿ Sep 18, 2008 06:38

nbv4: Aug 21, 2002; by Duchess Gummybuns

In my database, I have a table called "flights" which has information about the flight, as well as a plane_id, which is connected to a row in a "planes" table which has information about the plane. I have a user who has like 900 planes in his database which are just basically "orphan" planes. I want to remove these duplicate planes, which do not have a single flight connected to them. How do I do this? I came up with a query which pretty much does what I want that uses LEFT JOIN's and discards all entries that have a flight_id value greater that's not NULL, but this query for some stupid reason takes like over 40 seconds to run on normal user_id's, and for this particular user, it I guess takes too long because it always returns a "1053" error. Whats the best way to do this?

edit: here is the query that is taking too long to run:

code:

SELECT * 
FROM planes 
LEFT JOIN flights 
ON flights.plane_id = planes.plane_id
WHERE planes.pilot_id = 137 AND flights.flight_id is NULL

This code works on just about every other user_id I've tried, but this one particular user who has like 900 planes, it takes so long it returns an error.

nbv4 fucked around with this message at 20:04 on Dec 15, 2008

# ¿ Dec 15, 2008 19:49

nbv4: Aug 21, 2002; by Duchess Gummybuns

^^ actually I'm stupid. There were no indexes... I added the indexes and now it runs in under a second.

# ¿ Dec 15, 2008 20:29

nbv4: Aug 21, 2002; by Duchess Gummybuns

Triple Tech posted:

What database system is this that something like that takes over 40 seconds?

MySQL 5 with about 14,000 rows total and no indexes lollers.

# ¿ Dec 15, 2008 21:04

nbv4: Aug 21, 2002; by Duchess Gummybuns

nevermind...

nbv4 fucked around with this message at 02:49 on Sep 5, 2009

# ¿ Sep 4, 2009 22:21

nbv4: Aug 21, 2002; by Duchess Gummybuns

How do you copy a postgis database from one server to another? One the old server, I'm using postgres 8.3 and postgis 1.3.x. On the new server I'm using 8.4 and postgis 1.4.0.

Is there a way to do pg_dump without including the postgis stuff?

# ¿ Nov 7, 2009 19:50

nbv4: Aug 21, 2002; by Duchess Gummybuns

nevermind

nbv4 fucked around with this message at 02:43 on Nov 20, 2009

# ¿ Nov 20, 2009 02:35

nbv4: Aug 21, 2002; by Duchess Gummybuns

I have a backup script that looks like this:

code:

pg_dump -U doop mydb --table=mytable1 | bzip2 > /path/table1.sql.bz2
pg_dump -U doop mydb --table=mytable2 | bzip2 > /path/table2.sql.bz2
pg_dump -U doop mydb --table=mytable3 | bzip2 > /path/table3.sql.bz2

The only problem is that if someone were to add a record to table 3 after I started the backup script (which takes about 30 seconds to complete all tables), it won't work when trying to restore the backups, I'll get this error:

code:

ERROR:  insert or update on table "table3" violates foreign key constraint "somecolumn_id_refs_id_4932d05d"
DETAIL:  Key (somecolumn_id)=(322961) is not present in table "table1".

This is all done in a transaction, so since that one record is not present, the whole backup restore process is aborted. The restore script looks like this:

code:

bunzip2 -c /path/table1.sql.bz2 | psql table1
bunzip2 -c /path/table2.sql.bz2 | psql table2
bunzip2 -c /path/table3.sql.bz2 | psql table3

Is there any way I can tell postgres to ignore records that do not match up and just throw those ones out?

# ¿ Mar 5, 2010 20:48

nbv4: Aug 21, 2002; by Duchess Gummybuns

ShoulderDaemon posted:

You can pass multiple --table arguments to pg_dump, so your backups won't be inconsistent.

Ok, now I have this:

code:

pg_dump -U django logbook --format=c --compress=5
--table='(tagging|profile|foo|terds|derp|doop)_*' > dump.sql

which dumps it all to one file, which is nice, but the only problem is that my tables are very sensitive to the order that they are restored in (each table has a bunch of joins). Currently it works fine if I do this:

code:

pg_restore dump.sql --clean --table=table3 --format=c -a -d mydb;
pg_restore dump.sql --clean --table=table2 --format=c -a -d mydb;
pg_restore dump.sql --clean --table=table1 --format=c -a -d mydb;

but I'd rather have it in one single command, so I can take advantage of the -j switch. Apparently there is a -l switch that spits out a table of contents, which I can then edit the order of and then pass back in with -L, but the documentation on the postgres site is very limited on how this can be done. The TOC that gets spit out in my case is quite a bit different than the one in the docs... here is what mine looks like: http://pastebin.com/0Fj4GA3R How should I be ordering this stuff so it works?

# ¿ Mar 6, 2010 02:26

Adbot: ADBOT LOVES YOU

# ¿ Apr 29, 2024 11:43

nbv4: Aug 21, 2002; by Duchess Gummybuns

ShoulderDaemon posted:

If you pass --disable-triggers to pg_restore then it will prevent foreign key checks from being applied to the data as it restores

ah thanks that did it

# ¿ Mar 7, 2010 02:10

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'