I have this:
DROP SCHEMA Lab1 CASCADE;
CREATE SCHEMA Lab1;
CREATE TABLE Lab1.PERSONS(
SSN INT primary key,
Name CHARACTER (30),
HouseId INT,
ApartmentNumber INT ,
Salary DECIMAL (5, 2)
);
But this doesn't work, it creates the schema fine but then creates the table in a weird place where it can't be viewed (with \d+
for example), in fact, the only way I know it exists is if you try to drop the "lab1" schema it will throw an error saying that schema is being used by lab1.
I tried setting the default path to the lab1 schema: ALTER ROLE -myusername- SET SEARCH_PATH to Lab1;
But that didn't work either, \d+
still has "persons" in public.
So, how do I get this table into the schema I want? Thanks.
How to Use Schema With PostgreSQL. To access an object of a database schema, we must specify the schema's name before the name of a given database object that we want to use. For example, to query table product within schema store, we need to use the qualified name of the table: SELECT * FROM store.
1. A database in PostgreSQL contains the subset of schema. It contains all the schemas, records, and constraints for tables. A Schema in PostgreSQL is basically a namespace that contains all the named database objects like tables, indexes, data types, functions, stored procedures, etc.
tried runnig:
DROP SCHEMA Lab1 CASCADE;
CREATE SCHEMA Lab1;
CREATE TABLE Lab1.PERSONS(
SSN INT primary key,
Name CHARACTER (30),
HouseId INT,
ApartmentNumber INT ,
Salary DECIMAL (5, 2)
);
t=# \dt+ lab1.persons
lab1 | persons | table | postgres | 0 bytes |
As you can see the table is created in lab1 schema. If you want it in Lab1
schema, you should modify your statemet:
t=# DROP SCHEMA "Lab1" CASCADE;
ERROR: schema "Lab1" does not exist
t=# CREATE SCHEMA "Lab1";
CREATE SCHEMA
t=# CREATE TABLE "Lab1".PERSONS(
t(# SSN INT primary key,
t(# Name CHARACTER (30),
t(# HouseId INT,
t(# ApartmentNumber INT ,
t(# Salary DECIMAL (5, 2)
t(# );
CREATE TABLE
t=# \dt+ "Lab1".persons
Lab1 | persons | table | postgres | 0 bytes |
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With