Re: [GENERAL] CSV files & empty strings
On 20 okt 2009, at 16.15, Raymond O'Donnell wrote: On 20/10/2009 05:55, Nate Randall wrote: However, I need some method of "converting" the empty string "" values into NULL values after import, so that I can change the date fields back to date-type. Any suggestions on how this could be accomplished? How about: update set = null where = ''; You can also do the update and the column data type change in one fell swoop like so: ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE DATE USING NULLIF(column_name, '')::DATE; Sincerely, Niklas Johansson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] limit table to one row
On 4 jun 2009, at 22.17, Richard Broersma wrote:
On Thu, Jun 4, 2009 at 1:13 PM, Brandon Metcalf
wrote:
Is there a way when creating a table to limit it to one row? That
is,
without using a stored procedure?
Sure just add a check constraint along the lines of:
CONSTRAINT Only_one_row
CHECK( tableuniqueid = 1 ); --assuming you row has a unique id
of 1
Another way, which I've used a couple of times, is to use the rule
system:
CREATE TABLE single_row (value text);
INSERT INTO single_row VALUES ('value');
CREATE RULE no_insert AS ON INSERT TO single_row DO INSTEAD NOTHING;
CREATE RULE no_delete AS ON DELETE TO single_row DO INSTEAD NOTHING;
This way, the table must have exactly one row. I believe the
constraint check would still allow the row to be deleted, which you
may or may not want.
If you want an error to be raised when inserting or deleting, you'd
have to call a function raising the error in the rule. A minor
drawback is that the table still isn't safe from TRUNCATE though.
Sincerely,
Niklas Johansson
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql and Mac OS X
On 4 nov 2008, at 11.21, Tom Allison wrote: I tried getting a source install on my mac book yesterday and today. It's not a normal *nix installation. The location of the files are all non-standard. 'make' is prefixed by /Developer/usr/bin/. That's not right. It should definately live in /usr/bin on a normal Mac OS X install. What versions of Mac OS X and the developer tools do you have? Did you make some non-standard choice during the installation of the dev tools? Sincerely, Niklas Johansson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql and Mac OS X
On 28 okt 2008, at 23.41, Tom Allison wrote: I can get postgresql installed in three flavors: EnterpriseDB has a dmg package for Mac. macports has their own package. fink also has their own package. You also have the fourth, most delicious flavor: build it yourself; PostgreSQL compiles nicely on Mac OS X. I've never had any reason to regret not using a package manager yet. I was using macports but got into a cluster-F on versions and multiple installs. After a spell I had all four versions 8.0 - 8.3 installed in order to use postgres, ruby, perl, and rails together. Do you mean that Macports installed different versions of Postgres because the other packages had different dependencies? Don't know if compiling from source would help you there, but surely there must be some way to tell the package manager that a certain dependency already exists, albeit somewhere else? Sincerely, Niklas Johansson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql and Mac OS X
On 28 okt 2008, at 23.41, Tom Allison wrote: I can get postgresql installed in three flavors: EnterpriseDB has a dmg package for Mac. macports has their own package. fink also has their own package. You also have the fourth, most delicious flavor: build it yourself; PostgreSQL compiles nicely on Mac OS X. I've never had any reason to regret not using a package manager yet. I was using macports but got into a cluster-F on versions and multiple installs. After a spell I had all four versions 8.0 - 8.3 installed in order to use postgres, ruby, perl, and rails together. Do you mean that Macports installed different versions of Postgres because the other packages had different dependencies? Don't know if compiling from source would help you there, but surely there must be some way to tell the package manager that a certain dependency already exists, albeit somewhere else? Sincerely, Niklas Johansson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to tell PostgreSQL about a relationship
On 26 okt 2008, at 10.44, Thomas wrote: Currently I have 3 tables: Product(id, title, price) Item(id, product_id, order_id, quantity) Order(id, amount, paid) So how do I tell PgSQL that when I remove a given order, it should remove all associated items also? Use a foreign key constraint with the appropriate action: CREATE TABLE Item ( id SERIAL PRIMARY KEY, product_id INTEGER NOT NULL REFERENCES Product(id) ON DELETE RESTRICT ON UPDATE CASCADE, order_id INTEGER NOT NULL REFERENCES Order(id) ON DELETE CASCADE ON UPDATE CASCADE, quantity NUMERIC(5,2) NOT NULL ); For more info, see the docs: http://www.postgresql.org/docs/8.3/ interactive/ddl-constraints.html#DDL-CONSTRAINTS-FK Sincerely, Niklas Johansson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] installation on Mac OS X 10.5.1
On 22 dec 2007, at 06.56, Chuck wrote: What is the best way to install PostgreSQL on Mac OS X 10.5.1? Or, perhaps I should ask is there a preferred way to PostgreSQL on Mac OS X? Is a disk image installer fine? Or, is it better to compile all the components? I've always installed from source and I've never been sorry. Haven't tried it on Leopard yet though. There's no reason to believe that the various binary installers should be worse than a source install, but since I've never tried them I really can't tell. Compiling from source is a good way to get to know both your OS and Postgres better. Sincerely, Niklas Johansson ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] unexpected behavior in combining timestamps with times zone and intervals
On 3 nov 2007, at 12.26, Charles Seaton wrote:
select ('12/31/2006 UTC'::timestamptz + '307 days 02:45:30'::interval)
However, this gives an incorrect result (off by 1 hour)
"2007-11-02 18:45:30-07"
Have you checked your servers TimeZone setting? Also, which Postgres
version are you running? I get the following from 8.2.5, which seems
to be correct:
test=# select ('2006-12-31 UTC'::timestamptz + '307 days
02:45:30'::interval) AT TIME ZONE 'MST';
timezone
-
2007-11-02 19:45:30
(I have a different DateStyle setting, so I had to change the input
format. I also added the 'AT TIME ZONE' statement, since my server's
in another time zone.)
Sincerely,
Niklas Johansson
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] full join question...
On 5 apr 2006, at 18.37, Jonas F. Henriksen wrote: ehh, sorry, yes, I also want to retrieve other values from the table, but I left them out for clarity (which made it maby less clear...). Maybe it makes more sense if you define the table as CREATE TABLE testtable ( depth integer, measuretype integer, operation integer, value float ); ...where I'm actually interested in the value... Well, is there something else I don't get or couldn't you just select that as well (perhaps without the DISTINCT then)? SELECT depth, value FROM testtable WHERE measuretype IN (1040, 4001); Sincerely, Niklas Johansson ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Separation of clients' data within a database
On 1 dec 2006, at 15.19, John McCawley wrote: That's the first idea I've seen that looks like it might actually work... (Not that the other ideas were bad, but I just couldn't see how I could fit the solutions into my current app) So what would my user setup look like? Would it look something like this: createuser joe grant select on schema company_a to joe (whatever other permissions) alter user joe set search_path='common','company_a'; createuser bob grant select on schema company_b to bob (whatever other permissions) alter user bob set search_path='common','company_b'; No, you wouldn't need separate schemas for each user, and the users should *not* be allowed access to the master schema. The views in the customer schema would, as I said, use a function (e.g. get_client_ids ()) that uses CURRENT_USER (which will evaluate to either joe or bob, according to your example above) to lookup the actual client_ids. This means that you can grant every user the same rights on the customer schema views, and the rights management is done by the function (which is better than hardcoding values into the views; if the requirements change you just update the function), together with an additional table in the master schema. This table could look something like this: role | client_id -+-- joe | 100 joe | 101 bob | 102 which would mean that joe is a supervisor that can see both client 100 and client 101, while bob can see only client 102. You would probably need some other tables to keep track of which client_id should be used or allowed for data insertion if the user has more than one client_id, but you get the idea. How portable is all of this? Could a comparable structure be implemented in MS SQL or Oracle? As far as I know, yes. (Quite some time since I last had anything to do with either of those. Not that I lament the fact... :-) Sincerely, Niklas Johansson Phone: +46-322-108 18 Mobile: +46-708-55 86 90 ---(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: [GENERAL] Separation of clients' data within a database
How about this: * Have one master schema that holds all physical tables. This schema is accessible only by the superuser. * Create a schema which contains views that mirror the master schema. This is the schema that the customers connect to, each using a different db role, and since it's a mirror of the master schema, it means no change in app structure (except dropping rights management, see below). * Let these views pull their data from the respective master schema table (i.e. SELECT * FROM ...) with the addition of a WHERE-clause on client_id, that uses a function: ...WHERE client_id IN (get_client_ids ()). * The 'get_client_ids()'-function should query a table in the master schema that keeps the client_id's that are assigned to each db role (e.g. SELECT client_id FROM foo WHERE role=CURRENT_USER), and return those client_id's. For a regular customer, it would return one client_id, for a supervisor kind of user, it would return two or more, perhaps even all, client_id's. * Have UPDATE and INSERT rules on the views that store the data in the actual master schema tables. (The rules would of course have to add client_id, this time through a function that can only return one client_id.) To conclude: one master schema, one mirrored customer schema that adapts to the db role, one additional table in the master schema to handle the rights. Sincerely, Niklas Johansson Phone: +46-322-108 18 Mobile: +46-708-55 86 90 ---(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: [GENERAL] ORDER BY
On 14 nov 2006, at 23.03, MicroUser wrote: I need sorted result but the way like this: 0 | Anna 3 | Fernando 2 | Link 1 | Other Record '1 | Other' must by at the end of query result. How I can get it? Well, maybe not the answer you're looking for, but a rather clean way to do this would be to only store actual names in the table, let your application do the select and sort, and then add the 'Other' at runtime. If the 'Other' needs to be stored, perhaps it could be represented with a NULL value instead? (It's not really a name, just a placeholder for not knowing, isn't it?) Sincerely, Niklas Johansson Phone: +46-322-108 18 Mobile: +46-708-55 86 90 ---(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: [GENERAL] Default directory for postgres user?
On 2 jul 2006, at 18.29, Victor Escobar wrote: What should the default directory for the postgres user be? I'm using OSX 10.4. Right now, the default directory is set to /dev/null. I've set the home directory to /var/empty and the shell to /usr/bin/ false, like most of the other daemon users. /dev/null should be ok though. Sincerely, Niklas Johansson Phone: +46-322-108 18 Mobile: +46-708-55 86 90 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] full join question...
On 5 apr 2006, at 18.37, Jonas F. Henriksen wrote: ehh, sorry, yes, I also want to retrieve other values from the table, but I left them out for clarity (which made it maby less clear...). Maybe it makes more sense if you define the table as CREATE TABLE testtable ( depth integer, measuretype integer, operation integer, value float ); ...where I'm actually interested in the value... Well, is there something else I don't get or couldn't you just select that as well (perhaps without the DISTINCT then)? SELECT depth, value FROM testtable WHERE measuretype IN (1040, 4001); Mvh, Niklas Johansson Tel: 0322-108 18 Mobil: 0708-55 86 90 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] full join question...
On 5 apr 2006, at 16.39, Jonas F. Henriksen wrote: Hi, I'm working with postgres and I have a question regarding a self- join on my table (se below for table definition and testdata): I want to retrieve all depths that have either a measuretype=1040 or a measuretype=4001 or both. All help apreciated (hope you understand what I want to do...), Not sure I understand why you need a join... You say you want to retrieve all depths that have certain measuretypes. Wouldn't the following query do? SELECT DISTINCT depth FROM testtable WHERE measuretype IN (1040, 4001); Is there anything else you need from the query as well? Mvh, Niklas Johansson Tel: 0322-108 18 Mobil: 0708-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: [GENERAL] Problem getting plpgsql to choose the right query plan
On 13 mar 2006, at 11.35, Helge Elvik wrote: Is there any way for me to force plpgsql not to use a cached query plan, but instead figure out what’s best based on the LIKE-string that actually get passed to the function? You can build the query as a string and EXECUTE it. This will force a new plan to be prepared. http://www.postgresql.org/docs/8.1/interactive/plpgsql- statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Sincerely, Niklas Johansson ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT with REAL...
On 6 feb 2006, at 19.32, Philippe Ferreira wrote: I've just realized that this way, it works very fine : SELECT * FROM mytable WHERE myreal = 13.95::real; But I still don't understand very well why I need the explicit conversion (::real) ... Try this: SELECT 13.95 = 13.95::real; It should yield false, because the first number constant is presumed to be of type numeric, which is an exact format, and the second constant is explicitly cast to a single precision floating point number, in which it doesn't fit*, and therefore actually is stored as 13.948**. So, the comparison is in fact 13.95=13.948, which of course is false. To see the effect in another way, try: SELECT 13.95::real + 0.01; *) The reason it doesn't fit is that the floating point representation is using base 2, instead of base 10. **) The exact value could vary, depending on the floating point implementation of your system. This is what my implementation does. Sincerely, Niklas Johansson ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
