Quote: I’ve tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results.* SELECT * from dba_objects WHERE object_name like ‘%DTN%’ A column isn’t an object. If you mean that you expect the column name to be like ‘%DTN%’, the query you … Read more
Keep a friend table that holds the UserID and then the UserID of the friend (we will call it FriendID). Both columns would be foreign keys back to the Users table. Somewhat useful example: Table Name: User Columns: UserID PK EmailAddress Password Gender DOB Location TableName: Friends Columns: UserID PK FK FriendID PK FK (This … Read more
You need to do the subselect based on a column name, not *. For example, if you had an id field common to both tables, you could do: SELECT * FROM Table1 WHERE id NOT IN (SELECT id FROM Table2) Refer to the MySQL subquery syntax for more examples.
No, this will not drop your table if there are indeed foreign keys referencing it. To get all foreign key relationships referencing your table, you could use this SQL (if you’re on SQL Server 2005 and up): SELECT * FROM sys.foreign_keys WHERE referenced_object_id = object_id(‘Student’) and if there are any, with this statement here, you … Read more
The greatest value of an integer has little to do with the maximum number of rows you can store in a table. It’s true that if you use an int or bigint as your primary key, you can only have as many rows as the number of unique values in the data type of your … Read more
FrustratedWithFormsDesigner is correct, PL/pgSQL can do this. Here’s the script: CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$ DECLARE statements CURSOR FOR SELECT tablename FROM pg_tables WHERE tableowner = username AND schemaname=”public”; BEGIN FOR stmt IN statements LOOP EXECUTE ‘TRUNCATE TABLE ‘ || quote_ident(stmt.tablename) || ‘ CASCADE;’; END LOOP; END; $$ LANGUAGE … Read more
schema -> floor plan database -> house table -> room
This feature has been implemented in Postgres 9.1: CREATE TABLE IF NOT EXISTS myschema.mytable (i integer); For older versions, here is a function to work around it: CREATE OR REPLACE FUNCTION create_mytable() RETURNS void LANGUAGE plpgsql AS $func$ BEGIN IF EXISTS (SELECT FROM pg_catalog.pg_tables WHERE schemaname=”myschema” AND tablename=”mytable”) THEN RAISE NOTICE ‘Table myschema.mytable already exists.’; … Read more
Just in case anyone still cares: I had the same issue after copying a database directory directly using command cp -r /path/to/my/database /var/lib/mysql/new_database If you do this with a database that uses InnoDB tables, you will get this crazy ‘table does not exist’ error mentioned above. The issue is that you need the ib* files … Read more
Just use a where clause that won’t select any rows: create table xyz_new as select * from xyz where 1=0; Limitations The following things will not be copied to the new table: sequences triggers indexes some constraints may not be copied materialized view logs This also does not handle partitions