Re: [SQL] Question on imports with foreign keys
On Thu, 08 Dec 2011 08:48:51 +0200, Andreas wrote: Hi, suppose you need to import a csv with standard ciolums like name, adress, phone, ... and some additional text columns that need to be split off into referenced tables. Those lookup-tables will only be needed for a project with limited life time so I create a schema that might be called "project_x". There I create the necessary lookup tables. The core of the import will be added to the customers table with unlimited livespan. The customers table has a PKey id which is a serial. I don't want to add FKey columns into customers for the new lookup-tables so I create another table in project_x "projectinfos" that stores those FKeys and another FKey that references customers.id. First question: Is this a stupid aproach? It is logical to reduce dependency from the temporary schema to the customer table which is on the permanent schema. If not: How is the easiest way to to find the customer.id of the new customers so I can insert the projectinfos? It is easy to select rows not related with another table. One of the following queries can be used. Select * from "customers" where id not in (select "customerId" from "projectinfos") Select * from "customers" left join "projectinfos" on "customers"."id" = "projectinfos"."customerId" where "projectinfos"."customerid" is null Select * from "customers" where not exists (select true from "projectinfos" where "customers".id = "customerId") -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question on imports with foreign keys
Am 08.12.2011 09:39, schrieb Emre Hasegeli: On Thu, 08 Dec 2011 08:48:51 +0200, Andreas wrote: How is the easiest way to to find the customer.id of the new customers so I can insert the projectinfos? It is easy to select rows not related with another table. One of the following queries can be used. Select * from "customers" where id not in (select "customerId" from "projectinfos") I'm sorry I wasn't clear enough describing the scenario. Lets's say there were already 1000 records in the customers table. Now I add 357 new customers to this table. If I use one of your queries I'd get all 1357 entries of customers since "project_x.projectinfos" would be newly created for this project and therefor empty. I need to know which customers.id was created for which line in the temporary table that I read in with copy. When I have those ids I can fill "project_x.projectinfos" with just those new 357 customer.ids and foreign keys refering the new lookup-tables. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] partitions versus databases
have an db with about 15 tables that will handle many companies. no data overlap between companies. is it more efficient run-time to use one database and index each row by company id, and one database and partition each table by company id, or to create a database for each company? it is a web-based app using persistent connections. no copying. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] prepared statements
-Original Message- From: [email protected] [mailto:[email protected]] On Behalf Of Pavel Stehule Sent: Thursday, December 08, 2011 2:40 AM To: Vad N Cc: [email protected] Subject: Re: [SQL] prepared statements Hello 2011/12/8 Vad N : > > Hi. > > How can i pass a set of values to prepared statement? > > example > I have a prepared query: > select * from users in ( $1 ) > > i would like to pass: 1,2,3,4 and get: > > select * from users in ( 1,2,3,4 ) > > Any ideas? use a array parameter, please regards Pavel Stehule -- Or, in lieu of readily passing arrays from your client API you can always pass a string and modify the query to parse the string into an array. ... users = ANY ( regexp_split_to_array( $1, ',' )::integer[] ) David J. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] partitions versus databases
On 12/08/2011 10:26 PM, chester c young wrote: have an db with about 15 tables that will handle many companies. no data overlap between companies. is it more efficient run-time to use one database and index each row by company id, and one database and partition each table by company id, or to create a database for each company? it is a web-based app using persistent connections. no copying. If you post a question on Stack Overflow and on the mailing list, please link to your stack overflow question from your mailing list post! http://stackoverflow.com/questions/8432636/in-postgresql-are-partitions-or-multiple-databases-more-efficient/ That'll help avoid duplication of effort, and make it easier for people searching for similar topics later to find out more. -- Craig Ringer
[SQL] Query Timeout Question
Hi I set query timeout in code by using SQLSetStmtAttr ODBC API . SQLSetStmtAttr( StatementHandle, SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER)timeout, 0 ) ; SQLExecute(StatementHandle); But I find this setting has no effect.Before adding timeout setting, SQLExecute costs 47 seconds.After adding timeout, costing time is same. Postgres SQL version 8.4.2, ODBC driver 8.4.2 2011-12-09 feng.zhou
Re: [SQL] Query Timeout Question
sorry, timeout is 15 seconds 2011-12-09 feng.zhou 发件人： feng.zhou 发送时间： 2011-12-09 09:25:49 收件人： pgsql-sql 抄送： 主题： [SQL] Query Timeout Question Hi I set query timeout in code by using SQLSetStmtAttr ODBC API . SQLSetStmtAttr( StatementHandle, SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER)timeout, 0 ) ; SQLExecute(StatementHandle); But I find this setting has no effect.Before adding timeout setting, SQLExecute costs 47 seconds.After adding timeout, costing time is same. Postgres SQL version 8.4.2, ODBC driver 8.4.2 2011-12-09 feng.zhou
Re: [SQL] Query Timeout Question
On 12/09/2011 09:44 AM, feng.zhou wrote: Hi I set query timeout in code by using SQLSetStmtAttr ODBC API . SQLSetStmtAttr( StatementHandle, SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER)timeout, 0 ) ; SQLExecute(StatementHandle); But I find this setting has no effect.Before adding timeout setting, SQLExecute costs 47 seconds.After adding timeout, costing time is same. First: You're using a very old patch release with known bugs. Update to 8.4.10 . As for the timeout: PostgreSQL doesn't support query timeouts. It supports a session-level statement timeout. I don't know whether the ODBC driver uses that or not. Examine the ODBC `mylog' output after enabling psqlODBC debugging, and examine the server log after turning on query logging, so you can see what the ODBC driver actually asks the server for when you set a query timeout. -- Craig Ringer
[SQL] Re: Re: [SQL] Query Timeout Question
Thanks for your reply. if timeout setting doesn't be supported, How to solve this question that the client is hung for long time when executing time-comsuming query or query a locked table. 2011-12-09 feng.zhou 发件人： Craig Ringer 发送时间： 2011-12-09 10:05:44 收件人： feng.zhou 抄送： pgsql-sql 主题： Re: [SQL] Query Timeout Question On 12/09/2011 09:44 AM, feng.zhou wrote: Hi I set query timeout in code by using SQLSetStmtAttr ODBC API . SQLSetStmtAttr( StatementHandle, SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER)timeout, 0 ) ; SQLExecute(StatementHandle); But I find this setting has no effect.Before adding timeout setting, SQLExecute costs 47 seconds.After adding timeout, costing time is same. First: You're using a very old patch release with known bugs. Update to 8.4.10 . As for the timeout: PostgreSQL doesn't support query timeouts. It supports a session-level statement timeout. I don't know whether the ODBC driver uses that or not. Examine the ODBC `mylog' output after enabling psqlODBC debugging, and examine the server log after turning on query logging, so you can see what the ODBC driver actually asks the server for when you set a query timeout. -- Craig Ringer
