Thread: Two novice questions
I have received a collection of scripts to be used to build a PostgreSQL db and I can't figure out the following. 1. Usage of quotes in the scripts. The following comes from a script to build and populate a table: gid serial PRIMARY KEY, "fid_" int4, Why is the second column name in quotes, i.e. what is the difference between fid_ int4 and "fid_" int 4. In a similar vein, this comes from a script that creates a couple of tables: create table schema1."cache" ( ... create table schema1.cache2 ( ... Here one schema name is quoted and the other is not. 2. How does one run a script from PGAdmin? I have been 'opening' the script file then hitting the run button. Is there a way to just specify the path to the script? Thanks for any help or pointers, -=beeky
On Mon, Jul 14, 2008 at 11:08 AM, Wm.A.Stafford <stafford@marine.rutgers.edu> wrote: > I have received a collection of scripts to be used to build a PostgreSQL db > and I can't figure out the following. > > 1. Usage of quotes in the scripts. > The following comes from a script to build and populate a table: > gid serial PRIMARY KEY, > "fid_" int4, > Why is the second column name in quotes, i.e. what is the difference between > fid_ int4 and "fid_" int 4. > > In a similar vein, this comes from a script that creates a couple of > tables: > create table schema1."cache" ( ... > create table schema1.cache2 ( ... > Here one schema name is quoted and the other is not. The quoting is used to specify a literal name. In particular, if you specify something like: Name varchar(32) Without the quotes, this will be case-folded to all lower-case name. If you use the quotes, the name will not be case-folded. As a general RULE, if you use quotes anywhere, you should use them everywhere. Do not mix-and-match or you will likely get into trouble. > 2. How does one run a script from PGAdmin? I have been 'opening' the > script file then hitting the run button. Is there a way to just specify the > path to the script? I don't think so. You will probably want to look at using the psql executable for mass- or automatic-processing of scripts. Sean
"Sean Davis" <sdavis2@mail.nih.gov> writes: > On Mon, Jul 14, 2008 at 11:08 AM, Wm.A.Stafford > <stafford@marine.rutgers.edu> wrote: >> I have received a collection of scripts to be used to build a PostgreSQL db >> and I can't figure out the following. >> >> 1. Usage of quotes in the scripts. >> The following comes from a script to build and populate a table: >> gid serial PRIMARY KEY, >> "fid_" int4, >> Why is the second column name in quotes, i.e. what is the difference between >> fid_ int4 and "fid_" int 4. >> >> In a similar vein, this comes from a script that creates a couple of >> tables: >> create table schema1."cache" ( ... >> create table schema1.cache2 ( ... >> Here one schema name is quoted and the other is not. > The quoting is used to specify a literal name. In particular, if you > specify something like: > Name varchar(32) > Without the quotes, this will be case-folded to all lower-case name. In the particular examples given here, the quoting is useless --- you'd get exactly the same results without them. I don't know why "fid_" got quoted, though possibly that was generated by someone who incorrectly thought they'd need quotes for an underscore. It's possible that "cache" was quoted by something that knew CACHE is a Postgres keyword --- though since it's an unreserved keyword, it doesn't really need quoting. (If it were a reserved word then it *would* need quotes to be used as a table name.) Up till pretty recently pg_dump didn't make that distinction and would quote unreserved keywords, so I can believe that the use of quotes with "cache" is an artifact of some old pg_dump output. "fid_" I dunno ... Anyway, read the manual concerning identifier syntax: http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS regards, tom lane
Wm.A.Stafford wrote: > I have received a collection of scripts to be used to build a PostgreSQL > db and I can't figure out the following. > > 1. Usage of quotes in the scripts. > The following comes from a script to build and populate a table: > gid serial PRIMARY KEY, > "fid_" int4, > Why is the second column name in quotes, i.e. what is the difference > between fid_ int4 and "fid_" int 4. The use of quotes preserves the case and also allows identifier names to be keywords and to also contain spaces. What triggers identifiers to be quoted will depend on the program used to create the script. My guess here is that it only searches for lowercase alpha and numeric contents in identifiers and quotes otherwise, meaning the underscore may have triggered the quoting. > In a similar vein, this comes from a script that creates a couple of > tables: > create table schema1."cache" ( ... > create table schema1.cache2 ( ... > Here one schema name is quoted and the other is not. As above - cache is listed as a non-reserved keyword. cache2 is not. http://www.postgresql.org/docs/8.3/interactive/sql-keywords-appendix.html > 2. How does one run a script from PGAdmin? I have been 'opening' the > script file then hitting the run button. Is there a way to just specify > the path to the script? I don't believe there is in pgadmin. If you use psql (the cli client for postgresql) then you can run the script with \i or as an option when starting psql. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
I seem to be unable to compile a simple C++ file to access Postgresql
from what I believe are the C interfaces in the libpq (++) package.
(Suse 10.2, PostgreSQL 8.1.5)
I get the following errors (I searched the entire drive for libpq-fe.h
and it doesn't exist after installing all the libpq packages. Do I need
something else?
I installed all the libpq packages from the distribution CD:
Name Avail. Vers.Inst. Vers.Summary Size
libpq++ 4.0 4.0 C++ Client Library for PostgreSQL
100.0 K
libpq 2.5.5 2.5.5 C++ Client Library for PostgreSQL
435.0 K
libpq-devel 2.5.5 2.5.5 C++ Client Library for PostgreSQL
5.0 M
Compile Error:
g++ -I /usr/include/pgsql -c client.cpp
In file included from /usr/include/pgsql/libpq++.h:30,
from client.cpp:9:
/usr/include/pgsql/libpq++/pgconnection.h:30:22: error: libpq-fe.h: No
such file or directory
/usr/include/pgsql/libpq++/pgconnection.h:48: error: invalid function
declaration
/usr/include/pgsql/libpq++/pgdatabase.h:37: error: invalid function
declaration
/usr/include/pgsql/libpq++/pglobject.h:38: error: invalid function
declaration
/usr/include/pgsql/libpq++/pgtransdb.h:38: error: invalid function
declaration
/usr/include/pgsql/libpq++/pgcursordb.h:44: error: invalid function
declaration
I cannot seem to build C++ code that uses the libpq++.h header file. Including the file in my C++ code generates errors Command line: dev:/test # g++ -Wall -g -I. -I/usr/include/pgsql -c client.cpp Errors. /usr/include/pgsql/libpq++/pgconnection.h:48: error: invalid function declaration /usr/include/pgsql/libpq++/pgdatabase.h:37: error: invalid function declaration /usr/include/pgsql/libpq++/pglobject.h:38: error: invalid function declaration /usr/include/pgsql/libpq++/pgtransdb.h:38: error: invalid function declaration /usr/include/pgsql/libpq++/pgcursordb.h:44: error: invalid function declaration Line 48 of pgconnection.h class DLLIMPORT PgConnection Since DLLIMPORT is defined as nothing on my system in the file c.h, is the problem that somehow it doesn't understand the use of the word 'class' as if it's trying to interpret it as C and not C++? Or is it something else? Any ideas?