[SQL] some error when executing query in pgAdmin tool
I created one table in pgAdmin tool but when I am executing
query it is giving error….
CREATE TABLE "ADV"
(
"T-Section_Id" varchar(10) NOT NULL,
"CDA_No" varchar(7) NOT NULL,
"Imp_Schedule_Id" int4 NOT NULL,
"Sanction_No" varchar(20) NOT NULL,
"Sanction_Date" date NOT NULL,
"Station_From" varchar(20) NOT NULL,
"Station_To" varchar(20) NOT NULL,
"Amt_Claimed" int4,
"Amt_Admitted" int4,
"Dak_Id" varchar(20) NOT NULL,
"Refund_Dak_Id" int4 NOT NULL,
"T-Wing_Allowance_Id" varchar(10) NOT NULL,
CONSTRAINT "ADV_pkey" PRIMARY KEY
("T-Section_Id")
)
WITHOUT OIDS;
ALTER TABLE "ADV" OWNER TO postgres;
Above table I created…..
Select * from ADV; when I am executing this query I am
getting error is
Relation ADV does not exist… like this error is giving
… may I know y am I getting this error
Thanks & Regards
Penchal reddy | Software Engineer
Infinite Computer Solutions | Exciting Times…Infinite Possibilities...
SEI-CMMI level 5 | ISO 9001:2000
IT SERVICES |
BPO
Telecom | Finance
| Healthcare | Manufacturing
| Energy & Utilities | Retail
& Distribution | Government
Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com
Information transmitted by this e-mail is
proprietary to Infinite Computer Solutions and/ or its Customers and is
intended for use only by the individual or entity to which it is addressed, and
may contain information that is privileged, confidential or exempt from
disclosure under applicable law. If you are not the intended recipient or it
appears that this mail has been forwarded to you without proper authority, you
are notified that any use or dissemination of this information in any manner is
strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
Re: [SQL] some error when executing query in pgAdmin tool
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Penchalaiah
P.Sent: 23 May 2006 13:11To:
pgsql-sql@postgresql.orgSubject: [SQL] some error when executing
query in pgAdmin tool
I created one table in pgAdmin
tool but when I am executing query it is giving
error….
CREATE TABLE
"ADV"
(
"T-Section_Id" varchar(10)
NOT NULL,
"CDA_No" varchar(7) NOT
NULL,
"Imp_Schedule_Id" int4 NOT
NULL,
"Sanction_No" varchar(20)
NOT NULL,
"Sanction_Date" date NOT
NULL,
"Station_From" varchar(20)
NOT NULL,
"Station_To" varchar(20)
NOT NULL,
"Amt_Claimed"
int4,
"Amt_Admitted"
int4,
"Dak_Id" varchar(20) NOT
NULL,
"Refund_Dak_Id" int4 NOT
NULL,
"T-Wing_Allowance_Id"
varchar(10) NOT NULL,
CONSTRAINT "ADV_pkey"
PRIMARY KEY ("T-Section_Id")
)
WITHOUT
OIDS;
ALTER TABLE "ADV" OWNER TO
postgres;
Above table I
created…..
Select * from ADV;
when I am executing this query I am getting error
is
Relation
ADV does not exist… like this error is giving … may I know y am I getting this
error
Try
SELECT * FROM "ADV";
pgAdmin quoted the tablename because you used uppercase
characters. Without the quotes, the name is folded to
lowercase.
Regards, Dave.
Re: [SQL] some error when executing query in pgAdmin tool
From: Penchalaiah P. [mailto:[EMAIL PROTECTED] Sent: 23 May 2006 13:18To: Dave PageSubject: RE: [SQL] some error when executing query in pgAdmin tool Hi sir… I tried like that also … but I am getting same error……I tried like these…… 1).Select * from adv; 2). Select * from “adv”; 3).Select * from public.adv; here public is the schema name Like I said, try: SELECT * FROM "ADV"; You created the table with a quoted, uppercase name, so that is how you must reference it. Regards, Dave
[SQL] hi can u give solution to this query
Hi sir…
If u don’t mind can u reply for this question
This is one table… it has one primary key…….so
I can insert the values from data edit…
1)…CREATE TABLE "ADV"
(
"T-Section_Id" varchar(10) NOT NULL,
"CDA_No" varchar(7) NOT NULL,
"Imp_Schedule_Id" int4 NOT NULL,
"Sanction_No" varchar(20) NOT NULL,
"Sanction_Date" date NOT NULL,
"Station_From" varchar(20) NOT NULL,
"Station_To" varchar(20) NOT NULL,
"Amt_Claimed" int4,
"Amt_Admitted" int4,
"Dak_Id" varchar(20) NOT NULL,
"Refund_Dak_Id" int4 NOT NULL,
"T-Wing_Allowance_Id" varchar(10) NOT NULL,
CONSTRAINT "ADV_pkey" PRIMARY KEY
("T-Section_Id")
)
WITHOUT OIDS;
ALTER TABLE "ADV" OWNER TO postgres;
2) this table doesn’t contain
primary key … but I cant insert values from data edit…
CREATE TABLE "ADV_LTC"
(
"T-Section_Id" varchar(20) NOT
NULL,
"Ltc_Block_Year" date NOT
NULL,
"Ltc_Type" varchar(10) NOT
NULL,
"Leave_Period_From" date NOT
NULL,
"Leave_Period_To" date NOT
NULL,
"Date_Journey" date NOT NULL,
"Travelling" varchar(10) NOT
NULL,
"No_Child" int4,
"No_Adult" int4,
"Child_Fare" int4,
"Adult_Fare" int4,
"Journey_Fare" int4,
"Amt_Clm" int4 NOT NULL,
"Amt_Adm" int4,
"Remarks" varchar(40)
)
WITHOUT OIDS;
ALTER TABLE "ADV_LTC" OWNER TO
postgres;
Can u check it y I am unable to insert
values to 2nd table……….
Thanks & Regards
Penchal reddy | Software Engineer
Infinite Computer Solutions | Exciting Times…Infinite Possibilities...
SEI-CMMI level 5 | ISO 9001:2000
IT SERVICES |
BPO
Telecom | Finance
| Healthcare | Manufacturing
| Energy & Utilities | Retail
& Distribution | Government
Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com
Information transmitted by this e-mail is
proprietary to Infinite Computer Solutions and/ or its Customers and is
intended for use only by the individual or entity to which it is addressed, and
may contain information that is privileged, confidential or exempt from
disclosure under applicable law. If you are not the intended recipient or it
appears that this mail has been forwarded to you without proper authority, you
are notified that any use or dissemination of this information in any manner is
strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
Re: [SQL] some error when executing query in pgAdmin tool
Penchalaiah P. wrote:
I created one table in pgAdmin tool but when I am executing query it
is giving error….
CREATE TABLE "ADV"
(
"T-Section_Id" varchar(10) NOT NULL,
"CDA_No" varchar(7) NOT NULL,
"Imp_Schedule_Id" int4 NOT NULL,
"Sanction_No" varchar(20) NOT NULL,
"Sanction_Date" date NOT NULL,
"Station_From" varchar(20) NOT NULL,
"Station_To" varchar(20) NOT NULL,
"Amt_Claimed" int4,
"Amt_Admitted" int4,
"Dak_Id" varchar(20) NOT NULL,
"Refund_Dak_Id" int4 NOT NULL,
"T-Wing_Allowance_Id" varchar(10) NOT NULL,
CONSTRAINT "ADV_pkey" PRIMARY KEY ("T-Section_Id")
)
WITHOUT OIDS;
ALTER TABLE "ADV" OWNER TO postgres;
Above table I created…..
Select * from ADV; when I am executing this query I am getting error is
Relation ADV does not exist… like this error is giving … may I know y
am I getting this error
*Thanks & Regards*
*Penchal reddy **|** Software Engineer *
*Infinite Computer Solutions **|** Exciting Times…Infinite
Possibilities... *
*SEI-CMMI level 5 **| **ISO 9001:2000*
*IT SERVICES **|** BPO *
*Telecom **|** **Finance **|** **Healthcare **| **Manufacturing **|**
**Energy & Utilities **|** **Retail & Distribution **|** **Government *
*Tel +91-80-5193-(Ext:503)**|** Fax +91-80-51930009 **|** Cell No
+91-9980012376**|**www.infics.com** *
*Information transmitted by this e-mail is proprietary to Infinite
Computer Solutions and/ or its Customers and is intended for use only
by the individual or entity to which it is addressed, and may contain
information that is privileged, confidential or exempt from disclosure
under applicable law. If you are not the intended recipient or it
appears that this mail has been forwarded to you without proper
authority, you are notified that any use or dissemination of this
information in any manner is strictly prohibited. In such cases,
please notify us immediately at** [EMAIL PROTECTED] _**and delete
this mail from your records.*
by quoting ADV in the CREATE TABLE you made it case-sensitive
then by not quoting it in the SELECT, you used the case-insensitive form,
wich PostgreSQL (IIRC) translates as lower case
adv != ADV
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] hi can u give solution to this query
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Penchalaiah
P.Sent: 23 May 2006 13:41To:
pgsql-sql@postgresql.orgSubject: [SQL] hi can u give solution to
this query
Hi sir…
If u don’t mind can u reply
for this question
This is one table… it has one
primary key…….so I can insert the values from data
edit…
1)…CREATE TABLE
"ADV"
(
"T-Section_Id" varchar(10)
NOT NULL,
"CDA_No" varchar(7) NOT
NULL,
"Imp_Schedule_Id" int4 NOT
NULL,
"Sanction_No" varchar(20)
NOT NULL,
"Sanction_Date" date NOT
NULL,
"Station_From" varchar(20)
NOT NULL,
"Station_To" varchar(20)
NOT NULL,
"Amt_Claimed"
int4,
"Amt_Admitted"
int4,
"Dak_Id" varchar(20) NOT
NULL,
"Refund_Dak_Id" int4 NOT
NULL,
"T-Wing_Allowance_Id"
varchar(10) NOT NULL,
CONSTRAINT "ADV_pkey"
PRIMARY KEY ("T-Section_Id")
)
WITHOUT
OIDS;
ALTER TABLE "ADV" OWNER TO
postgres;
2) this table doesn’t
contain primary key … but I cant insert values from data
edit…
CREATE TABLE
"ADV_LTC"
(
"T-Section_Id"
varchar(20) NOT NULL,
"Ltc_Block_Year" date NOT NULL,
"Ltc_Type"
varchar(10) NOT NULL,
"Leave_Period_From" date NOT NULL,
"Leave_Period_To" date NOT NULL,
"Date_Journey"
date NOT NULL,
"Travelling"
varchar(10) NOT NULL,
"No_Child"
int4,
"No_Adult"
int4,
"Child_Fare"
int4,
"Adult_Fare"
int4,
"Journey_Fare"
int4,
"Amt_Clm" int4
NOT NULL,
"Amt_Adm"
int4,
"Remarks"
varchar(40)
)
WITHOUT
OIDS;
ALTER TABLE "ADV_LTC"
OWNER TO postgres;
Can u check it
y I am unable to insert values to 2nd table……….
You have answered
your own question. pgAdmin won't allow you to edit a table without either OIDs
or a Primary Key because it has no way to identify a row (which many would say
is a broken design).
Regards,
Dave.
[SQL] (Ab)Using schemas and inheritance
Hi! I'm modelling an application that will have data -- financial data, human resources, etc. -- for several hundred (even thousands) of companies. This is for an accounting office. I could put some kind of "company_id" column in all of my tables to separate data in a more standard way, I could create a separate schema for each client and then create all needed tables in there (could I? I'd be abusing schemas here and this is part of my doubt) and, finally, I could create a "base" schema, define all my standard tables and create an individual schema for each client where I'd inherit from those base.tables. This would allow me to separate all information with a "SET search_path TO company" without having to make the restriction "by hand" (on "company_id", for example). It would also allow me to view some complete statistics grouping all clients by SELECTing data from the base schema. We're testing views and functions to see how they behave with inherited tables and changes on "search_path", and it looks like we can do that for, at least, a small number of schemas. Of course, this has implications on permissions as well, so there will be a large number of groups -- probably at least one per schema + some common groups -- and roles as well... Is this a good idea? Would this be too bad, performance-wise, if I had thousands of schemas to use like that? Any advice on better approaches? Any expected problems? TIA, -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] (Ab)Using schemas and inheritance
Moving to -general, where it's more likely that others will have input. On Tue, May 23, 2006 at 05:16:54PM -0300, Jorge Godoy wrote: > I'm modelling an application that will have data -- financial data, human > resources, etc. -- for several hundred (even thousands) of companies. This > is for an accounting office. > > I could put some kind of "company_id" column in all of my tables to separate > data in a more standard way, I could create a separate schema for each client > and then create all needed tables in there (could I? I'd be abusing schemas > here and this is part of my doubt) and, finally, I could create a "base" > schema, define all my standard tables and create an individual schema for > each client where I'd inherit from those base.tables. > > This would allow me to separate all information with a "SET search_path TO > company" without having to make the restriction "by hand" (on "company_id", > for example). It would also allow me to view some complete statistics > grouping all clients by SELECTing data from the base schema. We're testing > views and functions to see how they behave with inherited tables and changes > on "search_path", and it looks like we can do that for, at least, a small > number of schemas. > > Of course, this has implications on permissions as well, so there will be a > large number of groups -- probably at least one per schema + some common > groups -- and roles as well... > > > Is this a good idea? Would this be too bad, performance-wise, if I had > thousands of schemas to use like that? Any advice on better approaches? Any > expected problems? One issue is that you'll probably be breaking new ground here a bit; I suspect there's very few people that are using more than a handful of schemas. Shouldn't pose any issues, but you never know; although any issues you do run into should only be performance problems. Another consideration is that the free space map doesn't care too much for tracking space info on tons of small tables. Perhaps the biggest issue is: what happens when you need to do DDL? If you have 1000 schemas that should be identical, you'll need to perform any DDL 1000 times. But as you point out, there's some interesting advantages to using schemas like this. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
