[SQL] Database OID
Hi, How can I find the database OID from simple SQL command? Thanks, kjt McAfee SCM 4.1 által ellenőrizve! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Database OID
O Kis Jαnos Tamαs έγραψε στις May 17, 2006 : > Hi, > > How can I find the database OID from simple SQL command? SELECT oid,* from pg_database ; > > Thanks, > kjt > > > McAfee SCM 4.1 αltal ellenυrizve! > > ---(end of broadcast)---TIP > 6: explain analyze is your friend > -- -Achilleus ---(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] Database OID
2006. May 17. Wednesday 09.51 dátummal Achilleus Mantzios ezt írta: > > > > How can I find the database OID from simple SQL command? > > SELECT oid,* from pg_database ; > I'm stupid! Thanks, kjt McAfee SCM 4.1 által ellenőrizve! ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Recursive SELECT problem
Hi, I'm trying to write a query to determine whether or not the current login role has CREATEDB (or CREATEROLE) privileges, either directly or inherited from a parent role. The query: SELECT EXISTS (SELECT 1 FROM pg_authid WHERE rolcreatedb = TRUE AND pg_has_role(rolname, 'MEMBER')) AS has_createdb does the job for CREATEDB, except that it doesn't honour rolinherit. Anyone know how I can do this in pure SQL? Thanks, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Table constraints and INSERT
On 17 maj 2006, at 08.42, Risto Tamme wrote:
INSERT INTO "PART" ("P_PARTKEY","P_RETAILPRICE") VALUES(999,90109.89);
but it fails: ERROR: new row for relation "PART" violates check
constraint "PART_check"
The P_PARTKEY column is an integer, which means the expression
P_PARTKEY/10 will yield 99, *not* 99.9.
Try executing
SELECT 9 + 999/10 + 999/10;
from psql or some GUI-utility; the result is 90108.
When you check using your head or pocket calculator then this INSERT
seems to be correct. Is it some floating point mystery?
Is there some trick?
You must cast the integer column to a float or numeric, try:
SELECT 9 + 999::numeric/10 + 999::numeric/100;
In your case:
CHECK ("P_RETAILPRICE" = (9 + "P_PARTKEY"::numeric / 10 +
"P_PARTKEY"::numeric / 100)
Sincerely,
Niklas Johansson
Phone: +46-322-108 18
Mobile: +46-708-55 86 90
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Recursive SELECT problem
"Dave Page" writes: > I'm trying to write a query to determine whether or not the current > login role has CREATEDB (or CREATEROLE) privileges, either directly or > inherited from a parent role. Those privileges don't inherit, so I'm not sure why you need a recursive check. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Recursive SELECT problem
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: 17 May 2006 15:07 > To: Dave Page > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Recursive SELECT problem > > "Dave Page" writes: > > I'm trying to write a query to determine whether or not the current > > login role has CREATEDB (or CREATEROLE) privileges, either > directly or > > inherited from a parent role. > > Those privileges don't inherit, so I'm not sure why you need > a recursive check. Because I didn't realise they didn't inherit 'cos I missed the note at the bottom of the CREATE ROLE page :-) Thanks - that makes life significantly easier! Regards, Dave. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Table constraints and INSERT
Niklas Johansson <[EMAIL PROTECTED]> writes: > You must cast the integer column to a float or numeric, try: Since the CHECK is expecting an exact result, better use numeric. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Add column and specify the column position in a table
Hello, I am trying to insert one column to a specific position in a table. In mysql, I can do: . create table test(id varchar(3), name varchar(12)); . alter table test add column givename varchar(12) after id; I am looking for similar things in postgresql to add a new column to the correct position in a table. Could someone hint me please. Thanks alot! Ying Lu ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] help..postgresql mulyiple return values
hi, im new in postgresql, generally new in databases. im trying to make a function using PGAdminIII which returns several types, example, my query is: "select count(id) as requests, organization from connection_requests group by organization" id is of type int8, organization is of type varchar(50). basically the query would return coun(id), and a varchar(50) which is organization. i really dont know what to put on the return type. what would be the best solution? hope to hear from you guys asap.. thank you Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less.
[SQL] insert related data into two tables
Hello, I have two tables like these: TABLE_1: people registry fields: ID_T1, SURNAME, NAME ID_T1 is primary key TABLE_2: work groups fields: ID_T2, TASK ID_T2 is foreign key related to ID_T1 the first table is the list of employees, the second the task. Sometime I need to insert a new employee and at the same time the task for him: I want to INSERT TO table 1 JOHN DOE (the ID is assigned automatically since it's a primary key) and INSERT TO table 2 the ID and the task name for JOHN DOE. Is it possible to create a single query? What is the best way to do this? Thanks, Filippo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] MySQL DB to PostgresSQL DB
Dear all, This is my first post and I will try to be short and clear in my questions and I hope you can help me with that. I have a MySQL DB and I want to export to PostgreSQL DB. My questions are; - Is posible to export MySQL DB to PostgreSQL DB? - Does PostgreSQL DB has tools to import MySQL DB? - If is posible, How do I need to build the DB in MySQL?, in order to have success in the exportation. - Do someone did it before?, exporting MySQL DB to PostgreSQL DB? - What kind of risk do we can to have in this process? - How long can take this process? Thanks in advance. Jose
[SQL] how referencing %rowtype variables without column names
I wrote a trigger and use %rowtype , the problue is I need to put the column name in a variable like Declare columnname VARCHAR(20); NOTIFYRULE NOTIFY_RULE%ROWTYPE begin columnname='TYPE' /* ONE OF NOTIFY_RULE column*/ SELECT * INTO NOTIFYRULE FROM NOTIFY_RULE WHERE STATUS=:OLD.STATUS; end; how can i use this columnname to get the column value form NOTIFYRULE without use "NOTIFYRULE.TYPE" ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [pgsql-www] MySQL DB to PostgresSQL DB
greetings, I don't know if pgsql-www is the right place for your questions or not, but I'll be nice and give you a few ideas on how to get your questions answered.. :) On Tue, 2006-05-16 at 00:19 +0200, Jose Apablaza wrote: > Dear all, > > This is my first post and I will try to be short and clear in my > questions and I hope you can help me with that. > > I have a MySQL DB and I want to export to PostgreSQL DB. > > My questions are; > > - Is posible to export MySQL DB to PostgreSQL DB? directly? without modification? not sure about that. at the very least many of the field types are different in pg vs. other databases like mysql. you'll need to run a script or do a search-and-replace to resolve that. > - Does PostgreSQL DB has tools to import MySQL DB? > - If is posible, How do I need to build the DB in MySQL?, in order to > have success in the exportation. > - Do someone did it before?, exporting MySQL DB to PostgreSQL DB? many people have converted from mysql to postgresql. > - What kind of risk do we can to have in this process? depends on what exactly mysql is doing for you now. > - How long can take this process? depends on the size of the database and skill at this sort of thing. also, if you are using mysql_* calls from (for example) php, you will need to convert that code. personally, I recommend PEAR::DB (assuming php is being used).. that way once you have the database imported to pg, you change the connect string and you are done. info about PEAR::DB is here: http://pear.php.net/DB ... you might also try http://pear.php.net/MDB2 , but I do not have personal experience with that module. I did a google search: http://www.google.com/search?hl=en&q=mysql+to+postgresql&btnG=Google +Search and found a number of links that might help you in your quest. if none of those links are helpful, please let me know privately. another recommendation I would make after using google and reading up on a few things is to ask about this process on irc.. #postgresql on irc.freenode.net is an *excellent* resource. > > Thanks in advance. > > Jose > regards, J -- Jeff MacDonald Zoid Technologies, http://zoidtechnologies.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] table constraint + INSERT
Title: table constraint + INSERT
Hello
I use PostgreSQL in my program and I found a strange behavior, at least for me.
I have a simple table with constraint
CREATE TABLE "PART"
(
"P_PARTKEY" int4 NOT NULL,
"P_RETAILPRICE" numeric,
CONSTRAINT "PART_PRIMARY" PRIMARY KEY ("P_PARTKEY"),
CONSTRAINT "PART_check" CHECK ("P_RETAILPRICE" = (9 + "P_PARTKEY" / 10 + "P_PARTKEY" / 100)
);
And I try to insert a row:
INSERT INTO "PART" ("P_PARTKEY","P_RETAILPRICE") VALUES(999,90109.89);
but it fails: ERROR: new row for relation "PART" violates check constraint "PART_check"
When you check using your head or pocket calculator then this INSERT seems to be correct. Is it some floating point mystery?
Is there some trick?
I'm using version 8.0.1 for Windows.
With thanks, Risto
[SQL] Question about SQL Control Structure(if then, for loop)
Hello, I am new to PostgreSQL and want to use the IF-THEN; LOOP statements in my SQL command file. Thus I may use psql -f cfile to exceute. However I always got syntax error on if/for in psql. For example: RDM=# for i in 1 .. 10 loop RDM-# select "test" RDM-# end loop; ERROR: syntax error at or near "for" at character 1 LINE 1: for i in 1 .. 10 loop RDM=# if exits ( select * from testtable) RDM-# then RDM-# select "TEST" RDM-# ; ERROR: syntax error at or near "if" at character 1 LINE 1: if exits ( select * from testtable) ^ Can someone tell me how to use if/for in psql? The document shows the control structures in the PL/pgSQL section. Must I use if/for inside some procedure/function? Thanks a lot. Jay Get your own "800" number Voicemail, fax, email, and a lot more http://www.ureach.com/reg/tag ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Add column and specify the column position in a table
Emi Lu a écrit : > I am trying to insert one column to a specific position in a table. > > In mysql, I can do: > . create table test(id varchar(3), name varchar(12)); > . alter table test add column givename varchar(12) after id; > > > I am looking for similar things in postgresql to add a new column to the > correct position in a table. > > Could someone hint me please. > There's no similar thing in PostgreSQL. You have to duplicate the table to do it. You can do it in a transaction : CREATE TABLE test (id varchar(3), name varchar(12)); then later : BEGIN; ALTER TABLE test RENAME TO oldtest; CREATE TABLE test (id varchar(3), givename varchar(12), name varchar(12)); INSERT INTO test (id, name) SELECT id, name FROM oldtest; DROP TABLE oldtest; COMMIT; Not really interesting if you have really big tables but, in fact, you shouldn't rely on columns' order. Regards. -- Guillaume. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Question about SQL Control Structure(if then, for loop)
am 16.05.2006, um 21:51:45 -0400 mailte Jay Chiu folgendes: > Can someone tell me how to use if/for in psql? The document In plain SQL isn't if, then, else and so on. > shows the control structures in the PL/pgSQL section. Must I use > if/for inside some procedure/function? Yes. HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 6: explain analyze is your friend
