Tagged Questions
5
votes
3answers
110 views
How to pass a table type with an array field to a function in postgresql
i have a table called book
CREATE TABLE book
(
id smallint NOT NULL DEFAULT 0,
bname text,
btype text,
bprices numeric(11,2)[],
CONSTRAINT key PRIMARY KEY (id )
)
and a ...
2
votes
1answer
92 views
query to identify all data types used in postgresql database tables
I don't want to know all data types, just all data types used in my database. Can this information be queried?
PostgreSQL 8.4 and 9.x versions
I currently need to know all data types for over 200 ...
3
votes
2answers
1k views
changing float->numeric casts from assignment to implicit, dangerous?
In porting an application to PostgreSQL (9.1), one odd SQL incompatibility I've discovered concerns the round() function, specifically the version that takes a second argument indicating the rounding ...
2
votes
3answers
621 views
Should I add an arbitrary length limit to VARCHAR columns?
According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT.
Should I add an arbitrary length limit to a name or address column?
4
votes
1answer
285 views
PostgreSQL data type text vs varchar without length
In PostgreSQL you can create a column with data type character varying (without length precision) or text like this:
ALTER TABLE test ADD COLUMN c1 varchar;
ALTER TABLE test ADD COLUMN c2 text;
Is ...
2
votes
1answer
2k views
Compare a date and a timestamp with time zone with now() in the same query?
I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and ...
3
votes
3answers
2k views
How to change the datatype of a column from integer to money?
I am attempting to convert a PostgreSQL table column from integer to money, but I am receiving the error:
cannot cast type MyColumn to money
I have tried these two statements, but just haven't ...
5
votes
2answers
797 views
Single data type for imprecise date values, as allowed by ISO 8601
How can I store date and time values with reduced precision in a PostgreSQL type, and have them behave as date and/or time values?
ISO 8601 allows date values with reduced precision. ‘1964’, ...
2
votes
1answer
342 views
What are the pro's and con's of using PostgreSQL vs MySQL for binary data?
I read that MySQL has binary safe blobs, but PostgreSQL does not.
So far what I've found suggests that "binary safe" is something that really relates to functions and handling of data, not the data ...
2
votes
2answers
465 views
PostgreSQL: Separate tables vs single table to perserve disk space?
I have 2 tables with the below schemas with equal number of rows. When I run the SELECT relname, relpages FROM pg_class ORDER BY relpages DESC command, they show up as 23GB each even though the data ...
1
vote
1answer
3k views
How can I convert from Double Precision to Bigint with PostgreSQL?
I need to convert a value of Double Precision to Bigint with PostgreSQL. How can I do that?
I have tried with to_bigint(myvalue) but that function didn't exist.
3
votes
1answer
2k views
How to insert an IP-address into an inet column in PostgreSQL?
I would like to insert an IP-address into a column that has type inet. In what format can I insert the data? is it only binary or is there any way I can insert from text e.g. "192.168.1.082"? Are ...
1
vote
1answer
614 views
How can I alter the type of a money-column to decimal in PostgreSQL?
I have a table in PostgreSQL where one column "vat" has type money but I would like to alter it to decimal instead.
How can I do it?
I tried with:
alter table my_table alter column vat type ...
15
votes
3answers
5k views
What are the drawbacks with using UUID or GUID as a primary key?
I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this ...
