[SQL] Select distinct and order by.
Hi all: We have developed an aplication in C with postgresql, using libpq. Our version of development is Postgresql 6.5.3 and it works fine. Now we are migrating the application to various flavours of Linux (S.u.s.e 7.1, tha uses postgresql 7.0.3; Debian Potato, that uses postgresql 7.0.2) and in these dists the next query fails. Select distinct field1, field2 from table1 order by field3; The value return by PQresultErrorMessage is: For SELECT DISTINCT, ORDER BY expressions must appear in target list Whatever this query works fine in postgresql 6.5.3. Is correct this query and so there was a bug on 6.5.3 or there is a bug on the new versions?. Also in certains situations (in versions 7.0.x) this query fails from libpq: Select distinct field1, field2, field3 from table1 order by field1, field2; but if we copy the statement with mouse and do: $psql -d ourdb -c "Select distinct field1, field2, field3 from table1 order by field1, field2;" WORKS FINE. These last situation are in a transaction whith some tables locked, that aren't table1. TIA Carlos. Solaria Mediterranea, S.L.L: P.S.: please send me the answers directly, because I isn't subscribed to the list. ---(end of broadcast)--- TIP 3: 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
[SQL] problem with a query
hi to all i need to do a query to a small postgres database my best efort to the pretended query is in the attached file 'query.txt' but i need that some rows don't be selected of thouse that the query returns. i send in another attached file 'result.txt' with the query result. looking at content of the file you will see some notes at the end of the table (#..) the database as the data to enable the constrution of a school class schedule. In the 'result.txt' all the rows with max=0 were the fisrt to be inserted do the database. each row represent as example math pratical starting at 8:30 for 1:30 at room 3.15 in the bilding CP the rows with max!=0 (1,2,3,...) mean that content of an insert with max=0 was changed [ex diferent time (fiel to_char) or room (field sala) or bilding (field edificio)of the school class]. row with max=1 is a substitution to a row with max=0, row with max=2 substitutes row with max=1 and ... my objective is to obtain all the rows that were not substituted and the ones that represent the last alteration to the shedule i hope the problem was understod thanks for your time _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 query.txt Description: Binary data result.txt Description: Binary data ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] small problem
hi to all I am doing a webpage and i manipulate data from a postgres database. I need to do the same thing with a oracle database. I am using pg_dump to transfer the database data from postgres to oracle but there are a few problems with same data types in a table when i try to import it to a oracle database. create table (...) (...) duration interval default 7 hourDaytimestamp with time zome (...) if i replace this two data types with varchar2(50) it works but i would like if there's a better choice thanks for your time and replay _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail ---(end of broadcast)--- TIP 3: 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
[SQL] small proble
hi to all I am doing a webpage and i manipulate data from a postgres database. I need to do the same thing with a oracle database. I am using pg_dump to transfer the database data from postgres to oracle but there are a few problems with same data types in a table when i try to import it to a oracle create table (...) (...) duration interval default 7 hourDaytimestamp with time zome (...) if i replace this two data types with varchar2(50) it works but i would like if there's a better choice thanks for your time and replay _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail ---(end of broadcast)--- TIP 3: 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
[SQL] bigger problem
in postgres database i created a table with a field like create table (...) (...) var text default '' not null (...) now i need to import the data from this table into a oracle database i used pg_dump I altered the field data type to (...) var varchar2(50) default '' not null (...) but when i was importing the the table into the oracle database all the rows with the field var='' were not inserted. Is there a way to work this around thanks for your time and replay _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Question on triggers and plpgsql
Hello, A question from a complete newbie on this area. I'm trying to implement a mechanism that would allow me to keep track of the last time each row of a table was modified. I have many applications modifying the data, and I would like to avoid having to modify each of those applications (with the risk of forgetting one of them). So, I figured a better approach would be a trigger that gets activated on update (before update, to be specific). Below is what I came up with, but being the very first time I do (or even read about) something with triggers or with plpgsql, I'd like to check if there are any obvious red flags, or if what I'm doing is hopelessly wrong. I added a column last_modified (timestamp data type), and create the following function: create function set_last_modified() returns trigger as ' begin new.last_modified = now(); return new; end; ' language plpgsql; (this is similar to an example from the PG documentation; I'm not sure the keyword "new" is the right thing to use in my case, but it would look like it's a standard way to refer to the "new row" that is about to replace the old one) Then, I created the trigger as follows: create trigger last_modified_on_update before update on table_name for each row execute procedure set_last_modified(); The thing seems to work -- I had to go in a shell as user postgres and execute the command: $ createlang -d dbname plpgsql (I'm not sure I understand why that is necessary, or what implications -- positive or negative -- it may have) Am I doing the right thing? Have I introduced some sort of catastrophe waiting to happen? Thanks for any guidance you may offer to this PL/PGSQL beginner! Carlos -- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Question on triggers and plpgsql
I think I sent my previous message to John only (sorry!) I just wanted to double check one detail that is not explicitly stated in the documentation for createlang. My question is: can I use createlang on a database that is currently active? That is, a database with plenty of tables that has been and is currently in use? My guess is that there should be no problem and no risk in doing that -- but being my first steps in PL, I wouldn't like to trust a beginner's intuition for a production system. Thanks, Carlos -- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Update aborted if trigger function fails?
Hi, I just noticed this (odd?) behaviour, and it kind of scares me. For testing purposes, I put a deliberate syntax error; this wouldn't happen in a real-life situation. But what if the error gets triggered by something that happens later on? say, if the trigger function uses a field that later on gets dropped from the table -- something that was working fine all of a sudden produces syntax errors while executing the function (I just verified, to see if the server would let me drop a column that is being referenced by a function invoked by an active trigger, and it did let me drop it). Isn't this a little fragile? Is there something I could do to avoid this situation? Should trigger functions be extremely simple as to guarantee that an error would never happen? Thanks, Carlos -- ---(end of broadcast)--- TIP 3: 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] Update aborted if trigger function fails?
Richard Huxton wrote: I just noticed this (odd?) behaviour, and it kind of scares me. Isn't this a little fragile? Is there something I could do to avoid this situation? Should trigger functions be extremely simple as to guarantee that an error would never happen? There's nothing else it can do, really. Far better that the whole update fails than you get an inconsistent database. Imagine you have a banking system, and every time you add a row to the transaction-history, you update the "current_balance" table. Which would you prefer, both updates fail, or the two get out of sync? Yes, you are absolutely correct. I guess the concern came up as result of a particular situation, in which failing to properly process the trigger function is not that crucial (I wanted to update some additional information that is "optional", and that can be reconstructed easily after discovering that the trigger function had been failing). But in our case, failing to complete the update is rather critical (things can be reconstructed but under certain conditions only, and only by temporarily shutting down the system for a few minutes). So, I was thinking that there may be a way for the user to instruct PG to ignore the fact that the trigger function failed -- that way, we would overcome the difficulties that you mention in improving dependency checking when functions are involved -- PG wouldn't have to determine it: the user would tell it. So - how do you deal with this? Well, you test. Fair enough. Thanks! Carlos -- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Update aborted if trigger function fails?
Andrew Sullivan wrote: On Tue, Apr 12, 2005 at 10:55:30AM -0400, Carlos Moreno wrote: I guess the concern came up as result of a particular situation, in which failing to properly process the trigger function is not that crucial (I wanted to update some additional information that is "optional", and that can be reconstructed easily after discovering that the trigger function had been failing). But in If you can do some things asynchronously, and you don't care about them very much, then you can use LISTEN/NOTIFY to do such processing. Thanks Andrew for the suggestion -- this may as well be exactly the feature that I needed to begin with. I'm not familiar with the details, but I'll go right away to dig through the docs. Thanks! Carlos -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)
Alvaro Herrera wrote: It is a Postgres limitation as well. We _could_ make the server "really start the transaction" at the point the first query is issued instead of when the BEGIN is issued. And also, really finish the transaction right after the last statement is executed, instead of waiting until the COMMIT is issued :-) Carlos -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] When To Use Quotes
I'm trying to understand when in SELECT statements should and should I not use single quotes to filter my results. For example: SELECT * FROM people WHERE fname = 'James'; or SELECT * FROM price WHERE msrb BETWEEN 50 AND 100; Now is it correct to say that in PostgreSQL or ANSI SQL in general I should use 'single quotes' when the condition is strictly a numerical data type value and everything else should be inside 'single quotes'? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] DECIMAL or NUMERIC Data Types
Does it matter when writing SQL code in PostgreSQL if I use DECIMAL or NUMERIC date types for a column named 'price' assuming it's to store the associated items actual dollar amount? Reading the fine manual*, I can't find a single difference between either and they both are supported / recognized by PostgreSQL: -decimalvariableuser-specified precision, exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point -numericvariableuser-specified precision, exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point CREATE TABLE computers ( id serial primary key, make varchar(50) not null, model varchar(50) not null, owner varchar(50) not null price decimal(7,2) not null, warranty date not null ); *Source = http://www.postgresql.org/docs/9.1/static/datatype-numeric.html -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Unable To Alter Data Type
I have an issue I can't figure out. I have the following TABLE: tysql=# \d customers Table "public.customers" Column| Type | Modifiers --++--- cust_id | character(10) | not null cust_name| character(50) | not null cust_address | character(50) | cust_city| character(50) | cust_state | character(5) | cust_zip | character(10) | cust_country | character(50) | cust_contact | character(50) | cust_email | character(255) | Indexes: "customers_pkey" PRIMARY KEY, btree (cust_id) Referenced by: TABLE "orders" CONSTRAINT "fk_orders_customers" FOREIGN KEY (cust_id) REFERENCES customers(cust_id) Now I'm attempting to ALTER the field 'cust_zip' TYPE from character to integer however I'm getting the following error: tysql=# ALTER TABLE customers ALTER COLUMN cust_zip TYPE bigint; ERROR: column "cust_zip" cannot be cast to type bigint I thought perhaps the stored data in the field conflicted with the data type but I can't see why: tysql=# SELECT cust_zip FROM customers; cust_zip 4 4 4 8 54545 32765 (6 rows) -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable To Alter Data Type
On Wed, Jan 11, 2012 at 7:13 PM, David Johnston wrote: > However, I will say again, you DO NOT WANT TO ACTUALLY DO THIS! > > The specific issue is that some US Postal Code begin with a zero ( 0 ) and > so whenever you want to the zip_code value you need to pad leading zeros if > the length is less than 5. Now consider that a full zip_code can be in 5+4 > format with an embedded hyphen and you no longer can even store it as > numeric. If you deal with Canada (and maybe Mexico) at all then spaces and > letters become acceptable characters within the zip_code. David - Thank you for that great info / explanation. Very informative and helpful. I was not required to make this change but rather just goofing off attempting to learn SQL as I'm rather terrible at it. Can you tell me if there's an organized cheat sheet or something documented in regards to data types commonly used for commonly used field association? I think that's great for people who can't look at the documentation and clearly understand specific definitions or assumed categorization based on the type definition. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Unable To Modify Table
I seem to have an issue where I can't modify a table due to another tables foreign key association: [CODE]trinity=# \d developers Table "public.developers" Column| Type | Modifiers --++--- id | character(10) | not null name| character(50) | not null address | character(50) | city| character(50) | state | character(2) | zip | character(10) | country | character(50) | phone | character(50) | email | character(255) | Indexes: "developers_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "orders" CONSTRAINT "fk_orders_developers" FOREIGN KEY (id) REFERENCES developers(id) [/CODE] Now I want to change the formatting of field data in 'id' in table 'developers': [CODE]trinity=# SELECT id FROM developers; id 11 12 13 14 15 16 (109 rows) [/CODE] Now when I try and change the values before I alter the field TYPE, I get an error that another table (orders) with a foreign key associated with public.developers 'id' field still has old values therefor can't change / modify the 'developers' table. [CODE]trinity=# UPDATE developers SET id = '1000' WHERE id = '11'; ERROR: update or delete on table "developers" violates foreign key constraint "fk_orders_developers" on table "orders" DETAIL: Key (id)=(11) is still referenced from table "orders". [/CODE] How does one accomplish my goal? Is this difficult to change or once that foreign key is created, are you stuck with that particular constraint? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Update Mass Data in Field?
I'm new to SQL so I'm looking for a way to change several email addresses with one command. For example everyone has a 'holyghost.org' domain and I need to change a few 100 email addresses in the field 'emp_email'. I need to UPDATE employees table which has a COLUMN 'emp_email' and change %holyghost.org to %ghostsoftware.com. I tried: UPDATE employees SET emp_email = '%ghostsoftware.com' WHERE emp_email LIKE '%holyghost.org'; It didn't update anything when I ran the command. Does anyone know how I need to correct my SQL statement above to change everyone's email address? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Display Length Between Var & Varchar
I've noticed when I set a field to char, it takes up lots of space over varchar: iamunix=# SELECT * FROM music; id | band| album |date| asin|label +---+--+++-- 1 | Dance Gavin Dance | Downtown Battle Mountain | 2007-05-15 | B000OQF4PQ | Rise Records (1 row) iamunix=# SELECT * FROM music; id | band| album |date| asin|label +---+--+++-- 1 | Dance Gavin Dance | Downtown Battle Mountain | 2007-05-15 | B000OQF4PQ | Rise Records (1 row) I don't know how well it will show in plain text via email but does anyone know why the field display width is wasted with so much white space when set to char? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Change Ownership Recursively
I have a database that I must assign ownership to a new role. I want this new role to own the entire database and all of it's tables, views, triggers, & all. When I run the ALTER DATABASE command below, it only changes the database role but the tables are all still owned by the previous role. Is there a way I can assign the 27 tables to Lauren rather than doing the command one by one for each table? postgres=# ALTER DATABASE iamunix OWNER TO lauren; ALTER DATABASE postgres=# \l List of databases Name| Owner | Encoding | Collate |Ctype| Access privileges ---+--+--+-+-+--- iamunix | lauren | UTF8 | en_US.UTF-8 | en_US.UTF-8 | All tables still owned by Carlos: iamunix=# \d List of relations Schema | Name | Type | Owner +--+--+ public | dept | table | carlos public | dept_id_seq | sequence | carlos public | employees| table | carlos public | employees_id_seq | sequence | carlos public | manager_lookup | view | carlos public | managers | table | carlos public | managers_id_seq | sequence | carlos **PS** I did do a Google search for "PostgreSQL 9.1 change ownership recursively" but either couldn't find what I was looking for or missed it. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Change Ownership Recursively
On Thu, Mar 1, 2012 at 11:38 AM, Eric Ndengang wrote: > Hi > You can try this command "REASSIGN OWNED BY TO ..." like this: > REASSIGN OWNED BY previous_role TO new_role; > DROP OWNED previous_role; I did as follows: iamunix=# \c postgres SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) You are now connected to database "postgres" as user "carlos". postgres=# REASSIGN OWNED BY carlos TO lauren; REASSIGN OWNED postgres=# DROP OWNED BY carlos; DROP OWNED iamunix=# \d List of relations Schema | Name | Type | Owner +--+--+---- public | dept | table| carlos public | dept_id_seq | sequence | carlos public | employees| table| carlos public | employees_id_seq | sequence | carlos public | manager_lookup | view | carlos public | managers | table| carlos public | managers_id_seq | sequence | carlos That didn't work for some reason but mostly because I don't follow the concept of what's being done. I've now since changed the database role owner back to Carlos so now 'Carlos' owns both the database and all of it's tables. Can we start fresh and assume I just got the request to change the specified database and all of it's tables, sequences, views, & triggers to Lauren? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Change Ownership Recursively
I changed to the suggested database which is owned by 'Carlos' and did as instructed. Everything worked fine. Thank you! On Thu, Mar 1, 2012 at 11:23 AM, Carlos Mennens wrote: > I did do a Google search for "PostgreSQL 9.1 change ownership > recursively" but either couldn't find what I was looking for or > missed it. On Thu, Mar 1, 2012 at 1:36 PM, Adrian Klaver wrote: > For future reference including the Postgres version would be > helpful. This area ownership/grants/etc has undergone a lot of changes over > the various versions. I specified above I was using 9.1 PostgreSQL. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Type Ahead Issue
I don't know if this is an issue with my client (Psql) or if it's something I'm doing wrong but I've noticed this issue before and can't figure it out. When I'm using the psql client, I really rely on the tab / type ahead auto completion. When I run my command on one single line, it works fine but when I break my line up into segments, it doesn't understand what I'm trying to do: ALTER TABLE meh ALTER C (if you press 'tab' after the 'c', psql knows the only logical option is 'COLUMN'. When I do the following, I don't get the same results: ALTER TABLE meh ALTER C (when I press 'tab' after 'C' to auto complete 'COLUMN', I get the options only for 'COLLATION' or 'CONVERSION'. Why does it do this? Am I missing something here? When the command is on one line, auto complete works fine but when I break it up as show above in the 2nd example, it acts like 'COLUMN' isn't even a valid option but if I manually type the word 'COLUMN' and finish the command, it works. Why is this acting this way? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] UPDATE Multiple Records At Once?
I'm trying to update a customer record in a table however I need to change several values (cust_address, cust_contact, cust_email). My question is how do I properly format this into one single command? forza=# SELECT cust_id, cust_name, cust_address, cust_contact, cust_email forza-# FROM customers forza-# WHERE cust_name = 'iamUNIX' forza-# ; cust_id | cust_name | cust_address | cust_contact | cust_email +---+---++ 16 | MobileNX | 200 South Shore Drive | Carlos Mennens | [email protected] (1 row) I did a quick Google search and I can see there is a method or procedure which involves parenthesis () however I'm not sure how I would change all the values listed above under one command. Can anyone please give me a quick example so I can see how this is drawn out via ANSI SQL? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Finding Max Value in a Row
I have a problem in SQL I don't know how to solve and while I'm sure there are 100+ ways to do this in ANSI SQL, I'm trying to find the most cleanest / efficient way. I have a table called 'users' and the field 'users_id' is listed as the PRIMARY KEY. I know I can use the COUNT function, then I know exactly how many records are listed but I don't know what the maximum or highest numeric value is so that I can use the next available # for a newly inserted record. Sadly the architect of this table didn't feel the need to create a sequence and I don't know how to find the highest value. Thank you for any assistance! -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Finding Max Value in a Row
Thanks for all the help thus far everyone! I sadly didn't create/design the table and would love to create a SEQUENCE on that particular field but not sure how unless I DROP the table and create from scratch. Currently the data TYPE on the primary key field (users_id) is CHAR and I have no idea why...it should be NUMERIC or SERIAL but it's not so my question is if I want to ALTER the column and create a sequence, would I simply do: ALTER TABLE users ALTER COLUMN users_id TYPE serial ; Obviously if any of the data stored in users_id is actual CHAR, I'm guessing the database would reject that request to change type as the existing data would match. However the data type is CHAR but the field values are all numeric from 100010 - 100301 so I'm hoping that would work for SERIAL which is just INTEGER, right? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Finding Max Value in a Row
On Fri, May 11, 2012 at 3:44 PM, Thomas Kellerer wrote: > Use this: > > alter table users > alter column users_id type integer using to_number(users_id, '9'); > > (Adjust the '9' to the length of the char column) When you wrote "Adjust the '9' to the length of the char column, do you mean change '9' to '312' if my last used maximum value was 312? So the next sequence primary key value would be '313', right? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Finding Max Value in a Row
On Fri, May 11, 2012 at 3:43 PM, Adrian Klaver wrote: > Well the question to ask is if it is declared CHAR was that done for a > legitimate reason? One reason I can think of is to have leading 0s in a > 'number'. Might want to double check that code downstream is not depending > on CHAR behavior. Very good question and asked by myself to the original SQL author and he explained while he didn't use the most efficient data types, he used ones "he" felt would be more transparent across a multitude of RDBMS vendors. So the answer is no, it would not be an issue considering I use and will always use PostgreSQL. If someone else uses a different vendor, they can manage that import/export process then. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Finding Max Value in a Row
On Fri, May 11, 2012 at 4:42 PM, Viktor Bojović wrote: > you can convert from type to type using ::varchar or ::char(size) or > ::integer > so you can use sequence but you will have to convert it's result to suitable > type (that can also be put in default value of user_id attribute) I'm not understanding why I'm not able to change this column type from char to integer? There are no non-numeric existing characters stored in this particular column (cust_id). I've gone so far as to delete the foreign key and primary key that associated with this column (cust_id) but still I get a generic error: forza=# \d customers Table "public.customers" Column| Type | Modifiers --++--- cust_id | character(10) | cust_name| character varying(100) | not null cust_address | character(50) | cust_city| character(50) | cust_state | character(5) | cust_zip | character(10) | cust_country | character(50) | cust_contact | character(50) | cust_email | character(255) | All the values in the column in question: forza=# SELECT cust_id forza-# FROM customers forza-# ORDER BY cust_id; cust_id 10001 10002 10003 10004 10005 (5 rows) forza=# ALTER TABLE customers ALTER COLUMN cust_id TYPE integer; ERROR: column "cust_id" cannot be cast to type integer When I view the logs in /var/log/postgresql.log, I see the same exact error printed above so I can only assume the problem is invalid SQL statement or I'm breaking some ANSI SQL rule. Can someone please help me understand how I can change the data type for this column? I've deleted the primary key constraint so I don't know if that was a good / bad idea. Thanks for any info / help! -Carlos -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Understanding Binary Data Type
Hello everyone! I wanted to ask the list a question about the 'bytea' data type & how I can picture this in my head. I've been reading SQL for about a few months now and since then, I've only been working with textual data. Basically I'm familiar with storing text and numerical characters into tables but my friend told me that databases can hold much more than just ASCI text. In so I've read up on some pages that describe the bytea data type: http://en.wikipedia.org/wiki/Binary_large_object http://www.postgresql.org/docs/9.1/static/datatype-binary.html So my question is can and in fact does PostgreSQL and most other RDBMS have the ability to store large binary files like photos, music, etc etc into an actual table? I'm guessing the data is dumped into the table but rather linked or parsed through the file system store path into the database itself, right? I would just like to know in a basic round about way how databases store and handle large files like .jpg or .png files & regardless how relative this term is, how common is it to use these files or 'bytea' data in tables? Thanks for any info! -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] need help
Hello, Maybe this query can help you SELECT p.name, l.name FROM location l INNER JOIN product_move m ON m.source_location = location.id INNER JOIN product p ON m.product_id = p.id WHERE p.id = $product_id AND m.datetime < $given_date ORDER BY datetime DESC LIMIT 1 It will return the name of the product and the location for a given id and date. 2013/2/21 denero team > Hi All, > > I need some help for my problem. > Problem : > I have following tables > 1. Location : > id, name, code > 2. Product > id, name, code, location ( ref to location table) > 2. Product_Move > id, product_id ( ref to product table), source_location (ref to > location table) , destination_location ( ref to location table) , > datetime ( date when move is created) > > now i want to know for given period of dates, where is the product > actually. > > can anyone help me ?? > > Thanks, > > Dhaval > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
[SQL] SELECT with WHERE clause by column number
Hi! I need to make a query like this: SELECT id FROM myTable WHERE column-number = 'value'; (PS: The id column is the primary key of myTable). That is a select using column number in the WHERE clause what don't exists in SQL. I need this because there's a situation in my program where I don't have the column name. I've solved that querying Postgresql the name of the column with that number, and then creating the SELECT query. But this solution is slow... two database calls... Can I do this with a single query or in a faster way through SQL, an internal function or through a Procedural Language? Thanks Carlos Henrique Iazzetti Santos Compels Informática Santa Rita do Sapucaí - MG BRAZIL www.compels.net ___ Você quer respostas para suas perguntas? Ou você sabe muito e quer compartilhar seu conhecimento? Experimente o Yahoo! Respostas ! http://br.answers.yahoo.com/
Res: [SQL] SELECT with WHERE clause by column number
All my columns have the same data type: text. So it's much easier. Carlos Henrique Iazzetti Santos Compels Informática Santa Rita do Sapucaí - MG www.compels.net - Mensagem original De: Richard Huxton Para: Carlos Santos <[EMAIL PROTECTED]> Cc: Lista PostgreSQL SQL Enviadas: Segunda-feira, 18 de Dezembro de 2006 15:25:27 Assunto: Re: [SQL] SELECT with WHERE clause by column number Carlos Santos wrote: > SELECT id FROM myTable WHERE column-number = 'value'; > (PS: The id column is the primary key of myTable). > > That is a select using column number in the WHERE clause what don't exists in > SQL. > > I need this because there's a situation in my program where I don't have the > column name. > I've solved that querying Postgresql the name of the column with that number, > and then creating the SELECT query. > But this solution is slow... two database calls... If you don't know what the column is, how do you know what you are testing against? Or what type it is, for that matter? Anyway, just have build a list of column-name,column-type pairs for relevant tables at application start-up, or store it in a configuration file. Unless you're building/changing tables all the time, that should work. I'm curious as to what type of application can usefuly query a database without knowing what structure it has. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ___ Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com
[SQL] Query a select that returns all the fields of an specific value of primary key without knownig the name of the single column's primary key?
Hi! I need to query a select that returns all the fields of an specific primary key, but I don't have the single column's name that is constrained as primary key. How can I do that? Something like: SELECT * FROM myTable WHERE myTable.pkey = 'foo'; Thanks Carlos Henrique Iazzetti Santos Compels Informática Santa Rita do Sapucaí - MG www.compels.net ___ O Yahoo! está de cara nova. Venha conferir! http://br.yahoo.com
[SQL] LOCK command inside a TRANSACTION
Hi! I need Postgresql somehow does this for me: - if an user query a select on a table, the rows of the table in the result of this select can not be updated or deleted by another user until this one update, delete or discard the changes on those rows. I've found something about the LOCK command inside a TRANSACTION but I didn't see how I could do that yet. Does anybody have any ideas? Carlos Henrique Iazzetti Santos Compels Informática Santa Rita do Sapucaí - MG Brazil www.compels.net __ Fale com seus amigos de graça com o novo Yahoo! Messenger http://br.messenger.yahoo.com/
[SQL] How can I know if a row is Locked?
How can I know if a row is locked by another transaction. I have in a transaction like that: BEGIN; SELECT * FROM compels.teste WHERE id = '1' FOR UPDATE; PS1: where id is the primary key. PS2: The COMMIT command is done after a long time. In this case the row with the primary-key equals to '1' will be locked to other transactions until the COMMIT command be executed. So, how can I detect if this row is locked? Tks Carlos Henrique Iazzetti Santos Compels Informática Santa Rita do Sapucaí - MG www.compels.net __ Fale com seus amigos de graça com o novo Yahoo! Messenger http://br.messenger.yahoo.com/
[SQL] Left join?
Hi, In the following table, codsol, codate and codfec are foreign keys referencing table func and I need some help to codify a SELECT command that produces the following result set but instead of codsol, codate and codfec I need the respectives names (column nome from table func). postgres=# select * from reqtran; codreq | codsol | codate | codfec +++ 1 ||| 2 | 1 || 3 | 1 | 1 | 4 | 1 | 1 | 1 (4 rows) Thanks in advance, Carlos __ Table definitions: postgres=# \d func Table "public.func" Column | Type | Modifiers +-+--- codfun | integer | not null nome | text| Indexes: "func_pkey" PRIMARY KEY, btree (codfun) postgres=# \d reqtran Table "public.reqtran" Column | Type | Modifiers +-+--- codreq | integer | not null codsol | integer | codate | integer | codfec | integer | Indexes: "reqtran_pkey" PRIMARY KEY, btree (codreq) Foreign-key constraints: "reqtran_codate_fkey" FOREIGN KEY (codate) REFERENCES func(codfun) "reqtran_codfec_fkey" FOREIGN KEY (codfec) REFERENCES func(codfun) "reqtran_codsol_fkey" FOREIGN KEY (codsol) REFERENCES func(codfun) __ Table contents: postgres=# select * from func; codfun | nome +--- 1 | nome1 2 | nome2 3 | nome3 (3 rows) postgres=# select * from reqtran; codreq | codsol | codate | codfec +++ 1 ||| 2 | 1 || 3 | 1 | 1 | 4 | 1 | 1 | 1 (4 rows) ---(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
RES: [SQL] Left join?
It´s just want I need! Perfect! Thanks! Carlos > -Mensagem original- > De: Richard Broersma Jr [mailto:[EMAIL PROTECTED] > Enviada em: sábado, 1 de julho de 2006 18:45 > Para: [EMAIL PROTECTED]; [email protected] > Assunto: Re: [SQL] Left join? > > > > In the following table, codsol, codate and codfec are foreign keys > > referencing table func and I need some help to codify a SELECT > command that > > produces the following result set but instead of codsol, codate > and codfec I > > need the respectives names (column nome from table func). > > > > postgres=# select * from reqtran; > > codreq | codsol | codate | codfec > > +++ > > 1 ||| > > 2 | 1 || > > 3 | 1 | 1 | > > 4 | 1 | 1 | 1 > > postgres=# \d func > > > Table "public.func" > > Column | Type | Modifiers > > +-+--- > > codfun | integer | not null > > nome | text| > > > Indexes: > > "reqtran_codate_fkey" FOREIGN KEY (codate) REFERENCES func(codfun) > > "reqtran_codfec_fkey" FOREIGN KEY (codfec) REFERENCES func(codfun) > > "reqtran_codsol_fkey" FOREIGN KEY (codsol) REFERENCES func(codfun) > > Would this do what you need? > > select R1.codreq, >CS.nome, >CD.nome, >CF.nome > from rectran as R1 >left join func as CS on (R1.codsol=CS.codefun) >left join func as CD on (R1.codate=CD.codefun) >left join func as CF on (R1.codfec=CF.codefun) > ; > > Regards, > > Richard Broersma Jr. > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] problems with copy
El Lun 24 Sep 2007 21:38, chester c young escribió:
> I'm getting lots of delimited files from Excel and MySQL users that,
> mid-file, begin truncating lines if ending in null values.
>
> for example:
> 1781: "one","two","three",,
> 1782: "one","two","three",,
> 1783: "one","two","three",,
>
> (delimited files from Open Office are well behaved)
>
> is there any way to handle this apparently standard aberration in PG?
>
You need to normalize the csv files filtering its contents with some script
...
...
$fields_in_table=9;
while (<>) {
chomp;
@f=split(",");
for $i ( 0 .. $fields_in_table ) {
print $f[$i].",";
};
print "\n";
};
...
...
--
Luis
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
