Tag Info

New answers tagged

0

Information schema vs. system catalogs We have had discussions about this many times. The information schema serves certain purposes. If you know your way around the system catalogs, those serve most purposes better, IMO. The system catalogs are the actual source of all information. The information schema provides standardized views which help with ...


1

Use the information_schema views, they're SQL-standard and contain the information you want. You can also directly access pg_class, pg_attribute, etc, but that's unportable and often fiddlier; you may need helper functions like oidvectortypes, pg_get_function_arguments, etc for some things. If you want to see how psql executes something like \dt, run psql ...


1

You can use the psql command line client. \dt will show a list of tables \dv will show a list of views \d [object_name] will describe the schema of the table or view Not sure how you would describe a query though. More info: https://manikandanmv.wordpress.com/tag/basic-psql-commands/


3

For now While stuck with your unfortunate solution: CHECK ((end_time AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time = '23:59:59'::time) That's right, AT TIME ZONE two times: The first instance transforms your timestamp without time zone into timestamp with time zone. that's assuming you are actually storing UTC times. The second instance converts ...


3

You may use to_char to get the time fields from a single function call: check (to_char(end_time at time zone 'UTC' at time zone 'US/Eastern','HH24:MI:SS') = '23:59:59') Seconds given by SS are not rounded up so that should be OK as an equivalent to floor


0

Try this. It maybe be faster, but still unreadable: alter table bananas add constraint ck_end_time_is_end_of_day check ( (end_time at time zone 'UTC' at time zone 'US/Eastern')::timestamp::date + interval '23:59:59' = date_trunc('second', end_time at time zone 'UTC' at time zone 'US/Eastern') ) ;


0

I have done something like this and it works. ([Drv DOB] == "0") || ([Drv DOB] == "101") ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([Drv DOB],1,2) + "-" + SUBSTRING([Drv DOB],3,2) + "-" + SUBSTRING([Drv DOB],5,2))


7

Data Flow Here's the general approach I'd take to solving your problem. I started with your source data and added some other conditions - a NULL as well as a 14 and 15 year to ensure my logic later is correct. SELECT D.DrvDOB FROM ( VALUES ('470324') , ('470324') , ('470209') , ('140209') , ('150209') , ('101') , ('0') , ...


0

It looks like you have your statement correct but it appears you have the order of substring wrong (currently you have yy-mm-dd). Try flipping it around to be mm-dd-yy (DT_DATE)(SUBSTRING([Drv DOB],3,2) + "-" + SUBSTRING([Drv DOB],5,2) + "-" + SUBSTRING([Drv DOB],1,2))


2

It depends on the method you're using to alter the column. We'll start with a simple table: CREATE TABLE dbo.Customers (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, CustomerName NVARCHAR(200)); CREATE INDEX IX_CustomerName ON dbo.Customers(CustomerName); Then use the SQL Server Management table designer to change the CustomerName column to a VARCHAR(200) ...


1

Just out of interest, how would you get records between say 28 Feb 2012 and Today? Ask your manager to do it being as it's their idea. Spoiler warning! For me, I would just skip the ( AND ( OR )) nonsense and go straight to a calendar table. You could also use DATEFROMPARTS in SQL 2012 but that will probably result in a table scan: -- Ugly; hard to ...


1

You cannot simply use the difference operator -, because it does not allow multiple resulting rows: ERROR: result of range difference would not be contiguous So you need to create three rows and use the intersection operator *: 1st range unbounded up to lower bound 2nd range as given 3rd range upper bound to unbounded WITH cte(r, val) AS (SELECT ...


3

No. To my knowledge, changing a column that is included in an index is not possible (you'll get an error message to that effect). You would have to drop the index before changing the column, and then re-apply the index again. The only exception to this that I am aware of is that you can change a column from NOT NULL to NULL without having to drop the index ...


2

I think it is never reasonable. Its like a create a latitude column, and longitude column, instead of using geographic datatypes. With the semantic datatypes, you will have timezone support, range types, and sooo much examples of good and advanced use, much, much more than have 3 columns. And then if you want (or your boss want...) you can use datepart


3

I guess it depends on how you want to use that data. There may be some advantage to the application using it. For example it might make reporting or filtering by year or month easier. But I've never heard of that as some kind of best practice on its own. The downside of this approach is that you lose the ability to use the standard date functions in SQL. ...


2

Horses for courses. If you want to be able to query on data in sliding ranges, like "sum of hours recorded for the last 2 weeks" then sticking with a date format may be better. If you think you'll be querying for things like "number of hours recorded in January (any year)" then go ahead and store it like that. In my experience, any small performance ...


2

The DateTime2(0) datatype takes up less space on the harddisk (6 bytes per row) The DateTime2 datatype is searchable, as it is possible to write a query that will span from December 30th 2013 to January 2nd, where the SQL Server will disregard dates before and after. Such a range query is not easy to write with individual columns. The DateTime2 datatype is ...


2

Per documentation: It is possible to access the two component numbers of a point as though the point were an array with indexes 0 and 1. But geometric types are not arrays. While the value as a whole can be NULL, parts of a point (or any geometric type) cannot. All geometric types can be input as string literals and cast to the respective type. ...


5

While Rolando has my vote, because he is right in your case, I want to answer your original question (for you in the future and for others) regarding percona toolkit: pt-table-sync is not what you want. Think about pt-online-schema-change. While there are some hard corners, it works on RDS with the right configuration or minimal changes on the script. ...


4

Look carefully at your question. You said you are using MEDIUMINT UNSIGNED. The range of MEDIUMINT SIGNED is -8,388,608 to 8,388,607 The range of MEDIUMINT UNSIGNED is 0 to 16,777,215 This is from the MySQL Documentation At present, you are not in danger of hitting the max value. UPDATE 2014-08-04 14:28 EDT If you are willing to schedule downtime and ...


5

The size of the physical table is typically (except for opportunistic pruning of removable pages from the end of the table) not reduced by running VACUUM (or VACUUM ANALYZE). You need to run VACUUM FULL to actually shrink the table. That's not necessarily what you want to do on a regular basis if you have write load on your table. Dead rows provide wiggle ...


1

The composite type is clean design, but it does not help performance at all. First of all, float translates to float8 a.k.a. double precision in Postgres. You are building on a misunderstanding. The real data type occupies 4 byte (not 8). It has to be aligned at multiples of 4 bytes. Measure actual sizes with pg_column_size(). SQL Fiddle demonstrating ...


1

The parameter INTERNALLENGTH is only applicable to the creation of a new base type, which is a rather specialized operation for advanced users. It would require to provide input and output function etc. What you display is the creation of a new composite type, which is a more common operation. There is no parameter INTERNALLENGTH for that purpose. Read the ...



Top 50 recent answers are included